Header Ads Widget

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

First i have switched over primary database to Standby database and then started database in mount stage for standby database.

I was trying to put standby database in recovery mode. it threw below errors.

Error:

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


Solutions:

this means that there are still many sessions connected in database which preventing the database from being put back to recovery mode. run below dynamic sql query to find out which user sessions are still connected.

SQL>spool kill_session.sql

SQL>select 'alter system kill session '||''''||sid||','||serial#||',@'||inst_id||''''||' IMMEDIATE;' from gv$session where username not in ('SYS','PUBLIC');

SQL>spool off;

SQL>@kill_session.sql


Now, try to put database in recovery mode. it should be all set now.