Home » RDBMS Server » Backup & Recovery » Restore and recover of corrupted datafiles
Restore and recover of corrupted datafiles [message #199292] Mon, 23 October 2006 04:39 Go to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi all,

I took the backup using the copy command running at RMAN> as follows:

RMAN> copy nochecksum datafile 'D:/Oracle/oradata/dbdata/tools01.dbf' to 'C:\too
ls01.bak' level 0;

Succesfully copied the file to the destination.


I am performing individual data files recovery.
Using the following:

RMAN> SQL 'ALTER DATABASE DATAFILE tools01.dbf OFFLINE';

sql statement: ALTER DATABASE DATAFILE tools01.dbf OFFLINE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 10/23/2006 14:33:33
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DATA
FILE tools01.dbf OFFLINE
ORA-02236: invalid file name

Got the above error.

Can anybody suggest me how can I perform the individual file recovery?


Thanks&Regards,
Kamal
Re: Restore and recover of corrupted datafiles [message #199300 is a reply to message #199292] Mon, 23 October 2006 05:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
SQL 'ALTER DATABASE DATAFILE tools01.dbf OFFLINE';

You need to provide a full path for the datafile
as you did here
Quote:
RMAN> copy nochecksum datafile 'D:/Oracle/oradata/dbdata/tools01.dbf' to 'C:\too
ls01.bak' level 0;
Re: Restore and recover of corrupted datafiles [message #199306 is a reply to message #199300] Mon, 23 October 2006 06:06 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

Thanx for your valid suggestions.

I have executed the following command:

RMAN> sql 'alter database datafile 'D:\Oracle\oradata\dbdata\tools01.dbf' offlin
e';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: ";"
RMAN-01008: the bad identifier was: D
RMAN-01007: at line 1 column 31 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signalled during parse
RMAN-02001: unrecognized punctuation symbol ":"


And also tried the following:

RMAN> sql 'alter database datafile D:\Oracle\oradata\dbdata\tools01.dbf offline'
;

sql statement: alter database datafile D:\Oracle\oradata\dbdata\tools01.dbf offl
ine
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 10/23/2006 16:32:05
RMAN-11003: failure during parse/execution of SQL statement: alter database data
file D:\Oracle\oradata\dbdata\tools01.dbf offline
ORA-02236: invalid file name


In both the cases it is failing.

Can you please suggest me how can I execute this?

Regargs,
Kamal
Re: Restore and recover of corrupted datafiles [message #199307 is a reply to message #199306] Mon, 23 October 2006 06:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said in documentation.
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    300      SYSTEM               ***     /u02/oradata/primary/system01.dbf
2    200      UNDOTBS1             ***     /u02/oradata/primary/undotbs01.dbf
3    150      SYSAUX               ***     /u02/oradata/primary/sysaux01.dbf
4    31       USERS                ***     /u02/oradata/primary/users01.dbf
5    10       TBS_EMP1             ***     /u02/oradata/primary/data_emp1
6    10       TBS_EMP2             ***     /u02/oradata/primary/data_emp2
7    10       TBS_EMP3             ***     /u02/oradata/primary/data_emp3
8    1024     TOOLS                ***     /u02/oradata/primary/tools.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/primary/temp01.dbf

RMAN>  sql "alter database datafile ''/u02/oradata/primary/data_emp3'' offline";

using target database control file instead of recovery catalog
sql statement: alter database datafile ''/u02/oradata/primary/data_emp3'' offline
Re: Restore and recover of corrupted datafiles [message #199310 is a reply to message #199307] Mon, 23 October 2006 06:39 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

Thanx for your quick reply.

I have executed the commands as you suggested.

The following is the result:

RMAN> sql "alter database datafile ''D:\ORACLE\ORADATA\DBDATA\TOOLS01.DBF'' offl
ine";

sql statement: alter database datafile ''D:\ORACLE\ORADATA\DBDATA\TOOLS01.DBF''
offline

RMAN> restore datafile 'C:\TOOLS01.BAK';

Starting restore at 23-OCT-06

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/23/2006 17:02:32
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: C:\TOOLS01.BAK



I have copied as a .BAK file. So please suggest me how can I restore this file?

Regards,
Kamalakar.

Re: Restore and recover of corrupted datafiles [message #199311 is a reply to message #199292] Mon, 23 October 2006 06:56 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

I have taken the backup using copy command.

Where the database is in archivelog mode and noncatalog mode.

RMAN> restore datafile 'C:\TOOLS01.DBF';

Starting restore at 23-OCT-06

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/23/2006 17:20:04
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: C:\TOOLS01.DBF

this is the latest error message I am getting.

Please suggest me.

Regards,
Kamalakar.
Re: Restore and recover of corrupted datafiles [message #199330 is a reply to message #199311] Mon, 23 October 2006 10:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Your syntax is wrong.
As is said before, everything is clearly accounted in Oracle RMAN documentation. Please read the documentation~.
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    300      SYSTEM               ***     /u02/oradata/primary/system01.dbf
2    200      UNDOTBS1             ***     /u02/oradata/primary/undotbs01.dbf
3    150      SYSAUX               ***     /u02/oradata/primary/sysaux01.dbf
4    31       USERS                ***     /u02/oradata/primary/users01.dbf
5    10       TBS_EMP1             ***     /u02/oradata/primary/data_emp1
6    10       TBS_EMP2             ***     /u02/oradata/primary/data_emp2
7    10       TBS_EMP3             ***     /u02/oradata/primary/data_emp3
8    1024     TOOLS                ***     /u02/oradata/primary/tools.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/primary/temp01.dbf

RMAN> copy nochecksum datafile '/u02/oradata/primary/tools.dbf' to '/tmp/tools.dbf';

Starting backup at 23-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=140 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/u02/oradata/primary/tools.dbf
output filename=/tmp/tools.dbf tag=TAG20061023T041701 recid=38 stamp=604556439
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:45
Finished backup at 23-OCT-06

Starting Control File and SPFILE Autobackup at 23-OCT-06
piece handle=/backup/primary/snapcf/c-1438506724-20061023-01 comment=NONE
Finished Control File and SPFILE Autobackup at 23-OCT-06


RMAN> list copy;


List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
38      8    A 23-OCT-06       1654033    23-OCT-06       /tmp/tools.dbf

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
70      1    49      A 13-OCT-06 /archive/1_49_601024036.dbf



RMAN> shutdown abort;

Oracle instance shut down

RMAN> host ;

oracle@dubbel:/backup> rm /u02/oradata/primary/tools.dbf
oracle@dubbel:/backup> exit
exit
host command complete

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 10/23/2006 04:24:45
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u02/oradata/primary/tools.dbf'

RMAN>  shutdown abort

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     289406976 bytes

Fixed Size                     1219016 bytes
Variable Size                 88081976 bytes
Database Buffers             197132288 bytes
Redo Buffers                   2973696 bytes

RMAN> run {
2> set newname for datafile '/u02/oradata/primary/tools.dbf' to '/tmp/tools.dbf';
3> }

executing command: SET NEWNAME
RMAN> restore datafile '/u02/oradata/primary/tools.dbf';

Starting restore at 23-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK

channel ORA_DISK_1: restoring datafile 00008
input datafile copy recid=38 stamp=604556439 filename=/tmp/tools.dbf
destination for restore of datafile 00008: /u02/oradata/primary/tools.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00008
output filename=/u02/oradata/primary/tools.dbf recid=39 stamp=604556993
Finished restore at 23-OCT-06

RMAN> recover database;

Starting recover at 23-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 23-OCT-06

RMAN> shutdown abort

Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area     289406976 bytes

Fixed Size                     1219016 bytes
Variable Size                 88081976 bytes
Database Buffers             197132288 bytes
Redo Buffers                   2973696 bytes
 

[Updated on: Mon, 23 October 2006 12:46]

Report message to a moderator

Re: Restore and recover of corrupted datafiles [message #199403 is a reply to message #199330] Tue, 24 October 2006 01:24 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

Thank you so much for your help. It is working fine.

Regards,
Kamal.

Re: Restore and recover of corrupted datafiles [message #199431 is a reply to message #199292] Tue, 24 October 2006 04:55 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

I have few queries.

Q 1). Is there any Instances in Oracle similar to SQL Server named Instances?

Q 2). How can I get all the Oracle Instance and Database names?

Please suggest me.

Regards,
Kamalakar.
Re: Restore and recover of corrupted datafiles [message #199434 is a reply to message #199431] Tue, 24 October 2006 05:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Q 1). Is there any Instances in Oracle similar to SQL Server named Instances?
cannot comment. I have no idea about SQL Server.
>> Q 2). How can I get all the Oracle Instance and Database names?
In a single machine?
http://www.orafaq.com/forum/m/198715/42800/#msg_198715

Please search the forum before posting. Most of the question are already answered.

[Updated on: Tue, 24 October 2006 05:02]

Report message to a moderator

Re: Restore and recover of corrupted datafiles [message #199846 is a reply to message #199292] Thu, 26 October 2006 07:50 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

Can I perform full database recovery by using individual datafiles backup?

If so, What are the necessary files that I need to take the backup?

Please help me.

Regards,
Kamalakar.
Re: Restore and recover of corrupted datafiles [message #199881 is a reply to message #199846] Thu, 26 October 2006 09:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Can I perform full database recovery by using individual datafiles backup?
Please rephrase.
Not sure what you are talking about.
I am interpreting it as
case: backup individual files. do a full database restore (no Point-in-time).
Yes. Posssible. Nothing special. as usual backup control file,datafiles and archivedlogs.
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm
Re: Restore and recover of corrupted datafiles [message #202083 is a reply to message #199881] Wed, 08 November 2006 02:55 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

Can I perform tablespace point-in-time recovery (TSPITR) with Oracle 9i databases?

I have read that we can perform in Oracle 10g. Please help me.


Regards,
Kamalakar.
Re: Restore and recover of corrupted datafiles [message #202084 is a reply to message #202083] Wed, 08 November 2006 02:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Can I perform tablespace point-in-time recovery (TSPITR) with Oracle 9i databases?
Yes.
Re: Restore and recover of corrupted datafiles [message #202090 is a reply to message #199292] Wed, 08 November 2006 03:12 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Mahesh,

Can you please send me the steps to perform tablespace point-in-time recovery (TSPITR) with Oracle 9i databases?

I have executed the following commands but couldn't failed to perform TSPITR.

RMAN> Run
2> {
3> Restore tablespace USERS;
4> Restore tablespace TOOLS;
5> }

Starting restore at 08-NOV-06

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00009 to C:\ORACLE\ORADATA\STREAM\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\TEMPDEST\STD_0EI1SON6_1_1 tag=TAG20061108T131934 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 08-NOV-06

Starting restore at 08-NOV-06

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to C:\ORACLE\ORADATA\STREAM\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\TEMPDEST\STD_0DI1SON2_1_1 tag=TAG20061108T131930 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 08-NOV-06

RMAN> RECOVER TABLESPACE users,tools UNTIL LOGSEQ 2 THREAD 1 AUXILIARY DESTINATI
ON 'E:\PITR';

Please help me.

Regards,
Kamalakar.



Re: Restore and recover of corrupted datafiles [message #202094 is a reply to message #202090] Wed, 08 November 2006 03:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Refer the other thread.
Re: Restore and recover of corrupted datafiles [message #202095 is a reply to message #202094] Wed, 08 November 2006 03:18 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And i am locking this thread.
Previous Topic: Fully automated TSPITR with RMAN
Next Topic: Review Cold backup script
Goto Forum:
  


Current Time: Sun Dec 04 08:58:18 CST 2016

Total time taken to generate the page: 0.05533 seconds