普通库迁移至ASM存储

Posted on 2011 年 08 月 01 日 by 惜分飞

--查看当前库文件位置
SQL> select name from v$datafile
  2  union
  3  select member from v$logfile
  4  union 
  5  select name from v$controlfile
  6  union
  7  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/control03.ctl
/u01/oradata/tos/example01.dbf
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/sysaux01.dbf
/u01/oradata/tos/system01.dbf
/u01/oradata/tos/temp01.dbf
/u01/oradata/tos/undotbs01.dbf
/u01/oradata/tos/users01.dbf
/u01/oradata/tos/xff01.dbf
/u01/oradata/tos/xff02.dbf

14 rows selected.

--备份控制文件
SQL> alter database backup controlfile to '/u01/control.ctl';

Database altered.

--修改spfile中的控制文件至asm
SQL>  alter system set control_files='+DATA' scope=spfile;

System altered.

--修改db_recovery_file_dest至asm中
SQL> alter system set db_recovery_file_dest='+FLASHBACK';

System altered.

--修改归档日志至asm中
SQL> alter system set log_archive_dest_1='location=+DATA';

System altered.

--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--登录rman
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011

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

connected to target database (not started)

--启动数据库至nomount状态
RMAN> startup nomount;

Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1260672 bytes
Variable Size                 67109760 bytes
Database Buffers              92274688 bytes
Redo Buffers                   7127040 bytes

--恢复控制文件
RMAN>  restore controlfile from '/u01/control.ctl';   

Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/tos/controlfile/current.268.754905785
Finished restore at 27-JUN-11

--打开数据库至mount状态
RMAN> alter database mount; 

database mounted
released channel: ORA_DISK_1

--copy数据文件至asm
RMAN> backup as copy database format '+DATA';

Starting backup at 27-JUN-11
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11

Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-11

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/oradata/tos/system01.dbf
output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf
output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/oradata/tos/example01.dbf
output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf
output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf
output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf
output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/oradata/tos/users01.dbf
output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

--恢复数据库
RMAN>  recover database;

Starting recover at 27-JUN-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log
archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11

--修改数据库中数据文件路径
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833"
datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021"
datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929"
datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035"
datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995"
datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027"
datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031"

--打开数据库
RMAN> alter database open resetlogs;

database opened

--添加日志文件
SQL> alter database add logfile group 4 '+DATA' size 10m;

Database altered.

SQL> alter database add logfile group 5 '+DATA' size 10m;

Database altered.

SQL> alter database add logfile group 6 '+DATA' size 10m;

Database altered.

--添加临时文件
SQL> alter tablespace temp add tempfile '+DATA' size 30m;

Tablespace altered.

--删除原临时文件
SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf';

Tablespace altered.

--切换日志
SQL> alter system switch logfile; --多次

System altered.

--数据文件修改写入磁盘
SQL> alter system checkpoint;

System altered.

--检查联机日志状态
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 CURRENT

6 rows selected.

--删除原联机日志
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

--查看修改后状态
SQL>  select name from v$datafile
  2      union
  3      select member from v$logfile
  4      union 
  5      select name from v$controlfile
  6      union
  7      select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/tos/controlfile/current.268.754905785
+DATA/tos/datafile/example.272.754905995
+DATA/tos/datafile/sysaux.271.754905929
+DATA/tos/datafile/system.270.754905833
+DATA/tos/datafile/undotbs1.273.754906021
+DATA/tos/datafile/users.276.754906035
+DATA/tos/datafile/xff.274.754906027
+DATA/tos/datafile/xff.275.754906031
+DATA/tos/onlinelog/group_4.277.754906309
+DATA/tos/onlinelog/group_5.278.754906319
+DATA/tos/onlinelog/group_6.279.754906321
+DATA/tos/tempfile/temp.280.754906369

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

Posted on 2011 年 08 月 01 日 by 惜分飞

--查看当前情况
SQL> select count(*) from hr.a;

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

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.754902279

6 rows selected.

--创建目录
SQL>  create directory asmsrc as '+DG2/TASM/datafile';

Directory created.

SQL>  create directory osdesc as '/u01/oradata';

Directory created.

--表空间离线
SQL> alter tablespace xff offline;

Tablespace altered.

--数据文件迁移
SQL> begin
  2      dbms_file_transfer.copy_file('ASMSRC',
  3      'xff.256.754902279',
  4      'OSDESC',
  5      'xff_new_2.dbf');
  6  END;
  7  /

PL/SQL procedure successfully completed.

--修改数据库中datafile路径
SQL> alter database rename file
  2             '+DG2/tasm/datafile/xff.256.754902279'
  3             to '/u01/oradata/xff_new_2.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
/u01/oradata/xff_new_2.dbf

6 rows selected.

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

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

--删除asm中文件
ASMCMD> rm XFF.256.754902279
ORA-15032: not all alterations performed
ORA-15028: ASM file '+dg2/tasm/datafile/XFF.256.754902279' not dropped; 
currently being accessed (DBD ERROR: OCIStmtExecute)
--不能直接删除，关闭数据库后可以删除（应该是bug）

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

Posted on 2011 年 08 月 01 日 by 惜分飞

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

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

Posted on 2011 年 08 月 01 日 by 惜分飞

--添加测试表空间
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 10g flashback

Posted on 2011 年 07 月 21 日 by 惜分飞

一、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的逆向操作语句