Managing an Oracle Physical Standby Database

This is an example about how to open a physical standby database in read-only mode:

SQL> select name, open_mode, database_role, switchover_status from v$database;

NAME      OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS
--------- ---------- ---------------- --------------------
MYDB0001  MOUNTED    PHYSICAL STANDBY SESSIONS ACTIVE

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name, open_mode, database_role, switchover_status from v$database;

NAME      OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS
--------- ---------- ---------------- --------------------
MYDB0001  READ ONLY  PHYSICAL STANDBY NOT ALLOWED

SQL> select instance_name, logins from v$instance;

INSTANCE_NAME    LOGINS
---------------- ----------
mydbr001         ALLOWED

And this is an example about restoring redo apply in the standby database:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
mydbr002

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  805306368 bytes
Fixed Size                  2087520 bytes
Variable Size             205522336 bytes
Database Buffers          591396864 bytes
Redo Buffers                6299648 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select name, applied, archived from v$archived_log where applied='NO';

no rows selected

SQL> select open_mode, database_role, switchover_status from v$database;

OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED    PHYSICAL STANDBY SESSIONS ACTIVE

SQL> select instance_name, logins from v$instance;

INSTANCE_NAME    LOGINS
---------------- ----------
mydbr002         ALLOWED

More information about managing physical standby databases:

Managing a Physical Standby Database