2022年数据库冷备份实验 .pdf

一、备份后完全恢复（1）对数据库做一个完全冷备份冷备份脚本（coldbak.sql）如下：#rem script.:coldbak.sql #rem desc:offline full backup database #enter database $ORACLE_HOME/bin/sqlplus /as sysdba shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. （3）启动到mount 状态，试着做recover（这就相当于将所有的冷备份文件都restore 回来的情况）SQL startup mount; ORACLE instance started. Total System Global Area 897581056 bytes Fixed Size 1222480 bytes Variable Size 520095920 bytes Database Buffers 369098752 bytes Redo Buffers 7163904 bytes Database mounted. SQL recover database; ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required 由此可知，如果冷备份包含所有的datafile,controlfile,online redo logfile，并且将所有的这些文件全部restore 后再来做recover，是不行的，oracle 会告诉你，当前数据库是一致的，无须 recover。（4）打开数据库，恢复过程结束SQL alter database open; 二、备份后不完全恢复打开数据库，建立测试表并插入测试数据SQL alter database open; Database altered. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 4 页 - - - - - - - - - SQL create table tt(id int,name varchar2(20); Table created. SQL insert into tt values(1,a); 1 row created. SQL commit; Commit complete. SQL select * from tt; ID NAME - - 1 a 切换日志，进行归档SQL alter system switch logfile; System altered. SQL / System altered. SQL / System altered. SQL / System altered. 关闭系统SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 1、恢复场景一：当前联机日志文件，控制文件都是完好的，数据文件损坏恢复方法如下：（1）restore 冷备份中的数据文件（2）启动数据库，报错SQL startup ORACLE instance started. Total System Global Area 897581056 bytes Fixed Size 1222480 bytes Variable Size 520095920 bytes Database Buffers 369098752 bytes Redo Buffers 7163904 bytes Database mounted. ORA-01113: file 1 needs media recovery ORA-01110: data file 1: /opt/ora10/oradata/orcl /system01.dbf （3）使用 recover database 恢复数据库SQL recover database; Media recovery complete. （4）打开数据库并检查测试数据SQL alter database open; Database altered. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 4 页 - - - - - - - - - SQL select * from tt; ID NAME - - 1 a 可以看到，数据库已经恢复。其实这种情况下，只要有归档日志文件，当前联机日志文件，当前控制文件，恢复起来是相当简单的，和使用联机热备的方式也没有什么区别。如果只是丢失了个别datafile ，也可以使用recover datafile 的方式来恢复。2、恢复场景二：当前联机日志文件、控制文件、数据文件全部损坏恢复方法：（1）restore 冷备份中的控制文件和数据文件（2）启动数据库SQL startup ORACLE instance started. Total System Global Area 897581056 bytes Fixed Size 1222480 bytes Variable Size 520095920 bytes Database Buffers 369098752 bytes Redo Buffers 7163904 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: /opt/ora10/oradata/orcl/system01.dbf ORA-01207: file is more recent than control file - old control file 由此可知，控制文件和其它文件不一致，需要进行恢复操作。（3）使用 recover database using backup controlfile 恢复数据库SQL recover database using backup controlfile; ORA-00279: change 540562 generated at 05/25/2011 13:38:59 needed for thread 1 ORA-00289: suggestion : /opt/ora10/flash_recovery_area/ORCL/archivelog/2011_05_25/o1_mf_1_4_%u_.arc ORA-00280: change 540562 for thread 1 is in sequence #4 Specify log: =suggested | filename | AUTO | CANCEL auto ORA-00308: cannot open archived log /opt/ora10/flash_recovery_area/ORCL/archivelog/2011_05_25/o1_mf_1_4_%u_.arc ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log /opt/ora10/flash_recovery_area/ORCL/archivelog/2011_05_25/o1_mf_1_4_%u_.arc ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 发现没有需要的archivelog 文件，于是查看日志文件名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 4 页 - - - - - - - - - SQL select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS - - - - - - - FIRST_CHANGE# FIRST_TIM - - 1 1 2 52428800 1 YES INACTIVE 539748 25-MAY-11 3 1 3 52428800 1 YES INACTIVE 539787 25-MAY-11 2 1 4 52428800 1 NO CURRENT 539792 25-MAY-11 由以下结果可以看出，第2 组日志文件没有归档，因此可以用它进行恢复操作SQL recover database using backup controlfile; ORA-00279: change 540562 generated at 05/25/2011 13:38:59 needed for thread 1 ORA-00289: suggestion : /opt/ora10/flash_recovery_area/ORCL/archivelog/2011_05_25/o1_mf_1_4_%u_.arc ORA-00280: change 540562 for thread 1 is in sequence #4 Specify log: =suggested | filename | AUTO | CANCEL /opt/ora10/oradata/orcl/redo02.log Log applied. Media recovery complete. （4）打开数据库并检查测试数据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. SQL select * from tt; ID NAME - - 1 a 可以看到，由于控制文件是老的控制文件，和当前联机日志文件的信息不同步，恢复完后需要使用resetlogs 才能打开数据库。恢复过程中如果无法将数据库恢复到一致状态，也可以尝试应用所有的当前联机日志。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 4 页 - - - - - - - - -