ITPub博客

首页 > 数据库 > 数据库开发技术 > 数据库备份与恢复测试 9

数据库备份与恢复测试 9

原创 数据库开发技术 作者:scoreking 时间:2006-02-15 18:42:52 0 删除 编辑

######################################################################################
# [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、查看恢复后的表,恢复成功
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/257699/viewspace-815195/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-04

  • 博文量
    40
  • 访问量
    209276