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.
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