Tuesday, February 17, 2009

Changing Data Guard Protection level

We are in process of testing Data Guard setup. While doing that I come across a situation where I have to change Protection status from status from Maximum Performance to Maximum Availability:
On Primary:
alter system set log_archive_dest_2='SERVICE=Instance_Name LGWR SYNC AFFIRM valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SID;
alter system set log_archive_dest_state_2=enable;


Shutdown and mount the primary and standby database:
SQL> -- [Connect to the primary database]
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

SQL> -- [Connect to the standby database]
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

On Primary
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

On Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On Primary
ALTER DATABASE OPEN;

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;

Happy Troubleshooting !!!

1 comment:

Don Parret said...

For an easy to use graphical alternative to Data Guard,
try the Standby Wizard for Oracle. The Standby Wizard automates
standby database creation, maintenance, and switchover functions needed for
any Oracle disaster-recovery or continuity planning senerio.

Free downloads are available at:

www.relationalwizards.com