The Issue:
After converting the Physical Standby database to a Snapshot Standby Database and opening it READ WRITE archiving of online redo logs for the READ WRITE Snapshot standby start to fail.
Archival errors in the Snapshot standby are reported in the alert log and the Snapshot standby hangs.
The Snapshot Standby is then shutdown and subsequent restarts of the Snapshot Standby then fail as the instance crashes at each start up attempt.
Issue Verification:
The Physical Standby is converted to a Snapshot Standby.
Mon Feb 10 07:00:21 2014
ARC3 started with pid=22, OS id=22463
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
Completed: alter database mount standby database
alter database flashback on
Starting background process RVWR
Mon Feb 10 07:00:21 2014
RVWR started with pid=23, OS id=22467
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Allocated 15097664 bytes in shared pool for flashback generation buffer
Flashback Database Enabled at SCN 5972659050601
Completed: alter database flashback on
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_02/10/2014 07:00:25
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 5972659050601
Resetting resetlogs activation ID 222010168 (0xd3b9b38)
Online log /< local directory >/log01a.dbf: Thread 1 Group 1 was previously cleared
Online log /< local directory >/log01b.dbf: Thread 1 Group 1 was previously cleared
Online log /< local directory >/log02a.dbf: Thread 1 Group 2 was previously cleared
Online log /< local directory >/log02b.dbf: Thread 1 Group 2 was previously cleared
Standby became primary SCN: 5972659050599
Setting recovery target incarnation to 206
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Archiving in the Snapshot Standby starts to fail and eventually all online redo log groups are filled and not archived causing all activity in the snapshot standby to be suspended.
Mon Feb 10 09:50:35 2014
Beginning log switch checkpoint up to RBA [0x3.2.10], SCN: 5972659680925
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 1 seq# 3 mem# 0: /< local directory >/log01a.dbf
Current log# 1 seq# 3 mem# 1: /< local directory >/log01b.dbf
Mon Feb 10 09:50:35 2014
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance < dbname > - Archival Error
ORA-16014: log 2 sequence# 2 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/< local directory >/log02a.dbf'
ORA-00312: online log 2 thread 1: '/< local directory >/log02b.dbf'
..
.
Mon Feb 10 14:04:26 2014
ORACLE Instance < dbname > - Can not allocate log, archival required
Thread 1 cannot allocate new log, sequence 4
All online logs needed archiving
Current log# 1 seq# 3 mem# 0: /< local directory >/log01a.dbf
Current log# 1 seq# 3 mem# 1: /< local directory >/log01b.dbf
The Instance is shutdown in an attempt to overcome the problem.
Mon Feb 10 14:42:03 2014
Shutting down instance (immediate)
Subsequent startups of the Snapshot Standby fail reporting.
Mon Feb 10 14:46:20 2014
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Archiver process freed from errors. No longer stopped
parallel recovery started with 8 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 3, block 2022102, scn 5972659918981
Recovery of Online Redo Log: Thread 1 Group 1 Seq 3 Reading mem 0
Mem# 0: /< local directory >/log01a.dbf
Mem# 1: /< local directory >/log01b.dbf
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 3, block 2022102, scn 5972659938982
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Errors in file /< adr dir >/trace/< dbname >_ora_1317.trc:
ORA-16014: log 2 sequence# 2 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/< local directory >/log02a.dbf'
ORA-00312: online log 2 thread 1: '/< local directory >/log02b.dbf'
USER (ospid: 1317): terminating the instance due to error 16014
System state dump requested by (instance=1, osid=1317), summary=[abnormal instance termination].
System State dumped to trace file /< adr dir >/trace/< dbname >_diag_1163.trc
Dumping diagnostic data in directory=[cdmp_20140210144622], requested by (instance=1, osid=1317), summary=[abnormal instance termination].
Instance terminated by USER, pid = 1317
The cause of this problem is the following:
A Snapshot Standby database is going to require a minimum of 2 archive destinations
- A destination for the archive logs and redo stream it is going to receive from the Primary Site (or in this case the cascading standby site).
- A destination for the archive logs it is now going to generate as a Snapshot Standby running in READ WRITE mode.
The parameters configured in the < dbname > database are only allowing archiving of redo that is generated from the Primary Site and is written into standby redo log groups.
The first destination in < dbname >
log_archive_dest_1 = "LOCATION=< directory >/< dbname >/archive VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=< dbname >"
The Valid for clause here is dictating the conditions under which this standby can write out archives for destination 1. That is the database role is standby (which is the case here) and via standby redo logs. This means only that redo stream coming from the Primary is going to be written out via this destination.
The second destination in < dbname >
log_archive_dest_2 = "LOCATION=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_state_2 = "DEFER"
The second destination has been deferred resulting in any redo generated by the Snapshot Standby itself failing to be archived.
It is this that has ultimately resulted in all online redo log groups in the snapshot standby filling and not being archived successfully which has in turn caused the and failures in restarting the Snapshot Standby database.
How to Correct this problem
Either
1. Set the valid_for clause in log_archive_dest_1 to allow archiving of all types of redo logs, both online redo log groups and standby redo log groups.
For Example:
log_archive_dest_1 = "LOCATION=< directory >/< dbname >/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=< dbname >"
OR the better solution
2. Enable a log_archive_dest_X parameter that allows archiving of any redo generated locally by the Snapshot Standby database. This would see two separate destinations available for archiving and separation of the two distinct redo streams.
For Example:
---专用
log_archive_dest_1 = "LOCATION=< directory >/< dbname >/archive/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=< dbname >"
log_archive_dest_2 = "LOCATION=< directory >/< dbname >/archive/ VALID_FOR=(ONLINE_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=< dbname >"
log_archive_dest_state_2 = "ENABLE"
SYS@orapoc1> show parameters log_arch
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(orapoc,orapoc_stby)
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) MAX_FAILURE=1 REOPEN
=5 DB_UNIQUE_NAME=orapoc ALTER
NATE=LOG_ARCHIVE_DEST_3
log_archive_dest_2 string service="orapoc_stby", ASYNC N
OAFFIRM delay=0 optional compr
ession=disable max_failure=0 r
eopen=300 db_unique_name="orap
oc_stby" net_timeout=30, valid
_for=(online_logfile,all_roles
)
log_archive_dest_3 string LOCATION=+DATA01 VALID_FOR=(AL
L_LOGFILES,ALL_ROLES) DB_UNIQU
E_NAME=orapoc ALTERNATE=LOG_AR
CHIVE_DEST_1