Duplicate database for cloning

This post is aimed at providing an understanding of how to clone a PROD database to a TEST/DEV database using DUPLICATE DATABASE command. The following are the environmental specifications to aid in understanding the setup/implementation. This is implemented on a single Windows host.

Target DB (TDB) - The PROD database which is cloned to form the TEST/DEV database.
Auxiliary DB (ADB) - The TEST/DEV database which is cloned from the PROD database.

The above acronyms will be widely used in the entire post.

Before we move any further, make an entry for both the Target Database (TDB) and the Auxiliary Database (ADB) in the tnsnames.ora file, also, make sure the listener is running and that both the services of TDB and ADB are registered with the listener.


1. Start the ADB (Duplicate Database) in nomount mode and exit out of the SQL propmt since the instance goes down in the process of creating a cloned DB. The existing init.ora parameter file for the ADB could be used to start it in the nomount mode.

C:\Documents and Settings\individual\Desktop>set ORACLE_SID=ADB

C:\Documents and Settings\individual\Desktop>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 17 16:32:29 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1248428 bytes
Variable Size 83886932 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
SQL>
SQL>


2. Connect to the Target Database (TDB) using RMAN as sys or any user who has SYSDBA privileges from another session of command prompt and take a full backup of the TDB along with the archive logs, this is the backup which will be used to create the ADB.


C:\Documents and Settings\individual\Desktop>rman target sys/manager@TDB

TDBvery Manager: Release 10.2.0.1.0 - Production on Wed Sep 17 16:29:13 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TDB (DBID=1449103016)

RMAN>
RMAN>
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup format 'C:\oracle\rman_bkup\TDB_duplicate_database.bkup' database;
5> backup format 'C:\oracle\rman_bkup\TDB_duplicate_archives.bkup' archivelog all;
6> release channel c1;
7> }


3. Once the backup is complete, connect to the auxiliary instance from the same RMAN session.


RMAN> connect auxiliary sys/sys@ADB

connected to auxiliary database: ADB (not mounted)

RMAN>
RMAN>

It is to be noted that the password used for the sys account is actually an imaginary one and the server will anyway let you in. Any text/password could be used to connect to the auxiliary instance.

4. Now we are going to manually rename every file from the TDB to a valid location in the ADB. This is where we actually clone the database with a simple "DUPLICATE TARGET DATABASE TO" command.

This step is like the "CREATE CONTROLFILE" step of the conventional cloning process using a physical backup copy, which indeed gets executed in the background. It also involves giving names of the logfiles for the ADB as shown below, ignoring which would end in an error.


RMAN> run
2> {
3> allocate auxiliary channel aux_c1 device type disk;
4> set newname for datafile 'C:\ORACLE\TDB\SYSTEM01.DBF' to 'C:\ORACLE\ADB\SYSTEM01.DBF';
5> set newname for datafile 'C:\ORACLE\TDB\UNDOTS.DBF' to 'C:\ORACLE\ADB\UNDOTS.DBF';
6> set newname for datafile 'C:\ORACLE\TDB\SYSAUX.DBF' to 'C:\ORACLE\ADB\SYSAUX.DBF';
7> set newname for datafile 'C:\ORACLE\TDB\TOOLS.DBF' to 'C:\ORACLE\ADB\TOOLS.DBF';
8> set newname for datafile 'C:\ORACLE\TDB\USERS.DBF' to 'C:\ORACLE\ADB\USERS.DBF';
9> set newname for tempfile 'C:\ORACLE\TDB\TEMP.DBF' to 'C:\ORACLE\ADB\TEMP.DBF';
10> duplicate target database to ADB
11> logfile
12> group 1('C:\ORACLE\ADB\LOG1A.RDO') size 5M,
13> group 2('C:\ORACLE\ADB\LOG2A.RDO') size 5M;
14> }


After hitting return key at the 14th line, a lot of scripts are seen executing on the screen. Capturing each and every snap of it would make this post a real lengthy one so only a gist of the scripts are shown below to give users a brief snapshot of what actually happens.

allocated channel: aux_c1
channel aux_c1: sid=36 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 17-SEP-08


contents of Memory Script:
{
set until scn 271346;
set newname for datafile 1 to
"C:\ORACLE\ADB\SYSTEM01.DBF";
set newname for datafile 2 to
.
.
}


sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ADB" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1752
LOGFILE
.
.
.

}


contents of Memory Script:
{
set until scn 271346;
TDBver
clone database
delete archivelog
;
}


database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started


renamed temporary file 1 to C:\ORACLE\ADB\TEMP.DBF in control file

cataloged datafile copy
datafile copy filename=C:\ORACLE\ADB\UNDOTS.DBF recid=1 stamp=665686637


contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 17-SEP-08

RMAN>

RMAN>

A few memory scripts are run in the background as part of the DUPLICATE DATABASE creation. The cloned TEST/DEV database is now ready to be used by the Testers/Developers. Now a comparison between the TDB and ADB will show different values for DBID.


SQL> select DBID,NAME,CREATED,OPEN_MODE,LOG_MODE from v$database;

DBID NAME CREATED OPEN_MODE LOG_MODE
---------- --------- --------- ---------- ------------
1449103016 TDB 08-SEP-08 READ WRITE ARCHIVELOG



SQL> select DBID,NAME,CREATED,OPEN_MODE,LOG_MODE from v$database;

DBID NAME CREATED OPEN_MODE LOG_MODE
---------- --------- --------- ---------- ------------
4023825067 ADB 17-SEP-08 READ WRITE ARCHIVELOG

UNIX alias

How many times have you wished to have shortcuts for some long syntaxes/commands on a UNIX host. Here is a way to do away with such hassles, There is a command "alias" available in all the UNIX hosts nowadays which allows one to associate a letter or a shortest word with a long command.

Listed below are the ways of using "alias" in the two major shells of UNIX that I have worked with. Let say for an instance I want to see the number of databases running on a host with the command "ps -ef|grep pmon", this is now going to have an alias of pmon

ksh $> alias pmon='ps -ef|grep pmon'

Ksh $> pmon
oracle 12219 1 0 Jun 02 ? 11:32 ora_pmon_DEV1
oracle 26642 1 0 Mar 21 ? 99:51 ora_pmon_DEV2
oracle 23512 21604 0 16:34:50 pts/3 0:00 grep pmon
oracle 26901 1 0 Mar 21 ? 118:19 ora_pmon_DEV3


csh $> alias pmon 'ps -ef|grep pmon'

csh $> pmon
oracle 12219 1 0 Jun 02 ? 11:32 ora_pmon_DEV1
oracle 26642 1 0 Mar 21 ? 99:51 ora_pmon_DEV2
oracle 23512 21604 0 16:34:50 pts/3 0:00 grep pmon
oracle 26901 1 0 Mar 21 ? 118:19 ora_pmon_DEV3



Any valid UNIX command can be associated with an alias, below are a couple of more examples.

Alias to open ORATAB file of Oracle

ksh $> alias oratab='vi /var/opt/oracle/oratab'

csh $> alias oratab 'vi /var/opt/oracle/oratab'

Here is another example of Navigating to a directory like ORACLE_HOME.

ksh $> alias o_h='cd $ORACLE_HOME'
csh $> alias o_h 'cd $ORACLE_HOME'


Now the crux of this concept is where to place the "alias" entry so you need not execute this for every session you open. This entry has to be made in the .profile for a ksh enabled unix account and in the .cshrc for a csh enabled unix account.But make a backup of the .profile/.cshrc file before making any changes to it as a general rule of thumb to not mess up with the integrity of these files.

If you want to remove the aliases permanently, comment out/remove the alias line from the .profile/.cshrc file which takes effect only at the next login or run the unalias command in the current session as a temporary fix as shown below.

ksh $> unalias o_h
csh $> unalias o_h

Crontab

Here is some information about crontab and the flags that a crontab command can accept.

crontab -e --> edits the crontab file so you can make entries of scheduled jobs to run.

crontab -l --> lists the crontab file, its like running a "cat" command on a text file.

crontab -r --> removes the crontab file. The most dangerous, make sure you have a backup of your crontab file before you run this command even be it for test reasons.

One has got to be very careful in dealing with the -r flag. if you inadvertently type -r for -e and hit enter, the next thing you know is your crontab file is gone. since E and R are located next to each other on a regular QWERTY keyboard and if you are not really good with the finger positioning of the keyboard, this is sure a nightmare.

This had once happened with me since I am not good at the finger positioning myself on a QWERTY keyboard.It is only after hitting the return key that I realized, I typed the wrong command. But a backup of the crontab should save the day, like it did to me.

Its always a good practice to have a backup of the crontab file. Use the command "crontab -l" to backup the crontab file as shown below.

00 01 * * * crontab -l > /export/oracle/crontab_backup.txt

The above line takes a backup of the crontab at 01:00 hours everyday.

Now, to restore a blank crontab, all you need to do is run the following command. sometimes this might require a bounce of the cron daemon

crontab < /export/oracle/crontab_backup.txt

Let's discuss about the conventions of making entries in a crontab file. There are six fields for an entry in a crontab, below is a depiction which explains about the fields. Fields are seperated by a white space or a tab.

*  *  *  *  *  COMMAND_OR_SCRIPT_LOCATION
--------------------------------------------------------------------
|  |  |  |  |   |_ Command or script location
|  |  |  |  |
|  |  |  |  |___   Day_Of_Week (0 - 6) (0=Sun, 1=Mon, 2=Tue,3=Wed,
|  |  |  |                                    4=Thu, 5=Fri, 6=Sat)
|  |  |  |_____    Month_Of_Year (1 - 12)
|  |  |
|  |  |_______     Day_Of_Month (1 - 31)
|  |
|  |_________      Hour (0 - 23)
|
|___________       Minutes (0 - 59) 

White spaces in filenames

Recently I faced an issue in tranferring a file from Windows to UNIX host. Unlike UNIX, windows as usual accepts white spaces in filenames. A file called "Copy of sqlnet.log" was transferred to a UNIX host and when tried to execute commands like cat or more, it reported the usual error of "No such file or directory" since a single file name is treated as three different filenames due to the white spaces.

But when tried with double quotes around the filename, works just fine.

UNIX-$> more "Copy of sqlnet.log" or cat "Copy of sqlnet.log"

Now, removing such a file is the most daunting task. A simple 'rm Cop*' takes the file out of your server but takes other files from the directory starting with a 'Cop' as a complement.


Method-1

Conventional method of removing a file.

UNIX-$> rm "Copy of sqlnet.log"

Removal of such a file using double quotes around it works good,as shown above. But just to innovate something new and flaunt about it to my colleagues I used the below method.


Method-2

Get the inode number of the file by a simple 'ls -li', this shows an extra field in the long listing of files. The first field of such a command's output is the inode number. To get an explanation about inode, follow my earlier post Inodes and Links

UNIX-$> ls -li

121604 -rwxr-xr-x 1 oracle oracle 96 Feb 21 18:00 oratab_bkup.txt
121621 -rw-rw-r-- 1 oracle oracle 576 Feb 21 23:55 db_check_ora_home.parm
121557 -rw-rw-r-- 1 oracle oracle 4244 Feb 22 01:00 cron_backup.txt
121638 -rw-r--r-- 1 oracle oracle 868 Feb 22 11:28 Copy of sqlnet.log

Now, use the 'find' command to remove the file with '-inum' and '-exec' arguments. -inum argument expects a file's/directory's inode number to be passed.

UNIX-$> find ./ -inum "121638" -exec rm {} ;

This method precisely fits the following anomaly. Imagine a directory full of files with extension '.dbf', User unknowingly makes a mistake and issues a command 'ls -ltr > *.dbf' to list all the files in the long format.

The output of the above command would create a file called '*.dbf' and the contents are long listing of all the '.dbf' files. Now, would anyone be really foolish enough to try 'rm *.dbf' ??? , the next thing you know is he/she is out of the Organisation (Chuckles!!!) . Well a good backup strategy should save the day .

Though the existence of such a file using up a few bytes is not a big deal, but it gives you an awkward look of your files, an entry as *.dbf. All you need to do to have a pleasant look of your '.dbf' files is to get the inode number of the file '*.dbf' and take it down.

UNIX-$> ls -li

1603 -rwxr-xr-x 1 oracle oracle 96 Feb 21 18:00 *.dbf
1628 -rw-rw-r-- 1 oracle oracle 576 Feb 21 23:55 three.dbf
1637 -rw-rw-r-- 1 oracle oracle 4244 Feb 22 01:00 two.dbf
1630 -rw-r--r-- 1 oracle oracle 868 Feb 22 11:28 one.dbf

UNIX-$> find ./ -inum "1603" -exec rm {} ;

Now, the file "*.dbf" is gone forever and a long listing of *.dbf only shows the actual files as shown below.

UNIX-$> ls -li

1628 -rw-rw-r-- 1 oracle oracle 576 Feb 21 23:55 three.dbf
1637 -rw-rw-r-- 1 oracle oracle 4244 Feb 22 01:00 two.dbf
1630 -rw-r--r-- 1 oracle oracle 868 Feb 22 11:28 one.dbf