CREATING A STANDBY DATABASE USING RMAN STEPS - (UNIX / LINUX)

Overview

This article provides a quick introduction on how to create an Oracle standby database using the RMAN DUPLICATE TARGET DATABASE FOR STANDBY command on the UNIX / Linux operating environment. For this demonstration, we will create a standby database using RMAN's DUPLICATE command to a different host other than the primary database.
You can use the Recovery Manager DUPLICATE TARGET DATABASE FOR STANDBY command to create a standby database. RMAN automates the following steps of the creation procedure:

1. Restores the standby control file.
2. Restores the primary datafile backups and copies.
3. Optionally, RMAN recovers the standby database (after the control file has been mounted) up to the specified time or to the latest archived redo log generated.
4. RMAN leaves the database mounted so that the user can activate it, place it in manual or managed recovery mode, or open it in read-only mode.

RMAN cannot fully automate creation of the standby database because you must manually create an initialization parameter file for the standby database, start the standby instance without mounting the control file, and perform any Oracle Net setup required before performing the creation of the standby. Also, you must have RMAN backups of all datafiles available as well as a control file backup that is usable as a standby control file.

Pre-requisites

Please refer the below link for the pre-requisites required for the creation of standby database using RMAN

Create standby database prerequisites

Steps Required

Step 1: Create Password File for Standby Database

Login to the standby database server and create a password file:

$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwORA920

OR
Copy the primary database password file to the standby $ORACLE_HOME/dbs location.

Step 2: Create a Standby Controlfile

There are several ways in which to create a standby control file to be used with the standby database instance. In most cases, the easiest way is to perform this is within RMAN. Keep in mind that RMAN will need to have a copy of the standby control file within the catalog before RMAN can use it with the duplicate ... for standby command. From the target (primary) database server, use the following:

$ ORACLE_SID=ORA920; export ORACLE_SID
$ rman target /
RMAN> backup current controlfile for standby format='/orabackup/rman/ORA920/stby_cfile.%U';

Step 3: Record Last Log Sequence

You will need to specify a point in time after the creation of the standby control file. To do this, perform a few log switches and then record the last log sequence number from the v$archived_log view. From the target (primary) database instance:

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
           208
     

Step 4: Backup New Archive Log Files

After creating the standby control file, you will need to backup the newly created archive redo logs (created from the alter system switch logfile; command above):

$ rman target /
RMAN> backup archivelog all delete input;

Step 5: Create Directory Structure on Standby (Auxiliary) Server

On the standby database server, create all needed directories for the standby database

Step 6: Create an Initialization Parameter for the Standby Database

Now, copy a version of the text initialization parameter from the target database to the standby database server and make the necessary changes for the standby database:
From the target (primary) database server:

$ export ORACLE_SID=ORA920
$ sqlplus "/ as sysdba"
SQL> create pfile='/u01/app/oracle/product/9.2.0/dbs/initORA920.ora' from spfile;

After creating and copying the initialization parameter for the standby database, change the following parameters for the standby database:

fal_client                  =
fal_server                  =
log_archive_dest_2          =
log_archive_dest_state_2    =
standby_file_management     = AUTO
 

NOTE: Keep in mind that the db_name initialization parameter of the standby database must match the db_name parameter for the primary database. This is required whether the standby database is on the same or different host from the primary database.
If you were to attempt to start two databases on the same host with the same db_name parameter, you will get the following error:
ORA-01102: Cannot mount database in exclusive mode
To get around this, you will need to modify the init.ora file of the standby database and add the parameter lock_name_space. You would set this parameter to a value different from the db_name parameter. Oracle will then use this name to lock memory segments without changing the db_name. For example,
lock_name_space=

Step 7: Start the Standby (Auxiliary) Instance

On the standby database server, start the Oracle instance in nomount mode:

$ ORACLE_SID=ORA920; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> startup nomount
 

Step 8: Ensure Oracle Net Connectivity to Standby (Auxiliary) Database

Modify both the listener.ora and tnsnames.ora file to be able to connect to the standby (auxiliary) database.
Once the Oracle networking files are configured for the primary & the standby (auxiliary) database host, ensure to start the TNS listener with the latest (valid) listener.ora file:

$ lsnrctl stop
$ lsnrctl start
 

Step 9: Mount or Open the Target Database

As mentioned in the pre-requisites section of this article, the target database must be either opened or mounted.

$ ORACLE_SID=ORA920; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> startup open
 

Step 10: Ensure You Have the Necessary Backups and Archived Redo Log Files

As mentioned in the pre-requisites section of this article, ensure that you have a current backup that you wish to use to create the standby database. Login to query the RMAN catalog.

RMAN> list backup summary
 

Step 11: Create Standby Database

Login to target (primary) and (standby) auxiliary database using RMAN. All of this should be performed from the target database server
Note: Notice that the parameter NOFILENAMECHECK must be used when you are duplicating a database to a different host with the same file system (directory structure)
Run the following:

$ ## primary & standby are net service names below
$ rman target sys/sys@primary auxiliary sys/sys@standby
RMAN> -- The following RUN block can be used to fully duplicate the target database from the latest full backup. 
RMAN> -- This will create the standby database:
RMAN> run {
    # Set the last log sequence number
    set until sequence = 208 thread = 1;
    # Allocate the channel for the duplicate work
    allocate auxiliary channel ch1 type disk;
    # Duplicate the database to ORA920
    duplicate target database for standby dorecover nofilenamecheck ;
}
RMAN> exit
 

Step 12: Place the Standby in Managed Recovery Mode

On the standby database, run the following:

$ sqlplus "/ as sysdba"
SQL> recover standby database;
SQL> alter database recover managed standby database disconnect;
Database altered
 

Happy Standby!! 

CREATING A STANDBY DATABASE USING RMAN - (UNIX / LINUX) - Pre-reqiusites

Prerequisites for creating the standby database using RMAN are as follows.

Step 1: Target database must be mounted or open

$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jan 3 19:42:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup open
ORACLE instance started.
Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

Step 2: Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Step 3: Enable Forced Logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

Step 4: Create a Password File
Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed

Step 5: Configure a Standby Redo Log – For real time apply
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone

Perform the following steps to configure the standby redo log.

    Step 5.1 Ensure log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files

    Step 5.2 Determine the appropriate number of standby redo log file groups.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads

    Step 5.3 Verify related database parameters and settings.
Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.

    Step 5.4 Create standby redo log file groups.
To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
2>   ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

    Step 5.5 Verify the standby redo log file groups were created.
To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step 6 A valid full database backup of the target database
From the target (primary) database, you will need to create a full backup of the database.

RMAN> backup database plus archivelog delete input;
RMAN> list backup summary;

Happy Standby building