How to Resolve ORA-01196 and ORA-01110

ORA-01196

Saw errors ORA-01196 thrown by the database when trying to activate a standby database.

SQL> alter database activate standby database;
alter database activate standby database
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+data/system.243.34698032'

Generally, if we'd like to open the standby database for testing purpose temporarily, we may convert it as a snapshot database and convert it back to the standby role after testing. However, we can also make the standby database open as read-write permanently.

Rationale

The statement above intends to open the database as a primary role no matter what current status of recovery. Which implies the following archivelogs can be abandoned or forgotten from this moment. It's usually for building testing environments and no easy ways to turn back. The only requisite to open as a primary is that the applied archivelogs must make the database consistent.

So I think the recovering standby database might have been interrupted by some incidents internally or externally.

Solutions

Sometimes, there may be some unaware archivelog gaps needed to be resolved before you active the standby database. You have to either resolve the gaps or make the database consistent before doing activation.

Since it's only for testing purpose, there's no serious reason to resolve the gap, so we choose the second approach.

1. Cancel the apply service.
SQL> alter database recover managed standby database cancel;
2. Manually recover the standby database until you think it's far enough to open, then enter CANCEL.
SQL> recover standby database until cancel;
...
CANCEL
3. Now we can try to activate the standby database.
SQL> alter database activate standby database;

Database altered.
4. Bounce the database
SQL> shutdown immediate;
SQL> startup;

Done.