Skipping Tablespaces in RMAN cloning

Skipping Tablespaces While Cloning RMAN Database

The example will show how to skip Read Write, Read Only and Offline tablespaces. Then how to clone the database to a past point in time will be covered.

Perform all above steps up to Step 5 and bring the auxiliary database to the nomount mode. Then create three tablespaces in the production database:

tbs_skip: This is a Read Write tablespace. This tablespace will be skipped during the clone process.
tbs_readonly: This is a Read Only tablespace and will be skipped automatically by the skip readonly command during the clone process.
tbs_offline: This is an offline tablespace and will be skipped automatically by RMAN during the clone process.

Then create a table and use it to show the result of the past point in time recovery. Look at this example in more details. Create three tablespaces:

sys@TEST>
create
tablespace tbs_skip datafile '/u02/oradata/test/tbs_skip.dbf' SIZE 1M;
Tablespace created.
sys@TEST>
create
tablespace tbs_readonly datafile '/u02/oradata/test/tbs_readonly.dbf'
SIZE 1M;
Tablespace created.
sys@TEST>
create
tablespace tbs_offline datafile '/u02/oradata/ /test/tbs_offline.dbf'
SIZE 1M;
Tablespace created.
sys@TEST>

Create two tables: tbs_test and tbs_readonly. The first table will be used in our past point in time recovery scenario. The second table will be used when the Read Only tablespace is recovered after the clone process. Create the tbs_readonly table and insert one row in it:

sys@TEST>
create
table tbl_test (id number);
Table created.
sys@TEST>
create
table tbl_readonly (col1 varchar2(15)) tablespace tbs_readonly;
Table created.
sys@TEST>
insert into
tbl_readonly values('Readonly table');
1 row created.
sys@TEST>
commit;
Commit complete;

Change the status of the tablespace tbs_readonly to Read Only, tbs_offline to Offline:

sys@TEST>
alter
Tablespace tbs_readonly Read Only;
Tablespace altered.
sys@TEST>
alter
tablespace tbs_offline Offline Normal;
Tablespace altered.
sys@TEST>

Connect to RMAN and take a full backup:

RMAN> backup database plus archivelog delete input;
Starting backup at 01-OCT-09
....................
....................
Finished backup at 01-OCT-09

Make changes to the tbl_test table and take a backup of the database and archivelogs. Insert a row to the tbl_test table and commit it. Then take the current scn value of the database that will be used during recovery of said database. A clone will be made of the database to this scn value.

Next, insert one more row since it will be assumed that this row is a mistake and will not be recovered during the clone process. Then switch the redo log file and take a backup of the archived redo log files:

sys@TEST>
insert into
tbl_test values(1);
1 row created.
sys@TEST>
commit;
Commit complete.
sys@TEST>
select * from
tbl_test;

ID
----------
1
sys@TEST>
select
current_scn
from
v$database;
current_scn
-----------
471662
sys@TEST>
insert into
tbl_test values(100000);
1 row created.
sys@TEST>
commit;
Commit complete.
sys@TEST>
select * from
tbl_test;

ID
----------
1
100000

sys@TEST>
alter
system switch logfile;
System altered.
sys@TEST>
exit
$ rman target /
RMAN> backup archivelog all delete input;
Starting backup at 01-OCT-09
....................
....................
Finished backup at 01-OCT-09
RMAN>

The database is ready to be cloned. To clone the database, connect to both databases and run the following script:

$ rman target sys/test auxiliary sys/test@clone_db
connected to target database: test (DBID=782965739)
connected to auxiliary database: clone_db (not mounted)
RMAN> run {
set newname for tempfile 1 to '/u03/oracle/new_clone/temp01.dbf';
set newname for datafile 1 to '/u03/oracle/new_clone/system01.dbf';
set newname for datafile 2 to '/u03/oracle/new_clone/undotbs01.dbf';
set newname for datafile 3 to '/u03/oracle/new_clone/sysaux01.dbf';
set newname for datafile 4 to '/u03/oracle/new_clone/users01.dbf';
set newname for datafile 5 to '/u03/oracle/new_clone/skip.dbf';
set newname for datafile 6 to '/u03/oracle/new_clone/readonly.dbf';
set newname for datafile 7 to '/u03/oracle/new_clone/offline.dbf';
duplicate target database to clone_db
skip tablespace tbs_skip
skip readonly
until scn 471662
logfile
'/u03/oracle/new_clone/redo log01.log' SIZE 5M,
'/u03/oracle/new_clone/redo log02.log' SIZE 5M,
'/u03/oracle/new_clone/redo log03.log' SIZE 5M;
}

RMAN starts to clone and recovers the database until the specified scn value by skipping the tbl_skip tablespace, Read Only and Offline tablespaces. With the SKIP TABLESPACE clause, the tablespaces can be excluded from the duplicate database, but not the system tablespace or undo tablespace which contains rollback or undo segments. Below is the part of the result of the script:

contents of Memory Script:
{
set until scn 471662;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: set until clause
Starting recover at 01-OCT-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=disk
datafile 5 not processed because file is offline
datafile 6 not processed because file is read-only
datafile 7 not processed because file is offline
starting media recovery
.................
.................
Finished recover at 01-OCT-09
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
................
................
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 tbs_skip including contents cascade
constraints";
# drop offline and skipped tablespaces
sql clone "drop tablespace tbs_offline including contents cascade
constraints";
}
executing Memory Script

sql statement: drop tablespace tbs_skip including contents cascade
constraints

sql statement: drop tablespace tbs_offline including contents cascade
constraints
Finished Duplicate Db at 01-OCT-09

RMAN>

As can be seen, the database has been recovered to scn 471662 and all tablespaces have been skipped.

datafile 5 not processed because file is offline
datafile 6 not processed because file is read-only
datafile 7 not processed because file is offline

Datafile 5 was manually skipped because the skip tablespace tbs_skip command was used. Datafile 6 was skipped because it was a Read Only tablespace and all Read Only tablespaces were skipped by the skip readonly command. Datafile 7 was skipped automatically by RMAN because it is an offline tablespace. Then the database was opened with the resetlogs optionand the tbs_skip and tbs_offline tablespaces dropped by RMAN.

Now, query the tbl_test table:

sys@clone_db>
select * from
tbl_test;

ID
----------
1

The last time when this table was queried, there were two lines: however, as the database was recovered until a specific scn value, at that scn there was only one row.

Now, query the tbl_readonly table:

sys@clone_db>
select * from
tbl_readonly;
select * from
tbl_readonly
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6:
'/u01/oracle/product/10.2.0/db_1/dbs/MISSING00006'

This shows that since the tbs_readonly tablespace was skipped with the skip readonly command, the table which resides on Datafile 6 cannot be queried. It was dropped after the database had been cloned and opened. Check the status of the datafile in the v$datafile view:

sys@clone_db>
set
linesize 1000
sys@clone_db>
select
status, enabled, name
from
v$datafile;

STATUS ENABLED NAME
------- ---------- -------------------------------------
SYSTEM READ WRITE /u03/oracle/new_clone/system01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/undotbs01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/sysaux01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/users01.dbf
OFFLINE READ ONLY /u01/oracle/product/10.2.0/db_1/dbs/MISSING00006

To recover this tablespace, copy it from the production databaseto the location where the auxiliary database's files are located and recover them by performing the following steps:

Copy it from the production database's file location to the auxiliary database's location:

sys@clone_db>host

$ cp /u01/oracle/test/tbs_readonly.dbf /u03/oracle/new_clone/

Note: Now is the time to use a cp copy of the datafile without taking a fresh backup because the tablespace is Read Only.

Then rename it and change the status to online in the clone_db database:

sys@clone_db>
alter tablespace
tbs_readonly rename datafile
'/u01/oracle/product/10.2.0/db_1/dbs/MISSING00006' TO
'/u03/oracle/new_clone/tbs_readonly.dbf';
Tablespace altered.
sys@clone_db>
alter tablespace
tbs_readonly online;
Tablespace altered.

Now view the status and datafile name of that file from the v$datafile view:

sys@clone_db>
select
status, enabled, name
from
v$datafile;

STATUS ENABLED NAME
------- ---------- ---------------------------------
SYSTEM READ WRITE /u03/oracle/new_clone/system01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/undotbs01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/sysaux01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/users01.dbf
ONLINE READ ONLY /u03/oracle/new_clone/tbs_readonly.dbf

Now the tbl_readonly table can be queried:

sys@clone_db>
select * from
tbl_readonly;
COL1
---------------
Readonly table
sys@clone_db>

Quest_in_OracleDBA

Thursday, November 19, 2015