Data Guard Physical Standby - After conversion to a Snapshot Standby the Snapshot Standby experiences problems archiving its own online redo logs . (文件 ID 1545846.1)

In this Document

Goal

Solution

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Goal

To convert a Physical Standby database to a Snapshot Standby database and configure a Snapshot Standby database archive destination that will allow the Snapshot Standby to generate its own archive logs while it is running in READ WRITE mode.

The Current Broker Configuration

$ dgmgrl

DGMGRL> connect /
Connected.

DGMGRL> show configuration;

Configuration - rs1_dg

Protection Mode: MaxAvailability
Databases:
rs1_stb - Primary database
rs1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The Conversion to snapshot standby is performed and succeeds.

DGMGRL> convert database rs1 to snapshot standby;
Converting database "rs1" to a Snapshot Standby database, please wait...
Database "rs1" converted successfully

DGMGRL> edit configuration set protection mode as MaxPerformance;
Succeeded.

DGMGRL> show configuration;

Configuration - rs1_dg

Protection Mode: MaxPerformance
Databases:
rs1_stb - Primary database
rs1 - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Alert Log Extract Detailing the conversion

Managed Recovery is cancelled by the broker and media recovery is completed with a reset logs operation.

Tue Apr 16 11:55:38 2013
ALTER SYSTEM SET db_recovery_file_dest_size='6G' SCOPE=BOTH;
Tue Apr 16 12:05:41 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Tue Apr 16 12:05:41 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/rs1/rs1/trace/rs1_pr00_4009.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 3294480
Tue Apr 16 12:05:42 2013
MRP0: Background Media Recovery process shutdown (rs1)
Managed Standby Recovery Canceled (rs1)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_04/16/2013 12:05:42
krsv_proc_kill: Killing 4 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 3294480
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1826002576 (0x6cd69690)
Online log +DATA/rs1/onlinelog/group_1.261.800115647: Thread 1 Group 1 was previously cleared
Online log +FRA/rs1/onlinelog/group_1.257.800115649: Thread 1 Group 1 was previously cleared
Online log +DATA/rs1/onlinelog/group_2.262.800115651: Thread 1 Group 2 was previously cleared
Online log +FRA/rs1/onlinelog/group_2.258.800115653: Thread 1 Group 2 was previously cleared
Online log +DATA/rs1/onlinelog/group_3.263.800115655: Thread 1 Group 3 was previously cleared
Online log +FRA/rs1/onlinelog/group_3.259.800115655: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3294478

The alert log shows the conversion to a snapshot standby succeeds.

Tue Apr 16 11:55:38 2013
ALTER SYSTEM SET db_recovery_file_dest_size='6G' SCOPE=BOTH;
Tue Apr 16 12:05:41 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Tue Apr 16 12:05:41 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/rs1/rs1/trace/rs1_pr00_4009.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 3294480
Tue Apr 16 12:05:42 2013
MRP0: Background Media Recovery process shutdown (rs1)
Managed Standby Recovery Canceled (rs1)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_04/16/2013 12:05:42
krsv_proc_kill: Killing 4 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 3294480
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1826002576 (0x6cd69690)
Online log +DATA/rs1/onlinelog/group_1.261.800115647: Thread 1 Group 1 was previously cleared
Online log +FRA/rs1/onlinelog/group_1.257.800115649: Thread 1 Group 1 was previously cleared
Online log +DATA/rs1/onlinelog/group_2.262.800115651: Thread 1 Group 2 was previously cleared
Online log +FRA/rs1/onlinelog/group_2.258.800115653: Thread 1 Group 2 was previously cleared
Online log +DATA/rs1/onlinelog/group_3.263.800115655: Thread 1 Group 3 was previously cleared
Online log +FRA/rs1/onlinelog/group_3.259.800115655: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3294478

The Problem

After the conversion to snapshot standby the snapshot standby which has now been opened READ WRITE has no archive destinations available for it to archive its online redo logs while it is running as a snapshot standby database.

The Snapshot Standby is opened READ WRITE

Completed: ALTER DATABASE OPEN
Tue Apr 16 12:05:59 2013
RFS[4]: Assigned to RFS process 4235
RFS[4]: Selected log 4 for thread 1 sequence 286 dbid 1823795963 branch 800115647
Tue Apr 16 12:06:03 2013

After the open and a few log switches the Snapshot Standby begins to report errors associated with the archival of logs from this newly formed incarnation of the standby site.

Tue Apr 16 13:35:25 2013
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: +DATA/rs1/onlinelog/group_3.263.800115655
Current log# 3 seq# 3 mem# 1: +FRA/rs1/onlinelog/group_3.259.800115655
Tue Apr 16 13:36:01 2013
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance rs1 - Archival Error
ORA-16014: log 1 sequence# 1 not archived, no available destinations
ORA-00312: online log 1 thread 1: '+DATA/rs1/onlinelog/group_1.261.800115647'
ORA-00312: online log 1 thread 1: '+FRA/rs1/onlinelog/group_1.257.800115649'

Solution

The errors can be corrected by enabling a destination that has the following properties for its valid_for clause, ONLINE_LOGFILE and STANDBY_ROLE.

SQL> alter system set log_archive_dest_4='location="+DATA", valid_for=(ONLINE_LOGFILE,STANDBY_ROLE)';

The Alert Log in the Snapshot Standby will now show archiving occurring

Tue Apr 16 14:15:56 2013
ALTER SYSTEM SET log_archive_dest_4='location="+DATA", valid_for=(ONLINE_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH;
Tue Apr 16 14:16:04 2013
Archived Log entry 519 added for thread 1 sequence 1 ID 0x6d78154b dest 4:
krse_arc_driver_core: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Tue Apr 16 14:16:14 2013
Archived Log entry 520 added for thread 1 sequence 2 ID 0x6d78154b dest 4: