--查看当前情况
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）
Author Archives: 惜分飞
asm数据文件迁移(asm–>asm)
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)
--添加测试表空间
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数据文件大小的限制
如果你比较细心，会注意到两件事情：
1、添加一个32g的数据文件会报错
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 32g;
alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 32g
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks
SQL> !oerr ora 1144
01144, 00000, “File size (%s blocks) exceeds maximum of %s blocks”
// *Cause: Specified file size is larger than maximum allowable size value.
// *Action: Specify a smaller size.
2、添加一个maxsize为unlimited的数据文件
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 10M AUTOEXTEND ON maxsize unlimited;
Tablespace altered.
SQL> select MAXBYTES/1024/1024/1024 from dba_data_files where file_name=’/opt/oracle/oradata/test/user32g.dbf’;
MAXBYTES/1024/1024/1024
———————–
31.9999847
你是不是有疑惑，为什么我添加32g的数据文件报错，为什么我添加一个数据文件maxsize设置为unlimited了，还是没有突破32g这个坎
原因分析：
由于Oracle的Rowid中使用22位来代表Block号，这22位最多只能代表2^22-1(4194303)个数据块，而在我们一般情况下使用的数据块大小为8k，所以数据文件的理论大小最大为： 31.9999924G
至于maxsize为unlimited时候，数据文件的大小为什么只有31.9999847G(blocks：4194301.99)，比最大块数(4194303)少了一块，也许是和数据库和系统之间的衔接原因导致。
由上面的分析我们可以知道，数据文件大小和db_block_size有关，那我们可以得到这样的数据文件最大理论值
| 数据块 | 数据文件 |
| 2KB | 8GB |
| 4KB | 16GB |
| 8KB | 32GB |
| 16KB | 64GB |
| 32KB | 128GB |
注：以上规则适用于smallfile tablespace 下的数据文件，bigfile tablespace 下的数据文件不受此限制
ASM简单管理(2)
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)
