RMAN clonning with Duplicate database option

rman target sys/abc123@ibpausa1 catalog rman/rman_oncdbmp@rmanmna2 auxiliary /

run
{
set until time "to_date('30-APR-2010 05:30','DD-MON-YYYY hh24:MI')";
allocate auxiliary channel C1 device type disk;
SET NEWNAME FOR DATAFILE 1 to '/oracle/data/dbpa/usa1/vol30/dbpausa1.system.a001.dbf';
SET NEWNAME FOR DATAFILE 2 to '/oracle/data/dbpa/usa1/vol40/dbpausa1.undo.a001.dbf';
SET NEWNAME FOR DATAFILE 3 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.sbpa0001.a001.dbf';
SET NEWNAME FOR DATAFILE 4 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.users.a001.dbf';
SET NEWNAME FOR DATAFILE 5 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.tools.a001.dbf';
SET NEWNAME FOR DATAFILE 6 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a001.dbf';
SET NEWNAME FOR DATAFILE 7 to '/oracle/data/dbpa/usa1/vol20/dbpausa1.xbpa0001.a001.dbf';
SET NEWNAME FOR DATAFILE 9 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a002.dbf';
SET NEWNAME FOR DATAFILE 10 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a003.dbf';
SET NEWNAME FOR DATAFILE 11 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.sysaux.a001.dbf';
SET NEWNAME FOR TEMPFILE 1 to '/oracle/data/dbpa/usa1/vol50/dbpausa1.tps00001.a001.dbf';
duplicate target database to dbpausa1;
}

log_file_name_convert =("/oracle/data/ibpa/usa1/", "/oracle/data/dbpa/usa1/")
db_file_name_convert =("/oracle/data/ibpa/usa1/", "/oracle/data/dbpa/usa1/")


ORACLE noteid's:


Note 228257.1 - RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G
Note 259694.1 - Oracle10G RMAN Database Duplication
Bug 5327865 - CLONING THE DB USING 10G OEM "SEARCH FOR HOST" RESULTS IN (NO ITEMS FOUND)

Clonning Pre steps..........................

PRE -STEPS
set lines 200
set pages 9999
col owner for a20
col db_link for a30
col username for a15
col host for a40
col created for a12
spool db_details.lst
select name from v$database;
select * from global_name;
select * from dba_db_links;
select name from v$controlfile;
select member from v$logfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;
spool off
spool create_db_links.sql
select 'create DATABASE LINK '||NAME|| ' connect to '|| userid || ' identified by '|| password || ' using '||''''|| host ||''''||'; ' FROM
sys.link$ where name='EXTPROC_CONNECTION.CISCO.COM';
spool off
spool alter_user.sql
select ' alter user '||username||' identified by values xxxxx'||password|| 'xxxxx;' from dba_users;
spool off
Set verify off
Set space 0
set feedback off;
set echo off;
set pages 1000;
set lines 150;
spool create_synonym.sql
Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR mailto:'%7C%7Ctable_owner%7C%7C'.'%7C%7Ctable_name%7C%7C'@'%7C%7Cdb_link%7C%7C';' from dba_synonyms where db_link
is not null and table_owner is not null;
Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR mailto:'%7C%7Ctable_name%7C%7C'@'%7C%7Cdb_link%7C%7C';' from dba_synonyms where db_link is not null and
table_owner is null;
spool off;

select count(1) from dba_synonyms where db_link is not null and table_owner is null;

restore database , catalog start with option

catalog start with

CATALOG START WITH '/fs1/datafiles/';

connect target /;
startup force nomount;
restore spfile from '${CTL_PIECE}';
shutdown immediate;
startup nomount;
restore controlfile from '${CTL_PIECE}';
shutdown immediate;
startup mount;
catalog start with '${BACKUP_LOC}' noprompt;
restore database;
recover database;
sql 'alter database open resetlogs';
EOF

RMAN restore commands

restore archivelog from logseq=100 until logseq=110;
restore archivelog from time='15-JUN-05' until time='20-JUN-05';

run {
set archivelog destination to '/oracle/data/dsed/mna1/vol90/';
allocate channel ch1 type disk;
restore archivelog
from logseq 51066
until logseq 51076;
}

run {
set archivelog destination to '';
allocate channel ch1 type disk;
restore archivelog
from logseq 49751
until logseq 49770;
}

RMAN list commands

IST BACKUP;
LIST BACKUP OF DATABASE;
LIST BACKUP SUMMARY;
LIST INCARNATION;
LIST BACKUP BY FILE;
LIST COPY OF DATABASE ARCHIVELOG ALL;
LIST COPY OF DATAFILE 1, 2, 3;
LIST BACKUP OF DATAFILE 11 SUMMARY;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
LIST BACKUPSET OF DATAFILE 1;
LIST BACKUP OF ARCHIVELOG all;
LIST EXPIRED BACKUP;

 list backup of database completed between '01-MAY-07' and '07-MAY-07' ;

 list backup of database;

 list backup of database summary completed between '01-MAY-07' and '07-MAY-07';

 list backup of archivelog all;

list backup of archivelog from time  '05-MAY-07';

list backup of archivelog high sequence=10796;

list backup of archivelog low sequence=10796;
      LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 10966;
list backup of archivelog from time '26-JUN-07';
backup archivelog until time 'SYSDATE-6' delete input;
backup archivelog sequence between 6 and 29 delete input;
list backup of archivelog time between '17-JUN-07' and '18-JUN-07';
list backup of database summary TAG 'clone_db_bk_full';
list backupset completed between '25-OCT-07' and '30-OCT-07' TAG 'clone_db_bk_full';

To get the report in HTML format

conn as sysdba SQL> set markup html on;
spool 1212703_preprod.xls

Get DDL ................

set verify off
set long 100000
set lines 132
set pages 0
prompt 'Pl Enter the Input in Capital Letters............'
accept owner char prompt 'Enter Owner : '
accept object_type char prompt 'Enter Object Type [TABLE/INDEX/MATERIALIZED VIEW/PACKAGE/PROCEDURE/FUNCTION] : '
accept object_name char prompt 'Enter Object Name : '
spool get_ddl
select dbms_metadata.get_ddl('&&object_type','&&object_name','&&owner') from dual;
spool off