ORA-01186, ORA-01122, ORA-01110, ORA-01209 when opening standby as Read Only

[Anton]

Recently a site experienced ORA-1209 errors when opening the standby database read-only. A call was logged with Oracle Support and a summary of the solution is provided below:


When you open a standby database as read only, you notice the following in the database alert log.

< ORA-01186: file 5 failed verification tests
< ORA-01122: database file 5 failed verification check
< ORA-01110: data file 5: '/u02/oracle/oradata/prod/data01.dbf'
< ORA-01209: data file is from before the last RESETLOGS
< File 5 not verified due to error ORA-01122
< ORA-16004 signalled during: alter database open read only...

After investigation the following was noted:

• File belong to a read-only tablespace called "DATA"
• The database was recovered in the past and opened with resetlogs; both primary and standby show the same resetlogs_time in v$database
• The datafile checkpoint_time in v$datafile_header show that it is before the v$database.resetlogs_time

To resolve the ORA-01209 to allow you to open the standby database read-only, follow these steps:

On the Primary Database:

• Place the DATA tablespace in "Read Write" mode.
• Take the datafile 5 offline
• Recover datafile 5
• Place tablespace DATA back in "Read Only" mode
• Switch the current redo log and issue a checkpoint
• Run dbvisit standby on both primary and standby

Example Steps on Primary Database:

sql> alter tablespace DATA read write;
sql> alter database datafile 5 offline;
sql> recover datafile 5;
sql> alter tablesapce DATA read only;
sql> alter system archive log current;
sql> alter system checkpoint;


Now run dbvisit on the Primary then Standby database.

You should now be able to open the standby database read only.