Oracle 数据库备份恢复实例.doc

仅仅丢失一个普通用户数据文件的恢复A(联机恢复) （例如，丢失D:BACKUPDBUSERS01.DBF） 准备工作, 通过下面的工作，如果完全恢复，应该可以看到；insert into test1 values(2); SQL conn lunar/lunar SQL select * from tab; TESTBACKUP3 TABLE SQL create table test1 (a number); SQL insert into test1 values(1); SQL alter system switch logfile; SQL commit; SQL alter system switch logfile; SQL insert into test1 values(2); SQL commit; SQL alter system switch logfile; SQL conn internalSQL archive log list数据库日志模式 存档模式 自动存档 启用 存档终点 d:BACKUPDBarchive 最早的概要信息日志序列 3 下一个存档日志序列 5 当前日志序列 5 shutdown abort关闭例程，模拟数据文件丢失 SQL shutdown abort ORACLE 例程已经关闭。 Mount数据库 SQL startup mount 数据库装载完毕。 使损坏的数据文件脱机 SQL alter database datafile D:BACKUPDBUSERS01.DBF offline; 打开数据库 SQL alter database open; 拷贝刚才热备的数据文件（USERS01.DBF） 恢复损坏的数据文件 SQL recover datafile D:BACKUPDBUSERS01.DBF; ORA-00279: ? 424116 (? 10/20/2002 20:42:04 ?) ? 1 ? ORA-00289: ?: D:BACKUPDBARCHIVEBACKUPT001S00001.ARC ORA-00280: ? 424116 ? 1 ? # 1 ? 指定日志: =suggested | filename | AUTO | CANCEL auto ORA-00279: ? 424125 (? 10/20/2002 20:44:14 ?) ? 1 ? ORA-00289: ?: D:BACKUPDBARCHIVEBACKUPT001S00002.ARC ORA-00280: ? 424125 ? 1 ? # 2 ? ORA-00278: ? D:BACKUPDBARCHIVEBACKUPT001S00001.ARC .已应用的日志。 完成介质恢复。 使恢复完成的数据文件联机 SQL alter database datafile D:BACKUPDBUSERS01.DBF online; 验证恢复的结果：完全恢复 SQL select * from tab; TNAME TABTYPE CLUSTERID SQL select * from test1; 说明： 1. shutdown abort关闭例程，模拟数据文件丢失 2. Mount数据库 3. 使损坏的数据文件脱机 4. 打开数据库 5. 拷贝刚才热备的数据文件（USERS01.DBF） 6. 恢复损坏的数据文件 7. 使恢复完成的数据文件联机shutdown immedate，恢复全部数据文件(不包括control和redo) （把热备的数据文件拷贝回来，不包括control和redo） SQL conn internal SQL shutdown immediate; 复制全部热备的数据文件过来（完全恢复成功！）mount数据库SQL startup mount完全恢复数据库 SQL recover database; ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1 ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.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:BACKUPDBARCHIVEBACKUPT001S00002.ARC ORA-00280: change 424125 for thread 1 is in sequence #2 ORA-00278: log file D:BACKUPDBARCHIVEBACKUPT001S00001.ARC no longer needed for this recovery .Log applied. Media recovery complete. 打开数据库 SQL alter database open; 验证恢复结果：完全恢复 SQL conn lunar/lunar SQL select * from test1; 完全恢复成功！ 说明： 1.复制全部热备的数据文件过来 2.mount数据库 3.完全恢复数据库 4.打开数据库shutdown abort的情况，恢复全部控制文件和数据文件(不包括redo) 准备工作 （这样，insert into test1 values(13);就是没有提交的数据了，如果完全恢复，应该一直可以看到insert into test1 values(12);） SQL conn lunar/lunar SQL select * from test1; SQL insert into test1 values(12); commit;SQL insert into test1 values(13); 单开一个session，用来shutdow abortE:sqlplus internal SQL shutdown abort ORACLE 例程已经关闭。 拷贝所有的控制文件和数据文件（不包括redo） mount数据库，按照提示重建口令文件SQL startup mount ORACLE instance started. ORA-01991: invalid password file d:oracle1ora81DATABASEPWDbackup.ORA SQL hostE:cd d:oracle1ora81DATABASE D:oracle1ora81databasedel PWDbackup.ORA D:oracle1ora81databaseorapwd file=d:oracle1ora81DATABASEPWDbackup.ORA pa ssword=oracle entries=10/* orapwd Usage: orapwd file= password= entries= where file - name of password file (mand), password - password for SYS and INTERNAL (mand), entries - maximum number of distinct DBAs and OPERs (opt), There are no spaces around the equal-to (=) character. */D:oracle1ora81databaseexit 这时，试图完全恢复数据库是不成功的 SQL recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done 用to trace备份控制文件 SQLalter database backup controlfile to trace; SQLshutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. 找到并且编辑控制文件STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE BACKUP NORESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 254 MAXINSTANCES 1 MAXLOGHISTORY 453 LOGFILE GROUP 1 D:BACKUPDBREDO01.LOG SIZE 1M, GROUP 2 D:BACKUPDBREDO02.LOG SIZE 1M, GROUP 3 D:BACKUPDBREDO03.LOG SIZE 1M DATAFILE D:BACKUPDBSYSTEM01.DBF, D:BACKUPDBRBS01.DBF, D:BACKUPDBUSERS01.DBF, D:BACKUPDBTEMP01.DBF, D:BACKUPDBTOOLS01.DBF, D:BACKUPDBINDX01.DBF CHARACTER SET ZHS16GBK; RECOVER DATABASE ALTER SYSTEM ARCHIVE LOG ALL; ALTER DATABASE OPEN; 重建控制文件 SQL startup nomount SQL D:BACKUPDBudumpORA01532.sqlORA-01081: cannot start already-running ORACLE - shut it down first Control file created. ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1 ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.ARC ORA-00280: change 424112 for thread 1 is in sequence #1 ORA-00308: cannot open archived log ALTER ORA-27041: unable to open file OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。ALTER DATABASE OPEN * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: D:BACKUPDBSYSTEM01.DBF shutdown immediate，然后重新恢复数据库 SQL shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL startup mount ORACLE instance started. 完全恢复数据库 SQL recover database; ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1 ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.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:BACKUPDBARCHIVEBACKUPT001S00002.ARC ORA-00280: change 424125 for thread 1 is in sequence #2 ORA-00278: log file D:BACKUPDBARCHIVEBACKUPT001S00001.ARC no longer needed for this recovery .Log applied. Media recovery complete. 打开数据库 SQL alter database open; SQL conn lunar/lunar SQL select * from test1; 说明： 1.拷贝所有的控制文件和数据文件（不包括redo） 2.mount数据库，按照提示重建口令文件 3.这时，试图完全恢复数据库是不成功的 4.用to trace备份控制文件 5.找到并且编辑控制文件 6.重建控制文件 7.shutdown immediate，然后重新恢复数据库 8.完全恢复数据库 9.打开数据库仅仅丢失一个普通用户数据文件的恢复B(脱机恢复) 准备工作 按照下面的输入，如果全部恢复，应该可以看到insert into test1 values(13)，因为insert into test1 values(14)没提交。 SQL conn lunar/lunarSQL insert into test1 values(13); SQL insert into test1 values(14); Shutdown immediate，然后模拟数据文件丢失 单开一个session，执行shutdown immediate（保证insert into test1 values(14);没有被隐式提交）E:sqlplus internalSQLshutdown immediate ORACLE 例程已经关闭。 模拟数据文件丢失，然后用热备覆盖这个文件 mount数据库 E:sqlplus internal SQLshutdown immediate ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist SQL startup mount 使损坏的数据文件脱机 SQLalter database datafile D:BACKUPDBUSERS01.DBF offline; Database altered. 恢复数据文件 SQL recover datafile D:BACKUPDBUSERS01.DBF; ORA-00279: change 424116 generated at 10/20/2002 20:42:04 needed for thread 1 ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.ARC ORA-00280: change 424116 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:BACKUPDBARCHIVEBACKUPT001S00002.ARC ORA-00280: change 424125 for thread 1 is in sequence #2 ORA-00278: log file D:BACKUPDBARCHIVEBACKUPT001S00001.ARC no longer needed for this recovery Log applied. Media recovery complete. 使恢复的数据文件联机 SQLalter database datafile D:BACKUPDBUSERS01.DBF online; 打开数据库 SQLalter database open; Database altered. 这时需要重新启动数据库，并完全恢复数据库 SQL conn lunar/lunar SQL select count(*) from test; select count(*) from test * ERROR at line 1: ORA-00942: table or view does not exist SQL conn internal SQL shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 重新启动数据库， SQL startup ORACLE instance started. 用recover database再次恢复数据库 SQL conn internal SQL recover database; ORA-00283: recovery session canceled due to errors ORA-01124: cannot recover data file 1 - file is in use or recovery ORA-01110: data file 1: D:BACKUPDBSYSTEM01.DBF 重新使恢复的表空间联机 SQL alter database datafile D:BACKUPDBUSERS01.DBF online; SQL conn lunar/lunar SQL select * from test1; ok.验证恢复结果：完全恢复 说明： 1.用热备覆盖这个文件 2.mount数据库 3.使损坏的数据文件脱机 4.恢复数据文件 5.使恢复的数据文件联机 6.打开数据库 7.这时需要重新启动数据库，并完全恢复数据库 8.重新启动数据库， 9.用recover database再次恢复数据库 10.重新使恢复的表空间联机shutdown abort后，丢失全部文件(除了archive log和init.ora)即，丢失了全部：数据文件、控制文件和redo log file 准备工作 下面的信息说明了如果是完全恢复，可以看到insert into test1 values(16);，否则可以看到15，就是被归档的那个。17因为没有提交，是不会被恢复的。 SQL conn internal SQL archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination d:BACKUPDBarchive Oldest online log sequence 14 Next log sequence to archive 16 Current log sequence 16 SQL conn lunar/lunar SQL select * from test1 where a10; SQL insert into test1 values(15); SQL alter system switch logfile; System altered. SQL insert into test1 values(16); SQL insert into test1 values(17); 新开一个session，进行shutdown abort E:sqlplus internal SQL shutdown abort ORACLE 例程已经关闭。 把热备的数据文件和控制文件拷贝过来 mount数据库 E:sqlplus internal SQL startup mount ORACLE instance started. ORA-01991: invalid password file d:oracle1ora81DATABASEPWDbackup.ORA 根据提示重建口令文件 SQL host E:del d:oracle1ora81DATABASEPWDbackup.ORA E:orapwd file=d:oracle1ora81DATABASEPWDbackup.ORA password=oracle entries= 10 用to trace备份控制文件 SQL alter database backup controlfile to trace; Database altered. 找到这个跟踪文件并编辑它 STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE BACKUP NORESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 254 MAXINSTANCES 1 MAXLOGHISTORY 453 LOGFILE GROUP 1 D:BACKUPDBREDO01.LOG SIZE 1M, GROUP 2 D:BACKUPDBREDO02.LOG SIZE 1M, GROUP 3 D:BACKUPDBREDO03.LOG SIZE 1M DATAFILE D:BACKUPDBSYSTEM01.DBF, D:BACKUPDBRBS01.DBF, D:BACKUPDBUSERS01.DBF, D:BACKUPDBTEMP01.DBF, D:BACKUPDBTOOLS01.DBF, D:BACKUPDBINDX01.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:BACKUPDBudumpORA02176.sql ORA-01081: cannot start already-running ORACLE - shut it down first CREATE CONTROLFILE REUSE DATABASE BACKUP NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01565: error in identifying file D:BACKUPDBREDO01.LOG ORA-27041: unable to open file OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-01507: database not mounted ALTER SYSTEM ARCHIVE LOG ALL * ERROR at line 1: ORA-01507: database not mounted ALTER DATABASE OPEN * ERROR at line 1: ORA-01507: database not mounted 可见，因为缺少所有的redo，重建控制文件是行不通的。 Mount数据库SQL alter database mount; Database altered. 用using backup controlfile进行恢复 SQL alter database mount; Database altered. SQL recover database until cancel using backup controlfile; ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1 ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.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:BACKUPDBARCHIVEBACKUPT001S00002.ARC ORA-00280: change 424125 for thread 1 is in sequence #2 ORA-00278: log file D:BACKUPDBARCHIVEBACKUPT001S00001.ARC no longer needed for this recovery ORA-00308: cannot open archived log D:BACKUPDBARCHIVEBACKUPT001S00017.ARC ORA-27041: unable to open file OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 用Open Resetlog 打开数据库 SQL alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL alter database open RESETLOGS; Database altered. 验证恢复结果：不完全恢复，redo里面的数据丢失了 SQL conn lunar/lunar SQL select * from test1 where a10; SQL conn internal SQL archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination d:BACKUPDBarchive Oldest online log sequence 0 Next log sequence to archive 1 Current log sequence 1 说明： 1.把热备的数据文件和控制文件拷贝过来 2.mount数据库 3.根据提示重建口令文件 4.用using backup controlfile进行恢复 5.用Open Resetlog 打开数据库丢失非系统非当前活动回滚段表空间中的一个数据文件 首先是做一次热备份（因为上次已经做了不完全恢复resetlogs） Microsoft Windows 2000 Version 5.00.2195 E:sqlplus internal SQLarchive log list; 数据库日志模式 存档模式 自动存档 启用 存档终点 d:BACKUPDBarchive 最早的概要信息日志序列 0 下一个存档日志序列 1 当前日志序列 1 SQL e:backupdbotheraa TO_CHAR(SYSDATE, .YY2002-10-21 08:10:22 SQL e:backupdbotherbackup_ts.sql SQL set echo off head off feedback off pagesize 0; 21-10月-02 BEGINING ARCHIVE LOG NUMBER IS : 数据库日志模式 存档模式 自动存档 启用 存档终点 d:BACKUPDBarchive 最早的概要信息日志序列 0 下一个存档日志序列 1 当前日志序列 1 Begin Backup Tablespace SYSTEM.D:BACKUPDBSYSTEM01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace RBS.D:BACKUPDBRBS01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace USERS.D:BACKUPDBUSERS01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace TEMP.D:BACKUPDBTEMP01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace TOOLS.D:BACKUPDBTOOLS01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace INDX.D:BACKUPDBINDX01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup CONTROLFILE D:BACKUPDBCONTROL01.CTL . Successed End Backup The CONTROLFILE . Begin Backup CONTROLFILE To Trace . Successed End Backup The CONTROLFILE . Before Switch Log, The Current Log is: 数据库日志模式 存档模式 自动存档 启用 存档终点 d:BACKUPDBarchive 最早的概要信息日志序列 0 下一个存档日志序列 1 当前日志序列 1 Begin Backup Switch Current Log . Successed End Switch Log . After Switch Log, The Ending Archive Log Number Is : 数据库日志模式 存档模式 自动存档 启用 存档终点 d:BACKUPDBarchive 最早的概要信息日志序列 1 下一个存档日志序列 2 当前日志序列 2 21-10月-02 SQL -set termout on; SQL select to_char(sysdate,yyyy-mm-dd hh:mm:ss) from dual;2002-10-21 08:10:54 数据准备工作1 从下面的情况看，因为改变了数据库的结构，所以，首先需要一个热备或者冷备才能进行恢复。如果已经备份，可以找回数据insert into test2 values(2);，因为2是几经commit;的，3是没有commit的，所以能找会到2。3则会丢失。 SQL alter tablespace system add datafile D:BACKUPDBSYSTEM02.DBF size 10M; 表空间已更改。 SQL alter tablespace users add datafile D:BACKUPDBUSERS02.DBF size 10M; 表空间已更改。 SQL create tablespace test datafile D:BACKUPDBtest01.dbf size 10M; 表空间已创建。 SQL archive log list; 数据库日志模式 存档模式 自动存档 启用 存档终点 d:BACKUPDBarchive 最早的概要信息日志序列 1 下一个存档日志序列 2 当前日志序列 2 SQL alter user lunar quota 10m on test; SQL create table test2(a number) tablespace test; SQL insert into test2 values(1); SQL alter system switch logfile; 系统已更改。 SQL conn lunar/lunar SQL select * from tab; TESTBACKUP3 TABLE SQL insert into test2 values(2); SQL insert into test2 values(3); 以上改动后需要作一次热备或者冷备，否则数据文件丢失后不能恢复（增加表空间，数据文件都要备份数据库） SQL conn internal SQL e:backupdbotheraa SQL select to_char(sysdate,yyyy-mm-dd hh:mm:ss) from dual; 2002-10-21 08:10:05 SQL set termout off SQL e:backupdbotherbackup_ts.sql SQL -set termout off; SQL set echo off head off feedback off pagesize 0; 21-OCT-02 BEGINING ARCHIVE LOG NUMBER IS : Database log mode Archive Mode Automatic archival Enabled Archive destination d:BACKUPDBarchive Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5 Begin Backup Tablespace SYSTEM.D:BACKUPDBSYSTEM01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace RBS.D:BACKUPDBRBS01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace USERS.D:BACKUPDBUSERS01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace TEMP.D:BACKUPDBTEMP01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace TOOLS.D:BACKUPDBTOOLS01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace INDX.D:BACKUPDBINDX01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace SYSTEM.D:BACKUPDBSYSTEM02.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace USERS.D:BACKUPDBUSERS02.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup Tablespace TEST.D:BACKUPDBTEST01.DBF . 已复制 1 个文件。 Successed End Backup This File . Begin Backup CONTROLFILE D:BACKUPDBCO