Oracle常规恢复的实验测试 电脑资料

Oracle常规恢复的实验测试 电脑资料 * 用户管理恢复 * 二、恢复类型：实例恢复、介质恢复 1、实例恢复的前提是数据文件、控制文件和在线日志均没有损坏，完全自动 2、介质恢复：在文件有物理损坏的情况下，将其还原、恢复、最后使数据库得以正常打开的操作称为介质恢复，人工干预 三、恢复方式：完全恢复、不完全恢复 四、恢复技术：用户管理恢复和RMAN恢复 五、完全恢复案例 v$recover_file /哪个文件需要恢复 select file#,change# from v$recover_file; v$log /在线日志 select min(first_change#) from v$log where status!=INACTIVE; v$archived_log /归档日志 select min(first_change#) from v$archived_log; v$recovery_log /需要哪些archvielog做恢复 1、*场景1:数据库关闭时的恢复(system,sysaux,undo,whole database):system表空间为例(冷备) select * from v$dbfile; shutdown immediate; cp -rf /u01/app/oracle/oradata/ocp/* /backup/cold -冷备整个数据文件 startup conn gyj/gyj insert into t1 values(4,gyj4); mit; alter system switch logfile; -切换日志 rm -rf system01.dbf -册除system系统表空间 shutdown abort; -异常关机 cp /backup/cold/system01.dbf /u01/app/oracle/oradata/ocp/ -用备份先做还原 startup; -会报错:ORA-01157，ORA-01110 recover datafile 1; -恢复 一直回车 alter database open; 2、*场景2:数据库打开时的恢复以用户数据的表空间为例(热备) create tablespace tp5 datafile /u01/app/oracle/oradata/ocp/tp5.dbf size 10M; conn gyj/gyj create table t5 (id int,name varchar2(10) tablespace tp5; insert into t5 values(1,gyj1); mit; alter tablespace tp5 begin backup; -热备 !cp /u01/app/oracle/oradata/ocp/tp5.dbf /backup/hot alter tablespace tp5 end backup; conn gyj/gyj insert into t5 values(2,gyj2); mit; rm -rf tp5.dbf; alter database datafile 9 offline; !cp /backup/hot/tp5.dbf /u01/app/oracle/oradata/ocp/ -用备份先做还原 recover datafile 9; -恢复 alter database datafile 11 online; 3、控制文件的恢复 select controlfile_sequence# from v$database;-当前控件文件记录的序列号 select hxfil as file#,FHCSQ from x$kcvfh; select controlfile_change# from v$database; -控制自身的检查点 select file#,checkpoint_change# from v$datafile_header; select checkpoint_change# from v$database; -数据库的检查点 select file#,checkpoint_change# from v$datafile; -从控制文件中读的s (1)*场景1:控制文件没有全坏,有一个是好的 rm -rf control02.ctl shutdown abort; cp control01.ctl control02.ctl startup (2)*场景2:所有控制文件都坏了,但损坏前有备份(二进制) alter database backup controlfile to /backup/control/control.bin; alter system checkpoint; rm -rf control* shutdown abort; cp /backup/control.bin control01.ctl cp /backup/control.bin control02.ctl cp /backup/control.bin control03.ctl startup select checkpoint_change# from v$database; select checkpoint_change# from v$datafile; select checkpoint_change# from v$datafile_header; recover database using backup controlfile; redologfile 一个个试 alter database open resetlogs; (3)*场景3:所有控制文件都坏了,但损坏前有备份(文本) alter database backup controlfile to trace as /backup/control/control.txt; rm -rf control* shutdown abort; vi control.bak -重构一个新的控制文件 conn /as sysdba /backup/control/control.txt recover database; alter database open; (4) *场景4:备份控制文件的恢复,控制文件中不包含数据文件,但联机日志中有 alter database backup controlfile to /backup/control/control.bin; create tablespace tp6 datafile /u01/app/oracle/oradata/ocp/tp6.dbf size 10M; conn gyj/gyj create table t6(id int,name varchar2(10) tablespace tp6; insert into t6 values(1,gyj1); mit; rm -rf control* shutdown abort; cp /backup/control/control.bin control01.ctl cp /backup/control/control.bin control02.ctl cp /backup/control/control.bin control03.ctl startup mount; select file#,checkpoint_change# from v$datafile; select file#,checkpoint_change# from v$datafile_header; alter database open; /提示控制文件旧了 recover database using backup controlfile; 输入:/oradata/orcl/redo01.log -状态是current 提示有一个文件不识别 select file#,name from v$datafile; alter database rename file /u01/app/oracle/product/11g/dbs/UNNAMED00005 to /u01/app/oracle/oradata/ocp/tp6.dbf; -看上面提示或 看alert.log日志 recover database using backup controlfile; 输入:/oradata/orcl/redo01.log -一select * from v$log; alter database open resetlogs; 二、不完全恢复案例 1、不完全恢复的动因 (1)被动恢复：原计划执行完全恢复，但是恢复的过程中发现recover命令需要的至少一个归档日志或未归档的ACTIVE/CURRENT在线日志损坏 (2)主动恢复：由于人为错误码或恶意篡改破坏了数据库内大量的信息（update,truncate） 2、不完全恢复：recover命令的四种情况 (1)精确到重做日志的基于日志序列号的“recover database until sequence”-仅适用RMAN (2)精确到到重做日志的基于手动交互式的“recover database until cancel”-仅适用SQL*PLUS (3)精确到重做记录的基于时间的 “recover database until time”-适用RMAN和SQL*PLUS (4)精确到重做记录的基于S的“recover database until s”-仅适用RMAN 3、基本时间的不完全恢复 (1)*场景:误操作删除表，控制文件没损坏 shutdown immediate; !cp /u01/app/oracle/oradata/ocp/* /backup/cold/ -做一个冷备 startup conn gyj/gyj insert into t1 values(3,gyj3); mit; select to_char(sysdate,yyyy-mm-dd:hh24:mi:ss) from dual;-恢复此时间之前的数据 insert into t1 values(4,gyj4); mit; drop table t1; -模拟误操作 shutdown immediate; rm -rf /u01/app/oracle/oradata/ocp/*.dbf -删除所有数据(不删控制文件和重做日志文件) cp -rf /backup/cold/*.dbf /u01/app/oracle/oradata/ocp/ -restore所有数据 startup mount; recover database until time xx-03-29 09:49:05; -基于时间的不完全恢复 alter database open resetlogs; (2) *场景1:误操作删除表，控制文件也被损坏了 shutdown immediate; !cp /u01/app/oracle/oradata/ocp/* /backup/cold/ -做一个冷备 startup conn gyj/gyj insert into t1 values(6,gyj6); mit; select to_char(sysdate,yyyy-mm-dd:hh24:mi:ss) from dual;-误操作前的时间 xx-06-22:17:09:08 insert into t1 values(7,gyj7); mit; drop table t1; -模拟误操作 shutdown abort; rm -rf /u01/app/oracle/oradata/ocp/*.dbf -删除数据文件 rm -rf /u01/app/oracle/oradata/ocp/*.ctl -删除控制文件,(不删除重做日志文件) cp -rf /backup/cold/*.dbf /u01/app/oracle/oradata/ocp/ -还原所有数据 cp -rf /backup/cold/*.ctl /u01/app/oracle/oradata/ocp/ -还原控制文件 startup mount; recover database until time xx-10-31:11:47:15 using backup controlfile; -用了备份的控制文件 alter database open resetlogs; 4、基本Cancel的不完全恢复 (1)*场景1:写到数据文件,归档没保留 shutdown immediate; !cp -rf /u01/app/oracle/oradata/ocp/* /backup/cold/ -重生做一个冷备 startup conn gyj/gyj insert into t1 values(4,gyj4); mit; alter system switch logfile; insert into t1 values(5,gyj5); mit; select group#,status from v$log; alter system checkpoint; -写到数据文件,归档没保留 rm -rf /u01/app/oracle/oradata/ocp/* -把所有的/oradata/orcl/* 全部删掉 shutdown abort; cp /backup/cold/*.dbf /u01/app/oracle/oradata/ocp/ -controlfile,redolog都没了 cp /backup/cold/*.ctl /u01/app/oracle/oradata/ocp/ startup mount; ; recover database using backup controlfile until cancel; -用了备份的控制文件 -如归档存在,直接回车 -如归档不存在,输入cancel,因为redolog全删了 alter database open resetlogs; alter tablespace temp add tempfile /u01/app/oracle/oradata/bxdb/temp.dbf size 10M; select * from t; -数据少了一条 ALTER TABLESPACE TEMP ADD TEMPFILE /u01/app/oracle/oradata/orcl/temp01.dbf REUSE; * RMAN恢复 * 1、RMAN完全恢复 (1)*场景1:参数文件损坏 backup spfile; -create pfile=/tmp/pfile.ora from spfile; shutdown immediate; rm -rf $ORACLE_HOME/dbs/spfileocp.ora startup pfile=/tmp/pfile.ora nomount; set dbid 43434343 restore spfile from autobackup; restore spfile to /temp/spfile.ora from autobackup; (2)*场景2:控制文件损坏 backup current controlfile; rm -rf control* startup nomount; set dbid=2690543208; restore controlfile from autobackup;-db_recovery_file_dest或$ORACLE_HOME/dbs(set dbid) alter database mount; recover database; alter database open resetlogs; (3) *场景3 system表空间数据文件损坏 backup database; rm -rf system01.dbf; shutdown abort; startup mount; restore datafile 1; recover datafile 1; alter database open; (4)*场景4 undo表空间损坏 startup mount; restore tablespace UNDOTBS1; recover database; alter database open; (5)*场景5:磁盘损坏 rm -rf tp1.dbf select name,file# from v$datafile; run shutdown abort; startup mount; set newname for datafile 6 to /u01/app/oracle/oradata/ocm/tp1.dbf; restore datafile 6; switch datafile 6 ; recover database; alter database open; (6)*场景6:普通数据文件损坏 rm -rf tp1.dbf 方法一 run shutdown abort; startup mount; restore datafile 6; recover datafile 6; alter database open; 方法二 run sql alter database datafile 6 offline; restore datafile 6; recover datafile 6; sql alter database datafile 6 online; 方法三 run shutdown abort; startup mount; sql alter database datafile 6 offline; alter database open; restore datafile 6; recover datafile 6; sql alter database datafile 6 online; (7)控制文件无损的情况下，只读文件损坏 *场景1:只读数据文件丢失 run shutdown abort; startup mount; restore datafile 5; alter database open; *场景2:只读数据文件在实例运行时丢失 run sql alter database datafile 5 offline; restore datafile 5; sql alter database datafile 5 online; *场景3:只读数据文件内部数据块损坏 run sql alter database datafile 5 offline; restore datafile 5 force; sql alter database datafile 5 online; *场景4:起先数据文件备份时是只读状态，后来其表空间改为读写状态，但没有备份，现数据文件坏了 run sql alter database datafile 6 offline; restore datafile 6; recover datafile 6; sql alter database datafile 6 online; (8)控制文件损坏的情况下，只读文件损坏 分析只读表空间在控制文件的情况 alter database backup controlfile to trace as /backup/control.bak01; alter tablespace read only; alter database backup controlfile to trace as /backup/control.bak02; diff control.bak01 control.bak02 -比较两个备份的控制文件 alter database rename file to ; -只读表空间在重构时不考虑,要在数据库打开后,重命名数据文件 alter database tp1 online; *场景1:只读数据文件连同控件文件一并损坏或丢失 run shutdown abort; startup nomount; restore controlfile from autobackup; alter database mount; restore datafile 6; recover database; alter database open resetlogs; *场景2:在数据文件为只读状态时，对该文件和控制文件进行了备份，随且对该表空间改为读写状态，但该文件和控制文件均未再有过备份，此时该数据文件连同控件文件一并损坏或丢失 run shutdown abort; startup nomount; restore controlfile from /backup/rman/full_0nlmfd_1_1; mount database; restore datafile 6; recover database; recover database; -注意再次执行 alter database open resetlogs; 二、RMAN不完全恢复 (1)*场景1:基本时间的不完全恢复 startup conn gyj/gyj insert into t1 values(6,gyj6); mit; select to_char(sysdate,yyyy-mm-dd:hh24:mi:ss) from dual; insert into t1 values(9,gyj9); mit; drop table t1; -误操作 run shutdown abort; startup mount; set until time to_date(xx-04-14:11:42:18,yyyy-mm-dd:hh24:mi:ss); restore database; recover database; alter database open resetlogs; (2)*场景2:基于sequence的不完全恢复 conn gyj/gyj insert into t1 values(9,gyj9); mit; alter system switch logfile; alter system switch logfile; alter system switch logfile; insert into t1 values(10,gyj10); mit; alter system