Cloning a Database with RMAN (DATA GUARD)

0
38

Active Database Duplication
Oracle 11g RMAN introduces the FROM ACTIVE DATABASE capability to the DUPLICATE FOR STANDBY command. This alleviates the previous need for interim storage on both the Primary and Standby systems, and the limitation of single stream network traffic.
Now when taking the backup of the Primary database you can simultaneously create and restore the standby database over the network in parallel streams. Apart from some simple Oracle Net setup, and creating a couple of directories and an interim password file, the whole standby creation can be done in one RMAN script.
RMAN will automatically copy the server parameter file to the standby host, start the auxiliary instance with the server parameter file, restore a backup control file, and copy all necessary database files and archived redo logs over the network to the standby host.
The database files are copied from the source to a destination or AUXILIARY instance via an inter-instance network connection. RMAN then uses a “memory script” (one that is contained only in memory) to complete recovery and open the database.
Password files are copied to the destination. The destination must have the same SYS user password as the source. In other words, at the beginning of the active database duplication process, both databases (source and destination) must have password files.
When creating a standby database, the password file from the primary database overwrites the current (temporary) password file on the standby database. When you use the command line and do not duplicate for a standby database, then you need to use the PASSWORD clause (with the FROM ACTIVE DATABASE clause of the RMAN DUPLICATE command).
The primary and standby databases must be using the same version of Oracle Database.


Configuration
The steps described here are to configure the standby database for maximum performance mode, which is the default data protection mode.

Details about this example:
Primary hostname – DCMV-SWDB1D
Standby hostname – CLDV-SWREP1

TNS alias for Primary – SEADEV
TNS alias for standby – SEAREPL

Primary DB_NAME Parameter=SEADEV     Primary DB_UNIQUE_NAME Parameter= SEADEV
Standby DB_NAME Parameter=SEADEV    Standby DB_UNIQUE_NAME Parameter= SEAREPL

NOTE =
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.
The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.


SELECT substr(name,1,30) Name, substr(value,1,30) Value

FROM v$parameter

WHERE name IN  ( ‘db_name’,’db_unique_name’, ‘log_archive_format’, ‘remote_login_passwordfile’)

ORDER BY NAME;

NAME                           VALUE

—————————— —————-

db_name                        SEADEV

db_unique_name                 SEADEV

log_archive_format             ARC%S_%R.%T

remote_login_passwordfile      EXCLUSIVE

On this step we want to be sure that the primary database is configured correctly to support a physical standby database.
You only need to perform these preparatory tasks once. After you complete these steps, the database is prepared to serve as the primary database for one or more standby databases. You should perform the following steps:

1. Primary in archivelog mode and enable FORCE LOGGING on Primary (if these steps are not already on place):
Determine if FORCE LOGGING is enabled on Primary. If it is not, then enable it by using the FORCE LOGGING mode.
This statement may take some time to complete, because it waits for all unlogged direct write I/O to finish. Use SQL*Plus to execute the following commands:
SELECT force_logging FROM v$database;
ALTER DATABASE FORCE LOGGING;
SELECT force_logging FROM v$database;

Note:
We are assuming that the Primary DB is already on archive log mode. If that is not the case, you need to perform the following steps on that Primary box:
archive log list;
shutdown immediate
startup mount;
alter database archivelog;
alter database open;
ALTER DATABASE FORCE LOGGING;


2. Create the Oracle Password file for the Auxiliary Instance
Data Guard uses Oracle Net sessions to transport redo data and control messages between the members of a Data Guard configuration. These redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file.

Create a passwordfile under $ORACLE_HOME/dbs (if that is not already there). Remember that the value of the password needs to be the same as that of the primary database’s password file. I have found that the ignorecase parameter is essential in 11g when putting one of these configurations together.
$ orapwd file= orapwSEADEV password=password ignorecase=y entries=25

Copy the password file from the $ORACLE_HOME/dbs directory on primary server to $ORACLE_HOME/dbs on the standby server.


3. Update listener.ora on Standby machine and tnsnames.ora in both machines
Update listener.ora on Standby machine by adding the following to the SID_LIST_LISTENER section

    (SID_DESC =
      (GLOBAL_DBNAME = SEAREPL)
      (ORACLE_HOME = E:\app\ORACLE_SVC\product\12.1.0\dbhome_1)       (SID_NAME = SEAREPL)
    )

Set up the standby database’s listener with a static reference to the standby database instance. Your listener.ora file will look like:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = SEAPROD)

      (ORACLE_HOME = E:\app\ORACLE_SVC\product\12.1.0\dbhome_1)

      (SID_NAME = SEAPROD)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = SEAREPL)

      (ORACLE_HOME = E:\app\ORACLE_SVC\product\12.1.0\dbhome_1)

      (SID_NAME = SEAREPL)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = CLDV-SWREP1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /home/oracle/app/oracle
LOGGING_LISTENER = OFF




Stop and restart the Listener on the Standby Machine
lsnrctl stop
lsnrctl start

Also, update tnsnames.ora on Standby as well as Primary site with the alias to SEADEV and SEAREPL
 SEADEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SEADEV) )
    )
  )  

SEAREPL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SECONDARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SEAREPL)
    )
  )  


4. Enable Role Transition on Primary DB
If you decide to run the data guard in maximum protection or maximum availability, the transaction are copied over to the standby site by the LGWR and are written to the standby logfiles; this is why we create standby logfiles that will be used not locally, but by the standby. We also recommend to create the standby logfiles so that after a possible switchover, the old primary
may work properly as a standby for any protection mode.
Configure the primary database to receive redo data, by adding the standby logfiles to the primary. You can use the following lines, after validating the directory paths reflect your environment.
NOTE: Always create one additional standby log group than there are online log groups. Also, they must be of the same size than the online log groups.

To check the current size you can use:
select bytes from v$log;

To Check the current Location you can use:
select GROUP# , type , substr(member,1,50)  from v$logfile;

    GROUP# TYPE    SUBSTR(MEMBER,1,50)
———- ——- ————————————
         7 ONLINE  M:\ORADATA\SEADEV\REDO07.LOG
         8 ONLINE  M:\ORADATA\SEADEV\REDO08.LOG
         9 ONLINE  M:\ORADATA\SEADEV\REDO09.LOG
         6 ONLINE  M:\ORADATA\SEADEV\REDO06.LOG

These standby logfiles will be automatically created on the standby DB once we run the RMAN commands. When standby redologs are created, they are listed in V$LOGFILE, but their groups are listed in V$STANDBY_LOG
set echo on
ALTER DATABASE ADD STANDBY LOGFILE ‘M:\ORADATA\SEADEV\REDO01_SEAREPL.LOG ‘SIZE 500m;
ALTER DATABASE ADD STANDBY LOGFILE ‘M:\ORADATA\SEADEV\REDO02_SEAREPL.LOG ‘SIZE 500m;
ALTER DATABASE ADD STANDBY LOGFILE ‘M:\ORADATA\SEADEV\REDO03_SEAREPL.LOG ‘SIZE 500m;
ALTER DATABASE ADD STANDBY LOGFILE ‘M:\ORADATA\SEADEV\REDO04_SEAREPL.LOG ‘SIZE 500m;

select GROUP#, type, substr(member,1,50) from v$logfile order by 1;

    GROUP# TYPE    SUBSTR(MEMBER,1,50)
———- ——- ————————————————–
         1 STANDBY M:\ORADATA\SEADEV\REDO01_SEAREPL.LOG
         2 STANDBY M:\ORADATA\SEADEV\REDO02_SEAREPL.LOG
         3 STANDBY M:\ORADATA\SEADEV\REDO03_SEAREPL.LOG
         4 STANDBY M:\ORADATA\SEADEV\REDO04_SEAREPL.LOG
         6 ONLINE  M:\ORADATA\SEADEV\REDO06.LOG
         7 ONLINE  M:\ORADATA\SEADEV\REDO07.LOG
         8 ONLINE  M:\ORADATA\SEADEV\REDO08.LOG
         9 ONLINE  M:\ORADATA\SEADEV\REDO09.LOG


5. Create init.ora on Standby
Create an init.ora on the Standby machine with just a single line which is the db_name parameter
echo DB_NAME=’SEAREPL’ > $ORACLE_HOME/dbs/initSEAREPL.ora


6. Create directories on secondary server
On the standby system, go to the /u01/app/oracle/admin directory. Create a directory with a name that matches your physical standby SID, if you plan to use a different name, then you will need to use the parameter file_name_convert in your RMAN script.
cd $ORACLE_BASE/admin
mkdir SEAREPL
mkdir SEAREPL/adump
mkdir SEAREPL/dpdump
mkdir SEAREPL/pfile
mkdir SEAREPL/scripts
mkdir SEAREPL/xdb_wallet
mkdir -p $ORACLE_BASE/oradata/SEAREPL
mkdir -p $ORACLE_BASE/diag/rdbms/SEAREPL/SEAREPL/trace
mkdir -p $ORACLE_BASE/diag/rdbms/SEAREPL/SEAREPL/cdump

Note: Depending on how you configured your existing primary database you may need to also create a similar directory in your fast recovery area (i.e. $ORACLE_BASE/fast_recovery_area)
mkdir -p $ORACLE_BASE/flash_recovery_area/SEAREPL


7 . Mount Standby DB
On the standby system, set the ORACLE_SID environment variable and start the instance in NOMOUNT mode with the text initialization parameter file created earlier
export ORACLE_SID=SEAREPL
sqlplus « sys as sysdba »
startup nomount;
or
startup nomount pfile=$ORACLE_HOME/dbs/initSEAREPL.ora


If this is a Windows Machine, then create the Database Service
% set ORACLE_SID=SEAREPL % set ORACLE_HOME=E:\app\ORACLE_SVC\product\12.1.0\dbhome_1
% oradim -NEW -SID SEAREPL


8. On the primary system, Set/Modify some init.ora parameters:
With this command, we are setting up the location of the Archive logs generated by Oracle and we also mention for which database to apply that.

Before we start, let’s go to some definitions from Oracle’s documentation:

  • LOG_ARCHIVE_DEST_n .- It controls different aspects of how redo transport services transfer redo data from primary database destination to a standby. This parameter has several attributes that are needed to setup your Dataguard environment, I will only mention the critical ones:
    • ASYNC .-This is the default, the redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit.
      or
    • SYNC .-The redo data generated by a transaction must have been received by every enabled destination which has this attribute before that transaction can commit.
    • AFFIRM and NOAFFIRM .- Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.
    • DB_UNIQUE_NAME .- Specifies a unique name for the database at this destination. You must specify a name; there is no default value
    • VALID_FOR .-Identifies when redo transport services can transmit redo data to destinations based on the following factors:
      • redo_log_type .-whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination
      • database_role .-whether the database is currently running in the primary or the standby role.
  • FAL_SERVER .-Specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name.
  • FAL_CLIENT .-Specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
  • LOG_ARCHIVE_CONFIG .- Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs. This parameter has several attributes, the most important for this exercise is below
    • DG_CONFIG .- Specifies a list of up to 30 unique database names (defined with the DB_UNIQUE_NAME initialization parameter) for all of the databases in the Data Guard configuration.

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SEADEV’ scope=both;

Here we enable that parameter
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=enable scope=both;

This defines the list of valid DB_UNIQUE_NAME parameters for your DG configuration
SQL> alter system set log_archive_config = ‘dg_config=( SEADEV,SEAREPL )’ scope=both;

This is the main parameter for DG to define redo transportation method
SQL> alter system set log_archive_dest_2 = ‘service=SEAREPL LGWR ASYNC valid_for=(online_logfile,primary_role) db_unique_name=SEAREPL’ scope=both;

Used only for physical standby. It helps by adding/dropping datafiles automatically to the standby if they are added/dropped in the SEADEV DB.
SQL> alter system set standby_file_management = ‘AUTO’ scope=both



9. Execute RMAN
On the primary system, ensure the ORACLE_SID environment variable is set to your primary database. Then start RMAN and connect to the source database as the target connection. The duplicate database instance will be specified in the AUXILIARY connection.
You can invoke the RMAN client on any host as long as that host has connectivity and you can connect to all of the required database instances. If the auxiliary instance requires a text-based initialization parameter file (pfile) then this file must exist and it must reside on the same host that runs the RMAN client application.

RMAN Options:

A brief explanation of the individual clauses is shown below.

  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the primary server to the secondary server.
  • NOFILENAMECHECK: Destination file locations are not checked

NOFILENAMECHECK
Prevents RMAN from checking whether the source database datafiles and online redo logs files share the same names as the duplicated files. This option is necessary when you are creating a duplicate database in a different host that has the same disk configuration, directory structure, and filenames as the host of the source database. If duplicating a database on the same host as the source database, then make sure that NOFILENAMECHECK is not set.
The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if you want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK


SPFILE … SET ‘string_pattern’
Sets the specified initialization parameters to the specified values. You can use SET to set the LOG_FILE_NAME_CONVERT parameter for the online redo logs.
It copies the server parameter file from the source database to the operating system-specific default location for this file on the standby database.
RMAN uses the server parameter file to start the auxiliary instance for standby database creation. Any remaining options of the DUPLICATE command are processed after the database instance is started with the server parameter file.
If you execute DUPLICATE with the SPFILE clause, then the auxiliary instance must already be started with a text-based initialization parameter file. In this case, the only required parameter in the temporary initialization parameter file is DB_NAME, which can be set to any arbitrary value. RMAN copies the binary server parameter file, modifies the parameters based on the settings in the SPFILE clause, and then restarts the standby instance with the server parameter file. When you specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.
If FROM ACTIVE DATABASE is specified on DUPLICATE, then a server parameter file must be in use by the source database instance. If FROM ACTIVE DATABASE is not specified on DUPLICATE, then RMAN restores a backup of the server parameter file to the standby database.


DORECOVER:
Specifies that RMAN should recover the standby database after creating it. If you specify an untilClause, then RMAN recovers to the specified SCN or time and leaves the database mounted.
RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. After RMAN creates the standby database, you must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-only mode.


PARAMETER_VALUE_CONVERT:
Replaces the first string with the second string in all matching initialization parameter values. Note that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are exceptions to this rule and are not affected.
You can use PARAMETER_VALUE_CONVERT to set a collection of initialization parameter values and avoid explicitly setting them all. For example, if the source database uses disk group +ALPHA while the standby database will use +BETA, then you could modify all parameters that refer to these disk groups by specifying SPFILE PARAMETER_VALUE_CONVERT (‘+ALHPA’,’+BETA’).


DB_FILE_NAME_CONVERT ‘string_pattern’
Specifies a rule for creating the filenames for duplicate datafiles and tempfiles. Note that DB_FILE_NAME_CONVERT specified on the DUPLICATE command overrides the initialization parameter DB_FILE_NAME_CONVERT if it is set in the initialization parameter file.

Example:
DUPLICATE TARGET DATABASE TO dup1
  FROM ACTIVE DATABASE
  DB_FILE_NAME_CONVERT ‘/disk1′,’/disk2’
  SPFILE
    PARAMETER_VALUE_CONVERT ‘/disk1’, ‘/disk2’
    SET LOG_FILE_NAME_CONVERT ‘/disk1′,’/disk2’
    SET SGA_MAX_SIZE ‘200M’
    SET SGA_TARGET ‘125M’;

The PARAMETER_VALUE_CONVERT option substitutes /disk2 for /disk1 in all initialization parameters that specify filenames (with the exception of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT). The SET LOG_FILE_NAME_CONVERT clause substitutes /disk2 for /disk1 in the filenames of the online redo logs of the duplicate database. The DB_FILE_NAME_CONVERT option replaces /disk1 with /disk2 in the names of the duplicate datafiles and tempfiles.

% rman target / auxiliary sys@SEAREPL or
% rman target sys/oracle@SEADEV auxiliary sys/oracle@SEAREPL or
% rman
RMAN> connect target sys@SEADEV
RMAN> connect auxiliary sys@SEAREPL

RMAN> run {
    allocate channel prmy1 type disk;
    allocate channel prmy2 type disk;
    allocate channel prmy3 type disk;
    allocate channel prmy4 type disk;
    allocate auxiliary channel stby type disk;
    duplicate target database for standbyfrom active databaseNOFILENAMECHECK
    spfile
        parameter_value_convert ‘SEADEV’,’SEAREPL’
        set db_unique_name=’SEAREPL’
        set db_file_name_convert=’/SEADEV/’,’/SEAREPL/’
        set log_file_name_convert=’/SEADEV/’,’/SEAREPL/’
        set control_files=’/home/oracle/app/oracle/flash_recovery_area/SEAREPL/control02.ctl’,’/home/oracle/app/oracle/oradata/SEAREPL/control01.ctl’
        set log_archive_max_processes=’5′
        set fal_client=’SEAREPL’
        set fal_server=’SEADEV’
        set standby_file_management=’AUTO’
        set log_archive_config=’dg_config=(SEADEV,SEAREPL)’
        SET LOG_ARCHIVE_DEST_2=’service=SEADEV LGWR ASYNC VALID_FOR=(online_logfile,primary_role) db_unique_name=SEADEV’
     ;
     }

These simple RMAN commands will now go off and do all the work you used to do manually to create the standby.
It will perform a live backup of the PRIMARY and a live restore of the STANDBY without an interim storage.
Once it finishes, you will have a fully functioning physical standby DB that is ready to receive redo. Of course, it will not yet be receiving redo nor applying it.

During this process, you can safely ignore the following message on the alert.log file:
PING[ARC2]: Heartbeat failed to connect to standby ‘SEAREPL’. Error is 16058


Nice Example for a Non-DG Duplication
RMAN>run
     {
      allocate channel c1 device type disk;
      allocate auxiliary channel a1 device type disk;
      allocate auxiliary channel a2 device type disk;

      duplicate target database to dupcdb
      from active database
       spfile
         parameter_value_convert   =’prmcdb’,’dupcdb’
        set db_file_name_convert  =’prmcdb’,’dupcdb’
        set log_file_name_convert =’prmcdb’,’dupcdb’
       using backupset;
      }

Description:
We are using 1 “target channels” (1) and 2 “auxiliary channels” , and because target channels is less than auxiliary channels RMN will use the “Pull-Based” method. Also we are using the parameters parameter_value_convert  and  db{log}_file_name_convert.
Finally, we are using the BACKUPSETS, that means that RMAN will use backup sets to duplicate the database (not taking a backup).



More Examples:

In its simplest form, the following command will create the duplicate DB with the SAME structure that the Primary DB:
RMAN> DUPLICATE TARGET DATABASE TO SEAREPL FROM ACTIVE DATABASE;

The following example will perform a conversion of the DB Files:
RMAN> DUPLICATE TARGET DATABASE TO SEAREPL FROM ACTIVE DATABASE DB_FILE_NAME_CONVERT ‘/opt/oradata/SEADEV’,’/opt/oradata/SEAREPL’;

The following example will perform a conversion of the parameter values, DB Files and Log Files:
RMAN> DUPLICATE TARGET DATABASE TO SEAREPL FROM ACTIVE DATABASE
  SPFILE
    PARAMETER_VALUE_CONVERT ‘SEADEV’, ‘SEAREPL’
    SET DB_FILE_NAME_CONVERT ‘SEADEV’,’SEAREPL’
    SET LOG_FILE_NAME_CONVERT ‘SEADEV’,’SEAREPL’;


On this example, we are renaming 1 by 1 the datafile names (we previously query them with select name, FILE# from v$datafile), we also change some init.ora parameters on the auxiliary DB:
run {
set newname for datafile ‘/u01/app/oracle/oradata/SEADEV/users01.dbf’ to ‘/u01/app/oracle/oradata/SEAREPL/users01.dbf’;
set newname for datafile ‘/u01/app/oracle/oradata/SEADEV/undotbs01.dbf’ to ‘/u01/app/oracle/oradata/SEAREPL/undotbs01.dbf’;
set newname for datafile ‘/u01/app/oracle/oradata/SEADEV/sysaux01.dbf’ to ‘/u01/app/oracle/oradata/SEAREPL/sysaux01.dbf’;
set newname for datafile ‘/u01/app/oracle/oradata/SEADEV/system01.dbf’ to ‘/u01/app/oracle/oradata/SEAREPL/system01.dbf’;
duplicate target database to SEAREPL from active database
db_file_name_convert ‘/u01/app/oracle/oradata/SEADEV’ , ‘/u01/app/oracle/oradata/SEAREPL’
spfile parameter_value_convert = ‘/u01/app/oracle/admin/SEADEV’ , ‘/u01/app/oracle/admin/SEAREPL’
set log_file_name_convert = ‘/u01/app/oracle/oradata/SEADEV’,’/u01/app/oracle/oradata/SEAREPL’
set audit_file_dest=’/u01/app/oracle/admin/SEAREPL/adump’
set log_archive_dest_1= »
set memory_target=’183001600′
set control_files=’/u01/app/oracle/oradata/SEAREPL/control01.ctl’,’/u01/app/oracle/oradata/SEAREPL/control02.ctl’,’/u01/app/oracle/oradata/SEAREPL/control03.ctl’
set db_recovery_file_dest_size = ‘2294967296’;
}


On the following example, we will create the auxiliary database to a past point in time. The only difference comes at the end, during the execution of the duplicate command. You must use the until time clause to create an auxiliary database to a past period in time.
RMAN> duplicate target database to SEAREPL
spfile
nofilenamecheck
until time ‘sysdate-1’;


10. Change some init.ora in Primary
Finally the last configuration change to perform in PROD
alter system set fal_server=’SEAREPL’ scope=both;
alter system set fal_client=’SEADEV’ scope=both;

11. Start the Apply Process on the Secondary Database and perform a log switch
In your Standby DB, execute the following (we will be using Real Time Apply):
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Execute a Log Switch on Primary:
alter system switch logfile;

Check the Auxiliary DB
sqlplus « sys as sysdba »
archive log list;
select name from v$datafile;
select name,open_mode from v$database;
show sga

Backup-based Duplication

All RMAN needs here is the location of the backup pieces (previously executed backup) which is accessible and rman reads the backup pieces and restores the spfile,controlfile,datafiles and archivelog files to perform the duplicate operation.

Details about this example:
Primary hostname – PRIMARY_HOST
Standby hostname – SECONDARY_HOST

TNS alias for Primary – SEADEV TNS alias for standby – SEAREPL

Primary DB_NAME Parameter=SEADEV      Primary DB_UNIQUE_NAME Parameter= SEADEV Standby DB_NAME Parameter=SEADEV     Standby DB_UNIQUE_NAME Parameter= SEAREPL

This is very simple to perform:

1- Create a backup of the source DB (if you do not have one already):
$ rman target=/
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

1) Take a backup of the database as follows (if you do not have one already):
RMAN > backup database plus archivelog;

or

RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset format ‘/oracle_backup/%d_%u’ database plus archivelog
include current controlfile;
release channel c1;
release channel c2;
}

2) Make the backup pieces available for duplicate operation.
++ If the duplicate is going to happen on different server, move the backup pieces to a new server using commands like ftp,scp etc.
++ If the duplicate is going to happen on the same server as target, then you can either retain them in the same location where the backup was done or copy it to a required location.


3) Create a password file for the auxiliary instance.
For unix copy the password file from the target database to $ORACLE_HOME/dbs and rename it.
For windows copy the password file from the target database to %ORACLE_HOME/database and rename it.


4) Create a initialization parameter for the auxiliary instance with only one parameter DB_NAME.
Since we are duplicating the database onto a separate server with the same filesystem as the original, we don’t need to convert the file names.
In this case, the PFILE is called « initFGUARD.ora » and is placed in the « $ORACLE_HOME/dbs » directory.

echo DB_NAME=’SEAREPL’ > $ORACLE_HOME/dbs/initSEAREPL.ora

# Convert file names to allow for different directory structure if necessary
#DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/PROD/,/u01/app/oracle/oradata/SEAREPL/)
#LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/PROD/,/u02/app/oracle/oradata/SEAREPL/)


5) Setup the tnsnames.ora file
Add the appropriate entries into the « tnsnames.ora » file in the « $ORACLE_HOME/network/admin » directory to allow connections to the target database from the duplicate server on the Duplicated Server.
SEADEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )  

SEAREPL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SECONDARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SEAREPL)
    )
  )  


6) Create directories (if needed) on Auxiliary Server
On the auxiliary DB, create a directory with a name that matches your SID:
cd $ORACLE_BASE/admin
mkdir SEAREPL mkdir SEAREPL/adump
mkdir SEAREPL/bdump
mkdir SEAREPL/cdump
mkdir SEAREPL/udump
mkdir SEAREPL/dpdump
mkdir SEAREPL/pfile
mkdir SEAREPL/scripts
mkdir -p $ORACLE_BASE/oradata/SEAREPL
mkdir -p $ORACLE_BASE/diag/rdbms/SEAREPL/trace
mkdir -p $ORACLE_BASE/diag/rdbms/SEAREPL/cdump


7) Copy the backup files from the source database to the destination server(optional)
That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use something like:
scp -r oracle@destination_server:/u01/app/oracle/fast_recovery_area/FGUARD/archivelog /u01/app/oracle/fast_recovery_area/FGUARD


8) Now start the auxiliary instance to no mount mode.

Unix Example
Just set the environment variables and start the instance.
% export ORACLE_SID=SEAREPL % export ORACLE_HOME=/home/oracle/ora11g
% export PATH=$ORACLE_HOME/bin:$PATH
% sqlplus « /as sysdba »
SQL > startup nomount


Windows Example
Create a service and then set the necessary environment variables and start the instance.
% oradim -new -sid SEAREPL set ORACLE_SID=SEAREPL set ORACLE_HOME=D:\Orahome\Ora11gr2
set PATH=D:\Orahome\Ora11gr2\bin;%PATH%
% sqlplus « /as sysdba »
SQL > startup nomount


9) Connect to the auxiliary instance from RMAN and perform the rman duplicate as follows:

For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing, we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).
$ export ORACLE_SID=FGUARD
$ rman AUXILIARY /
$ rman TARGET sys/password@SEADEV AUXILIARY /
$ rman CATALOG rman/password@CATALOG AUXILIARY /
$ rman TARGET sys/password@SEADEV CATALOG rman/password@CATALOG AUXILIARY /

RMAN > DUPLICATE DATABASE TO SEAREPL UNTIL TIME « TO_DATE(’29-MAY-2010 14:16:42′,’DD-MON-YYYY HH24:MI:SS’) »
SPFILE
set control_files=’D:\SEAREPL\c1.ctl’
set db_file_name_convert=’D:\Database\Ora11g2\ora11g2′,’d:\SEAREPL’
set log_file_name_convert=’D:\Database\Ora11g2\ora11g2′,’d:\SEAREPL’
BACKUP LOCATION ‘D:\dup11r2’
;

Here use of the BACKUP LOCATION clause identifies the type of duplication as having no target connection, no recovery catalog and being backup-based.

More Examples:
# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET’s current state.
DUPLICATE TARGET DATABASE TO FGUARD SPFILE NOFILENAMECHECK;


# Duplicate database to TARGET’s state 4 days ago.
DUPLICATE TARGET DATABASE TO FGUARD UNTIL TIME ‘SYSDATE-4’ SPFILE NOFILENAMECHECK;


# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO FGUARD SPFILE BACKUP LOCATION ‘/source/app/oracle/fast_recovery_area/FGUARD’ NOFILENAMECHECK;