分类目录归档：Oracle备份恢复

使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障

发表于 2013 年 8 月 26 日 由 惜分飞

以前写过一篇乱用_allow_resetlogs_corruption参数导致悲剧的文章,昨天晚上又遇到一个朋友不谨慎使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
环境描述
系统环境:solaris
数据库版本:10.2.0.5.7
数据存储方式:ASM
数据量:15T以上
补充事宜:数据库SCN距离headroom只有54天

报ORA-00020错误，实例crash
数据库因为超过了系统的进程数，出现dbwn进程写数据文件异常

Sun Aug 25 16:00:41 CST 2013
Errors in file /opt/oracle/admin/orcl/bdump/orcl_dbw0_7490.trc:
ORA-01148: 无法刷新数据文件 22 的文件大小
ORA-01110: 数据文件 22: '+DATA/orcl/datafile/index_jh.dbf'
ORA-00020: 超出最大进程数 ()
Sun Aug 25 16:00:41 CST 2013
Errors in file /opt/oracle/admin/orcl/bdump/orcl_dbw0_7490.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
ORA-01110: 数据文件 22: '+DATA/orcl/datafile/index_jh.dbf'
Sun Aug 25 16:00:41 CST 2013
DBW0: terminating instance due to error 1242
Termination issued to instance processes. Waiting for the processes to exit
Sun Aug 25 16:00:51 CST 2013
Instance termination failed to kill one or more processes
Instance terminated by DBW0, pid = 7490

ORA-00600[kcbtema_10]
实例恢复出现ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []

Sun Aug 25 19:19:23 CST 2013
ALTER DATABASE OPEN
Sun Aug 25 19:19:38 CST 2013
Beginning crash recovery of 1 threads
 parallel recovery started with 16 processes
Sun Aug 25 19:19:40 CST 2013
Started redo scan
Sun Aug 25 19:20:07 CST 2013
Completed redo scan
 12016413 redo blocks read, 93405 data blocks need recovery
Sun Aug 25 19:20:19 CST 2013
Started redo application at
 Thread 1: logseq 53681, block 1091966
Sun Aug 25 19:20:19 CST 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53681 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/redo_1_1.log
  Mem# 1: +DATA/orcl/onlinelog/redo_1_2.log
Sun Aug 25 19:20:21 CST 2013
Errors in file /opt/oracle/admin/orcl/bdump/orcl_p011_16944.trc:
ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []
Sun Aug 25 19:20:23 CST 2013
Errors in file /opt/oracle/admin/orcl/bdump/orcl_p011_16944.trc:
ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []
Sun Aug 25 19:20:23 CST 2013
Aborting crash recovery due to slave death, attempting serial crash recovery
Sun Aug 25 19:20:23 CST 2013
Beginning crash recovery of 1 threads
Sun Aug 25 19:20:23 CST 2013
Started redo scan
Sun Aug 25 19:20:47 CST 2013
Completed redo scan
 12016413 redo blocks read, 93405 data blocks need recovery
Sun Aug 25 19:20:54 CST 2013
Started redo application at
 Thread 1: logseq 53681, block 1091966
Sun Aug 25 19:20:54 CST 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53681 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/redo_1_1.log
  Mem# 1: +DATA/orcl/onlinelog/redo_1_2.log
Sun Aug 25 19:20:54 CST 2013
Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_16751.trc:
ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []
Sun Aug 25 19:20:56 CST 2013
Aborting crash recovery due to error 600
Sun Aug 25 19:20:56 CST 2013
Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_16751.trc:
ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...

使用隐含参数

ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE;

报ORA-00704/ORA-01555
因为在前面的恢复中进行了不完全恢复，因此这里加入隐含参数，然后尝试resetlogs,然后报如下错误

Sun Aug 25 20:11:54 CST 2013
alter database open resetlogs
Sun Aug 25 20:12:10 CST 2013
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 13429649847189
Resetting resetlogs activation ID 1312390734 (0x4e397e4e)
Sun Aug 25 20:16:25 CST 2013
Setting recovery target incarnation to 2
Sun Aug 25 20:16:42 CST 2013
************************************************************
Warning: The SCN headroom for this database is only 54 days!
************************************************************
Sun Aug 25 20:16:43 CST 2013
Assigning activation ID 1352200163 (0x5098efe3)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: +DATA/orcl/onlinelog/redo_1_1.log
  Current log# 1 seq# 1 mem# 1: +DATA/orcl/onlinelog/redo_1_2.log
Successful open of redo thread 1
Sun Aug 25 20:16:43 CST 2013
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Aug 25 20:16:52 CST 2013
SMON: enabling cache recovery
Sun Aug 25 20:16:52 CST 2013
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0c36.d582339b):
Sun Aug 25 20:16:52 CST 2013
select ctime, mtime, stime from obj$ where obj# = :1
Sun Aug 25 20:16:52 CST 2013
Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_2859.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 143 (名称为 "_SYSSMU143$") 过小
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Termination issued to instance processes. Waiting for the processes to exit
Sun Aug 25 20:17:02 CST 2013
Instance termination failed to kill one or more processes
Instance terminated by USER, pid = 2859
ORA-1092 signalled during: alter database open resetlogs...

数据库当前SCN

SQL > select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
    13429649947222

SQL > select distinct CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
    13429649947222

解决方法
因为该数据库版本为10.2.0.5.7,已经包含了scn patch,因此不能使用event或者隐含参数来修改scn,而且该库容量15T以上(asm),因此也无法使用bbed修改数据文件头,最后决定使用ordebug来解决该问题
使用oradebug DUMPvar SGA kcsgscn_
使用oradebug poke 

sqlplus / as sysdba
startup mount

oradebug setmypid
oradebug DUMPvar SGA kcsgscn_
oradebug poke 

recover database;
alter database open;

事后总结
查询MOS,发现ORA-00600[kcbtema_10] Raised During Recovery Operations (Doc ID 472282.1)

--故障原因
The cause of this problem has been identified and verified in unpublished Bug 5184359 ORA-600 [KCBTEMA_10].
Due to this bug, during recovery, the class designation of a data block has changed.
--处理方法 
SQL>startup mount
SQL>recover database;
SQL>alter database open;

因为MOS上给的解决思路在该数据库中已经无法尝试，不能确定该方法一定可行，但是对于本次的恢复过程中，没有任何直接recover database操作(只有一次不完全恢复)确实让人有无限的遗憾和可惜。对于本次应该先查询MOS，尝试该种方法，慎重使用_allow_resetlogs_corruption参数

发表在 非常规恢复 | 标签为 , , , , , | 评论关闭

修改props$.NLS_CHARACTERSET导致ORA-00900异常恢复

发表于 2013 年 8 月 20 日 由 惜分飞

今天一朋友和我说他的数据库不能open,open过程提示ORA-00900错误,通过分析alert日志和props$表,发现他们修改了一个无效的NLS_CHARACTERSET值，导致数据库无法正常启动(准确的说，因为数据库里面该值无效，当数据库open的过程中，检测到控制文件指定的编码和该值不一致，然后修改控制文件的编码，修改之后，数据库一到mount状态执行任何语句都报ORA-00900错误),通过一些工具修改NLS_CHARACTERSET为正确值该故障解决

重现ORA-00900故障

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select value$ from props$ where name='NLS_CHARACTERSET';

VALUE$
-------------------------------------------------------
ZHS16GBK

SQL> update props$ set value$='AL16UTF16' where name='NLS_CHARACTERSET';

1 row updated.

SQL> commit;

Commit complete.

SQL> alter database backup controlfile to trace as '/tmp/ora11g.ctl';

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
Process ID: 5277
Session ID: 125 Serial number: 5

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00900: invalid SQL statement

SQL>select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00900: invalid SQL statement

SQL> shutdown abort
ORACLE instance shut down.

第一次startup(open)过程报错

SMON: enabling tx recovery
Updating character set in controlfile to AL16UTF16
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5277.trc:
ORA-00604: error occurred at recursive SQL level %s
ORA-00900: invalid SQL statementursive SQL level %s
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5277.trc:
ORA-00604: error occurred at recursive SQL level %s
ORA-00900: invalid SQL statementursive SQL level %s
Error 604 happened during db open, shutting down database
USER (ospid: 5277): terminating the instance due to error 604
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_smon_5243.trc:
ORA-00604: error occurred at recursive SQL level %s
ORA-00900: invalid SQL statementursive SQL level %s
Instance terminated by USER, pid = 5277
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (5277) as a result of ORA-1092
Sat May 18 00:44:27 2013
ORA-1092 : opitsk aborting process

这里比较明显的看到有一条(Updating character set in controlfile to AL16UTF16),正是由于这个操作，更新控制文件的编码为一个无效的编码，从而导致在后面数据库mount(加载控制文件)之后，就不能再进行其他任何操作

解决思路
使用odu找出来block位置，或者在同版本库中查询
使用dul或者bbed修改props$的NLS_CHARACTERSET值
重建控制文件(noresetlogs方式)

处理过程

SQL> shutdown abort
ORACLE instance shut down.

odu找出来block位置
dul或者bbed修改block值
重建控制文件(noresetlogs方式)

SQL> startup
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'


SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

从oracle 9i开始，修改数据库的编码，直接使用alter database character set internal_use方式进行,而不要使用直接修改props$基表,更不能修改一个实际中不存在的编码值(本文中数据库编码和国家编码搞混淆从而出现该故障)

发表在 非常规恢复 | 标签为 , | 2 条评论

ORACLE 12C 在datapump方面增强参数

发表于 2013 年 7 月 14 日 由 惜分飞

在阅读ORACLE 12C datapump相关文档之时,发现有两个比较欣喜的参数LOGTIME和SQLFILE,鉴于他们是12C新增加参数,对他们的使用方法和用途进行简单说明
LOGTIME参数
该参数可以用于expdp/impdp,主要作用是记录执行步骤的开始时间,精确到微秒,使用语法为

LOGTIME=[NONE | STATUS | LOGFILE | ALL]
• NONE--No timestamps on status or log file messages (same as default)
• STATUS--Timestamps on status messages only
• LOGFILE--Timestamps on log file messages only
• ALL--Timestamps on both status and log file messages

该参数主要在我们对于一些数据库迁移升级项目使用datapump的时候,在测试阶段能够通过该参数发现哪一步执行时间较长,然后对其调优减少执行时间;另外一点就是可以通过做减法精确到具体的时间(毫米),我们可以知道我们的datapump主要耗时在哪一步,做到心中有数不慌

LOGTIME测试

SQL> conn chf/xifenfei@pdb
已连接。

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO

SQL> create directory temp as 'e:\';

目录已创建。

SQL> create table t_xifenfei as select * from dba_objects;

表已创建。

SQL> create index ind_t_xifenfei on t_xifenfei(object_id);

索引已创建。

C:\Users\XIFENFEI>expdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenf
ei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all

Export: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:11:24 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
14-7月 -13 20:11:35.961: 启动 "CHF"."SYS_EXPORT_TABLE_01":  chf/********@pdb dumpfile=t_xifenfei.dmp 
 tables=t_xifenfei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all
14-7月 -13 20:11:37.703: 正在使用 BLOCKS 方法进行估计...
14-7月 -13 20:11:40.636: 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
14-7月 -13 20:11:40.825: 使用 BLOCKS 方法的总估计: 13 MB
14-7月 -13 20:11:48.802: 处理对象类型 TABLE_EXPORT/TABLE/TABLE
14-7月 -13 20:11:54.543: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
14-7月 -13 20:11:57.204: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
14-7月 -13 20:11:59.269: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
14-7月 -13 20:11:59.306: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER
14-7月 -13 20:12:36.563: . . 导出了 "CHF"."T_XIFENFEI"  10.36 MB   90865 行
14-7月 -13 20:12:37.527: 已成功加载/卸载了主表 "CHF"."SYS_EXPORT_TABLE_01"
14-7月 -13 20:12:37.533: ******************************************************************************
14-7月 -13 20:12:37.537: CHF.SYS_EXPORT_TABLE_01 的转储文件集为:
14-7月 -13 20:12:37.547:   E:\T_XIFENFEI.DMP
14-7月 -13 20:12:37.577: 作业 "CHF"."SYS_EXPORT_TABLE_01" 已于 星期日 7月 14 20:12:37 2013 elapsed 0 00:01:06 成功完成

SQLFILE参数
该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为

SQLFILE=[directory_object:]file_name

注意事项:
1.directory_object可以不和impdp的DIRECTORY参数不一致,如果是一样,directory_object可以省略
2.SQLFILE文件必须写入到磁盘之上,不能写入到ASM中
3.SQLFILE和QUERY参数冲突,不能同时使用

SQLFILE测试

C:\Users\XIFENFEI>impdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei logfile=t_xifenfei.log
 directory=temp  sqlfile=t_xifenfei.sql

Import: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:42:13 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
已成功加载/卸载了主表 "CHF"."SYS_SQL_FILE_TABLE_01"
启动 "CHF"."SYS_SQL_FILE_TABLE_01":  chf/********@pdb dumpfile=t_xifenfei.dmp ta
bles=t_xifenfei logfile=t_xifenfei.log directory=temp sqlfile=t_xifenfei.sql
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER
作业 "CHF"."SYS_SQL_FILE_TABLE_01" 已于 星期日 7月 14 20:42:25 2013 elapsed 0 00:00:08 成功完成

t_xifenfei.sql内容

-- CONNECT CHF
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "CHF"."T_XIFENFEI" 
   (	"OWNER" VARCHAR2(128 BYTE), 
	"OBJECT_NAME" VARCHAR2(128 BYTE), 
	"SUBOBJECT_NAME" VARCHAR2(128 BYTE), 
	"OBJECT_ID" NUMBER, 
	"DATA_OBJECT_ID" NUMBER, 
	"OBJECT_TYPE" VARCHAR2(23 BYTE), 
	"CREATED" DATE, 
	"LAST_DDL_TIME" DATE, 
	"TIMESTAMP" VARCHAR2(19 BYTE), 
	"STATUS" VARCHAR2(7 BYTE), 
	"TEMPORARY" VARCHAR2(1 BYTE), 
	"GENERATED" VARCHAR2(1 BYTE), 
	"SECONDARY" VARCHAR2(1 BYTE), 
	"NAMESPACE" NUMBER, 
	"EDITION_NAME" VARCHAR2(128 BYTE), 
	"SHARING" VARCHAR2(13 BYTE), 
	"EDITIONABLE" VARCHAR2(1 BYTE), 
	"ORACLE_MAINTAINED" VARCHAR2(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "CHF"."IND_T_XIFENFEI" ON "CHF"."T_XIFENFEI" ("OBJECT_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "CHF"."IND_T_XIFENFEI" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER

ORACLE 12C datapump关于LOGTIME和SQLFILE参数

发表在 ORACLE 12C, 逻辑备份/恢复 | 标签为 | 一条评论