How to set PHYSICAL STANDBY to "READ ONLY WITH APPLY" mode

hawre picture hawre · Oct 5, 2017 · Viewed 20.3k times · Source

I have standby database(version : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0) in "MOUNTED" mode.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

when i want to change OPEN_MODE from "MOUNTED" to "READ ONLY WITH APPLY" mode, the error(ORA-01093) will be occurred.

SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2.7793E+10 bytes
Fixed Size                  2189008 bytes
Variable Size            1.1207E+10 bytes
Database Buffers         1.6576E+10 bytes
Redo Buffers                7385088 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

how to fix the error, in other words how to kill other sessions?

Answer

RGO picture RGO · Jan 17, 2018

The correct syntax is as shown below:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;