一个朋友向我咨询了一个问题，
向你请教一个oracle数据库的恢复问题：
假设中午12：00对数据库A做了一个全量冷拷贝（不是用工具或命令进行备份，而是对文件直接拷贝）， 到12:30时候，数据库宕机了，假如redo文件在另外的硬盘上且未损，怎么利用12:00时的冷拷贝和未损坏的redo文件恢复数据库？
麻烦了，谢谢
我刚才请教下姚老师，
有三种恢复方法：
1》一种是使用你12：00的冷拷贝来进行恢复，但是只能恢复到12：00
2》如果想恢复到12：30的，需要把冷拷贝的拿出来，（排除了3个redo)
,再用你未损的12：30的redo，也放入这个集合里。
alter database mount；
recover database until cancel；
指定联机日志文件为12:30 的redo，(三个都试一下，有一个是联机的redo），
最后应该可以恢复成功
3》如果当时的日志归档了，
也可以在归档日志中把文件调出来，进行恢复，
最后open database resetlogs，
数据不会丢失。
实验过程如下
初始状态：
[oracle@VLG app]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 11:04:54 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count (*) from tabs;
COUNT(*)
----------
708
SQL> select * from cc;
NAME AGE
---------- ----------
cx 26
cj 29
cst 25
SQL> set line 200
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 2675377 05-FEB-09
2 1 2 52428800 1 NO CURRENT 2675378 05-FEB-09
3 1 0 52428800 1 YES UNUSED 0
把当前的数据库做一个冷备份：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
（关闭数据库）
（做冷备份）
[oracle@VLG oems]$ cd ..
[oracle@VLG app]$ pwd
/app
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems
[oracle@VLG app]$ mkdir oemsoldbakup
[oracle@VLG app]$ cp -rf oems oemsoldbakup/ &
[1] 12439
[oracle@VLG app]$ ps -ef |grep cp
root 11 7 0 Feb04 ? 00:00:00 [kacpid]
root 2632 1 0 Feb04 ? 00:00:00 /usr/sbin/acpid
68 2835 2829 0 Feb04 ? 00:00:00 hald-addon-acpi: listening on acpid socket /var/run/acpid.socket
root 3172 3165 0 Feb04 tty7 00:00:01 /usr/bin/Xorg :0 -br -audit 0 -auth /var/gdm/:0.Xauth -nolisten tcp vt7
oracle 12714 8935 0 11:15 pts/3 00:00:00 grep cp
[1]+ Done cp -rf oems oemsoldbakup/
打开数据库，做一些插入操作，commit，同时checkpoint
[oracle@VLG app]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 11:15:35 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 360710144 bytes
Fixed Size 1267500 bytes
Variable Size 130025684 bytes
Database Buffers 226492416 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> insert into cc values('xf','24');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> set line 200
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 2675377 05-FEB-09
2 1 2 52428800 1 NO CURRENT 2675378 05-FEB-09
3 1 0 52428800 1 YES UNUSED 0
关闭数据库，把现在的数据库做备份，备份到 oemsckpt
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@VLG app]$ cp -rf oems oemsckpt &
[1] 13894
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems oemsckpt oemsoldbakup
[oracle@VLG app]$ ps -ef |grep cp
root 11 7 0 Feb04 ? 00:00:00 [kacpid]
root 2632 1 0 Feb04 ? 00:00:00 /usr/sbin/acpid
68 2835 2829 0 Feb04 ? 00:00:00 hald-addon-acpi: listening on acpid socket /var/run/acpid.socket
root 3172 3165 0 Feb04 tty7 00:00:01 /usr/bin/Xorg :0 -br -audit 0 -auth /var/gdm/:0.Xauth -nolisten tcp vt7
oracle 14073 2890 0 11:29 pts/2 00:00:00 grep cp
[1]+ Done cp -rf oems oemsckpt
再打开数据库，做switchlogfile 操作，再关闭数据库，把库文件备份到oemslogswitch 下
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 2675377 05-FEB-09
2 1 2 52428800 1 YES ACTIVE 2675378 05-FEB-09
3 1 3 52428800 1 NO CURRENT 2680540 05-FEB-09
（当前日志已经归档了。）
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
（关闭数据库）
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems oemsckpt oemsoldbakup
[oracle@VLG app]$ cp ./oems/redo0*.log ./oemslogswitch/
[oracle@VLG app]$ cd oemslogswitch/
[oracle@VLG oemslogswitch]$ ls
redo01.log redo02.log redo03.log
[oracle@VLG oemslogswitch]$ cd ..
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems oemsckpt oemslogswitch oemsoldbakup
[oracle@VLG app]$ cd oems
[oracle@VLG oems]$ ls
control01.ctl control03.ctl mgmt.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf mgmt_ecm_depot1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@VLG oems]$ cd ..
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems oemsckpt oemslogswitch oemsoldbakup
（冷备份当前数据库）
把当前库重命名，模拟故障
[oracle@VLG app]$ mv oems oemsguzhang
把老库文件拷贝进来，再加上ckpt库的redo文件
[oracle@VLG app]$ mkdir oems
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems oemsckpt oemsguzhang oemslogswitch oemsoldbakup
[oracle@VLG app]$ cd oemsoldbakup/
[oracle@VLG oemsoldbakup]$ ls
oems
[oracle@VLG oemsoldbakup]$ cd oems/
[oracle@VLG oems]$ ls
control01.ctl control03.ctl mgmt.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf mgmt_ecm_depot1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@VLG oems]$ mv *.* /app/oems/
[oracle@VLG oems]$ ls
[oracle@VLG oems]$ cd ..
[oracle@VLG oemsoldbakup]$ ls
oems
[oracle@VLG oemsoldbakup]$ cd ..
[oracle@VLG app]$ ls
flash_recovery_area lost+found oems oemsckpt oemsguzhang oemslogswitch oemsoldbakup
[oracle@VLG app]$ cd oems
[oracle@VLG oems]$ ls
control01.ctl control03.ctl mgmt.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf mgmt_ecm_depot1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@VLG oems]$ mv redo*.log ../oemsoldbakup/oems/
[oracle@VLG oems]$ ls
control01.ctl control03.ctl mgmt.dbf sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf mgmt_ecm_depot1.dbf system01.dbf undotbs01.dbf
[oracle@VLG oems]$ cp ../oemsckpt/redo*.log ./
[oracle@VLG oems]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 11:49:08 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 360710144 bytes
Fixed Size 1267500 bytes
Variable Size 130025684 bytes
Database Buffers 226492416 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1: '/app/oems/redo02.log'
SQL> recover database using backup controlfile;
ORA-00279: change 2677299 generated at 02/05/2009 11:07:47 needed for thread 1
ORA-00289: suggestion :
/app/flash_recovery_area/OEMS/archivelog/2009_02_05/o1_mf_1_2_%u_.arc
ORA-00280: change 2677299 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oems/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from cc;
NAME AGE
---------- ----------
cx 26
cj 29
cst 25
xf 24
此时能够恢复数据库，（利用 ckpt的redo）
第三种情况：
如果做了logswitch操作，只需要找到当时生成的archive 文件，
在recover database using backup controlfile里面指定这个归档就可以了。
但是最后还要recover database using backup controlfile until cancel，
再 open database resetlogs来恢复文件。
380
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
