CHECKLIST BEFORE SWITCHOVER OPERATION

CHECKLIST BEFORE SWITCHOVER OPERATION
=======================================
Please note

log_archive_dest_state_3 :(archive dest for standby database)
log_archive_dest_state_1 :(archive dest for primary database)

SELECT PROTECTION_MODE FROM V$DATABASE;
SQL> select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_state_1','log_archive_dest_3','log_archive_dest_state_3);

Make sure 'lgwr' and not 'arch' is mentioned here, otherwise new primary database will not open after switchover (if the protection_mode is Maximum availability).

-- on standby database--
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

-- on primary --
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL>select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_state_1', 'log_archive_dest_3','log_archive_dest_state_3);
log_archive_dest_state_3
------------------
DEFER          --->DO NOT PROCEED.
issue this command : sql> alter system set log_archive_dest_state_3='ENABLE' scope=both;


---- on primary ----
SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

If any file(s) are in RECOVER status, DO NOT PROCEED with the switchover


-- on standby database--
SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
MOUNTED            --->PROCEED with switchover.



SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
READ ONLY       --->DO NOT PROCEED with switchover.

To confirm that logs are being shipped and more importantly the LAST LOG archived has been applied…

---- on primary ----
SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
3988

-- on standby database--
SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
3988

---> PROCEED

ORA-16608 | Oracle Dataguard

DGMGRL> show configuration

Configuration
Name: mhd_dr
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orc1 - Primary database
lsd - Physical standby database
drs - Physical standby database

Current status for "mhd_dr":
Warning: ORA-16608: one or more databases have warnings


Now we have to track which Standby Database is generating errors


DGMGRL> show database verbose 'drs';

Database
Name: drs
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
drs

Properties:
InitialConnectIdentifier = 'drs'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/orc1, +DATA/drs, +FLASH/orc1, +FLASH/drs, +IDX/orc1, +IDX/drs, +UNDO/orc1, +UNDO/drs'
LogFileNameConvert = '+DATA/orc1, +DATA/drs, +FLASH/orc1, +FLASH/drs, +IDX/orc1, +IDX/drs, +UNDO/orc1, +UNDO/drs'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'DBDRC'
SidName = 'drs'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=DBDRC)(PORT=
1521))'
StandbyArchiveLocation = 'E:\ARCHIVE'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "drs":
SUCCESS



DGMGRL> show database verbose 'lsd';

Database
Name: lsd
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
lsd

Properties:
InitialConnectIdentifier = 'lsd'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/orc1, +DATA/lsd, +FLASH/orc1, +FLASH/lsd, +IDX/orc1, +IDX/lsd, +UNDO/orc1, +UNDO/lsd'
LogFileNameConvert = '+DATA/orc1, +DATA/lsd, +FLASH/orc1, +FLASH/lsd, +IDX/orc1, +IDX/lsd, +UNDO/orc1, +UNDO/lsd'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'LOCALSTANDBY'
SidName = 'lsd'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=LOCALSTANDBY
)(PORT=1521))'
StandbyArchiveLocation = 'E:\ARCHIVE'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "lsd":
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property



So now we find that LSD has problems. So we review “drcLSD.log “ file to know the exact problem.


============ error message from drclsd.log=========================

DG 2011-01-01-11:20:30 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16826: apply service state is inconsistent with the DelayMins property
DG 2011-01-01-11:20:30 0 2 738705582 Operation CTL_GET_STATUS continuing with warning, status = ORA-16826
DG 2011-01-01-11:20:30 0 2 738705582 Operation CTL_GET_STATUS continuing with warning, status = ORA-16826
DG 2011-01-01-11:21:30 0 2 0 RSM: Redo Apply is running without USING CURRENT LOGFILE option while DelayMins=0
DG 2011-01-01-11:21:30 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16826: apply service state is inconsistent with the DelayMins property
DG 2011-01-01-11:21:30 0 2 738705585 Operation CTL_GET_STATUS continuing with warning, status = ORA-16826
DG 2011-01-01-11:21:30 0 2 738705585 Operation CTL_GET_STATUS continuing with warning, status = ORA-16826
============ error message from drclsd.log=========================


What does this message mean ?

DG 2011-01-01-11:21:30 0 2 0 RSM: Redo Apply is running without USING CURRENT LOGFILE option while DelayMins=0
DG 2011-01-01-11:21:30 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16826: apply service state is inconsistent with the DelayMins property

That means broker property DelayMins is set to 0, but recover managed standby database didn't start as real apply service. So DelayMins=0 cannot be achieved.


Solution :
Restart the physical standby with this command

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT FROM SESSION;

Database altered.



DGMGRL> show configuration

Configuration
Name: mhd_dr
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orc1 - Primary database
lsd - Physical standby database
drs - Physical standby database

Current status for "mhd_dr":
SUCCESS







DGMGRL> show database verbose 'lsd';

Database
Name: lsd
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
lsd

Properties:
InitialConnectIdentifier = 'lsd'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/orc1, +DATA/lsd, +FLASH/orc1, +FLASH/lsd, +IDX/orc1, +IDX/lsd, +UNDO/orc1, +UNDO/lsd'
LogFileNameConvert = '+DATA/orc1, +DATA/lsd, +FLASH/orc1, +FLASH/lsd, +IDX/orc1, +IDX/lsd, +UNDO/orc1, +UNDO/lsd'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'LOCALSTANDBY'
SidName = 'lsd'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=LOCALSTANDBY
)(PORT=1521))'
StandbyArchiveLocation = 'E:\ARCHIVE'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "lsd":
SUCCESS