参数文件控制文件和数据文件丢失的恢复

在做这个实验之前要保证数据库存在有效的备份。

报错信息：
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

 

由提示信息可知参数文件丢失，数据库无法nomount。
于是启动rman在没有参数文件的情况下将数据库打开到nomount，然后使用autobackup恢复参数文件，如果原来的磁盘已经损坏，
那么我们就要将参数文件恢复到其他目录：
[oracle@localhost ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 25 18:33:24 2014

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

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

RMAN> set dbid=1383986269

executing command: SET DBID

RMAN> restore spfile to '/u01/app/spfile/spfileorcl.ora' from autobackup;

Starting restore at 25-AUG-14
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140825
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140824
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140823
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140822
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140821
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140820
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140819
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/25/2014 18:41:12
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
出现这种错误是由于rman找不到自动备份，解决方法是指定路径让rman去找。

RMAN> restore spfile from '/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_08_25
2> /o1_mf_s_856548550_9zqqkqy0_.bkp';

Starting restore at 25-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_08_25/o1_mf_s_856548550_9zqqkqy0_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-AUG-14

启动数据库到mount:

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

查看警报日志文件：
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

控制文件全部丢失，于是使用rman恢复控制文件：
RMAN> restore controlfile to '/u01/app/controlfile/control01.ctl' from 
2> '/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_08_25
3> //o1_mf_s_856548550_9zqqkqy0_.bkp';

Starting restore at 25-AUG-14
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-AUG-14

修改参数control_files:
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

[oracle@localhost dbs]$ vi initorcl.ora

*.control_files='/u01/app/controlfile/control01.ctl'

SQL> create spfile from pfile;

File created.

打开数据库到mount：
SQL> startup mount;
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             553651792 bytes
Database Buffers          289406976 bytes
Redo Buffers                5132288 bytes
Database mounted.

打开数据库到open：
SQL> alter database open; 
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


查看警报日志文件：
alter database open
Mon Aug 25 22:52:19 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_11521.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/hh01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11611.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...

数据文件hh01.dbf已经丢失，使用rman进行恢复：
[oracle@localhost app]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 25 22:54:29 2014

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

connected to target database: ORCL (DBID=1383986269, not open)
using target database control file instead of recovery catalog

RMAN> restore database;

Starting restore at 25-AUG-14
using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/app/oracle/oradata/orcl/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/orcl/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/orcl/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/orcl/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl/example01.dbf
skipping datafile 6; already restored to file /u01/app/datafile/test01.dbf
channel ORA_DISK_1: restoring datafile 00007
input datafile copy RECID=15 STAMP=856565947 file name=/u01/app/datafile/hh01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orcl/hh01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00007
output file name=/u01/app/oracle/oradata/orcl/hh01.dbf RECID=0 STAMP=0
Finished restore at 25-AUG-14

RMAN> recover database;

Starting recover at 25-AUG-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/archivelog/dest1/1_1_856310126.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/archivelog/dest1/1_2_856310126.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/orcl/redo02.log
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/orcl/redo03.log
archived log file name=/u01/app/archivelog/dest1/1_1_856310126.dbf thread=1 sequence=1
archived log file name=/u01/app/archivelog/dest1/1_2_856310126.dbf thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1
archived log file name=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/orcl/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-AUG-14

再使用alter database open resetlogs;即可打开数据库。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29800581/viewspace-1258004/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29800581/viewspace-1258004/