How to start & Stop 11gr2 Active Data Guard Standby Database?
This post is tested in Oracle 11.2.0.4 Active Data Guard instance. This step works on 11.1.0.7, 11.2.0.2, 11.2.0.4 as well. Let us assume, we have primary and standby database and we need to shutdown the standby database for maintenance schedule.
Step-1
Tail the alert log on both primary and standby for monitoring the database.
tail -f alert_$ORACLE_SID.log
Defer the log shipping in primary database.
Login to primary database and run the below command to defer the log shipping. Assuming, log_archive_dest_state_2 is standby location.
alter system set log_archive_dest_state_2='DEFER' scope=both;
Step-2
Login to standby database and shutdown the database.
alter database recover managed standby database cancel;
shutdown immediate
After the maintenance schedule is over, then bring the standby instance.
Step-3
Login to primary instance and enable the log shipping.
alter system set log_archive_dest_state_2='ENABLE' scope=both;
Step-4
Login to standby instance, run the below command.
sql> startup nomount;
sql> alter database mount standby database;
sql> alter database recover managed standby database disconnect from session;
Leave the standby instance for few min to catch up the logs and run the rest of the commands.
sql> alter database recover managed standby database cancel;
sql> alter database open;
sql> alter database recover managed standby database using current logfile disconnect;
Check the alert logs on the both primary and standby and make sure no errors.
****************************************************************************
This post is tested in Oracle 11.2.0.4 Active Data Guard instance. This step works on 11.1.0.7, 11.2.0.2, 11.2.0.4 as well. Let us assume, we have primary and standby database and we need to shutdown the standby database for maintenance schedule.
Step-1
Tail the alert log on both primary and standby for monitoring the database.
tail -f alert_$ORACLE_SID.log
Defer the log shipping in primary database.
Login to primary database and run the below command to defer the log shipping. Assuming, log_archive_dest_state_2 is standby location.
alter system set log_archive_dest_state_2='DEFER' scope=both;
Step-2
Login to standby database and shutdown the database.
alter database recover managed standby database cancel;
shutdown immediate
After the maintenance schedule is over, then bring the standby instance.
Step-3
Login to primary instance and enable the log shipping.
alter system set log_archive_dest_state_2='ENABLE' scope=both;
Step-4
Login to standby instance, run the below command.
sql> startup nomount;
sql> alter database mount standby database;
sql> alter database recover managed standby database disconnect from session;
Leave the standby instance for few min to catch up the logs and run the rest of the commands.
sql> alter database recover managed standby database cancel;
sql> alter database open;
sql> alter database recover managed standby database using current logfile disconnect;
Check the alert logs on the both primary and standby and make sure no errors.
****************************************************************************
Stop & start primary and standby databases 11gr2.
There are have a lot of ways to stop/start primary and standby databases. I will show you one of them.
We needed to migrate physically servers to new plaza. So, I had to stop all with clean.
Parameters:
Data guard broker was configured.
DGMGRL> show configuration
Configuration - DGMANAGER
Protection Mode: MaxPerformance
Databases:
FCDBPROD - Primary database
FCDBSTND - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
1. Stop applying. # I do it on Standby side
DGMGRL> edit database "FCDBSTND" set state="APPLY-OFF";
3. Shutdown Standby database
[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
DGMGRL>
Stop listener
lsnrctl stop
Shutdown OS
After unmount servers, migrate to new place and mount follow below:
Standby side
1. After start OS and listener, startup database
[oracle@fcdbdb_stby ~]$ export ORACLE_SID=FCDBSTND
[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> startup mount
ORACLE instance started.
Database mounted.
DGMGRL>
2. Start primary database
[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> startup
ORACLE instance started.
Database mounted.
Database opened.
DGMGRL>
3. Enable apply process
DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Error: ORA-12543: TNS:destination host unreachable
Error: ORA-16625: cannot reach database "FCDBPROD"
Failed.
DGMGRL>
There is error. Let check tnsping. There is connection problem. In my situation, after OS restart firewall was enabled. Stop it
/etc/init.d/iptables status #check status
/etc/init.d/iptables stop
If you want to turn off firewall on boot do:
chkconfig iptables off
Try to enable again
DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Succeeded.
DGMGRL> show configuration
Configuration - DGMANAGER
Protection Mode: MaxPerformance
Databases:
FCDBPROD - Primary database
FCDBSTND - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database "FCDBSTND";
Database - FCDBSTND
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
FCDBSTND
Database Status:
SUCCESS
DGMGRL> show database "FCDBPROD";
Database - FCDBPROD
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
FCDBPROD
Database Status:
SUCCESS
Now check archive logs.
On primary:
[oracle@fcdbdb ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 22 15:04:15 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
722
SQL>
On Standby side
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
722
now on primary switch logfile
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
724
SQL>
check on standby
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
722
SQL> /
MAX(SEQUENCE#)
--------------
724
SQL>
At the end check alertlogs on both side
Check alertlog
stnaby alert
Thread 1 advanced to log sequence 725 (LGWR switch)
Current log# 2 seq# 725 mem# 0: /u01/app/oracle/oradata/FCDBPROD/redo02.log
Sat Feb 22 15:05:42 2014
Archived Log entry 963 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 725 for destination LOG_ARCHIVE_DEST_2
primary alert
Sat Feb 22 15:05:45 2014
Archived Log entry 461 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
Media Recovery Waiting for thread 1 sequence 725 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 725 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/FCDBSTND/onlinelog/o1_mf_5_9f753t2h_.log
We needed to migrate physically servers to new plaza. So, I had to stop all with clean.
Parameters:
| Primary | Stanby | |
| db_name | FCDBPROD | FCDBPROD |
| instance_name | FCDBPROD | FCDBSTND |
| open_mode | READ WRITE | MOUNTED |
| database_role | PRIMARY | PHYSICAL STANDBY |
| ip | 10.10.10.10 | 10.10.10.20 |
Data guard broker was configured.
DGMGRL> show configuration
Configuration - DGMANAGER
Protection Mode: MaxPerformance
Databases:
FCDBPROD - Primary database
FCDBSTND - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
1. Stop applying. # I do it on Standby side
DGMGRL> edit database "FCDBSTND" set state="APPLY-OFF";
2. Shutdown Primary database
[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL>
Stop listener
lsnrctl stop
Shutdown OS
[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
DGMGRL>
Stop listener
lsnrctl stop
After unmount servers, migrate to new place and mount follow below:
Standby side
1. After start OS and listener, startup database
[oracle@fcdbdb_stby ~]$ export ORACLE_SID=FCDBSTND
[oracle@fcdbdb_stby ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> startup mount
ORACLE instance started.
Database mounted.
DGMGRL>
2. Start primary database
[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> startup
ORACLE instance started.
Database mounted.
Database opened.
DGMGRL>
3. Enable apply process
DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Error: ORA-12543: TNS:destination host unreachable
Error: ORA-16625: cannot reach database "FCDBPROD"
Failed.
DGMGRL>
There is error. Let check tnsping. There is connection problem. In my situation, after OS restart firewall was enabled. Stop it
/etc/init.d/iptables status #check status
/etc/init.d/iptables stop
If you want to turn off firewall on boot do:
chkconfig iptables off
Try to enable again
DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Succeeded.
DGMGRL> show configuration
Configuration - DGMANAGER
Protection Mode: MaxPerformance
Databases:
FCDBPROD - Primary database
FCDBSTND - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database "FCDBSTND";
Database - FCDBSTND
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
FCDBSTND
Database Status:
SUCCESS
DGMGRL> show database "FCDBPROD";
Database - FCDBPROD
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
FCDBPROD
Database Status:
SUCCESS
Now check archive logs.
On primary:
[oracle@fcdbdb ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 22 15:04:15 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
722
SQL>
On Standby side
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
722
now on primary switch logfile
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
724
SQL>
check on standby
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
722
SQL> /
MAX(SEQUENCE#)
--------------
724
SQL>
At the end check alertlogs on both side
Check alertlog
stnaby alert
Thread 1 advanced to log sequence 725 (LGWR switch)
Current log# 2 seq# 725 mem# 0: /u01/app/oracle/oradata/FCDBPROD/redo02.log
Sat Feb 22 15:05:42 2014
Archived Log entry 963 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 725 for destination LOG_ARCHIVE_DEST_2
primary alert
Sat Feb 22 15:05:45 2014
Archived Log entry 461 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
Media Recovery Waiting for thread 1 sequence 725 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 725 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/FCDBSTND/onlinelog/o1_mf_5_9f753t2h_.log
No comments:
Post a Comment