1. 介绍
重做日志文件记录了数据库的变更数据。
一般重做日志文件的失败不会使数据库数据丢失，但是会影响数据库的恢复。
重做日志分为两种状态当前联机重做日志、非当前的联机重做日志
rman备份不会备份日志文件，所以这里的使用是没有用到rman恢复的
1.1. 非当前 redo(联机重做日志)文件丢失恢复
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 NO CURRENT 2296 354 2023-04-01 11:54:27 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> /
System altered.
HfeduSQL> /
/
System altered.
HfeduSQL>
System altered.
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 4 209715200 512 1 YES INACTIVE 2300 204 2023-04-01 12:44:31 2300207 2023-04-01 12:44:31 0
2 1 5 209715200 512 1 YES INACTIVE 2300 207 2023-04-01 12:44:31 2300211 2023-04-01 12:44:35 0
3 1 6 209715200 512 1 NO CURRENT 2300 211 2023-04-01 12:44:35 1.8447E+19 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 7 209715200 512 1 NO CURRENT 2300 216 2023-04-01 12:44:45 1.8447E+19 0
2 1 5 209715200 512 1 YES INACTIVE 2300 207 2023-04-01 12:44:31 2300211 2023-04-01 12:44:35 0
3 1 6 209715200 512 1 YES ACTIVE 2300 211 2023-04-01 12:44:35 2300216 2023-04-01 12:44:45 0
HfeduSQL> select * from v$log
2 ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 7 209715200 512 1 NO CURRENT 2300 216 2023-04-01 12:44:45 1.8447E+19 0
2 1 5 209715200 512 1 YES INACTIVE 2300 207 2023-04-01 12:44:31 2300211 2023-04-01 12:44:35 0
3 1 6 209715200 512 1 YES INACTIVE 2300 211 2023-04-01 12:44:35 2300216 2023-04-01 12:44:45 0
HfeduSQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- ------
IS_ CON_ID
--- ----------
1 ONLINE
/oradata/hfzcdb/redo01.log
NO 0
3 ONLINE
/oradata/hfzcdb/redo03.log
NO 0
2 ONLINE
/oradata/hfzcdb/redo02.log
NO 0
HfeduSQL> !rm /oradata/hfzcdb/redo03.log
HfeduSQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 99985
Session ID: 9 Serial number: 35808
HfeduSQL> select * from v$log;
ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE
HfeduSQL> startup mount
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sql[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplu / as sysdba
bash: sqlplu: command not found...
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 12:48:08 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> startup mount
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
HfeduSQL>
HfeduSQL>
HfeduSQL> alter database clear logfile group 2; 【这里删错了，最好去看下日志，到底丢失那个文件】
Database altered.
HfeduSQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 100791
Session ID: 9 Serial number: 48626
HfeduSQL> alter database open;
ERROR:
ORA-03114: not connected to ORACLE
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 12:56:04 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 104155
Session ID: 9 Serial number: 50392
HfeduSQL> select * from v$log;
ERROR:
ORA-03114: not connected to ORACLE
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
exit[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 12:57:18 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> startup mount
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 7 209715200 512 1 NO CURRENT 2300 216 2023-04-01 12:44:45 1.8447E+19 0
3 1 6 209715200 512 1 YES INACTIVE 2300 211 2023-04-01 12:44:35 2300216 2023-04-01 12:44:45 0
2 1 0 209715200 512 1 YES UNUSED 2300 207 2023-04-01 12:44:31 2300211 2023-04-01 12:44:35 0
HfeduSQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 104723
Session ID: 9 Serial number: 38868
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$log
Errors in file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_lgwr_104650.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/hfzcdb/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2023-04-01T12:57:57.242234+08:00
Errors in file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_lgwr_104650.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/hfzcdb/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2023-04-01T12:57:57.244396+08:00
Errors in file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_ora_104723.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 3 thread 1: '/oradata/hfzcdb/redo03.log'
2023-04-01T12:57:57.255879+08:00
ARC3 started with pid=42, OS id=104892
USER (ospid: ): terminating the instance due to ORA error
2023-04-01T12:57:57.479311+08:00
System state dump requested by (instance=1, osid=104723), summary=[abnormal instance termination] .
System State dumped to trace file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_diag_1 04633.trc
2023-04-01T12:57:58.127377+08:00
Dumping diagnostic data in directory=[cdmp_20230401125757], requested by (instance=1, osid=104723 ), summary=[abnormal instance termination].
2023-04-01T12:57:59.195023+08:00
Instance terminated by USER, pid = 104723
^C
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$^C
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 12:59:09 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
HfeduSQL> exit
Disconnected
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 12:59:29 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> startup mount
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
HfeduSQL> alter database clear logfile group 3;
Database altered.
HfeduSQL> alter database open;
Database altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 7 209715200 512 1 NO CURRENT 2300 216 2023-04-01 12:44:45 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 2300 207 2023-04-01 12:44:31 2300211 2023-04-01 12:44:35 0
3 1 0 209715200 512 1 YES UNUSED 2300 211 2023-04-01 12:44:35 2300216 2023-04-01 12:44:45 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> /
System altered.
HfeduSQL> /
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 10 209715200 512 1 NO CURRENT 2301 447 2023-04-01 13:00:49 1.8447E+19 0
2 1 8 209715200 512 1 YES INACTIVE 2301 441 2023-04-01 13:00:45 2301444 2023-04-01 13:00:47 0
3 1 9 209715200 512 1 YES INACTIVE 2301 444 2023-04-01 13:00:47 2301447 2023-04-01 13:00:49 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHAN GE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- --- ------------------- ------------ ------------------- ----------
1 1 10 209715200 512 1 YES INACTIVE 2301 447 2023-04-01 13:00:49 2301451 2023-04-01 13:00:55 0
2 1 11 209715200 512 1 NO CURRENT 2301 451 2023-04-01 13:00:55 1.8447E+19 0
3 1 9 209715200 512 1 YES INACTIVE 2301 444 2023-04-01 13:00:47 2301447 2023-04-01 13:00:49 0
HfeduSQL>
--6.8.2.当前 redo(联机重做日志)文件丢失恢复 【 alter system set"_allow_resetlogs_corruption" = true scope = spfile;
recover database until cancel;alter database open resetlogs;】
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 10 209715200 512 1 YES INACTIVE 2 301447 2023-04-01 13:00:49 2301451 2023-04-01 13:00:55 0
2 1 11 209715200 512 1 NO CURRENT 2 301451 2023-04-01 13:00:55 1.8447E+19 0
3 1 9 209715200 512 1 YES INACTIVE 2 301444 2023-04-01 13:00:47 2301447 2023-04-01 13:00:49 0
HfeduSQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- ------------
IS_ CON_ID
--- ----------
1 ONLINE
/oradata/hfzcdb/redo01.log
NO 0
3 ONLINE
/oradata/hfzcdb/redo03.log
NO 0
2 ONLINE
/oradata/hfzcdb/redo02.log
NO 0
HfeduSQL> !rm /oradata/hfzcdb/redo02.log
HfeduSQL> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 105894
Session ID: 9 Serial number: 62497
HfeduSQL> startup
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 13:08:29 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$log
Errors in file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_mz00_109763.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 2 new persistent data failures
^C
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 13:09:01 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
HfeduSQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
HfeduSQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
hfzcdb MOUNTED
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 10 209715200 512 1 YES INACTIVE 2 301447 2023-04-01 13:00:49 2301451 2023-04-01 13:00:55 0
3 1 9 209715200 512 1 YES INACTIVE 2 301444 2023-04-01 13:00:47 2301447 2023-04-01 13:00:49 0
2 1 11 209715200 512 1 NO CURRENT 2 301451 2023-04-01 13:00:55 1.8447E+19 0
HfeduSQL> alter database clear unarchived logfile group 2; 【视频里面：clear没有报错，执行成功的，成功后直接就可以open数据库】--如果可以清空，不报错的话就可以直接打开了（alter database open;），我这里报错了
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> alter system set"_allow_resetlogs_corruption" = true scope = spfile;
System altered.
HfeduSQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> shutdown abort
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
HfeduSQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> alter system set"_allow_resetlogs_corruption" = true scope = spfile;
System altered.
HfeduSQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
HfeduSQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> shutdwon abort
SP2-0734: unknown command beginning "shutdwon a..." - rest of line ignored.
HfeduSQL> shutdown abort
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> recover database until cancel;
ORA-00279: change 2301451 generated at 04/01/2023 13:00:55 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_04_01/o1_mf_1_11_%u_.arc
ORA-00280: change 2301451 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/archive/HFZCDB/archivelog/2023_04_01/o1_mf_1_11_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log '/archive/HFZCDB/archivelog/2023_04_01/o1_mf_1_11_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
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: '/oradata/hfzcdb/system01.dbf'
HfeduSQL> alter database open '
2 ;
alter database open '
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
HfeduSQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
HfeduSQL> alter database open resetlogs;
Database altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 NO CURRENT 2 301452 2023-04-01 13:20:23 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 YES ACTIVE 2 301452 2023-04-01 13:20:23 2302387 2023-04-01 13:20:49 0
2 1 2 209715200 512 1 NO CURRENT 2 302387 2023-04-01 13:20:49 1.8447E+19 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 YES ACTIVE 2 301452 2023-04-01 13:20:23 2302387 2023-04-01 13:20:49 0
2 1 2 209715200 512 1 YES ACTIVE 2 302387 2023-04-01 13:20:49 2302392 2023-04-01 13:20:56 0
3 1 3 209715200 512 1 NO CURRENT 2 302392 2023-04-01 13:20:56 1.8447E+19 0
HfeduSQL>
1.2. 当前 redo(联机重做日志)文件丢失恢复
recover database until cancel;alter database open resetlogs;】
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 10 209715200 512 1 YES INACTIVE 2 301447 2023-04-01 13:00:49 2301451 2023-04-01 13:00:55 0
2 1 11 209715200 512 1 NO CURRENT 2 301451 2023-04-01 13:00:55 1.8447E+19 0
3 1 9 209715200 512 1 YES INACTIVE 2 301444 2023-04-01 13:00:47 2301447 2023-04-01 13:00:49 0
HfeduSQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- ------------
IS_ CON_ID
--- ----------
1 ONLINE
/oradata/hfzcdb/redo01.log
NO 0
3 ONLINE
/oradata/hfzcdb/redo03.log
NO 0
2 ONLINE
/oradata/hfzcdb/redo02.log
NO 0
HfeduSQL> !rm /oradata/hfzcdb/redo02.log
HfeduSQL> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 105894
Session ID: 9 Serial number: 62497
HfeduSQL> startup
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 13:08:29 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$log
Errors in file /oracle/app/oracle/diag/rdbms/hfzcdb/hfzcdb/trace/hfzcdb_mz00_109763.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 2 new persistent data failures
^C
[oracle@hfzcdb91:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 1 13:09:01 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
HfeduSQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
HfeduSQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
hfzcdb MOUNTED
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 10 209715200 512 1 YES INACTIVE 2 301447 2023-04-01 13:00:49 2301451 2023-04-01 13:00:55 0
3 1 9 209715200 512 1 YES INACTIVE 2 301444 2023-04-01 13:00:47 2301447 2023-04-01 13:00:49 0
2 1 11 209715200 512 1 NO CURRENT 2 301451 2023-04-01 13:00:55 1.8447E+19 0
HfeduSQL> alter database clear unarchived logfile group 2; 【视频里面：clear没有报错，执行成功的，成功后直接就可以open数据库】--如果可以清空，不报错的话就可以直接打开了（alter database open;），我这里报错了
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> alter system set"_allow_resetlogs_corruption" = true scope = spfile;
System altered.
HfeduSQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> shutdown abort
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
HfeduSQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> alter system set"_allow_resetlogs_corruption" = true scope = spfile;
System altered.
HfeduSQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
HfeduSQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance hfzcdb (thread 1)
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
HfeduSQL> shutdwon abort
SP2-0734: unknown command beginning "shutdwon a..." - rest of line ignored.
HfeduSQL> shutdown abort
ORACLE instance shut down.
HfeduSQL> startup
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 671088640 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/hfzcdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
HfeduSQL> recover database until cancel;
ORA-00279: change 2301451 generated at 04/01/2023 13:00:55 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_04_01/o1_mf_1_11_%u_.arc
ORA-00280: change 2301451 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/archive/HFZCDB/archivelog/2023_04_01/o1_mf_1_11_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log '/archive/HFZCDB/archivelog/2023_04_01/o1_mf_1_11_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
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: '/oradata/hfzcdb/system01.dbf'
HfeduSQL> alter database open '
2 ;
alter database open '
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
HfeduSQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
HfeduSQL> alter database open resetlogs;
Database altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 NO CURRENT 2 301452 2023-04-01 13:20:23 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 YES ACTIVE 2 301452 2023-04-01 13:20:23 2302387 2023-04-01 13:20:49 0
2 1 2 209715200 512 1 NO CURRENT 2 302387 2023-04-01 13:20:49 1.8447E+19 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
HfeduSQL> alter system switch logfile;
System altered.
HfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_C HANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------- ------ ------------------- ------------ ------------------- ----------
1 1 1 209715200 512 1 YES ACTIVE 2 301452 2023-04-01 13:20:23 2302387 2023-04-01 13:20:49 0
2 1 2 209715200 512 1 YES ACTIVE 2 302387 2023-04-01 13:20:49 2302392 2023-04-01 13:20:56 0
3 1 3 209715200 512 1 NO CURRENT 2 302392 2023-04-01 13:20:56 1.8447E+19 0
HfeduSQL>
扫一扫
137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
