数据库不一致时启动数据库


本文介绍数据库不一致时如何启动数据库。

环境准备

创建一个user2表空间,然后人为的制造这个表空间的数据文件和其它数据文件不一致:

SQL> create tablespace user2 datafile '/u01/app/oracle/oradata/ORCL/users02.dbf' size 10m;

Tablespace created.

SQL> create table user2_t1 tablespace user2  as select object_id,object_name from dba_objects;

Table created.

SQL> ! cp /u01/app/oracle/oradata/ORCL/users02.dbf /u01/app/oracle/oradata/ORCL/users02.dbf.bk

SQL> alter system checkpoint;

System altered.
SQL> shutdown abort
ORACLE instance shut down.
SQL> ! cp /u01/app/oracle/oradata/ORCL/users02.dbf.bk /u01/app/oracle/oradata/ORCL/users02.dbf

SQL> startup 
ORACLE instance started.

Total System Global Area  771747984 bytes
Fixed Size		    8900752 bytes
Variable Size		  629145600 bytes
Database Buffers	  125829120 bytes
Redo Buffers		    7872512 bytes
Database mounted.
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/u01/app/oracle/oradata/ORCL/users02.dbf'


SQL> set linesize 200
col name form a55 
select a.file#,a.name,a.CHECKPOINT_CHANGE# con_cc,b.CHECKPOINT_CHANGE# data_cc,to_char(a.CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CPT from v$datafile a ,v$datafile_header b where a.file#=b.file#;
SQL> SQL> 
     FILE# NAME 						       CON_CC	 DATA_CC CPT
---------- ------------------------------------------------------- ---------- ---------- -------------------
	 1 /u01/app/oracle/oradata/ORCL/system01.dbf		      2805225	 2805225 2021-04-26 17:41:49
	 3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf		      2805225	 2805225 2021-04-26 17:41:49
	 4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf		      2805225	 2805225 2021-04-26 17:41:49
	 5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf	      1559612	 1559612 2018-10-19 11:55:19
	 6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf	      1559612	 1559612 2018-10-19 11:55:19
	 7 /u01/app/oracle/oradata/ORCL/users01.dbf		      2805225	 2805225 2021-04-26 17:41:49
	 8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf	      1559612	 1559612 2018-10-19 11:55:19
	 9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf	      2583603	 2583603 2021-04-26 11:42:12
	10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf	      2583603	 2583603 2021-04-26 11:42:12
	11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf	      2583603	 2583603 2021-04-26 11:42:12
	12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf	      2583603	 2583603 2021-04-26 11:42:12

     FILE# NAME 						       CON_CC	 DATA_CC CPT
---------- ------------------------------------------------------- ---------- ---------- -------------------
	13 /u01/app/oracle/oradata/ORCL/users02.dbf		      2805225	 2804056 2021-04-26 17:41:49

12 rows selected.

视图v d a t a f i l e 的 内 容 来 自 控 制 文 件 , 视 图 v datafile的内容来自控制文件,视图v datafilevdatafile_header的内容来自数据文件,连接这两个视图发现第13个数据文件的控制文件的checkpoint和数据文件头的checkpoint不一致。

使用recover database进行恢复

SQL> 
SQL> recover database until cancel;
ORA-00279: change 2804056 generated at 04/26/2021 17:38:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/18.0.0/db_1/dbs/arch1_3_1070883714.dbf
ORA-00280: change 2804056 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORCL/redo03.log
Log applied.
Media recovery complete.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS		 FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
	 1	    1	       1  209715200	   512		1 YES
INACTIVE	       2583461 26-APR-21      2685066 26-APR-21 	 0

	 3	    1	       3  209715200	   512		1 NO
CURRENT 	       2803521 26-APR-21   1.8447E+19			 0

	 2	    1	       2  209715200	   512		1 YES
INACTIVE	       2685066 26-APR-21      2803521 26-APR-21 	 0

提示需要从SCN 2804056开始恢复,检查v l o g 视 图 , 发 现 这 个 S C N 在 第 3 个 联 机 日 志 里 面 , 输 入 第 3 个 联 机 日 志 文 件 的 文 件 路 径 和 文 件 名 , 恢 复 后 , 检 查 发 现 第 13 个 数 据 文 件 的 S C N 变 成 了 一 致 的 状 态 。 S Q L > s e l e c t n a m e f r o m v log视图,发现这个SCN在第3个联机日志里面,输入第3个联机日志文件的文件路径和文件名,恢复后,检查发现第13个数据文件的SCN变成了一致的状态。 SQL> select name from v logSCN3313SCNSQL>selectnamefromvfixed_table where name like ‘%ARCHIVE%’;
也可以在V$ARCHIVED_LOG视图中查找包含这个SCN的归档文件。

使用隐含参数_allow_resetlogs_corruption启动数据库

参考:
_allow_resetlogs_corruption tips
  在前面等例子中,如果需要等第三个联机日志找不到了,还可以使用隐含参数_allow_resetlogs_corruption启动数据库。


SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  771747984 bytes
Fixed Size		    8900752 bytes
Variable Size		  629145600 bytes
Database Buffers	  125829120 bytes
Redo Buffers		    7872512 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

在数据库日志中有类似下面的提示:

RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 2805283 time

验证数据文件13里面的数据结构:

SQL> ANALYZE TABLE user2_t1 VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE user2_t1 VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbzib_6], [0], [4], [], [], [], [], [], [], [], [], []
SQL> select count(*) from user2_t1;
select count(*) from user2_t1
                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbzib_6], [0], [4], [], [], [], [], [], [], [], [], []

将数据导出,新建数据库再导入。

ALTER SYSTEM SET "_allow_resetlogs_corruption"= false SCOPE = SPFILE;
姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页