分类目录归档：Oracle

Oracle dul支持18c

发表于 2018 年 6 月 15 日 由 惜分飞

在以前的文章中已经写过oracle 原厂dul工具可以很好的支持oracle 11g,12c(Oracle dul支持Oracle 12.2(12c),dul 10支持oracle 11g r2),现在确认通过一些处理,dul也可以完美支持oracle 18c
数据库版本18c

[oracle@localhost dul]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Fri Jun 15 14:23:00 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select BANNER from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------
/u02/app/oracle/oradata/XFFDB/system01.dbf
/u02/app/oracle/oradata/XFFDB/sysaux01.dbf
/u02/app/oracle/oradata/XFFDB/undotbs01.dbf
/u02/app/oracle/oradata/XFFDB/users01.dbf

SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from sys.t_xifenfei;

COUNT(*)
-------------------------------------------------------------
72870

dul加载18c字典失败
DUL: FATAL Error: File OBJ.dat和DUL: Error: string2ub8错误导致obj和TAB字典加载失败

[oracle@localhost dul]$ ./dul

Data UnLoader: 11.2.0.6.1 - Internal Only - on Fri Jun 15 12:04:17 2018
with 64-bit io functions and the decompression option

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

 Strictly Oracle Internal Use Only


DUL: Warning: ulimit process stack size is only 33554432
Found db_id = 946715269
Found db_name = XFFDB
DUL> show datafiles;
ts# rf# start   blocks offs open  err file name
  0   1     0   110081    0    1    0 /u02/app/oracle/oradata/XFFDB/system01.dbf
  1   3     0    79361    0    1    0 /u02/app/oracle/oradata/XFFDB/sysaux01.dbf
  2   4     0     7681    0    1    0 /u02/app/oracle/oradata/XFFDB/undotbs01.dbf
  4   7     0      641    0    1    0 /u02/app/oracle/oradata/XFFDB/users01.dbf
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
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 11
 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$   72872 rows unloaded
. unloading table                      TAB$    2173 rows unloaded
. unloading table                      COL$  120489 rows unloaded
. unloading table                     USER$     123 rows unloaded
Reading USER.dat 123 entries loaded
Reading OBJ.dat
DUL: FATAL Error: File OBJ.dat, line 20174: identifier too long


[oracle@localhost dul]$ ./dul

Data UnLoader: 11.2.0.6.1 - Internal Only - on Fri Jun 15 13:46:51 2018
with 64-bit io functions and the decompression option

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

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
DUL: Warning: ulimit process stack size is only 33554432
Reading USER.dat 123 entries loaded
Reading OBJ.dat 35926 entries loaded and sorted 35926 entries
Reading TAB.dat
DUL: Error: string2ub8(618970019642690137449563136), Conversion to number (ub8) overflowed
DUL: Error: Number conversion error in file TAB.dat, line 22
DUL: Warning: Ignoring file TAB.dat cache
Reading COL.dat
DUL: Notice: Increased the size of DC_COLUMNS from 100000 to 132768 entries
 120489 entries loaded and sorted 120489 entries
Reading BOOTSTRAP.dat 60 entries loaded
Found db_id = 946715269
Found db_name = XFFDB

通过dul修复字典之后

[oracle@localhost dul]$ ./dul

Data UnLoader: 11.2.0.6.1 - Internal Only - on Fri Jun 15 14:12:52 2018
with 64-bit io functions and the decompression option

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

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
DUL: Warning: ulimit process stack size is only 33554432
Reading USER.dat 123 entries loaded
Reading OBJ.dat 35926 entries loaded and sorted 35926 entries
Reading TAB.dat 2155 entries loaded
Reading COL.dat
DUL: Notice: Increased the size of DC_COLUMNS from 100000 to 132768 entries
 120489 entries loaded and sorted 120489 entries
Reading TABPART.dat 299 entries loaded and sorted 299 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 216 entries loaded and sorted 216 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2845 entries loaded
Reading LOB.dat 665 entries loaded
Reading ICOL.dat 4911 entries loaded
Reading COLTYPE.dat 2971 entries loaded
Reading TYPE.dat 4031 entries loaded
Reading ATTRIBUTE.dat 15856 entries loaded
Reading COLLECTION.dat
DUL: Notice: Increased the size of DC_COLLECTIONS from 1024 to 8192 entries
 1454 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 18 entries loaded and sorted 18 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 42 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16
Found db_id = 946715269
Found db_name = XFFDB
DUL> unload table sys.t_xifenfei;
. unloading table                T_XIFENFEI   72870 rows unloaded
DUL>

由此可以看出来dul,可以比较好的支持oracle 18c数据库

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

12.2 standby 报ORA-01110

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

12.2备库报错

2018-06-13T19:29:00.302767+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/xifenfei/system01.dbf'
2018-06-13T19:29:00.829861+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 2: '/u01/app/oracle/oradata/xifenfei/rich101.dbf'
2018-06-13T19:29:00.930632+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/xifenfei/sysaux01.dbf'
2018-06-13T19:29:01.010230+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 4: '/u01/app/oracle/oradata/xifenfei/undotbs01.dbf'
2018-06-13T11:29:01.055975+00:00
Archived Log entry 5072 added for T-1.S-5020 ID 0x6a8e9d72 LAD:1
RFS[18]: Selected log 10 for T-1.S-5024 dbid 1787743346 branch 957530932
2018-06-13T19:29:01.091059+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 5: '/u01/app/oracle/oradata/xifenfei/richman01.dbf'
2018-06-13T19:29:01.172613+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 7: '/u01/app/oracle/oradata/xifenfei/users01.dbf'
2018-06-13T19:29:01.251906+08:00
Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc:
ORA-01110: data file 8: '/u01/app/oracle/oradata/xifenfei/r_index01.dbf'

trace文件

*** 2018-06-13T19:29:00.282836+08:00
*** SESSION ID:(2281.15120) 2018-06-13T19:29:00.282868+08:00
*** CLIENT ID:() 2018-06-13T19:29:00.282873+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2018-06-13T19:29:00.282878+08:00
*** MODULE NAME:(MMON_SLAVE) 2018-06-13T19:29:00.282883+08:00
*** ACTION NAME:(DDE async action) 2018-06-13T19:29:00.282888+08:00
*** CLIENT DRIVER:() 2018-06-13T19:29:00.282892+08:00

========= Dump for error ORA 312 (no incident) ========
----- DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
dbkh_reactive_run_check: BEGIN
dbkh_reactive_run_check:; incident_id=0
dbkh_run_check_internal: BEGIN; check_namep=DB Structure Integrity Check, run_namep=<null>
dbkh_run_check_internal: BEGIN; timeout=0
dbkh_run_check_internal: AFTER RUN CREATE; run_id=1841

*** 2018-06-13T19:29:00.302510+08:00
DDE previous invocation failed before phase II
DDE was called in a 'No Invocation Mode'
----- Start Diag Diagnostic Dump -----
Diagnostic dump is performed due to an error in the diagfw code during error handling.
Dump error and call stack for the diagnostic dump:

*** 2018-06-13T19:29:00.302576+08:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
ORA-01110: data file 1: '/u01/app/oracle/oradata/xifenfei/system01.dbf'
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+119         call     kgdsdst()            7FFF1A0D6C68 000000002
                                                   7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
                                                   000000000 000000082 ?
dbkedDefDump()+1200  call     ksedst()             000000000 000000002 ?
                                                   7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
ksedmp()+259         call     dbkedDefDump()       000000001 000000000
                                                   7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
dbgexExecuteIntDiag  call     ksedmp()             000000001 000000000 ?
Dmp()+1457                                         7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
dbgeBeginInvoke()+3  call     dbgexExecuteIntDiag  7F5A00000003 7F5A99B856C0
59                            Dmp()                7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
dbgePostErrorKGE()+  call     dbgeBeginInvoke()    7F5A99B856C0 7FFF1A0D7D20
1676                                               7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   7F5A99BC59A0 7F5A99AA0048
90                                                 000000456 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
kgeade()+432         call     dbkePostKGE_kgsf()   7F5A99BC59A0 7F5A99AA0048
                                                   000000456 7FFF1A0B87E8 ?
                                                   000000000 ? 000000082 ?
kgerelv()+144        call     kgeade()             7F5A99BC59A0 ? 7F5A99BC5BE8 ?
                                                   7F5A99AA0048 ? 000000456 ?
                                                   000000000 000000000
kgerev()+36          call     kgerelv()            7F5A99BC59A0 ? 7F5A99AA0048 ?
                                                   7F5A99AA0048 ? 000000456 ?
                                                   012E79CF4 ? 000000002 ?
kserec2()+185        call     kgerev()             7F5A99BC59A0 ? 7F5A99AA0048 ?
                                                   7F5A99AA0048 ? 000000456 ?
                                                   7FFF1A0D8000 000000002 ?
kcf_record_fn()+634  call     kserec2()            7F5A99BC59A0 ? 000000000
                                                   000000001 000000001 00000002C
                                                   141E0C518
kcvvra_dfh()+5278    call     kcf_record_fn()      000000001 151622BB8 000000000
                                                   7FFF1A0DA5D8 00000002C ?
                                                   141E0C518 ?
kcidr_file_header_c  call     kcvvra_dfh()         7FFF1A0DA460 ? 7FFF1A0D9FE8 ?
heck_common()+4669                                 000000000 ? 7FFF1A0D9398
                                                   7F5A94379000 ? 000000001 ?
kcidr_file_header_a  call     kcidr_file_header_c  7F5A99A9F7A0 7F5A94379000
ll_check_common()+2           heck_common()        000000001 000000000
259                                                7F5A94379000 ? 000000000
kcidr_cross_check()  call     kcidr_file_header_a  7F5A99A9F7A0 7FFF1A0DABE4
+566                          ll_check_common()    000000001 ? 000000000 ?
                                                   7F5A94379000 ? 000000000 ?
dbkird_cross_check(  call     kcidr_cross_check()  7F5A99A9F7A0 7FFF1A0DABE4 ?
)+557                                              7F5A99BC5BE8 000000000 ?
                                                   7F5A94379000 ? 000000000 ?
dbkh_run_check_inte  call     dbkird_cross_check(  7F5A99A9F7A0 7FFF1A0DABE4 ?
rnal()+2228                   )                    7F5A99BC5BE8 ? 000000000 ?
                                                   7F5A94379000 ? 000000000 ?
dbkh_reactive_run_c  call     dbkh_run_check_inte  7FFF1A0DB970 000000000
heck()+3011                   rnal()               000000002 000000000 000000000
                                                   000000000
dbgdaAsyncReceive()  call     dbkh_reactive_run_c  7F5A99B856C0 7FFF1A0DBC90
+279                          heck()               000000002 ? 000000000 ?
                                                   000000000 ? 000000000 ?
dbgea_exec_()+1739   call     dbgdaAsyncReceive()  7F5A99B856C0 0020C0029
                                                   7FFF1A0E7CA0 7FFF1A0E7D20
                                                   000000002 000000000 ?
dbgea_exec()+621     call     dbgea_exec_()        7F5A99B856C0 7F5A94984D18
                                                   0000000E8 000000000
                                                   000000002 ? 000000000 ?
dbkea_exec()+1718    call     dbgea_exec()         7F5A99B856C0 7F5A94984D18
                                                   0000000E8 000000000
                                                   000000002 ? 000000000 ?
dbkea_slave_exec()+  call     dbkea_exec()         7F5A99B856C0 ? 7F5A94984D18 ?
518                                                0000000E8 ? 000000000 ?
                                                   000000002 ? 000000000 ?
kebm_slave_cb()+64   call     dbkea_slave_exec()   1453D7248 7F5A94984D18 ?
                                                   0000000E8 ? 000000000 ?
                                                   000000002 ? 000000000 ?
kebm_slave_main()+7  call     kebm_slave_cb()      1453D7248 ? 7F5A94984D18 ?
72                                                 0000000E8 ? 000000000 ?
                                                   000000002 ? 000000000 ?
ksvrdp_int()+2010    call     kebm_slave_main()    1453D7248 ? 1453D7248
                                                   0000000E8 ? 000000000 ?
                                                   000000002 ? 000000000 ?
opirip()+602         call     ksvrdp_int()         000000000 ? 000000000 ?
                                                   0000000E8 ? 000000000 ?
                                                   000000002 ? 000000000 ?
opidrv()+602         call     opirip()             000000032 000000004
                                                   7FFF1A0EAD98 000000000 ?
                                                   000000002 ? 000000000 ?
sou2o()+145          call     opidrv()             000000032 000000004
                                                   7FFF1A0EAD98 000000000 ?
                                                   000000002 ? 000000000 ?
opimai_real()+202    call     sou2o()              7FFF1A0EAD70 000000032
                                                   000000004 7FFF1A0EAD98
                                                   000000002 ? 000000000 ?
ssthrdmain()+417     call     opimai_real()        000000000 7FFF1A0EB080
                                                   000000004 ? 7FFF1A0EAD98 ?
                                                   000000002 ? 000000000 ?
main()+262           call     ssthrdmain()         000000000 000000003
                                                   7FFF1A0EB080 000000001
                                                   000000000 000000000 ?
__libc_start_main()  call     main()               000000000 7FFF1A0EB2B8
+245                                               7FFF1A0EB080 ? 000000001 ?
                                                   000000000 ? 000000000 ?
_start()+41          call     __libc_start_main()  000D05240 000000001
                                                   7FFF1A0EB2B8 7F5A95015C05 ?
                                                   000000000 ? 000000000 ?


--------------------- Binary Stack Dump ---------------------

BUG:24844841 – PHSB:CDB M000 REPORTED ORA-1110 ON ADG WHEN A DATAFILE IS ADDED ON PRIMARY
@ The M000 messages is a false alarm as well. It is a false alarm by DRA check
@ that doesn’t consider standby media recovery properly. Adding a file happens
@ to trigger the timing for the false alarm.
@ One way to fix this is to skip file header check if standby recovery is
@ running inside kcidr_file_header_all_check_common.
M000进程检查数据库文件头信息,由于bug原因报ORA-01110错误.

处理建议
1.打上补丁24844841
2.19.1版本修复该问题
3.重启备库,启动mgr
4.暂时忽略该问题(目前没有发现影响数据库同步)
参考:ORA-01110 For All Files In Standby Database (Doc ID 2322290.1)

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

UNIFORM_LOG_TIMESTAMP_FORMAT—控制alert日志时间格式

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

细心的朋友可能注意到了在12.2中oracle alert日志的时间格式显示发生了改变,根据对oracle的理解,一般新特性有event或者参数进行控制的,通过分析确定是由于UNIFORM_LOG_TIMESTAMP_FORMAT进行控制的

2018-06-13T19:52:30.014659+08:00
RFS[21]: Selected log 12 for T-1.S-5589 dbid 1787743346 branch 957530932
2018-06-13T11:52:30.473314+00:00
Archived Log entry 5178 added for T-1.S-5582 ID 0x6a8e9d72 LAD:1

UNIFORM_LOG_TIMESTAMP_FORMAT修改为false

[oracle@xff ~]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 13 19:52:59 2018

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


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

SQL> show parameter UNIFORM_LOG_TIMESTAMP_FORMAT ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
uniform_log_timestamp_format         boolean     TRUE
SQL> alter system set uniform_log_timestamp_format=false;

System altered.

验证UNIFORM_LOG_TIMESTAMP_FORMAT=false效果

018-06-13T19:53:16.374197+08:00
RFS[19]: Selected log 14 for T-1.S-5120 dbid 1787743346 branch 957530932
2018-06-13T11:53:16.782388+00:00
Archived Log entry 5181 added for T-1.S-5117 ID 0x6a8e9d72 LAD:1
2018-06-13T19:53:19.797345+08:00
RFS[18]: Selected log 11 for T-1.S-5121 dbid 1787743346 branch 957530932
2018-06-13T11:53:20.268621+00:00
Archived Log entry 5182 added for T-1.S-5118 ID 0x6a8e9d72 LAD:1
Wed Jun 13 19:53:35 2018
ALTER SYSTEM SET uniform_log_timestamp_format=FALSE SCOPE=BOTH;
Wed Jun 13 11:53:37 2018
Wed Jun 13 19:53:37 2018
RFS[21]: Selected log 13 for T-1.S-5596 dbid 1787743346 branch 957530932
Wed Jun 13 11:53:38 2018
Archived Log entry 5183 added for T-1.S-5589 ID 0x6a8e9d72 LAD:1

UNIFORM_LOG_TIMESTAMP_FORMAT修改为true

SQL> alter system set uniform_log_timestamp_format=true;

System altered.

SQL>

验证UNIFORM_LOG_TIMESTAMP_FORMAT=true效果

Wed Jun 13 11:54:07 2018
Media Recovery Waiting for thread 1 sequence 5122 (in transit)
Wed Jun 13 11:54:07 2018
Recovery of Online Redo Log: Thread 1 Group 10 Seq 5122 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/xifenfei/std_redo10.log
2018-06-13T19:54:23.396030+08:00
ALTER SYSTEM SET uniform_log_timestamp_format=TRUE SCOPE=BOTH;
2018-06-13T11:54:24.597999+00:00
2018-06-13T19:54:24.615045+08:00
RFS[21]: Selected log 11 for T-1.S-5601 dbid 1787743346 branch 957530932
2018-06-13T11:54:25.054848+00:00
Archived Log entry 5187 added for T-1.S-5596 ID 0x6a8e9d72 LAD:1
发表在 ORACLE 12C | 标签为 | 评论关闭