有业务人员反馈连接数据库报错,Oracle ORA-01033: ORACLE initialization or shutdown in progress。发现数据库异常,尝试重启数据库,启动时报 ORA-01113: file 1 needs media recovery,有数据文件需要恢复,然后尝试恢复数据文件。以下是此次恢复的操作记录。
#启动数据库报错,只能进入mount阶段。
SQL> startup
ORACLE instance started.
Total System Global Area 1.0055E+10 bytes
Fixed Size 2261888 bytes
Variable Size 2382367872 bytes
Database Buffers 7650410496 bytes
Redo Buffers 19742720 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/dtberp/system01.dbf'
#再次尝试打开数据库,还是失败
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
#尝试使用noresetlogs和resetlogs启动数据库
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/app/oracle/oracle/oradata/uatcrm/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 602446 generated at 10/12/2011 09:38:56 needed for thread 1
ORA-00289: suggestion :
/app/oracle/oracle/fast_recovery_area/UATCRM/archivelog/2021_08_17/o1_mf_1_1722_.arc
ORA-00280: change 602446 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/app/oracle/oracle/fast_recovery_area/UATCRM/archivelog/2021_08_17/o1_mf_1_1722_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
#决定使用redo日志恢复数据库
[oracle@kvm-10-115-107-20 uatcrm]$ ls -lrt
total 40224300
-rw-r----- 1 oracle oinstall 441458688 Aug 6 10:37 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 12 10:10 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 12 14:57 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 12 20:29 redo03.log
-rw-r----- 1 oracle oinstall 1478500352 Aug 18 06:48 system01.dbf
-rw-r----- 1 oracle oinstall 2757763072 Aug 18 06:48 sysaux01.dbf
-rw-r----- 1 oracle oinstall 25559048192 Aug 18 06:48 users01.dbf
-rw-r----- 1 oracle oinstall 304095232 Aug 18 06:48 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10485768192 Aug 18 06:48 uatibssDATA01.dbf
-rw-r----- 1 oracle oinstall 9748480 Aug 18 06:53 control01.ctl
[oracle@kvm-10-115-107-20 uatcrm]$ pwd
/app/oracle/oracle/oradata/uatcrm
[oracle@kvm-10-115-107-20 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 18 06:44:53 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> show parameter pf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/oracle/product/11.
2.4/dbs/spfileuatcrm.ora
SQL> show parameter background
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /app/oracle/oracle/diag/rdbms/
uatcrm/uatcrm/trace
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 77021067 generated at 08/12/2021 14:57:22 needed for thread 1
ORA-00289: suggestion :
/app/oracle/oracle/fast_recovery_area/UATCRM/archivelog/2021_08_18/o1_mf_1_1722_
%u_.arc
ORA-00280: change 77021067 for thread 1 is in sequence #1722
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oracle/oradata/uatcrm/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open RESETLOGS;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE