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

DUL挖ORACLE 8.0数据库

发表于 2012 年 7 月 27 日 由 惜分飞

老古董的东西,在你一不小心的时候就可能遇到,测试了dul成功挖ORACLE 8.0数据库
创建模拟环境

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.

SVRMGR> create table t_xifenfei
     2> as
     3> select * from dba_tables;
Statement processed.
SVRMGR> select count(*) from t_xifenfei;
COUNT(*)
----------
       183
1 row selected.

SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

dul参数配置

    osd_big_endian_flag=false
    osd_dba_file_bits=10
    osd_c_struct_alignment=32
    osd_file_leader_size=1
    dc_columns=2000000
    dc_tables=10000
    dc_objects=1000000
    dc_users=400
    dc_segments=100000
    control_file = control.txt
    db_block_size=2048
    export_mode=true
    compatible=8
    file = dump

dul挖数据

C:\dul>dul8.exe dictv8.ddl

Data UnLoader 8.0.6.8 - Internal Use Only - on Thu Jul 26 20:08:33 2012
with 64-bit io functions

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


DUL: Warning: Recreating file "dul.log"
Parameter altered
Parameter altered
Parameter altered
Parameter altered
. unloading table                      OBJ$    2681 rows unloaded
. unloading table                      TAB$     187 rows unloaded
. unloading table                      COL$   11181 rows unloaded
. unloading table                     USER$      23 rows unloaded
. unloading table                  TABPART$       0 rows unloaded
. unloading table                      IND$     221 rows unloaded
. unloading table                     ICOL$     419 rows unloaded
. unloading table                      LOB$      13 rows unloaded

Life is DUL without it

C:\dul>dul8.exe

Data UnLoader 8.0.6.8 - Internal Use Only - on Thu Jul 26 20:13:44 2012
with 64-bit io functions

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


DUL: Warning: Recreating file "dul.log"
Loaded 23 entries from USER.dat
Loaded 2681 entries from OBJ.dat
Loaded 188 entries from TAB.dat
Loaded 11218 entries from COL.dat
Loaded 0 entries from TABPART.dat
Loaded 221 entries from IND.dat
Loaded 13 entries from LOB.dat
Loaded 419 entries from ICOL.dat
DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI     183 rows unloaded

启动8.0数据库

C:\oracle\ora80\BIN>SVRMGR30.EXE

Oracle Server Manager Release 3.0.5.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SVRMGR> connect internal/xifenfei
Connected.
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.
Database opened.

imp导入数据

C:\dul>imp chf/xifenfei file=dump001.dmp full=y

Import: Release 8.0.5.0.0 - Production on 星期二 11月 1 23:34:36 2011

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


连接到: Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

经由常规路径导出由EXPORT:V07.00.07创建的文件

警告: 此对象由 Bernard's DUL 导出, 而不是当前用户

. 正在将Bernard's DUL的对象导入到 CHF
. . 正在导入表                    "T_XIFENFEI"        183行被导入
成功终止导入，但出现警告。

测试恢复数据

SVRMGR> connect chf/xifenfei
Connected.
SVRMGR> select count(*) from t_xifenfei;
COUNT(*)
----------
       183
1 row selected.

再次证明了dul确实异常的强大,第三方的工具在某些方面确实不是它的对手

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

记录一次AIX 4.3.0+ORACLE 8.0.5恢复过程

发表于 2012 年 7 月 23 日 由 惜分飞

最近接手一个AIX下面ORACLE 8.0.5恢复需求.这个需求要从2个月前说起.2个月前有客户提出需求,帮他们恢复8.0.5的库(该库被9.2.0.4的ORACLE强制拉过,但是未成功).当我准备去恢复的时候,客户和我说数据库的存储找不到了,暂时不用处理.本来这个事情到此也就算结束了,最多算一个笑话(找人数据库恢复,发现数据库丢了).前几天又接到关于这个恢复的请求,说他们还有磁带的备份(备份方式:begin tablespace backup+cp+archivelog),让我去协助恢复.按照客户的描述,他们购买了国外的读磁带机器可以读取磁带到硬盘,他们解压好数据文件,然后我去恢复,而且数据文件,控制文件,归档日志都存在.我当时认为这个是一个简单的恢复,顺利的话,一个小时就可以搞定.这里告诉我们:哪怕是N久的备份,也可能是最后的救命数据(2004年的磁带备份),一定要做好备份
今天到达现场,客户第一句话:文件没有解压,第二句话:归档日志没有恢复出来.听到这里,我有点感觉情况很不妙.
详细一问:文件没有解压是因为AIX 4.3.0默认的是文件系统格式是jfs,最大支持lv的大小为20g(估计和客户参数有关系),现在如果解压需要分到多个目录中(数据库原始运行就是按照该模式进行的,如:oradata001,oradata002等分别放几个数据文件),需要我去给他们做规划,如果存放这些文件.大脑一晕,dba原来真的什么活都要干啊.本身就对AIX不熟悉,还要去想办法处理这些问题,而且是从来没有见过的AIX 4.3.0.最后通过我和客户的一起努力终于解决了这个问题:使用其他技巧在AIX 4.3中建立jfs2文件系统(先划分小的LV,使用jfs系统,然后修改系统为jfs2,然后增加lv大小),解决以前jfs文件系统限制,解压的时候需要规划文件目录的体力活.这个问题告诉我们:有时候解决问题需要学会变通
归档日志没有恢复出来的原因:因为连续几天的恢复,加上客户本身工作繁忙,可能实在是太累,在最后一盘磁带的恢复的时候(一盘磁带15小时,一共4盘),客户敲错了命令tar -xvf输入成了-cvf,使得磁盘头被覆盖,磁带原则上报废,从而使得归档日志无法恢复出来.这一点点的事故告诉我们:越疲劳越容易出错,越到最后越容易出错,一定要小心谨慎
到这一步,没有解压(已经解决文件系统问题,接下来的解压问题不大),没有归档(修改scn原则上可以解决),这些东西总的来说问题都不大,当我安装好AIX FOR ORACLE 8.0.5,启动数据库到mount状态,核对恢复出来的数据文件和控制文件中的数据文件的时候,发现少了好几个,这下不能容忍了(能够open库,但是可能丢失需要数据,这个太不划算[因为用户还有该备份的前几天的备份]),寻找出现数据文件从磁带中丢失原因:1.因为jfs文件系统限制,不停的mv到其他目录导致丢失.2.uncompress解压丢失.3.最后一盘磁带损坏导致丢失.一切原因都是浮云,解决了jfs2文件系统,客户根据当前的情况,决定使用其他的备份再次从磁带中导出,然后进行恢复
AIX 4.3安装ORACLE 8.0.5

--检查内存
 lsattr -El sys0 -a realmem

--检查交换分区
 lsps -a

--检查临时目录
 df -k /tmp

--检查操作系统位数
getconf HARDWARE_BITMODE
bootinfo -y

--检查操作系统版本号
 oslevel -r

--检查软件包
 lslpp -l bos.adt.base, bos.adt.libm

--检查补丁包
 instfix -i | grep IX71948  

--升级aix
smit install_latest 
smit update_all

增加用户： 
useradd   oracle

增加组： 
mkgrp   dba 

更改用户所属组： 
usermod   -g  dba oracle 

更改用户密码： 
passwd   oracle 
pwdadm   oracle


--关于用户
smit mkuser	建立用户
smit lsuser	列出所有用户的属性
lsuser ALL
smit chuser	改变用户属性
rmuser -p *	删除用户*
smit rmuser	只删除用户，但是不删除所属目录，等于rmuser
smit passwd	修改密码
smit lockuser	给用户加锁
--关于用户组
smit mkgroup	建立新组
smit lsgroup	显示所有组的属性
smit chgroup	修改组的属性
smit rmgroup	删除*组
rmgroup *

配置shell limits( smit chuser)
  soft FILE size -1
  soft CPU time -1
  soft DATA segment -1
  soft STACK size -1

新建目录： 
mkdir   /u01

更改目录属主： 
chown   oracle   /u01 

更改目录所属组： 
chgrp   dba   /u01 


vi   /home/oracle/.profile 
export   LINK_CNTRL=L_PTHREADS_D7 
export   NLS_LANG=american_america.zhs16cgb231280
export	 ORACLE_OWNER=oracle
export   ORACLE_TERM=vt100
#export   ORACLE_TERM=xterm
export   ORACLE_BASE=/oracle
export   ORACLE_HOME=$ORACLE_BASE/product/8.0.5 
export   ORACLE_SID=ora8 
export   LD_LIBRARY_PATH=$/ORACLE_HOME/lib:$LD_LIBRARY_PATH
export   LIBPATH=$ORACLE_HOME/lib:$LIBPATH
export   ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data 
export   PATH=$ORACLE_HOME/bin:$PATH 
export   TMPDIR=/tmp 
export   DISPLAY=172.100.1.2:0.0
set -o vi
umask 022

--Mount产品光盘 
$   su   root 
-查看光驱
$ lsdev -Cc cdrom
#   mkdir   /cdrom 
#   chmod   777   /cdrom 
#   /etc/mount   -rv   cdrfs   /dev/cd0   /cdrom 
#   exit

--运行rootpre.sh脚本。 
$   su   root 
#   cd   /cdrom/orainst 
#   ./rootpre.sh 
#   exit  

--运行安装程序
$   cd   /cdrom/orainst 
-图形
$   ./orainst   /m 
-字符
$   ./orainst   /c 

# cd $ORACLE_HOME/orainst
# ./root.sh

建立密码文件

cd $ORACLE_HOME/dbs
orapwd file=orapw$ORACLE_SID password=oracle

创建pfile文件

vi $ORACLE_HOME/dbs/init.ora
db_name=ORCL
db_files = 5000							      
control_files = /oradata/ctl1ORCL.ora
db_file_multiblock_read_count =  8 
db_block_buffers =  100000			      
shared_pool_size =  115343360			      
log_checkpoint_interval = 10000
processes =  590					      
parallel_max_servers = 5                                       
log_buffer =  163840 				      
sequence_cache_entries =  100 	      
sequence_cache_hash_buckets =  90     
max_dump_file_size = 102400   
global_names = TRUE
background_dump_dest=/oracle/trace
user_dump_dest=/oracle/trace
db_block_size = 4196
remote_login_passwordfile = shared
text_enable = TRUE
job_queue_processes = 2
job_queue_interval = 10
job_queue_keep_connections = false
distributed_lock_timeout = 300
distributed_transactions = 5
open_links = 4

操作8.0.5数据库

--结果测试与win,linux/unix使用svrmgrl命令
C:\oracle\ora80\BIN>SVRMGR30.EXE

Oracle Server Manager Release 3.0.5.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SVRMGR> connect system/manager
Connected.
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.

SVRMGR> connect internal/oracle
Connected.
SVRMGR> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            %RDBMS80%\
Oldest online log sequence     3
Current log sequence           6

SVRMGR> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount;
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.
SVRMGR> alter database archivelog;
Statement processed.
SVRMGR> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\ora80\RDBMS80\
Oldest online log sequence     3
Next log sequence to archive   6
Current log sequence           6
发表在 Oracle备份恢复 | 一条评论

using backup controlfile 两种使用情况区别

发表于 2012 年 7 月 15 日 由 惜分飞

使用备份控制文件和重建控制文件恢复,都需要使用到using backup controlfile命令,但是两种情况下却有着本质的区别
试验准备条件

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 to_char(sysdate,'yyyy-mm-dd')"www.xifenfei.com" from dual;

www.xifenfei.com
--------------------
2012-07-13

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$controlfile;

NAME
----------------------------------------------------
/u01/oracle/oradata/ora11g/control01.ctl

SQL> !cp /u01/oracle/oradata/ora11g/control01.ctl /tmp/xff.ctl

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system switch logfile;     

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort;    
ORACLE instance shut down.
SQL> !rm /u01/oracle/oradata/ora11g/control01.ctl

SQL> ! cp /tmp/xff.ctl /u01/oracle/oradata/ora11g/control01.ctl

SQL> startup
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             432014844 bytes
Database Buffers           83886080 bytes
Redo Buffers                5861376 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file

相关说明：
1.通过resetlogs使得试验更加清晰
2.通过多次的checkpoint实现增加scn,switch logfile实现日志组切换
3.通过模拟备份控制文件恢复

查询相关SCN

SQL> set linesize 150
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  3  from v$datafile_header;

     FILE# SCN                                RESETLOGS SCN                      FUZZY
---------- ---------------------------------- ---------------------------------- ------
         1           2118981                            2118577                  YES
         2           2118981                            2118577                  YES
         3           2118981                            2118577                  YES
         4           2118981                            2118577                  YES
         6           2118981                            2118577                  YES

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          2118580
         2          2118580
         3          2118580
         4          2118580
         6          2118580

SQL> select CONTROLFILE_CHANGE#   from v$database;

CONTROLFILE_CHANGE#
-------------------
            2118713

做关于控制文件和数据文件dump

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump file_hdrs 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_593.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 03:05:48 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid;
Statement processed.
SQL>  oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_775.trc

分析file_hdrs 3 dump文件

--datafile 1的datafile header信息
Tablespace #0 - SYSTEM  rel_fn:1 
Creation   at   scn: 0x0000.00000007 09/18/2011 17:33:47
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2eff82e3 scn: 0x0000.002053b1
 prev reset logs count:0x2e9e8451 scn: 0x0000.0016eaab
 recovered at 07/13/2012 02:56:18
 status:0x2004 root dba:0x00400208 chkpt cnt: 760 ctl cnt:759
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00205545 07/13/2012 03:01:42

--datafile 1的控制文件中信息
DATA FILE #1: 
  name #7: /u01/oracle/oradata/ora11g/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:752 scn: 0x0000.002053b4 07/13/2012 02:59:18
 Stop scn: 0xffff.ffffffff 07/13/2012 02:58:43
 Creation Checkpointed at scn:  0x0000.00000007 09/18/2011 17:33:47

通过这里可以看出来:
datafile header的Checkpointed scn(00205545)>controfile datafile的Checkpoint scn(002053b4)
datafile header的checkpiont count(760)>controfile datafile的checkpiont count(752)
所以在数据库open的时候会报ORA-01207错误

尝试恢复数据库

SQL> recover database using backup controlfile;
ORA-00279: change 2118713 generated at 07/13/2012 02:58:43 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf
ORA-00280: change 2118713 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> 
SQL> select to_char(2118713,'xxxxxxx') from dual;

TO_CHAR(2118713,
----------------
  205439

分析controlf 3 dump文件

***************************************************************************
DATABASE ENTRY
***************************************************************************
 (size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 1, numrecs = 1)
 03/12/2012 22:17:06
 DB Name "ORA11G"
 Database flags = 0x00404000 0x00001000
 Controlfile Creation Timestamp  03/12/2012 22:17:07
 Incmplt recovery scn: 0x0000.00000000
 Resetlogs scn: 0x0000.002053b1 Resetlogs Timestamp  07/13/2012 02:58:43
 Prior resetlogs scn: 0x0000.0016eaab Prior resetlogs Timestamp  05/01/2012 13:14:57
 Redo Version: compatible=0xb200000
 #Data files = 5, #Online files = 5
 Database checkpoint: Thread=1 scn: 0x0000.002053b4
 Threads: #Enabled=1, #Open=1, Head=1, Tail=1
 Max log members = 3, Max data members = 1
 Arch list: Head=0, Tail=0, Force scn: 0x0000.00000000scn: 0x0000.00000000
 Activation ID: 4184707968
 Controlfile Checkpointed at scn:  0x0000.00205439 07/13/2012 02:59:25 <==控制文件checkpiont,控制文件每3秒的一次checkpiont
 thread:0 rba:(0x0.0.0)

通过recover的提示和对于controlf 3 dump文件的分析,可以确定数据库使用备份控制文件恢复,需要改控制文件备份之时开始的所有归档日志

查询数据库当前redo情况

SQL> select member from v$logfile;

MEMBER
-------------------------------------------------------------
/u01/oracle/oradata/ora11g/redo03.log
/u01/oracle/oradata/ora11g/redo02.log
/u01/oracle/oradata/ora11g/redo01.log

SQL> select SEQUENCE#,STATUS,FIRST_CHANGE# ,NEXT_CHANGE# from v$log; 

 SEQUENCE# STATUS                           FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------- ------------- ------------
         1 CURRENT                                2118577   2.8147E+14
         0 UNUSED                                       0            0
         0 UNUSED                                       0            0

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oracle/oradata/archivelog/ora11g
Oldest online log sequence     1
Current log sequence           1

SQL> alter system dump logfile '/u01/oracle/oradata/ora11g/redo03.log';

System altered.

SQL> alter system dump logfile '/u01/oracle/oradata/ora11g/redo02.log';

System altered.

SQL> alter system dump logfile '/u01/oracle/oradata/ora11g/redo01.log';

System altered.

因为数据库处于非归档模式,而得到的redo信息主要都是来自控制文件,所以只能通过dump redo来分析当前redo的情况

分析redo log dump

DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo03.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
	Compatibility Vsn = 186646528=0xb200000
	Db ID=4173966754=0xf8c9ada2, Db Name='ORA11G'
	Activation ID=4184707968=0xf96d9380
	Control Seq=7660=0x1dec, File size=30720=0x7800
	File Number=3, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000003, SCN 0x00000020553d-0x000000205540"
 thread: 1 nab: 0x2 seq: 0x00000003 hws: 0x2 eot: 0 dis: 0
 resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)
 prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)
 Low  scn: 0x0000.0020553d (2118973) 07/13/2012 03:01:34
 Next scn: 0x0000.00205540 (2118976) 07/13/2012 03:01:35
 Enabled scn: 0x0000.002053b1 (2118577) 07/13/2012 02:58:43
 Thread closed scn: 0x0000.0020553d (2118973) 07/13/2012 03:01:34
 Disk cksum: 0xa716 Calc cksum: 0xa716


DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo02.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
	Compatibility Vsn = 186646528=0xb200000
	Db ID=4173966754=0xf8c9ada2, Db Name='ORA11G'
	Activation ID=4184707968=0xf96d9380
	Control Seq=7663=0x1def, File size=30720=0x7800
	File Number=2, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000005, SCN 0x000000205543-0xffffffffffff"
 thread: 1 nab: 0xffffffff seq: 0x00000005 hws: 0x1 eot: 1 dis: 0
 resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)
 prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)
 Low  scn: 0x0000.00205543 (2118979) 07/13/2012 03:01:36
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
 Enabled scn: 0x0000.002053b1 (2118577) 07/13/2012 02:58:43
 Thread closed scn: 0x0000.00205543 (2118979) 07/13/2012 03:01:36
 Disk cksum: 0xc3f9 Calc cksum: 0xc3f9
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000


DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo01.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
	Compatibility Vsn = 186646528=0xb200000
	Db ID=4173966754=0xf8c9ada2, Db Name='ORA11G'
	Activation ID=4184707968=0xf96d9380
	Control Seq=7663=0x1def, File size=30720=0x7800
	File Number=1, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000004, SCN 0x000000205540-0x000000205543"
 thread: 1 nab: 0x2 seq: 0x00000004 hws: 0x2 eot: 0 dis: 0
 resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)
 prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)
 Low  scn: 0x0000.00205540 (2118976) 07/13/2012 03:01:35
 Next scn: 0x0000.00205543 (2118979) 07/13/2012 03:01:36
 Enabled scn: 0x0000.002053b1 (2118577) 07/13/2012 02:58:43
 Thread closed scn: 0x0000.00205540 (2118976) 07/13/2012 03:01:35
 Disk cksum: 0xaa26 Calc cksum: 0xaa26
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000

通过对redo dump的分析可以得到：
1.最小的sequence#=3是redo03.log
2.current redo为redo02.log

继续尝试恢复

SQL> recover database using backup controlfile;
ORA-00279: change 2118713 generated at 07/13/2012 02:58:43 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf
ORA-00280: change 2118713 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
ORA-00326: log begins at change 2118979, need earlier change 2118713
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo02.log'

关于备份控制文件使用using backup controlfile总结:恢复的启动控制文件备份的scn,需要该控制文件备份后的所有归档日志.对于当前这个非归档,而且redo被覆盖的库,该方法无法正常恢复

重建控制文件并做controlf 3 dump

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

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log'  SIZE 15M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log'  SIZE 15M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log'  SIZE 15M BLOCKSIZE 512
DATAFILE
  '/u01/oracle/oradata/ora11g/system01.dbf',
  '/u01/oracle/oradata/ora11g/sysaux01.dbf',
  '/u01/oracle/oradata/ora11g/undotbs01.dbf',
  '/u01/oracle/oradata/ora11g/users01.dbf',
  '/u01/oracle/oradata/ora11g/xifenfei02.dbf'
CHARACTER SET AL32UTF8
;
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             432014844 bytes
Database Buffers           83886080 bytes
Redo Buffers                5861376 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  
Control file created.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_1867.trc

尝试数据库恢复

SQL>  recover database using backup controlfile;
ORA-00279: change 2118981 generated at 07/13/2012 03:01:42 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf
ORA-00280: change 2118981 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> 
SQL> select to_char(2118981,'xxxxxxx') from dual;

TO_CHAR(2118981,
----------------
  205545

SQL> set linesize 150
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  3  from v$datafile_header;

     FILE# SCN                                RESETLOGS SCN                      FUZZY
---------- ---------------------------------- ---------------------------------- ------
         1           2118981                            2118577                  YES
         2           2118981                            2118577                  YES
         3           2118981                            2118577                  YES
         4           2118981                            2118577                  YES
         6           2118981                            2118577                  YES

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          2118981
         2          2118981
         3          2118981
         4          2118981
         6          2118981

分析 controlf 3 dump文件

***************************************************************************
DATABASE ENTRY
***************************************************************************
 (size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 1, numrecs = 1)
 07/13/2012 03:24:51
 DB Name "ORA11G"
 Database flags = 0x00400102 0x00001000
 Controlfile Creation Timestamp  07/13/2012 03:24:51
 Incmplt recovery scn: 0x0000.00000000
 Resetlogs scn: 0x0000.002053b1 Resetlogs Timestamp  07/13/2012 02:58:43
 Prior resetlogs scn: 0x0000.0016eaab Prior resetlogs Timestamp  05/01/2012 13:14:57
 Redo Version: compatible=0xb200000
 #Data files = 5, #Online files = 5
 Database checkpoint: Thread=1 scn: 0x0000.00205543   

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1: 
  name #8: /u01/oracle/oradata/ora11g/system01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:760 scn: 0x0000.00205545 07/13/2012 03:01:42
 Stop scn: 0xffff.ffffffff 07/13/2012 03:24:51
 Creation Checkpointed at scn:  0x0000.00000007 09/18/2011 17:33:47

完成恢复

SQL> recover database using backup controlfile;
ORA-00279: change 2118981 generated at 07/13/2012 03:01:42 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf
ORA-00280: change 2118981 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
Log applied.
Media recovery complete.

关于重建控制文件后使用using backup总结:重建控制文件后,恢复的起点是datafile header scn 最小值,需要改scn之后的所有日志

发表在 Oracle备份恢复 | 一条评论