Scripts to start the Dataguard after system reboot in Windows

16 Replies

Scripts to start the Dataguard after system reboot in Windows

Scripts to start the Dataguard Standby Server after Windows system reboot occurred.

Problem:
When the window reboot occurred, the DR site of the data guard environment did not come up in recovery mode.
We get a gap between the physical and DR site.

Solution: Script OR TRIGGER to make the data guard standby site start in recovery mode on every window restart.

1. Placed the given below scripts on the folder as shown below:
Save the two files shown below in a folder script.

-- Save file as DGSTART.BAT
 SQLPLUS SYS/PASSWORD AS SYSDBA @C:\SCRIPT\DGSTART.SQL > C:\SCRIPT\DBSTART.LOG
--Save file as DGSTART.SQL
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
exit;

OR

We can use this trigger directly on Standby Database to verify its primary or standby and close the instance and start the recovery.

CREATE OR REPLACE TRIGGER startup_mount
AFTER STARTUP ON DATABASE
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role != ‘PRIMARY’ THEN
execute IMMEDIATE ‘alter database close’;
execute IMMEDIATE ‘alter database recover managed standby database using current logfile disconnect from session’;
END IF;
END startup_mount;
/

2. Schedules this script in the Window Task Scheduler event “after startup”. Detailed steps are as follows:

a. Open the task scheduler in Windows and create essential task on it.

taskscheduler1

b. Select the Task Trigger –> When the Computer starts

tashscheduler2

c. Select the start a program.

taskscheduler3

d. Select your script by browser button and put the location of the script also in the START IN(OPTIONAL) field.

taskcheduler4.PNG

e. Click on properties and press the finish button.

taskscheduler5

f. Change the setting to RUN WHETHER USER IS LOGGED ON OR NOT.

taskscheduler6

16 thoughts on “Scripts to start the Dataguard after system reboot in Windows”

  1. Paulo's avatarPaulo

    Thanks for sharing the script.
    We need to know first if data server is with database in standup mode or not. Maybe is in primary mode after switch…
    Am I right?
    Thanks

    Reply ↓
  3. Unknown's avatarAnonymous

    Dear SandeepSingh DBA
    Thanks for your answer but you need to know where standby is to start that script… imagine that you implement that start script via Window Task Scheduler in a server that became primary?
    Thanks
    Paulo

    Reply ↓
  5. SandeepSingh DBA's avatarSandeepSingh DBA

    Yes thanks for update, you can put one check in between this script which let you check before firing command that database server is Standby or Primary. By using this statement:

    select database_role from v$database;

    Reply ↓
  6. paulopires16's avatarpaulopires16

    Before open the databases, you need to know which is PRIMARY and STANDBY to run the STARTUP or STARTUP MOUNT command.
    So, you can’t run any command…
    Thanks
    Paulo

    Reply ↓
  8. SandeepSingh DBA's avatarSandeepSingh DBA

    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 1610609744 bytes
    Fixed Size 9028688 bytes
    Variable Size 1258291200 bytes
    Database Buffers 335544320 bytes
    Redo Buffers 7745536 bytes
    Database mounted.
    SQL> select database_role from v$database;

    DATABASE_ROLE
    —————-
    PRIMARY

    Reply ↓
  9. paulopires16's avatarpaulopires16

    Dear SandeepSingh DBA,

    On primary is not a problem, you check witch is primary after mount mode and then “alter database open;”

    The problem is on standby. You mount standby and then, what you will do to set as standby?
    > ALTER DATABASE MOUNT STANDBY DATABASE; (you need this to knowledge by observer)
    > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

    Thanks

    Reply ↓
    2. udayjampani's avatarudayjampani

      As oracle database and there by database starts automatically. create this trigger in primary ;it should reflect in standby and do the job for you:
      CREATE OR REPLACE TRIGGER startup_mount
      AFTER STARTUP ON DATABASE
      DECLARE
      role VARCHAR(30);
      BEGIN
      SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
      IF role ‘PRIMARY’ THEN
      alter database close;
      alter database recover managed standby database using current logfile disconnect from session;
      END IF;
      END;
      /

      Reply ↓
  10. udayjampani's avatarudayjampani

    Final version :

    CREATE OR REPLACE TRIGGER startup_mount
    AFTER STARTUP ON DATABASE
    DECLARE
    role VARCHAR(30);
    BEGIN
    SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
    IF role != ‘PRIMARY’ THEN
    execute IMMEDIATE ‘alter database close’;
    execute IMMEDIATE ‘alter database recover managed standby database using current logfile disconnect from session’;
    END IF;
    END startup_mount;
    /

    Reply ↓

Leave a Reply