Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Data Guard: Switch to MANAGED RECOVERY mode only after SHUTDOWN

    Hello world

    I've set up a physical standby solution (Oracle 9i on RH-AS3).
    The primary DB is fed during the night with some data and answers user queries during the day.
    The standby DB should be set in "MANAGED RECOVERY" mode for the night to apply the archived logs from the primary DB and turned back to "READ-ONLY OPEN" mode for the day to answer queries to take some load from the primary DB.
    I wrote two shell scripts which are called by cron to switch between the two modes. Everything works fine but...
    If only one session is connected during the attempt to start "MANAGED RECOVERY" mode, the script will fail with following 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
    ----------------------------------

    Therefore I choosed to shutdown the instance (physical standby DB) and start it up again, to be sure no sessions are connected when I start "MANAGED RECOVERY" mode:

    ----------------------------------
    sqlplus /nolog <<EOF
    connect sys/xxx as sysdba
    set echo on
    set termout on
    SHUTDOWN IMMEDIATE;
    EOF

    sqlplus /nolog <<EOF
    connect sys/xxx as sysdba
    set echo on
    set termout on
    STARTUP NOMOUNT;
    ALTER DATABASE MOUNT STANDBY DATABASE;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
    EOF
    ----------------------------------

    This works fine but I don't like the shutdown. Is there any possibility to first disconnect all "unwanted" sessions before switching to "MANAGED RECOVERY" mode?
    I tried with a sql script, which uses a cursor on the V$SESSION view to query all user sessions (except my own [;-)]) and using "UTL_FILE.PUT_LINE" to create a second script consisting only of:

    ----------------------------------
    alter system disconnect session 'x1, y1';
    alter system disconnect session 'x2, y2';
    ...
    ----------------------------------


    The first sql script fails with:
    ORA-16000: database open for read-only access

    It seems I cannot declare any cursor on a read-only DB.

    Does anybody has any hint, how I can disconnect the unwanted users and switch to "MANAGED RECOVERY" mode without shutdown?

    Thanks in advance

    S@M

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You have to shutdown the STANDBY DB (PHYSICAL) to bring it back to MANAGED RECOVERY mode from READ ONLY mode.

    But, from the scenerio you have mentioned, I think LOGICAL STANDBY DB is more favourable than PHYSICAL one.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    Hi Hings

    Thank you for your reply.

    > You have to shutdown the STANDBY DB (PHYSICAL)
    > to bring it back to MANAGED RECOVERY mode from
    > READ ONLY mode.

    I don't think so.
    If no users are connected I can simply do:

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

    whithout restarting.
    Oracle DataGuard-Admin handbook reads:

    ------------------------------
    To change the standby database from being open for read-only access
    to performing managed recovery:
    1. Terminate all active user sessions on the standby database.
    2. Restart log apply services:
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
    2> DISCONNECT FROM SESSION;
    ------------------------------

    So it should not be necessary to shutdown and restart.

    My question is how can I disconnect the users without shutdown?

    > But, from the scenerio you have mentioned, I think
    > LOGICAL STANDBY DB is more favourable than PHYSICAL
    > one.

    We keep the standby DB also as backup solution, i.e. for switchover in case of crash of the primary instance, so PHYSICAL STANDBY is what we need.

    > HTH

    Thanx

    S@M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •