In my last post about ORA-16792, I said I didn’t find anything wrong and the issue was gone after I disabled/enabled the configuration. Actually there was a problem and I ran into it after I opened the standby database and did some queries. I shutdown the database and could not start it:

14:50:08 SQL> startup mount;
ORA-16033: parameter LOG_ARCHIVE_DEST_2 destination cannot be the same as parameter LOG_ARCHIVE_DEST_1 destination

Looking at the two parameters:

log_archive_dest_1     string      location="/ora_archivelog1/archivelog", valid_for=(ALL_LOGFILES,ALL_ROLES)

log_archive_dest_2     string      location="/ora_archivelog1/archivelog/", valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)

valid_for for log_archive_dest_1 already covers all roles and all log files. The value log_archive_dest_1 is set by the broker. So I need to clear the parameter log_archive_dest_2.

Fortunately without the database up (same error for nomount), we can still create pfile from spfile or vice versa.

SQL> create pfile from spfile;

File created.

The pfile is created under $ORACLE_HOME/dbs. Remove the line of log_archive_dest_2, then create spfile from it.

SQL> create spfile from pfile='/opt/oracle/121020/dbs/initAPROD.ora';

File created.

The problem has been solved.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 9.6637E+10 bytes
Fixed Size                  4510496 bytes
Variable Size            2.9528E+10 bytes
Database Buffers         6.6840E+10 bytes
Redo Buffers              263921664 bytes
Database mounted.

• Oracle