分类目录归档：Oracle备份恢复

asm数据文件迁移(asm–>asm)

发表于 2011 年 8 月 1 日 由 惜分飞

rman迁移操作

[oracle@localhost oradata]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 05:50:03 2011

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

connected to target database: TASM (DBID=3032096031)

RMAN> sql ‘alter tablespace xff offline’;

using target database control file instead of recovery catalog
sql statement: alter tablespace xff offline

RMAN> backup device type disk as copy datafile 6 format '+DG2';

Starting backup at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/tasm/xff01.dbf
output filename=+DG2/tasm/datafile/xff.256.754899605 tag=TAG20110627T062003 recid=4 stamp=754899608
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-JUN-11

RMAN> switch tablepspace xff  to copy;

datafile 6 switched to datafile copy "+DG2/tasm/datafile/xff.256.754899605"

RMAN> sql 'alter tablespace xff online';

sql statement: alter tablespace xff online

sql验证操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DG2/tasm/datafile/xff.256.754899605

6 rows selected.

SQL> select count(*) from hr.a;

  COUNT(*)
----------
      1580
发表在 Oracle ASM, rman备份/恢复 | 评论关闭

asm数据文件迁移(os–>asm)

发表于 2011 年 8 月 1 日 由 惜分飞 
--添加测试表空间
SQL> create tablespace xff datafile '/u01/oradata/xifenfei.dbf' size 10m autoextend on maxsize 100m;

Tablespace created.

--查看数据文件位置
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
/u01/oradata/xifenfei.dbf

6 rows selected.

--创建测试表
SQL> create table hr.a tablespace xff
  2  as
  3  select * from dba_tables;

Table created.

SQL> select count(*) from hr.a;

  COUNT(*)
----------
      1580
--转移数据文件位置
[oracle@localhost oradata]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 04:30:22 2011

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

connected to target database: TASM (DBID=3032096031)

RMAN> sql 'alter tablespace xff offline';

using target database control file instead of recovery catalog
sql statement: alter tablespace xff offline

RMAN> backup as copy tablespace xff format '+DATA';

Starting backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/xifenfei.dbf
output filename=+DATA/tasm/datafile/xff.269.754893121 tag=TAG20110627T043200 recid=2 stamp=754893123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11

RMAN> switch tablespace xff to copy;

datafile 6 switched to datafile copy "+DATA/tasm/datafile/xff.269.754893121"

RMAN> sql 'alter tablespace xff online';

sql statement: alter tablespace xff online

--查看转移后的数据文件位置
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DATA/tasm/datafile/xff.269.754893121

6 rows selected.

--测试其中数据是否存在
SQL> select count(*) from hr.a;

  COUNT(*)
----------
      1580

--创建asm中文件别名
ASMCMD> mkalias  +DATA/tasm/datafile/xff.269.754893121 +DATA/tasm/xff01.dbf

--文件重命名
SQL> alter tablespace xff offline;

Tablespace altered.

SQL> alter database rename file '+DATA/tasm/datafile/xff.269.754893121' to '+DATA/tasm/xff01.dbf';

Database altered.

SQL> alter tablespace xff online;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DATA/tasm/xff01.dbf

6 rows selected.

--手工删除原来数据
[oracle@localhost oradata]$ rm xifenfei.dbf

说明：可以在rman移植数据文件位置的时候，同时处理好别名，然后对表空间重命名，实现只需要表空间离线一次

发表在 Oracle ASM, rman备份/恢复 | 评论关闭

oracle 10g flashback

发表于 2011 年 7 月 21 日 由 惜分飞

一、oracle falshback drop
利用flashback drop oracle10g可以对DDL操作进行恢复,oracla提供类似回收站的recyclebin来收集被删除的对象，其实对象在删除的时候oracle把对象写到一个数据字典表中，当用户不需要该对象的时候，可以利用purge命令来从回收站进行清除
select object_name,droptime,dropscn,purge_object,ORIGINAL_NAME from recyclebin;
flashback table f_drop to before drop;
清空回收站：
1）清空一张表：purge table “BIN$N+i42FTvSSemvMrH6frCQg==$0″/table_name;
2）清空一个index：purge index index_name;
2）清空所有对象：PURGE recyclebin;

二、oracle falshback table
对于误drop的table此可以使用本操作，还原drop的table
select t_odu.*,ora_rowscn from a;–查询每条记录对应scn
select dbms_flashback.get_system_change_number from dual;–系统当前scn
alter table t_odu enable row movement;–table row movement
flashback table t_odu to scn 1831189;–基于scn恢复
flashback table t_odu to timestamp to_timestamp(’2011-6-27 11:45:20′,’yyyy-mm-dd hh24:mi:ss’);–基于时间点
scn补充：
可以通过select ora_rowscn from table 得到每一条结果集当前的SCN，timestamp_to_scn()将scn转换到stmestamp；scn_to_timestamp()将timestamp转换到scn。

三、oracle falshback version query
racle10g falshback 能将所有做了提交的行进行记录，就类似于审计的功能，通过falshback可以查询什么时候执行了什么操作，非常方便，包括闪回版本的查询和审计等
select COUNT(*) from t_query as of scn 1831544;
SELECT COUNT(*) FROM t_query as of timestamp to_timestamp(’2011-07-21 14:58:00′,’yyyy-mm-dd hh24:mi:ss’);

四、oracle falshback transaction query
回闪事务功能提供对过去某段时间内所完成的事务的查询和撤销
SELECT * FROM flashback_transaction_query a WHERE a.table_name=’T_QUERY’;
补充：
UNDO_SQL 就是当时对表T_QUERY的逆向操作语句

发表在 Oracle备份恢复 | 一条评论