When you are requested to refresh a database you can achieve it from hot backup, export backup and cold copy of database. Depending on situation which type of backup you have and how much time you have left, you have to choose wisely a method to refresh your database or recover completely from scratch. Here in this document we are dealing with refreshing or recreating the database from cold backup.

You must know what are the database files you have to copy and modify at destination.

• init.ora
• listener.ora
• tnsnames.ora
• database files (*.dbf)
• create control file

From source database issue the command to collect datafile names and sizes.

SQL> select name from v$database;

NAME

PROD

SQL> select name from v$datafile;

NAME

/u01/oradata/PROD/system.dbf

/u02/oradata/PROD/rollback01.dbf

/u03/oradata/PROD/users01.dbf

/u03/oradata/PROD/temp01.dbf

/u04/oradata/PROD/aufs_data01.dbf

/u05/oradata/PROD/afus_indx01.dbf

SQL> select sum(bytes) from dba_data_files;

SUM(BYTES)

5408982342

SQL> alter database backup controlfile to trace;

Database altered.

You can follow any method to transfer files from one machine to another machine, here are some tips.

• ftp
• rcp
• rdist

for rcp and rdist usage, you must create .rhosts file in source and destination hosts. Add simply an entry either hostname or hostname.domainname in .rhosts fle.

Usage of rcp and rdist are

From source machine

$ rcp source_path/filename destinatin_host_name:/destinationpath/filename

$ rdist source_path/filename destinatin_host_name:/destinationpath/filename

If you use rdist, it will display the file names while copying and also it compares the binary versions of the file it it exists at the destnation and updates to the latest.

Shutdown the source database normally, copy initPROD.ora to new location of $ORACLE_HOME/dbs. Edit the init.ora file to reflect the new database name eg. DROY or whatever name you choose.

Modify the following parameters in init.ora file to reflect new database and create related sub directory structures.

• db_name
• control_files
• background_dump_dest
• core_dump_dest
• user_dump_dest
• log_archive_dest

Copy all datafiles to destination . No need to copy control files and redo log files, these will be generated automatically during startup.

Transfer a text copy of control file from user_dump_dest of source database PROD to new host. You can use ‘ls –lrt’ to find the last generated trace file. Copy to destination as "cr_ctrl_db.sql".

• Remove all the lines till "STARTUP NOMOUNT" from the beginning
• CREATE CONTROLFILE ……; , statement will end with a semicolon ";"

• Insert a word "SET" before the DATABASE word in CREATE CONTROLFILE line[ optional if oracle is R8i ]
• Modify Database name to DROY on CREATE CONTROLFILE line
• Modify NORESETLOGS to RESETLOGS in CREATE CONTROLFILE line
• Modify all LOGFILE locations
• Modify all DATAFILE locations to reflect you new locations

Modified control file in oracle 8i will look like this

Set ORACLE_SID to DROY, check ORACLE_HOME is set properly.

Invoke server manager line mode "svrmgrl" and follow the following commands below

SVRMGR> connect internal

SVRMGR> startup nomount

SVRMGR> @cr_ctrl_db.sql

Oracle Instance Started.

Total System Global Area 568987532 bytes

Fixed size 345671 bytes

Variable size 340987832 bytes

Database Buffers 87649783 bytes

Redo Buffers 153590 bytes

Statement processed.

SVRMGR> alter database recover database until cancel using backupc controlfile;

SVRMGR> alter database recover cancel;

Statement processed

SVRMGR> alter database open resetlogs;

Statement processed.

SVRMGR> alter database rename global_name to ‘DROY.QUESTAM.COM’;

Statement processed

Shutdown the database and startup. Take a backup of the new database.

SVRMGR> select instance from v$thread;

INSTANCE

DROY

Do the following changes in listener.ora

Add the following entry in tnsnames.ora file

If you get errors during any stages don’t panic.

ORA-07307: "sms1sg: shmat error, unable to attach sga."

Cause: Failed to attach shared memory segment, after having gotten it.

Action: Check errno returned. Verify that SGA attach address is valid.

Problem Explanation: By default, "EPC_DISABLED" is set to false, which causes trace files with a ".dat" extension to be created. When space runs out for these files, your database may hang.

Solution Description: You need to set the environment variable EPC_DISABLED to TRUE. Do the following:

Bourne and Korn Shell:

$ EPC_DISABLED=true;export EPC_DISABLED

C-Shell:

% setenv EPC_DISABLED true

As part of Oracle*Trace, a shadow process will collect diagnostic trace

information. This can be disabled by setting "EPC_DISABLED" to TRUE

in the user environment on Unix.

Now Oracle begins allocating sets of size SEMMSL (or ORANSEMS, as the case may be) until it has at least PROCESSES semaphores.

Solution Description : Increase the SEMMSL parameter value in /etc/system file then reboot the UNIX box or Decrease the processes value in init.ora file.

The init.ora file is not found at ORACLE_HOME/dbs dirctory. If you have else were the create a link file at ORACLE_HOME/dbs directory.

If the file present at /export/home/oracle/admin/DROY/pfile, then create link as

$ln –s /export/home/oracle/admin/DROY/pfile/init

$ORACLE_HOME/dbs/init.ora

[ format: ln –s source destination ]

After startup nomount if the create controlfile "cr_ctrl_db.sql" fails then you may have made a mistake in modifying the controlfile.

Follow the steps:

Issue shutdown abort comand to the database

Remove all control files from all locations

Check your control file datafiles and its locations and make sure everything is correct.

Startup the database in nomount and run the create controlfile, if you still get the same err. Repeat the above procedure till you don’t get this err.

*****