by Kevin Medlin
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;
1. Delete old log files and rename current logs.
2. Delete all RMAN backup files.
3. Perform a level 0 (zero) RMAN backup.
4. Create clone files.
5. Create archivelog backup, which includes Recovery Catalog housekeeping.
6. Delete data pump export files.
7. Perform data pump export.
8. Check logs for errors.
9. Page and/or email short error description.
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! Im 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. Lets take a look at the script to see what Im 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. Lets 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 DBAs job. This is one way to help accomplish these goals.
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.