Oracle DBA Warriors Blog

Steps on converting a Snapshot Standby database to Physical Standby database: 1) Check for database role, open mode & Restore point create by this conversion. STDBY.SYS.SQL> set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000 set trimspool on time on timi on feedback on verify off echo off heading on; col name for a65 col scn for 9999999999999999999999 col time for a40 col guarantee_flashback_database for a30 col db_unique_name for a15 select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b; select name, scn, time, guarantee_flashback_database, storage_size, database_incarnation# from gv$restore_point; 2) If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversi...

Standby Databases: Till Oracle 10g, we have two types of standby databases namely physical standby database and logical standby database. From Oracle 11g, we got two more types of standby databases, called ADG (Active Data Guard) and snapshot standby database. Will the details of each Standby Databases below: 1.) Physical Standby Database: 2.) Logical Standby Database: 3.) Oracle Active Data Guard: 4.) Snapshot Standby Database: Oracle provides a unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions. Quite often we have the standby database opened in READ Only mode for reporting purposes and optionally have the active data guard implemented, but a snapshot standby database can be used to perform both READ and WRITE activities. The use of snapshot standby database is in the situation where if we want to clone a production database for testing something, we can convert existing...

Steps on converting a Physical Standby database to Snapshot Standby database: 1)   Check if Flashback is enabled or not. For this conversion is achieved using flashback database, but the physical standby database does not need to have flashback database explicitly enabled. Even if the flashback is not enabled explicitly the conversion will work. But Flash Recovery Area should have configured on the physical standby database. STDBY.SYS.SQL> set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000; set trimspool on time on timi on feedback on verify on echo off heading on; col db_unique_name for a15 select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b; show parameter db_recovery_file_dest 2) Stop Redo Apply, if it is active. $ dgmgrl sys/...