Data Guard Sessions

From my experience, it is common practice to open up a physical standby database for read only, reporting purposes. If you’re running the data guard set up in maximum performance mode, it makes sense to allow some use of the physical standby DB so as to reduce load on the primary database server.

You can see my other article on how to open and close a physical standby database for read only/reporting purposes. If you are doing so, you may come across this error:

ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

This means that there are sessions still connected to the database which is preventing the instance from being put back into recovery mode. You can run the following dynamic SQL in order to kill any user sessions. You should edit the not in part of the statement in order to exclude particular user sessions.

set lines 120
set pages 0
set heading off
set feedback off

spool kill_user_sessions.out

SELECT ‘alter system kill session ”’||sid||’,’||serial#||”’ immediate;’
FROM v$session where username not in (‘SYS’,’PUBLIC’);

spool off


You can find out more information about opening a physical standby database for read only access.

Like it, share it...

Category: Data Guard

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *