整个数据库的恢复(丢失多个数据文件时)
1.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.
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
Fixed Size Variable Size Database Buffers Redo Buffers 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; ---------- ------- ------- -------------------- ---------- ---------- 有四个数据文件需要恢复 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机时间可能比较长一些。 2.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.
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.
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
Fixed Size Variable Size Database Buffers Redo Buffers 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; ---------- ------- ------- -------------------- ---------- ---------- 可以知道有四个数据文件需要恢复 6、利用RMAN进行恢复 C:>rman Recovery Manager: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002,
Oracle Corporation.
RMAN> connect rcvcat rman/rman connected to recovery
catalog database
RMAN> connect target sys/sys connected to target database: 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进行数据库的备份。 |
|