Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Skipping Tablespaces in RMAN cloning

Expert Oracle Database Tips by Donald BurlesonMarch 25, 2015

Skipping Tablespaces While Cloning RMAN Database  

The next 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_skipThis 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>

 

 

 
 
 
Get the Complete
Oracle Backup & Recovery Details 

The landmark book "Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump " provides real world advice for resolving the most difficult Oracle performance and recovery issues. Buy it for 40% off directly from the publisher.
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster