###################################################################################### # [10]. 数据库基于时间点的不完全恢复 ###################################################################################### ********************************************** [10.1] 所有数据文件基于时间点的不完全恢复测试： ********************************************** incremental backup database : 1、做一次level 0级的备份 RMAN> backup incremental level 0 database; 2、在sqlplus建立empcopy表 SQL>create table empcopy as select * from emp; 3、做一次level 1级的备份 RMAN>backup incremental level 1 database; 4、查看一下系统时间 SQL> select sysdate from dual; SYSDATE ------------------- 2005-02-17 15:01:20 5、切换日志 SQL>alter system switch logfile; 6、模拟不完全恢复 SQL>drop table empcopy; 7、关闭数据库 SQL>shutdown immediate 8、将数据库启动到mount状态 SQL>startup mount 9、启动rman c:>rman target sys/oracle nocatalog; 10、进行不完全恢复 恢复archive log 文件： RMAN> run { allocate channel test type disk; SET ARCHIVELOG DESTINATION TO 'D:oracleoradataarchive'; RESTORE ARCHIVELOG ALL; release channel test; } 恢复controlfile文件： RMAN> run { allocate channel test type disk; restore controlfile to 'd:control.ctl'; release channel test; } 恢复数据文件： RMAN> run { set until time='2005-02-17 15:01:20'; restore database; recover database; alter database open resetlogs; } 11、数据库恢复成功 SQL> select table_name from user_tables; TABLE_NAME ------------------------------ BONUS DEPT EMP EMPCOPY EMPCOPY1 EMPCOPY2 EMPCOPY3 EMPCOPY4 SALGRADE 注意：需要所有的archivelog 文件及所有的备份文件 11、重新注册rman RMAN> register database; RMAN> list incarnation of database; RMAN> reset database; RMAN> list incarnation of database; ---------------------------------------------------------------------------------------------------------------------------- #################################################### # [10.2] TSPITR测试,单独表空间数据文件基于时间点的恢复测试： #################################################### 测试1 SQL> startup ORACLE 例程已经启动。 Total System Global Area 101784276 bytes Fixed Size 453332 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes ORA-01991: ???????'C:oracleora92DATABASEPWDtestdb.ORA' SQL> alter database open; 数据库已更改。 SQL> create table arch1 (status varchar(2)) tablespace users; 表已创建。 SQL> alter system switch logfile; 系统已更改。 SQL> insert into arch1 select 'ok' from dba_objects; 已创建6166行。 SQL> commit; 提交完成。 SQL> insert into arch1 select * from arch1; 已创建6166行。 SQL> / 已创建12332行。 SQL> / 已创建24664行。 SQL> / 已创建49328行。 SQL> commit; 提交完成。 SQL> alter system switch logfile; 系统已更改。 SQL> commit; 提交完成。 SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 5 NO CURRENT 2 3 YES INACTIVE 3 4 YES ACTIVE SQL> alter database backup controlfile to 'c:backup.ctl'; 数据库已更改。 SQL> select count(*) from arch1; COUNT(*) ---------- 98656 SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2005-03-07:15:46:17 alter system switch logfile; 系统已更改。 -->>这一步很重要，如果没有在时间点恢复时应用归档，那么我们只能拷贝在线日志去恢复了，否则会出现system01.dbf没有恢复的错误 SQL> drop table arch1; 表已丢弃。 SQL> create table salgrade (grade number,losal number,hisal number) tablespace users; 表已创建。 SQL> select owner,name,tablespace_name, 2 to_char(creation_time,'YYYY-MM-DD:HH24:MI:SS') 3 from sys.ts_pitr_objects_to_be_dropped 4 where tablespace_name in ('USERS') 5 and creation_time > to_date('2005-03-07:15:46:17','YYYY-MM-DD:HH24:MI:SS') 6 order by tablespace_name, creation_time; OWNER NAME ------------------------------ ---------------------------------------- TABLESPACE_NAME TO_CHAR(CREATION_TI ------------------------------ ------------------- SYS SALGRADE USERS 2005-03-07:15:46:36 SQL> alter tablespace users offline for recover; 表空间已更改。 ~~~~~~~~~~~~~~~~ 克隆数据库 ~~~~~~~~~~~~~~~~ oradim -new -sid clone -intpwd clone set oracle_sid=clone 创建初始化文件initCLONE.ora 增加*.lock_name_space=CLONE 注意db_name跟主数据库一样 归档路径也可以一样，以便于恢复时使用默认的路径 SQL> create pfile='C:oracleora92databaseinitCLONE.ora' from spfile; 文件已创建。 shutdown set ORACLE_SID=CLONE C:Documents and Settingslifeng.fang>sqlplus "sys/clone as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 3月 7 17:18:51 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 已连接到空闲例程。 SQL> startup nomount pfile='C:oracleora92databaseinitCLONE.ora' ORACLE 例程已经启动。 Total System Global Area 101784276 bytes Fixed Size 453332 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> alter database mount clone database; SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- C:ORACLEORADATATESTDBSYSTEM01.DBF C:ORACLEORADATATESTDBUNDOTBS01.DBF C:ORACLEORADATATESTDBINDX01.DBF C:ORACLEORADATATESTDBTOOLS01.DBF C:ORACLEORADATATESTDBUSERS01.DBF SQL> alter database rename file 'c:ORACLEORADATATESTDBSYSTEM01.DBF' to 'c: ORACLEORADATACLONESYSTEM01.DBF' ; 数据库已更改。 SQL> alter database rename file 'c:ORACLEORADATATESTDBUNDOTBS01.DBF' to 'c: ORACLEORADATACLONEUNDOTBS01.DBF'; 数据库已更改。 SQL> alter database rename file 'c:ORACLEORADATATESTDBINDX01.DBF' to 'c: ORACLEORADATACLONEINDX01.DBF' ; 数据库已更改。 SQL> alter database rename file 'c:ORACLEORADATATESTDBTOOLS01.DBF' to 'c: ORACLEORADATACLONETOOLS01.DBF' ; 数据库已更改。 SQL> alter database rename file 'c:ORACLEORADATATESTDBUSERS01.DBF' to 'c: ORACLEORADATACLONEUSERS01.DBF' ; 数据库已更改。 SQL> SQL> SQL> alter database datafile 'C:ORACLEORADATACLONESYSTEM01.DBF' online; 数据库已更改。 SQL> alter database datafile 'C:ORACLEORADATACLONEUNDOTBS01.DBF' online; 数据库已更改。 SQL> alter database datafile 'C:ORACLEORADATACLONEINDX01.DBF' online; 数据库已更改。 SQL> alter database datafile 'C:ORACLEORADATACLONETOOLS01.DBF' online; 数据库已更改。 SQL> alter database datafile 'C:ORACLEORADATACLONEUSERS01.DBF' online; 数据库已更改。 SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME '2005-03-07:16:57:31'; ORA-00279: 更改 69257 (在 02/28/2005 11:08:18 生成) 对于线程 1 是必需的 ORA-00289: 建议: C:ORACLEORADATATESTDBARCHIVE1_3.DBF ORA-00280: 更改 69257 对于线程 1 是按序列 # 3 进行的 指定日志: {=suggested | filename | AUTO | CANCEL} ORA-00279: 更改 69556 (在 03/07/2005 16:45:42 生成) 对于线程 1 是必需的 ORA-00289: 建议: C:ORACLEORADATATESTDBARCHIVE1_4.DBF ORA-00280: 更改 69556 对于线程 1 是按序列 # 4 进行的 ORA-00278: 此恢复不再需要日志文件 'C:ORACLEORADATATESTDBARCHIVE1_3.DBF' 指定日志: {=suggested | filename | AUTO | CANCEL} ORA-00279: 更改 69601 (在 03/07/2005 16:45:43 生成) 对于线程 1 是必需的 ORA-00289: 建议: C:ORACLEORADATATESTDBARCHIVE1_5.DBF ORA-00280: 更改 69601 对于线程 1 是按序列 # 5 进行的 ORA-00278: 此恢复不再需要日志文件 'C:ORACLEORADATATESTDBARCHIVE1_4.DBF' 指定日志: {=suggested | filename | AUTO | CANCEL} 已应用的日志。 完成介质恢复。 在clone库exp该表空间 exp SYS/clone TRANSPORT_TABLESPACE=y TABLESPACES=(users) TTS_FULL_CHECK=y 在主数据库中 DROP TABLESPACE users INCLUDING CONTENTS; 在主库 imp TRANSPORT_TABLESPACE=y FILE=expat.dmp DATAFILES=('C:ORACLEORADATACLONEUSERS01.DBF') #################################################### [10.2] 单独表空间数据文件基于时间点的恢复测试： #################################################### 1、做一次全库冷备份，将所有数据文件从 D:oracleoradata 拷贝至 D:oracleoradataoracle 目录下 SQL> alter database backup controlfile to 'd:oracleoradataoraclecontrol.ctl'; 2、显示系统时间 SQL> select sysdate from dual; SYSDATE ------------------- 2005-02-28 10:26:05 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATASYSTEM01.DBF SYSTEM D:ORACLEORADATAUNDOTBS01.DBF ONLINE D:ORACLEORADATACWMLITE01.DBF ONLINE D:ORACLEORADATADRSYS01.DBF ONLINE D:ORACLEORADATAEXAMPLE01.DBF ONLINE D:ORACLEORADATAINDX01.DBF ONLINE D:ORACLEORADATAODM01.DBF ONLINE D:ORACLEORADATATOOLS01.DBF ONLINE D:ORACLEORADATAUSERS01.DBF ONLINE D:ORACLEORADATAXDB01.DBF ONLINE D:ORACLEORADATASINO.DBF ONLINE D:ORACLEORADATARCVCAT.DBF ONLINE D:ORACLEORADATAPERFSTAT.DBF ONLINE 已选择13行。 3、执行 drop table empcopysystem; 4、执行 create table empcopy tablespace users as select * from emp; 5、执行 select table_name,tablespace_name from user_tables; 6、执行 alter tablespace users offline; 7、执行 alter system switch logfile; 8、执行语句，查看基于时间点后的table select owner,name,tablespace_name,to_char(creation_time,'yyyy-mm-dd hh24:MI:SS') from sys.ts_pitr_objects_to_be_dropped where tablespace_name in ('USERS') and creation_time > to_date('2005-02-28 10:27:00','yyyy-mm-dd hh24:MI:SS') order by tablespace_name,creation_time; 9、执行 alter system switch logfile; 10、执行create table emptest as select * from emp; 11、关闭数据库，用已有的init.ora文件重启数据库，在init.ora文件中把controlfile文件指向旧的controlfile文件 startup mount pfile='D:oracleinit.ora' 12、执行下列语句,将新的数据库文件指向备份文件，进行恢复。 SQL> alter database rename file 'D:ORACLEORADATAUSERS01.DBF' to 'D:ORACLEORADATAORACLEUSERS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATASYSTEM01.DBF' to 'D:ORACLEORADATAORACLESYSTEM01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAUNDOTBS01.DBF' to 'D:ORACLEORADATAORACLEUNDOTBS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATACWMLITE01.DBF' to 'D:ORACLEORADATAORACLECWMLITE01.DBF'; SQL> alter database rename file 'D:ORACLEORADATADRSYS01.DBF' to 'D:ORACLEORADATAORACLEDRSYS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAEXAMPLE01.DBF' to 'D:ORACLEORADATAORACLEEXAMPLE01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAINDX01.DBF' to 'D:ORACLEORADATAORACLEINDX01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAODM01.DBF' to 'D:ORACLEORADATAORACLEODM01.DBF'; SQL> alter database rename file 'D:ORACLEORADATATOOLS01.DBF' to 'D:ORACLEORADATAORACLETOOLS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAXDB01.DBF' to 'D:ORACLEORADATAORACLEXDB01.DBF'; SQL> alter database rename file 'D:ORACLEORADATASINO.DBF' to 'D:ORACLEORADATAORACLESINO.DBF'; SQL> alter database rename file 'D:ORACLEORADATARCVCAT.DBF' to 'D:ORACLEORADATAORACLERCVCAT.DBF'; SQL> alter database rename file 'D:ORACLEORADATAPERFSTAT.DBF' to 'D:ORACLEORADATAORACLEPERFSTAT.DBF'; 13、进行数据库的不完全恢复 SQL> recover database using backup controlfile until time '2005-02-28 13:45:00'; ORA-00279: ?? 3589502 (? 02/28/2005 13:37:10 ??) ???? 1 ???? ORA-00289: ??: D:ORACLEORA92RDBMSARC00053.001 ORA-00280: ?? 3589502 ???? 1 ???? # 53 ??? 指定日志: {=suggested | filename | AUTO | CANCEL} D:oracleoradataarchiveARC00053.001 已应用的日志。 完成介质恢复。 14、打开数据库并检查已删除的table SQL> alter database open resetlogs; SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATAoracleSYSTEM01.DBF SYSTEM D:ORACLEORADATAoracleUNDOTBS01.DBF ONLINE D:ORACLEORADATAoracleCWMLITE01.DBF ONLINE D:ORACLEORADATAoracleDRSYS01.DBF ONLINE D:ORACLEORADATAoracleEXAMPLE01.DBF ONLINE D:ORACLEORADATAoracleINDX01.DBF ONLINE D:ORACLEORADATAoracleODM01.DBF ONLINE D:ORACLEORADATAoracleTOOLS01.DBF ONLINE D:ORACLEORADATAoracleUSERS01.DBF ONLINE D:ORACLEORADATAoracleXDB01.DBF ONLINE D:ORACLEORADATAoracleSINO.DBF ONLINE D:ORACLEORADATAoracleRCVCAT.DBF ONLINE D:ORACLEORADATAoraclePERFSTAT.DBF ONLINE 已选择13行。 15、将已删除的表进行导出 c:>exp scott/tiger file=exp_empcopysystem.dmp tables=empcopysystem 16、关闭数据库，用spfile打开数据库至原始状态(删除文件状态) SQL> startup mount 打开数据库时，如果redo中有活动的transaction，会报错 SQL> alter database open; alter database open * ERROR 位于第 1 行: ORA-00314: 日志 1 (线程 1)，预计序号 与 不匹配 ORA-00312: 联机日志 1 线程 1: 'D:ORACLEORADATAORACLEREDO01.LOG' 清空未归档的日志文件 SQL> alter database clear unarchived logfile 'D:ORACLEORADATAORACLEREDO01.LOG'; SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATASYSTEM01.DBF SYSTEM D:ORACLEORADATAUNDOTBS01.DBF ONLINE D:ORACLEORADATACWMLITE01.DBF ONLINE D:ORACLEORADATADRSYS01.DBF ONLINE D:ORACLEORADATAEXAMPLE01.DBF ONLINE D:ORACLEORADATAINDX01.DBF ONLINE D:ORACLEORADATAODM01.DBF ONLINE D:ORACLEORADATATOOLS01.DBF ONLINE D:ORACLEORADATAUSERS01.DBF ONLINE D:ORACLEORADATAXDB01.DBF ONLINE D:ORACLEORADATASINO.DBF ONLINE D:ORACLEORADATARCVCAT.DBF ONLINE D:ORACLEORADATAPERFSTAT.DBF ONLINE 已选择13行。 17、导入文件 exp_empcopysystem.dmp c:>imp scott/tiger file=exp_empcopysystem.dmp tables=empcopysystem 总结：在做这个实验时，要注意两次恢复的数据文件，通过v$datafile可以看出第一次基于时间点的不完全恢复，所有的数据文件要用备份的 数据文件和备份的control文件，在init文件中，要把control文件指向这个备份的control文件。将所需的文件导出后，要重新用原来 的数据文件和control文件启动数据库，这时可能会遇到上面的错误信息，因为redo日志中会有transaction，这时要注意，在做第一次 不完全恢复时要切换几次日志，保证redo中没有需要回滚的信息，以免强制clear日志后造成的不必要的损失。 ---------------------------------------------------------------------------------------------------------------------------- #################################################### [10.3] 分区表空间数据文件基于时间点的恢复测试： #################################################### ########################################################################## # [10.3_1] 分区表空间数据文件基于时间点的恢复测试： 基于时间的不完全恢复 ########################################################################## 1、创建分区表 SQL> create table test (id number,name varchar2(40),hire_date date,salary number) 2 partition by range(hire_date) 3 ( 4 partition p1 values less than (to_date('2005-01-01','yyyy-mm-dd')) tablespace test1, 5 partition p2 values less than (to_date('2006-01-01','yyyy-mm-dd')) tablespace test2, 6 partition p3 values less than (to_date('2007-01-01','yyyy-mm-dd')) tablespace test3 7 ); 表已创建。 SQL> insert into test values (1001,'zhang xiao',to_date('2004-01-01','yyyy-mm-dd'),1200); SQL> insert into test values (1002,'zhang namo',to_date('2005-11-01','yyyy-mm-dd'),1200); SQL> insert into test values (1003,'zhang namo',to_date('2006-11-01','yyyy-mm-dd'),1200); SQL> select table_name,tablespace_name,high_value,partition_name from user_tab_partitions; TABLE_NAME TABLESPACE_NAME HIGH_VALUE PARTITION_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ TEST TEST1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P1 TEST TEST2 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P2 TEST TEST3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P3 2、做一次全库冷备份，将所有数据文件从 D:oracleoradata 拷贝至 D:oracleoradataoracle 目录下 SQL> alter database backup controlfile to 'd:oracleoradataoraclecontrol.ctl'; 3、显示系统时间 SQL> select sysdate from dual; SYSDATE ------------------- 2005-03-01 11:11:36 4、SQL> insert into test values (1004,'zhang namo',to_date('2005-10-01','yyyy-mm-dd'),1200); SQL> insert into test values (1005,'zhang namo',to_date('2006-10-01','yyyy-mm-dd'),1200); SQL> alter table test drop partition p2; SQL> insert into test values (1006,'zhang namo',to_date('2006-10-01','yyyy-mm-dd'),1200); SQL> insert into test values (1007,'zhang namo',to_date('2004-10-01','yyyy-mm-dd'),1200); SQL> insert into test values (1008,'zhang namo',to_date('2005-09-01','yyyy-mm-dd'),1200); 5、恢复前数据库的状态 SQL> select * from test partition(p1); ID NAME HIRE_DATE SALARY ---------- -------------------------------------------------- ------------------- ---------- 1001 zhang xiao 2004-01-01 00:00:00 1200 1007 zhang namo 2004-10-01 00:00:00 1200 SQL> select * from test partition(p2); select * from test partition(p2) * ERROR 位于第 1 行: ORA-02149: 指定的分区不存在 SQL> select * from test partition(p3); ID NAME HIRE_DATE SALARY ---------- -------------------------------------------------- ------------------- ---------- 1003 zhang namo 2006-11-01 00:00:00 1200 1005 zhang namo 2006-10-01 00:00:00 1200 1006 zhang namo 2006-10-01 00:00:00 1200 1008 zhang namo 2005-09-01 00:00:00 1200 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATASYSTEM01.DBF SYSTEM D:ORACLEORADATAUNDOTBS01.DBF ONLINE D:ORACLEORADATACWMLITE01.DBF ONLINE D:ORACLEORADATADRSYS01.DBF ONLINE D:ORACLEORADATAEXAMPLE01.DBF ONLINE D:ORACLEORADATAINDX01.DBF ONLINE D:ORACLEORADATAODM01.DBF ONLINE D:ORACLEORADATATOOLS01.DBF ONLINE D:ORACLEORADATAUSERS01.DBF ONLINE D:ORACLEORADATAXDB01.DBF ONLINE D:ORACLEORADATASINO.DBF ONLINE D:ORACLEORADATARCVCAT.DBF ONLINE D:ORACLEORADATAPERFSTAT.DBF ONLINE D:ORACLEORADATATEST1.DBF ONLINE D:ORACLEORADATATEST2.DBF ONLINE D:ORACLEORADATATEST3.DBF ONLINE D:ORACLEORADATATEST4.DBF ONLINE 已选择17行。 6、用原有的init.ora文件启动数据库到mount状态，并在init.ora文件中将control指向备份的control文件d:oracleoradataoraclecontrol.ctl SQL> startup mount pfile='D:oracleinit.ora' 6、将数据文件指向备份的数据文件 SQL> alter database rename file 'D:ORACLEORADATAUSERS01.DBF' to 'D:ORACLEORADATAORACLEUSERS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATASYSTEM01.DBF' to 'D:ORACLEORADATAORACLESYSTEM01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAUNDOTBS01.DBF' to 'D:ORACLEORADATAORACLEUNDOTBS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATACWMLITE01.DBF' to 'D:ORACLEORADATAORACLECWMLITE01.DBF'; SQL> alter database rename file 'D:ORACLEORADATADRSYS01.DBF' to 'D:ORACLEORADATAORACLEDRSYS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAEXAMPLE01.DBF' to 'D:ORACLEORADATAORACLEEXAMPLE01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAINDX01.DBF' to 'D:ORACLEORADATAORACLEINDX01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAODM01.DBF' to 'D:ORACLEORADATAORACLEODM01.DBF'; SQL> alter database rename file 'D:ORACLEORADATATOOLS01.DBF' to 'D:ORACLEORADATAORACLETOOLS01.DBF'; SQL> alter database rename file 'D:ORACLEORADATAXDB01.DBF' to 'D:ORACLEORADATAORACLEXDB01.DBF'; SQL> alter database rename file 'D:ORACLEORADATASINO.DBF' to 'D:ORACLEORADATAORACLESINO.DBF'; SQL> alter database rename file 'D:ORACLEORADATARCVCAT.DBF' to 'D:ORACLEORADATAORACLERCVCAT.DBF'; SQL> alter database rename file 'D:ORACLEORADATAPERFSTAT.DBF' to 'D:ORACLEORADATAORACLEPERFSTAT.DBF'; SQL> alter database rename file 'D:ORACLEORADATATEST1.DBF' to 'D:ORACLEORADATAORACLETEST1.DBF'; SQL> alter database rename file 'D:ORACLEORADATATEST2.DBF' to 'D:ORACLEORADATAORACLETEST2.DBF'; SQL> alter database rename file 'D:ORACLEORADATATEST3.DBF' to 'D:ORACLEORADATAORACLETEST3.DBF'; SQL> alter database rename file 'D:ORACLEORADATATEST4.DBF' to 'D:ORACLEORADATAORACLETEST4.DBF'; 7、进行数据库的不完全恢复 SQL> recover database using backup controlfile until time '2005-03-01 11:11:36'; ORA-00279: 更改 3663151 (在 02/28/2005 17:11:09 生成) 对于线程 1 是必需的 ORA-00289: 建议: D:ORACLEORA92RDBMSARC00018.001 ORA-00280: 更改 3663151 对于线程 1 是按序列 # 18 进行的 指定日志: {=suggested | filename | AUTO | CANCEL} D:ORACLEORA92RDBMSARC00018.001 已应用的日志。 完成介质恢复。 7、打开数据库 SQL> alter database open resetlogs; SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATAORACLESYSTEM01.DBF SYSTEM D:ORACLEORADATAORACLEUNDOTBS01.DBF ONLINE D:ORACLEORADATAORACLECWMLITE01.DBF ONLINE D:ORACLEORADATAORACLEDRSYS01.DBF ONLINE D:ORACLEORADATAORACLEEXAMPLE01.DBF ONLINE D:ORACLEORADATAORACLEINDX01.DBF ONLINE D:ORACLEORADATAORACLEODM01.DBF ONLINE D:ORACLEORADATAORACLETOOLS01.DBF ONLINE D:ORACLEORADATAORACLEUSERS01.DBF ONLINE D:ORACLEORADATAORACLEXDB01.DBF ONLINE D:ORACLEORADATAORACLESINO.DBF ONLINE NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATAORACLERCVCAT.DBF ONLINE D:ORACLEORADATAORACLEPERFSTAT.DBF ONLINE D:ORACLEORADATAORACLETEST1.DBF ONLINE D:ORACLEORADATAORACLETEST2.DBF ONLINE D:ORACLEORADATAORACLETEST3.DBF ONLINE D:ORACLEORADATAORACLETEST4.DBF ONLINE 已选择17行。 8、导出数据 建立交换table create table swap_p1 (id number,name varchar2(40),hire_date date,salary number) tablespace test4; SQL> alter table test exchange partition p2 with table swap_p1; SQL> select obj1_owner,obj1_name,obj1_type,ts1_name,obj2_name,obj2_type,obj2_owner,ts2_name,reason from ts_pitr_check where (ts1_name in('TEST2') and ts2_name not in ('TEST2')) or (ts1_name not in('TEST2') and ts2_name in ('TEST2')); 将分区的内容作为table导出 exp scott/tiger file=d:exp_swap.dmp tables=swap_p1 9、重新启动数据库至mount状态 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 数据库装载完毕。 SQL> alter database open; alter database open * ERROR 位于第 1 行: ORA-00314: 日志 1 (线程 1)，预计序号 与 不匹配 ORA-00312: 联机日志 1 线程 1: 'D:ORACLEORADATAORACLEREDO01.LOG' 清空日志 SQL> alter database clear unarchived logfile 'D:ORACLEORADATAORACLEREDO03.LOG'; SQL> alter database clear unarchived logfile 'D:ORACLEORADATAORACLEREDO02.LOG'; SQL> alter database clear unarchived logfile 'D:ORACLEORADATAORACLEREDO01.LOG'; 打开数据库 SQL> alter database open; 9、检查数据库状态 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATASYSTEM01.DBF SYSTEM D:ORACLEORADATAUNDOTBS01.DBF ONLINE D:ORACLEORADATACWMLITE01.DBF ONLINE D:ORACLEORADATADRSYS01.DBF ONLINE D:ORACLEORADATAEXAMPLE01.DBF ONLINE D:ORACLEORADATAINDX01.DBF ONLINE D:ORACLEORADATAODM01.DBF ONLINE D:ORACLEORADATATOOLS01.DBF ONLINE D:ORACLEORADATAUSERS01.DBF ONLINE D:ORACLEORADATAXDB01.DBF ONLINE D:ORACLEORADATASINO.DBF ONLINE NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATARCVCAT.DBF ONLINE D:ORACLEORADATAPERFSTAT.DBF ONLINE D:ORACLEORADATATEST1.DBF ONLINE D:ORACLEORADATATEST2.DBF ONLINE D:ORACLEORADATATEST3.DBF ONLINE D:ORACLEORADATATEST4.DBF ONLINE 已选择17行。 SQL> select table_name,tablespace_name,high_value,partition_name from user_tab_partitions; TABLE_NAME TABLESPACE_NAME HIGH_VALUE PARTITION_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ TEST TEST1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P1 TEST TEST3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P3 10、导入已删除的分区的内容 增加分区 SQL> alter table test split partition p3 at (to_date('2006-01-01','yyyy-mm-dd')) into (partition p2 tablespace test2,partition p3); 导入表 imp scott/tiger file=d:exp_swap.dmp tables=swap_p1 将数据插入分区表 insert into test select * from swap_p1; 11、数据库的状态 SQL> select table_name,tablespace_name,high_value,partition_name from user_tab_partitions; TABLE_NAME TABLESPACE_NAME HIGH_VALUE PARTITION_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ TEST TEST2 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P2 TEST TEST1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P1 TEST TEST3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P3 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATASYSTEM01.DBF SYSTEM D:ORACLEORADATAUNDOTBS01.DBF ONLINE D:ORACLEORADATACWMLITE01.DBF ONLINE D:ORACLEORADATADRSYS01.DBF ONLINE D:ORACLEORADATAEXAMPLE01.DBF ONLINE D:ORACLEORADATAINDX01.DBF ONLINE D:ORACLEORADATAODM01.DBF ONLINE D:ORACLEORADATATOOLS01.DBF ONLINE D:ORACLEORADATAUSERS01.DBF ONLINE D:ORACLEORADATAXDB01.DBF ONLINE D:ORACLEORADATASINO.DBF ONLINE NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATARCVCAT.DBF ONLINE D:ORACLEORADATAPERFSTAT.DBF ONLINE D:ORACLEORADATATEST1.DBF ONLINE D:ORACLEORADATATEST2.DBF ONLINE D:ORACLEORADATATEST3.DBF ONLINE D:ORACLEORADATATEST4.DBF ONLINE 已选择17行。 ---------------------------------------------------------------------------------------------------------------------------- ########################################################################## # [10.3_2] 分区表空间数据文件基于时间点的恢复测试： 数据文件损坏的恢复 ########################################################################## 1、数据库分区表的状态： SQL> select table_name,tablespace_name,high_value,partition_name from user_tab_partitions; TABLE_NAME TABLESPACE_NAME HIGH_VALUE PARTITION_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ TEST TEST2 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P2 TEST TEST1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P1 TEST TEST3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P3 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATASYSTEM01.DBF SYSTEM D:ORACLEORADATAUNDOTBS01.DBF ONLINE D:ORACLEORADATACWMLITE01.DBF ONLINE D:ORACLEORADATADRSYS01.DBF ONLINE D:ORACLEORADATAEXAMPLE01.DBF ONLINE D:ORACLEORADATAINDX01.DBF ONLINE D:ORACLEORADATAODM01.DBF ONLINE D:ORACLEORADATATOOLS01.DBF ONLINE D:ORACLEORADATAUSERS01.DBF ONLINE D:ORACLEORADATAXDB01.DBF ONLINE D:ORACLEORADATASINO.DBF ONLINE NAME STATUS -------------------------------------------------- ------- D:ORACLEORADATARCVCAT.DBF ONLINE D:ORACLEORADATAPERFSTAT.DBF ONLINE D:ORACLEORADATATEST1.DBF ONLINE D:ORACLEORADATATEST2.DBF ONLINE D:ORACLEORADATATEST3.DBF ONLINE D:ORACLEORADATATEST4.DBF ONLINE 已选择17行。 2、备份数据库 RMAN> backup database; 备份archivelog文件 RMAN> run { 2> allocate channel c1 type disk; 3> backup archivelog all delete input; (或者BACKUP ARCHIVELOG TIME BETWEEN 'SYSDATE-31' AND 'SYSDATE-7'; ) 4> release channel c1; 5> } 备份controlfile文件 RMAN> run { 2> allocate channel c1 type disk; 3> backup current controlfile ; 4> release channel c1; 5> } 3、启动数据到mount状态，在rman中进行恢复 显示备份的archivelog文件 list backup of archivelog all; 恢复日志文件，如果不指定destination，则恢复到当前数据库默认的log_archive_dest目录下 RMAN> run { 2> allocate channel c1 type disk; 3> set ARCHIVELOG DESTINATION TO 'D:oracleora92rdbms'; 3> restore archivelog sequence between 34 and 40; 4> release channel c1; 5> } 恢复数据文件 RMAN> run { 2> allocate channel c1 type disk; 3> restore datafile 14; 4> recover datafile 14; 5> alter database open; 6> release channel c1; 7> } 4、查看恢复后的表，恢复成功 |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/257699/viewspace-815195/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/257699/viewspace-815195/
1552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
