:

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:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
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

@kill_user_sessions.out

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 *