11g - Data Guard - Primary/Standby Windows Server reboot

1. Procedure for Primary Oracle server reboot

1. Create safe backups for Primary and Standby databases

C:\Users\oracle>set oracle_sid=<SID_NAME>

C:\Users\oracle> rman target /

RMAN> backup database current controlfile;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE PLUS ARCHIVELOG;

Check Oracle RMAN status:

SQL> select sid, start_time,(sofar/totalwork) * 100 done,

to_char(sysdate + time_remaining/3600/24,'dd-mm-yyyy hh24:mi')

from v$session_longops

where opname not like '%aggregate%' and opname like 'RMAN%' and totalwork > sofar;

2. Stop Oracle Enterprise Manager Console

C:\Users\oracle>set oracle_sid=<SID_NAME>

C:\Users\oracle>emctl status dbconsole

Oracle Enterprise Manager 11g is running.

C:\Users\oracle>emctl stop dbconsole

The OracleDBConsole<DB_NAME> service was stopped successfully.

3. Disable Primary and Standby scheduled backup tasks that might interfere with switchover and reboot activity

Set backup jobs for all databases to Disabled.

4. Switchover databases to Standby

Check gaps for online redo

• on primary :

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

• on standby :

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE APPLIED = 'YES'

AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = 'CURRENT')

GROUP BY THREAD#;

Check the difference (should not be bigger the 1 or 2 according to Oracle documentation)

Check no jobs are running (automatic jobs starting at 22:00 should be finished) :

SQL> SELECT * FROM DBA_JOBS_RUNNING;

Tail alert log files (optional)

• With Power Shell:

PS C:\Users\oracle> Get-Content E:\app\oracle\diag\rdbms\<db_name>\<SID>\trace\alert_<sid>.log -wait

Perform switchover

• Connect to the primary instance and perform the switchover

DGMGRL> CONNECT SYS/password@primary

DGMGRL> SHOW CONFIGURATION

DGMGRL> SWITCHOVER TO <standby database name>;

• Check the new configuration

DGMGRL> SHOW CONFIGURATION

• on the new primary  check on each instance if the services are running:

SQL> SELECT NAME FROM V$SERVICES; 

• on the new standby check on each instance if the services are NOT running:

SQL> SELECT NAME FROM V$SERVICES;

5. Prepare server for reboot

• Connect to observer server

C:\Users\oracle>dgmgrl /@<primary_db_name>

DGMGRL> show configuration;

Fast-Start Failover: ENABLED

DGMGRL> disable fast_start failover;

Disabled.

DGMGRL> show configuration;

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

• Checking New Primary/New standby:

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

SQL> select db_unique_name, database_role, open_mode from v$database;

• New primary:

SQL> show parameter log_archive_dest_state_2;

SQL> alter system set log_archive_dest_state_2=defer scope=both;

SQL> alter system switch logfile;

• New standby:

SQL> shutdown immediate

• Stop Oracle services on New standby

6. REBOOT New standby SERVER

7. Startup Oracle services and instances on New standby server reboot

• Start Oracle services on New standby

• Startup database in MOUNT state

C:\Users\oracle> set oracle_sid=<SID_NAME>

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

SQL> startup mount

SQL> select db_unique_name, database_role, open_mode from v$database;

• Check database and alert.log to see that log_archive_dest_state_2 is set automatically to ENABLE.

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string ENABLE

• Enable fast start failover

C:\Users\oracle> dgmgrl /@<db_name>

DGMGRL> show database verbose <primary database>;

DGMGRL> enable fast_start failover;

DGMGRL> show configuration verbose;

8. Switchover databases to Primary

Check gaps for online redo

• on New primary :

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

• on New standby :

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE APPLIED = 'YES'

AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = 'CURRENT')

GROUP BY THREAD#;

Check the difference (should not be bigger the 1 or 2 according to Oracle documentation)

Check no jobs are running (automatic jobs starting at 22:00 should be finished) :

SQL> SELECT * FROM DBA_JOBS_RUNNING;

Tail alert log files (optional)

• With Power Shell:

PS C:\Users\oracle> Get-Content E:\app\oracle\diag\rdbms\<db_name>\<SID>\trace\alert_<sid>.log -wait

Perform switchover

• Connect to the new primary instance and perform the switchover

DGMGRL> CONNECT SYS/password@New primary

DGMGRL> SHOW CONFIGURATION

DGMGRL> SWITCHOVER TO <Primary database name>;

• Check the new configuration

DGMGRL> SHOW CONFIGURATION

• on the primary check on each instance if the services are running:

SQL> SELECT NAME FROM V$SERVICES; 

• on the new standby check on each instance if the services are NOT running:

SQL> SELECT NAME FROM V$SERVICES;

9. Enable scheduled tasks

Enable Scheduled tasks

2. Procedure for STANDBY Oracle server reboot

1. Create safe backups for Primary and Standby databases

C:\Users\oracle>set oracle_sid=<SID_NAME>

C:\Users\oracle> rman target /

RMAN> backup database current controlfile;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE PLUS ARCHIVELOG;

Check Oracle RMAN status:

SQL> select sid, start_time,(sofar/totalwork) * 100 done,

to_char(sysdate + time_remaining/3600/24,'dd-mm-yyyy hh24:mi')

from v$session_longops

where opname not like '%aggregate%' and opname like 'RMAN%' and totalwork > sofar;

2. Disable DCO scheduled backup tasks that might interfere with reboot activity

Set backup jobs for all databases to Disabled.

3. Prepare server for reboot (stop databases and Oracle services)

• Connect to observer server

C:\Users\oracle>dgmgrl /@<primary_db_name>

DGMGRL> show configuration;

Fast-Start Failover: ENABLED

DGMGRL> disable fast_start failover;

Disabled.

DGMGRL> show configuration;

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

• Checking Primary/standby:

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

SQL> select db_unique_name, database_role, open_mode from v$database;

• primary:

SQL> show parameter log_archive_dest_state_2;

SQL> alter system set log_archive_dest_state_2=defer scope=both;

SQL> alter system switch logfile;

• standby:

SQL> shutdown immediate

• Stop Oracle services on standb

4. REBOOT Standby SERVER

5. Startup Oracle services and instances on Standby server after server reboot

• Start Oracle services on standby

• Startup databases in MOUNT state

C:\Users\oracle> set oracle_sid=<SID_NAME>

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

SQL> startup mount

SQL> select db_unique_name, database_role, open_mode from v$database;

• Check database and alert.log to see that log_archive_dest_state_2 is set automatically to ENABLE.

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string ENABLE

• Enable fast start failover

C:\Users\oracle> dgmgrl /@<db_name>

DGMGRL> show database verbose <primary database>;

DGMGRL> enable fast_start failover;

DGMGRL> show configuration verbose;

• Select the time of action

SQL> set linesize 200

SQL> select (select (to_char(sysdate,'dd - mm - yyyy hh24:mi:ss')) from dual) as TimeofExec, db_unique_name,

database_role, open_mode from v$database;

6. Enable scheduled tasks on Standby server

Enable Scheduled tasks