Oracle DataGuard 11g R2 Administration


Hello Everyone!

From my previous article, I have shared how to setup Oracle Dataguard 11.2 from scratch. The next thing that a DBA will do is to be familiar with the environment, to explore its functionality and to observe its behavior. With these, you will have a familiarization with Oracle Dataguard.

Let me know if you have some questions, clarifications and additional information so we can discuss them as soon as we can.

Thanks.

Let's share and make others love Oracle, too.


========================================================================

Familiarization with Oracle DataGuard 11.2

With our Data Guard configuration running in maximum performance mode, the validation tasks will involve:

1.         Switching redo log files from the primary.
2.         Verifying those log files are being shipped.
3.         Checking that the redo logs are applied to the physical standby database.




Redo log switch and transport

On the primary server, do the following steps:

[oracle@ server01 ~]$ sqlplus sys/oracle@banc01 as sysdba

SQL>alter system archive log current;

SQL>alter system switch logfile;

System altered.





SQL>select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- ---------------------------------------------------------
VALID

The output should have no error, otherwise, investigate the error and troubleshoot the problem.



Check the redo transport and redo apply

On the standby server:

[oracle@server02 ~]$ sqlplus sys/oracle@banc02 as sysdba

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;




Starting Up and Shutting Down the Data Guard Instance

Shutting Down
           
            On the primary database:
           
            SQL> shutdown immediate;
     SQL> quit




           
On the standby database:
           
     SQL> alter database recover managed standby database cancel;
     SQL> shutdown immediate;
     SQL> quit

Starting Up

            On the primary database:
           
     SQL> startup;
     SQL> quit

           
On the standby database:
           
     SQL> startup nomount;
     SQL> alter database mount standby database;
     SQL> alter database recover managed standby database
          disconnect;
           

Switching the standby to READ-ONLY mode and back to recovery mode

           
Set the standby to read-only mode

On the primary database:
           
            SQL> alter system set log_archive_dest_state_2=DEFER;
           
            On the standby database:
           
            SQL> alter database recover managed standby database cancel;
     SQL> alter database open read only;




Set standby back to recovery mode (Original Setup)

On the standby database:

     SQL> shutdown immediate;
     SQL> startup nomount;
     SQL> alter database mount standby database;
     SQL> alter database recover managed standby database
          disconnect;

On the primary database:

     SQL> alter system set log_archive_dest_state_2=ENABLE;



Role Transitions

Performing a Switch Over

This process will convert the standby database to be the new primary database.

            On the primary database

            SQL> alter system archive log current;
           
On the standby database

            SQL> select sequence#, first_time, next_time,
          archived, applied
           from v$archived_log order by sequence#;







The last archive log must be transported and applied to the standby database. Check the alert logs of both databases.
           
     SQL> select * from v$archive_gap;


The output should return: “No Rows Selected”. If some rows will be selected, check the network traffic and connectivity (or take note of the issue and resolve)


On the primary database

            SQL> select * from v$archive_gap;
           
            Should also have no rows selected.

Still on the primary database

     SQL> alter database commit to switchover to physical
          standby with session shutdown;
     SQL> shutdown immediate;
     SQL> startup nomount;
     SQL> alter database mount standby database;
           
           
On the standby database

            SQL> alter database commit to switchover to primary;
     SQL> alter database open;

You have now converted the standby database as the new primary database. Now you will need to perform a manage recovery on the new standby database.


On the NEW STANDBY database

            SQL> alter database recover managed standby
          database disconnect;
           
Check the new set up for Data Guard functionality—log switch, log transport and log apply.

To revert to the original setup, perform another Switch Over and check for Data Guard functionality.


Performing a Fail Over

On the standby database

            SQL> alter database recover managed standby database finish;
     SQL> alter database activate standby database;

     SQL> shutdown immediate;
     SQL> startup;


Perform RMAN backup and check for consistency.


On the primary database (if still possible)


Shutdown the database and try to recover data. If completely destroyed, recreate the Data Guard setup using the surviving database instance.

Comments

Post a Comment

Popular posts from this blog

RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7'; SHOW ALL; REPORT SCHEMA; LIST BACKUP OF DATABASE; REPORT NEED BACKUP; REPORT UNRECOVERABLE; LIST EXPIRED BACKUP BY FILE; LIST ARCHIVELOG ALL; REPORT OBSOLETE; CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK COPY OF ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; RUN { sql 'alter system archive log current'; BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; } LIST BACKUP ...
Read more

[Script] Tablespace Usage Alert

Hi, This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts. This will alert you via email if a certain tablespace  reaches the set threshold. Let's share and make others love Oracle, too. For tablespace monitoring alert scripts. We have the following: Tablespace Monitoring Script You will notice that there is a pattern in the scripts creation presented in this blog.  Part 1: Report Generation : Here it is the tablespace report Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report. Part 3: Check if the file generates alert : Email is sent if entities met the threshold. # # # # # #----- Start of Script -----###### #!/bin/bash  # Report tablespace usage  sqlplus "/ as sysdba" << EOF SET LINES 700...
Read more

[Script] ASM Diskgroup Space Usage Alert

Hi Again, Another important task that a DBA should do is to monitor the ASM diskgroup usage. We do not just add space to tablespaces, create more tablespaces or resize datafiles to increase storage without checking if there is space to our ASM diskgroup. Also, when we perform RMAN backups stored in ASM or Datapump Exports stored in ASM, we have to monitor and get alerts if we are running out of space. This is a simple monitoring script. I hope this could help you. Let's share and make others love Oracle, too. ASM Diskgroup  Space Monitoring ######--- Start of Script -----###### #!/bin/bash  # Report ASM Diskgroup space usage  #NOTES:  # 1. There is really a slash on the v$asm_diskgroup so Unix interpreter will treat dollar sign as a literal $ # 2. Set your tablespace threshold by changing the value of the AND clause  " >= 80" sqlplus "/ as sysdba" << EOF SET LINES 700 SET FEEDBACK OFF SET TRIMSPOOL ON SET ECHO OFF SPOOL /tmp/ASMs...
Read more