检查数据库的数据（完全恢复）
SQL> select * from test;
A
说明
采用热备份需要运行在归档模式下可以实现数据库的完全恢复也就是说从备份后到数据库崩溃时的数据都不会丢失;
可以采用全备份数据库的方式备份对于特殊情况也可以只备份特定的数据文件如只备份用户表空间（一般情况下对于某些写特别频繁的数据文件可以单独加大备份频率）
如果在恢复过程中发现损坏的是多个数据文件即可以采用一个一个数据文件的恢复方法（第步中需要对数据文件一一脱机第步中需要对数据文件分别恢复）也可以采用整个数据库的恢复方法
如果是系统表空间的损坏不能采用此方法
RMAN备份方案
RMAN也可以进行联机备份而且备份与恢复方法将比OS备份更简单可靠
连接数据库创建测试表并插入记录
SQL> connect internal/password as sysdba;
Connected
SQL> create table test(a int) tablespace users;
Table created
SQL> insert into test values();
row inserted
SQL> commit;
Commit complete
备份数据库表空间users
C:\>rman
Recovery Manager: Release Production
RMAN> connect rcvcat rman/rman@back
RMAN: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN: connected to target database: TEST (DBID=)
RMAN> run{
> allocate channel c type disk;
> backup tag tsuser format d:\backup\tsuser_%u_%s_%p
> tablespace users;
> release channel c;
> }
RMAN: compiling command: allocate
RMAN: executing command: allocate
RMAN: allocated channel: c
RMAN: channel c: sid= devtype=DISK
RMAN: compiling command: backup
RMAN: performing implicit partial resync of recovery catalog
RMAN: executing command: partial resync
RMAN: starting partial resync of recovery catalog
RMAN: partial resync complete
RMAN: executing command: backup
RMAN: channel c: starting full datafile backupset
RMAN: set_count= set_stamp= creation_time=MAY
RMAN: channel c: specifying datafile(s) in backupset
RMAN: input datafile fno= name=D:\Oracle\ORADATA\TEST\USERDBF
RMAN: channel c: piece created
RMAN: piece handle=D:\BACKUP\TSUSER_ENAC__ comment=NONE
RMAN: backup set complete elapsed time: ::
RMAN: executing command: partial resync
RMAN: starting partial resync of recovery catalog
RMAN: partial resync complete
RMAN: compiling command: release
RMAN: executing command: release
RMAN: released channel: c
RMAN>
继续在测试表中插入记录
SQL> insert into test values();
row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
SQL> alter system switch logfile;
System altered
SQL>r
* alter system switch logfile;
System altered
关闭数据库模拟丢失数据文件
SQL> shutdown immediate;
Database closed
Database dismounted
Oracle instance shut down
C:\>del D:\Oracle\ORADATA\TEST\USERDBF
启动数据库检查错误
SQL> startup
Oracle instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : D:\Oracle\ORADATA\TEST\USERDBF
先打开数据库
SQL> alter database datafile offline drop;
Database altered
SQL> alter database open;
Database altered
恢复该表空间
恢复脚本可以是恢复单个数据文件
run{
allocate channel c type disk;
restore datafile ;
recover datafile ;
sql alter database datafile online;
release channel c;
}
也可以是恢复表空间
run{
allocate channel c type disk;
restore tablespace users;
recover tablespace users;
sql alter database datafile online;
release channel c;
}
过程如下
C:\>rman
Recovery Manager: Release Production
RMAN> connect rcvcat rman/rman@back
RMAN: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN: connected to target database: TEST (DBID=)
RMAN> run{
> allocate channel c type disk;
> restore datafile ;
> recover datafile ;
> sql alter database datafile online;
> release channel c;
> }
//输出内容冗长省略编者
RMAN>
检查数据是否完整
SQL> alter database open;
Database altered
SQL> select * from test;
A
说明
RMAN也可以实现单个表空间或数据文件的恢复恢复过程可以在mount下或open方式下如果在open方式下恢复可以减少down机时间;
如果损坏的是一个数据文件建议offline并在open方式下恢复;
这里可以看到RMAN进行数据文件与表空间恢复的时候代码都比较简单而且能保证备份与恢复的可靠性所以建议采用RMAN的备份与恢复
丢失多个数据文件实现整个数据库的恢复
OS备份方案
OS备份归档模式下损坏（丢失）多个数据文件进行整个数据库的恢复
连接数据库创建测试表并插入记录
SQL> connect internal/password as sysdba;
Connected
SQL> create table test(a int);
Table created
SQL> insert into test values();
row inserted
SQL> commit;
Commit complete
备份数据库备份除临时数据文件后的所数据文件
SQL> @hotbaksql 或在DOS下 svrmgrl @hotbaksql
继续在测试表中插入记录
SQL> insert into test values();
row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
关闭数据库模拟丢失数据文件
<
