No results found

Your search did not match any results.

We suggest you try the following to help find what you're looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
  • Try one of the popular searches shown below.
  • Start a new search.

 

Trending Questions

The DBA’s Guide to Setting Up Oracle RAC One Node and Oracle Data Guard, Part 2 (Advanced)

by Martin Bach
Published July 2011

In this installment, learn the manual, more advanced approach to this configuration.

In Part 1 of this article, we covered a Oracle Data Guard Broker configuration for Oracle RAC One Node and Oracle Data Guard. This configuration is well suited for less experienced DBAs who don’t want or need to be in control of each step during the role transitions. Furthermore, the broker is ideal in situations where automation is key or the staff is less experienced. In this installment, we will examine a manual configuration that gives the DBA more fine-grained control.

Unlike the broker approach, the manual Oracle Data Guard configuration requires the user to understand the process better and how Oracle Data Guard works, and requires the user to exercise more attention to detail, ensuring the configuration is identical across all standby databases. With the increase of standby database configurations to 30 in Oracle 11.2, this becomes even more important.

Creating a Manual Oracle Data Guard Configuration

Surprisingly there is no difference between how a Oracle RAC One Node standby is configured compared to how this task is done with an Oracle RAC database. In generic terms, you will have to perform the configuration steps on each database, primary and standby. It is important to prepare the primary database for a standby role, including the creation of standby redo logs. The question is not if the role change is going to arrive as a task on your desk, it’s rather when that is going to happen. And remember that the database doesn’t have to be the faulty component in the stack-a problem with the middle tier can equally cause a massive outage.

The main initialization parameter to modify with regards to standby databases is the log_archive_dest_n parameter which defines where to ship an archived log to, and in which role that should happen. One of the real improvements to Oracle Data Guard came with Oracle Database 10g, which allowed configuring log shipping based on database role. In other words, you could set up the log shipping on the primary, instructing it to send redo only if it was in a standby role.

Let’s have a look at how this is done in real life. Start by configuring the primary database.



        SQL alter system set log_archive_dest_2=
          2> 'service=RONDG valid_for=(online_logfiles,primary_role) db_unique_name=RONDG';
    System altered.
    SQL> alter system set fal_server=RONDG;
    System altered.
    SQL> alter system set log_archive_config='dg_config=(RON,RONDG)';
    System altered.
    SQL> alter system set standby_file_management='auto';
    System altered.


These steps are the minimum necessary to create a configuration. The log_archive_dest_2 parameter specifies that the database RON should submit redo to the service “RONDG” when it is in primary role. The Oracle Data Guard Concepts and Administration guide has all the options, the more interesting ones probably being around the compression option (might require the Advanced Compression option). With Oracle 11.1 traffic caused by gap resolution (FAL – fetch archive log) could be compressed to save bandwidth, and now with 11.2 you can even compress ordinary redo log shipping.

The necessary configuration steps on the standby database are very similar, just the inverse to the previous ones:


        SQL alter system set log_archive_dest_2=
          2> 'service=RON valid_for=(online_logfiles,primary_role) db_unique_name=RON';
    System altered.
    SQL> alter system set fal_server=RON;
    System altered.
    SQL> alter system set log_archive_config='dg_config=(RON,RONDG)';
    System altered.
    SQL> alter system set standby_file_management='auto';
    System altered.


Optionally create standby redo log files for your standby (and primary database!) at this stage for real time apply. If you are planning to, you should add standby redo logs for each thread, of the same size as the online redo logs. One member is enough; standby redo logs do not need to be multiplexed. Also, you should create one more standby redo log group than existing groups per thread.

In our primary database we have two threads (instance RON_1 is currently up and running which explains why thread 2 is closed):



RON SQL> select thread#,status,enabled,groups from v$thread;

   THREAD# STATUS ENABLED      GROUPS
---------- ------ -------- ----------
         1 OPEN   PUBLIC            2
         2 CLOSED PUBLIC            2

RON SQL>


You can furthermore query v$log to check about size and groups per thread you have, columns bytes, group# and thread# reveal important information.

Adding standby redo logs then is simple. For each thread, add group# + 1 standby redo logs. For my database with 5 groups per thread, the command to use could be as follows:


BEGIN
  FOR THREAD IN 1..2 LOOP
    FOR LOGFILE IN 1..6 LOOP
      EXECUTE IMMEDIATE'ALTER DATABASE ADD LOGFILE THREAD ' || THREAD || ' SIZE 150M';
    END LOOP;
  END LOOP;
END;
/

That will create six standby redo logs per thread, enough for real time apply. Now let’s enable managed recovery to complete the configuration.


        SQL> alter database recover managed standby database using current logfile
  2  disconnect from session;

You should see now that media recovery is taking place – the alert log should emit lines similar to these:


        Media Recovery Log +FRA/rondg/archivelog/2011_03_29/thread_1_seq_265.270.747054265
           Media Recovery Log +FRA/rondg/archivelog/2011_03_29/thread_1_seq_266.271.747054265
           Media Recovery Log +FRA/rondg/archivelog/2011_03_29/thread_1_seq_267.261.747048749



Well done, you are on track! Before closing the ticket used to implement the Oracle Data Guard configuration I usually check if log shipping is working.

Testing Data Guard Log Shipping

The first step after a successful configuration is to test if log shipping works. Here’s an example: I am querying the current log sequence number from the primary database before performing a log switch:



        SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     297
Next log sequence to archive   300
Current log sequence           300
SQL> alter system switch logfile

System altered.

The standby database was current up to sequence# 299 before the log switch. After the log switch it reported the new log applied:


SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
           299

-- log switch on primary

SQL> /

MAX(SEQUENCE#)
--------------
           300

In an Oracle RAC database you’d have to take the individual threads into account in the above query, but with Oracle RAC One Node and only one active instance, this is not needed. This is good news for developers of custom monitoring scripts.

Flashback Database

Just to be on the safe side, I usually enable Flashback Database on all involved databases before a switchover -- this includes the standby database(s) as well! Checking if it has been enabled is simple, as is the correction if it’s not. Consider this example of database RONDG where Flashback was not enabled:



        SQL> select db_unique_name,database_role,flashback_on from v$database
            2 /
    DB_UNIQUE_NAME DATABASE_ROLE FLASHBACK_ON
       ------------------------------ ---------------- ------------------
       RONDG PHYSICAL STANDBY NO
    SQL> alter database recover managed standby database cancel;
    Database altered.
    SQL> alter database flashback on
    Database altered.
    SQL> alter database recover managed standby database using current logfile
        2 disconnect from session;
    Database altered
    SQL> select db_unique_name,database_role,flashback_on from v$database;
    DB_UNIQUE_NAME DATABASE_ROLE FLASHBACK_ON
       ------------------------------ ---------------- ------------------
       RONDG PHYSICAL STANDBY YES


Testing a Graceful Switchover

No Oracle Data Guard setup is complete without regular switchover tests. A lot of experienced instructors from Oracle and DBAs from the field also suggest that a failover should be exercised regularly, but most sites I have visited refuse this kind of stringent testing. The failover situation is shown in the next section.

Before beginning any switchover operation it is prudent to ensure you are logged on to the correct database and that flashback is enabled on all involved databases. In this scenario, it is:


        SQL> select db_unique_name,database_role, flashback_on from v$database;
    DB_UNIQUE_NAME DATABASE_ROLE FLASHBACK_ON
       ------------------------------ ---------------- ------------------
     RON PRIMARY YES
    SQL> select db_unique_name,database_role, flashback_on from v$database;
    DB_UNIQUE_NAME DATABASE_ROLE FLASHBACK_ON
       ------------------------------ ---------------- ------------------
       RONDG PHYSICAL STANDBY YES


Initiate the switchover command on the primary, as shown below:



SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

[oracle@node1 ~]$ srvctl start database -d RON -o mount
[oracle@node1 ~]$ srvctl status database -d RON
Instance RON_1 is running on node node1
Online relocation: INACTIVE


Connected to the database we see that the role has indeed changed:


SQL> select db_unique_name,database_role from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
RON                            PHYSICAL STANDBY

Now all that remains to be done is to enable managed recovery:


        SQL> alter database recover managed standby database
  2  using current logfile
  3  disconnect from session;   

Database altered.


You should also use srvctl modify database to update the database’s role to physical standby to avoid violating your license (unless you have Oracle Active Data Guard of course).

On the standby, issue these commands after the alter database commit to switchover... completed to turn it into a primary:


    SQL> alter database commit to switchover to primary;
    Database altered.
    SQL> alter database open;
    Database altered.
    SQL> select db_unique_name,database_role,flashback_on from v$database;
    DB_UNIQUE_NAME DATABASE_ROLE FLASHBACK_ON
       ------------------------------ ---------------- ------------------
          RONDG PRIMARY YES


It’s important to check that log shipping is working, using the same technique as before. First query the primary database:


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 304
Next log sequence to archive 304
Current log sequence 304
SQL> alter system switch logfile;
System altered.



Next check the standby database:


        SQL> select max(sequence#) from v$archived_log where applied='YES' and 
            2 resetlogs_change#=(select max(resetlogs_change#) from v$archived_log);

    MAX(SEQUENCE#)
       --------------
        305
    
    

Alternatively you could show alert –tail –f option in ADRCI to follow Oracle as it ships, archives and applies the log. The view V$MANAGED_STANDBY is also very useful.

Finally start the service RON_APP.example.com and update the configuration of RONDG to reflect that it is now a primary database using srvctl modify database.

Testing a Failover

Taking the scenario one step further is the test of a failover. Previous to Oracle Database 10g, a failover meant that the failed database had to be restored from backup. Luckily this has changed! In the next section I am going to explore how to make the best use of the Flashback Database feature to minimize the required effort to get the database back.

Following the previous example of graceful switchover to RONDG, let’s start by simulating a failure on the primary database, RONDG:

    
    SQL> select db_unique_name,database_role,flashback_on from v$database;

    DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
    ------------------------------ ---------------- ------------------
    RONDG                          PRIMARY           YES
    [oracle@drnode1 ~]$ srvctl stop database -d RONDG -o abort

Assume that the primary database cannot be started again within the agreed service level, and the standby has to be activated. The following example shows these steps on the standby database:


    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
    Database altered.
    SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
    Database altered.
    SQL> select db_unique_name,database_role,flashback_on from v$database;

    DB_UNIQUE_NAME                 DATABASE_ROLE    FLASHBACK_ON
    ------------------------------ ---------------- ------------------
   RON                             PRIMARY          YES

With this done, the service has been restored, and the application teams busily re-point their application servers to the new database. Again, don’t forget to reflect the configuration change in the Oracle Cluster Registry. If not started already, ensure that the service RON_APP.example.com is started on RONDG before handing the system over to the application server team.

Reinstate the Old Primary as Physical Standby

As we said earlier, activating the standby database meant a lot of work in Oracle9i Database and before, but luckily this can be avoided with Flashback Database. (But only if all the required logs are still there!). The new primary database keeps a record of the System Change Number at which it has been activated:


    SQL> select standby_became_primary_scn , database_role from v$database
    STANDBY_BECAME_PRIMARY_SCN DATABASE_ROLE
    -------------------------- ----------------    
                       1397880 PRIMARY

The old primary, RONDG, has to be flashed back to this SCN and converted back to a physical standby. After the problem has been rectified, the database can be brought up again:


    [oracle@drnode1 ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 31 14:50:19 2010

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 4275781632 bytes
    Fixed Size 2233336 bytes
    Variable Size 2348813320 bytes
    Database Buffers 1912602624 bytes
    Redo Buffers 12132352 bytes
    Database mounted.
    SQL> flashback database to scn 1397880;

    Flashback complete.

    SQL> alter database convert to physical standby;

    Database altered.

    SQL> shutdown immediate
    sORA-01507: database not mounted 

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

    Total System Global Area 4275781632 bytes
    Fixed Size 2233336 bytes
    Variable Size 2348813320 bytes
    Database Buffers 1912602624 bytes
    Redo Buffers 12132352 bytes
    Database mounted.

    SQL> select db_unique_name,database_role from v$database;

    DB_UNIQUE_NAME DATABASE_ROLE
    ------------------------------ ----------------
    RONDG                          PHYSICAL STANDBY

    SQL> alter database recover managed standby database disconnect from session
           2 /

Log shipping and application should be validated again before the system can be declared functional. At a later stage a graceful switchover can restore things to how they were.

Summary

In this two-part article, you have learned how to set up a physical standby database for an Oracle RAC One Node primary using ACFS for the diagnostic destination and other administrative files. The standby database has been registered with the Oracle Cluster Registry, and manual as well as broker configurations have been created and tested. The scenario could be taken further by trying Fast Start Failover for lights-out management of a RON environment, but this remains an exercise for the reader.

Martin Bach is a freelance Oracle consultant in the UK and is the co-author of Pro Oracle Database 11g RAC on Linux (Apress).