*仅仅丢失一个普通用户数据文件的恢复A(联机恢复)
（例如，丢失D:\BACKUPDB\USERS01.DBF）
准备工作
通过下面的工作，如果完全恢复，应该可以看到；insert into test1 values(2);
SQL> conn lunar/lunar
已连接。
SQL> select * from tab;
TESTBACKUP3 TABLE
已选择 1 行。
SQL> create table test1
2 (a number);
表已创建。
SQL> insert into test1 values(1);
已创建 1 行。
SQL> alter system switch logfile;
系统已更改。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
SQL> insert into test1 values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
SQL> conn internal
已连接。
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 d:\BACKUPDB\archive
最早的概要信息日志序列 3
下一个存档日志序列 5
当前日志序列 5
SQL>
shutdown abort关闭例程，模拟数据文件丢失
SQL> shutdown abort
ORACLE 例程已经关闭。
Mount数据库
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
数据库装载完毕。
使损坏的数据文件脱机
SQL> alter database datafile 'D:\BACKUPDB\USERS01.DBF' offline;
数据库已更改。
打开数据库
SQL> alter database open;
数据库已更改。
SQL>
拷贝刚才热备的数据文件（USERS01.DBF）
恢复损坏的数据文件
SQL> recover datafile 'D:\BACKUPDB\USERS01.DBF';
ORA-00279: ?? 424116 (? 10/20/2002 20:42:04 ??) ???? 1 ????
ORA-00289: ??: D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: ?? 424116 ???? 1 ???? # 1 ???
指定日志: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: ?? 424125 (? 10/20/2002 20:44:14 ??) ???? 1 ????
ORA-00289: ??: D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: ?? 424125 ???? 1 ???? # 2 ???
ORA-00278: ??????????? 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC'
ORA-00279: ?? 424135 (? 10/20/2002 21:55:35 ??) ???? 1 ????
ORA-00289: ??: D:\BACKUPDB\ARCHIVE\BACKUPT001S00003.ARC
ORA-00280: ?? 424135 ???? 1 ???? # 3 ???
ORA-00278: ??????????? 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC'
已应用的日志。
完成介质恢复。
SQL>
使恢复完成的数据文件联机
SQL> alter database datafile 'D:\BACKUPDB\USERS01.DBF' online;
Database altered.
验证恢复的结果：完全恢复
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TESTBACKUP3 TABLE
SQL> select * from test1;
A
----------
1
2
说明：
1. shutdown abort关闭例程，模拟数据文件丢失
2. Mount数据库
3. 使损坏的数据文件脱机
4. 打开数据库
5. 拷贝刚才热备的数据文件（USERS01.DBF）
6. 恢复损坏的数据文件
7. 使恢复完成的数据文件联机
shutdown immedate，恢复全部数据文件（不包括control和redo）
（把热备的数据文件拷贝回来，不包括control和redo）
SQL> conn internal
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
复制全部热备的数据文件过来（完全恢复成功！）
mount数据库
SQL> startup mount
ORACLE instance started.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
SQL>
完全恢复数据库
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
ORA-00279: change 424135 generated at 10/20/2002 21:55:35 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00003.ARC
ORA-00280: change 424135 for thread 1 is in sequence #3
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC' no longer needed
for this recovery
ORA-00279: change 424139 generated at 10/20/2002 21:57:42 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00004.ARC
ORA-00280: change 424139 for thread 1 is in sequence #4
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00003.ARC' no longer needed
for this recovery
Log applied.
Media recovery complete.
SQL>
打开数据库
SQL> alter database open;
Database altered.
验证恢复结果：完全恢复
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;
A
----------
1
2
SQL>
完全恢复成功！
说明：
1. 复制全部热备的数据文件过来
2. mount数据库
3. 完全恢复数据库
4. 打开数据库
*shutdown abort的情况，恢复全部数据文件（不包括control和redo）
用热备的数据文件恢复（把热备的数据文件拷贝回来，不包括control和redo）
SQL> conn internal
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL>
复制全部热备的数据文件过来（完全恢复成功！）
mount数据库
SQL> startup mount
ORACLE instance started.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.

完全恢复数据库
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
ORA-00279: change 424135 generated at 10/20/2002 21:55:35 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00003.ARC
ORA-00280: change 424135 for thread 1 is in sequence #3
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC' no longer needed
for this recovery
ORA-00279: change 424139 generated at 10/20/2002 21:57:42 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00004.ARC
ORA-00280: change 424139 for thread 1 is in sequence #4
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00003.ARC' no longer needed
for this recovery
Log applied.
Media recovery complete.
打开数据库
SQL> alter database open;
Database altered.
验证恢复结果：完全恢复
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;
A
----------
1
2
SQL>
完全恢复成功！
说明：
1. 复制全部热备的数据文件过来
2. mount数据库
3. 完全恢复数据库
4. 打开数据库

*shutdown immediate，丢失全部控制文件（不包括数据文件和redo），A(完全恢复)
SQL> conn internal
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
用热备的控制文件恢复（把热备的控制文件拷贝回来）
mount数据库
SQL> startup mount
ORACLE instance started.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
完全恢复和until cancel using backup controlfile都失败
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 424123 generated at 10/20/2002 20:44:12 needed for thread 1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00266: name of archived log file needed

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
SQL>
重建控制文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL>
找到那个控制文件，然后编辑
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\BACKUPDB\REDO01.LOG' SIZE 1M,
GROUP 2 'D:\BACKUPDB\REDO02.LOG' SIZE 1M,
GROUP 3 'D:\BACKUPDB\REDO03.LOG' SIZE 1M
DATAFILE
'D:\BACKUPDB\SYSTEM01.DBF',
'D:\BACKUPDB\RBS01.DBF',
'D:\BACKUPDB\USERS01.DBF',
'D:\BACKUPDB\TEMP01.DBF',
'D:\BACKUPDB\TOOLS01.DBF',
'D:\BACKUPDB\INDX01.DBF'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
重建控制文件，并且恢复数据库（完全恢复成功！）
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
SQL> @D:\BACKUPDB\udump\ORA02120.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Cluster altered.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
验证恢复结果：完全恢复
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;
A
----------
1
2

SQL>
完全恢复成功！
说明：
当shutdown immediate的以后，如果丢失全部控制文件（不包括数据文件和redo），需要用热备的控制文件恢复数据库的时候，要想完全恢复（一直恢复到redo中commit的数据），必须执行以下步骤：
1. mount数据库，
2. backup controlfile to trace
3. 修改这个生成的控制文件
4. nomount
5. 重建控制文件，
*shutdown abort的情况，恢复全部控制文件（不包括数据文件和redo）
准备工作
以下说明，如果完全恢复数据库，应该可以看到insert into test1 values(7);
SQL> insert into test1 values(3);
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> insert into test1 values(4);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter system switch logfile;

System altered.

SQL> conn internal
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\BACKUPDB\archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>
SQL> select * from test1;
A
----------
1
2
3
4
SQL> insert into test1 values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test1 values(6);
1 row created.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> conn internal
Connected.
SQL> conn lunar/lunar
Connected.
SQL> insert into test1 values(7);
1 row created.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn internal
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
删除那个控制文件，把热备的控制文件拷贝过来
mount数据库
SQL> startup mount
ORACLE instance started.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
ORA-01991: invalid password file 'd:\oracle1\ora81\DATABASE\PWDbackup.ORA'
根据提示，重建口令文件
SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
E:\>cd d:\oracle1\ora81\DATABASE
E:\>d:
D:\oracle1\ora81\database>del PWDbackup.ORA
D:\oracle1\ora81\database>dir
驱动器 D 中的卷是 Program
卷的序列号是 D0E6-FA1C
D:\oracle1\ora81\database 的目录
2002-10-21 00:42