View Full Version : ORA-01195 and ORA-01194 on Standby Database

Mike Donovan
06-23-2011, 12:15 PM

One of our customers attempted to activate the standby database in a test scenario and received the following message:

SQL> alter database activate standby database
ERROR at line 1:
ORA-01195: online backup of file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/oradata/blldb/undotbs01.dbf'

The error ORA-01195: online backup of file 2 needs more recovery to be consistent indicates that a tablespace is still in hot-backup mode on the primary database while trying to activate the standby database.

This is in fact the same situation as with a normal hot backup itself you need all archivelogs till end of the backup to have a useful backup.
When using a standby database, it effectively means that the standby database may not be consistent during the hot backup period of the primary database.

Short term solutions:

Take the primary database out of backup mode and run Dbvisit Standby once more on primary and standby OR:
Try initiating a cancel-based recovery on the standby, and immediately canceling it this usually works to open resetlogs the database (this updates the file headers with correct SCNs):
SQL> recover standby database until cancel

Long term solution:

Look at using RMAN in your backup. RMAN does not put the tablespace into hotbackup mode.

Other scenarios:

The error "ORA-01194: file x needs more recovery to be consistent" which is closely related to ORA-01195 can occur during the following 2 scenarios:

a. Straight after standby database creation - where not all the archive logs have been applied that were created during the creation of the standby database. The solution is to apply all the archive log files.

b. If the standby controlfile was created after the backup of the primary database was made - in this case the solution is to re-create the standby database using a standby controlfile that was created before the backup of the primary database was made.