Yasin Yazıcı

Recover Datafile with Rman

Herhangi bir şekilde silinmiş bir datafile rman ile nasıl recover edileceğinden bahsedeceğim.Sistemimizde varolan datafile bakabiliriz.

SQL>select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/databs/system01.dbf SYSTEM
/u01/app/oracle/oradata/databs/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/databs/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/databs/users01.dbf ONLINE

1)İlk olarak test için bir tablespace ederiz.

SQL>create tablespace rectbs datafile '/u01/app/oracle/oradata/databs/recdata.dbf' size 50M;
Tablespace created.

SQL>select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/databs/system01.dbf SYSTEM
/u01/app/oracle/oradata/databs/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/databs/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/databs/users01.dbf ONLINE
/u01/app/oracle/oradata/databs/recdata.dbf ONLINE

2)Test için oluşturulan tablespace içerisinde bir table oluştururuz ve table içerisine data insert ederiz..

SQL>create table stutinfo(id number, name varchar(20)) tablespace rectbs;
Table created.

SQL>select table_name,tablespace_name from dba_tables where tablespace_name='RECTBS';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STUTINFO RECTBS

SQL>insert into stutinfo values(3,'emre');
1 row created.

SQL>insert into stutinfo values(5,'mehmet');
1 row created.

SQL>commit;
Commit complete.

SQL>select * from stutinfo;
ID NAME
---------- ---------------------------------------------
3 emre
5 mehmet

3) Table içerisine data insert ettikten sonra recdata.dbf dosyasını file system den sileriz

$rm -rf /u01/app/oracle/oradata/databs/recdata.dbf

SQL>select * from stutinfo;

ID NAME
---------- ---------------------------------------------
3 emre
5 mehmet

4)Datafile sildikten sonra içerisinde bulunan data erişebiliyoruz.Bunun nedeni bu datalaraın hala cache olmasından dolayıdır. Cache içerisindeki datayı sileriz.

SQL>alter system flush buffer_cache;
System altered.

Datafile içerisindeki dataya ulaşmaya çalıştığımızda aşağıdaki gibi hata verecektir.

SQL>select * from stutinfo;
select * from stutinfo
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/databs/recdata.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

5)Rman ile hangi datafile problem olduğunu öğrenebiliriz.

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
28 HIGH OPEN 17-JAN-13 One or more non-system datafiles are missing

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
28 HIGH OPEN 17-JAN-13 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/databs/recdata.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/databs/databs/hm/reco_821205902.hm

6) Şimdi zarar görmüş datafile kurtarmaya çalışacağız. İlk önce datafile offline moda almalıyız.Değişikliği yaptıktan sonra datafile status recover olarak görülecektir.

SQL> alter database datafile 5 offline;

Database altered.

SQL> column name format a45

SQL> select name,status from v$datafile;

NAME STATUS

--------------------------------------------- -------

/u01/app/oracle/oradata/databs/system01.dbf SYSTEM

/u01/app/oracle/oradata/databs/sysaux01.dbf ONLINE

/u01/app/oracle/oradata/databs/undotbs01.dbf ONLINE

/u01/app/oracle/oradata/databs/users01.dbf ONLINE

/u01/app/oracle/oradata/databs/recdata.dbf RECOVER

SQL> !rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 17 15:25:18 2013

connected to target database: DATABS (DBID=1522812961)

RMAN> restore datafile 5;

Starting restore at 17-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/databs/recdata.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 17-JAN-13

RMAN> recover datafile 5;

Starting recover at 17-JAN-13

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 17-JAN-13

RMAN> exit

7)Recover işlemi bittikten sonra datafile offline modda olacaktır.Online konumuna almalıyız.

SQL> select name,status from v$datafile;

NAME STATUS

--------------------------------------------- -------

/u01/app/oracle/oradata/databs/system01.dbf SYSTEM

/u01/app/oracle/oradata/databs/sysaux01.dbf ONLINE

/u01/app/oracle/oradata/databs/undotbs01.dbf ONLINE

/u01/app/oracle/oradata/databs/users01.dbf ONLINE

/u01/app/oracle/oradata/databs/recdata.dbf OFFLINE

SQL> alter database datafile 5 online;

Database altered.

SQL> select name,status from v$datafile;

NAME STATUS

--------------------------------------------- -------

/u01/app/oracle/oradata/databs/system01.dbf SYSTEM

/u01/app/oracle/oradata/databs/sysaux01.dbf ONLINE

/u01/app/oracle/oradata/databs/undotbs01.dbf ONLINE

/u01/app/oracle/oradata/databs/users01.dbf ONLINE

/u01/app/oracle/oradata/databs/recdata.dbf ONLINE

8)Datafile online konumuna aldıktan sonra data ya erişip erişemediğimiz kontrol ederiz.

SQL> select * from stutinfo;

ID NAME

---------- ---------------------------------------------

3 emre

5 mehmet

Yasin Yazıcı

Sayfalar

18 Ocak 2013 Cuma

Hiç yorum yok: