Duplicate sem conectar no database origem 11gR2 – (RAC para Single)

A Oracle na versão 11gR2 disponibiliza como New Feature a forma de fazer o duplicate sem precisar estar conectado na base de origem. Facilitando muitas vezes o transporte de uma base inteira, como irei mostrar neste teste case de um duplicate RAC 11gR2 para um Single 11gR2.

Meu ambiente que estarei demonstrando será tudo em linux, RAC+ASM para Single Non-ASM.

Primeiramente temos que fazer um backup full do database RAC, no meu caso chamado database: TKRAC11G.

[oracle@rac11g1 backup]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Feb 14 15:07:24 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TKRAC11G (DBID=2569840887)

RMAN> run {
2> allocate channel d1 type disk FORMAT '/ora01/backup/df_%d_%s_%p_%t.dbf' maxpiecesize 3000M;
3> backup tag 'BackupDatabaseFullDiario' database;
4> sql 'alter system archive log current';
5> backup tag 'BackupArchivelogDiario' archivelog all delete input;
6> backup tag 'BackupCurrentControlfile' current controlfile;
7> delete noprompt obsolete ;
8> CROSSCHECK BACKUPSET;
9> CROSSCHECK COPY;
10> crosscheck archivelog all ;
11> DELETE NOPROMPT EXPIRED BACKUP;
12> DELETE NOPROMPT EXPIRED COPY;
13> release channel d1;}

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=145 instance=tkrac11g1 device type=DISK

Starting backup at 14-FEB-12
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DGDADOS/tkrac11g/datafile/tasy_data02.dbf
input datafile file number=00006 name=+DGDADOS/tkrac11g/datafile/tasy_data01.dbf
input datafile file number=00001 name=+DGDICT/tkrac11g/datafile/system.259.758649415
input datafile file number=00003 name=+DGDICT/tkrac11g/datafile/undotbs1.261.758649537
input datafile file number=00004 name=+DGDICT/tkrac11g/datafile/undotbs2.263.758649565
input datafile file number=00002 name=+DGDICT/tkrac11g/datafile/sysaux.260.758649481
input datafile file number=00008 name=+DGINDEX/tkrac11g/datafile/tasy_index01.dbf
input datafile file number=00009 name=+DGINDEX/tkrac11g/datafile/tasy_index02.dbf
input datafile file number=00010 name=+DGDICT/tkrac11g/datafile/teikoadm01.dbf
input datafile file number=00005 name=+DGDICT/tkrac11g/datafile/users.264.758649583
channel d1: starting piece 1 at 14-FEB-12
channel d1: finished piece 1 at 14-FEB-12
piece handle=/ora01/backup/df_TKRAC11G_9_1_775235269.dbf tag=BACKUPDATABASEFULLDIARIO comment=NONE
channel d1: starting piece 2 at 14-FEB-12
channel d1: finished piece 2 at 14-FEB-12
piece handle=/ora01/backup/df_TKRAC11G_9_2_775235269.dbf tag=BACKUPDATABASEFULLDIARIO comment=NONE
channel d1: starting piece 3 at 14-FEB-12
channel d1: finished piece 3 at 14-FEB-12
piece handle=/ora01/backup/df_TKRAC11G_9_3_775235269.dbf tag=BACKUPDATABASEFULLDIARIO comment=NONE
channel d1: backup set complete, elapsed time: 00:15:28
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 14-FEB-12
channel d1: finished piece 1 at 14-FEB-12
piece handle=/ora01/backup/df_TKRAC11G_10_1_775236200.dbf tag=BACKUPDATABASEFULLDIARIO comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 14-FEB-12

sql statement: alter system archive log current

Starting backup at 14-FEB-12
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=395 RECID=489 STAMP=775236217
input archived log thread=2 sequence=218 RECID=490 STAMP=775236219
input archived log thread=2 sequence=219 RECID=492 STAMP=775236223
input archived log thread=1 sequence=396 RECID=491 STAMP=775236220
channel d1: starting piece 1 at 14-FEB-12
channel d1: finished piece 1 at 14-FEB-12
piece handle=/ora01/backup/df_TKRAC11G_11_1_775236224.dbf tag=BACKUPARCHIVELOGDIARIO comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
channel d1: deleting archived log(s)
archived log file name=+DGINDEX/tkrac11g/archivelog/2012_02_14/thread_1_seq_395.271.775236215 RECID=489 STAMP=775236217
archived log file name=+DGINDEX/tkrac11g/archivelog/2012_02_14/thread_2_seq_218.270.775236219 RECID=490 STAMP=775236219
archived log file name=+DGINDEX/tkrac11g/archivelog/2012_02_14/thread_2_seq_219.269.775236223 RECID=492 STAMP=775236223
archived log file name=+DGINDEX/tkrac11g/archivelog/2012_02_14/thread_1_seq_396.268.775236221 RECID=491 STAMP=775236220
Finished backup at 14-FEB-12

Starting backup at 14-FEB-12
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
channel d1: starting piece 1 at 14-FEB-12
channel d1: finished piece 1 at 14-FEB-12
piece handle=/ora01/backup/df_TKRAC11G_12_1_775236229.dbf tag=BACKUPCURRENTCONTROLFILE comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-FEB-12

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1

no obsolete backups found

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/ora01/backup/df_TKRAC11G_9_1_775235269.dbf RECID=15 STAMP=775235274
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/ora01/backup/df_TKRAC11G_9_2_775235269.dbf RECID=16 STAMP=775235810
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/ora01/backup/df_TKRAC11G_9_3_775235269.dbf RECID=17 STAMP=775236155
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/ora01/backup/df_TKRAC11G_10_1_775236200.dbf RECID=18 STAMP=775236204
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/ora01/backup/df_TKRAC11G_11_1_775236224.dbf RECID=19 STAMP=775236224
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/ora01/backup/df_TKRAC11G_12_1_775236229.dbf RECID=20 STAMP=775236232
Crosschecked 6 objects

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

specification does not match any archived log in the repository

specification does not match any backup in the repository

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

released channel: d1

RMAN>

Feito o backup full conforme mostrado a cima agora vamos copiar remotamente para o servidor de destino, no meu caso servidor tkora11g.

[oracle@rac11g1 backup]$ ls -ltr /ora01/backup/
total 6488684
drwxr-xr-x 2 oracle oinstall       4096 Aug  6  2011 log
-rw-r----- 1 oracle oinstall 3145728000 Feb 14 15:16 df_TKRAC11G_9_1_775235269.dbf
-rw-r----- 1 oracle oinstall 3145728000 Feb 14 15:22 df_TKRAC11G_9_2_775235269.dbf
-rw-r----- 1 oracle oinstall  301367296 Feb 14 15:23 df_TKRAC11G_9_3_775235269.dbf
-rw-r----- 1 oracle oinstall   19169280 Feb 14 15:23 df_TKRAC11G_10_1_775236200.dbf
-rw-r----- 1 oracle oinstall    6764032 Feb 14 15:23 df_TKRAC11G_11_1_775236224.dbf
-rw-r----- 1 oracle oinstall   19136512 Feb 14 15:23 df_TKRAC11G_12_1_775236229.dbf

[oracle@rac11g1 backup]$ scp df_TKRAC11G_* 192.168.10.105:/ora02/backup
oracle@192.168.10.105's password:
df_TKRAC11G_10_1_775236200.dbf                                                                                          100%   18MB  18.3MB/s   00:01
df_TKRAC11G_11_1_775236224.dbf                                                                                          100% 6606KB   6.5MB/s   00:00
df_TKRAC11G_12_1_775236229.dbf                                                                                          100%   18MB  18.3MB/s   00:01
df_TKRAC11G_9_1_775235269.dbf                                                                                           100% 3000MB  13.5MB/s   03:42
df_TKRAC11G_9_2_775235269.dbf                                                                                           100% 3000MB  14.1MB/s   03:33
df_TKRAC11G_9_3_775235269.dbf                                                                                           100%  287MB  14.4MB/s   00:20

Feito a copia dos arquivos do backup da base RAC vamos conectar no servidor tkora11g (single).

Variaveis de ambiente do usuario oracle no servidor destino tkora11g (single):

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/ora02/app; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=dbdup; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
GGATE=$ORACLE_BASE/oracle/product/gg
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$GGATE; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Carregado as variaveis de ambiente, temos que criar um pfile para essa base, a base se chamará DBDUP:

[oracle@tkora11g dbs]$ cat $ORACLE_HOME/dbs/initdbdup.ora
*.db_cache_size=113246208
*.java_pool_size=4194304
*.large_pool_size=4194304
*.oracle_base='/ora02/app'#ORACLE_BASE set from environment
*.pga_aggregate_target=159383552
*.sga_target=264241152
*.shared_io_pool_size=0
*.shared_pool_size=134217728
*.streams_pool_size=0
*.audit_file_dest='/ora02/app/admin/dbdup/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/ora02/app/oradata/dbdup/control01.ctl','/ora02/app/oradata/dbdup/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dbdup'
*.diagnostic_dest='/ora02/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbdupXDB)'
*.log_archive_dest_1='LOCATION=/ora02/archives/dbdup/'
*.log_archive_format='dbdup_%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DGDICT/tkrac11g/datafile/','/ora02/oradata/dbdup/','+DGINDEX/tkrac11g/datafile/','/ora02/oradata/dbdup/','+DGDADOS/tkrac11g/datafile/','/ora02/oradata/dbdup/','+DGDICT/tkrac11g/tempfile/','/ora02/oradata/dbdup/'
*.log_file_name_convert='+DGINDEX/tkrac11g/onlinelog/','/ora02/oradata/dbdup/','+DGDICT/tkrac11g/onlinelog/','/ora02/oradata/dbdup/'

Pode notar que no final do arquivo há os parametros db_file_name_convert e log_file_name_convert que devem ser adicionados a base no caso que estou convertendo um RAC+ASM para um Single Non-ASM.

Criado o pfile para iniciarmos a base, não podemos de esquecer de criar os diretorios informados no init principalmente.

Agora iniciando o banco em modo nomount:

SQL> startup nomount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1344616 bytes
Variable Size             301992856 bytes
Database Buffers          113246208 bytes
Redo Buffers                6086656 bytes
SQL> exit

Iniciado a base em modo nomount vamos para o processo mais complexo desse artigo, o duplicate.
Pode ser notado aqui, que o backup não precisa estar no mesmo local que foi gerado na origem, como era feito nas versões anteriores.

DUPLICATE DATABASE TO DBDUP BACKUP LOCATION '/ora02/backup/';

Segue a sequencia do meu case:

[oracle@tkora11g backup]$ rman auxiliary /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Feb 14 15:40:02 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: DBDUP (not mounted)

RMAN> DUPLICATE DATABASE TO DBDUP BACKUP LOCATION '/ora02/backup/';

Starting Duplicate Db at 14-FEB-12

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                306187160 bytes
Database Buffers             109051904 bytes
Redo Buffers                   6086656 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TKRAC11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DBDUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/ora02/backup/df_TKRAC11G_12_1_775236229.dbf';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TKRAC11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DBDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                306187160 bytes
Database Buffers             109051904 bytes
Redo Buffers                   6086656 bytes

Starting restore at 14-FEB-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/ora02/app/oradata/dbdup/control01.ctl
output file name=/ora02/app/oradata/dbdup/control02.ctl
Finished restore at 14-FEB-12

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   set until scn  21387985;
   set newname for datafile  1 to
 "/ora02/oradata/dbdup/system.259.758649415";
   set newname for datafile  2 to
 "/ora02/oradata/dbdup/sysaux.260.758649481";
   set newname for datafile  3 to
 "/ora02/oradata/dbdup/undotbs1.261.758649537";
   set newname for datafile  4 to
 "/ora02/oradata/dbdup/undotbs2.263.758649565";
   set newname for datafile  5 to
 "/ora02/oradata/dbdup/users.264.758649583";
   set newname for datafile  6 to
 "/ora02/oradata/dbdup/tasy_data01.dbf";
   set newname for datafile  7 to
 "/ora02/oradata/dbdup/tasy_data02.dbf";
   set newname for datafile  8 to
 "/ora02/oradata/dbdup/tasy_index01.dbf";
   set newname for datafile  9 to
 "/ora02/oradata/dbdup/tasy_index02.dbf";
   set newname for datafile  10 to
 "/ora02/oradata/dbdup/teikoadm01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-FEB-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /ora02/oradata/dbdup/system.259.758649415
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora02/oradata/dbdup/sysaux.260.758649481
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora02/oradata/dbdup/undotbs1.261.758649537
channel ORA_AUX_DISK_1: restoring datafile 00004 to /ora02/oradata/dbdup/undotbs2.263.758649565
channel ORA_AUX_DISK_1: restoring datafile 00005 to /ora02/oradata/dbdup/users.264.758649583
channel ORA_AUX_DISK_1: restoring datafile 00006 to /ora02/oradata/dbdup/tasy_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /ora02/oradata/dbdup/tasy_data02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /ora02/oradata/dbdup/tasy_index01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /ora02/oradata/dbdup/tasy_index02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /ora02/oradata/dbdup/teikoadm01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /ora02/backup/df_TKRAC11G_9_1_775235269.dbf
channel ORA_AUX_DISK_1: piece handle=/ora02/backup/df_TKRAC11G_9_1_775235269.dbf tag=BACKUPDATABASEFULLDIARIO
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: reading from backup piece /ora02/backup/df_TKRAC11G_9_2_775235269.dbf
channel ORA_AUX_DISK_1: piece handle=/ora02/backup/df_TKRAC11G_9_2_775235269.dbf tag=BACKUPDATABASEFULLDIARIO
channel ORA_AUX_DISK_1: restored backup piece 2
channel ORA_AUX_DISK_1: reading from backup piece /ora02/backup/df_TKRAC11G_9_3_775235269.dbf
channel ORA_AUX_DISK_1: piece handle=/ora02/backup/df_TKRAC11G_9_3_775235269.dbf tag=BACKUPDATABASEFULLDIARIO
channel ORA_AUX_DISK_1: restored backup piece 3
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:09:42
Finished restore at 14-FEB-12

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=775237836 file name=/ora02/oradata/dbdup/system.259.758649415
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=775237836 file name=/ora02/oradata/dbdup/sysaux.260.758649481
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=775237836 file name=/ora02/oradata/dbdup/undotbs1.261.758649537
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=775237836 file name=/ora02/oradata/dbdup/undotbs2.263.758649565
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=775237836 file name=/ora02/oradata/dbdup/users.264.758649583
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=775237836 file name=/ora02/oradata/dbdup/tasy_data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=775237836 file name=/ora02/oradata/dbdup/tasy_data02.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=775237836 file name=/ora02/oradata/dbdup/tasy_index01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=775237836 file name=/ora02/oradata/dbdup/tasy_index02.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=20 STAMP=775237836 file name=/ora02/oradata/dbdup/teikoadm01.dbf

contents of Memory Script:
{
   set until scn  21387985;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-12
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=395
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=218
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=219
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=396
channel ORA_AUX_DISK_1: reading from backup piece /ora02/backup/df_TKRAC11G_11_1_775236224.dbf
channel ORA_AUX_DISK_1: piece handle=/ora02/backup/df_TKRAC11G_11_1_775236224.dbf tag=BACKUPARCHIVELOGDIARIO
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/ora02/archives/dbdup/dbdup_1_395_758649399.arc thread=1 sequence=395
archived log file name=/ora02/archives/dbdup/dbdup_2_218_758649399.arc thread=2 sequence=218
channel clone_default: deleting archived log(s)
archived log file name=/ora02/archives/dbdup/dbdup_2_218_758649399.arc RECID=3 STAMP=775237839
archived log file name=/ora02/archives/dbdup/dbdup_2_219_758649399.arc thread=2 sequence=219
channel clone_default: deleting archived log(s)
archived log file name=/ora02/archives/dbdup/dbdup_1_395_758649399.arc RECID=4 STAMP=775237839
archived log file name=/ora02/archives/dbdup/dbdup_1_396_758649399.arc thread=1 sequence=396
channel clone_default: deleting archived log(s)
archived log file name=/ora02/archives/dbdup/dbdup_1_396_758649399.arc RECID=1 STAMP=775237839
channel clone_default: deleting archived log(s)
archived log file name=/ora02/archives/dbdup/dbdup_2_219_758649399.arc RECID=2 STAMP=775237839
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-FEB-12
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                306187160 bytes
Database Buffers             109051904 bytes
Redo Buffers                   6086656 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DBDUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DBDUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                306187160 bytes
Database Buffers             109051904 bytes
Redo Buffers                   6086656 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBDUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      584
 LOGFILE
  GROUP  1 ( '/ora02/oradata/dbdup/redo01a.log', '/ora02/oradata/dbdup/redo01b.log' ) SIZE 100 M  REUSE,
  GROUP  2 ( '/ora02/oradata/dbdup/redo02b.log', '/ora02/oradata/dbdup/redo02a.log' ) SIZE 100 M  REUSE,
  GROUP  5 ( '/ora02/oradata/dbdup/redo05b.log', '/ora02/oradata/dbdup/redo05a.log' ) SIZE 100 M  REUSE,
  GROUP  6 ( '/ora02/oradata/dbdup/redo06b.log', '/ora02/oradata/dbdup/redo06a.log' ) SIZE 100 M  REUSE,
  GROUP  9 ( '/ora02/oradata/dbdup/redo09a.log', '/ora02/oradata/dbdup/redo09b.log' ) SIZE 100 M  REUSE
 DATAFILE
  '/ora02/oradata/dbdup/system.259.758649415'
 CHARACTER SET WE8MSWIN1252

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'
  GROUP  3 ( '/ora02/oradata/dbdup/group_3.265.758650261' ) SIZE 50 M  REUSE,
  GROUP  4 ( '/ora02/oradata/dbdup/redo04b.log', '/ora02/oradata/dbdup/redo04a.log' ) SIZE 100 M  REUSE,
  GROUP  7 ( '/ora02/oradata/dbdup/redo07a.log', '/ora02/oradata/dbdup/redo07b.log' ) SIZE 100 M  REUSE,
  GROUP  8 ( '/ora02/oradata/dbdup/redo08b.log', '/ora02/oradata/dbdup/redo08a.log' ) SIZE 100 M  REUSE

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/ora02/oradata/dbdup/temp.262.758649555";
   switch clone tempfile all;
   catalog clone datafilecopy  "/ora02/oradata/dbdup/sysaux.260.758649481",
 "/ora02/oradata/dbdup/undotbs1.261.758649537",
 "/ora02/oradata/dbdup/undotbs2.263.758649565",
 "/ora02/oradata/dbdup/users.264.758649583",
 "/ora02/oradata/dbdup/tasy_data01.dbf",
 "/ora02/oradata/dbdup/tasy_data02.dbf",
 "/ora02/oradata/dbdup/tasy_index01.dbf",
 "/ora02/oradata/dbdup/tasy_index02.dbf",
 "/ora02/oradata/dbdup/teikoadm01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /ora02/oradata/dbdup/temp.262.758649555 in control file

cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/sysaux.260.758649481 RECID=1 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/undotbs1.261.758649537 RECID=2 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/undotbs2.263.758649565 RECID=3 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/users.264.758649583 RECID=4 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/tasy_data01.dbf RECID=5 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/tasy_data02.dbf RECID=6 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/tasy_index01.dbf RECID=7 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/tasy_index02.dbf RECID=8 STAMP=775237874
cataloged datafile copy
datafile copy file name=/ora02/oradata/dbdup/teikoadm01.dbf RECID=9 STAMP=775237874

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=775237874 file name=/ora02/oradata/dbdup/sysaux.260.758649481
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=775237874 file name=/ora02/oradata/dbdup/undotbs1.261.758649537
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=775237874 file name=/ora02/oradata/dbdup/undotbs2.263.758649565
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=775237874 file name=/ora02/oradata/dbdup/users.264.758649583
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=775237874 file name=/ora02/oradata/dbdup/tasy_data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=775237874 file name=/ora02/oradata/dbdup/tasy_data02.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=775237874 file name=/ora02/oradata/dbdup/tasy_index01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=775237874 file name=/ora02/oradata/dbdup/tasy_index02.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=775237874 file name=/ora02/oradata/dbdup/teikoadm01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-FEB-12

E Voilá ! A base está duplicada com o nome DBDUP, sem precisar conexão com catalog algum, sem conexão com a base de origem !
Muito simples, fácil e rápido dependendo é claro do tamanho da base !

%name Duplicate sem conectar no database origem 11gR2   (RAC para Single)

Autor: Rafael Stoever

Bacharel em Sistema de Informação pela Uniasselvi, atualmente cursando Gerenciamento de Projetos em TI pela Pós Graduação Uniasselvi. Atuo como Analista de suporte a banco de dados – DBA pela Lumina Serviços em TI residente de Blumenau/ SC, OPN Certified Specialist, Certificado OCP 10g/11g/12c, OCE RAC10g e Linux 10g. Conhecimentos em Microsoft SqlSever, Mysql e programação web (php,asp).