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
=================================================================================================================
==================================
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