Wednesday, January 28, 2015

Flashback usage on Data guard for Data Recover and Re-instate Standby database after failover

Flashback usage in Standby for Data recovery
==================================

1) Enable flashback on primary and standby database.



SQL> select DB_UNIQUE_NAME, FLASHBACK_ON from V$database;

DB_UNIQUE_NAME                 FLASHBACK_ON
------------------------------ ------------------
source                         YES


SQL> select DB_UNIQUE_NAME, FLASHBACK_ON from V$database;

DB_UNIQUE_NAME                 FLASHBACK_ON
------------------------------ ------------------
sourstby                       YES



Primary
=======

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      5000

SQL> delete from test_user.emp where rownum < 2000;

1999 rows deleted.

SQL> commit;

Commit complete.


SQL> alter system switch logfile;

System altered.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001



In Standby
==========

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001


-- In primary it has been deleted erroronously.

Lets restore the database back in the standby site.


SQL> alter database recover managed standby database cancel;

Database altered.


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             230687000 bytes
Database Buffers           75497472 bytes
Redo Buffers                4747264 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp('29-JAN-15 00:30:00','DD-MON-YY HH24:MI:SS') ;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      5000

-- Now export the table and import in the primary.



[oracle@standalone2 ~]$ exp file=emp_tbl.dmp tables=test_user.emp log=emp_tbl.log

Export: Release 11.2.0.3.0 - Production on Thu Jan 29 02:27:44 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Username: / as sysdba


About to export specified tables via Conventional Path ...
Current user changed to TEST_USER
. . exporting table                            EMP       5000 rows exported
Export terminated successfully without warnings.


[oracle@standalone2 ~]$ exp file=emp_tbl.dmp tables=test_user.emp log=emp_tbl.log

Export: Release 11.2.0.3.0 - Production on Thu Jan 29 02:27:44 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST_USER
. . exporting table                            EMP       5000 rows exported
Export terminated successfully without warnings.


scp the dmp file to the prod.


Truncate the table in prod


SQL> truncate table test_user.emp;

Table truncated.


-- Import the dmp file.

[oracle@rhel11gr2rac1 oracle]$ imp file=emp_tbl.dmp tables=emp fromuser=test_user touser=test_user ignore=y

Import: Release 11.2.0.3.0 - Production on Thu Jan 29 10:39:25 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST_USER's objects into TEST_USER
. . importing table                          "EMP"       5000 rows imported
Import terminated successfully without warnings.


Now start the MRP process in standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001
SQL> /

  COUNT(*)
----------
      3001

SQL> /

  COUNT(*)
----------
      3001

SQL> /
/select count(*) from test_user.emp
*
ERROR at line 1:
ORA-08103: object no longer exists

SQL> /

  COUNT(*)
----------
      5000

-- Now both the db are in sync





====*********************************************================================================================



Flashback usage to re-instate the standby database after the failover
=====================================================================


Create standby redologs on both primary and standby logs.

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/source/stdb_redo0.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/source/stdb_redo05.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/source/stdb_redo06.log') size 52428800;

Database altered.


SQL> select * from V$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
         4 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED
         5 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED
         6 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED


-- Now

Standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/source/stdb_redo0.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/source/stdb_redo05.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/source/stdb_redo06.log') size 52428800;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

    THREAD       LAST
---------- ----------
         1        123


SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> alter database open;

Database altered.


SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
2937170


On the primary flashback the database to the above scn.



SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             301990168 bytes
Database Buffers            4194304 bytes
Redo Buffers                4747264 bytes
Database mounted.


SQL> flashback database to scn 2937170;

Flashback complete.


SQL> alter database flashback off;

Database altered.

-- Flashback off will delete all the flashback logs, since we will be creating a standby controlfile and mounting it, it will
overwrite all the flashback details will become obsolete, so we can turn off the flashback.


SQL> alter database flashback off;

Database altered.

=== For conver this database to physical standby either we can create a standby controlfile on the database and over write
the existing controlfiles or can execute the command to conver to physcial stanby

===============================================================================
SQL> alter database create standby controlfile as '/home/oracle/ctl_file.ctl';

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> exit

SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             301990168 bytes
Database Buffers            4194304 bytes
Redo Buffers                4747264 bytes
Database mounted.

SQL> select database_role from V$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

-- Overwrite the existing controlfile with the created standby controlfile.

[oracle@rhel11gr2rac1 ~]$ cp ctl_file.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@rhel11gr2rac1 ~]$ cp ctl_file.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl

==============================(or)=============================================

SQL> alter database convert to physical standby ;

Database altered.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             230687000 bytes
Database Buffers           75497472 bytes
Redo Buffers                4747264 bytes
Database mounted.

SQL> select database_role from V$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY


=====================================================================================



-- Now enable the archive dest 2 in the current primay database.

SQL> show parameter dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE


-- Now start the MRP in the current standby db.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.



SQL> select database_role from V$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY



SQL> alter database recover managed standby database cancel;

Database altered.


SQL> alter database flashback on;

Database altered.


SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


In prod

======

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001


SQL> delete from test_user.emp where rownum < 1000;

999 rows deleted.

SQL> commit;

Commit complete.


In Current Standby:=
====================

Now check the propogation in standby.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001

SQL> /

  COUNT(*)
----------
      2002


=================================================================================================================




No comments:

Post a Comment