Restoring Database on a Different Node Using Cold Backup

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

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.

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]

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.

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

Sure.

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

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.

Quote:

---

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

I guess, you need that too.

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

I Have performed the following steps till now

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..

Now the third step as suggested by gsprince is

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

To startup the database i need to set the oracle_sid and create the services which I did as below

C:\>set oracle_sid=testsid6

C:\>oradim80 -new -sid testsid6 -intpwd oracle1 -startmode auto -pfile C:\testsid6\inittestsid6.ora

C:\>oradim80 -startup -sid testsid6 -starttype srvc,inst -usrpwd oracle1 -pfile C:\testsid6\inittestsid6.ora

Now open the server manager and try to mount the database
using the following commands

SVRMGR> connect internal/oracle1
ORA-12203: TNS:unable to connect to destination
SVRMGR> connect internal/oracle1@testsid6
ORA-12154: TNS:could not resolve service name

Please tell me what am I doing wrong here.
I want to restore the whole database on a production system if this test works. If you need I can also post my init.ora file also.

Thanks
Anurag

I think you need to add one entry for your new sid in your tnsnames.ora file which will resolve your tns problem.

Good luck.

Dragon

I'm not sure if this is the right place to look or not, but it can't hurt to ask.

Is SVRMGR trying to connect to your local database through the standard BEQ-LOCAL service? If that is the case, then what does the BEQ-LOCAL entry in your TNSNAMES.ORA file look like? Is it referencing the SID of the database to which you are trying to connect?

Hi,
I added the entries into the ltnsnames.ora file and the listener file.

Now it gives me the following error.

SVRMGR> connect internal/oracle1@testsid6
ORA-12500: TNS:listener failed to start a dedicated server process

Please suggest.

Thanks
Anurag

check the listner services

***
check "control panel --> services-->", the oracle service you added (say: OracleServiceSID) is started.
***

If not try start the service from control panel.

go to command prompt (DOS prompt)

set ORACLE_SID=SID

C:\> svrmgrl

SVRMGR> connect internal

Note: Asumption is ur database is installed on the same machine where u r trying the connection. Else, you need the TNS stuff like that.

How about passwordfile -
did you also copy over to your new db.
I am doing new instance from cold backup tomorrow with a little bit confuse about password file.

Hi,
I was able to restore the database on a different node using cold backup. Thanks a lot everybody.

The problem was that the services were not getting created using the oradim80 command.

I have 2 oracle homes installed on my machines. One for Oracle 8.1.7 and one for Oracle 8.0.5. i tried creating the services using the oradim command (8.1.7) and it created them and it worked.

BUT WHY DID IT NOT CREATE THE SERVICES USING THE ORADIM80 COMMAND.

ANY IDEAS.....

Thanks
Anurag