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.