This post explains the steps which are used for RMAN: how to duplicate a database on the same host.
We need to duplicate a database. There are many possibilities to complete this task and I choose to use RMAN and its commands: in particular, I decided (to be honest it was just a poor notebook configuration to address my choice) to duplicate the target database to the same host. Perhaps I could spend my time on other possibilities offered by RMAN such as for example to duplicate the target database on a remote host.
I’m working with Oracle Database 11.2.0.1.0 on 64bit hosted by a CentOS virtualized machine: the target database is DB11G and the duplicated database will be DUP11G.
So this is what I did. First thing I have to do is to get a valid backup of DB11G.
The second thing is that I need to get some information about my machine because I’ve added and removed many virtual disks lately and I don’t remember where are exactly my datafiles… I just remember I have space problems.
Let’s start the instance (SQL is an alias for sqlplus / as sysdba and my ORACLE_SID is already set to DB11G)
[oracle@plutone DB11G]$ SQL SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 23 16:48:54 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. idle> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 432017400 bytes Database Buffers 184549376 bytes Redo Buffers 7544832 bytes Database mounted. Database opened.
I usually don’t run test database in archivelog mode, just let me check. Otherwise, I will set it on archivelog mode.
idle> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 50 Current log sequence 52 idle> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. idle> startup mount; ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 432017400 bytes Database Buffers 184549376 bytes Redo Buffers 7544832 bytes Database mounted. idle> alter database archivelog; Database altered. idle> alter database open; Database altered. idle> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 50 Next log sequence to archive 52 Current log sequence 52
Let me see how much space I have on FRA.
idle> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area db_recovery_file_dest_size big integer 3882M recovery_parallelism integer 0 idle> exit
I don’t have any space to get a valid backup
[oracle@plutone DB11G]$ cd /u01/app/oracle/flash_recovery_area/DB11G [oracle@plutone DB11G]$ df -h . Filesystem Size Used Avail Use% Mounted on /dev/sda3 18G 16G 802M 96% / [oracle@plutone DB11G]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 18G 16G 802M 96% / /dev/sda1 190M 18M 163M 10% /boot tmpfs 617M 0 617M 0% /dev/shm /dev/sdb1 9.9G 4.3G 5.1G 46% /u02
Let me check what is the size of DB11G datafiles.
[oracle@plutone DB11G]$ cd /DATA/DB11G [oracle@plutone DB11G]$ ll total 4128412 -rw-r----- 1 oracle oinstall 195043328 Sep 23 16:51 temp01.dbf -rw-r----- 1 oracle oinstall 52429312 Sep 23 16:54 redo03.log -rw-r----- 1 oracle oinstall 52429312 Sep 23 16:54 redo02.log -rw-r----- 1 oracle oinstall 7872512 Sep 23 16:55 users01.dbf -rw-r----- 1 oracle oinstall 592453632 Sep 23 16:55 undotbs01.dbf -rw-r----- 1 oracle oinstall 713039872 Sep 23 16:55 system01.dbf -rw-r----- 1 oracle oinstall 524296192 Sep 23 16:55 sysaux01.dbf -rw-r----- 1 oracle oinstall 1918902272 Sep 23 16:55 soe.dbf -rw-r----- 1 oracle oinstall 52429312 Sep 23 16:55 redo01.log -rw-r----- 1 oracle oinstall 104865792 Sep 23 16:55 example01.dbf -rw-r----- 1 oracle oinstall 9748480 Sep 23 16:55 control01.ctl [oracle@plutone DB11G]$ du -hs 4.0G .
Definitely, I have no space to host a duplicated database.
Let me point the FRA of DB11G to /u02.
[root@plutone u02]# cd /u02/ [root@plutone u02]# ll total 40 drwxr-xr-x 3 ora10g oinstall 4096 Aug 16 14:18 app drwxr-xr-x 3 ora10g oinstall 4096 Aug 16 16:43 DATA10 drwxr-xr-x 2 oracle oinstall 4096 Sep 23 17:32 DATA_DUP11G drwxr-xr-x 3 ora10g oinstall 4096 Aug 16 16:44 FRA10 drwxr-xr-x 2 oracle oinstall 4096 Sep 23 17:30 FRA11G drwx------ 2 ora10g oinstall 16384 Aug 16 14:04 lost+found drwxr-xr-x 2 oraxe oinstall 4096 Sep 7 11:42 XE [root@plutone u02]# mkdir FRA11G [root@plutone u02]# chown -R oracle.oinstall FRA11G/ idle> alter system set db_recovery_file_dest='/u02/FRA11G' scope=spfile; System altered. idle> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area db_recovery_file_dest_size big integer 3882M recovery_parallelism integer 0 idle> alter system set db_recovery_file_dest='/u02/FRA11G' scope=memory; System altered. idle> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u02/FRA11G db_recovery_file_dest_size big integer 3882M recovery_parallelism integer 0
Let me check if Oracle is able to write on that new FRA location:
[root@plutone FRA11G]# pwd /u02/FRA11G [root@plutone FRA11G]# ll total 0 idle> alter database open; Database altered. idle> alter system switch logfile; System altered. [root@plutone FRA11G]# ll total 4 drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:38 DB11G [root@plutone FRA11G]# cd DB11G/archivelog/ [root@plutone archivelog]# ll total 4 drwxr-x--- 2 oracle oinstall 4096 Sep 23 17:38 2010_09_23 [root@plutone archivelog]# cd 2010_09_23/ [root@plutone 2010_09_23]# ll total 1908 -rw-r----- 1 oracle oinstall 1947648 Sep 23 17:38 o1_mf_1_52_69px43oj_.arc
Let me proceed with RMAN configuration:
[oracle@plutone DB11G]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 23 17:40:40 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: DB11G (DBID=197032563) RMAN> list backup; using target database control file instead of recovery catalog specification does not match any backup in the repository RMAN> show all; RMAN configuration parameters for database with db_unique_name DB11G are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB11G.f'; # default RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored RMAN>
Just before I execute the backup I notice there is only one directory under /u02/FRA11G/DB11G:
[root@plutone DB11G]# ll total 4 drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:38 archivelog
Perform a complete full database backup plus archivelog:
RMAN> backup database plus archivelog; Starting backup at 2010-09-23:17:42:26 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=52 RECID=1 STAMP=730489124 input archived log thread=1 sequence=53 RECID=2 STAMP=730489346 channel ORA_DISK_1: starting piece 1 at 2010-09-23:17:42:29 channel ORA_DISK_1: finished piece 1 at 2010-09-23:17:42:33 piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_annnn_TAG20100923T174228_69pxc5wb_.bkp tag=TAG20100923T174228 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 2010-09-23:17:42:34 Starting backup at 2010-09-23:17:42:34 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/DATA/DB11G/soe.dbf input datafile file number=00001 name=/DATA/DB11G/system01.dbf input datafile file number=00003 name=/DATA/DB11G/undotbs01.dbf input datafile file number=00002 name=/DATA/DB11G/sysaux01.dbf input datafile file number=00005 name=/DATA/DB11G/example01.dbf input datafile file number=00004 name=/DATA/DB11G/users01.dbf channel ORA_DISK_1: starting piece 1 at 2010-09-23:17:42:35 channel ORA_DISK_1: finished piece 1 at 2010-09-23:17:49:02 piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp tag=TAG20100923T174235 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:06:27 Finished backup at 2010-09-23:17:49:02 Starting backup at 2010-09-23:17:49:03 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=54 RECID=3 STAMP=730489744 channel ORA_DISK_1: starting piece 1 at 2010-09-23:17:49:05 channel ORA_DISK_1: finished piece 1 at 2010-09-23:17:49:06 piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_annnn_TAG20100923T174904_69pxqkjl_.bkp tag=TAG20100923T174904 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2010-09-23:17:49:06 Starting Control File and SPFILE Autobackup at 2010-09-23:17:49:07 piece handle=/u02/FRA11G/DB11G/autobackup/2010_09_23/o1_mf_s_730489747_69pxqqxd_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2010-09-23:17:49:14
And now on file system I’ve backupset:
[root@plutone DB11G]# ll total 8 drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:38 archivelog drwxr-x--- 3 oracle oinstall 4096 Sep 23 17:42 backupset [root@plutone DB11G]# ll -h backupset/2010_09_23/ total 3.3G -rw-r----- 1 oracle oinstall 2.0M Sep 23 17:42 o1_mf_annnn_TAG20100923T174228_69pxc5wb_.bkp -rw-r----- 1 oracle oinstall 165K Sep 23 17:49 o1_mf_annnn_TAG20100923T174904_69pxqkjl_.bkp -rw-r----- 1 oracle oinstall 3.3G Sep 23 17:48 o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp
Now I have to add a new virtualized disk because you can see I have no space to create a duplicate database of a 4G target one.
Have a look to my current disk configuration:
[root@plutone DB11G]# mount /dev/sda3 on / type ext3 (rw) proc on /proc type proc (rw) sysfs on /sys type sysfs (rw) devpts on /dev/pts type devpts (rw,gid=5,mode=620) /dev/sda1 on /boot type ext3 (rw) tmpfs on /dev/shm type tmpfs (rw) /dev/sdb1 on /u02 type ext3 (rw) none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw) none on /proc/fs/vmblock/mountPoint type vmblock (rw) sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw) [root@plutone DB11G]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 18G 16G 794M 96% / /dev/sda1 190M 18M 163M 10% /boot tmpfs 617M 392M 226M 64% /dev/shm /dev/sdb1 9.9G 7.6G 1.9G 81% /u02 [root@plutone DB11G]# more /etc/fstab LABEL=/ / ext3 defaults 1 1 LABEL=/boot /boot ext3 defaults 1 2 tmpfs /dev/shm tmpfs defaults 0 0 devpts /dev/pts devpts gid=5,mode=620 0 0 sysfs /sys sysfs defaults 0 0 proc /proc proc defaults 0 0 LABEL=SWAP-sda2 swap swap defaults 0 0 /dev/sdb1 /u02 ext3 defaults 1 1 [root@plutone ~]# fdisk -l Disk /dev/sda: 21.4 GB, 21474836480 bytes 255 heads, 63 sectors/track, 2610 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 * 1 25 200781 83 Linux /dev/sda2 26 286 2096482+ 82 Linux swap / Solaris /dev/sda3 287 2610 18667530 83 Linux Disk /dev/sdb: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdb1 1 1305 10482381 83 Linux
After I’ve added a new virtualized disk of 10G I need to format and mount it.
[root@plutone ~]# fdisk -l Disk /dev/sda: 21.4 GB, 21474836480 bytes 255 heads, 63 sectors/track, 2610 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 * 1 25 200781 83 Linux /dev/sda2 26 286 2096482+ 82 Linux swap / Solaris /dev/sda3 287 2610 18667530 83 Linux Disk /dev/sdb: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdb1 1 1305 10482381 83 Linux Disk /dev/sdc: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdc doesn’t contain a valid partition table
[root@plutone ~]# fdisk /dev/sdc Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. The number of cylinders for this disk is set to 1305. There is nothing wrong with that, but this is larger than 1024, and could in certain setups cause problems with: 1) software that runs at boot time (e.g., old versions of LILO) 2) booting and partitioning software from other OSs (e.g., DOS FDISK, OS/2 FDISK) Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-1305, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305): Using default value 1305 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@plutone ~]# fdisk -l Disk /dev/sda: 21.4 GB, 21474836480 bytes 255 heads, 63 sectors/track, 2610 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 * 1 25 200781 83 Linux /dev/sda2 26 286 2096482+ 82 Linux swap / Solaris /dev/sda3 287 2610 18667530 83 Linux Disk /dev/sdb: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdb1 1 1305 10482381 83 Linux Disk /dev/sdc: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdc1 1 1305 10482381 83 Linux [root@plutone ~]# mkfs.ext3 /dev/sdc1 mke2fs 1.39 (29-May-2006) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) 1310720 inodes, 2620595 blocks 131029 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=2684354560 80 block groups 32768 blocks per group, 32768 fragments per group 16384 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632 Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done This filesystem will be automatically checked every 37 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override.
I then edited /etc/fstab and added the following line:
/dev/sdc1 /u03 ext3 defaults 1 1 [root@plutone ~]# mkdir /u03 [root@plutone ~]# mount /u03 [root@plutone ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 18G 16G 794M 96% / /dev/sda1 190M 18M 163M 10% /boot tmpfs 617M 0 617M 0% /dev/shm /dev/sdb1 9.9G 7.6G 1.9G 81% /u02 /dev/sdc1 9.9G 151M 9.2G 2% /u03
Ok now I have space to continue. Now it’s time to add two dedicated local listeners (tns is an alias to go on the directory containing listener.ora and tnsnames.ora files):
[oracle@plutone DB11G]$ tns [oracle@plutone admin]$ vi 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 = DB11G) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1/) (SID_NAME = DB11G) ) (SID_DESC = (GLOBAL_DBNAME = DUP11G) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1/) (SID_NAME = DUP11G) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521)) ) )
Add the following information (adding of course DUP11G service) on tnsnames.ora:
[oracle@plutone admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DUP11G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DUP11G) ) ) DB11G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = plutone)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB11G) ) )
Now it’s time to create a init.ora file for DUP11G.
[oracle@plutone dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs [oracle@plutone dbs]$ strings spfileDB11G.ora DB11G.__db_cache_size=184549376 DB11G.__java_pool_size=4194304 DB11G.__large_pool_size=4194304 DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DB11G.__pga_aggregate_target=218103808 DB11G.__sga_target=411041792 DB11G.__shared_io_pool_size=0 DB11G.__shared_pool_size=201326592 DB11G.__streams_pool_size=4194304 *.audit_file_dest='/u01/app/oracle/admin/DB11G/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/DATA/DB11G/control01.ctl','/u01/app/or acle/flash_recovery_area/DB11G/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='DB11G' *.db_recovery_file_dest_size=4070572032 *.db_recovery_file_dest='/u02/FRA11G' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)' *.memory_target=629145600 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' [oracle@plutone dbs]$ strings spfileDB11G.ora > initDUP11G.ora
I edited the spooled init file to accomplish my needing (and YES.. I know I don’t have to put on the same mount point all my control files!!
[oracle@plutone dbs]$ vi initDUP11G.ora *.compatible='11.2.0.0.0' *.control_files='/u03/DUP11G/control01.ctl','/u03/DUP11G/control02.ctl' *.db_block_size=8192 *.db_name='DUP11G' *.remote_login_passwordfile='EXCLUSIVE' db_file_name_convert = '/DATA/DB11G','/u03/DUP11G' log_file_name_convert = '/DATA/DB11G','/u03/DUP11G'
Let’s start DUP11G in nomount mode:
[oracle@plutone dbs]$ export ORACLE_SID=DUP11G [oracle@plutone dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 07:25:00 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. idle> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initDUP11G.ora ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2211928 bytes Variable Size 159387560 bytes Database Buffers 50331648 bytes Redo Buffers 5226496 bytes
Let’s start the target database:
[oracle@plutone admin]$ export ORACLE_SID=DB11G [oracle@plutone admin]$ ps -ef|grep smon oracle 6702 1 0 07:35 ? 00:00:00 ora_smon_DUP11G oracle 10411 9789 0 09:35 pts/4 00:00:00 grep smon [oracle@plutone admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 09:35:54 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. idle> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 432017400 bytes Database Buffers 184549376 bytes Redo Buffers 7544832 bytes Database mounted. Database opened. idle> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now let’s try to execute the duplicate command:
[oracle@plutone admin]$ export ORACLE_SID=DUP11G [oracle@plutone admin]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 24 09:36:56 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target sys/oracle@DB11G connected to target database: DB11G (DBID=197032563) RMAN> connect auxiliary / RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-04006: error from auxiliary database: ORA-12541: TNS:no listener RMAN> exit Recovery Manager complete.
Ok.. I forgot to start the listener with DUP11G information.
[oracle@plutone dbs]$ lsnrctl start
And let’s continue with the second attempt to duplicate DB11G to DUP11G (the scripts following the last command I issued “duplicate target database to DUP11G” are run by Oracle itself…. I mean automatically!!!
[oracle@plutone admin]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 24 09:36:56 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/oracle@DB11G
connected to target database: DB11G (DBID=197032563)
RMAN> connect auxiliary /
connected to auxiliary database: DUP11G (not mounted)
RMAN> duplicate target database to DUP11G;
Starting Duplicate Db at 2010-09-24:09:44:19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUP11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUP11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Starting restore at 2010-09-24:09:44:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/FRA11G/DB11G/autobackup/2010_09_23/o1_mf_s_730489747_69pxqqxd_.bkp
channel ORA_AUX_DISK_1: piece handle=/u02/FRA11G/DB11G/autobackup/2010_09_23/o1_mf_s_730489747_69pxqqxd_.bkp tag=TAG20100923T174907
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/u03/DUP11G/control01.ctl
output file name=/u03/DUP11G/control02.ctl
Finished restore at 2010-09-24:09:44:46
database mounted
contents of Memory Script:
{
set until scn 1223173;
set newname for datafile 1 to
"/u03/DUP11G/system01.dbf";
set newname for datafile 2 to
"/u03/DUP11G/sysaux01.dbf";
set newname for datafile 3 to
"/u03/DUP11G/undotbs01.dbf";
set newname for datafile 4 to
"/u03/DUP11G/users01.dbf";
set newname for datafile 5 to
"/u03/DUP11G/example01.dbf";
set newname for datafile 6 to
"/u03/DUP11G/soe.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
Starting restore at 2010-09-24:09:44:52
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 /u03/DUP11G/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/DUP11G/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/DUP11G/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/DUP11G/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/DUP11G/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u03/DUP11G/soe.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp
channel ORA_AUX_DISK_1: piece handle=/u02/FRA11G/DB11G/backupset/2010_09_23/o1_mf_nnndf_TAG20100923T174235_69pxccs4_.bkp tag=TAG20100923T174235
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:05:06
Finished restore at 2010-09-24:09:49:58
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=730547400 file name=/u03/DUP11G/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=730547400 file name=/u03/DUP11G/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=730547400 file name=/u03/DUP11G/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=730547401 file name=/u03/DUP11G/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=730547401 file name=/u03/DUP11G/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=730547401 file name=/u03/DUP11G/soe.dbf
contents of Memory Script:
{
set until scn 1223173;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2010-09-24:09:50:04
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 54 is already on disk as file /u02/FRA11G/DB11G/archivelog/2010_09_23/o1_mf_1_54_69pxqj3g_.arc
archived log file name=/u02/FRA11G/DB11G/archivelog/2010_09_23/o1_mf_1_54_69pxqj3g_.arc thread=1 sequence=54
media recovery complete, elapsed time: 00:00:02
Finished recover at 2010-09-24:09:50:11
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''DUP11G'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: alter system set db_name = ''DUP11G'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/DUP11G/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/DUP11G/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/DUP11G/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/DUP11G/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/DUP11G/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/DUP11G/sysaux01.dbf",
"/u03/DUP11G/undotbs01.dbf",
"/u03/DUP11G/users01.dbf",
"/u03/DUP11G/example01.dbf",
"/u03/DUP11G/soe.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u03/DUP11G/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u03/DUP11G/sysaux01.dbf RECID=1 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/undotbs01.dbf RECID=2 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/users01.dbf RECID=3 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/example01.dbf RECID=4 STAMP=730547441
cataloged datafile copy
datafile copy file name=/u03/DUP11G/soe.dbf RECID=5 STAMP=730547441
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=730547441 file name=/u03/DUP11G/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=730547441 file name=/u03/DUP11G/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=730547441 file name=/u03/DUP11G/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=730547441 file name=/u03/DUP11G/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=730547441 file name=/u03/DUP11G/soe.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 2010-09-24:09:51:10
RMAN> exit
Recovery Manager complete.
How many instances are running and are they the same ?
[oracle@plutone admin]$ ps -ef|grep smon oracle 10511 1 0 09:36 ? 00:00:01 ora_smon_DB11G oracle 11303 1 0 09:50 ? 00:00:00 ora_smon_DUP11G oracle 11421 9789 0 09:51 pts/4 00:00:00 grep smon [oracle@plutone admin]$ SQL SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 09:52:25 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options sys@DUP11G> set pagesize 999 sys@DUP11G> select username from dba_users order by created; USERNAME ------------------------------ SYS SYSTEM OUTLN DIP ORACLE_OCM DBSNMP APPQOSSYS WMSYS EXFSYS CTXSYS ANONYMOUS XDB XS$NULL ORDDATA MDSYS ORDPLUGINS ORDSYS SI_INFORMTN_SCHEMA OLAPSYS MDDATA SPATIAL_WFS_ADMIN_USR SPATIAL_CSW_ADMIN_USR SYSMAN MGMT_VIEW APEX_030200 FLOWS_FILES APEX_PUBLIC_USER OWBSYS OWBSYS_AUDIT SCOTT SH OE HR PM BI IX STORE SOE 38 rows selected. sys@DUP11G> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@plutone admin]$ export ORACLE_SID=DB11G [oracle@plutone admin]$ SQL SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 24 10:02:33 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options sys@DB11G> set pagesize 999 sys@DB11G> select username from dba_users order by created; USERNAME ------------------------------ SYS SYSTEM OUTLN DIP ORACLE_OCM DBSNMP APPQOSSYS WMSYS EXFSYS CTXSYS ANONYMOUS XDB XS$NULL ORDDATA MDSYS ORDPLUGINS ORDSYS SI_INFORMTN_SCHEMA OLAPSYS MDDATA SPATIAL_WFS_ADMIN_USR SPATIAL_CSW_ADMIN_USR SYSMAN MGMT_VIEW APEX_030200 FLOWS_FILES APEX_PUBLIC_USER OWBSYS OWBSYS_AUDIT SCOTT SH OE HR PM BI IX STORE SOE 38 rows selected. sys@DB11G> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@plutone admin]$ cd /u03/DUP11G/ [oracle@plutone DUP11G]$ ll total 3949168 -rw-r----- 1 oracle oinstall 7872512 Sep 24 09:50 users01.dbf -rw-r----- 1 oracle oinstall 1918902272 Sep 24 09:50 soe.dbf -rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo03.log -rw-r----- 1 oracle oinstall 52429312 Sep 24 09:50 redo02.log -rw-r----- 1 oracle oinstall 104865792 Sep 24 09:50 example01.dbf -rw-r----- 1 oracle oinstall 195043328 Sep 24 09:52 temp01.dbf -rw-r----- 1 oracle oinstall 592453632 Sep 24 10:02 undotbs01.dbf -rw-r----- 1 oracle oinstall 713039872 Sep 24 10:02 system01.dbf -rw-r----- 1 oracle oinstall 524296192 Sep 24 10:02 sysaux01.dbf -rw-r----- 1 oracle oinstall 52429312 Sep 24 10:02 redo01.log -rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control02.ctl -rw-r----- 1 oracle oinstall 10076160 Sep 24 10:03 control01.ctl
At this point, you can remove from DUP11G database any reference to db_file_name_convert and log_file_name_convert parameters.
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
