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

ORA-00600: internal error code, arguments: [kcvorl_2]

发表于 2018 年 7 月 4 日 由 惜分飞

一个朋友的数据库,由于redo损坏,经过一系列恢复,当我接手之时,已经是ORA-00283和ORA-16433错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORCL\SYSTEM01.DBF'

SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

通过重建控制文件继续恢复,遭遇ORA-00600 kcvorl_2错误

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

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1224738384 bytes
Database Buffers          905969664 bytes
Redo Buffers                5001216 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\ORCL\redo01.log'  SIZE 50M,
  9    GROUP 2 'D:\ORCL\redo02.log'  SIZE 50M,
 10    GROUP 3 'D:\ORCL\redo03.log'  SIZE 50M
 11  DATAFILE
 12  'D:\ORCL\SYSTEM01.DBF',
 13  'D:\ORCL\SYSAUX01.DBF',
 14  'D:\ORCL\UNDOTBS01.DBF',
 15  'D:\ORCL\USERS01.DBF',
 16  'D:\ORCL\XIFENFEI1',
 17  'D:\ORCL\XIFENFEI2'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcvorl_2], [0], [54271176], [0],[54271176], [], [], [], [], [], [], []

查询了mos发现该错误一般是由于Bug 20562968 – ORA-600 [KCVORL_2] DURING SWITCHOVER AFTER DOWNGRADING TO 11.2.0.1导致主库在switchover的时候可能会遇到该错误,还是第一次遇到数据库在resetlogs 打开的时候遭遇该错误.分析trace文件

Dump continued from file: e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_1892.trc
ORA-00600: internal error code, arguments: [kcvorl_2], [0], [54271176], [0], [54271176], [], [], [], [], [], [], []

========= Dump for incident 3738 (ORA 600 [kcvorl_2]) ========

*** 2018-07-03 16:35:41.404
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           009233DA2 000000000 000000000
                                                   000000000
ksedst()+69          CALL???  ksedst1()            000000002 000000000 006F605E0
                                                   000000000
dbkedDefDump()+4536  CALL???  ksedst()             000000287 000000000 000000000
                                                   000000000
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000468E71
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexProcessError()  CALL???  dbgexPhaseII()       00C9B0570 00C9BD448 000000E9A
+2563                                              000000002
dbgeExecuteForError  CALL???  dbgexProcessError()  00C9B0570 00C9B7540 000000001
()+65                                              000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  BC30C65D3 019606FF0 018881658
1726                          ()                   000502034
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()   0196075B0 00CB80040 000000258
75                                                 BA268928B586
kgeadse()+342        CALL???  dbkePostKGE_kgsf()   000000000 000000000
                                                   BA2688AA3890 7FFFB6A1728
kgerinv_internal()+  CALL???  kgeadse()            006F8C5A8 0196075B0 000000000
76                                                 0196072B0
kgerinv()+49         CALL???  kgerinv_internal()   018883960 0071C3480 000000000
                                                   000000000
kgeasnmierr()+64     CALL???  kgerinv()            0014B18A0 0071C3480 018881C20
                                                   000000000
kcvorl()+8957        CALL???  kgeasnmierr()        0071C4650 018882B00 000000000
                                                   000000004
adbdrv()+54131       CALL???  kcvorl()+428         000000008 018883E20 018887A88
                                                   078136DFB
opiexe()+20842       CALL???  adbdrv()             000000023 000000003
                                                   7FF00000102 000000000
opiosq0()+5129       CALL???  opiexe()+16981       000000004 000000000 01888A8E0
                                                   009361AB3
kpooprx()+357        CALL???  opiosq0()            000000003 00000000E 01888ABB0
                                                   0000000A4
kpoal8()+940         CALL???  kpooprx()            000020C80 008832840 00CBD1A48
                                                   000000001
opiodr()+1662        CALL???  kpoal8()             00000005E 00000001C 01888E120
                                                   00CA5BAA8
ttcpip()+1325        CALL???  opiodr()             480000000000005E
                                                   49004D000000001C 01888E120
                                                   4100200000000000
opitsk()+2040        CALL???  ttcpip()             0196212D0 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             00000001E 000000000 000000000
                                                   01888FA18
opiodr()+1662        CALL???  opiino()             00000003C 000000004 01888FAD0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 01888FAD0
                                                   6F5C3A6500000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 01888FAD0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              01888FB00 01888FBC4
                                                   100003000707E2 202020029001D
opimai()+191         CALL???  opimai_real()        00000001A 01888FC88 000000034
                                                   000000000
OracleThreadStart()  CALL???  opimai()             01888FE90 01211FF38 000000002
+724                                               01888FC88
0000000078D3B6DA     CALL???  OracleThreadStart()  01211FF38 000000000 000000000
                                                   01888FFA8
 

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

通过分析发现其中一个文件scn不对

SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,FUZZY,
  4  count(*) ROW_NUM
  5  FROM v$datafile_header
  6  GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME    FUZ    ROW_NUM
------- ------------------ ------------------ --- ----------
ONLINE            54271175 27-MAY-18          YES          1
ONLINE            54271179 25-JUN-18          YES          5

SQL> set numw 16
SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,last_change#,
  4  count(*) ROW_NUM
  5  FROM v$datafile
  6  GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME        LAST_CHANGE#          ROW_NUM
------- ------------------ ------------------ ---------------- ----------------
RECOVER           54271175 27-MAY-18                                          1
RECOVER           54271179 25-JUN-18                                          4
SYSTEM            54271179 25-JUN-18                                          1

SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
  2  to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
  3  to_char(checkpoint_change#,'9999999999999999') "SCN",
  4  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  5  from v$datafile_header;

             TS#            FILE# TABLESPACE_NAME                STATUS  CREATE_
TIME         SCN               RESETLOGS SCN     FUZ
---------------- ---------------- ------------------------------ ------- -------
------------ ----------------- ----------------- ---
               0                1 SYSTEM                         ONLINE  2010-03
-30 10:07:48          54271179          54271176 YES
               1                2 SYSAUX                         ONLINE  2010-03
-30 10:07:52          54271175          54271176 YES
               2                3 UNDOTBS1                       ONLINE  2010-03
-30 11:07:21          54271179          54271176 YES
               4                4 USERS                          ONLINE  2010-03
-30 10:08:04          54271179          54271176 YES
               6                5 XIFENFEI1                      ONLINE  2016-08
-02 18:52:23          54271179          54271176 YES
               7                6 XIFENFEI2                      ONLINE  2016-08
-02 18:52:31          54271179          54271176 YES

6 rows selected.

有SYSAUX数据文件的scn不对,通过bbed修改scn继续恢复成功

SQL> alter database open resetlogs;

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

truncate table 无论drop storage还是reuse storage不影响数据恢复

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

今天有朋友咨询,truncate table有drop storage和reuse storage方式,是否影响后续的数据恢复(在没有其他覆盖的情况下),我做了一个简单的测试证明,这些都不影响truncate table的数据库恢复
创建测试环境

SQL> create table t_xifenfei tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL> create table t_xifenfei2  tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL>  create table t_xifenfei3  tablespace USERNEW
  2  as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     86348

SQL>  select count(*) from t_xifenfei2;

  COUNT(*)
----------
     86349

SQL>  select count(*) from t_xifenfei3;

  COUNT(*)
----------
     86350

SQL> select object_id,data_object_id,object_name from dba_objects where object_name like 't_xifenfei%';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
     88205          88205 t_xifenfei
     88206          88206 t_xifenfei2
     88207          88207 t_xifenfei3

SQL> truncate table t_xifenfei;

Table truncated.

SQL> truncate table t_xifenfei2 drop storage;

Table truncated.

SQL> truncate table t_xifenfei3 reuse storage;

Table truncated.

使用dul进行恢复

  
DUL>  bootstrap;
DUL> scan database;

scanning database...
scanning database finished.

DUL> unload table sys.t_xifenfei object 88205;

Unloading table: t_xifenfei,object ID: 88205
Unloading segment,storage(Obj#=88205 DataObj#=88205 TS#=6 File#=5 Block#=1410 Cluster=0)
86348 rows unloaded

DUL> unload table sys.t_xifenfei2 object 88206;

Unloading table: t_xifenfei2,object ID: 88206
Unloading segment,storage(Obj#=88206 DataObj#=88206 TS#=6 File#=5 Block#=2690 Cluster=0)
86349 rows unloaded

DUL> unload table sys.t_xifenfei3 object 88207;

Unloading table: t_xifenfei3,object ID: 88207
Unloading segment,storage(Obj#=88207 DataObj#=88207 TS#=6 File#=5 Block#=3970 Cluster=0)
86350 rows unloaded

这里证明truncate table不管是drop storage还是reuse storage或者默认,在没有被覆盖的情况下，数据均完全恢复出来

发表在 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备份恢复 | 标签为 , , , , | 评论关闭