ITPub博客

首页 > Linux操作系统 > Linux操作系统 > rman tablespace point-in-time recovery(一)

rman tablespace point-in-time recovery(一)

原创 Linux操作系统 作者:Nalternative 时间:2011-05-23 21:16:12 0 删除 编辑

--数据文件和表空间对应关系
SELECT FILE_NAME ,TABLESPACE_NAME FROM DBA_DATA_FILES;
/
--1、检查恢复集是否是自包含的表空间集合
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USER02',TRUE)

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT *FROM  TRANSPORT_SET_VIOLATIONS;

NO ROWS SELECTED
/
--2、检查包含的数据文件
SELECT FILE_NAME ,TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USER02';

FILE_NAME                          TABLESPACE_NAME
--------------------------------------------------------------------------------
/ORACLE/ORADATA/ORCL/USER02.DBF    USER02

-----------------
SELECT TABLE_NAME ,TABLESPACE_NAME FROM USER_TABLES

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS
/
--create table as 指定表空间
CREATE TABLE TEST4 TABLESPACE USER02 AS SELECT *FROM TEST ;
/
--3、检查辅助集的数据文件和控制文件备份
SELECT FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME in('SYSTEM','UNDOTBS1')
UNION
SELECT MAX(NAME)
FROM V$CONTROLFILE;

FILE_NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control03.ctl
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undotbs01.dbf

/
--4、检查tspitr之后丢失的对象
SQL> select owner,name from ts_pitr_objects_to_be_dropped  where tablespace_name='USER02' AND CREATION_TIME>TO_DATE('20110523 11:10:10','YYYYMMDD HH24:MI:SS');

OWNER                          NAME
------------------------------ ------------------------------
JASON                          TEST4

如果有,则可以在执行tspitr之前进行expdp 执行tspitr之后再impdp导入该表

/
--连接到目标数据库
Recovery Manager complete.
[oracle@oracle ~]$ rman nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 23 11:29:51 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target   sys/zja@orcl

connected to target database: ORCL (DBID=1278821908)
using target database control file instead of recovery catalog
--备份主数据库的所有数据文件和控制文件
RMAN> run {
2> backup database format='/oracle/backup/%d_%s.bak';
3> sql 'alter system archive log current';
4> }
.....................................................
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/23/2011 11:31:15
ORA-19504: failed to create file "/oracle/backup/ORCL_6.bak"
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

[root@oracle etc]# chown -R oracle:oinstall /oracle/backup

RMAN> run {
2> backup database format='/oracle/backup/%d_%s.bak';
3> sql 'alter system archive log current';
4> }

Starting backup at 23-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00006 name=/oracle/oradata/orcl/user02.dbf
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-MAY-11
channel ORA_DISK_1: finished piece 1 at 23-MAY-11
piece handle=/oracle/backup/ORCL_7.bak tag=TAG20110523T114045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-11
channel ORA_DISK_1: finished piece 1 at 23-MAY-11
piece handle=/oracle/backup/ORCL_8.bak tag=TAG20110523T114045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAY-11

sql statement: alter system archive log current


-------------------------------
--主数据库上写数据
SQL> insert into test(id) values(1111);

1 row created.

SQL> insert into test3(id) values(1111);

1 row created.

SQL> commit;

SQL> host date
Mon May 23 11:47:31 CST 2011
--模拟用户误操作
truncate table test3;
insert into test(id) values(222);
insert into test3(id) values(222);
commit;
create table test44 tablespace user02 as select *From test;

由于test44是误操作之后建立的,所以恢复后将丢失该表信息
/
select *from v$log
/
select log_mode from v$database
/
chown -R oracle:oinstall /oracle/auxiliary
/
--- 怎么确定恢复到哪个日志序列号呢?查询误操作的时间
rman target sys/zja@orcl nocatalog
RMAN>recover tablespace user02 until logseq 11 thread 1 auxiliary destination  '/oracle/auxiliary';

RMAN>sql 'alter tablespace user02 online';
RMAN> backup tablespace user02 format='/oracle/backup/%d_%s.bak';

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

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

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    178285