Rman duplicate cloning - skip tablespace

Rman duplicate cloning - skip tablespace

Rman duplicate cloning - skip tablespace

Sometimes client asked to clone the database for testing purpose or new environment setup.
Normally we well knew about RMAN cloning.
Also they told we don’t want XYZ schema data for new database.
Normally in my environment each schema user has individual data tablespace & index tablespace.
I will plan to skip the XYZ schema associated tablespaces while cloning the database.

I have tested this scenario in my test server.

Environment Details:

Operating system: Windows XP service pack 2
Database Version: 11.1.0.7 (32 bit)
Source database name: che
Clone database name: skipdb

How its work?

RMAN before restore the datafiles its skip the mentioned skip tablespace belonging datafiles.That datafiles are offline before restore operation is performed. After recovery, its remove the skip tablespace information from current control file.

1.Login into CHE database

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
-------------------- ------------------------------------------------------------
USERS D:\ORACLE\APP\PRODUCT\ORADATA\CHE\USERS01.DBF
UNDOTBS1 D:\ORACLE\APP\PRODUCT\ORADATA\CHE\UNDOTBS01.DBF
SYSAUX D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSAUX01.DBF
SYSTEM D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSTEM01.DBF

----created new tablespaces

SQL> create tablespace raja_data
2 datafile 'D:\ORACLE\APP\PRODUCT\ORADATA\CHE\raja_data01.dbf' size 100m;

Tablespace created.

SQL> create tablespace raja_indx
2 datafile 'D:\ORACLE\APP\PRODUCT\ORADATA\CHE\raja_indx01.dbf' size 50m;

Tablespace created.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
-------------------- ------------------------------------------------------------
USERS D:\ORACLE\APP\PRODUCT\ORADATA\CHE\USERS01.DBF
UNDOTBS1 D:\ORACLE\APP\PRODUCT\ORADATA\CHE\UNDOTBS01.DBF
SYSAUX D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSAUX01.DBF
SYSTEM D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSTEM01.DBF
RAJA_DATA D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_DATA01.DBF
RAJA_INDX D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_INDX01.DBF

6 rows selected.

----created new user

SQL> create user raja identified by raja
2 default tablespace RAJA_DATA
3 quota unlimited on RAJA_DATA
4 quota unlimited on RAJA_INDX;

User created.

SQL> grant connect, resource, dba to raja;

Grant succeeded.

----created new objects

SQL> create table raja.objects as select * from dba_objects;

Table created.

SQL> create index raja.object_id_indx on objects(object_id) tablespace raja_indx;

Index created.

SQL> col segment_name for a20
SQL> col tablespace_name for a20
SQL> select segment_name,segment_type,tablespace_name from dba_segments where owner in ('RAJA');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ --------------------
OBJECT_ID_INDX INDEX RAJA_INDX
OBJECTS TABLE RAJA_DATA

2.Backup the target database ( CHE database)

C:\>set oracle_sid=che

Recovery Manager: Release 11.1.0.7.0 - Production on Wed May 5 13:13:19 2010

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

RMAN> connect target sys@che

target database Password:
connected to target database: CHE (DBID=3458403522)

RMAN> backup database plus archivelog;


Starting backup at 05-MAY-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 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=1 RECID=5 STAMP=717585117
input archived log thread=1 sequence=2 RECID=6 STAMP=717933744
input archived log thread=1 sequence=3 RECID=7 STAMP=720282824
input archived log thread=1 sequence=4 RECID=8 STAMP=718183666
input archived log thread=1 sequence=5 RECID=9 STAMP=718204428
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_ANNNN_TAG20100
505T131350_5Y28FQJ3_.BKP tag=TAG20100505T131350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
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=1 RECID=2 STAMP=716895745
input archived log thread=1 sequence=2 RECID=3 STAMP=716895747
input archived log thread=1 sequence=3 RECID=4 STAMP=716895750
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_ANNNN_TAG20100
505T131350_5Y28FYY8_.BKP tag=TAG20100505T131350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 05-MAY-10

Starting backup at 05-MAY-10
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=00001 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSTEM01.DBF
input datafile file number=00002 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSAUX01.DBF
input datafile file number=00005 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_DATA01.DBF
input datafile file number=00004 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\USERS01.DBF
input datafile file number=00006 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_INDX01.DBF
input datafile file number=00003 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_NNNDF_TAG20100
505T131406_5Y28G74D_.BKP tag=TAG20100505T131406 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_NCSNF_TAG20100
505T131406_5Y28J9K2_.BKP tag=TAG20100505T131406 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAY-10

Starting backup at 05-MAY-10
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=6 RECID=10 STAMP=718204515
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_ANNNN_TAG20100
505T131515_5Y28JCRW_.BKP tag=TAG20100505T131515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAY-10

3.Configure the network files.

Listener configuration:


SID_LIST_LISTENER11G=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=che)
(SID_NAME=che)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=skipdb)
(SID_NAME=skipdb)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
)
)

TNS configuration:


CHE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = che)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = che)
)
)


SKIPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = che)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = skipdb)
)
)


4.Create the instance using oradim utility ( applicable for windows platform)

C:\>oradim -new -sid skipdb
Instance created.

5.Create a password file for skipdb database using orapwd utility

C:\>orapwd file=D:\Oracle\app\product\11.1.0\db_1\dbs\orapwSKIPDB password=skipdbadmin entries=20

6.Create the init. ora file for skipdb database.

Che database: We create the pfile for skipdb from che database & edit the parameter.

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\APP\PRODUCT\11.1.0\D
B_1\DATABASE\SPFILECHE.ORA

SQL> create pfile='D:\Oracle\app\product\admin\skipdb\pfile\initskipdb.ora'
from spfile;

File created.

Init parameter change “skipdb” instead of che & use to start the Skipdb.

skipdb.__db_cache_size=130023424
skipdb.__java_pool_size=12582912
skipdb.__large_pool_size=4194304
skipdb.__oracle_base='D:\Oracle\app\product'#ORACLE_BASE set from environment
skipdb.__pga_aggregate_target=192937984
skipdb.__sga_target=348127232
skipdb.__shared_io_pool_size=0
skipdb.__shared_pool_size=184549376
skipdb.__streams_pool_size=8388608
*.audit_file_dest='D:\Oracle\app\product\admin\skipdb\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\Oracle\app\product\oradata\skipdb\control01.ctl','D:\Oracle\app\product\oradata\skipdb\control02.ctl','D:\Oracle\app\product\oradata\skipdb\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='skipdb'
*.diagnostic_dest='D:\Oracle\app\product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=skipdbXDB)'
*.log_archive_dest='D:\Oracle\app\product\archive\skipdb'
*.log_archive_start=TRUE
*.memory_target=538968064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
db_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\CHE','D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB')
log_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\CHE','D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB')


7.Create the appropriate folder for skipdb database (admin/oradata folders etc..)


8.Startup the clone database in mount stage

C:\>set oracle_sid=skipdb

C:\>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 28 14:21:08 2010

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 398462052 bytes
Database Buffers 130023424 bytes
Redo Buffers 5828608 bytes

9.Connect the target & auxiliary database using RMAN

C:\>rman target sys/cheadmin@che

Recovery Manager: Release 11.1.0.7.0 - Production on Wed May 5 13:49:04 2010

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

Connected to target database: CHE (DBID=3458403522)

RMAN> connect auxiliary sys/skipdbadmin@skipdb

connected to auxiliary database: SKIPDB (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO SKIPDB SKIP TABLESPACE RAJA_DATA, RAJA_INDX;

Starting Duplicate Db at 05-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=152 device type=DISK
Datafile 5 skipped by request
Datafile 6 skipped by request

contents of Memory Script:
{
set until scn 1000208;
set newname for datafile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF";
restore
clone database
skip tablespace "RAJA_INDX",
"RAJA_DATA" ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-MAY-10
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 D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DB
F
channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DB
F
channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.D
BF
channel ORA_AUX_DISK_1: restoring datafile 00004 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF

channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACK
UPSET\2010_05_05\O1_MF_NNNDF_TAG20100505T131406_5Y28G74D_.BKP
channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05
_05\O1_MF_NNNDF_TAG20100505T131406_5Y28G74D_.BKP tag=TAG20100505T131406
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 05-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SKIPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


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

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=718206667 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.
DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=718206667 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01
.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=718206667 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.D
BF

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

executing command: SET until clause

Starting recover at 05-MAY-10
using channel ORA_AUX_DISK_1
datafile 5 not processed because file is offline
datafile 6 not processed because file is offline

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file D:\ORACLE\APP\PRODUCT\FLASH_REC
OVERY_AREA\CHE\ARCHIVELOG\2010_05_05\O1_MF_1_6_5Y28JC3X_.ARC
archived log file name=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2010_05_05\O1_MF_1_6
_5Y28JC3X_.ARC thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-MAY-10

contents of Memory Script:
{
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 535662592 bytes

Fixed Size 1348508 bytes
Variable Size 201329764 bytes
Database Buffers 327155712 bytes
Redo Buffers 5828608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SKIPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for tempfile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF RECID=1 STAMP=718206692

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF RECID=2 STAMP=718206692

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF RECID=3 STAMP=718206693

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=718206692 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.
DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=718206692 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01
.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=718206693 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.D
BF

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

database opened

contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone 'drop tablespace "RAJA_INDX" including contents cascade constraints';
# drop offline and skipped tablespaces
sql clone 'drop tablespace "RAJA_DATA" including contents cascade constraints';
}
executing Memory Script

sql statement: drop tablespace "RAJA_INDX" including contents cascade constraints

sql statement: drop tablespace "RAJA_DATA" including contents cascade constraints
Finished Duplicate Db at 05-MAY-10


10.Verify the skipdb database.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
SKIPDB READ WRITE

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\Oracle\app\product\archive\skipdb
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
SYSTEM D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF
SYSAUX D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF
UNDOTBS1 D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF
USERS D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF

SQL> select username,account_status from dba_users where username='RAJA';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
RAJA OPEN

SQL> select segment_name,segment_type,tablespace_name from dba_segments where owner in ('RAJA');

no rows selected

Other scenario:

A user accidentally dropped a table in production database. We have a proper RMAN backup.

Normally we follow the below steps for recovery:

1. Offline the datafiles/tablespace belonging to table.
2. Restore the datafiles/tablespace.
3. Recover the datafiles.

For this we need an outage for the application. So we using prior backup to clone the database & export the table and import into production. Our database is very huge database. So restoration is very difficult.

That time we used skip tablespace feature. Restore the corresponding tablespace, system, undo & sysaux tablespaces. To reduce the restoration downtime & avoid the space issue



I Hope this article helped to you. Suggestions are welcome.