Automatic Storage Management (ASM)在oracle的使用

jolly10發表於2009-03-09
ASMOracle
測試了一下ASM在資料庫中的使用，各種檔案的建立方法。

1.登入ASM庫建立diskgroup

Session ASM:

SQL> drop diskgroup dgroup1;

Diskgroup dropped.

SQL> create diskgroup dgroup1 normal redundancy
2 failgroup fgroup1 disk 'ORCL:VOL1','ORCL:VOL2'
3 failgroup fgroup2 disk 'ORCL:VOL3','ORCL:VOL4';

Diskgroup created.

SQL> create diskgroup dgroup2 external redundancy
2 disk 'ORCL:VOL5','ORCL:VOL6';

Diskgroup created.

SQL> select group_number,state,type,total_mb,free_mb From v$asm_diskgroup;

GROUP_NUMBER STATE TYPE TOTAL_MB FREE_MB
------------ ----------- ------ ---------- ----------
1 MOUNTED NORMAL 396 290
2 MOUNTED EXTERN 198 146

SQL> select group_number,disk_number,mount_status,header_status,state,total_mb,free_mb,name,failgroup from v$asm_disk
2 order by PATH;

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE TOTAL_MB FREE_MB NAME FAILGROUP
------------ ----------- ------- ------------ -------- ---------- ---------- --------------- -------------------

1 0 CACHED MEMBER NORMAL 99 69 VOL1 FGROUP1
1 1 CACHED MEMBER NORMAL 99 76 VOL2 FGROUP1
1 2 CACHED MEMBER NORMAL 99 71 VOL3 FGROUP2
1 3 CACHED MEMBER NORMAL 99 74 VOL4 FGROUP2
2 0 CACHED MEMBER NORMAL 99 72 VOL5 VOL5
2 1 CACHED MEMBER NORMAL 99 74 VOL6 VOL6

6 rows selected.


2.在init.ora中增加如下引數：
db_create_file_dest='+dgroup1'
db_recovery_file_dest='+dgroup2'


[oracle@rhel131 pfile]$ export ORACLE_SID=ORCL

[oracle@rhel131 pfile]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 20 11:04:35 2009

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

Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/admin/orcl/pfile/init_asm.ora';
ORACLE instance started.

Total System Global Area 335544320 bytes
Fixed Size 1219280 bytes
Variable Size 104858928 bytes
Database Buffers 226492416 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.


3.DB Session 啟動後來建立ASM的datafile。

SQL> create tablespace tspace1;

Tablespace created.


SQL> select name From v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system.dbf
/u01/app/oracle/oradata/orcl/undo.dbf
/u01/app/oracle/oradata/orcl/sysaux.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/D1SPACE_001.dbf
+DGROUP1/orcl/datafile/tspace1.256.679316749

6 rows selected.


也可以建立表空間時建立大小

Session DB:

SQL> create tablespace tspace2 datafile '+dgroup1' size 10m autoextend on;

Tablespace created.

SQL> select name,bytes From v$datafile;

NAME BYTES
-------------------------------------------------- ----------
/u01/app/oracle/oradata/orcl/system.dbf 251658240
/u01/app/oracle/oradata/orcl/undo.dbf 125829120
/u01/app/oracle/oradata/orcl/sysaux.dbf 52428800
/u01/app/oracle/oradata/orcl/users01.dbf 17039360
/u01/app/oracle/oradata/orcl/D1SPACE_001.dbf 104857600
+DGROUP1/orcl/datafile/tspace1.256.679316749 104857600
+DGROUP1/orcl/datafile/tspace2.257.679323037 10485760

7 rows selected.

修改表空間的大小

Session DB:

SQL> ALTER DATABASE DATAFILE '+DGROUP1/orcl/datafile/tspace1.256.679316749' RESIZE 10M;

Database altered.

不用OMF建立undo表空間（+dgroup1/mydir目錄在此要先建好）

Session DB:

SQL> create undo tablespace myundo
2 datafile '+dgroup1/mydir/my_undo_ts' size 20m;

Tablespace created.

這種方法建立的檔案由於沒有使用OMF，如果刪除此表空間的話，需要手動來刪除ASM檔案

Session DB:

SQL> drop tablespace myundo;

Tablespace dropped.

Session ASM:

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS;

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
TSPACE1.256.679316749 1 256 N Y
TSPACE2.257.679323037 1 257 N Y
MYUNDO.258.679324117 1 258 N Y
mydir 1 4294967295 Y N
my_undo_ts 1 258 N N

7 rows selected.

手工來刪除ASM檔案


Session ASM:

SQL> alter diskgroup dgroup1 drop file '+dgroup1/mydir/my_undo_ts';

Diskgroup altered.

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS;

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
TSPACE1.256.679316749 1 256 N Y
TSPACE2.257.679323037 1 257 N Y
mydir 1 4294967295 Y N

4.
測試到這裡，發現有一些是系統自動建立的alias,
之前沒有ASM檔案來建立alias,正好現在試驗一下alias是怎麼手動建立的


Session ASM:

SQL> alter diskgroup dgroup1 add alias '+dgroup1/mydir/tsapce1.dbf'
2 for '+dgroup1/orcl/datafile/TSPACE1.256.679316749';

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS;

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
TSPACE1.256.679316749 1 256 N Y
TSPACE2.257.679323037 1 257 N Y
mydir 1 4294967295 Y N
tsapce1.dbf 1 256 N N

6 rows selected.


修改別名可以透過rename子句


Session ASM:

SQL> alter diskgroup dgroup1 rename alias '+dgroup1/mydir/tsapce1.dbf'
2 to '+dgroup1/mydir/tsapce1.datafile';

Diskgroup altered.

刪除一個別名，這裡官方文件是用delete來刪除的，可我刪除不掉，用drop可用，看來官方文件也會有錯。

SQL> alter diskgroup dgroup1 delete alias '+dgroup1/mydir/tsapce1.datafile';
alter diskgroup dgroup1 delete alias '+dgroup1/mydir/tsapce1.datafile'
*
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter diskgroup dgroup1 drop alias '+dgroup1/mydir/tsapce1.datafile';

Diskgroup altered.


5.建立用OMF建立ASM的redo檔案

Session DB:

SQL> alter database add logfile;

Database altered.

Session ASM:

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS;

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
TSPACE1.256.679316749 1 256 N Y
TSPACE2.257.679323037 1 257 N Y
ONLINELOG 1 4294967295 Y Y
group_4.258.679325313 1 258 N Y
mydir 1 4294967295 Y N
ORCL 2 4294967295 Y Y
ONLINELOG 2 4294967295 Y Y
group_4.256.679325321 2 256 N Y

10 rows selected.

發現不僅group_number 1裡增加了一個redo檔案，且增加了group_number為2的兩個目錄及檔案。這是因為redo的STRIPE是FINE。

SQL> select * from v$asm_template where name in ('ONLINELOG','DATAFILE') and group_number=1;

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME
------------ ------------ ------ ------ - ------------------------------
1 4 MIRROR FINE Y ONLINELOG
1 5 MIRROR COARSE Y DATAFILE


SQL> select group_number,block_size,bytes,type,redundancy,striped from v$asm_file;

GROUP_NUMBER BLOCK_SIZE BYTES TYPE REDUND STRIPE
------------ ---------- ------------ --------------- ------ ------
1 8192 10493952 DATAFILE MIRROR COARSE
1 8192 10493952 DATAFILE MIRROR COARSE
1 512 104858112 ONLINELOG MIRROR FINE
2 512 104858112 ONLINELOG UNPROT FINE


每個redo檔案預設的大小是100M，兩個共200M；但是在資料庫v$log檢視裡只會顯示一個100M的大小。

SQL> select * From v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 61 10485760 1 YES INACTIVE 328875764 07-FEB-09
2 1 62 10485760 1 NO CURRENT 328876135 07-FEB-09
3 1 60 10485760 1 YES INACTIVE 328875428 07-FEB-09
4 1 0 104857600 2 YES UNUSED 0


6.建立控制檔案

對於現有的資料庫，要將controlfile放在ASM disk上，要先將init.ora的CONTROL_FILES引數先remark掉，啟動到nomount下，建立

controlfile,步驟如下：

SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown immediate;

將init_asm.ora的CONTROL_FILES引數拿掉，現在的init_asm.ora如下：
[oracle@rhel131 pfile]$ cat init_asm.ora
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=71303168
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracl

e/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/oracle/admin/orcl/archive'
*.open_cursors=300
*.pga_aggregate_target=111149056
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=334495744
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

db_create_file_dest='+dgroup1'
db_recovery_file_dest='+dgroup2'

[oracle@rhel131 pfile]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 20 14:01:35 2009

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/admin/orcl/pfile/init_asm.ora';
ORACLE instance started.

Total System Global Area 335544320 bytes
Fixed Size 1219280 bytes
Variable Size 104858928 bytes
Database Buffers 226492416 bytes
Redo Buffers 2973696 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
3 4 5 MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo1.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo2.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo3.log' SIZE 10M,
GROUP 4 (
'+DGROUP1/orcl/onlinelog/group_4.258.679325313',
'+DGROUP2/orcl/onlinelog/group_4.256.679325321'
) SIZE 100M
-- STANDBY LOGFILE
6 DATAFILE
'/u01/app/oracle/oradata/orcl/system.dbf',
7 '/u01/app/oracle/oradata/orcl/undo.dbf',
'/u01/app/oracle/oradata/orcl/sysaux.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'+DGROUP1/orcl/datafile/tspace1.256.679316749',
'+DGROUP1/orcl/datafile/tspace2.257.679323037'
CHARACTER SET WE8MSWIN1252
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 ;

Control file created.

到ASM session檢查一下是否建立在asm disk上。
ASM Session:

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS;

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
TSPACE1.256.679316749 1 256 N Y
TSPACE2.257.679323037 1 257 N Y
ONLINELOG 1 4294967295 Y Y
group_4.258.679325313 1 258 N Y
CONTROLFILE 1 4294967295 Y Y
Current.259.679327319 1 259 N Y
mydir 1 4294967295 Y N
ORCL 2 4294967295 Y Y
ONLINELOG 2 4294967295 Y Y
group_4.256.679325321 2 256 N Y
CONTROLFILE 2 4294967295 Y Y
Current.257.679327319 2 257 N Y

14 rows selected.

看到分別兩個group_number上都多了一個CONTROLFILE目錄和一個currnet打頭的控制檔案。


根據trace出來的controlfile提示繼續開啟資料庫

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE RENAME FILE 'MISSING00005' TO '/u01/app/oracle/oradata/orcl/D1SPACE_001.dbf';

Database altered.

SQL> ALTER TABLESPACE "D1SPACE" ONLINE;

Tablespace altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M;

Tablespace altered.

最後要記錄修改引數檔案的control_files引數

*.control_files='+dgroup2/orcl/CONTROLFILE/Current.257.679327319','+dgroup1/orcl/CONTROLFILE/Current.259.679327319'


7.將歸檔日誌放在asm

由於init_asm.ora裡已指定了db_recovery_file_dest引數，要將歸檔日誌放在此引數指定的asm file，需要先註冊掉LOG_ARCHIVE_DEST_n

引數。

[oracle@rhel131 pfile]$ cat init_asm.ora
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=71303168
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracl

e/oradata/orcl/control03.ctl'
*.control_files='+dgroup2/orcl/CONTROLFILE/Current.257.679327319','+dgroup1/orcl/CONTROLFILE/Current.259.679327319'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
#*.log_archive_dest_1='location=/u01/app/oracle/admin/orcl/archive'
*.open_cursors=300
*.pga_aggregate_target=111149056
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=334495744
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

db_create_file_dest='+dgroup1'
db_recovery_file_dest='+dgroup2'

SQL> startup pfile='/u01/app/oracle/admin/orcl/pfile/init_asm.ora';
ORACLE instance started.

SQL> alter system switch logfile;

System altered.


ASM Session:

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS;

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
TSPACE1.256.679316749 1 256 N Y
TSPACE2.257.679323037 1 257 N Y
ONLINELOG 1 4294967295 Y Y
group_4.258.679325313 1 258 N Y
CONTROLFILE 1 4294967295 Y Y
Current.259.679327319 1 259 N Y
mydir 1 4294967295 Y N
ORCL 2 4294967295 Y Y
ONLINELOG 2 4294967295 Y Y
group_4.256.679325321 2 256 N Y
CONTROLFILE 2 4294967295 Y Y
Current.257.679327319 2 257 N Y
ARCHIVELOG 2 4294967295 Y Y
2009_02_20 2 4294967295 Y Y
thread_1_seq_64.258.679328655 2 258 N Y

17 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-564931/，如需轉載，請註明出處，否則將追究法律責任。

相關文章