意外断电导致当前日志文件损坏
服务器意外断电，恢复供电后启动数据库时，当前日志文件损坏。不幸的是，这个是开发使用的数据库是从不备份的。
这次运气比较好，用隐藏参数打开了数据库。数据库不大，做了个冷备。
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
ORA-00742: Log read detects lost write in thread 1 sequence 20 block 57401
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo04.rdo'
[root@tstdemo02 ~]# su - oracle
Last login: Fri Oct 16 09:04:58 EDT 2020 on pts/0
[oracle@tstdemo02 ~]$ export ORACLE_SID=SCPRDI1
[oracle@tstdemo02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 16 09:06:12 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select open_mode form v$database;
select open_mode form v$database
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/admin/SCPRDI1/arch/arch.log
Oldest online log sequence 13
Current log sequence 20
SQL> set line 200
SQL> select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------ ----------
1 1 17 1073741824 512 1 NO INACTIVE 6140750 10-OCT-20 6357971 11-OCT-20 0
2 1 18 1073741824 512 1 NO INACTIVE 6357971 11-OCT-20 6867925 14-OCT-20 0
3 1 19 1073741824 512 1 NO INACTIVE 6867925 14-OCT-20 7192830 16-OCT-20 0
8 1 16 1073741824 512 1 NO INACTIVE 6005854 09-OCT-20 6140750 10-OCT-20 0
5 1 13 1073741824 512 1 NO INACTIVE 4784933 23-JUN-20 5562286 25-JUN-20 0
6 1 14 1073741824 512 1 NO INACTIVE 5562286 25-JUN-20 5904958 07-JUL-20 0
7 1 15 1073741824 512 1 NO INACTIVE 5904958 07-JUL-20 6005854 09-OCT-20 0
4 1 20 1073741824 512 1 NO CURRENT 7192830 16-OCT-20 1.8447E+19 0
8 rows selected.
SQL> set line 233;
SQL> col member for a60;
SQL> SELECT a.group#,a.MEMBER,b.thread#,b.status,b.bytes / 1024 / 1024 MB,b.ARCHIVED,b.sequence# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY a.group#;
GROUP# MEMBER THREAD# STATUS MB ARC SEQUENCE#
---------- ------------------------------------------------------------ ---------- ---------------- ---------- --- ----------
1 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo01.rdo 1 INACTIVE 1024 NO 17
2 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo02.rdo 1 INACTIVE 1024 NO 18
3 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo03.rdo 1 INACTIVE 1024 NO 19
4 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo04.rdo 1 CURRENT 1024 NO 20
5 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo05.rdo 1 INACTIVE 1024 NO 13
6 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo06.rdo 1 INACTIVE 1024 NO 14
7 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo07.rdo 1 INACTIVE 1024 NO 15
8 /u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo08.rdo 1 INACTIVE 1024 NO 16
8 rows selected.
SQL> shu immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
ORA-00742: Log read detects lost write in thread 1 sequence 20 block 57401
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/SCPRDI1/SCPRDI1_redo04.rdo'
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
7192831
7192831
7192831
7192831
7192831
7192831
6 rows selected.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
7192831
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
7192831
7192831
7192831
7192831
7192831
7192831
6 rows selected.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7192831 generated at 10/16/2020 01:55:03 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/SCPRDI1/arch/arch.log1_20_1042166589.dbf
ORA-00280: change 7192831 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/SCPRDI1/SCPRDI1_system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/SCPRDI1/SCPRDI1_system01.dbf'
SQL> recover database using backup controlfile;
ORA-00279: change 7192831 generated at 10/16/2020 01:55:03 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/SCPRDI1/arch/arch.log1_20_1042166589.dbf
ORA-00280: change 7192831 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/u01/app/admin/SCPRDI1/arch/arch.log1_20_1042166589.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
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: '/u01/app/oracle/oradata/SCPRDI1/SCPRDI1_system01.dbf'
SQL> startup force mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS 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: '/u01/app/oracle/oradata/SCPRDI1/SCPRDI1_system01.dbf'
SQL> create spfile from pfile;
File created.
SQL> startup force mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Process ID: 26360
Session ID: 2 Serial number: 28121
由于隐藏参数导致的600错误。
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@tstdemo02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 16 10:26:53 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile;
System altered.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 301991224 bytes
Database Buffers 759169024 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
