Oracle备份/恢复案例03——全库恢复
一、
OS备份方案
OS备份归档模式下损坏(丢失)多个数据文件，进行整个数据库的恢复
1、连接数据库，创建测试表并插入记录
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 13 10:03:27 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/sys as sysdba;
Connected.
SQL> create table test(a int) tablespace users;
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete
2、备份数据库，备份除临时数据文件后的所数据文件
SQL> @D:\test\hotbak.sql
3、继续在测试表中插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
-----------------------------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
4、关闭数据库，模拟丢失数据文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF
C:>del D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
C:>del D:\ORACLE\ORADATA\DODO\INDX01.DBF
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
5、启动数据库，检查错误
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF'
详细信息可以查看报警文件
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Tue Jan 13 16:58:04 2009
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE\ORADATA\DODO\INDX01.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Tue Jan 13 16:58:04 2009
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\DODO\TOOLS01.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Tue Jan 13 16:58:04 2009
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\DODO\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
通过查询v$recover_file可以看到
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ----------
1 ONLINE ONLINE FILE NOT FOUND 0
4 ONLINE ONLINE FILE NOT FOUND 0
5 ONLINE ONLINE FILE NOT FOUND 0
6 ONLINE ONLINE FILE NOT FOUND 0
有四个数据文件需要恢复
6、拷贝备份回到原地点(restore)，开始恢复数据库(recover)
restore过程:
C:>copy D:\DATABASE\BACK\SYSTEM01.DBF D:\ORACLE\ORADATA\DODO
C:>copy D:\DATABASE\BACK\INDX01.DBF D:\ORACLE\ORADATA\DODO
C:>copy D:\DATABASE\BACK\TOOLS01.DBF D:\ORACLE\ORADATA\DODO
C:>copy D:\DATABASE\BACK\USERS01.DBF D:\ORACLE\ORADATA\DODO
Recover过程:
SQL> recover database;
Media recovery complete.
7、打开数据库，检查数据库的数据(完全恢复)
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------
1
2
说明：
1、只要有备份与归档存在，就可以实现数据库的完全恢复(不丢失数据)
2、适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复
3、恢复过程在mount下进行，如果恢复成功，再打开数据库，down机时间可能比较长一些。
二、RMAN备份方案
RMAN备份归档模式下损坏(丢失)多个数据文件，进行整个数据库的恢复
1、连接数据库，创建测试表并插入记录
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 13 10:03:27 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/sys as sysdba;
Connected.
SQL> create table test(a int) tablespace users;
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete
2、备份数据库
C:> rman cmdfile=bakup.rcv msglog=backup.log;
backup.log中记录的备份信息如下：
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> # script:bakup.rcv
2> # desc:backup all database datafile in archive with rman
3> # connect database
4> # set oracle_sid=dodo;
5> connect target sys/sys;
6> # start backup database
7> run{
8> allocate channel c1 type disk;
9> backup full tag 'dbfull' format 'D:\DATABASE\RMAN\full%u_%s_%p' database
10> include current controlfile;
11> sql 'alter system archive log current';
12> release channel c1;
13> }
14> # end
15>
connected to target database: DODO (DBID=472976704)
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=17 devtype=DISK
Starting backup at 2009-01-13
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00008 name=D:\ORACLE\ORADATA\DODO\WXQ_TBS.ORA
input datafile fno=00006 name=D:\ORACLE\ORADATA\DODO\USERS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\DODO\UNDOTBS01.DBF
input datafile fno=00007 name=D:\ORACLE\ORADATA\DODO\XDB01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\DODO\INDX01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\DODO\DRSYS01.DBF
input datafile fno=00009 name=D:\ORACLE\ORADATA\DODO\RECOVERY_TBS.ORA
input datafile fno=00005 name=D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
channel c1: starting piece 1 at 2009-01-13
channel c1: finished piece 1 at 2009-01-13
piece handle=D:\DATABASE\RMAN\FULL1QK4NINF_58_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:37
Finished backup at 2009-01-13
Starting Control File and SPFILE Autobackup at 2009-01-13
piece handle=D:\ORACLE\ORADATA\DODO\RMANBACK\CTL_C-472976704-20090113-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2009-01-13
sql statement: alter system archive log current
released channel: c1
Recovery Manager complete.
3、继续在测试表中插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
4、关闭数据库，模拟丢失数据文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF
C:>del D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
C:>del D:\ORACLE\ORADATA\DODO\INDX01.DBF
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
5、启动数据库，检查错误
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF'
查询v$recover_file
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ----------
1 ONLINE ONLINE FILE NOT FOUND 0
4 ONLINE ONLINE FILE NOT FOUND 0
5 ONLINE ONLINE FILE NOT FOUND 0
6 ONLINE ONLINE FILE NOT FOUND 0
可以知道有四个数据文件需要恢复
6、利用RMAN进行恢复
C:>rman
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect rcvcat rman/rman
connected to recovery catalog database
RMAN> connect target sys/sys
connected to target database: DODO (DBID=472976704)
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database;
5> sql 'alter database open';
6> release channel c1;
7> }
allocated channel: c1
channel c1: sid=12 devtype=DISK
Starting restore at 2009-01-13
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\DODO\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\DODO\DRSYS01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\DODO\INDX01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\DODO\USERS01.DBF
restoring datafile 00007 to D:\ORACLE\ORADATA\DODO\XDB01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\DODO\WXQ_TBS.ORA
restoring datafile 00009 to D:\ORACLE\ORADATA\DODO\RECOVERY_TBS.ORA
channel c1: restored backup piece 1
piece handle=D:\DATABASE\RMAN\FULL1QK4NINF_58_1 tag=DBFULL params=NULL
channel c1: restore complete
Finished restore at 2009-01-13
Starting recover at 2009-01-13
starting media recovery
archive log thread 1 sequence 43 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_43.DBF
archive log thread 1 sequence 44 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_44.DBF
archive log thread 1 sequence 45 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_45.DBF
archive log filename=D:\ORACLE\ORADATA\DODO\ARCHIVE\1_43.DBF thread=1 sequence=43
media recovery complete
Finished recover at 2009-01-13
sql statement: alter database open
released channel: c1
7、检查数据库的数据(完全恢复)
SQL> select * from test;
A
--------------------------
1
2
说明：
1、只要有备份与归档存在，RMAN也可以实现数据库的完全恢复(不丢失数据)
2、同OS备份数据库恢复，适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复
3、目标数据库在mount下进行，如果恢复成功，再打开数据库。
4、RMAN的备份与恢复命令相对比较简单并可靠，建议有条件的话，都采用RMAN进行数据库的备份。