Home » RDBMS Server » Backup & Recovery » Online Datafile Media Recovery
Online Datafile Media Recovery [message #213727] Fri, 12 January 2007 01:12 Go to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Hi,

My question is on online datafile media recovery.

My Scenario is as follows:
=========================

Our production DB has two tablespaces that is not used by any of our transactions.So we decided to offline the datafiles of those tablespaces and issued the command.After that i checked the status of these datafiles and it shown "recover" status.Then i tried to brought it back online,but i was asked for media recovery.
So that we are performing the DB online media recovery, and the steps are as follows.
I need your attention and valuable suggestions towards my problem.
Quote:

Database:Oracle9.2.0.8.0 (Production Database)

OS: SunSolaris 10 (T-2000)

Steps To be Followed:

1.Take the Tablespaces containing the damaged datafiles offline

SQL>alter tablespace <name> offline;

2.Restore the datafiles which is needed for media recovery from the previous backup.

Using the OS cp command i can restore it to the appropriate location of the datafile.

But,already the damaged datafile are there in the same location.If i restore the datafile from the backup to the same location,while oracle applying the archives it may throw error message,i suppose.

3.Conn as sysdba

4.recover tablespace <name1,name2> ;

a) I want oracle to automatically apply the needed archive logs on the datafiles.All my archive logs are in the default
location,as mentioned in the init.ora file's log_archive_dest_1 parameter.

(Or) is there any command to perform the auto recovery for the same.

5.Bring back the offlined tablespace online.

SQL>alter tablespace <name> online;


Please throw your experienced ideas on this issue.

Thanks
Re: Online Datafile Media Recovery [message #213774 is a reply to message #213727] Fri, 12 January 2007 04:40 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
My suggestion will be not direct to backup, but I think it will be useful for You. In Your situation is more better to make tablespaces read-only, not to tak them offline.
Re: Online Datafile Media Recovery [message #213790 is a reply to message #213727] Fri, 12 January 2007 05:30 Go to previous messageGo to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Thaks for your reply.

But, Now my situation is to eliminate the the datafile status "RECOVER".To do this i must recover the datafiles and make them online.After this as you said i can change the tablespace mode to "ReadOnly".I think iam correct.

So conform the steps i have included in my previous pose.
Re: Online Datafile Media Recovery [message #213955 is a reply to message #213790] Fri, 12 January 2007 23:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
you can perform recovery from two ways.
1. when you start your database during in mount mode.
SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\SH\USERS01.DBF'
SQL>--copy backup to source destiation.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

In above situation you have no need to offline your datafile. or tablespace.

And After open your database.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\SH\USERS01.DBF'


SQL> alter database datafile 4 offline drop;

Database altered.

SQL> alter database open;

Database altered.



SQL> select status from v$datafile where file# = 4;

STATUS
-------
RECOVER

SQL> alter database recover datafile 4;

Database altered.

SQL> alter database datafile 4 online;

Database altered.

regards
Taj
Re: Online Datafile Media Recovery [message #213971 is a reply to message #213727] Sat, 13 January 2007 03:03 Go to previous messageGo to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Thanks Taj.

Now my production DB is UP.Iam not going to shutdown my database.So i have planned to perform the online datafile media recovery.
So what i have mentioned in my first posing ,the steps that i have to follow is correct,am i right.Anyway i'm going to take those tablespaces offline,since those are not active on my DB transactions.In this case,is there anyway for me to perform auto recovery? so that oracle will automatically apply the archive logs which is needed for media recovery.My archive logs are in the default location.
Re: Online Datafile Media Recovery [message #213999 is a reply to message #213971] Sat, 13 January 2007 08:05 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
SQL> select count(*) from emp;
select count(*) from emp
                     *
ERROR at line 1:
ORA-01115: IO error reading block from file 4 (block # 37)
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\SH\USERS01.DBF'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 38) Reached the end of the file.


SQL> --i recevied above error but file is exists in location may be possible
SQL> --clause is file is corrupted.
SQL> alter database datafile 4 offline;
alter database datafile 4 offline
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn sys@sh as sysdba
Enter password:
Connected.
SQL> alter database datafile 4 offline;

Database altered.

SQL> select status from v$datafile where file# = 4;

STATUS
-------
RECOVER

SQL> --restore datafile from backup

SQL> alter database recover datafile 4;

Database altered.

SQL> alter database datafile 4 online;

Database altered.

SQL> conn scott/tiger@sh
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL>

regards
Taj
Previous Topic: How to config Tape backup ?
Next Topic: Duplicate database to remote host using rman
Goto Forum:
  


Current Time: Thu Dec 08 00:09:53 CST 2016

Total time taken to generate the page: 0.05456 seconds