Duplicate database from active without backup
                                                 Last update (2016-12-20 19:19:23)
                                                                                                                   Date added (2013-09-18 08:30:11)

Summary
RMAN can be used to duplicate, or clone, a database from a previous full online backup.

In the Oracle11g there is a new feature where you can do the same clone without using a previous full backup, but immediately from the production database.

The advantage of this feature is that you don't need anymore RMAN catalog database and a backup manager (like VERITAS) and tapes. The only thing that you need is the source DB to be in archivelog mode and a network connection in case the target is located in different server than the source. The clone can be done in the same server or on a remote server.

Here I will demonstrate how to do it, with Oracle11g and RMAN and on a remote server with different filesystems.

Prepare the environment on the remote server
In order to make the clone on the new remote server you must set up:
1. A new unix user with the same privileges like the production unix user. 
   A profile for the unix user with $ORACLE_HOME, $PATH, $TNS_ADMIN, $ORACLE_SID enviroment    
   variables

2. Copy the $ORACLE_HOME binaries from the production server to the new remote server.
    This will be your new $ORACLE_HOME for the clone. Relink to make it valid!

3. Add a new listener.ora on the remote server (duplicate) and start the listener
    $ lsnrctl start DUP

   A listener.ora example configuration file follows:

SID_LIST_DUP =
  (SID_LIST =
      (SID_DESC =
            (GLOBAL_DBNAME = DUP)
                  (ORACLE_HOME = /orandr/ANDR/11.2.0)
                        (SID_NAME = DUP)
                            )
                              )

DUP =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rp1dev)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER_DUP = /orandr/ANDR/11.2.0

4. Add tnsnames alias entries for production database and duplicate database. Both servers must have 
connection strings to each database. The source database must have tnsnames alias for the source 
and clone database and the clone database must have alias for source and clone database.

    In the example the production database is ANDR
    and the Duplicate will be DUP database.

ANDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = crmcc)(PORT = 1521))
    (CONNECT_DATA =
      (SID = ANDR)))

DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rp1dev)(PORT = 1551))
    (CONNECT_DATA =
      (SID = DUP)))

Check that you can tnsping the aliases from both machines. 
For example: The production must be able to tnsping the duplicate and the duplicate the production!

$ tnsping ANDR

5. Create on the new server (duplicate) the paths for the following files:
    a. directories for the new controlfiles
    b. directories for the new datafiles
    c. directories for the new redologs
    d. directories for the new trace files

6. Create a new init.ora file for the process on the remote server at $ORACLE_HOME/dbs location
   Now you have to create the initDUP.ora, at $ORACLE_HOME/dbs. Follow the example:

db_name = "DUP"
instance_name = DUP
control_files = (/orandr/ANDR/controlfiles/control01.ctl, /orandr/ANDR/controlfiles/control02.ctl)
db_file_name_convert=(/orandr/ANDR/datafiles/,/orandr/ANDR/datafiles_new/)
log_file_name_convert=(/orandr/ANDR/redologs/,/orandr/ANDR/redologs_new/)
db_block_size = 8192
compatible = 11.2.0.0.0
diagnostic_dest='/orandr/ANDR/11.2.0'
undo_management = 'AUTO'
processes=500
sessions=550

Tip: The parameters compatible and db_block_size must the the same as production database.

control_files is where the new controlfiles will be created for the duplicate database.

db_file_name_convert are the matching pairs of production database filesystems to the duplicate db filesystems.

for example if at the production database server the datafiles where stored in 3 filesystems:

/production/oradata1/
/production/oradata2/
/production/oradata3/

then you must create 3 filesystems also in the new server (clone), lets say:

/clone/oradata1/
/clone/oradata2/
/clone/oradata3/

and the parameter db_file_name_convert will be:

db_file_name_convert=(/production/oradata1/, /clone/oradata1/, 
/production/oradata2/, /clone/oradata2/, 
/production/oradata3/, /clone/oradata3/)

log_file_name_convert are the matching pairs of production redologs filesystems to the clone redolog filesystems. 
It is the same concept like the parameter db_file_name_convert.

7. Copy the password file from the source system to the target system. After that the sys password for the duplicate db will be the same as the source db. If the password file does not exist in the source db, then create it.

$ orapwd file=/orandr/ANDR/11.2.0/dbs/orapwANDR password=***** entries=10

Now it is time to create the duplicate database. On the remote server (duplicate) execute the following:
sqlplus "/ as sysdba"
sql>startup nomount pfile=$ORACLE_HOME/dbs/initDUP.ora
exit sqlplus and run from the shell
rman target sys/*****@ANDR
rman> connect auxiliary sys/same_password_as_source@DUP
rman> configure device type disk parallelism 4 backup type to backupset;
rman> duplicate target database to DUP from active database;
The duplicate procedure starts transferring the datafiles in parallel (in this example 4 datafiles)
from the source system to the target system. The order of the copy it is based on the size of the datafiles. First are the largest datafiles.
select * from dba_data_files 
order by bytes desc, file_id asc;
The last step in the duplication process is to apply all the archivelogs that have been produced while you are copying the datafiles from the source to the target.

Very important tip: Do not delete the archives from the source while the datafiles are being copied. They are needed for the recovery of the duplicate db. The duplicate command applies them, so you don't have to do something extra.

When you reach the point where the archives are being applied, you can use the following query to check how quickly the database is being moving forward:
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start", 
to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" 
from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search