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
'/u064/oradata/clone/system01.dbf';
set newname for datafile '/u064/oradata/train/users01.dbf' to
'/u064/oradata/clone/users01.dbf';
set newname for datafile '/u065/oradata/train/rbs01.dbf' to
'/u064/oradata/clone/rbs01.dbf';
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;
}
'/u064/oradata/clone/system01.dbf';
set newname for datafile '/u064/oradata/train/users01.dbf' to
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
"/u064/oradata/clone/system01.dbf";
set newname for datafile 2 to
"/u064/oradata/clone/rbs01.dbf";
set newname for datafile 3 to
"/u064/oradata/clone/users01.dbf";
set newname for datafile 4 to
"/u064/oradata/clone/tempxx.dbf";
restore
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?
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "clone" RESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 5 MAXDATAFILES 255 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 ( '/u064/oradata/clone/log_1.f' ) SIZE 10240 REUSE, GROUP 2 ( '/u065/oradata/clone/log_2.f' ) SIZE 10240 REUSE DATAFILE '/u064/oradata/clone/system01.dbf' 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.
