Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
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.
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,
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.
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,
You can check it is mounted but not opened by,
Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.
Step 05: Open the database and check the database_role and open_mode.
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,
In case of RAC you just need to shut down all but one instance.
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 areahas 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 800Gwhich 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,
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 BDDIPDRSYou 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 BDDIPDRSNow 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 point SNAPSHOT_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;
In case of RAC you just need to shut down all but one instance.
No comments:
Post a Comment