Carlson Skunk Works

March 28th, 2008

Oracle DBA Stuff

Posted by Roger in Oracle DBA

This is my first post of Oracle DBA related stuff. Maybe I can remember to update this as I work on the databases and run into things that I think are interesting.

What I am working on this week is duplicating our development database to a testing environment. This has been done before several times by me, but for some reason it is giving me fits this time. Due to the size of the database the duplication takes about five hours to reach the point where it fails. That gets all the data files copied from the RMAN backup on the dev system to their proper location on the test system. This part seems to work quite nicely. The thing that is giving the problem is that at the point where RMAN is supposed to shut down the database and restart it, I get an error about the database not being available.

I printed out chapter 13, “Creating and Updating Duplicate Databases with RMAN”, from the RMAN manual. I thought about printing the whole manual, but it is almost 500 pages. I also got out Volume II – Student Guide from the Oracle Database 10g: Backup and Recovery class that I took last summer. I am referencing the “Solutions for Practice 6-1: Create a Duplicate Database” section in the class manual. By combining the instructions from each of these documents and filling in the holes from experience and Googling, I have been following this procedure:

1) System preparation and software installation:

The test database server is a Sun V210 with a newly installed copy of Solaris 9/05 that has had the patches 112874-33, 113225-08 and 118335-08 installed. I followed the Oracle 10g installation procedure laid out by Roger Schrag in his paper “Installing and Configuring Oracle Database 10g on the Solaris Platform” as far as installing 10.1.0.2.0 was concerned. We are running the bare first release of Oracle 10g and part of the function of the duplicated database will be to test upgrading to 10.1.0.4.0 and 10.1.0.5.0, but that will come later, so we just want the bare 10.1.0.2.0 Oracle software installed for now.

The patches that I installed on the system are to allow X fowarding across ssh and to implement the daylight savings time changes that went into effect last year.

The database was installed with the Oracle Universal Installer. I did not install any example or sample databases during the installation. Once the database is installed and running, the next step is to shut it down.

In order to create the duplicate database the source database server needs to be accessible from the test server. In my case I needed ports 22 and 1521 open both ways.

I am using RMAN to do daily full backups of the source database. These files are compressed and stored on a disk array that is mounted vis NFS across a direct (point to point) gigabit network connection. I duplicated this connection on the test system and mounted the drive with an identical mount point name. The duplicate database comes from the backup files, therefore I am (initially anyway) wanting to duplicate the database from development the way it was yesterday at this time. This will be important to keep in mind later on.

2) Setting up for duplication:

Information:
The ORACLE_SID will be “test”.
The password for the sys and system users will be “cafin8ed”

Task 1: Create an Oracle Password File for the New Database
This is fairly straight forward, but there were a couple of gaps in the information that I had. The password that you use is the password that you will use when you login as the sys user. Therefore, if you want to be able to use the command “sqlplus sys/charles as sysdba” the password that you should use with the orapwd command would be “charles”. The other thing that is not obvious is that the ORACLE_SID is case sensitive. If the SID of your new database is going to be “test”, then don’t set it some places as “TEST” and others as “test”. This is also used in naming your password file.

I like to use the password from my source database, just to keep things the same until after the duplication is completed. That would make the passwords on the source and target databases the same.

So, the first step is to go to $ORACLE_HOME/dbs and make sure that there is not a password file in there that has the same name as the password file that you intend to use. If you used your new SID during the installation of the database software there probably will be. Either remove the file or rename it. I like to rename it until I am sure that the new one is properly in place.

When that is clear we can create the new password file with the command:
orapwd file=orapwtest password=cafin8ed entries=10

Task 2: Establish Oracle Net Connectivity to the New Database
What this means is that you need to get your tnsnames.ora file setup correctly to allow you to connect to both your source database and to your new database. I simply copy the tnsnames.ora file from my source database to the new database, then edit it to add the entry for my new database.

The tnsnames.ora file from my source database:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.1.0/src/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SRC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devdbserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = src)
)
)

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = proddbserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

The tnsnames.ora file from my new database:
# tnsnames.ora Network Configuration File: /s01/app/oracle/product/10g/test/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = newdbserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

SRC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devdbserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = src)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Since these are different servers you need to be sure that the server names used in the tnsnames.ora files are known to both systems. I just updated my /etc/hosts files on each system. I also added an entry to my source database tnsnames.ora file for the test database, just in case I needed to access it from the source system at some time.

When these files are in place and the new instance is up you should be able to use tnsping to verify that you can access each of the instances. If you have problems, they will need to be corrected before continuing.

Task 3: Create an Initialization Parameter File for the New Database.
This parameter file is to get you going. It can be modified once the new database is up and running. The way to create this is to get a copy of the parameters that are currently being used on the source database. Therefore, logon to the source database server and decide where you want to put the initialization parameter file. Then create the file from the spfile (if you are using an spfile – most people will be) or copy the current pfile.

The command to create the pfile from the spfile is fairly simple. In my case I did the following:
SQL> create pfile=’/export/home/oracle/initTEST.ora’ from spfile;

Then you need to copy thie initTEST.ora file from the source system to the new database server. I just leave the file in my Oracle user’s home directory.

Now the “hard” part! The initTEST.ora file needs to be edited to prepare it for the duplication process. Here is what my init file looks like before editing:

SRC.__db_cache_size=4412407808
SRC.__java_pool_size=16777216
SRC.__large_pool_size=16777216
SRC.__shared_pool_size=1107296256
*._db_block_hash_latches=10240
*._recyclebin=false
*.compatible=’10.1.0.0.0′
*.control_files=’/u02/oradata/src/control.001.dbf’,’/u02/oradata/src/control.002.dbf’,’/u02/oradata/src/control.003.dbf’
*.db_block_size=8192
*.db_cache_size=8M
*.db_domain=’bucklehq.com’
*.db_file_multiblock_read_count=16
*.db_name=’src’
*.db_recovery_file_dest=’/u01/oradata/logs’
*.db_recovery_file_dest_size=59055800320
*.db_writer_processes=2
*.fast_start_mttr_target=600
*.filesystemio_options=’SETALL’
*.java_pool_size=0
*.job_queue_processes=250
*.large_pool_size=0
*.log_buffer=100000000
*.open_cursors=10000
*.pga_aggregate_target=650M
*.processes=250
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=5400M
*.sga_target=5670699008
*.shared_pool_size=104857600
*.timed_statistics=TRUE
*.trace_enabled=false
*.undo_management=’AUTO’
*.undo_retention=1800
*.undo_tablespace=’SRC_UNDO’

There are a number of changes that I made, so I will step through them one at a time and try to explain why I did each one.

The first thing to do is get rid of any entry that starts with the old instance name. In my case that is anything that starts with “SRC.”. Just delete these lines.

Next, the new instance needs to know where it should put the control files and what to call them. Edit the line that begins “*.control_files=” to identify where your new control files should be located.

Be sure to change the db_name from the source database, “src”, to the new database, “test”. Be sure to be careful about the case!

Do not change the db_block_size value! If it is set leave it’s value. If it is not set, then don’t set it.

I wanted to change the location where the archive log files were stored, so I changed the value of the line that starts “*.db_recovery_file_dest=”.

I also wanted to specify where my dump files and audit files went, so I added lines to set the audit_file_dest, background_dump_dest, core_dump_dest and user_dump_dest locations. If you already have these lines you may want to change the values to meet your needs.

The last thing to do is to tell the system where to put the datafiles. In my case I was changing the path to the various files. At other times I have gone from a source system with the files spread across multiple small disks and moved to a system that had a single partition on a disk array. Use the DB_FILE_NAME_CONVERT parameter to change the file names at this point. Use the LOG_FILE_NAME_CONVERT to change where the log files are located. In each case the values given to the *_CONVERT parameter need to come in pairs: ,. You can even change the location of each particular file by putting a value pair for each file that will be created on the new system. I haven’t had a need to become quite so fancy yet.

So after all the editing, here is my finished initTEST.ora file:

*._db_block_hash_latches=10240
*._recyclebin=false
*.compatible=’10.1.0.2.0′
*.control_files=’/s01/oradata/test/control01.ctl’,’/s01/oradata/test/control02.ctl’,’/s01/oradata/test/control03.ctl’
*.db_block_size=8192
*.db_cache_size=8M
*.db_domain=’bucklehq.com’
*.db_file_multiblock_read_count=16
*.db_name=’test’
*.db_recovery_file_dest=’/s01/logs’
*.db_recovery_file_dest_size=55G
*.db_writer_processes=2
*.fast_start_mttr_target=600
*.filesystemio_options=’SETALL’
*.java_pool_size=0
*.job_queue_processes=250
*.large_pool_size=0
*.log_buffer=100000000
*.open_cursors=10000
*.pga_aggregate_target=650M
*.processes=250
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=5400M
*.sga_target=5670699008
*.shared_pool_size=104857600
*.timed_statistics=TRUE
*.trace_enabled=false
*.undo_management=’AUTO’
*.undo_retention=1800
*.undo_tablespace=’WSD_UNDO’
audit_file_dest=’/s01/logs/adump’
background_dump_dest=’/s01/logs/bdump’
core_dump_dest=’/s01/logs/cdump’
user_dump_dest=’/s01/logs/udump’
DB_FILE_NAME_CONVERT=’/u01/oradata/src’,’/s01/oradata/test’,’/u02/oradata/src’,’/s02/oradata/test’
LOG_FILE_NAME_CONVERT=’/u03/oradata/src’,’/s01/oradata/test’

Task 4: Start the New Database
There are two documented ways to do this. Either way should work, but I have had the best luck with the method that is taught in the Backup and Recovery class.

Be sure your SID is set correctly! This is where I think I have gotten into trouble with case sensitivity. So for our example do the following:
$ ORACLE_SID=test
$ export ORACLE_SID
$ echo $ORACLE_SID

The response should be “test”.

Now login to sqlplus as the sysdba:
$ sqlplus / as sysdba

You should now be connected to an idle instance.

Startup the instance using the initTEST.ora file that you have just gotten done editing:
SQL> startup nomount pfile=’/export/home/oracle/initTEST.ora’

Then create a server parameter file from the pfile:
SQL> create spfile from pfile=’/export/home/oracle/initTEST.ora’;

When you are told that the file has been created you can exit from sqlplus.

Task 5: Mount or Open the Source Database

In my case the source database is always in use, so it is always open and mounted. However, it is a good idea to verify that you are able to access the source database from the new system, just to be sure. Therefore, do the following:
$sqlplus sys/cafin8ed@src as sysdba
SQL> select open_mode from v$database;

You should see that the database has an open mode of “READ WRITE”.

3) Do the duplication!

If everything has gone ok so far, you should be ready to kick off the actual duplication commands. I like to script these, but they can be done from the command line just as easily. The main reason that I script mine is that I like to kick off a duplication just before I go home and let it run in the background. So I set up a shell script that calls RMAN with CMDFILE and LOG parameters. Then I can come in the next morning and see what happened by looking at the log file.

My duplication script looks like this:
#!/bin/ksh

#-> set -x

ORACLE_SID=test
export ORACLE_SID

ORACLE_BASE=/s01/app/oracle
export ORACLE_BASE

ORACLE_HOME=/s01/app/oracle/product/10g/test
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/ccr/bin:/usr/bin:/opt/sfw/bin:/usr/local/bin:/usr/ucb:/etc:.
export PATH

NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG

NLS_DATE_FORMAT=YYYY Mon DD HH24:MI:SS
export NLS_DATE_FORMAT

RMAN=/s01/app/oracle/product/10g/test/bin/rman
COMMANDFILE=/export/home/oracle/RMAN/copy.scrpt
LOGFILE=/export/home/oracle/RMAN/copy.log
RUNCHECKFILE=/export/home/oracle/RMAN/copy.out

echo “Running RMAN duplication of SRC to TEST at `date`” >$RUNCHECKFILE
echo >>$RUNCHECKFILE

$RMAN CMDFILE = $COMMANDFILE LOG = $LOGFILE

echo >>$RUNCHECKFILE
echo “RMAN duplication of SRC to TEST completed at `date`” >>$RUNCHECKFILE
echo >>$RUNCHECKFILE
echo “Please check the setup log \”/export/home/oracle/RMAN/copy.log\” for details.” >>$RUNCHECKFILE

My RMAN script file that has the commands to actually do the duplication looks like this:

connect auxiliary sys/o4guk8x
connect target sys/o4guk8x@src

run
{
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
allocate auxiliary channel aux3 device type disk;
duplicate target database to test until time ‘sysdate – 1’;
}

The reason that I allocated three channels was because the Oracle documentation says “If the backups reside on disk, then the more channels you allocate, the faster the duplication will be.” I have not experienced any speedup in the duplication process in going from one channel to three channels. However, I am going to try adding more channels to the backup as well to see if that will help. I suspect that increasing the number of channels will only work up to the number of channels that were used to create the backup. This is something that I will be checking out later. Right now it is taking about five hours to do a duplication in my environment.

What I do to start the duplication is:
$ nohup ./copy.sh &
$ tail -f copy.log

and just sit and watch it do its stuff.

That is it for now. I hope you enjoyed this and found it useful.