Creating an 11.2.0.2 RAC Logical Standby Database

Yesterday I created an 11.2.0.2 RAC logical standby database for an 11.2.0.2 RAC primary database. Here are the steps.

You create a logical standby database by first creating a physical standby database and then transitioning it to a logical standby database. So first I created a physical standby database "belmont" for primary database "gilroy":

SQL> SELECT db_unique_name,name,open_mode,database_role FROM v$database;
DB_UNIQUE_NAME                 NAME      OPEN_MODE            DATABASE_ROLE
------------------------------ --------- -------------------- ----------------
belmont                        GILROY    MOUNTED              PHYSICAL STANDBY

Before converting it to a logical standby, you need to stop Redo Apply on the physical standby database:

SQL> alter database recover managed standby database cancel;

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. To build the LogMiner dictionary, issue the following statement on primary database:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

All auxiliary instances have to be shut down and disable the cluster on the target standby if your standby is a RAC (in my case). Shut down all but the instance on which the MRP was running (your actual target instance). Once they are all done, then disable the cluster and bounce the standby:

SQL> alter system set cluster_database=false scope=spfile;
SQL> shutdown immediate;
SQL> startup mount exclusive;

Now you are ready to tell MRP that it needs to continue applying redo data to the physical standby database until it is ready to convert to a logical standby database:

SQL> alter database recover to logical standby fremont;

In above statement, you changed the actual database name of the standby to "fremont" so it can become a logical standby database. Data Guard will change the database name (DB_NAME) and set a new database identifier (DBID) for the logical standby.
At this point, you can re-enable the cluster database parameter, if you had a RAC, and then restart and open the new logical standby database:

SQL> alter system set cluster_database=true scope=spfile;
SQL> shutdown;
SQL> startup mount;
SQL> alter database open resetlogs;

Issue the following statement to start SQL Apply in real-time apply mode using the IMMEDIATE keyword:

SQL> alter database start logical standby apply immediate;

Creating a 10.2.0.4 Physical Standby Database

Firstly, install Oracle Database 10.2.0.4 software for both primary and standby sites.

Creating the primary database
Then create primary database "chicago" on file system using DBCA.

Remember to enable ARCHIVELOG mode during DBCA interview.

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

Create a listener using NETCA in GUI mode.

Preparing the standby system
You must configure the network as per your tuning with the TNS names for the primary database in the TNSNAMES file. This can be done using the Oracle Net Manager ($ORCLE_HOME/bin/netmgr) in GUI mode.

In addition, create the various directories for the dump parameters and, if you are not using ASM, the directories, where the data files, control files, online log files, and archive log files will be placed. In this case, these directories include (given $ORACLE_BASE is "/oracle/opt/oracle" and standby database DB_UNIQUE_NAME is "boston"):

/oracle/opt/oracle/oradata/boston

/oracle/opt/oracle/oradata/boston/archivelog

/oracle/opt/oracle/admin/boston/adump

/oracle/opt/oracle/admin/boston/bdump

/oracle/opt/oracle/admin/boston/cdump

/oracle/opt/oracle/admin/boston/udump

Getting the necessary files and creating the backups
You need to gather 4 main files on primary system for transporting to the target standby system be able to create a standby database:

• The initialization parameters
• The password file
• A full backup of the database
• The control file backup (as a standby control file)

Create a PFILE for standby database from primary database's SPFILE:

SQL> CREATE PFILE='/home/ora1024/stage/initboston.ora' FROM SPFILE;

Copy the password file from the primary system to your target standby system:

cp $ORACLE_HOME/dbs/orapwchicago /home/ora1024/stage/orapwboston

Create a full backup of the entire primary database "chicago":

RMAN> BACKUP DEVICE TYPE DISK FORMAT '/home/ora1024/stage/Database%U' DATABASE PLUS ARCHIVELOG;

Create a copy of the control file for the standby database:

RMAN> BACKUP DEVICE TYPE DISK FORMAT '/home/ora1024/stage/Control%U' CURRENT CONTROLFILE FOR STANDBY;

Now copy all these files to the SAME directory i.e. /home/ora1024/stage on your standby system "pstby5":

scp -p /home/ora1024/stage/* pstby5:/home/ora1024/stage/

Preparing the standby database
At minimum, you need to change the DB_UNIQUE_NAME to the name ("boston" in this case) of the standby in the PFILE i.e. initboston.ora:

*.db_unique_name='boston'

If your directory structure is different, you also need to add file name conversion parameters:

*.db_file_name_convert='/chicago/','boston'

*.log_file_name_convert='/chicago/','boston'

Restoring the backup
Once the initialization parameters are all set and the various directories have been created, start the standby database up in NOMOUNT mode:

export ORACLE_SID=boston

sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/home/ora1024/stage/initboston.ora';

SQL> CREATE SPFILE FROM PFILE='/home/ora1024/stage/initboston.ora';

SQL> SHUTDOWN ABORT;

SQL> STARTUP NOMOUNT;

SQL> EXIT;

And using RMAN to connect to the primary database as the target and the standby database as the auxiliary, then duplicate database for standby:

rman target sys/oracle@chicago auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

Now standby database should be in MOUNT state.

Configuring the standby database
Add necessary SRL files to the standby database for redo transport:

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/boston/redo04.rdo' size 512M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/boston/redo05.rdo' size 512M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/boston/redo06.rdo' size 512M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/boston/redo07.rdo' size 512M;

You can now finish defining the Data Guard parameters that will be necessary in the standby role as well as the primary role when a switchover (or failover) occurs:

SQL> ALTER SYSTEM SET FAL_SEVER=chicago;

SQL> ALTER SYSTEM SET FAL_CLIENT=boston;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=chicago ASYNC DB_UNIQUE_NAME=chicago VALID_FOR=(online_logfile,primary_role)';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

And start the apply process on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This will create and clear the ORL files so that they exist when the standby becomes a primary.

Finalizing the primary database
Add SRL files so that they are in place for a future role transition:


SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/chicago/redo04.rdo' size 512M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/chicago/redo05.rdo' size 512M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/chicago/redo06.rdo' size 512M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/opt/oracle/oradata/chicago/redo07.rdo' size 512M;

Set the Data Guard parameters on the primary database that will be used to send redo to the standby. Also set those parameters that will be used when the primary becomes a standby database after a role transition:

SQL> ALTER SYSTEM SET FAL_SEVER=boston;

SQL> ALTER SYSTEM SET FAL_CLIENT=chicago;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC DB_UNIQUE_NAME=boston VALID_FOR=(online_logfile,primary_role)';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

To start sending redo, switching log files on the primary:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Verifying the physical standby database is performing properly
Using follow queries to verify the physical standby database is performing properly:

SQL> SELECT DB_UNIQUE_NAME,NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;

SQL> SELECT PROCESS,STATUS,THREAD#,SEQUENCE# FROM V$MANAGED_STANDBY;

And you can force a logfile switch to archive current ORL file on primary database and then verify the redo data was archived on the standby database.