标签归档：Oracle 恢复

DUL10直接支持ORACLE 8.0

发表于 2014 年 2 月 15 日由惜分飞

在以前的文章中,写过DUL挖ORACLE 8.0数据库,使用的是dul 8的版本,现在测试dul 10直接支持ORACLE 8.0数据库
数据库版本 ORACLE 8

SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production
5 rows selected.

dul版本 DUL 10

e:\dul10>dul

Data UnLoader: 10.2.0.5.26 - Internal Only - on Sat Feb 15 15:54:15 2014
with 64-bit io functions

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

 Strictly Oracle Internal Use Only


Found db_id = 1207542366
Found db_name = ORCL

DUL读取数据文件

DUL> show datafiles;
ts# rf# start   blocks offs open  err file name
UNK   1     0   102401    0    1    0 C:\Users\XIFENFEI\Desktop\temp\SYS1ORCL.ORA

DUL10参数配置

DUL> show parameter;
_SLPE_DEBUG               = FALSE
ALLOW_CHECKSUM_MISMATCH   = TRUE
ALLOW_DBA_MISMATCH        = TRUE
ALLOW_OTHER_OBJNO         = TRUE
ALLOW_TRAILER_MISMATCH    = TRUE
ASM_DO_HARD_CHECKS        = TRUE
AUTO_UPDATE_CHECKSUM      = TRUE
AUTO_UPDATE_TRAILER       = TRUE
BUFFER                    = 10485760
CF_FILES                  = 1022
CF_TABLESPACES            = 64
COMPATIBLE                = 8
CONTROL_FILE              = control.txt
DB_BLOCK_SIZE             = 2048
DB_NAME                   =
DB_ID                     = 0
DC_COLUMNS                = 100000
DC_OBJECTS                = 128k
DC_TABLES                 = 10000
DC_USERS                  = 1000
DC_SEGMENTS               = 10000
DC_EXTENTS                = 10000
DEFAULT_CHARACTER_SET     =
DEFAULT_NATIONAL_CHARACTER_SET =
EXPORT_MODE               = false
FEEDBACK                  = 1000
FILE                      =
FILE_SIZE_IN_MB           = 0
LDR_ENCLOSE_CHAR          = |
LDR_OUTPUT_IN_UTF8        = FALSE
LDR_PHYS_REC_SIZE         = 0
LOGFILE                   = dul.log
MAX_OPEN_FILES            = 8
OSD_MAX_THREADS           = 1055
OSD_BIG_ENDIAN_FLAG       = false
OSD_DBA_FILE_BITS         = 10
OSD_FILE_LEADER_SIZE      = 1
OSD_C_STRUCT_ALIGNMENT    = 32
OSD_WORD_SIZE             = 32
PARSE_HEX_ESCAPES         = FALSE
RESET_LOGFILE             = FALSE
SCAN_DATABASE_SCANS_LOB_SEGMENTS = TRUE
SCAN_STEP_SIZE            = 512
TRACE_FLAGS               = 0
UNEXP_MAX_ERRORS          = 1000
UNEXP_VERBOSE             = FALSE
USE_LOB_FILES             = TRUE
USE_SCANNED_EXTENT_MAP    = FALSE
VERIFY_NUMBER_PRECISION   = TRUE
WARN_RECREATE_FILES       = TRUE
WRITABLE_DATAFILES        = FALSE

DUL10加载ORACLE 8数据字典

DUL> bootstrap;
Probing file = 1, block = 527
  database version 8 bootstrap$ at file 1, block 352
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      52 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 52 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 8
 OBJ$: segobjno 18, file 1 block 167
 TAB$: segobjno 2, tabno 1, file 1  block 52
 COL$: segobjno 2, tabno 5, file 1  block 52
 USER$: segobjno 10, tabno 1, file 1  block 147
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$    3504 rows unloaded
. unloading table                      TAB$     434 rows unloaded
. unloading table                      COL$   16185 rows unloaded
. unloading table                     USER$      24 rows unloaded
Reading USER.dat 24 entries loaded
Reading OBJ.dat 3504 entries loaded and sorted 3504 entries
Reading TAB.dat 434 entries loaded
Reading COL.dat 16185 entries loaded and sorted 16185 entries
Reading BOOTSTRAP.dat 52 entries loaded

DUL: Error: No entry in OBJ$ for "TABCOMPART$" type = 2
DUL: Error: No base dict info for SYS.TABCOMPART$
DUL: Error: No entry in OBJ$ for "INDCOMPART$" type = 2
DUL: Error: No base dict info for SYS.INDCOMPART$
DUL: Error: No entry in OBJ$ for "TABSUBPART$" type = 2
DUL: Error: No base dict info for SYS.TABSUBPART$
DUL: Error: No entry in OBJ$ for "INDSUBPART$" type = 2
DUL: Error: No base dict info for SYS.INDSUBPART$
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 8
 OBJ$: segobjno 18, file 1 block 167
 TAB$: segobjno 2, tabno 1, file 1  block 52
 COL$: segobjno 2, tabno 5, file 1  block 52
 USER$: segobjno 10, tabno 1, file 1  block 147
 TABPART$: segobjno 180, file 1 block 1275
 INDPART$: segobjno 182, file 1 block 1285
 IND$: segobjno 2, tabno 3, file 1  block 52
 ICOL$: segobjno 2, tabno 4, file 1  block 52
 LOB$: segobjno 2, tabno 8, file 1  block 52
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
    3504 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
     434 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
   16185 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      24 rows unloaded
. unloading table                  TABPART$       0 rows unloaded
. unloading table                  INDPART$       0 rows unloaded
. unloading table                      IND$     525 rows unloaded
. unloading table                     ICOL$     899 rows unloaded
. unloading table                      LOB$      27 rows unloaded
Reading USER.dat 24 entries loaded
Reading OBJ.dat 3504 entries loaded and sorted 3504 entries
Reading TAB.dat 434 entries loaded
Reading COL.dat 16185 entries loaded and sorted 16185 entries
Reading TABPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 525 entries loaded
Reading LOB.dat 27 entries loaded
Reading ICOL.dat 899 entries loaded
Reading BOOTSTRAP.dat 52 entries loaded

DUL 10 unload ORACLE 8 TABLE

DUL> unload table ts$;
. unloading table                       TS$       4 rows unloaded
DUL>

通过测试,证明DUL10可以完美支持ORACLE 8.0数据库,在以后的低版本数据使用dul unload过程中,可以直接使用最新版本,而不用去到处寻找老版本dul

发表在非常规恢复 | 标签为 dul, ORACLE 8, Oracle 恢复, ORACLE恢复, ORACLE数据库恢复 | 评论关闭

ORACLE 8.0.5 ORA-01207故障恢复

发表于 2014 年 2 月 14 日由惜分飞

朋友和我说，他的数据库ORACLE 8.0.5出现ORA-01207,进行了尝试恢复但是别未成功,让我协助其完成恢复
数据库版本

SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production
5 rows selected.

open数据库报ORA-01207错误

SVRMGR> alter database open;
alter database open
*
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'D:\ORANT\DATABASE\SYS1ORCL.ORA'
ORA-01207: file is more recent than controlfile - old controlfile

出现该错误的原因是因为控制文件里面的scn或者checkpoint_time>数据文件中的对应值,从而出现该错误,解决方法重建控制文件或者执行recover using backup controlfile 之类命令

重建控制文件,并open报ORA-600[4147]

SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area                         15077376 bytes
Fixed Size                                          49152 bytes
Variable Size                                    12906496 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        73728 bytes
SVRMGR> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
     2>     MAXLOGFILES 32
     3>     MAXLOGMEMBERS 2
     4>     MAXDATAFILES 32
     5>     MAXINSTANCES 16
     6>     MAXLOGHISTORY 3260
     7> LOGFILE
     8>   GROUP 1 'D:\ORANT\DATABASE\LOG4ORCL.ORA'  SIZE 1M,
     9>   GROUP 2 'D:\ORANT\DATABASE\LOG3ORCL.ORA'  SIZE 1M,
    10>   GROUP 3 'D:\ORANT\DATABASE\LOG2ORCL.ORA'  SIZE 1M,
    11>   GROUP 4 'D:\ORANT\DATABASE\LOG1ORCL.ORA'  SIZE 1M
    12> DATAFILE
    13>   'D:\ORANT\DATABASE\SYS1ORCL.ORA',
    14>   'D:\ORANT\DATABASE\USR1ORCL.ORA',
    15>   'D:\ORANT\DATABASE\RBS1ORCL.ORA',
    16>   'D:\ORANT\DATABASE\TMP1ORCL.ORA'
    17> ;
Statement processed.
SVRMGR> recover database using backup controlfile;
ORA-00279: change 46960617 generated at 01/31/14 18:51:49 needed for thread 1
ORA-00289: suggestion : D:\ORANT\RDBMS80\ARC12900.1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORANT\DATABASE\LOG3ORCL.ORA
Log applied.
Media recovery complete.
SVRMGR> alter database open;
alter database open
*
ORA-00600: internal error code, arguments: [4147], [16], [1], [], [], [], [], []

The ORA-600[4147] basically indicates some kind of corruption with the UNDO (rollback segment)block, most probably due to a lost write to the rollback segment.
ORA-600[4147]是因为回滚段坏块导致(具体是因为undoblock的scn无效),解决方法是用dul找出来回滚段,并屏蔽之

继续恢复报ORA-00600[3668]

SVRMGR> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area                         15077376 bytes
Fixed Size                                          49152 bytes
Variable Size                                    12906496 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        73728 bytes
Database mounted.
ORA-00600: internal error code, arguments: [3668], [1], [2], [17232], [17232], [4], [], []

ORA-00600[3668]是因为在ORACLE 7.0到9.2的版本中The FIRST time an attempt has been made to start an instance after a CREATE CONTROLFILE command has been issued.
At least one data file needs MEDIA RECOVERY.在9.2.0.x及其以后版本报:ORA-1113: file needs media recovery.
通过重建控制文件,执行recover database,再open数据库恢复成功

发表在 Oracle备份恢复 | 标签为 8.0.5, ORA-01207, ORA-600 3668, ORA-600 4147, Oracle 恢复, ORACLE恢复, ORACLE数据库恢复 | 2 条评论

使用DUL挖数据文件恢复非数据外对象方法

发表于 2014 年 1 月 18 日由惜分飞

在dul进行数据库挖掘恢复的时候，我们可以通过unload table/user等方式来恢复表数据，但是对于一些view,index,trigger,source,seq,Dblink等不能直接通过unload来实现,但是可以通过挖基表来实现相关操作,这里提供了一些处理思路,在实际操作中根据需求,分析数据字典灵活应用
一．view
导出对象

USER$
OBJ$
COL$
VIEW$

执行sql语句

Set pages 10000
Set long 1000
Spool d:\create_view.sql
select 
  'CREATE OR REPLACE VIEW '||O.NAME||' ('||
   replace(c.cols,',',','||chr(10))||')'||CHR(10)||
  'as'||chr(10), v.text
from
user$ u, obj$ o, view$ v,
( SELECT COL.OBJ#, COL.COLS
  FROM
  (SELECT 
    OBJ#, COL#, substr(SYS_CONNECT_BY_PATH(NAME,','),2) COLS
  FROM COL$
  WHERE COL# > 0
  START WITH COL# = 1
  CONNECT BY PRIOR OBJ# = OBJ# AND PRIOR COL# = COL# - 1 ) COL,
  (SELECT OBJ#, COUNT(*) COLCNT FROM COL$ 
  WHERE COL# > 0 GROUP BY OBJ#) CN
  WHERE COL.OBJ# = CN.OBJ# AND COL.COL# = CN.COLCNT
) C
where u.user#=o.owner# and o.obj# = c.obj#
  and v.obj# = o.obj# and u.name=upper('&username');

说明
1）分布执行，不能放置一个脚本文件中执行
2）每条as后面的select语句可能需要重新格式化
3） Create view 语句最后需要增加”;”

二．source
导出对象

USER$
SOURCE$
OBJ$

执行sql语句

Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_source.sql
SELECT DECODE(S.LINE,1,'CREATE OR REPLACE ','')||SOURCE SOURCE
FROM 
  USER$ U, OBJ$  O, SOURCE$ S
WHERE 
  U.USER# = O.OWNER# AND 
  O.OBJ# = S.OBJ# AND
  U.NAME = UPPER('&username')
  -- AND O.NAME = UPPER('&SOURCE_NAME')
ORDER BY S.OBJ#, S.LINE;

说明
1）注意SOURCE中的用户名,如果导入不是相同用户,需要修改该脚本用户名
2）修改完用户名后,直接执行生成脚本即可

三．Index
导出对象

USER$
OBJ$
COL$
IND$
ICOL$

执行sql

Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_index.sql
SELECT 
  'CREATE '||decode(bitand(IDX.property, 1), 1, 'UNIQUE', '')||
  ' INDEX '||I.NAME||' ON '||T.NAME||'('||IDX.PATH||');' INDEX_DDL
FROM 
  USER$ U, OBJ$  T, OBJ$ I, 
  (
    select I.PROPERTY, I.BO#, I.OBJ#, C.POS#,
            SUBSTR(sys_connect_by_path(CN.NAME,','),2) path
    from IND$ I, ICOL$ C, COL$ CN
    WHERE I.OBJ# = C.OBJ# AND I.BO# = C.BO#
      AND I.BO# = CN.OBJ# AND C.COL# = CN.INTCOL#
    start with C.POS#=1 
    connect by PRIOR I.OBJ# = I.OBJ# 
            AND prior C.POS# = C.POS# - 1 ) IDX,
  (SELECT I.BO#, I.OBJ#, COUNT(*) COLCNT 
    FROM ICOL$ I GROUP BY I.BO#, I.OBJ#) IDXC
WHERE 
  U.USER# = T.OWNER# AND 
  IDX.BO# = T.OBJ# AND
  IDX.OBJ# = I.OBJ# AND
  IDX.BO# =  IDXC.BO# AND
  IDX.OBJ# = IDXC.OBJ# AND
  IDX.POS# = IDXC.COLCNT AND
  U.NAME = upper('&username')
ORDER BY T.NAME, I.NAME;

说明
1）因为SYS_CONNECT_BY_PATH所以需要10g及其以上版本
2） SQL中没有分区唯一性索引
3）注意检查sql是否因为行长度不够导致异常

四．Sequence
导出对象

USER$
OBJ$
SEQ$

执行sql语句

Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_sequence.sql
SELECT 
  'CREATE SEQUENCE '|| SEQ_NAME || 
  ' MINVALUE '||minval ||
  ' MAXVALUE '||MAXVAL ||
  ' START WITH '||LASTVAL ||
  ' ' || CYC || ' ' || ORD ||
  DECODE(SIGN(CACHE), 1,' CACHE '|| CACHE, 'NOCACHE') ||
  ';' SEQ_DDL
from
  (select u.name OWNER, o.name SEQ_NAME,
      s.minvalue MINVAL, s.maxvalue MAXVAL, 
      s.increment$ INC,
      decode (s.cycle#, 0, 'NOCYCLE', 1, 'CYCLE ') CYC,
      decode (s.order$, 0, 'NOORDER', 1, 'ORDER') ORD,
      s.cache, s.highwater LASTVAL
  from seq$ s, obj$ o, user$ u
  where u.user# = o.owner#
    and o.obj# = s.obj# 
and u.name=upper('&username'));

五．TRIGGER
导出对象

OBJ$
USER$
TRIGGER$

执行sql语句

Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_trigger.sql
select
   'CREATE OR REPLACE TRIGGER '|| trigger_name || chr(10)||
   decode( substr( trigger_type, 1, 1 ), 
           'A', 'AFTER ', 'B', 'BEFORE ', 'I', 
           'INSTEAD OF ' ) ||
   triggering_event || ' ON ' || table_owner || '.' ||
   table_name || chr(10) || REF_CLAUSE || chr(10) ||
   decode( instr( trigger_type, 'EACH ROW' ), 0, null,
           'FOR EACH ROW' ), trigger_body
from  (
   select trigusr.name owner, trigobj.name trigger_name,
      decode(t.type#, 0, 'BEFORE STATEMENT',
           1, 'BEFORE EACH ROW',   2, 'AFTER STATEMENT',
           3, 'AFTER EACH ROW',    4, 'INSTEAD OF',
           'UNDEFINED') trigger_type,
   decode(t.insert$*100 + t.update$*10 + t.delete$,
           100, 'INSERT', 010, 'UPDATE', 001, 'DELETE',
           110, 'INSERT OR UPDATE', 101, 'INSERT OR DELETE',
           011, 'UPDATE OR DELETE',
           111, 'INSERT OR UPDATE OR DELETE', 
           'ERROR') triggering_event,
   tabusr.name table_owner, tabobj.name table_name, 
   'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname REF_CLAUSE,
   t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR') STATUS,
   t.definition , t.action# trigger_body
   from obj$ trigobj, obj$ tabobj, trigger$ t,
        user$ tabusr, user$ trigusr
   where (trigobj.obj#   = t.obj# and
       tabobj.obj#    = t.baseobject and
       tabobj.owner#  = tabusr.user# and
       trigobj.owner# = trigusr.user# and
       bitand(t.property, 63)     < 8 ))
where table_owner=upper('&username')
order by owner, trigger_name;

六. Dblink
导出对象

Sys.link$
sys.user$

执行查询sql

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

发表在非常规恢复 | 标签为 dul, dul恢复plsql, Oracle 恢复, ORACLE恢复, ORACLE数据库恢复, 非常规恢复 | 评论关闭

17813235971

QQ 咨询

标签归档：Oracle 恢复

DUL10直接支持ORACLE 8.0

ORACLE 8.0.5 ORA-01207故障恢复

使用DUL挖数据文件恢复非数据外对象方法

文章分类

最近发表

标签归档：Oracle 恢复

DUL10直接支持ORACLE 8.0

ORACLE 8.0.5 ORA-01207故障恢复

使用DUL挖数据文件恢复非数据外对象方法

标签云

文章分类

最近发表