archiver stuck after set log_archive_dest_2 to fast recovery area oracle

Question

All data guard expert,

I am currently trying to configure a primary database for creating physical standby database with an oracle 11g stand alone database.

Oracle version: 11.2.0.4

It's on archivelog mode with automatic archiving using fast recovery area.

db_recovery_file_dest is set to /u01/app/oracle/oraarch

db_recovery_file_deset_size is set to 4G.

archiving is working normally before starting configure this database for physical standby database creation.

here is what's done so far on this primary database:

1. force logging on primary database is turned on:

   alter database force logging;

2. standby redo logs are created. While this primary database has two redo log file groups, so I created 3 standby redo log file as Oracle recommeneded.

SQL> SELECT * FROM V$STANDBY_LOG;

GROUP# DBID THREAD#  SEQUENCE#

---

BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM

---

NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME

---

4 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED

5 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED

6 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED

then

Set archive_log_dest_2 parameter to us DB_RECOVERY_AREA.

alter system set log_archive_dest_2 = 'location=user_db_recovery_file_dest valid_for=(standby_logfile,standby_role)' scope=both;

and turn log_archive_deset_state_2 be 'enable', while it's was set to enable as default.

alter system set log_archive_dest_state_2=enable scope=both;

Then the problem I am stuck begins. When I issue SQL> alter system switch logfile; 3 times, then it's hung up.

I check the /oraarch directory, the redo log file are not even archived at the first two switch logfile command.

and when the third time switch logfile is issued, then it's hung up.

Please help/advise while you expertise is really appreciated. thank you so much.

regards

Answer 1

So you told the primary database, that it should put archivelogs in the FRA, but only when it has the standby role. After forcing log switches a few times, your repeated attempt of forcing a log switch hung, because with your settings, you basically prevented the database from archiving logs, and the database can not switch to a new log file group, because it could not archive any other previous log sequences, so it can not reuse the files.

Here is an example of the parameters needed in the primary database from the documentation:

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_DEST_1 is a local destination that can archive any log in any role.

LOG_ARCHIVE_DEST_2 is a remote destination that points to the standby.

You should have something like this:

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'

Also, here is the whole process of Creating a Physical Standby.