ORA-01195 and ORA-01194 in standby database
One of our customers attempted to activate the standby database in a test scenario and received the error message below. Find out what the short and long-term solutions are, along with other scenarios that can cause the same error.
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 archive logs till the 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 back 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
cancel
exit
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:
- 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.
- 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.