[2020-12-25]oracle redo文件损坏的几种情况和处理方法

第一种情况

一、非current日志组并且状态不是active

--//redo 损坏测试
--//非current日志组且状态不是active

SQL> select * from t;

        ID
----------
        10

SQL> update t set id = 11;

1 row updated.

SQL> col member for a34
SQL> select v1.group#,v1.status, member,sequence#,first_change#
  2  from v$log v1,v$logfile v2
  3  where v1.group#=v2.group#;

    GROUP# STATUS           MEMBER                              SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------------------------------- ---------- -------------
         3 UNUSED           /u01/app/oradata/QXY/redo03.log             0             0
         2 UNUSED           /u01/app/oradata/QXY/redo02.log             0             0
         1 CURRENT          /u01/app/oradata/QXY/redo01.log             1       1757410

SQL> 
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit

--//在主机层面删除日志组2

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@QXY ~]$ rm -rf /u01/app/oradata/QXY/redo02.log 
[oracle@QXY ~]$ 

--//启动数据库

SQL> startup 
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2946
Session ID: 125 Serial number: 5

--//清除日志组之后再次启动数据库

SQL> alter database clear logfile group 2;
ERROR:
ORA-03114: not connected to ORACLE


SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL> 

--//对应后台日志

alter database clear logfile group 2
Clearing online log 2 of thread 1 sequence number 0
Errors in file /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_2997.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/QXY/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_2997.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/QXY/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Dec 23 15:32:22 2020
Errors in file /u01/app/diag/rdbms/qxy/QXY/trace/QXY_m000_2999.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/u01/app/oradata/QXY/redo02.log'
Completed: alter database clear logfile group 2
Checker run found 1 new persistent data failures
alter database open
Wed Dec 23 15:32:27 2020
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oradata/QXY/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 23 15:32:27 2020
SMON: enabling cache recovery
[2997] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:449374 end:449394 diff:20 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 23 15:32:28 2020
QMNC started with pid=20, OS id=3003 
Completed: alter database open

参考MOS 

Redo Log Corruption - Dropping Redo Log Is Not Possible - CLEAR LOGFILE (Doc ID 1078456.6)

APPLIES TO:

Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

 

Redo log corruption errors in one of the redo log files while the database is open.

The redo log corruption could be any of these errors:

ORA-16038 log %s sequence# %s cannot be archived
ORA-354 corrupt redo log block header
ORA-353 log corruption near block <num> change <str >time <str>
ORA-367 checksum error in log file header
ORA-368 checksum error in redo log block

Dropping the redo log is not possible because it may be needed for instance recovery.

The online redo logs may not be dropped if:

• There are only two log groups
• The corrupt redo log file belongs to the current group

CHANGES

 

CAUSE

 There are many possible reasons why a redo log file becomes corrupted like Hardware Failures. 

SOLUTION

Clear the logfile having the problem:

Syntax:

alter database clear <unarchived> logfile group <integer>;
alter database clear <unarchived> logfile '<filename>';

Example:

alter database clear logfile group 1;
alter database clear unarchived logfile group 1;

An online redo log file with status=CURRENT or status=ACTIVE in v$log may not be cleared and the error ORA-1624 can be produced. In this case, the database will have to be restored and recovered to a point in time to the last available archived log file.

NOTE: the 'alter database clear logfile' should be used cautiously. If no archived log was produced, then a complete recovery will not be possible. Perform a backup immediately after completing this command.

Explanation:

If an online redo log file has been corrupted while the database is open, the 'alter database clear logfile' command can be used to clear the files without the database having to be shutdown.

The command erases all data in the specified logfile group.

IMPORTANT: It is essential that a new database backup is taken as missing archivelog sequence will affect full database recovery.

 

第二种情况

--//非current，但是状态是active，并且上面有事物

SQL> col member for a34
SQL> select v1.group#,v1.status, member,sequence#,first_change#
  2  from v$log v1,v$logfile v2
  3  where v1.group#=v2.group#;

    GROUP# STATUS           MEMBER                              SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------------------------------- ---------- -------------
         3 CURRENT          /u01/app/oradata/QXY/redo03.log             3       1779820
         2 INACTIVE         /u01/app/oradata/QXY/redo02.log             2       1779184
         1 INACTIVE         /u01/app/oradata/QXY/redo01.log             1       1757410

SQL> select * from t;

        ID
----------
        10

SQL> update t set id = 11;

1 row updated.

SQL> alter system switch logfile;

System altered.

SQL> col member for a34
SQL> select v1.group#,v1.status, member,sequence#,first_change#
  2  from v$log v1,v$logfile v2
  3  where v1.group#=v2.group#;

    GROUP# STATUS           MEMBER                              SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------------------------------- ---------- -------------
         3 ACTIVE           /u01/app/oradata/QXY/redo03.log             3       1779820
         2 INACTIVE         /u01/app/oradata/QXY/redo02.log             2       1779184
         1 CURRENT          /u01/app/oradata/QXY/redo01.log             4       1779858

SQL> 
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit

--//删除日志组3

[oracle@QXY ~]$ rm -rf /u01/app/oradata/QXY/redo03.log
[oracle@QXY ~]$ 

--//启动数据库

SQL> startup 
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oradata/QXY/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> 

--//清除日志组报错

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance QXY (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oradata/QXY/redo03.log'


SQL> 

--//使用隐含参数打开
--//*._allow_resetlogs_corruption=TRUE

--//创建新的参数文件

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL> 

--//把参数*._allow_resetlogs_corruption=TRUE添加进去
--//重新启动数据库

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile.txt' mount;
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
SQL> 

--//使用resetlogs打开

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

--//使用recover让oracle认为是recover模式

SQL> recover database until cancel;
ORA-00279: change 1779835 generated at 12/23/2020 15:37:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_3_%u_.arc
ORA-00280: change 1779835 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


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/oradata/QXY/system01.dbf'


SQL> 

--//再次使用resetlogs方式打开

SQL> alter database open resetlogs;

Database altered.

SQL> 

第三种情况

--//第三种情况，是active但是上面的事务都已经提交

SQL> col member for a34
SQL> select v1.group#,v1.status, member,sequence#,first_change#
  2  from v$log v1,v$logfile v2
  3  where v1.group#=v2.group#;

    GROUP# STATUS           MEMBER                              SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------------------------------- ---------- -------------
         3 CURRENT          /u01/app/oradata/QXY/redo03.log             3       1780299
         2 INACTIVE         /u01/app/oradata/QXY/redo02.log             2       1780248
         1 INACTIVE         /u01/app/oradata/QXY/redo01.log             1       1779836

SQL> select * from t;

        ID
----------
        10

SQL> update t set id = 11;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> col member for a34
SQL> select v1.group#,v1.status, member,sequence#,first_change#
  2  from v$log v1,v$logfile v2
  3  where v1.group#=v2.group#;

    GROUP# STATUS           MEMBER                              SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------------------------------- ---------- -------------
         3 ACTIVE           /u01/app/oradata/QXY/redo03.log             3       1780299
         2 INACTIVE         /u01/app/oradata/QXY/redo02.log             2       1780248
         1 CURRENT          /u01/app/oradata/QXY/redo01.log             4       1780352

SQL> 

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit

--//删除日志组3

[oracle@QXY ~]$ rm -rf /u01/app/oradata/QXY/redo03.log 
[oracle@QXY ~]$ o

--//启动数据库

SQL> startup 
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oradata/QXY/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> 

--//首先尝试使用clear日志组方式

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance QXY (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oradata/QXY/redo03.log'

--//发现无法clear，还是需要使用*._allow_resetlogs_corruption=TRUE
--//创建pfile，添加隐含参数
--//创建新的参数文件

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL> 

--//添加隐含参数
--//使用pfile启动数据库

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile.txt' mount;
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
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>

--//这次不使用第二种的recover database until cancel;
--//使用重建控制文件的方式来达到resetlogs启动的方式
--//backup 控制文件

SQL> 
SQL> alter database backup controlfile to trace;

Database altered.

SQL> select value from v$diag_info where name like '%Default%';

VALUE
--------------------------------------------------------------------------------
/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_3388.trc

SQL> 

--//获取resetlogs方式的创建脚本

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "QXY" RESETLOGS FORCE LOGGING NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oradata/QXY/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oradata/QXY/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oradata/QXY/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oradata/QXY/system01.dbf',
  '/u01/app/oradata/QXY/sysaux01.dbf',
  '/u01/app/oradata/QXY/undotbs01.dbf',
  '/u01/app/oradata/QXY/users01.dbf',
  '/u01/app/oradata/QXY/example01.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Set Database Guard and/or Supplemental Logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/QXY/temp01.dbf' REUSE;
-- End of tempfile additions.
--

--//重新使用pfile方式启动到nomount的模式

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> startup pfile='/tmp/pfile.txt' nomount;
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
SQL> 

--//重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "QXY" RESETLOGS FORCE LOGGING NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oradata/QXY/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oradata/QXY/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oradata/QXY/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oradata/QXY/system01.dbf',
 14    '/u01/app/oradata/QXY/sysaux01.dbf',
 15    '/u01/app/oradata/QXY/undotbs01.dbf',
 16    '/u01/app/oradata/QXY/users01.dbf',
 17    '/u01/app/oradata/QXY/example01.dbf'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 1780339 generated at 12/23/2020 15:47:37 needed for thread 1
ORA-00289: suggestion :
/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_3_%u_.arc
ORA-00280: change 1780339 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/fast_recovery_area/QXY/archivelog/2020_12_23/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--//使用resetlogs方式打开

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> 
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/QXY/temp01.dbf' REUSE;

Tablespace altered.

SQL> 

第四种情况

--//删除current日志组

SQL> set lin 300
SQL> /

    GROUP# STATUS           MEMBER                              SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------------------------------- ---------- -------------
         3 INACTIVE         /u01/app/oradata/QXY/redo03.log             3       1881000
         2 INACTIVE         /u01/app/oradata/QXY/redo02.log             2       1880772
         1 CURRENT          /u01/app/oradata/QXY/redo01.log             4       1881202

SQL> select * from t;

        ID
----------
        10

SQL> update t set id = 11;

1 row updated.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit

--//删除当前日志组

[oracle@QXY ~]$ rm -rf /u01/app/oradata/QXY/redo01.log

--//启动数据库

[oracle@QXY ~]$ o

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 23 16:55:32 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oradata/QXY/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--//创建pfile

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL> exit

--//增加隐含参数*._allow_resetlogs_corruption=TRUE

--//重新创建控制文件

SQL> startup pfile='/tmp/pfile.txt' mount;  
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> select value from v$diag_info where name like 'Def%';

VALUE
--------------------------------------------------------------------------------
/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4370.trc

SQL> exit

SQL> startup pfile='/tmp/pfile.txt' nomount;
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "QXY" RESETLOGS FORCE LOGGING NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oradata/QXY/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oradata/QXY/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oradata/QXY/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oradata/QXY/system01.dbf',
 14    '/u01/app/oradata/QXY/sysaux01.dbf',
 15    '/u01/app/oradata/QXY/undotbs01.dbf',
 16    '/u01/app/oradata/QXY/users01.dbf',
 17    '/u01/app/oradata/QXY/example01.dbf'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.

--//使用resetlogs的方式报错2662

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [1881212], [0],
[1882699], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [1881211], [0],
[1882699], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1881209], [0],
[1882699], [12583040], [], [], [], [], [], []
Process ID: 4414
Session ID: 125 Serial number: 3


SQL> 

--//启动失败之后，再次open会提示文件需要recover

SQL> startup 
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup 
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oradata/QXY/system01.dbf'


SQL> 

--//再次重新创建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "QXY" RESETLOGS FORCE LOGGING NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oradata/QXY/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oradata/QXY/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oradata/QXY/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oradata/QXY/system01.dbf',
 14    '/u01/app/oradata/QXY/sysaux01.dbf',
 15    '/u01/app/oradata/QXY/undotbs01.dbf',
 16    '/u01/app/oradata/QXY/users01.dbf',
 17    '/u01/app/oradata/QXY/example01.dbf'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.

--//获取checkpoint_change#大小

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           1881206
           1881206
           1881206
           1881206
           1881206

--//使用oradebug推进scn，推进大小可以在checkpoint_change#增加一些

SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL> oradebug poke 0x06001AE70 8  1891206
BEFORE: [06001AE70, 06001AE78) = 00000000 00000000
AFTER:  [06001AE70, 06001AE78) = 001CDB86 00000000

--//使用resetlogs方式打开数据库

SQL> alter database open resetlogs;

Database altered.

SQL>