Friday, August 15, 2008

Switch from Physical Dataguard to Logical Dataguard

# pre-checks

SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);
SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

# on Primary A
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=b';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=a LGWR ASYNC REOPEN=60 MAX_FAILURE=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=a';
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/bie_oraexp/B1/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=b';

# on standby B
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=a';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=b LGWR ASYNC REOPEN=60 MAX_FAILURE=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=b';
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/bie_oraexp/A1/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=a';

# Cancel recovery on physical standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

# on primary
EXECUTE DBMS_LOGSTDBY.BUILD;

# on standby database recover and set the new name.
ALTER DATABASE RECOVER TO LOGICAL STANDBY b;

# create the password file for the new database name.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypassword entries=5

# open the new database
STARTUP MOUNT force;
ALTER DATABASE OPEN RESETLOGS;

# start the sql apply process on the logical standby
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
--ALTER DATABASE STOP LOGICAL STANDBY APPLY;

# To switchover
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;