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移植数据文件位置的时候，同时处理好别名，然后对表空间重命名，实现只需要表空间离线一次

ASM简单管理(2)

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

1、磁盘组卸载/挂载

--查看当前磁盘组状态
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           1 ARCHIVELOG           MOUNTED                     12637      12585
           2 DATA                 MOUNTED                     10228       7644
           3 FLASHBACK            MOUNTED                      7836       7786
           4 DG2                  MOUNTED                      5114       5012
--卸载dg2磁盘组
SQL> alter diskgroup dg2 dismount;

Diskgroup altered.

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           1 ARCHIVELOG           MOUNTED                     12637      12585
           2 DATA                 MOUNTED                     10228       7644
           3 FLASHBACK            MOUNTED                      7836       7786
           0 DG2                  DISMOUNTED                      0          0
--挂载dg2磁盘组
SQL> alter diskgroup dg2 mount;

Diskgroup altered.

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           1 ARCHIVELOG           MOUNTED                     12637      12585
           2 DATA                 MOUNTED                     10228       7644
           3 FLASHBACK            MOUNTED                      7836       7786
           4 DG2                  MOUNTED                      5114       5012
--卸载所有磁盘组
SQL> alter diskgroup all dismount;

Diskgroup altered.

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           0 DATA                 DISMOUNTED                      0          0
           0 DG2                  DISMOUNTED                      0          0
           0 FLASHBACK            DISMOUNTED                      0          0
           0 ARCHIVELOG           DISMOUNTED                      0          0
--挂载所有磁盘组
SQL> alter diskgroup all mount;

Diskgroup altered.

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           2 DATA                 MOUNTED                     10228       7644
           3 DG2                  MOUNTED                      5114       5012
           4 FLASHBACK            MOUNTED                      7836       7786
           1 ARCHIVELOG           MOUNTED                     12637      12585
--如果当前磁盘组有实例在访问，则不能被卸载

SQL> alter diskgroup data dismount;
alter diskgroup data dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount

2、磁盘组目录

--创建目录
SQL> alter diskgroup dg2 add directory '+DG2/CHENGFEI';

Diskgroup altered.
--使用asmcmd查看（export ORACLE_SID=+ASM;asmcmd进入)
ASMCMD> pwd
+DG2
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        N    CHENGFEI/
--目录重命名
SQL> alter diskgroup dg2 rename directory '+DG2/CHENGFEI' TO '+DG2/XIFENFEI';

Diskgroup altered.

ASMCMD> ls 
XIFENFEI/
--删除目录
SQL> alter diskgroup dg2 drop directory '+DG2/xifenfei';

Diskgroup altered.

ASMCMD> ls
ASMCMD>

3、文件别名

--添加别名
SQL> alter diskgroup dg2 add alias '+dg2/XIFENFEI' for '+dg2/tasm/datafile/XFF.256.754832383';

Diskgroup altered.
--使用asmcmd查看别名是否成功                                           
ASMCMD> pwd
+dg2
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    TASM/
                                            N    XIFENFEI => +DG2/TASM/DATAFILE/XFF.256.754832383
--别名重命名
SQL> alter diskgroup dg2 rename alias '+DG2/XIFENFEI' TO '+DG2/FEIFEI.DBF';

Diskgroup altered.

                                            N    XIFENFEI => +DG2/TASM/DATAFILE/XFF.256.754832383
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            N    FEIFEI.DBF => +DG2/TASM/DATAFILE/XFF.256.754832383
 
                                           Y    TASM/
--删除别名
SQL> alter diskgroup dg2 drop alias '+dg2/feifei.dbf';

Diskgroup altered.

ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    TASM/

说明：
1）asmcmd命令行操作中，目录文件名不区分大小写，命令关键字区分大小写
2）磁盘组中的一个文件，最多只能建立一个别名可以通过v$asm_alias视图查看别名的相关信息
ASM简单管理(1)

ASMCMD常用命令

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

1、显示指定的ASM目录下ASM文件占用的所有磁盘空间
du

2、列出ASM目录下的内容及其属性
ls -ls

3、查看当前路径
pwd

4、打开目录
cd ../DATA

5、列出当前ASM客户端的信息
lsct

6、列出所有磁盘组及其属性
lsdg

7、列出数据文件信息
lsof

8、列出盘的信息
lsdsk

Note:大小写敏感

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Category Archives: Oracle ASM

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

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

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

ASM简单管理(2)

ASMCMD常用命令

2025 年十月
S	M	T	W	T	F	S
« Sep
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31