Automatic Storage Management (ASM)在oracle的使用

最新推荐文章于 2021-08-26 10:45:58 发布

ckomuo086028

最新推荐文章于 2021-08-26 10:45:58 发布

阅读量107

点赞数

测试了一下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

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;

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;

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;

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;

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

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;

17 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-564931/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-564931/

ckomuo086028

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Automatic Storage Management (ASM)在oracle的使用

测试了一下ASM在数据库中的使用，各种文件的建立方法。1.登入ASM库建立diskgroupSession ASM:SQL> drop diskgroup dgroup1;Diskgroup dropped...
复制链接

扫一扫