演练手工恢复之前，先做一次数据库的冷热备。
--手工冷备
SQL> conn / as sysdba
Connected.
SQL> @/home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk1/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk2/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/control01.ctl /home/oracle/prod_bak/cold_bak
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
--手工热备
SQL> @/home/oracle/prod_bak/hot_bak
alter system switch logfile;
alter tablespace SYSTEM begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup;
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace UNDOTBS end backup;
alter tablespace USERS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace USERS end backup;
alter tablespace LXTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace LXTBS end backup;
alter database backup controlfile to trace;
alter database backup controlfile to '/home/oracle/prod_bak/hot_bak/control01.dbf' reuse;
alter system switch logfile;
注意：冷热备脚本详见上一篇“手工备份”笔记
下面会演示八个案例，场景分别对应如下：
归档模式下：
1、所有控制文件、数据文件都丢失；
2、所有数据文件丢失（完全恢复）；
3、部分数据文件丢失（一般恢复，如恢复tablespace或datafile）；
4、部分数据文件丢失（高可用模式恢复tablespace或datafile，即先开库再恢复）；
5、新建表空间丢失，且此表空间无相关备份（不可转储）；
6、部分数据文件丢失（高可用模式恢复，且将数据文件恢复至新的位置）。
非归档模式下：
1、数据丢失，且历史日志没有被覆盖；
2、数据丢失，但日志发生切换，历史日志被覆盖；
案例一、所有控制文件、数据文件都丢失，有控制文件转储信息且有冷热备，进行手工恢复
1、破坏性实验前准备工作（转储控制文件信息）
先转储控制文件，因为控制文件丢失情况恢复需重建控制文件，所以演示前先转储下控制文件，主要是要找到重建控制文件语句。
--转储控制文件
SQL> alter database backup controlfile to trace;
SQL> show parameter dump;
background_core_dump string partial
background_dump_dest string /u01/app/oracle/oradata/PROD/d
ump/diag/rdbms/prod/PROD/trace
core_dump_dest string /u01/app/oracle/oradata/PROD/d
ump/diag/rdbms/prod/PROD/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/oradata/PROD/d
ump/diag/rdbms/prod/PROD/trace
[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace
[oracle@gc1 trace]$ ls -lt | grep PROD_ora
-rw-r----- 1 oracle oinstall 20057 Apr 7 17:39 PROD_ora_3811.trc
-rw-r----- 1 oracle oinstall 322 Apr 7 17:39 PROD_ora_3811.trm
-rw-r----- 1 oracle oinstall 14399 Apr 7 17:06 PROD_ora_3626.trc
-rw-r----- 1 oracle oinstall 256 Apr 7 17:06 PROD_ora_3626.trm
……
--获取转储的控制文件信息，主要提取重建控制文件语句
[oracle@gc1 trace]$ more PROD_ora_3811.trc
注意：转储控制文件时，可能会因为时间太长会记不住哪个是转储的控制文件信息，我们可以给转储下来的控制文件命名，如
SQL> alter database backup controlfile to trace as '/u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/PROD_ora_20140513.trc';
[oracle@gc1 trace]$ ls -lt | grep trc$
2、创建实验表（分三步插入数据：提交归档、只提交不归档、不提交不归档）
SQL> create table scott.emp1 as select * from scott.emp;
Table created.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
14
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
28
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
42
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
注意：scott.emp1表插入28条记录时，提交且归档了，插入42条记录时提交了但未归档，插入56条记录时，未提交且未归档，此时出现非法关机，且所有控制文件与数据文件都丢失，如下：
3、手工破坏性操作（非法关机且删除所有数据文件）
SQL> shutdown abort;
ORACLE instance shut down.
--数据文件破坏性操作（手工删除所有控制文件和数据文件）
[oracle@gc1 prod_bak]$ cd /u01/app/oracle/oradata/PROD/
[oracle@gc1 PROD]$ ls
disk1 disk2 disk3 disk4 disk5 dump
[oracle@gc1 PROD]$ cd disk1
[oracle@gc1 disk1]$ ls
control01.ctl redo01.log redo02.log redo03.log
[oracle@gc1 disk1]$ rm *.ctl
[oracle@gc1 disk1]$ cd ../
[oracle@gc1 PROD]$ ls
disk1 disk2 disk3 disk4 disk5 dump
[oracle@gc1 PROD]$ cd disk2
[oracle@gc1 disk2]$ ls
control01.ctl redo01.log redo02.log redo03.log
[oracle@gc1 disk2]$ rm *.ctl
[oracle@gc1 disk2]$ cd ../disk3
[oracle@gc1 disk3]$ ls
control01.ctl lxtbs01.dbf system01.dbf users01.dbf
[oracle@gc1 disk3]$ rm *
[oracle@gc1 disk3]$ ls
[oracle@gc1 disk3]$ cd ../disk4
[oracle@gc1 disk4]$ ls
sysaux01.dbf temp01.dbf
[oracle@gc1 disk4]$ rm *
[oracle@gc1 disk4]$ cd ../disk5
[oracle@gc1 disk5]$ ls
undotbs01.dbf
[oracle@gc1 disk5]$ rm *
[oracle@gc1 disk5]$
--启库
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
ORA-00205: error in identifying control file, check alert log for more info
注意：启库时报未找到控制文件错误，因为控制文件被非法破坏，找到之前转储的控制文件信息，进行重建控制文件操作。
4、手工恢复
--重建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 2
4 MAXDATAFILES 30
5 MAXINSTANCES 1
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '/u01/app/oracle/oradata/PROD/disk1/redo01.log',
10 '/u01/app/oracle/oradata/PROD/disk2/redo01.log'
11 ) SIZE 100M BLOCKSIZE 512,
12 GROUP 2 (
13 '/u01/app/oracle/oradata/PROD/disk1/redo02.log',
14 '/u01/app/oracle/oradata/PROD/disk2/redo02.log'
15 ) SIZE 100M BLOCKSIZE 512,
16 GROUP 3 (
17 '/u01/app/oracle/oradata/PROD/disk1/redo03.log',
18 '/u01/app/oracle/oradata/PROD/disk2/redo03.log'
19 ) SIZE 100M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 '/u01/app/oracle/oradata/PROD/disk3/system01.dbf',
23 '/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf',
24 '/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf',
25 '/u01/app/oracle/oradata/PROD/disk3/users01.dbf',
26 '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
27 CHARACTER SET ZHS16GBK
28 ;
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
注意：报未找到相应的数据文件，因此重建控制文件之前，需先转储数据文件（即将之前备份的数据文件先转储至原来存放的位置）。
--转储数据文件信息
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/system01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/sysaux01.dbf /u01/app/oracle/oradata/PROD/disk4
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/PROD/disk5
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/users01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$
--再次执行重建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 2
4 MAXDATAFILES 30
5 MAXINSTANCES 1
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '/u01/app/oracle/oradata/PROD/disk1/redo01.log',
10 '/u01/app/oracle/oradata/PROD/disk2/redo01.log'
11 ) SIZE 100M BLOCKSIZE 512,
12 GROUP 2 (
13 '/u01/app/oracle/oradata/PROD/disk1/redo02.log',
14 '/u01/app/oracle/oradata/PROD/disk2/redo02.log'
15 ) SIZE 100M BLOCKSIZE 512,
16 GROUP 3 (
17 '/u01/app/oracle/oradata/PROD/disk1/redo03.log',
18 '/u01/app/oracle/oradata/PROD/disk2/redo03.log'
19 ) SIZE 100M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 '/u01/app/oracle/oradata/PROD/disk3/system01.dbf',
23 '/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf',
24 '/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf',
25 '/u01/app/oracle/oradata/PROD/disk3/users01.dbf',
26 '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
27 CHARACTER SET ZHS16GBK
28 ;
Control file created.
注意：这次重建控制文件成功了，然后只要恢复数据库，开库即可。
--恢复database并开库
SQL> recover database;
ORA-00279: change 525864 generated at 04/07/2014 17:10:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_24_827176743.dbf
ORA-00280: change 525864 for thread 1 is in sequence #24
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
--查看scott.emp1表的记录数
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
42
注意：由此可见，做数据库的完全恢复是恢复至最后一次提交，而非最后一次归档。
案例二、所有数据文件丢失（完全恢复）；
1、构造测试表emp2
前面的操作步骤与上例一样，在此不多述，直接附上测试代码
SQL> create table emp2 tablespace lxtbs as select * from scott.emp;
Table created.
SQL> select count(1) from emp2;
COUNT(1)
----------
14
SQL> insert into emp2 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from emp2;
COUNT(1)
----------
28
SQL> insert into emp2 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp2;
COUNT(1)
----------
42
SQL> insert into emp2 select * from scott.emp;
14 rows created.
2、手工破坏性操作（非法关机且删除所有数据文件）
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/system01.dbf
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/users01.dbf
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
--这时再启库，报错如下
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
注意：由此可见，所有数据文件都坏掉了。下面先转储所有数据文件
3、手工恢复
转储所有数据文件，然后恢复整个数据库
--转储数据文件
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/system01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/sysaux01.dbf /u01/app/oracle/oradata/PROD/disk4
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/PROD/disk5
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/users01.dbf /u01/app/oracle/oradata/PROD/disk3
--恢复数据库，开库
SQL>
recover database;
ORA-00279: change 525864 generated at 04/07/2014 17:10:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_24_827176743.dbf
ORA-00280: change 525864 for thread 1 is in sequence #24
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 525961 generated at 04/07/2014 17:12:22 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_25_827176743.dbf
ORA-00280: change 525961 for thread 1 is in sequence #25
ORA-00279: change 529755 generated at 04/07/2014 20:09:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_26_827176743.dbf
ORA-00280: change 529755 for thread 1 is in sequence #26
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(1) from emp2;
COUNT(1)
----------
42
案例三、部分数据文件丢失（一般恢复，如恢复tablespace或datafile）
恢复原理：先转储被破坏的数据文件，然后恢复表空间或数据文件，再开库即可。
操作步骤和前面案例类似，不详解，直接附上演示代码如下：
SQL> create table emp3 tablespace lxtbs as select * from scott.emp;
Table created.
SQL> select count(1) from emp3;
COUNT(1)
----------
14
SQL> insert into emp3 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from emp3;
COUNT(1)
----------
28
SQL> insert into emp3 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp3;
COUNT(1)
----------
42
SQL> insert into emp3 select * from scott.emp;
14 rows created.
SQL> select count(1) from emp3;
COUNT(1)
----------
56
SQL> select file#,name from v$datafile;
FILE# NAME
----- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--非法关库
SQL> shutdown abort
ORACLE instance shut down.
--手工破坏某个数据文件
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--启库报错，查看具体的坏数据文件信息
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
----- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
--转储坏掉的数据文件
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
--恢复表空间或数据文件（若被破坏的数据文件对应的表空间，指定了多个数据文件，则选择恢复表空间，否则选择恢复数据文件）
SQL>
recover tablespace lxtbs;
ORA-00279: change 525936 generated at 04/07/2014 17:12:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_24_827176743.dbf
ORA-00280: change 525936 for thread 1 is in sequence #24
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 525961 generated at 04/07/2014 17:12:22 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_25_827176743.dbf
ORA-00280: change 525961 for thread 1 is in sequence #25
ORA-00279: change 529755 generated at 04/07/2014 20:09:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_26_827176743.dbf
ORA-00280: change 529755 for thread 1 is in sequence #26
ORA-00279: change 529813 generated at 04/07/2014 20:11:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_27_827176743.dbf
ORA-00280: change 529813 for thread 1 is in sequence #27
ORA-00279: change 549820 generated at 04/07/2014 21:00:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_28_827176743.dbf
ORA-00280: change 549820 for thread 1 is in sequence #28
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(1) from emp3;
COUNT(1)
----------
42
案例四、部分数据文件丢失（高可用模式恢复tablespace或datafile，即先开库再恢复）
恢复原理：与部分数据丢失后，一般恢复类似，只是高可用模式是先将破坏的文件offline，然后开库，再做破坏文件的转储及恢复，然后破坏文件online，具体操作步骤见下面代码：
SQL> create table emp4 as select * from scott.emp;
Table created.
SQL> select count(1) from emp4;
COUNT(1)
----------
14
SQL> insert into emp4 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from emp4;
COUNT(1)
----------
28
SQL> insert into emp4 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp4;
COUNT(1)
----------
42
SQL> insert into emp4 select * from scott.emp;
14 rows created.
SQL> select count(1) from emp4;
COUNT(1)
----------
56
SQL> col name for a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@gc1 ~]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
--对表空间或数据文件offline（这里不能对表空间offline，因为表空间的offline必须在开库时使用）
SQL> alter tablespace lxtbs offline;
alter tablespace lxtbs offline
*
ERROR at line 1:
ORA-01109: database not open
SQL>
alter database datafile 5 offline;
Database altered.
SQL> alter database open;
Database altered.
--转储数据文件及恢复
[oracle@gc1 ~]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
SQL>
recover datafile 5;
ORA-00279: change 525936 generated at 04/07/2014 17:12:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_24_827176743.dbf
ORA-00280: change 525936 for thread 1 is in sequence #24
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 525961 generated at 04/07/2014 17:12:22 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_25_827176743.dbf
ORA-00280: change 525961 for thread 1 is in sequence #25
ORA-00279: change 529755 generated at 04/07/2014 20:09:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_26_827176743.dbf
ORA-00280: change 529755 for thread 1 is in sequence #26
ORA-00279: change 529813 generated at 04/07/2014 20:11:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_27_827176743.dbf
ORA-00280: change 529813 for thread 1 is in sequence #27
ORA-00279: change 549820 generated at 04/07/2014 21:00:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_28_827176743.dbf
ORA-00280: change 549820 for thread 1 is in sequence #28
ORA-00279: change 550247 generated at 04/07/2014 21:12:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_29_827176743.dbf
ORA-00280: change 550247 for thread 1 is in sequence #29
ORA-00279: change 570258 generated at 04/07/2014 21:36:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_30_827176743.dbf
ORA-00280: change 570258 for thread 1 is in sequence #30
ORA-00279: change 570504 generated at 04/07/2014 21:40:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_31_827176743.dbf
ORA-00280: change 570504 for thread 1 is in sequence #31
Log applied.
Media recovery complete.
SQL>
alter database datafile 5 online;
Database altered.
SQL> select count(1) from emp4;
COUNT(1)
----------
42
案例五、新建表空间丢失，且此表空间无相关备份（不可转储）
恢复原理：前面与高可用模式类似，即先将被破坏的数据文件offline，然后开库。但无法转储破坏的数据文件，因为是新建的表空间，没来得及做备份，这时可以先创建一空白的数据文件，然后完全利用日志记录做数据文件的恢复，最后再将数据文件online。具体操作流程见下面代码：
SQL> create tablespace zhentbs datafile '/u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf' size 50m;
Tablespace created.
SQL> create table emp5 tablespace zhentbs as select * from scott.emp;
Table created.
SQL> select count(1) from emp5;
COUNT(1)
----------
14
SQL> insert into emp5 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from emp5;
COUNT(1)
----------
28
SQL> insert into emp5 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp5;
COUNT(1)
----------
42
SQL> insert into emp5 select * from scott.emp;
14 rows created.
SQL> select count(1) from emp5;
COUNT(1)
----------
56
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
6 /u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf
6 rows selected.
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@gc1 ~]$ rm /u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 UNKNOWN ERROR
6 FILE NOT FOUND
6 rows selected.
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database open;
Database altered.
--创建空白的数据文件
SQL>
alter database create datafile '/u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf';
Database altered.
--完全利用日志恢复数据文件
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select count(1) from emp5;
COUNT(1)
----------
42
案例六、部分数据文件丢失（高可用模式恢复，且将数据文件恢复至新的位置）
恢复原理：与部分数据文件丢失后，高可用模式恢复情况类似，只是做数据文件转储时是转储到新的位置，转储后要通过alter database rename datafile的方式变更控制文件信息记录的数据文件存放信息，然后再恢复及online。具体操作流程，见下面演示代码：
SQL> create table emp6 as select * from scott.emp;
Table created.
SQL> select count(1) from emp6;
COUNT(1)
----------
14
SQL> insert into emp6 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from emp6;
COUNT(1)
----------
28
SQL> insert into emp6 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp6;
COUNT(1)
----------
42
SQL> insert into emp6 select * from scott.emp;
14 rows created.
SQL> select count(1) from emp6;
COUNT(1)
----------
56
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
6 /u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf
6 rows selected.
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@gc1 ~]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database open;
Database altered.
--转储数据文件至新的位置disk4
[oracle@gc1 ~]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/
disk4
--修改控制文件记录的数据文件信息
SQL>
alter database rename file '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf' to '/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf' ;
Database altered.
SQL> recover datafile 5;
ORA-00279: change 525936 generated at 04/07/2014 17:12:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_24_827176743.dbf
ORA-00280: change 525936 for thread 1 is in sequence #24
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 525961 generated at 04/07/2014 17:12:22 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_25_827176743.dbf
ORA-00280: change 525961 for thread 1 is in sequence #25
ORA-00279: change 529755 generated at 04/07/2014 20:09:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_26_827176743.dbf
ORA-00280: change 529755 for thread 1 is in sequence #26
ORA-00279: change 529813 generated at 04/07/2014 20:11:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_27_827176743.dbf
ORA-00280: change 529813 for thread 1 is in sequence #27
ORA-00279: change 549820 generated at 04/07/2014 21:00:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_28_827176743.dbf
ORA-00280: change 549820 for thread 1 is in sequence #28
ORA-00279: change 550247 generated at 04/07/2014 21:12:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_29_827176743.dbf
ORA-00280: change 550247 for thread 1 is in sequence #29
ORA-00279: change 570258 generated at 04/07/2014 21:36:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_30_827176743.dbf
ORA-00280: change 570258 for thread 1 is in sequence #30
ORA-00279: change 570504 generated at 04/07/2014 21:40:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_31_827176743.dbf
ORA-00280: change 570504 for thread 1 is in sequence #31
ORA-00279: change 590610 generated at 04/07/2014 21:53:59 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_32_827176743.dbf
ORA-00280: change 590610 for thread 1 is in sequence #32
ORA-00279: change 591967 generated at 04/08/2014 02:45:40 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_33_827176743.dbf
ORA-00280: change 591967 for thread 1 is in sequence #33
ORA-00279: change 611979 generated at 04/08/2014 02:55:26 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_34_827176743.dbf
ORA-00280: change 611979 for thread 1 is in sequence #34
Log applied.
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> select count(1) from emp6;
COUNT(1)
----------
42
注意：以上六个案例都是在归档模式下，进行不同场景的手工恢复操作，下面演示二个案例是在非归档模式下执行的恢复。
--首先切换为非归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/PROD
Oldest online log sequence 38
Next log sequence to archive 40
Current log sequence 40
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/PROD
Oldest online log sequence 38
Current log sequence 40
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
注意：开关归档后，必须重做备份，下面重做冷备份（因为非归档模式无法做热备份）
SQL> @/home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk1/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk2/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/control01.ctl /home/oracle/prod_bak/cold_bak
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
案例七、数据丢失，且历史日志没有被覆盖
恢复原理：与归档模式下，部分数据文件丢失，一般恢复原理一样，即先转储数据文件，然后恢复数据文件，再开库。
注意：非归档模式，部分数据文件丢失不可采用高可用模式进行恢复，详见下面演示代码：
SQL> create table emp7 as select * from scott.emp;
Table created.
SQL> select count(1) from emp7;
COUNT(1)
----------
14
SQL> insert into emp7 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
注意：以上报错，是因为当前数据库为非归档模式，因此不能执行归档，可利用切换日志组方式，生成日志
SQL> alter system switch logfile;
System altered.
SQL> select count(1) from emp7;
COUNT(1)
----------
28
SQL> insert into emp7 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp7;
COUNT(1)
----------
42
SQL> insert into emp7 select * from scott.emp;
14 rows created.
SQL> select count(1) from emp7;
COUNT(1)
----------
56
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/PROD/disk3/system01.dbf
2
/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3
/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
4
/u01/app/oracle/oradata/PROD/disk3/users01.dbf
5
/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
6
/u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf
6 rows selected.
SQL> col name for a50;
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
6 /u01/app/oracle/oradata/PROD/disk3/zhentbs01.dbf
6 rows selected.
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
--先尝试用高可用性模式进行恢复，即offline破坏数据文件，再开库
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
由此可见，非归档模式，在未开库的情况下，无法对数据文件进行offline，因此只能用一般模式进行恢复（先恢复再开库）。
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3/
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(1) from emp7;
COUNT(1)
----------
42
案例八、数据丢失，但日志发生切换，历史日志被覆盖
恢复原理：因为为非归档模式且日志丢失的情况做恢复，无法进行完全恢复，只能做不完全恢复，即利用冷备信息，关库状态，转储所有控制文件及数据文件，然后recover database until cancel，最后采用open resetlogs的方式开库。
SQL> create table emp8 as select * from scott.emp;
Table created.
SQL> select count(1) from emp8;
COUNT(1)
----------
14
SQL> insert into emp8 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select count(1) from emp8;
COUNT(1)
----------
28
SQL> insert into emp8 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from emp8;
COUNT(1)
----------
42
SQL> insert into emp8 select * from scott.emp;
14 rows created.
SQL> select count(1) from emp8;
COUNT(1)
----------
56
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
1 1 43 104857600 512 2 NO CURRENT 695373
08-APR-14 2.8147E+14
2 1 41 104857600 512 2 NO INACTIVE 675128
08-APR-14 695138 08-APR-14
3 1 42 104857600 512 2 NO ACTIVE 695138
08-APR-14 695373 08-APR-14
注意：系统总共有三组日志，要想日志被覆盖，只要切换四次日志即可，如下：
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
这样之前的日志信息肯定被覆盖了，这时再非法关库，且手工破坏某个数据文件，如下：
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@gc1 prod_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3/
SQL> recover datafile 5;
ORA-00279: change 674874 generated at 04/08/2014 04:05:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_40_827176743.dbf
ORA-00280: change 674874 for thread 1 is in sequence #40
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/PROD/1_40_827176743.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/home/oracle/PROD/1_40_827176743.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
注意：转储后，对数据文件进行恢复，这时报错，因为归档信息丢失。
--尝试恢复数据库
SQL> recover database;
ORA-00279: change 674874 generated at 04/08/2014 04:05:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_40_827176743.dbf
ORA-00280: change 674874 for thread 1 is in sequence #40
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/PROD/1_40_827176743.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/home/oracle/PROD/1_40_827176743.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
注意：也失败了，再尝试用until cancel
SQL>
recover database until cancel;
ORA-00279: change 674874 generated at 04/08/2014 04:05:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_40_827176743.dbf
ORA-00280: change 674874 for thread 1 is in sequence #40
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'
ORA-01112: media recovery not started
注意：当且仅当recover database为until cancel（表示日志能连到哪就连到哪）时，后面给日志时，才可用cancel模式。
上面的信息可知，用recover database until cancel时，也无法恢复，这是因为，非归档模式下，且日志丢失的情况下，无法做完全恢复 （只恢复破坏的数据文件），只能做不完全恢复，不完全恢复的前提是要转储所有的数据文件信息。下面就演示下非归档模式下的不完全恢复（即先关库，然后转储所有控制文件及数据文件信息）。
--关库
SQL> shutdown abort;
ORACLE instance shut down.
--转储所有控制文 件及数据文件
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/system01.dbf /u01/app/oracle/oradata/PROD/disk3/
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/users01.dbf /u01/app/oracle/oradata/PROD/disk3/
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/zhentbs01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/sysaux01.dbf /u01/app/oracle/oradata/PROD/disk4/
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/undotbs01.dbf /u01/app/oracle/oradata/PROD/disk5
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/control01.ctl /u01/app/oracle/oradata/PROD/disk1
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/control01.ctl /u01/app/oracle/oradata/PROD/disk2
[oracle@gc1 prod_bak]$ cp /home/oracle/prod_bak/cold_bak/control01.ctl /u01/app/oracle/oradata/PROD/disk3
--启库至mount状态，并查看三个scn号
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
674874
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 674874
2 674874
3 674874
4 674874
5 674874
6 674874
6 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 674874
2 674874
3 674874
4 674874
5 674874
6 674874
6 rows selected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 9325
Session ID: 18 Serial number: 5
注意：这时开库失败了，且断掉了，需重连才行（10g提示会友好些，且不会断掉）。
--重连后，关库再启至mount状态，然后recove database until cancel
SQL> quit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 8 05:08:26 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1161372/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1161372/
扫一扫
94
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
