Friday 23 September 2016

Data Recovery Advisor 11g?

for example we shutdown the database and remove the datafile.Then when we startup the database the database will not open and we will get an error.
before removing the datafile take the backup of the datafile.I am deleting the undo datafile.
when a non critical datafile is missing we had two options
1)we can do recovery immediately.
2)we can take that datafile offline and open the rest of the database.

Here we are not opening the database just we are just recovering the datafile.

[oracle@subbu1 oradata]$ cp undotbs1.dbf undotbs1.dbf.bkp          (backup the undo datafile)

[oracle@subbu1 oradata]$ ls
arch           redolog1b.log  redolog2b.log  subbu1       temp1.dbf     undotbs1.dbf.bkp
redolog1a.log  redolog2a.log  rmanbkp        sysaux1.dbf  undotbs1.dbf

[oracle@subbu1 oradata]$ rm undotbs1.dbf              (here deleting the undo datafile)

[oracle@subbu1 oradata]$ ls
arch  redolog1a.log  redolog1b.log  redolog2a.log  redolog2b.log  rmanbkp  subbu1  sysaux1.dbf  temp1.dbf  undotbs1.dbf.bkp

[oracle@subbu1 oradata]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 23 17:28:29 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oradata/undotbs1.dbf'


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@subbu1 oradata]$ cd

[oracle@subbu1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 23 17:29:12 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SUBBU1 (DBID=880804144, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
242        HIGH     OPEN      23-SEP-16     One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
242        HIGH     OPEN      23-SEP-16     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=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=17 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oradata/undotbs1.dbf was unintentionally renamed or moved, restore it

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


In another terminal go to the above repair script location

file 3;  and recover datafile
   restore datafile 3;
   recover datafile 3;


RMAN> restore datafile 3;

Starting restore at 23-SEP-16
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oradata/undotbs1.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oradata/rmanbkp/08rgf9ej.dbf%
channel ORA_DISK_1: piece handle=/u01/app/oradata/rmanbkp/08rgf9ej.dbf% tag=TAG20160922T174802
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 23-SEP-16

RMAN> recover datafile 3;

Starting recover at 23-SEP-16
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 41 is already on disk as file /u01/app/oradata/arch/1_41_922985200.dbf
archived log for thread 1 with sequence 42 is already on disk as file /u01/app/oradata/arch/1_42_922985200.dbf
archived log file name=/u01/app/oradata/arch/1_41_922985200.dbf thread=1 sequence=41
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-SEP-16

RMAN> exit;


Recovery Manager complete.

[oracle@subbu1 ~]$ cd /u01/app/oradata
[oracle@subbu1 oradata]$ ls
arch           redolog1b.log  redolog2b.log  subbu1       temp1.dbf     undotbs1.dbf.bkp
redolog1a.log  redolog2a.log  rmanbkp        sysaux1.dbf  undotbs1.dbf

we sucessfully recovered the datafile.

No comments:

Post a Comment