分类目录归档：Oracle

oracle dul 11 正式发布

发表于 2016 年 6 月 13 日 由 惜分飞

千呼万唤oracle dul 11终于发布了,迫不及待的下载来测试,现在版本号为dul 11.2.0.0.1,目前只发布了for linux,其他版本估计要等等.期待该版本有引进新功能
oracle dul 11发布
dul-11.2
oracle-dul-11

dul 11 测试恢复oracle 12c版本

[root@ora1221 dul11]# ./dul

Data UnLoader: 11.2.0.0.1 - Internal Only - on Mon Jun 13 00:04:50 2016
with 64-bit io functions

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Found db_id = 1414800805
Found db_name = ORCL

DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   72547 rows unloaded
. unloading table                      TAB$    2137 rows unloaded
. unloading table                      COL$  115645 rows unloaded
. unloading table                     USER$     129 rows unloaded
Reading USER.dat 129 entries loaded
Reading OBJ.dat
DUL: FATAL Error: File OBJ.dat, line 22094: identifier too long

dul 11 恢复oracle 12c 报错分析
看报错OBJ.dat报错数据,从定义上判断是长度过长

[root@ora1221 dul11]# vi OBJ.dat 
  22091 "22435" "" "0" "JVMFCB" "" "9" ""
  22092 "22436" "" "0" "JVMFCB" "" "11" ""
  22093 "22437" "56417" "0" "JAVA$CBOBJ$" "" "2" ""
----  22094 "22439" "" "0" "sun/util/xml/PlatformXmlPropertiesProvider$Resolver" "" "29" ""
  22095 "22440" "" "0" "sun/util/xml/PlatformXmlPropertiesProvider$EH" "" "29" ""
  22096 "22441" "" "0" "sun/util/xml/PlatformXmlPropertiesProvider$1" "" "29" ""
  22097 "22442" "" "0" "sun/util/resources/LocaleNamesBundle" "" "29" ""

对象名长度分析

[oracle@ora1221 ~]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Fri Aug 7 02:50:35 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL> select length(object_name) from dba_objects 
  2  where object_name='sun/util/xml/PlatformXmlPropertiesProvider$Resolver';

LENGTH(OBJECT_NAME)
-------------------
                 51

SQL> select max(length(object_name)) from dba_objects;

MAX(LENGTH(OBJECT_NAME))
------------------------
                     123

SQL> desc dba_objects
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(23)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(128)
 SHARING                                            VARCHAR2(18)
 EDITIONABLE                                        VARCHAR2(1)
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 APPLICATION                                        VARCHAR2(1)
 DEFAULT_COLLATION                                  VARCHAR2(100)
 DUPLICATED                                         VARCHAR2(1)
 SHARDED                                            VARCHAR2(1)
 CREATED_APPID                                      NUMBER
 CREATED_VSNID                                      NUMBER
 MODIFIED_APPID                                     NUMBER
 MODIFIED_VSNID                                     NUMBER

在oracle 12c中对象名长度已经变为128,但是dul依旧不能支持列长度，既然作者已经在init.dul的Compatible参数曾增加了对12c的支持,那对这样明显的bug应该在不久的将来即可修复。当然这个问题,可以通过一些人工技巧绕过去

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

_use_single_log_writer和_max_outstanding_log_writes

发表于 2016 年 6 月 7 日 由 惜分飞

SCALABLE LGWR是12cR1中引入的一个令人激动的特性， 这是由于在OLTP环境中LGWR写日志往往成为系统的主要性能瓶颈， 如果LGWR进程能像DBWR(DBW0~DBWn)那样多进程（LGNN）写出redo到LOGFILE那么就可能大幅释放OLTP的并发能力，增长Transcation系统的单位时间事务处理能力。这里在12.2版本中进行测试，确定_use_single_log_writer和_max_outstanding_log_writes参数对于SCALABLE LGWR特性的影响
数据库版本

SQL> select * from v$version;

BANNER                                                                                         CON_ID
------------------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production                        0
PL/SQL Release 12.2.0.0.3 - Production                                                              0
CORE    12.2.0.0.3      Production                                                                  0
TNS for Linux: Version 12.2.0.0.3 - Production                                                      0
NLSRTL Version 12.2.0.0.3 - Production                                                              0

_use_single_log_writer和_max_outstanding_log_writes默认值

SQL> /
Enter value for param: _use_single_log_writer
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_use_single_log_writer%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ -------------------------------------------
_use_single_log_writer                               ADAPTIVE                 Use a single process for redo log writing

SQL> /
Enter value for param: _max_outstanding_log_writes
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_max_outstanding_log_writes%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ----------------------------------------------
_max_outstanding_log_writes                          2                        Maximum number of outstanding redo log writes

lg进程数量
这里可以看出来，有一个lgwr进程，两个lg进程和_max_outstanding_log_writes参数配置匹配

[oracle@ora1221 ~]$ ps -ef|grep ora_lg
oracle    49790      1  0 10:32 ?        00:00:00 ora_lgwr_orcl12c2
oracle    49794      1  0 10:32 ?        00:00:00 ora_lg00_orcl12c2
oracle    49798      1  0 10:32 ?        00:00:00 ora_lg01_orcl12c2

修改_max_outstanding_log_writes参数
通过修改_max_outstanding_log_writes参数为4，发现lg进程数量也变为了4，证明_max_outstanding_log_writes进程决定lg进程数量

SQL> alter system set "_max_outstanding_log_writes"=4 ;     
alter system set "_max_outstanding_log_writes"=4
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set "_max_outstanding_log_writes"=4 scope=spfile;

System altered.

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

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
[oracle@ora1221 ~]$ ps -ef|grep lg
oracle    72339      1  0 13:45 ?        00:00:00 ora_lgwr_orcl12c2
oracle    72343      1  0 13:45 ?        00:00:00 ora_lg00_orcl12c2
oracle    72347      1  0 13:45 ?        00:00:00 ora_lg01_orcl12c2
oracle    72351      1  0 13:45 ?        00:00:00 ora_lg02_orcl12c2
oracle    72359      1  0 13:45 ?        00:00:00 ora_lg03_orcl12c2

修改_use_single_log_writer参数
通过测试_use_single_log_writer参数修改，我们可以确定_use_single_log_writer修改为true后，数据库恢复到12c之前的lgwr管理方式

[oracle@ora1221 ~]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Thu Aug 6 13:45:33 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL> alter system set "_use_single_log_writer"=1 ;     
alter system set "_use_single_log_writer"=1
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL>  alter system set "_use_single_log_writer"=1 scope=spfile;
 alter system set "_use_single_log_writer"=1 scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value 1 for parameter _use_single_log_writer, must be from
among ADAPTIVE, FALSE, TRUE


SQL> alter system set "_use_single_log_writer"=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
[oracle@ora1221 ~]$ ps -ef|grep lg
oracle    72702  71510  0 13:46 pts/0    00:00:00 grep lg
[oracle@ora1221 ~]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Thu Aug 6 13:46:50 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
Database opened.
SQL> exit   
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
[oracle@ora1221 ~]$ ps -ef|grep lg
oracle    72754      1  0 13:46 ?        00:00:00 ora_lgwr_orcl12c2
oracle    73008  71510  0 13:47 pts/0    00:00:00 grep lg

从这里可以确定_use_single_log_writer确定是否启用SCALABLE LGWR(多个lg子进程),_max_outstanding_log_writes确定lg进程个数

发表在 ORACLE 12C | 标签为 , , | 评论关闭

ORA-01555 ORA-600 kdiulk:kcbz_objdchk ORA-600 kdBlkCheckError等错误恢复

发表于 2016 年 6 月 1 日 由 惜分飞

数据库启动ORA-00704,0RA-00604,ORA-01555导致数据库无法启动

Tue May 31 17:32:42 2016
SMON: enabling cache recovery
SUCCESS: diskgroup RECOVERY was mounted
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0004.3af84bee):
select ctime, mtime, stime from obj$ where obj# = :1
Archived Log entry 5 added for thread 1 sequence 10 ID 0x86a261e7 dest 1:
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_12779.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_1592079335$" too small
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_12779.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_1592079335$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 12779): terminating the instance due to error 704

通过bbed修改事务之后启动数据库

Tue May 31 17:35:49 2016
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Updating character set in controlfile to AL32UTF8
Tue May 31 17:35:50 2016
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p021_13862.trc  (incident=166002):
ORA-00600: 内部错误代码, 参数: [kdiulk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
Tue May 31 17:35:50 2016
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p010_13818.trc  (incident=165914):
ORA-00600: 内部错误代码, 参数: [kdiulk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
Tue May 31 17:35:50 2016
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p004_13794.trc  (incident=165866):
ORA-00600: 内部错误代码, 参数: [kdiulk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
Tue May 31 17:35:50 2016
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_13822.trc  (incident=165922):
ORA-00600: 内部错误代码, 参数: [kdiulk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
Tue May 31 17:35:50 2016
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p016_13842.trc  (incident=165962):
ORA-00600: 内部错误代码, 参数: [kdiulk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []

ORA-600 [kdiulk:kcbz_objdchk] trace文件

*** SESSION ID:(3.5) 2016-05-31 17:35:50.068
OBJD MISMATCH typ=6, seg.obj=-2, diskobj=222225, dsflg=0, dsobj=285890, tid=285890, cls=1
ORA-00600: 内部错误代码, 参数: [kdiulk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []

Parallel Transaction recovery server caught exception 600
begin Parallel Recovery Context Dump
nsi: 48, nsactive: 48
, nirsi: 1, nidti: 1, ndt: 1, rescan: 0, ptrs: 48
[ktprsi] wdone: 50
[ktpritp 378651b8] ktprsi: 
37903b60 37903b78 37903b90 37903ba8 37903bc0 37903bd8 37903bf0 37903c08 37903c20 37903c38 37903c50 
37903c68 37903c80 37903c98 37903cb0 37903cc8 37903ce0 37903cf8 37903d10 37903d28 37903d40 37903d58 
37903d70 37903d88 37903da0 37903db8 37903dd0 37903de8 37903e00 37903e18 37903e30 37903e48 37903e60 
37903e78 37903e90 37903ea8 37903ec0 37903ed8 37903ef0 37903f08 37903f20 37903f38 37903f50 37903f68 
37903f80 37903f98 37903fb0 37903fc8 
[ktprht] nhb: 47, nfl: 20247, flg: 2

*** 2016-05-31 17:36:08.584
[ktprhb] nfl: 1, nelem: 97, flg: 0, sqn: 1
flist: 37698940 
nhe: [ktprhe 32] sqn: -1297235803
[kturur] uoff: -1797708320, sqn: 4
uba: 0x098004cd.07e4.0b
*-----------------------------
* Rec #0xb  slt: 0x07  objn: 123986(0x0001e452)  objd: 285891  tblspc: 10(0x0000000a)
*       Layer:  10 (Index)   opc: 22   rci 0x0a   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000

这里基本上可以确定是由于undo index中的dataobj#和block中的dataobj# 不匹配.在数据库undo回滚之时出现该错误.可以通过跳过undo回滚,然后重建对象

Tue May 31 17:36:06 2016
Simulated error on redo application.
Block recovery from logseq 12, block 959 to scn 20401094719
Recovery of Online Redo Log: Thread 1 Group 3 Seq 12 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_3.263.802446627
Block recovery completed at rba 12.1012.16, scn 4.3221225536
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Simulated error for redo application done.
Errors in file /opt/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p009_13814.trc  (incident=165906):
ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [26], [950417], [18025], [], [], [], [], [], [], [], []

这些错误是由于数据库block逻辑异常导致,错过参数含义
在10g中ORA-600 kddummy_blkchk 在11g中ORA-600 kdBlkCheckError

ARGUMENTS:
Arg [a] Absolute file number
Arg [b] Bock number
Arg 1 Internal error code returned from kcbchk() which indicates the problem encountered. 
See Note 46389.1 for details of block check codes.

根据QREF kddummy_blkchk / kdBlkCheckError Check Codes Listing (Full) (Doc ID 1264040.1)分析
这里的18025是代码的KCBTEMAP_EC_START + KTS4_EC_SBFREE部分异常，主要表现在Incorrect firstfree or nfree 可以通过设置一些参数进行屏蔽

在恢复过程中还有其他错误

ORA-600 encountered when generating server alert SMG-4128
ORA-00600: internal error code, arguments: [ktcpoptx:!cmt top lvl], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4406], [0x1026B65348], [0x000000000], [2], [6215], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ktcpoptx:!cmt top lvl], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORACLE Instance xifenfei (pid = 15) - Error 600 encountered while recovering transaction (10, 7) on object 123986.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kewrose_1], [600], 
  [ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.

通过整体分析错误主要是由于undo异常导致,通过设置_corrupted_rollback_segments设置db_block_checking等相关参数,清理SMON_SCN_TIME等操作数据库没有其他异常报错,让其通过逻辑方式重建库

发表在 Oracle备份恢复 | 标签为 , , , , , , , , , | 评论关闭