Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 8, 2009

RMAN Drills

By Steve Callan

This article and the next cover a couple of RMAN exercises using an older version of Oracle. Newer versions, specifically 10g and up, take care of many issues or limitations present in 9i and 8i days, and as we all know, not everyone is using the latest and greatest version(s). The exercises or samples cover duplicating a database on the same server, and performing a tablespace point in time recovery (with a built-in error condition that requires some file editing).

Duplication on the same server

The reasons and need to duplicate a database are beyond the scope of this article (i.e., we know all the reasons why we do this). The setup for what is essentially the auxiliary instance – but is now the target for duplication – is practically identical to setting up the init.ora file as if it were being used for a tablespace point in time recovery (TSPITR). Other options for duplicating a database include creating it on a remote server and to a point in time in the past (either local or remote). Both examples are based on using a recovery catalog database.

As a cautionary note, duplicating on the same server does have a risk in that you may step on a production file, so definitely use one of two options: set a newname or use the DB_FILE_NAME_CONVERT parameter in the init.ora file. As a safeguard, use both (noting that the NEWNAME option in the RMAN session takes precedence over the parameter). Another pitfall is the nofilenamecheck option. If used incorrectly, it can lead to a disaster if you are performing the duplication on the same server.

Create a simple database, with system, RBS/UNDO, TEMP, and a tablespace of interest such as USERS. Put something in USERS (e.g., load the SCOTT schema) so you know you have something recognizable on the duplicate. Register the database with the catalog. Backup the database and toss in some archive log switches too. Once the source database is created, take note of pathing information in the init.ora file and on the file system with respect to where the datafiles were created. To make duplication and TSPITR operation similar, create a password file for the source.

Shown below is a complete script for the source database just described.

rman target / catalog=rman/rman@rmanprod.world
connect auxiliary sys/mypasswd@clone
run {
  allocate auxiliary channel dev1 type 'sbt_tape';
  set newname for datafile '/u065/oradata/train/system01.dbf' to 
  set newname for datafile '/u064/oradata/train/users01.dbf'  to 
  set newname for datafile '/u065/oradata/train/rbs01.dbf'    to 
  duplicate target database to "clone" logfile
    group 1 ('/u064/oradata/clone/log_1.f') size 10K REUSE,
    group 2 ('/u065/oradata/clone/log_2.f') size 10K REUSE;

Run at the command line with ORACLE_SID set to the source database (TRAIN), the first line connects the session to the target and to the catalog. The next line (which is purposely put as a second line despite other syntax example showing all three connections being done one line) connects you to the auxiliary instance (CLONE), which is going to be the name of the new database. Note that we connect via TNS to the clone even though it is on the same server (why?).

Why didn’t the script include a file for the temporary tablespace? And if we’re duplicating a database, wouldn’t it be more convenient to have the TEMP tablespace created too? RMAN does not backup tempfiles, so maybe the DUPLICATE command takes care of creating the TEMP tablespace for us. We’ll see.

The connection to the auxiliary instance is just like TSPITR: the status is nomount. This is actually a good test of the clone init.ora file: you can test startup nomount before getting into the RMAN session.

Much of what RMAN does in both scenarios is the same: lots of output, but within the output, you can see lines showing “RMAN-03027: printing stored script: Memory Script.” We’ll see where some of this comes from in the TSPITR example.

The stored script, after taking care of variable resolution based on the input you provided in the run block, looks like this:

RMAN-03027: printing stored script: Memory Script
   set until scn  135929;
   set newname for datafile  1 to
   set newname for datafile  2 to
   set newname for datafile  3 to
   set newname for datafile  4 to
   check readonly
   clone database

I named my duplicate as CLONE, and within the script the last line shows “clone database.” The name “clone” isn’t necessarily the best db_name in the world as it looks like it is a reserved word. If in doubt about a name, double quote it. Plenty of syntax examples state that you can use the file ID for the source file, and that is what RMAN did in the memory script. You can too, in your run block, but explicitly identifying the source file by name and path helps during the stare and compare phase of proofing your work before running the command.

Do you need to copy a controlfile in place beforehand? No, RMAN will create one for you, and upon examining the SQL statement in the RMAN output, what do you note about the log mode of the clone?

  GROUP  1 ( '/u064/oradata/clone/log_1.f' ) SIZE      10240  REUSE,
  GROUP  2 ( '/u065/oradata/clone/log_2.f' ) SIZE      10240  REUSE
 CHARACTER SET US7ASCII (other lines after this not shown)

Do you run your duplicate database(s) in archivelog mode or not? Looked at another way, even though you may edit your target init.ora file to remove all references to archiving, the control file for the clone/target is going to think it is running in archivelog mode. Why not? That is how the source database runs – which is obviously why we’re able to do all of this in the first place with RMAN. The takeaway here is that there may be some cleanup work at the end to take care of the differences between the init.ora file and the controlfile’s state of mind when it comes to the log mode.

During the recover phase of the duplication, RMAN applies archivelogs (which is why I mentioned creating some during the setup; you want to test this as well).

RMAN-08515: archivelog filename=/u060/oradata/train/train_2.arc thread=1 sequence=2
RMAN-08515: archivelog filename=/u060/oradata/train/train_3.arc thread=1 sequence=3
RMAN-08515: archivelog filename=/u060/oradata/train/train_4.arc thread=1 sequence=4
RMAN-08515: archivelog filename=/u060/oradata/train/train_5.arc thread=1 sequence=5
RMAN-08515: archivelog filename=/u060/oradata/train/train_6.arc thread=1 sequence=6

In my test run, RMAN was looking for sequence 7, so I blew it off because I know it didn’t have anything of relevance in it. The point in time (most current as possible) for the duplication was good enough. If you need to ensure a specific point in time, switch logfiles and archive them.

The end result of the duplication was the creation of the CLONE database. Its status was MOUNTED, and an attempt to open the database resulted in an ORA-00600 error, with arguments [3668], [1], [2], [768], [768], [4], [], []. The MetaLink/My Oracle Support ORA-600 lookup utility steers you to Doc ID 93665.1, “Media Recovery Required After CREATE CONTROLFILE.” The note suggest two solutions; the simplest is probably to just re-create the controlfile again (it’s a whole new database, it has all you need from production, it’s probably going to be blown away again later, so no harm, no foul in re-creating the CF). Was this error raised because RMAN didn’t include archivelog sequence 7?

Before you do, take care of the archiving status before doing “alter database backup controlfile to trace.” The difference of importance will be NORESETLOGS NOARCHIVELOG in the first line. Shutdown the instance, connect as sysdba, and run the new version of the create controlfile SQL script (get the trc file from the directory specified in USER_DUMP_DEST).

At this point, the database should be open for business and you now have a duplicate of the source.

In Closing

It is not uncommon to have the duplication process scripted out and have it running in place for years. If it always works, then great, but what if you need to re-create the script, or even better, want to understand everything it does, step by step? It is also useful to know how to perform the cloning or duplication process under the non-current duplicate database scenario. The time points, so to speak, can be tagged in one of three ways, although they eventually come back to one of them.

The “until time” option can be troublesome if the NLS date settings between the server (your RMAN session) and the database do not match. The sequence approach is easy (note that it is logseq, not sequence, as shown in several places on the Internet), but you have to dig that up from the data dictionary, which is not hard to do, but adds an extra step. The third, based on the SCN, and what the other two point back to, is also a sure way to nail a point in time, but again, it requires a little digging around to get the SCN from some point in the past.

You can also think of performing duplication to a point in time in the past as a superset of TSPITR, where if many tablespaces are involved in the recovery set, it may be more efficient to just do the entire database instead of trying to resolve dependencies among tablespaces.

This is an easy demo in terms of being able to set it up, to include using a recovery catalog database, and it doesn’t have to be from tape either. A PC with sufficient space and ability to run three relatively small instances is all you need. In the next article, we’ll look at a variation on TSPITR.

» See All Articles by Columnist Steve Callan

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.