Oracle 10g Cloning Guide: A Small County Government Approach

by

Kevin Medlin

Abstract

Database
refreshes are one of the most popular ad hoc requests that a database
administrator receives. Data becomes stale for testing or becomes corrupt after
much development. In any case, data refreshes are a way of life. This document
offers one approach to database refreshes for Oracle 10g databases on Windows
32 and 64 bit servers. Recovery Manager (RMAN) is employed as the primary backup
application. Using RMAN backups, we can use the cloning process to recreate the
production environment in any of the test environments. All steps are
automated, and scripts are provided with explanations in the document.

In my first article of this series, “Oracle 10g
Backup Guide,” we performed all of the necessary setup for cloning a
database. Here are the steps we accomplished:

Our 9 steps for a great
10g nightly backup strategy are;

For
complete explanations, setup information, scripts, suggestions, etc. see the
first article here.

Cloning an Oracle database is a very effective way
to refresh data in development, test, and QA environments (Hart & Jesse, 2004, p.309). It is so
easy, you can literally run it in your sleep! I’m going to show you how we have
our cloning jobs set up so you can run them whenever you want. Everything will
be in place before hand and you can just start a scheduled task. If you are
like us, you can schedule a select few of them to run on a nightly basis
automatically. We normally have this set up with databases on different
servers, or nodes. It also works when the databases are on the same node.

A couple of parameters you will need to set are DB_FILE_NAME_CONVERT
and LOG_FILE_NAME_CONVERT. These pfile parameters change the target file names
to the auxiliary file names (Alapati, 2005, p. 727). My recommendation is to
keep the same drive layout on your servers. This will make things much simpler.
For instance, if you keep your data files on the O:\ drive and your redo logs
on the L:\ drive in production, try to use the same in test. Using these
parameters, you will only need to list the database names to convert the files
instead of listing directory structures as well.

Everything starts with one batch program. Clone_db
is the core to automating a database refresh. We will first be looking at
clone_db_NoArch.bat. We call clone_db as a scheduled task. Here is the command
used in Windows Scheduled Tasks:

Code Listing 1:

D:\oracle\admin\common\clone\clone_db_NoArch.bat TargetDB CatalogDB AuxDB

There
are three batch parameters that are passed with the command. The first is the
target database name (TechNet, 2008). The target database is the database you
will be copying your backup from. The second variable is the catalog database
name. This is the catalog database you used when you performed your backup on
the target database. The last variable is the auxiliary database name. This is
the name of the database you are refreshing, or cloning.

Code Listing 2:

@rem

set TargetDB=%1

set CatalogDB=%2

set AuxDB=%3

set ORACLE_SID=%AuxDB%

set local=%AuxDB%

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1

set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

set NLS_DATE_FORMAT=YYYY-MON-DD HH24:MI:SS

set PASSWORD=dbpassword

set RCATPW=catpassword

if defined TargetDB  (echo TargetDB  is defined as %TargetDB%)  else exit

if defined CatalogDB (echo CatalogDB is defined as %CatalogDB%) else exit

if defined AuxDB  (echo AuxDB  is defined as %AuxDB%)  else exit

set CURDIR=D:\oracle\admin\common\clone

@rem

cd %CURDIR%

@rem

%ORACLE_HOME%\bin\sqlplus /nolog @startup_AuxDB.sql

%ORACLE_HOME%\bin\rman target sys/%PASSWORD%@%TargetDB% catalog

  %CatalogDB%/%RCATPW%@%CatalogDB% auxiliary sys/%PASSWORD%@%AuxDB%

  log=logs\clone_%TargetDB%_to_%AuxDB%.log @clone_to_%AuxDB%.rcv

page_on_clone_plus_email.vbs %1 %2 %3

%ORACLE_HOME%\bin\sqlplus /nolog @tempfile_%AuxDB%.sql

@rem

%ORACLE_HOME%\bin\sqlplus /nolog @global_name_%AuxDB%.sql

%ORACLE_HOME%\bin\sqlplus /nolog @noarchivelog.sql

@rem

You will notice that the first three commands are
setting the parameters that have been passed into the script. The next five
commands are setting additional parameters needed by Oracle to run RMAN
effectively. There are two commands that set the ORACLE_SID and local variables
to the auxiliary database name. ORACLE_HOME is set next to let Oracle know
where the software is installed. Setting NLS_LANG removes any doubt about the
database character set (Stern & Womack, 2004, p. 4-10). We set
NLS_DATE_FORMAT this way because it matches our backup and our clone file
creation. The database password and catalog password that will be used several
times in the script are set next. The next three commands make sure that the parameters
are not empty. If they are empty then the script is stopped. The current
directory of the clone_db script is set, and then the directory is changed
there.

Now we are getting to the nitty gritty, we are
calling our first Oracle script! Startup_AuxDB.sql performs a little more than
just a startup. In fact, it does more and less. Let’s take a look at the script
to see what I’m talking about.

Code Listing 3:

connect sys/%password% as sysdba;

set time on;

set echo on;

set scan off;

spool logs\startup_%AuxDB%.log;

startup force nomount;

spool off;

exit;

After
connecting as SYS the output is spooled to a log file. Then the auxiliary
database is forced into a startup nomount state (Kumar, 2005). Of course, this
actually forces a shutdown, and then starts up the database. Shutdown immediate
usually works just as well. There has been a time or two when an immediate
shutdown has hung. Why take a chance?

Now
we are to the heart of the script. This is the RMAN command that calls the
actual RMAN script that performs the clone. I have provided IDs, passwords, and
database names so the command can be automated. A log file will be created so
that a history of the clone job will be kept. If there is an error, we will be
able to refer back to this log file. The clone script itself was created with
the target database backup.

Next,
we have a Visual Basic script that sorts through whether or not we have an
error and immediately sends us a page. This script runs at every execution and
sends an email with the log output. This is something we want on these jobs
regardless of whether the job completes successfully or not. But if it fails,
we want an email of the log and a page indicating the failure.

Tempfiles are sometimes much larger in production
than they should be for test environments. For this reason, we recreate them.
This is the next command you see. It is the Oracle script tempfile_%AuxDB%.sql. You will notice that part of
the script name is a variable. This is because we have a separate tempfile
creation script for each database we clone. In this case, we have the database
name in file directory. Let’s take a look at the script:

Code Listing 4:

connect sys/%password% as sysdba;

set time on;

set echo on;

set scan off;

spool logs\tempfile_%AuxDB%.log;

select a.name, b.name from v$tablespace a, v$tempfile b where a.ts#=b.ts#;

select tablespace_name from dba_tablespaces where contents = ‘TEMPORARY’;

alter system set db_create_file_dest=’O:\oradata\AUXDATABASE’;

create temporary tablespace temp_ts extent management local;

alter database default temporary tablespace temp_ts;

drop tablespace temp including contents and datafiles;

create temporary tablespace temp tempfile ‘O:\oradata\AUXDATABASE\temp01.dbf’

size 572M reuse autoextend on next 100M maxsize 2048M;

alter tablespace temp add tempfile ‘O:\oradata\AUXDATABASE\temp02.dbf’

size 572M reuse autoextend ON NEXT 100M MAXSIZE 2048M;

alter tablespace temp add tempfile ‘O:\oradata\AUXDATABASE\temp03.dbf’

size 572M reuse autoextend ON NEXT 100M MAXSIZE 2048M;

alter database default temporary tablespace temp;

drop tablespace temp_ts including contents and datafiles;

select a.name, b.name from v$tablespace a, v$tempfile b where a.ts#=b.ts#;

spool off;

exit;

After connecting as SYS the output is spooled to a
log file. Next a query is run to see what tempfiles currently exist. You will
find what came from your target database. Next is a query to show the temporary
tablespaces in the database. The next command sets a destination for Oracle
managed files (Greenwald, 2004). The next two lines create a short-term temporary
tablespace and then make it the default for the database. The original
temporary tablespace is dropped and recreated. New, smaller tempfiles are
created for the temporary tablespace. The original temporary tablespace is made
the default and the short-term temporary tablespace is dropped. One more query
is run to see to ensure the new tempfiles now exist.

Global_name would be the next piece to be updated. Our
new database still has the global_name of the old database and this needs to be
corrected (Looney, 2005, p. 968). There is a short script that can take care of
it, global_name_%AuxDB%.sql. Again, here you need a different script for each
database you plan to clone since they each have a different name.

Code Listing 5:

connect sys/%password% as sysdba;

set time on;

set echo on;

set scan off;

spool logs\global_name_auxdatabase.log;

alter database rename global_name to AUXDATABASE;

grant sysdba to ALTSYSID;

commit;

spool off;

exit;

This is a simple script but very important one.
There are really only two commands we are concerned with. The first is renaming
the global_name to the auxiliary database name. Many developers will use
global_name to check the name of their database since they do not have access
to dictionary tables. The second command grants sysdba to an alternate SYS ID
we use in the database. This needs to be explicitly granted on this ID after
the clone.

One additional step is to put the database in noarchivelog
mode using noarchivelog.sql. This will save on space and headaches down the
road.

Code Listing 6:

connect sys/%password% as sysdba;

set time on;

set echo on;

set scan off;

spool logs\noarchivelog_%AuxDB%.log;

shutdown immediate;

startup mount exclusive;

alter database noarchivelog;

alter database open;

select dbid,name,log_mode from v$database;

spool off;

exit;

Initially, shut down the database and start it back
up in mount mode. Then alter the database for no archive logging and open the
database for use. It is no longer necessary to stop archiver processes. This
happens automatically depending on the archive mode of the database (Bersinic & Watson, 2005, p. 22). For troubleshooting purposes, a select
statement is run at the end of the script showing the database ID, database
name, and archive log mode.

One thing you may have noticed is that the
passwords are the same for the target database and the auxiliary database. This
is by design. It is much easier to perform a database clone this way. You can
clone databases with different passwords but you will have the extra manual
step of recreating the service, unless you want to recreate the password file
separately.

We
have just completed the script clone_DB_NoArch.bat. It cloned a database and
put it in noarchivelog mode. Well, what if you want to keep your database in
archivelog mode? You will need to perform backups, which can be set up using
the Oracle 10g backup guide. You will also need a different cloning script
called clone_DB_Arch.bat.

Code Listing 7:

@rem

set TargetDB=%1

set CatalogDB=%2

set AuxDB=%3

set ORACLE_SID=%AuxDB%

set local=%AuxDB%

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1

set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

set NLS_DATE_FORMAT=YYYY-MON-DD HH24:MI:SS

set PASSWORD=dbpassword

set RCATPW=catpassword

if defined TargetDB  (echo TargetDB  is defined as %TargetDB%)  else exit

if defined CatalogDB (echo CatalogDB is defined as %CatalogDB%) else exit

if defined AuxDB  (echo AuxDB  is defined as %AuxDB%)  else exit

set CURDIR=D:\oracle\admin\common\clone

@rem

cd %CURDIR%

@rem

%ORACLE_HOME%\bin\sqlplus /nolog @startup_AuxDB.sql

%ORACLE_HOME%\bin\rman target sys/%PASSWORD%@%TargetDB% catalog

  %CatalogDB%/%RCATPW%@%CatalogDB% auxiliary sys/%PASSWORD%@%AuxDB%

  log=logs\clone_%TargetDB%_to_%AuxDB%.log @clone_to_%AuxDB%.rcv

page_on_clone_plus_email.vbs %1 %2 %3

%ORACLE_HOME%\bin\sqlplus /nolog @tempfile_%AuxDB%.sql

@rem

rman target sys/%PASSWORD%@%AuxDB% catalog %CatalogDB%/%RCATPW%@%CatalogDB%

  log=logs\re_register_%AuxDB%.log @re_register_DB.rcv

@rem

%ORACLE_HOME%\bin\sqlplus /nolog @global_name_%AuxDB%.sql

@rem

There
are only two differences in the clone_DB_NoArch and clone_DB_Arch batch
scripts. The first is there is no noarchivelog.sql in the clone_DB_Arch. The
second difference is that there is an additional RMAN script in the archive
version. Re_register_DB.rcv registers the newly cloned database in the RMAN
catalog so that it can begin or continue to be backed up.

Code Listing 8:

register database;

list incarnation;

exit;

The
first command registers the database in the RMAN catalog. The second command
lists the incarnation of the database in the catalog. The output is captured in
the RMAN log file for troubleshooting purposes (Hart & Freeman, 2007).

Additional
scripts can be added to the clone_DB batch scripts. There may be unique
characteristics about a set of databases you refresh. You may need to replace
database links, refresh materialized views, or some other kind of update. Here
are a few examples of the types of scripts you might add to your clone_DB batch
script:

Code Listing 9:

%ORACLE_HOME%\bin\sqlplus /nolog @update_%AuxDB%.sql

%ORACLE_HOME%\bin\sqlplus /nolog @create_DBUSER.sql

%ORACLE_HOME%\bin\sqlplus /nolog @refresh_snapshots.sql

%ORACLE_HOME%\bin\sqlplus /nolog @create_links_%AuxDB%.sql

%ORACLE_HOME%\bin\sqlplus /nolog @ChangePassword.sql

Cloning
databases is the fastest way to refresh data in a database. This is also a
great way to test out recovery scenarios (Bryla, 2004). Having scripts such as
these set up in your environment, can help you automate such data refreshes
saving time, energy, training, and sometimes frustration. Keeping customers
happy is an important part of every DBA’s job. This is one way to help
accomplish these goals.

References

Alapati, Sam R. (2005). Expert
Oracle Database 10g Administration. New
York. Springer-Verlag
New York, Inc.

Bersinic, Damir & Watson, John
(2005). Oracle Database 10g OCP Certification All-In-One Exam Guide
[Electronic Version]. California. The McGraw-Hill Companies, Inc.

Bryla, Bob (2004). Oracle Database
Foundations. California. Sybex, Inc.

Greenwald, Rick, Stackowiak, Robert
& Stern, Jonathan (2004). Oracle Essentials: Oracle Database 10g. California. O’Reilly Media, Inc.

Hart, Matthew & Freeman, Robert G.
(2007). Oracle Database 10g RMAN Backup & Recovery. California. The McGraw-Hill Companies, Inc.

Hart, Matthew & Jesse, Scott (2004).
Oracle Database 10g High Availability With RAC, Flashback & Data Guard.
California. The McGraw-Hill Companies, Inc.

Kumar, Arun R., Kanagaraj, John &
Stroupe, Richard (2005). Oracle Database 10g INSIDER SOLUTIONS
[Electronic Version]. Indiana. Sams Publishing.

Looney, Kevin (2005). Oracle
Database 10g: The Complete Reference. California. The McGraw-Hill Companies, Inc.

Microsoft Technet (2008). Command
Line Reference A-Z. Retrieved 04-02-2008 from http://technet.microsoft.com/en-us/library/bb490873.aspx.

Stern, Janet & Womack, James
(2004). Oracle Database 10g: Administration Workshop II, Student Guide. California. Oracle Corporation.

Kevin Medlin has been administering, supporting, and developing in a variety of industries including energy, retail, insurance and government since 1997. He is currently a DBA supporting Oracle and SQL Server, and is Oracle certified in versions 8 through 10g. He received his graduate certificate in Storage Area Networks from Regis University and he will be completing his MS in Technology Systems from East Carolina University in 2008. When he’s not trying to make the world a better place through IT, he enjoys spending time with his family, traveling, hanging out by the pool, riding horses, hiking, and camping.