Restoring Database on a Different Node Using Cold Backup

[anuragmin]

Hi,
How do I restore a full database on a different node using cold backup.

I have seen one thread here which says the following....

By SReddy

Copying all the files(data/control/redo/init.ora) that constitute your 100GB database to the server you wanted and modify dbname/controlfile/archive destination parameters in init.ora and set environment variables..and start the database...in mount mode and see the locations of your files from V$control/data/logfiles and rename the file locations with new file locations. that should take care off...

My question is how do I start the database in Mount State and alter the datafile locations because I dont have the services set up on this node.

Can somebody put some more light on the setting up of the environment variable thing or point me to a thread/article which gives a step by step instructions on restoring a full cold backup on a different node.

Thanks
Anurag

[Halo]

1. backup controlfile to trace on the primary db (edit this for file locations)
2. create db on the other server
3. restore from cold backup
4. edit init.ora parameters
5. use trace backup of controlfile to create controlfile.
6. open db.

[anuragmin]

I have the O/S Level Back up of the Following

Control Files
Init.Ora File
DataFiles
Archived Redo Log Files
Online Redo Log Files

I did not give a Alter database Backup Control File to Trace command.

Now I need to restore this cold backup on a seperate node.


Thanks
Anurag

[Edited by anuragmin on 04-30-2001 at 11:55 AM]

[Halo]

The dbf path names in the controlfiles will not be the same when restored on the other machine. You will need to recreate the controlfile esp. if the path names of the dbf are different on the target db if diff. from the source db.

[anuragmin]

Can I backup Control File to Trace and use it now.
I am sure that the database strcture has not changed since yesterday.

Thanks
Anurag

[Halo]

Sure.

[anuragmin]

Hi,
I did a backup control file and edited the control file so that it looks as below


STARTUP NOMOUNT pfile=c:\testsid6\inittestsid6.ora
CREATE CONTROLFILE SET DATABASE "TESTSID6" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 3 (
'C:\TestSid6\LOG6G3M0.ORA',
'C:\TestSid6\LOG6G3M1.ORA'
) SIZE 5M,
GROUP 4 (
'C:\TestSid6\LOG6G4M0.ORA',
'C:\TestSid6\LOG6G4M1.ORA'
) SIZE 5M
DATAFILE
'C:\TestSid6\SYS1SID6.ORA',
'C:\TestSid6\RBS1SID6.ORA',
'C:\TestSid6\USR1SID6.ORA',
'C:\TestSid6\TMP1SID6.ORA',
'C:\TestSid6\INDX1SID6.ORA',
'C:\TestSid6\TLS1SID6.ORA',
'C:\TestSid6\MD1SID6.ORA',
'C:\TestSid6\MD2SID6.ORA',
'C:\TestSid6\RBS2SID6.ORA',
'C:\TestSid6\RBS3SID6.ORA',
'C:\TestSid6\TMP2SID6.ORA',
'C:\TestSid6\SYS2SID6.ORA',
'C:\TestSid6\INDX2SID6.ORA'
;
# Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('C:\TestSid6\SNCFTestSID6.ORA');
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN resetlogs;

This is in a file called createctrl.sql.

Then I created a batch file as followed

set ORACLE_SID=testsid6
oradim80 -new -sid testsid6 -intpwd oracle1 -startmode auto -pfile C:\testsid6\inittestsid6.ora
oradim80 -startup -sid testsid6 -starttype srvc,inst -usrpwd testsid6 -pfile C:\testsid6\inittestsid6.ora
svrmgr30 @createctrl.sql

Now when I run this batch file I get the following error.These get generated when the createctrl.sql is executed. Please Help.


ORA-12203: TNS:unable to connect to destination
LCC-00161: Message 161 not found; product=RDBMS80; facility=MGR

ORA-12203: TNS:unable to connect to destination
CREATE CONTROLFILE SET DATABASE "TESTSID6" RESETLOGS ARCHIVELOG
*
ORA-03114: not connected to ORACLE
BEGIN
*
ORA-03114: not connected to ORACLE
ALTER SYSTEM ARCHIVE LOG ALL
*
ORA-03114: not connected to ORACLE
ALTER DATABASE OPEN resetlogs
*
ORA-03114: not connected to ORACLE

Server Manager complete.

Thanks
Anurag

[gsprince]

If you have a cold backup of the database and you want to set up the DB in another DB, the best way is (as Reddy suggested)

1, copy all the files to the new Node

2, change the initSID.ora file to reflect the path of the new controlfile, back_dump, core_dump,..etc, etc..
(if you have the same path names on the target machine you dont have to do this at all)

3, startup mount ; /* make sure ORACLE_SID, ORACLE_HOME, PATH everything is set */

if you restore the files to a different directory do the following.

ALTER DATABASE RENAME FILE 'src_file' TO 'tgt_file';

You have to rename the datafiles, redofiles whatever is restored to a different path from the original machine.

note: step 2 and 3 is not needed, if you have the same file structure in the target machine and you are restoring to the same place as in the aource machine.

Start the DB. This will bring the DB to the same state when the cold backup was taken.

[anuragmin]

startup mount ; /* make sure ORACLE_SID, ORACLE_HOME, PATH everything is set */

Will just creating Oracle_Sid suffice. Dont we need to create the services also using ORAdim .

Thanks
Anurag

[gsprince]

I guess, you need that too.

Again, I am not too familiar with Oracle on Windows.