Monday, June 29, 2009

Steps to shutdown Dataguard/Standby database for maintenance work

In our office there are certain situations where we have to shutdown Dataguard site, mostly for maintenance work, such as shared disk, server's hardware or any general maintenance at our DR (Disaster Recovery) site.

Point behind shutdown/startup of Dataguard is when Dataguard/Standby database come online it should get synchronized to Production/Primary site.

There may be many ways to achieve our goal, here is my recipe:

SHUTDOWN STANDBY DATABASE

step 1: Disable standby archive writing:
at primary/production database

SQL> alter system set log_archive_dest_state_2=defer scope = both sid = '*';

PS: in case of RAC: this step should be done at just ONE of the RAC node.

step 2: Disable auto recovery at standby site:
at secondary/DR database

SQL> alter database recover managed standby database cancel;
PS: In case of RAC: above command should run on just ONE of the standby RAC node.

SQL> shutdown immediate
PS: in case of RAC: shutdown should be done on ALL standby RAC nodes.

STARTUP STANDBY DATABASE

Step 1: Coping all archive from primary to DR generated during shutdown.

in this step we will copy all archive those were generated after standby database was taken down.

$scp /opt/oracle/archive/*.arc oracle@DR-Site-IP:/opt/oracle/archive

Step 2:
Startup standby database and configure automatic recovery:

SQL> startup nomount

SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

PS: in case of RAC: repeat following commands on all standby nodes.

SQL> startup nomount
SQL> alter database mount standby database;

step 3: Enable standby archive writing:
at primary/production database

SQL> alter system set log_archive_dest_state_2=enable scope = both sid = '*';
PS: in case of RAC: this step should be done at just ONE of the RAC node.

cheers

10 comments:

SolJanuzi said...

Nayyar
I really enjoyed your blog. I have a question about oracle standby and I wish you would give me a hint:
I created a standby database from a production database on a remote server to do some tests. Everything worked perfectly. What do I need to remove the standby database? I do not want the logs are transferred to the local LOG_ARCHIVE_DEST_2. I no longer need the standby database.
Thanks for advance.
Solange Januzi

nayyares said...

Hi Soljanuzi,

thanks for reading me :)

regarding your question, once you disable "LOG_ARCHIVE_DEST_2" your standby database is no logger attached to primary database, you can then just use standard procedure to remove your remote test database.

you can use "alter system set log_archive_dest_state_2=defer scope = both sid = '*';" command to disable standby at primary database.

cheers

abdul said...

Hi ahmed
iam very much thanks for great collection , i have two node rac 11gr2 , for maintainence we need to down the standby , i followed ur steps here but node1 was mounted successfull , i have faced ORA-01153 while mounting node2 in MRM please help me

Regards
Abdul

abdul said...

SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

nayyares said...

Abdul, try to disabled and recreated Block Change Tracking - on the Standby.

thanks

LC said...

I'd add one step:

oracle@standby> lsnrctl start

Unknown said...

hi,

Transport off in primary db
and apply off in standby db are not required?

Unknown said...

In the process of removing the standby database, we can have LOG_ARCHIVE_DEST_2 to defer and but what about the standby redo logs? are there any other things to consider for removing the standby completely.

Zeeshan said...

As I see this forum is little active.. I have doubt if someone can help me out ...

suppose I have a standby db with 5 nodes and I want to separate one node of my standby cluster and give it to unix team for some their patch activity... I want to make sure the recovery must continue with the remaining 4 nodes for time being until the 5th node is back in action with the team of other 4 nodes... I dont want to take off the node from cluster by giving cluster_db=false... Is there a way I can do this in RAC standby.. Is there a way I switch off the recovery only on 1 node without others getting affected and joining it back...

-Zeeshan

Sudhakar Malineni said...

Thank you, well explanation. very help full steps.