Converting a Physical Standby Database into a Snapshot Standby Database
Before starting into the steps it is worth to know what is snapshot standby database.
A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby database.
Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.
The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.
Step 01: Stop Redo Apply in Physical Standby Database, if it is active.
A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby database.
Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.
The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.
Step 01: Stop Redo Apply in Physical Standby Database, if it is active.
Login to any database instance of the Physical standby database if it is RAC. In case of Non-RAC you have only once instance/one database.
- Check status by,
SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;
OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
MOUNTED PHYSICAL STANDBY BDDIPDC BDDIPDRS
- I have also checked the instance to know in which instance I am performing stop redo log.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
bddipdc1
- Issue stop redo log apply command.
SQL> alter database recover managed standby database cancel;
Database altered.
Step 02: In case of RAC database, shut down all but one instance.
If you are non RAC environment then simply you can ignore this step. In case of RAC environment just you need to shutdown other instances except one. In this case I have 2 node RAC and I am just shutting down bddipdc2 instance.
$ sqlplus sys/oracle@bddipdrs2 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 12:48:48 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;
OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
MOUNTED PHYSICAL STANDBY BDDIPDC BDDIPDRS
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
bddipdc2
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Step 03: Ensure that database is mounted and not open. Also ensure that a fast recovery area has been configured.
It is not necessary for flashback database to be enabled but you should enable FLASHBACK database if you want to convert a snapshot standby database back into a physical standby database.
Checking fast recovery area by,
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> show parameter db_reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECOVERY
db_recovery_file_dest_size big integer 800G
which means fast recovery area is configured as we are using DB_RECOVERY_FILE_DEST as archival destination.
You can check it is mounted but not opened by,
You can check it is mounted but not opened by,
SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;
OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
MOUNTED PHYSICAL STANDBY BDDIPDC BDDIPDRS
You can check flashback database option by,
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.
SQL> alter database convert to snapshot standby;
Database altered.
Step 05: Open the database and check the database_role and open_mode.
SQL> alter database open;
Database altered.
SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;
OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
READ WRITE SNAPSHOT STANDBY BDDIPDC BDDIPDRS
Now let's see in the alert log to know what happens in the backend.
alter database recover managed standby database cancel
Tue Mar 08 12:17:24 2011
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Waiting for MRP0 pid 17366 to terminate
Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Mar 08 12:17:24 2011
MRP0: Background Media Recovery process shutdown (bddipdc1)
Managed Standby Recovery Canceled (bddipdc1)
Completed: alter database recover managed standby database cancel
Tue Mar 08 12:17:42 2011
Reconfiguration started (old inc 8, new inc 10)
List of instances:
1 (myinst: 1)
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Tue Mar 08 12:17:42 2011
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Tue Mar 08 12:17:42 2011
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
Tue Mar 08 12:17:53 2011
alter database convert to snapshot standby
Created guaranteed restore pointSNAPSHOT_STANDBY_REQUIRED_03/08/2011 12:17:53
Tue Mar 08 12:17:53 2011
krsv_proc_kill: Killing 219043332100 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 449582938
Resetting resetlogs activation ID 1523683415 (0x5ad19057)
Online log +DATA/bddipdrs/onlinelog/group_1.351.728933147: Thread 1 Group 1 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_1.256.728933147: Thread 1 Group 1 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_2.356.728933149: Thread 1 Group 2 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_2.326.728933149: Thread 1 Group 2 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_4.379.728933149: Thread 2 Group 4 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_4.366.728933149: Thread 2 Group 4 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_14.374.728933149: Thread 2 Group 14 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_14.354.728933149: Thread 2 Group 14 was previously cleared
Standby became primary SCN: 449582936
Tue Mar 08 12:17:55 2011
Setting recovery target incarnation to 6
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
alter database open
This instance was first to open
Picked broadcast on commit scheme to generate SCNs
Tue Mar 08 12:18:00 2011
Assigning activation ID 1523975741 (0x5ad6063d)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: +DATA/bddipdrs/onlinelog/group_1.351.728933147
Current log# 1 seq# 1 mem# 1: +DATA/bddipdrs/onlinelog/group_1.256.728933147
Successful open of redo thread 1
Tue Mar 08 12:18:01 2011
ARC4: Becoming the 'no SRL' ARCH
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Mar 08 12:18:01 2011
ARC0: Becoming the 'no SRL' ARCH
Tue Mar 08 12:18:01 2011
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process GTX0
Tue Mar 08 12:18:02 2011
GTX0 started with pid=43, OS id=24612
Starting background process RCBG
Tue Mar 08 12:18:02 2011
RCBG started with pid=44, OS id=24614
replication_dependency_tracking turned off (no async multimaster replication found)
Redo thread 2 internally disabled at seq 1 (CKPT)
Tue Mar 08 12:18:02 2011
ARC3: Becoming the 'no SRL' ARCH
ARC3: Archiving disabled thread 2 sequence 1
Archived Log entry 48206 added for thread 2 sequence 1 ID 0x0 dest 1:
Tue Mar 08 12:18:03 2011
Starting background process QMNC
Tue Mar 08 12:18:03 2011
QMNC started with pid=46, OS id=24618
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
Note that, when the physical standby is converted to a snapshot standby database a guaranteed restore point is created. In this case we see the name is SNAPSHOT_STANDBY_REQUIRED_03/08/2011. When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion.
If you want to convert the snapshot standby database into physical standby database then you simply need to do,
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby;
