How to Create a RAC Standby Database

Version 1

    In a previous document, I showed the steps needed to create a physical standby database. In that document, the primary and standby databases are both single-instance. This document is very similar but it shows how to create a RAC physical standby from a RAC primary.

     

    Readers of this document may want to read the previous one as well. I will be referring to this previous document many times in this paper.

     

    The Setup

    For this paper, I have a two-node Oracle RAC database with the following details:

     

    Hosts:                            prim01 & prim02

    OS:                                Oracle Linux 7.2

    Grid Infrastructure:        Oracle GI 12.1.0.2

    Oracle RDBMS:             Oracle EE 12.1.0.2

    Instances:                      orcl1 & orcl2  (db name is ‘orcl’)

     

    The RAC primary database is already up and running.

     

    The standby system will be another two-node RAC database. Currently, the nodes are up and running. Grid Infrastructure has been installed. The RDBMS software has been installed, but there is no standby database running. That's the point of this paper after all.  The following are details for the standby cluster:

     

    Hosts:                             stdby01 & stdby02

    OS:                                 Oracle Linux 7.2

    Grid Infrastructure:         Oracle GI 12.1.0.2

    Oracle RDBMS:              Oracle EE 12.1.0.2

    Instances:                       orcls1 & orcls2 (db name will be ‘orcls’)

     

    The Basics

    Just like creating a single-instance standby database, the basic steps are as follows:

    1. Prepare the primary to support a physical standby
    2. Create the standby database
    3. Start the standby and apply recovery

    If you’ve read the previous document, these steps are the same. There is one additional step for this document.

        4. Convert the standby to Oracle RAC

    The way I create a RAC standby is to first create it as a single-instance database. I then convert the single-instance standby database to Oracle RAC as a final step. You can use Enterprise Manager or the DBCA to convert the database to RAC, but I perform the process manually. As you'll see towards the end, it is quite easy.

     

    Prepare the Primary

    Just like a single-instance physical standby database, we have to prepare the primary. The basic steps are the same.

    1. Enable Forced Logging
    2. Set Initialization Parameters
    3. Enable Archive Logging

    The previous document talks about the reasoning behind these settings in more detail so I won’t provide too much depth in this paper.

    Enabling force logging is pretty simple. Because we use the ALTER DATABASE command, we only have to do this in one instance of the RAC primary.

     

    SQL> alter database force logging;

     

    Database altered.

     

    SQL> select force_logging from v$database;

     

    FORCE_LOGGING

    ---------------------------------------

    YES

     

    Next, I need to define the initialization parameters in the primary. From the previous document, I noted these parameters that need to be set accurately in the primary:

    • DB_NAME – both the primary and the physical standby database will have the same database name. After all, the standby is an exact copy of the primary and its name needs to be the same.
    • DB_UNIQUE_NAME – While both the primary and the standby have the same database name, they have a unique name. In the primary, DB_UNIQUE_NAME equals DB_NAME. In the standby, DB_UNIQUE_NAME does not equal DB_NAME.  The DB_UNIQUE_NAME will match the ORACLE_SID for that environment.
    • LOG_ARCHIVE_FORMAT – Because we have to turn on archive logging, we need to specify the file format of the archived redo logs.
    • LOG_ARCHIVE_DEST_1 – The location on the primary where the ORLs are archived. This is called the archive log destination.
    • LOG_ARCHIVE_DEST_2 – The location of the standby database.
    • REMOTE_LOGIN_PASSWORDFILE – We need a password file because the primary will sign on to the standby remotely as SYSDBA.

     

     

    First, I’ll create a PFILE to help me quickly modify the parameters.

     

    SQL> create pfile='/home/oracle/pfile.txt' from spfile;

     

    File created.

     

    Then I’ll shutdown the instances.

     

    [oracle@prim01 ~]$ srvctl stop database -d orcl -o immediate

     

    With a text editor, I will make sure I have the correct parameter values in the PFILE.

     

    *.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

    *.cluster_database=true

    *.compatible='12.1.0'

    *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'

    *.db_block_size=8192

    *.db_domain=''

    *.db_name='orcl'

    *.db_unique_name='orcl'

    orcl2.instance_number=2

    orcl1.instance_number=1

    *.log_archive_dest_1='LOCATION="/u01/app/oracle/oradata/arch"'

    *.log_archive_dest_2='SERVICE="orcls", ASYNC NOAFFIRM'

    *.log_archive_format=%t_%s_%r.arc

    *.memory_target=2048M

    *.remote_login_passwordfile='exclusive'

    orcl2.thread=2

    orcl1.thread=1

    orcl1.undo_tablespace='UNDOTBS1'

    orcl2.undo_tablespace='UNDOTBS2'

     

    The parameters in red are the one that I needed to define to have this be a primary db. If you look back at the previous document, you will see that these are defined exactly the same way as if the primary and standby databases are single-instance. This means the initialization parameters to support the standby are no different just because the primary is now a RAC database.

     

    The parameters in green above are instance-specific, but they were set by the DBCA when the RAC database was created.

     

    Next, I’ll need to create the SPFILE from this PFILE and then start just one of the instances and put the database in archive log mode. With both instances down, on one node, I do the following:

     

    SQL> connect / as sysdba

    Connected to an idle instance.

    SQL> create spfile='/u01/app/oracle/oradata/orcl/spfileorcl.ora'

      2  from pfile='/home/oracle/pfile.txt';

     

    File created.

     

    I specifically had to mention the SPFILE location. Because this is Oracle RAC, the SPFILE is on Shared Storage and $ORACLE_HOME is not on shared storage for my configuration. If I did not specifically denote the SPFILE location, the SPFILE would have been created in $ORACLE_HOME/dbs for just this one instance.

     

    Now I’ll put the database in archive log mode on this one node.

     

    SQL> startup mount

    ORACLE instance started.

     

    Total System Global Area 2147483648 bytes

    Fixed Size 2926472 bytes

    Variable Size 1392511096 bytes

    Database Buffers 738197504 bytes

    Redo Buffers 13848576 bytes

    Database mounted.

    SQL> alter database archivelog;

     

    Database altered.

     

    SQL> alter database open;

     

    Database altered.

     

    Putting the RAC database in archivelog mode is the same as a single-instance database so far. But I started this one instance with SQL*Plus, not the srvctl utility. So I’ll shutdown this instance and start it the RAC way, along with the other instance as well.

     

    SQL> shutdown immediate

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> exit

    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

    and Real Application Testing options

    [oracle@prim01 ~]$ srvctl start database -d orcl

    [oracle@prim01 ~]$ srvctl status database -d orcl

    Instance orcl1 is running on node prim01

    Instance orcl2 is running on node prim02

     

    As a sanity check, I use SQL*Plus to connect to each instance and issue “archive log list” to verify things are setup correctly. I then check the archive log destination to verify I have archive redo logs from both threads, both instances of the primary.

     

    [oracle@prim01 ~]$ cd /u01/app/oracle/oradata/arch

    [oracle@prim01 arch]$ l

    total 15492

    -r--r-----. 1 oracle oinstall    1024 Nov 10 13:51 1_146_925053972.arc

    -r--r-----. 1 oracle oinstall    1024 Nov 10 13:51 1_147_925053972.arc

    -r--r-----. 1 oracle oinstall  823808 Nov 10  2016 1_148_925053972.arc

    -r--r-----. 1 oracle oinstall 5825536 Nov 10 13:49 2_132_925053972.arc

    -r--r-----. 1 oracle oinstall    1024 Nov 10 13:51 2_133_925053972.arc

    -r--r-----. 1 oracle oinstall  959488 Nov 10  2016 2_134_925053972.arc

     

    From the log archive format setting, the first number in the file name is the thread number. As you can see, I have archived redo logs from threads 1 and 2 (from each RAC instance) so things are looking good.

     

    The last thing to setup on the primary is the TNS alias used by LOG_ARCHIVE_DEST_2

     

    ORCLS = (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = stdby01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcls)

      )

    )

     

    The TNS alias looks very similar to the alias I would define if the standby were single-instance. There is one minor difference which may go unnoticed. The HOST value is set to just the first node of the standby. This is because when we initially get the standby up and running, it will only be running on that node and will not be a RAC database.

     

    Create the Standby Database

    Next, I’ll create the standby database. In this section, the standby will only be a single-instance database. So the steps in this section will be almost identical to the steps in the previous document. At a high level, those steps are:

    1. Create a backup of the primary.
    2. Create a standby controlfile.
    3. Copy the password file.
    4. Create a parameter file.
    5. Create a TNSNAMES.ORA file.

    First I need a backup of the primary. For this paper, I’m going to use just OS commands to take a cold backup of the database. The database is very small and since I’m the only user, I can handle the downtime.  I need to find the files so I query the Data Dictionary.

     

    SQL> connect / as sysdba

    Connected.

    SQL> select file_name from dba_data_files;

     

    FILE_NAME

    ------------------------------------------------------

    /u01/app/oracle/oradata/orcl/system01.dbf

    /u01/app/oracle/oradata/orcl/sysaux01.dbf

    /u01/app/oracle/oradata/orcl/undotbs01.dbf

    /u01/app/oracle/oradata/orcl/undotbs02.dbf

    /u01/app/oracle/oradata/orcl/users01.dbf

     

    Then I’ll perform an orderly shutdown of the database. Because the primary is Oracle RAC, I need to do this with the srvctl utility. After I shutdown the instances, I’ll create a directory to hold my backup, copy the files to that directory, then bring the primary back up.

     

    [oracle@prim01 ~]$ srvctl stop database -d orcl -o immediate

    [oracle@prim01 ~]$ mkdir /u01/app/oracle/oradata/orcl/bkup

    [oracle@prim01 ~]$ cp /u01/app/oracle/oradata/orcl/* /u01/app/oracle/oradata/orcl/bkup/.

    cp: omitting directory ‘/u01/app/oracle/oradata/orcl/bkup’

    [oracle@prim01 ~]$ srvctl start database -d orcl

     

    On one of the nodes, I will copy the backup files to shared storage on the standby cluster.

     

    [oracle@prim01 ~]$ scp /u01/app/oracle/oradata/orcl/bkup/* oracle@stdby01:/u01/app/oracle/oradata/orcl/.

    control01.ctl                                 100%   18MB 18.1MB/s   00:01

    control02.ctl                                 100%   18MB 18.1MB/s   00:01

    orapworcl                                     100% 7680     7.5KB/s   00:00

    redo01.log                                    100%   50MB 16.7MB/s   00:03

    redo02.log                                    100%   50MB 16.7MB/s   00:03

    redo03.log                                    100%   50MB 16.7MB/s   00:03

    redo04.log                                    100%   50MB 16.7MB/s   00:03

    spfileorcl.ora                                100% 4608     4.5KB/s 00:00

    sysaux01.dbf                                  100% 1200MB  17.9MB/s   01:07

    system01.dbf                                  100%  700MB 30.4MB/s   00:23

    temp01.dbf                                    100%   25MB 25.0MB/s   00:01

    undotbs01.dbf                                 100%  415MB 29.6MB/s   00:14

    undotbs02.dbf                                 100%  200MB 25.0MB/s   00:08

    users01.dbf                                   100% 5128KB   5.0MB/s   00:00

     

    Next, I need to create a standby controlfile and copy it to the standby’s shared storage.

     

    SQL> alter database create standby controlfile

      2  as '/home/oracle/control.stdby';

     

    Database altered.

     

    SQL> !scp /home/oracle/control.stdby oracle@stdby01:/u01/app/oracle/oradata/orcl/.

    control.stdby                                 100%   18MB 9.0MB/s   00:02

     

    If you were paying attention to the file transfers above, you may have seen that when I copied the backup to the standby’s shared storage, I also copied the password file as well.

    Remember from the previous document that we need to remove the control files on the standby and replace them with the standby control file. On one node of the standby cluster, in the shared storage:

     

    [oracle@stdby01 orcl]$ rm control01.ctl control02.ctl

    [oracle@stdby01 orcl]$ cp control.stdby control01.ctl

    [oracle@stdby01 orcl]$ mv control.stdby control02.ctl

     

    Next I need to prepare a PFILE that I’m going to use to startup the standby database. Here is what I have in my standby’s PFILE:

     

    *.audit_file_dest='/u01/app/oracle/admin/orcls/adump'

    *.cluster_database=true

    *.compatible='12.1.0'

    *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'

    *.db_block_size=8192

    *.db_domain=''

    *.db_name='orcl'

    *.db_unique_name='orcls'

    orcls2.instance_number=2

    orcls1.instance_number=1

    orcls.instance_number=1

    *.log_archive_dest_1='LOCATION="/u01/app/oracle/oradata/arch"'

    *.log_archive_dest_2='SERVICE="orcl", ASYNC NOAFFIRM'

    *.log_archive_format=%t_%s_%r.arc

    *.memory_target=2048M

    *.remote_login_passwordfile='exclusive'

    orcls2.thread=2

    orcls1.thread=1

    orcls.thread=1

    orcls1.undo_tablespace='UNDOTBS1'

    orcls2.undo_tablespace='UNDOTBS2'

    orcls.undo_tablespace='UNDOTBS1'

     

    Before I go further, I should explain a few things about the parameter file above. The changes in red are pretty simple and are identical to what we changed in the document were I created a single-instance physical standby database. The parameters in green are largely to support the RAC database. For example, the RAC standby will have two threads.

    orcls2.thread=2

    orcls1.thread=1

     

    But for a temporary basis, I will start this as a single-instance database. I will need a single instance version of this parameter, i.e. a parameter for an instance with no instance number associated with it.

    orcls.thread=1

     

    You will notice similarly for the other parameters in green that I have defined the parameter for instances 1 and 2 and for the single instance. 

    Next I need to precreate the audit dump directory. On both nodes, I do the following:

     

    [oracle@stdby01 oracle]$ cd /u01/app/oracle/admin

    [oracle@stdby01 admin]$ mkdir orcls

    [oracle@stdby01 admin]$ cd orcls

    [oracle@stdby01 orcls]$ mkdir adump

     

    I’m close to starting up the standby database for the first time. I have a few minor tasks left. Remember that we copied over the password file and it is on shared storage? I need to rename that file to match the convention for this standby database.

     

    [oracle@stdby01 ~]$ cd /u01/app/oracle/oradata/orcl

    [oracle@stdby01 orcl]$ mv orapworcl orapworcls

     

    Next I’ll set my environment variables.

     

    [oracle@stdby01 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

    [oracle@stdby01 dbs]$ export ORACLE_SID=orcls

    [oracle@stdby01 dbs]$ export PATH=$ORACLE_HOME/bin:$PATH

     

    The last thing for me to do is to create a TNS alias. If you look back when I created the parameter file for the standby, I specified that LOG_ARCHIVE_DEST_2 will point back to the primary. I need to have a TNS alias for that database. Should we switchover, this standby will then be able to transport redo to what is now the new standby.

     

    ORCL =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = primary_scan)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = orcl)

        )

      )

     

     

     

    Start Standby and Recovery

    We’re ready to start our standby, albeit in single-instance mode. I have my PFILE on some directory on disk and the SPFILE on shared storage. Since neither is in $ORACLE_HOME/dbs, I will need to explicitly point to one of them when I start the instance.  After starting the instance, I’ll start managed recovery.

     

    SQL> connect / as sysdba

    Connected to an idle instance.

    SQL> startup mount pfile='/home/oracle/pfile.txt';

    ORACLE instance started.

     

    Total System Global Area 2147483648 bytes

    Fixed Size 2926472 bytes

    Variable Size 1392511096 bytes

    Database Buffers 738197504 bytes

    Redo Buffers 13848576 bytes

    Database mounted.

    SQL> alter database recover managed standby database

      2  disconnect from session;

     

    Database altered.

     

    The standby is now up and running. Let’s verify that both instances on the primary and transporting redo to the standby.

    On the first instance of the primary, I will get the current log sequence number and then force a log switch.

     

    SQL> archive log list

    Database log mode Archive Mode

    Automatic archival Enabled

    Archive destination /u01/app/oracle/oradata/arch

    Oldest online log sequence     149

    Next log sequence to archive   150

    Current log sequence           150

    SQL> alter system switch logfile;

     

    System altered.

     

    I’ll do similarly for the second instance of the primary.

     

    SQL> archive log list

    Database log mode Archive Mode

    Automatic archival Enabled

    Archive destination /u01/app/oracle/oradata/arch

    Oldest online log sequence   135

    Next log sequence to archive 136

    Current log sequence         136

    SQL> alter system switch logfile;

     

    System altered.

     

    If I look in the archive log destination of the standby, I can see the files are there for both threads. 

     

    [oracle@stdby01 arch]$ ls -l

    total 19980

    -r--r-----. 1 oracle oinstall 3838464 Nov 10 14:53 1_149_925053972.arc

    -r--r-----. 1 oracle oinstall 2225152 Nov 10 14:53 1_150_925053972.arc

    -r--r-----. 1 oracle oinstall 2831360 Nov 10 14:53 2_135_925053972.arc

    -r--r-----. 1 oracle oinstall 2596352 Nov 10 14:53 2_136_925053972.arc

     

    Instance 1 was on log sequence number 150 and instance 2 was on sequence 136. We can see both logs arrived safely at the standby destination. If we look in the standby database’s alert log, we can see media recovery has applied the contents as well.

     

    Thu Nov 10 15:17:14 2016

    Media Recovery Log /u01/app/oracle/oradata/arch/1_150_925053972.arc

     

    At this point, we now have a single instance physical standby database up and running. Much of it was the same as we saw in the earlier document. Next we’ll convert our standby database to Oracle RAC

     

    Convert Standby to RAC

    There seems to be some mysticism on how to convert an Oracle database to RAC when it’s really quite simple. The basic steps are:

    1. Prepare the SPFILE and password file on shared storage.
    2. Register the database and its instances with Grid Infrastructure
    3. Start the database and its instances.

    When its boiled down to just two steps, it doesn’t look so daunting. So let’s get started. First, I’ll shutdown the single-instance standby we have running.

     

    SQL> shutdown abort

    ORACLE instance shut down.

     

    Next, remember that in our PFILE, we had some instance-specific parameters like the following:

     

    orcls2.instance_number=2

    orcls1.instance_number=1

    orcls.instance_number=1

     

    We have a parameter value for ORCLS1, ORCLS2 and our temporary ORCLS. Remove the ORCLS parameters from the PFILE for the following parameters:

    • INSTANCE_NUMBER
    • THREAD
    • UNDO_TABLESPACE

    We won’t have an instance named ORCLS running any more, only instances with numbers at the end for the RAC database. Next, I’ll create the SPFILE from this PFILE and store it on shared storage.

     

    SQL> create spfile='/u01/app/oracle/oradata/orcl/spfileorcls.ora'

      2  from pfile='/home/oracle/pfile.txt';

     

    File created.

     

    I should have both the SPFILE and the password file on shared storage now. If you remember, when I copied the primary database over to shared storage, it included the password file which was then renamed. So both the SPFILE and password file are ready to go.

     

    If you remember, I had the TNS alias on the primary nodes pointing to just one node of the standby.

    ORCLS =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = stdby01)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = orcls)

        )

      )

    Now I need to change the TNS alias on the primary nodes to point to the Scan Listener of the standby cluster.

     

    ORCLS =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = standby_scan)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = orcls)

        )

      )

     

    At this point, the config is all set. We now need to add the standby to Grid Infrastructure. First, we’ll add the database. I did that with this command:

     

    [oracle@stdby01 ~]$ srvctl add database -db orcls -oraclehome /u01/app/oracle/product/12.1.0.2 -dbtype RAC -spfile /u01/app/oracle/oradata/orcl/spfileorcls.ora -pwfile /u01/app/oracle/oradata/orcl/orapworcls -role PHYSICAL_STANDBY -startoption MOUNT -stopoption IMMEDIATE

     

    I’ll walk through those parameters in the ‘srvctl add database’ command.

    • -db                         This should match the DB_UNIQUE_NAME parameter. Our standby database’s name.
    • -oraclehome          The location of the ORACLE_HOME directory
    • -dbtype                  We are adding a RAC database, not single instance.
    • -spfile                    The location of the SPFILE on shared storage
    • -pwfile                   The location of the password file on shared storage
    • -role                      What role this database serves. In our work here, this database is a physical standby.
    • -startoption           How do you want Grid Infrastructure to start the database? Because it’s a standby, we want to do the equivalent of STARTUP MOUNT.
    • -stopoption           How do you want GI to stop the instances? I’m specifying the equivalent of SHUTDOWN IMMEDIATE

    Now that the database is registered with Grid Infrastructure, we need to register the instances as follows:

     

    [oracle@stdby01 ~]$ srvctl add instance -db orcls -instance orcls1 -node stdby01

    [oracle@stdby01 ~]$ srvctl add instance -db orcls -instance orcls2 -node stdby02

     

    That’s not too difficult. All that’s left is to start the RAC version of our standby database.

     

    [oracle@stdby01 ~]$ srvctl start database -db orcls -startoption MOUNT

    [oracle@stdby01 ~]$ srvctl status database -db orcls

    Instance orcls1 is running on node stdby01

    Instance orcls2 is running on node stdby02

     

    Success! Our standby is now running as an Oracle RAC database. Let’s verify.

     

    SQL> select inst_id,instance_name,status from gv$instance;

     

       INST_ID INSTANCE_NAME    STATUS

    ---------- ---------------- ------------

             2 orcls2           MOUNTED

             1 orcls1           MOUNTED

     

    All we need to do is to start managed recovery on one node.

     

    SQL> alter database recover managed standby database disconnect from session;

     

    Database altered.

     

    If you attempt to start managed recovery on the other node when MRP is running already, you will receive an 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-01153: an incompatible media recovery is active

     

    The ORA-1153 is saying that MRP is already active on another node.

    The reader go through checks we’ve already seen to verify that the log transport is occurring from both nodes of the primary and that redo apply is operating correctly.

    Conclusion

    Hopefully this document helps you get your RAC physical standby databases up and running. Its not that much more difficult than a single-instance standby. Most of the steps are the same. At the end, we just convert a single-instance standby to a RAC standby.

     

    After you have your RAC standby running, the next thing to do is to create Standby Redo Logs. For information on how and why you want SRLs, see this document. https://community.oracle.com/docs/DOC-1007036

    I will also mention that when creating SRLs for RAC standby databases, do not assign the SRL to a specific thread of redo. If you do not assign them to a redo thread, then that SRL can be used by any thread from the primary.

     

    The next step after setting up SRLs is to configure the DG Broker. More on that can be found in this document.  https://community.oracle.com/docs/DOC-1007327

    Since you’re running a RAC standby, Grid Infrastructure will automatically start the instances on server reboot and put the instances in MOUNT mode as we configured. If you have the DG Broker running, then as soon as those instances start, they will start the Broker Monitor (DMON) and DMON will automatically start managed recovery for you as well. With the DG Broker, you won’t have to write any scripts to automate the startup/shutdown of your RAC standby.