How to perform a clone from active database

With Oracle 11g, a new method to clone databases was introduced: "Clone from Active Database".
This method allows for cloning of a database using RMAN's DUPLICATE command, but without being dependent on any previous backups being available.

The following are my notes on how to use this feature to produce a clone from production database PRODDB01 to test database TESTDB01.

In file run_duplication.cmd:

connect catalog cataloguser/password@RMANCAT
connect target sys/clonedb@PRODDB01
connect auxiliary sys/clonedb@TESTDB01;
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
run {
debug io;
DUPLICATE TARGET DATABASE TO TESTDB01
FROM ACTIVE DATABASE
SKIP TABLESPACE TOOLS;
debug off;
}
exit

to execute:

rman cmdfile='run_duplication.cmd' debug trace='run_duplication.trc' log='run_duplication.log'


For this to work smoothly I followed the principles below:

• Script executed from the target server
• Auxiliary database must be in NOMOUNT mode
• Sys passwords must be identical on both source and target.
• Use the notation uid/pwd@<ORACLE_SID> when you Connect to both target and auxiliary. Do not use operating system authenticaion ( connect / ) - it will throw an error
• The db_block_size parameter must be identical on both source and target
• TNS connection must exist on both sides e.g. communication must be able to go from and from the source and the target. If not you will see the error

RMAN-03002: failure of Duplicate Db command at 07/21/2015 10:06:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on c1 channel at 07/21/2015 10:06:15
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

• I set the db_file_name_convert and the log_file_name_convert parameter in the auxiliary database. Make sure you check the location of the tempfiles, too, and include this in the db_file_name_convert value.
• I had no success using the TABLESPACE clause (which means an implicit EXCLUDE of all other tablespaces except those named explicitly)
• Using SKIP TABLESPACE ,, etc works, if the tablespaces are self-contained, meaning there are no referential constraints from object in one tablespace to another (even if both are skipped!)

Remember to reregister the duplicated database if it's backed up using a catalog database:

RMAN> connect target /
RMAN> list incarnation of database;
RMAN> register database;
RMAN> list incarnation of database;


The prerequisites for ACTIVE DATABASE cloning as laid out in the Oracle documentation are:

• At least one normal target channel and at least one AUXILIARY channel are required.
• When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication.
• The source database must be mounted or open. If the source database is open, then archiving must be enabled.
• If the source database is not open, then it must have been shut down consistently.
• When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.
• The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files.
• You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

How to shrink a datafile in a temporary tablespace

alter tablespace TEMP shrink tempfile '/data/oracle/PRODDB01/datafiles/temp_20.dbf' KEEP 8G;

This feature was introduced in Oracle 11g.

How does the 11gR2 feature "deferred segment creation" work?

Starting from 11gR2 onwards, Oracle introduced a feature called deferred segment creation. This means that Oracle will not need to create any segments for a table until you actually insert rows into it.

Oracle creates all needed metadata about the new table in data dictionary, but doesn’t actually allocate any space from the tablespace. This applies to other segment types like index and table/index partitions as well.

Syntax:

-- Without deffered segment creation
CREATE TABLE TEST(tab_id number)
SEGMENT CREATION IMMEDIATE
TABLESPACE USERS;

-- With deffered segment creation
CREATE TABLE TEST (tab_id number)
SEGMENT CREATION DEFERRED
TABLESPACE USERS;


The parameter deferred_segment_creation controls the default behavior:

SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE

How to make a table read only (11g only)

ALTER TABLE table_name READ ONLY;

The command would make the table read-only even for its owner.

To reverse the operation:

ALTER TABLE table_name READ WRITE;