OraFAQ Forum: Backup & Recovery » Need to restore database, have all files

Home » RDBMS Server » Backup & Recovery » Need to restore database, have all files (10gR2 MS Window Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to restore database, have all files [message #561251]

Thu, 19 July 2012 16:29

jxh461
Messages: 185
Registered: March 2005

Senior Member

I need to restore a database. I have the following files:

All Control fiels CONTROL*.ctl
All tablespace files *.dbf
All Redo file REDO*.LOG

I need step by step advice as I am doing such restore for the first time.

Please be gentle guys.

Report message to a moderator

Re: Need to restore database, have all files [message #561253 is a reply to message #561251]

Thu, 19 July 2012 16:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If the files had been copied after clean SHUTDOWN,
then place files into same directory structure & just STARTUP the cloned DB copy.
If the file had been copied while the DB was open & online,
then you have wasted CPU Cycles, disk space & time resulting in useless & corrupted files.

Which is the case for you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Need to restore database, have all files [message #561257 is a reply to message #561253]

Thu, 19 July 2012 16:50

jxh461
Messages: 185
Registered: March 2005

Senior Member

The files were copied after a clean shutdown.
However there was no cloned db created. The network admin (culprit)
was only able to copy said files.

I am thinking to start with installing the 10g software, but not sure where to go from there.

Please advise.

Report message to a moderator

Re: Need to restore database, have all files [message #561259 is a reply to message #561257]

Thu, 19 July 2012 16:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am thinking to start with installing the 10g software, but not sure where to go from there.
yes, & must be exact same version to 4 decimal places.

do you know how to create the required CONTROLFILEs?

Is original DB online & available?

Report message to a moderator

Re: Need to restore database, have all files [message #561260 is a reply to message #561259]

Thu, 19 July 2012 17:10

jxh461
Messages: 185
Registered: March 2005

Senior Member

The database version would be 10.2.0

No. I don't know how to create the required control files.

No. The original database is not available.

So is step #1 to install the software for 10.2.0 ? Is there something I should be careful about ?

I am assuming based on your question that
step#2 is to create the required control files. Am I right ? How can I do that ?

Report message to a moderator

Re: Need to restore database, have all files [message #561261 is a reply to message #561259]

Thu, 19 July 2012 17:16

jxh461
Messages: 185
Registered: March 2005

Senior Member

Need to clarify something.

I see a directory structure of J:\oracle\product\10.2.0\oradata\....

But there are no services running. Only diretory fiels were salvaged.

Report message to a moderator

Re: Need to restore database, have all files [message #561266 is a reply to message #561257]

Thu, 19 July 2012 23:17

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If all files where saved then put back the files in their respective directories.
Recreate the service using ORADIM.
Start the service.
Start the database.

Regards
Michel

Report message to a moderator

Re: Need to restore database, have all files [message #561267 is a reply to message #561253]

Thu, 19 July 2012 23:18

jxh461
Messages: 185
Registered: March 2005

Senior Member

After installing the software. Here is the script I am using to create the control file.

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\APP\JOHN\ORADATA\ORCL\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'C:\APP\JOHN\ORADATA\ORCL\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'C:\APP\JOHN\ORADATA\ORCL\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'C:\APP\JOHN\ORADATA\ORCL\SYSTEM01.DBF',
  'C:\APP\JOHN\ORADATA\ORCL\SYSAUX01.DBF',
  'C:\APP\JOHN\ORADATA\ORCL\UNDOTBS01.DBF',
  'C:\APP\JOHN\ORADATA\ORCL\USERS01.DBF',
  'C:\APP\JOHN\ORADATA\ORCL\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\JOHN\ORADATA\ORCL\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Then I run the script as follows:

SQL> @c:\backup\orcl_ora_2600_recreate.sql
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2176168 bytes
Variable Size            1056967512 bytes
Database Buffers          536870912 bytes
Redo Buffers                7397376 bytes

Control file created.

ORA-00279: change 1680947 generated at 07/19/2012 22:33:07 needed for thread 1
ORA-00289: suggestion :
C:\APP\JOHN\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_07_20\O1_MF_1_34_%U_.ARC
ORA-00280: change 1680947 for thread 1 is in sequence #34


ORA-00308: cannot open archived log 'ALTER'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log 'ALTER'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log 'SIZE'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log
'C:\APP\JOHN\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_07_20\O1_MF_1_34_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL>

The database is not in archivelog mode. When is it trying to open archived log ?

What can I do given that the archive log file is not available ? what are my options ?
Can I bypass this ?

[Updated on: Thu, 19 July 2012 23:22]

Report message to a moderator