Search This Blog

Tuesday, May 28, 2013

11gR2 RAC (ASM) to Single Instance (NON-ASM) Cloning.

Source = 2 node rac cluster with asm  SID=dell
Target = single instance non asm      SID=dup(to be made)

Notes:

1) For cloning Rman active database duplication will be used,
   that means no backups are required(11gR2 feature). 

Other Methods which can be used for duplication using RMAN.

1.1) Duplication without connection to target(11gR2 Feature).
1.2) Backup and recreate database manually.
1.3) Rman duplication using backups.

2) Since source database is OMF managed target will be OMF managed as well.

   following parameters will be used to achieve that.

2.1) db_create_file_dest---------------->> This directory will contain data,redo & control files.
2.2) db_recovery_file_dest-------------->> This will contain Flash recover Area.

3) In case where source is NON-OMF managed use following parameters : -

3.1) control_files
3.2) db_file_name_convert
3.3) log_file_name_convert 


Source

1) Make sure rac database is in archive log mode.
2) Copy password file to target.

[oracle@rac1 dbs]$ scp orapwdell1 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdup
oracle@192.168.1.69's password:
orapwdell1                                        100% 1536     1.5KB/s   00:00

3) make tnsnames.ora entry using "NETCA" as "Oracle" user
   so that a connection could be made to target.


Sample on both nodes:

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora.rac1 Network Configuration File: /u01/app/oracle/product/11.2.0/
dbhome_1/network/admin/tnsnames.ora.rac1
# Generated by Oracle configuration tools.

DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dup)
    )
  )

DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dell.example.com)
    )
  )




   Copy the modified tnsnames.ora file to target.

[oracle@rac1 admin]$ scp tnsnames.ora 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/network/admin/
oracle@192.168.1.69's password:
tnsnames.ora                                                100%  533     0.5KB/s   00:00


4) Make pfile out of spfile and modify it for single instance
   cloning then copy it to target.


Original:

[oracle@rac1 oracle]$ cat initdup.ora

dell1.__db_cache_size=29360128
dell2.__db_cache_size=29360128
dell1.__java_pool_size=4194304
dell2.__java_pool_size=4194304
dell1.__large_pool_size=4194304
dell2.__large_pool_size=4194304
dell1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dell1.__pga_aggregate_target=109051904
dell2.__pga_aggregate_target=109051904
dell1.__sga_target=155189248
dell2.__sga_target=155189248
dell1.__shared_io_pool_size=0
dell2.__shared_io_pool_size=0
dell1.__shared_pool_size=113246208
dell2.__shared_pool_size=113246208
dell1.__streams_pool_size=0
dell2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dell/adump'
*.audit_trail='DB'
*.cluster_database=true
dell1.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/dell/controlfile/current.261.800523723','+FRA/dell/controlfile/current.268.800523725'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='dell'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dellXDB)'
dell2.instance_number=2
dell1.instance_number=1
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_listener='rac-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
dell2.thread=2
dell1.thread=1
dell1.undo_tablespace='UNDOTBS1'
dell2.undo_tablespace='UNDOTBS2'


Modified:

dup.__db_cache_size=29360128
dup.__java_pool_size=4194304
dup.__large_pool_size=4194304
dup.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dup.__pga_aggregate_target=109051904
dup.__sga_target=155189248
dup.__shared_io_pool_size=0
dup.__shared_pool_size=113246208
dup.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain='example.com'
*.db_name='dup'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'


Target

1) Make ORACLE_SID entry in the bash profile before doing anything else.

----------------------------------snip------------------------------------
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=dup; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
------------------------------------snip------------------------------------

2) Make a listener and register the "dup" database and start it using lsnrctl command.

Sample listener.

[oracle@newyork admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0
/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dup)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = dup)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


3) Make appropriate directory structures.

[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dup/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

4) Make appropriate entries in "/etc/resolv.conf" so a connection can
   be made through SCAN.

search example.com
nameserver 192.168.1.100

5) Do tnsping from both nodes to make sure both systems are reachable.

tnsping dell
tnsping dup

6) create spfile from pfile and start database in nomount stage.

[oracle@newyork ~]$ echo $ORACLE_SID
dup
[oracle@newyork ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 28 01:24:39 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/initdup.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1335892 bytes
Variable Size             230690220 bytes
Database Buffers           29360128 bytes
Redo Buffers                2252800 bytes

7) Through RMAN do active database cloning.

[oracle@newyork ~]$ echo $ORACLE_SID
dup
[oracle@newyork ~]$ rman target sys/sys@dell nocatalog auxiliary sys/sys@dup

Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 28 01:28:28 2013

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

connected to target database: DELL (DBID=3771264459)
using target database control file instead of recovery catalog
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database to dup
2> from active database
3> nofilenamecheck;

Starting Duplicate Db at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'', ''/u01/app/oracle/
flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name = 
 ''DELL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/DUP/controlfile/
o1_mf_8t7gsyns_.ctl';
   restore clone controlfile to  '/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl' from 
 '/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl';
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =  
 ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl''
 comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

Starting backup at 28-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=dell2 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dell2.f tag=TAG20130528T013011 RECID=2 STAMP=816571814
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-MAY-13

Starting restore at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 28-MAY-13

sql statement: alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl''
 comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   datafile  6 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

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 backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dell/datafile/system.267.800523427
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:39
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dell/datafile/sysaux.263.800523439
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dell/datafile/example.266.800523445
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:21
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dell/datafile/undotbs1.265.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/dell/datafile/undotbs2.258.800523875
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:10
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dell/datafile/users.262.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 28-MAY-13

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/dell/archivelog/2013_05_28/thread_2_seq_3.270.816570261" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_%u_.arc"   archivelog like 
 "+FRA/dell/archivelog/2013_05_28/thread_1_seq_5.269.816572061" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_%u_.arc"   archivelog like 
 "+FRA/dell/archivelog/2013_05_28/thread_2_seq_4.267.816572061" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=3 RECID=6 STAMP=816570261
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=5 RECID=7 STAMP=816572061
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=4 RECID=8 STAMP=816572062
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
Finished backup at 28-MAY-13

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
  RMAN-07517: Reason: The file header is corrupted

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf

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

executing command: SET until clause

Starting recover at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
archived log for thread 2 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
 thread=1 sequence=5
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
 thread=2 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-MAY-13

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''DUP'' 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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

sql statement: alter system set  db_name =  ''DUP'' 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     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG 
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M 
 DATAFILE
  '/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf'
 CHARACTER SET WE8MSWIN1252

sql statement: ALTER DATABASE ADD LOGFILE 
  
  INSTANCE 'i2' 
  GROUP  3  SIZE 50 M ,
  GROUP  4  SIZE 50 M 

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf RECID=1 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf RECID=2 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf RECID=3 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf RECID=4 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf RECID=5 STAMP=816572129

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf

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

database opened
Finished Duplicate Db at 28-MAY-13

8) after cloning disable thread 2 and remove respective redo groups.

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL> select group# from v$log where thread#=2;

    GROUP#
----------
         3
         4

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#, status, enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC


9) drop additional undo tablespaces

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
EXAMPLE

7 rows selected.


SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.



10) check status of tablespaces.

SQL> select tablespace_name,file_name,status from dba_temp_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
TEMP
/u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_8t7h4w47_.tmp
ONLINE



SQL> select tablespace_name,file_name,status from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
SYSTEM
/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
AVAILABLE

SYSAUX
/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
AVAILABLE

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------

UNDOTBS1
/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
AVAILABLE

USERS
/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
AVAILABLE

EXAMPLE
/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
AVAILABLE



Cloning Finished.


Read more >>