SQL> select file_id,file_name,AUTOEXTENSIBLE
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME AUT
---------- ------------------------------------------------------------ ---
1 +DATA01/10gasm/datafile/system.256.776886753 YES
2 +DATA01/10gasm/datafile/undotbs1.258.776886753 YES
3 +DATA01/10gasm/datafile/sysaux.257.776886753 YES
4 +DATA01/10gasm/datafile/users.259.776886755 YES
SQL> select GROUP_NUMBER, FILE_NUMBER, NAME
2 from v$asm_alias
3 group by GROUP_NUMBER, FILE_NUMBER, NAME;
File
GROUP_NUMBER Number NAME
------------ ---------- ------------------------------------------------
1 256 SYSTEM.256.776886753
1 257 SYSAUX.257.776886753
1 258 UNDOTBS1.258.776886753
1 259 USERS.259.776886755
1 260 Current.260.776886827
1 261 group_1.261.776886835
1 262 group_2.262.776886835
1 263 group_3.263.776886837
1 264 TEMP.264.776886851
1 265 spfile.265.777691577
1 4294967295 10GASM
1 4294967295 DATAFILE
1 4294967295 TEMPFILE
1 4294967295 ONLINELOG
1 4294967295 CONTROLFILE
1 4294967295 PARAMETERFILE
2 256 ROGER.256.777429425
2 4294967295 10GASM
2 4294967295 DATAFILE
19 rows selected.
这里我们来看datafile +DATA01/10GASM/DATAFILE/USERS.259.776886755 的分布情况。
SQL> select disk_kffxp, au_kffxp, xnum_kffxp
2 from x$kffxp
3 where GROUP_KFFXP=1
4 and NUMBER_KFFXP=259;
DISK_KFFXP AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
0 791 0
0 792 1
0 793 2
0 794 3
0 795 4
0 796 5
6 rows selected.
我们可以看到users这个datafile虽然大小为5m多一点点，但是分配了6个AU，而且都是连续的。
SQL> select GROUP_KFFXP, DISK_KFFXP, AU_KFFXP
2 from x$kffxp
3 where number_kffxp = (select file_number
4 from v$asm_alias
5 where name = 'USERS.259.776886755');
GROUP_KFFXP DISK_KFFXP AU_KFFXP
----------- ---------- ----------
1 0 791
1 0 792
1 0 793
1 0 794
1 0 795
1 0 796
6 rows selected.
SQL> set lines 200
SQL> col path for a40
SQL> select a.GROUP_KFFXP, a.DISK_KFFXP, a.AU_KFFXP, b.path
2 from x$kffxp a, v$asm_disk b, v$asm_alias c
3 where a.number_kffxp = c.file_number
4 and a.GROUP_KFFXP = b.group_number
5 and a.disk_kffxp = b.disk_number
6 and c.name = '&filename';
Enter value for filename: USERS.259.776886755
GROUP_KFFXP DISK_KFFXP AU_KFFXP PATH
----------- ---------- ---------- ----------------------------------------
1 0 791 /dev/sdb
1 0 792 /dev/sdb
1 0 793 /dev/sdb
1 0 794 /dev/sdb
1 0 795 /dev/sdb
1 0 796 /dev/sdb
6 rows selected.
SQL> create tablespace roger datafile '+DATA02' size 20m;
Tablespace created.
SQL> select file_id,file_name,AUTOEXTENSIBLE
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME AUT
---------- -------------------------------------------------- ---
1 +DATA01/10gasm/datafile/system.256.776886753 YES
2 +DATA01/10gasm/datafile/undotbs1.258.776886753 YES
3 +DATA01/10gasm/datafile/sysaux.257.776886753 YES
4 +DATA01/10gasm/datafile/users.259.776886755 YES
5 +DATA02/10gasm/datafile/roger.256.777429425 NO
下面用dd进行复制datafile ：
[oracle@10gasm ~]$ dd if=/dev/sdb of=users2.dbf bs=8192 skip=101248 count=641
641+0 records in
641+0 records out
5251072 bytes (5.3 MB) copied, 0.117082 seconds, 44.8 MB/s
[oracle@10gasm ~]$ dbv file=users2.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Sun Mar 11 20:08:20 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = users2.dbf
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 15
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing (Index): 0
Total Pages Processed (Other): 26
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 597
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 442362 (0.442362)
新加一个disk到DATA01磁盘组中，再次进行观察AU分布。
SQL> alter diskgroup data01 add disk '/dev/sde';
Diskgroup altered.
SQL> SELECT NVL(a.name, '[CANDIDATE]') disk_group_name
2 , b.path disk_file_path
3 , b.name disk_file_name
4 , b.failgroup disk_file_fail_group
5 , b.total_mb total_mb
6 , (b.total_mb - b.free_mb) used_mb
7 , ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
8 FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
9 ORDER BY a.name
10 /
Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB) Pct. Used
-------------------- ----------------- -------------------- -------------------- -------------- -------------- ---------
DATA01 /dev/sdb DATA01_0000 DATA01_0000 1,024 576 56.25
/dev/sde DATA01_0001 DATA01_0001 1,024 441 43.07
******************** -------------- --------------
2,048 1,017
DATA02 /dev/sdd DATA02_0001 DATA02_0001 1,024 73 7.13
/dev/sdc DATA02_0000 DATA02_0000 1,024 73 7.13
******************** -------------- --------------
2,048 146
-------------- --------------
Grand Total: 4,096 1,163
我们可以发现，加上了/dev/sde以后，asm 完成了reblance操作。
SQL> set lines 200
SQL> col path for a40
SQL> select a.GROUP_KFFXP, a.DISK_KFFXP, a.AU_KFFXP, b.path
2 from x$kffxp a, v$asm_disk b, v$asm_alias c
3 where a.number_kffxp = c.file_number
4 and a.GROUP_KFFXP = b.group_number
5 and a.disk_kffxp = b.disk_number
6 and b.group_number=1
7 and c.name like '%USERS%';
GROUP_KFFXP DISK_KFFXP AU_KFFXP PATH
----------- ---------- ---------- ----------------------------------------
1 0 792 /dev/sdb
1 0 794 /dev/sdb
1 0 796 /dev/sdb
1 1 408 /dev/sde
1 1 406 /dev/sde
1 1 407 /dev/sde
6 rows selected.
++++++ 使用dd进行复制datafile ++++++
[oracle@10gasm ~]$ dd if=/dev/sde of=user_1.dbf bs=1024k skip=406 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0175263 seconds, 59.8 MB/s
[oracle@10gasm ~]$ dd if=/dev/sdb of=user_2.dbf bs=1024k skip=792 count=1
dd if=/dev/sde of=user_5.dbf bs=1024k skip=408 count=1
dd if=/dev/sdb of=user_6.dbf bs=8192 skip=101888 count=11+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0184495 seconds, 56.8 MB/s
[oracle@10gasm ~]$ dd if=/dev/sde of=user_3.dbf bs=1024k skip=407 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0150804 seconds, 69.5 MB/s
[oracle@10gasm ~]$ dd if=/dev/sdb of=user_4.dbf bs=1024k skip=794 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0118394 seconds, 88.6 MB/s
[oracle@10gasm ~]$ dd if=/dev/sde of=user_5.dbf bs=1024k skip=408 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.00784235 seconds, 134 MB/s
[oracle@10gasm ~]$ dd if=/dev/sdb of=user_6.dbf bs=8192 skip=101888 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00032412 seconds, 25.3 MB/s
[oracle@10gasm ~]$ dd if=user_1.dbf of=user_dd.dbf bs=8192 count=128
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.014171 seconds, 74.0 MB/s
[oracle@10gasm ~]$ dd if=user_2.dbf of=user_dd.dbf bs=8192 count=128 seek=128
dd if=user_3.dbf of=user_dd.dbf bs=8192 count=128 seek=256
128+0 records in
128+0 records out
dd if=user_4.dbf of=user_dd.dbf bs=8192 count=128 seek=384
1048576 bytes (1.0 MB) copied, 0.00760896 seconds, 138 MB/s
[oracle@10gasm ~]$ dd if=user_3.dbf of=user_dd.dbf bs=8192 count=128 seek=256
dd if=user_5.dbf of=user_dd.dbf bs=8192 count=128 seek=512
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.00653819 seconds, 160 MB/s
[oracle@10gasm ~]$ dd if=user_4.dbf of=user_dd.dbf bs=8192 count=128 seek=384
128+0 records in
128+0 records out
dd if=user_6.dbf of=user_dd.dbf bs=8192 count=1 seek=6401048576 bytes (1.0 MB) copied, 0.00619976 seconds, 169 MB/s
[oracle@10gasm ~]$ dd if=user_5.dbf of=user_dd.dbf bs=8192 count=128 seek=512
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.0060106 seconds, 174 MB/s
[oracle@10gasm ~]$ dd if=user_6.dbf of=user_dd.dbf bs=8192 count=1 seek=640
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000270084 seconds, 30.3 MB/s
[oracle@10gasm ~]$ ls -ltr user_*
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_5.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_4.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_3.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_2.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_1.dbf
-rw-r--r-- 1 oracle oinstall 8192 Mar 12 07:17 user_6.dbf
-rw-r--r-- 1 oracle oinstall 5251072 Mar 12 07:19 user_dd.dbf
[oracle@10gasm ~]$ dbv file=user_dd.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Mar 12 07:19:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = user_dd.dbf
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 15
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing (Index): 0
Total Pages Processed (Other): 26
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 597
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 442362 (0.442362)
++++++ dd 复制spfile 内容 ++++++
SQL> set lines 200
SQL> col path for a40
SQL> select a.GROUP_KFFXP, a.DISK_KFFXP, a.AU_KFFXP, b.path
2 from x$kffxp a, v$asm_disk b, v$asm_alias c
3 where a.number_kffxp = c.file_number
4 and a.GROUP_KFFXP = b.group_number
5 and a.disk_kffxp = b.disk_number
6 and c.name = '&filename';
Enter value for filename: spfile.265.776886941
old 6: and c.name = '&filename'
new 6: and c.name = 'spfile.265.776886941'
GROUP_KFFXP DISK_KFFXP AU_KFFXP PATH
----------- ---------- ---------- ----------------------------------------
1 0 1004 /dev/sdb
[oracle@10gasm ~]$ dd if=/dev/sdb of=spfile.ora bs=8192 skip=128512 count=128
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.0624247 seconds, 16.8 MB/s
[oracle@10gasm ~]$ strings spfile.ora
10gasm.__db_cache_size=121634816
10gasm.__java_pool_size=4194304
10gasm.__large_pool_size=4194304
10gasm.__shared_pool_size=75497472
10gasm.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/10gasm/adump'
*.background_dump_dest='/home/oracle/admin/10gasm/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA01/10gasm/controlfile/current.260.776886827'
*.core_dump_dest='/home/oracle/admin/10gasm/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='10gasm'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gasmXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=+DATA01/10gasm/'
*.log_archive_dest='/home/oracle/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=62914560
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/10gasm/udump'
发表回复