《Oracle备份方案分析和总结.docx》由会员分享，可在线阅读，更多相关《Oracle备份方案分析和总结.docx（26页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、一.理解什么是数据库恢复当我们使用一个数据库时，总希望数据库的内容是可靠的、正确的，但由于计 算机系统的故障(硬件故障、软件故障、网络故障、进程故障和系统故障)影响 数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库中全 部或部分数据丢失。因此当发生上述故障后，希望能重构这个完整的数据库，该 处理称为数据库恢复。恢复过程大致可以分为复原(Restore)与恢复(Recover)过 程。数据库恢复可以分为以下两类：1.1 实例故障的一致性恢复当实例意外地(如掉电、后台进程故障等)或预料地(发出SHUTDOUM ABORT语 句)中止时出现实例故障，此时需要实例恢复。实例恢复将数
2、据库恢复到故障之 前的事务一致状态。如果在在线后备发现实例故障，则需介质恢复。在其它情况 Oracle在下次数据库起动时(对新实例装配和打开)，自动地执行实例恢复。 如果需要，从装配状态变为打开状态，自动地激发实例恢复，由下列处理：(1)为了解恢复数据文件中没有记录的数据，进行向前滚。该数据记录在 在线日志，包括对回滚段的内容恢复。(2)回滚未提交的事务，按步1重新生成回滚段所指定的操作。(3)释放在故障时正在处理事务所持有的资源。(4)解决在故障时正经历一阶段提交的任何悬而未决的分布事务。1. 2介质故障或文件错误的不一致恢复介质故障是当一个文件、一个文件的部分或磁盘不能读或不能写时出现的故
3、障。 文件错误一般指意外的错误导致文件被删除或意外事故导致文件的不一致。这种 状态下的数据库都是不一致的，需要DBA手工来进行数据库的恢复，这种恢复有 两种形式，决定于数据库运行的归档方式和备份方式。(1)完全介质恢复可恢复全部丢失的修改。一般情况下需要有数据库的备份且 数据库运行在归档状态下并且有可用归档日志时才可能。对于不同类型的错误， 有不同类型的完全恢复可使用，其决定于毁坏文件和数据库的可用性。(2)不完全介质恢复是在完全介质恢复不可能或不要求时进行的介质恢复。重 构受损的数据库，使其恢复介质故障前或用户出错之前的一个事务一致性状态。 不完全介质恢复有不同类型的使用，决定于需要不完全介
4、质恢复的情况，有下列 类型：基于撤消、基于时间和基于修改的不完全恢复。恢复该数据文件SQL recover datafile 3;ORA-00279: change 1053698 generated at 05/07/2003 17:51:26 needed for thread 1 0RA-00289: suggestion :D:/0racle/0RADATA/TEST/ARCHIVE/TESTT001S00304. ARC0RA-00280: change 1053698 for thread 1 is in sequence #304Specify log: =suggested |
5、 filename | AUTO | CANCEL) AUTO0RA-00279: change 1053701 generated at 05/07/2003 17:51:39 needed for thread 1 0RA-00289: suggestion :D:/0racle/0RADATA/TEST/ARCHIVE/TESTT001S00305. ARC0RA-00280: change 1053701 for thread 1 is in sequence #305 0RA-00278: log fileD:/0racle/0RADATA/TEST/ARCHIVE/TESTT001
6、S00304. ARC，no longer needed for this recovery Log applied.Media recovery complete.恢复成功，联机该数据文件SQL alter database datafile 3 online;Database altered.7、检查数据库的数据（完全恢复） SQL select * from test;A说明:1、采用热备份，需要运行在归档模式下，可以实现数据库的完全恢复，也就是 说，从备份后到数据库崩溃时的数据都不会丢失；2、可以采用全备份数据库的方式备份，对于特殊情况，也可以只备份特定的数据文件，如只备份用户表空间（
7、一般情况下对于某些写特别频繁的数据文件, 可以单独加大备份频率）；3、如果在恢复过程中，发现损坏的是多个数据文件，即可以采用一个一个数据.文件的恢复方法（第5步中需要对数据文件一一脱机，第6步中需要对数据文 件分别恢复），也可以采用整个数据库的恢复方法；4、如果是系统表空间的损坏，不能采用此方法。4. 2. 2 RMAN备份方案RMAN也可以进行联机备份，而且备份与恢复方法将比OS备份更简单可靠。1、连接数据库，创建测试表并插入记录SQL connect internal/pas sword as sysdba;Connected.SQL create table test(a int) ta
8、blespace users;Table createdSQL insert into test values(1); 1 row insertedSQL commit;Commit complete2、备份数据库表空间usersC:/rmanRecovery Manager: Release 8.1.6.0. 0 - ProductionRMAN connect rcvcat rman/rmanbackRMAN-06008: connected to recovery catalog databaseRMAN connect target internal/virpureRMAN-06005
9、: connected to target database: TEST (DBID=1788174720)RMAN run2 allocate channel cl type disk;3 backup tag 1 tsuser, formatJ d:/backup/tsuser_%u_%s_%p,4 tablespace users;5 release channel cl;6 RMAN-03022: compiling command: allocateRMAN-03023: executing command: allocateRMAN-08030: allocated channel
10、: clRMAN-08500: channel cl: sid=16 devtype=DISKRMAN-03022: compiling command: backupRMAN-03025: performing implicit partial resync of recovery catalogRMAN-03023: executing command: partial resyncRMAN-08003: starting partial resync of recovery catalogRMAN-08005: partial resync completeRMAN-03023: exe
11、cuting command: backupRMAN-08008: channel cl: starting full datafile backupsetRMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03RMAN-08010: channel cl: specifying datafile(s) in backupsetRMAN-08522: input datafile fno=00003name=D:/Orac1e/ORADATA/TEST/USERO1. DBFRMAN-08013: channe1 c
12、l: piece 1 createdRMAN-08503: piece handle=D: /BACKUP/TSUSER_05EN93AC_5_l comment=NONERMAN-08525: RMAN-03023:RMAN-08003:RMAN-08005:RMAN-03022:RMAN-03023:RMAN-08031: RMANbackup set complete, elapsed time: 00:00:01 executing command: partial resyncstarting partial resync of recovery catalog partial re
13、sync complete compiling command: release executing command: release released channel: cl3、继续在测试表中插入记录SQL insert into test values(2);1 row insertedSQL commit;Commit completeSQL select * from test;A 12SQL alter system switch logfile;System altered.SQLr1* alter system switch logfile;System altered.4、关闭
14、数据库，模拟丢失数据文件SQL shutdown immediate;Database closed.Database dismounted.Oracle instance shut downC:/del D:/Orac1e/ORADATA/TEST/USERO1. DBF5、启动数据库,检查错误10202036470924854876161638400077824bytes bytes bytes bytes bytesSQL startupOracle instance started.Total System Global AreaFixed SizeVariable SizeDatab
15、ase BuffersRedo BuffersDatabase mounted.ORA-01157: cannot identify/lock data file 3 - see DBWR trace file0RA-01110: data file 3: , D:/Oracle/ORADATA/TEST/USEROL DEF16、先打开数据库SQL alter database datafile 3 offline drop;Database altered.SQL alter database open;Database altered.7、恢复该表空间恢复脚本可以是恢复单个数据文件run
16、allocate channel cl type disk;restore datafile 3;recover datafile 3;sql alter database datafile 3 online，release channel cl;)也可以是,恢复表空间runallocate channel cl type disk;restore tablespace users;recover tablespace users;sql J alter database datafile 3 online，release channel cl;)过程如下：C:/rmanRecovery Ma
17、nager: Release 8.1.6.0.0 - ProductionRMAN connect rcvcat rman/rmanbackRMAN-06008: connected to recovery catalog databaseRMAN connect target internal/virpureRMAN-06005: connected to target database: TEST (DBID=1788174720)RMAN run2 allocate channel cl type disk;3 restore datafile 3;4 recover datafile
18、3;5 sql J alter database datafile 3 online，;6 release channel cl;7 输出内容冗长，省略一编者 RMAN8、检查数据是否完整SQL alter database open; Database altered.SQL select * from test;A说明:1、RMAN也可以实现单个表空间或数据文件的恢复，恢复过程可以在mount下或 open方式下，如果在open方式下恢复，可以减少down机时间;2、如果损坏的是一个数据文件，建议offline并在open方式下恢复;3、这里可以看到，RMAN进行数据文件与表空间恢复的时候
19、，代码都比较简单,而且能保证备份与恢复的可靠性，所以建议采用RMAN的备份与恢复.4.3丢失多个数据文件，实现整个数据库的恢复.4. 3.1 OS备份方案OS备份归档模式下损坏（丢失）多个数据文件，进行整个数据库的恢复 1、连接数据库，创建测试表并插入记录SQL connect internal/password as sysdba;Connected.SQL create table test(a int);Table createdSQL insert into test values(1); 1 row insertedSQL commit;Commit complete2、备份数据库，
20、备份除临时数据文件后的所数据文件 SQL hotbak. sql 或在 DOS 下 svrmgrl hotbak. sql3、继续在测试表中插入记录SQL insert into test values(2); 1 row insertedSQL commit;Commit completeSQL select * from test;ASQL alter system switch logfile; System altered.SQL alter system switch logfile; System altered.4、关闭数据库，模拟丢失数据文件 SQL shutdown imme
21、diate;Database closed.Database dismounted.Oracle instance shut downC:/del D:/Oracle/ORADATA/TEST/SYSTEMOL DBF C:/del D:/Orac1e/ORADATA/TEST/INDXO1. DBF C:/del D:/Orac1e/ORADATA/TEST/TOOLSO1. DBFC:/del D:/Orac1e/ORADATA/TEST/RBSO1. DBF 模拟媒体毁坏(这里删除多个数据文件)5、启动数据库，检查错误SQL STARTUPOracle instance started.
22、102020364 bytes70924 bytes85487616 bytes16384000 bytes77824 bytesTotal System Global AreaFixed SizeVariable SizeDatabase BuffersRedo BuffersDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: D:/Oracle/ORADATA/TEST/SYSTEMOL DBF详细信息可以查看报警文件ORA-115
23、7 signalled during: ALTER DATABASE OPEN.Thu May 08 09:39:36 2003Errors in file D:/Oracle/admin/test/bdump/testDBWO. TRC:ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: D:/Oracle/ORADATA/TEST/SYSTEMOL DBFORA-27041: unable to open file0SD-04002: unable to open
24、file0/S-Error: (OS 2)系统找不到指定的文件。Thu May 08 09:39:36 2003Errors in file D:/Oracle/admin/test/bdump/testDBWO. TRC:ORA-01157: cannot identify/lock data file 2 - see DBWR trace fileORA-01110: data file 2: D:/Oracle/ORADATA/TEST/RBSOL DBFORA-27041: unable to open file0SD-04002: unable to open fileO/S-Err
25、or: (OS 2)系统找不到指定的文件。Thu May 08 09:39:36 2003Errors in file D:/Oracle/admin/test/bdump/testDBWO. TRC:ORA-01157: cannot identify/lock data file 5 - see DBWR trace file 0RA-01110: data file 5: J D:/0racle/0RADATA/TEST/T00LS01. DBF, ORA-27041: unable to open file 0SD-04002: unable to open file 0/S-Erro
26、r: (OS 2)系统找不到指定的文件。Thu May 08 09:39:36 2003Errors in file D:/Oracle/admin/test/bdump/testDBWO. TRC: ORA-01157: cannot identify/lock data file 6 - see DBWR trace file 0RA-01110: data file 6: J D:/0racle/0RADATA/TEST/INDX01. DB ORA-27041: unable to open file 0SD-04002: unable to open file 0/S-Error:
27、(OS 2)系统找不到指定的文件。通过查询v$recover_file可以看到SQL select * from v$recover_file;FILE# ONLINE ERRORCHANGE# TIME1 ONLINE2 ONLINE5 ONLINE6 ONLINE0000FILE NOT FOUNDFILE NOT FOUNDFILE NOT FOUNDFILE NOT FOUND有四个数据文件需要恢复6、拷贝备份回到原地点(restore),开始恢复数据库(recover) restore 过程：C:/copy D:/DATABAK/SYSTEM01. DBF D:/Oracle/ORA
28、DATA/TEST/C:/copy D:/DATABAK/TEST/INDX01. DBF D:/Orac1e/ORADATA/TEST/C:/copy D:/DATABAK/TEST/T00LS01. DBF D:/Oracle/ORADATA/TEST/C: /copy D: /DATABAK/TEST/RBS01. DBF. DBF D: /Orac 1 e/ORADATA/TEST/Recover 过程：SQL recover database;ORA-00279: change 1073849 generated at 05/08/2003 08:58:35 needed for t
29、hread 1 0RA-00289: suggestion :D:/Orac1e/ORADATA/TEST/ARCHIVE/TESTTOO1S00311. ARC0RA-00280: change 1073849 for thread 1 is in sequence #311Specify log: =suggested | filename | AUTO | CANCEL auto0RA-00279: change 1073856 generated at 05/08/2003 09:03:27 needed for thread 10RA-00289: suggestion :D:/0r
30、acle/0RADATA/TEST/ARCHIVE/TESTT001S00312. ARC0RA-00280: change 1073856 for thread 1 is in sequence #3120RA-00278: log fileD:/0racle/0RADATA/TEST/ARCHIVE/TESTT001S00311. ARC no longer needed for this recovery0RA-00279: change 1073858 generated at 05/08/2003 09:11:43 needed for thread 10RA-00289: sugg
31、estion :D:/Orac1e/ORADATA/TEST/ARCHIVE/TESTTOO1S00313. ARC0RA-00280: change 1073858 for thread 1 is in sequence #3130RA-00278: log fileD:/0racle/0RADATA/TEST/ARCHIVE/TESTT001S00312. ARC, no longer needed for this recovery0RA-00279: change 1073870 generated at 05/08/2003 09:11:46 needed for thread 10
32、RA-00289: suggestion :D:/0racle/0RADATA/TEST/ARCHIVE/TESTT001S00314. ARC0RA-00280: change 1073870 for thread 1 is in sequence #3140RA-00278: log fileD:/Orac1e/ORADATA/TEST/ARCHIVE/TESTTOO1S00313. ARC，no longer needed for this recoveryLog applied.Media recovery complete.7、打开数据库，检查数据库的数据（完全恢复）SQL alte
33、r database open;Database altered.SQL select * from test;A 12说明：1、只要有备份与归档存在，就可以实现数据库的完全恢复(不丢失数据)；2、适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复；3、恢复过程在mount下进行，如果恢复成功，再打开数据库，down机时间可能 比较长一些。4. 3. 2 RMAN备份方案RMAN备份归档模式下损坏(丢失)多个数据文件，进行整个数据库的恢复1、连接数据库，创建测试表并插入记录SQL connect internal/pas sword as sysdba;Connected.SQL c
34、reate table test(a int);Table createdSQL insert into test values(1);1 row insertedSQL commit;Commit complete2、备份数据库DOS 下 C:/ rman cmdfile=bakup. revmsglog=backup. log;以下是backup, log内容。Recovery Manager: Release 8.1.6.0. 0 - ProductionRMAN # script:bakup. rev2 #creater:chenjiping3 #date:5. 8. 20034 #
35、desc:backup all database datafile in archive with rman56 connect database7 connect reveat rman/rmanback;8 connect target internal/virpure;910 ttstart backup database11 run12 allocate channel cl type disk;13 backup full tag dbfull format J d:/backup/full%u_%s_%p, database14 include current controlfil
36、e;15 sql alter system archive log current,;16 release channel cl;17 18 Send19RMAN-06008: connected to recovery catalog databaseRMAN-06005:RMAN-03022:RMAN-03023:RMAN-08030:connected to target database: TEST (DBID=1788174720)compiling executing allocatedcommand: allocate command: allocate channel: clR
37、MAN-08500: channel cl: sid=15 devtype=DISKRMAN-03022: compiling command: backupRMAN-03023: executing command: backupRMAN-08008: channel cl: starting full datafile backupsetRMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03RMAN-08010: channel cl: specifying datafile(s) in backupsetRM
38、AN-08522: input datafile fno=00002name=D:/Orac1e/ORADATA/TEST/RBSO1. DBFRMAN-08522: input datafile fno=00001name=D:/Oracle/ORADATA/TEST/SYSTEMOl. DBFRMAN-08011: including current controlfile in backupsetRMAN-08522: input datafile fno=00005name=D:/Orac1e/ORADATA/TEST/TOOLSO1. DBFRMAN-08522: input dat
39、afile fno=00004name=D:/Orac1e/ORADATA/TEST/TEMPO1. DBFRMAN-08522: input datafile fno=00006name=D:/Orac1e/ORADATA/TEST/INDXO1. DBFRMAN-08522: input datafile fno=00003name=D:/Orac1e/ORADATA/TEST/USERO1. DBFRMAN-08013: channe1 cl: piece 1 createdRMAN-08503: piece handle=D: /BACKUP/FULL04EN5UG0_4_l comm
40、ent=NONERMAN-08525: backup set complete, elapsed time: 00:01:16RMAN-03023: executing command: partial resyncRMAN-08003: starting partial resync of recovery catalogRMAN-08005: partial resync completeRMAN-03022: compiling command: sqlRMAN-06162: sql statement: alter system archive log currentRMAN-0302
41、3: executing command: sqlRMAN-03022: compiling command: releaseRMAN-03023: executing command: releaseRMAN-08031: released channel: clRecovery Manager complete. 到这里表示备份成功。3、继续在测试表中插入记录SQL insert into test values(2);1 row insertedSQL commit;Commit completeSQL select * from test;基于撤消(CANCEL)恢复：在某种情况，不完
42、全介质恢复必须被控制，DBA可撤消在指定点的操作。基于撤消的恢复地在一个或多个日志组(在线的或归档的) 已被介质故障所破坏，不能用于恢复过程时使用，所以介质恢复必须控制，以致在 使用最近的、未损的日志组于数据文件后中止恢复操作。基于时间(TIME)和基于修改(SCN)的恢复：如果DBA希望恢复到过去的某个指定点，是一种理想的不完全介质恢复，一般发生在恢复到某个特定操作之前， 恢复到如意外删除某个数据表之前。第二章.数据库恢复案例测试环境2. 1数据库环境以下的所有案例都是通过测试经过，环境为：OS： Windows 2000 ServerDB： Oracle 816 DBNAME：TEST数据
43、文件：SQL select file#, status, enabled, name from v$datafile;FILE# STATUS ENABLED NAME1 SYSTEM READ WRITE D: /Oracle/ORADATA/TEST/SYSTEMOl. DBF2 ONLINE READ WRITE D:/Orac1e/ORADATA/TEST/RBSO1.DBF3 ONLINE READ WRITE D:/Oracle/ORADATA/TEST/USERSOl. DBF 4 ONLINE READ WRITE D:/0racle/0RADATA/TEST/TEMP01.
44、DBF5 ONLINE READ WRITE D:/Oracle/ORADATA/TEST/TOOLSOl. DBF6 ONLINE READ WRITE D:/Oracle/ORADATA/TEST/INDXO1. DBF控制文件：SQL select * from v$controlfile;STATUS NAMED:/Orac1e/ORADATA/TEST/CONTROLO1. CTL D:/0racle/0RADATA/TEST/C0NTR0L02. CTLD:/0racle/0RADATA/TEST/C0NTR0L03. CTL联机日志：SQL select * from v$log
45、file;GROUP# STATUSMEMBER12SQLalter system switch logfile;System altered.SQL alter system switch logfile;System altered.4、关闭数据库，模拟丢失数据文件SQL shutdown immediate;Database closed.Database dismounted.Oracle instance shut downC:/del D: /Oracle/ORADATA/TEST/SYSTEMOL DBFC:/del D:/Orac1e/ORADATA/TEST/INDXO1.
46、DBFC:/del D:/Orac1e/ORADATA/TEST/TOOLSO1. DBFC:/del D:/Orac1e/ORADATA/TEST/RBSO1. DBF 5、启动数据库，检查错误SQL STARTUPOracle instance started.102020364 bytes70924 bytes85487616 bytes16384000 bytes77824 bytesTotal System Global AreaFixed SizeVariable SizeDatabase BuffersRedo BuffersDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: D:/Oracle/ORADATA/TEST/SYSTEMOL DBF查询 v$recover_fileSQL select * from v$recover_file;FILE#