oracle11gASM管理.docx
《oracle11gASM管理.docx》由会员分享,可在线阅读,更多相关《oracle11gASM管理.docx(43页珍藏版)》请在冰豆网上搜索。
oracle11gASM管理
一, 基本维护 :
注意:
以下除OS之外的命令及过程基本都可以通过ASMCA中的ASM相关操作以及
OracleEnterpriseMananger来管理.
1.查看ASM磁盘及磁盘组:
--SQL查看有哪些磁盘组.
[root@rac01~]#su-grid
[grid@rac01~]$sqlplus"/assysasm"
SQL>selectgroup_number,name,state,typefromv$asm_diskgroup;
GROUP_NUMBERNAMESTATETYPE
-----------------------------------------------------------
1OCR_VOTEMOUNTEDEXTERN
2ORADATAMOUNTEDEXTERN
3ORAFLASHMOUNTEDEXTERN
通过一些字段total_mb及free_mb等可以看出磁盘组的空间使用情况.
--通过SQL语句查看ASM磁盘
SQL>setline2000
SQL>selectgroup_number,name,failgroup,pathfromv$asm_disk;
GROUP_NUMBERNAMEFAILGROUPPATH
----------------------------------------------------------------------------------------------------
2ASMDATA01ASMDATA01ORCL:
ASMDATA01
3ASMDATA02ASMDATA02ORCL:
ASMDATA02
1OCR_VOTE01OCR_VOTE01ORCL:
OCR_VOTE01
通过group_number可以将磁盘与磁盘组联系起来(一个磁盘组可以对应多个磁盘).这里
可以看到磁盘OCR_VOTE01对应OCR_VOTE磁盘组,磁盘ASMDATA01对应ORADATA
磁盘组,磁盘ASMDATA02对应ORAFLASH磁盘组(建议名称一致比较好)。
--通过asmcmd查看磁盘组(每个磁盘组表现为一个目录):
[root@rac01~]#su-grid
[grid@rac01~]$asmcmd
ASMCMD>ls-al
StateTypeRebalName
MOUNTEDEXTERNNOCR_VOTE/
MOUNTEDEXTERNNORADATA/
MOUNTEDEXTERNNORAFLASH/
备注:
或者ASMCMD>lsdg
--通过OS下oracleasm命令查看ASM磁盘.
[root@rac01bin]#/usr/sbin/oracleasmlistdisks
ASMDATA01
ASMDATA02
OCR_VOTE01
2.认识ASM相关路径:
通过asmcm命令可以看到ASM会根据dbca时制定的datafile路径,在ORADATA磁盘组
下面建立一些默认的路径(一般是db_name下ARCHIVELOG/,CONTROLFILE/,
DATAFILE/,ONLINELOG/,PARAMETERFILE/,TEMPFILE/,目录都是大写,其
中后面小写的arch是用户自己建的,SYS字段为Y的都是系统自建的,N是用户定义
的)
[root@rac01bin]#su-grid
[grid@rac01~]$asmcmd
ASMCMD>ls
OCR_VOTE/
ORADATA/
ORAFLASH/
ASMCMD>cdoradata
ASMCMD>ls
RACDB/
ASMCMD>cdracdb
ASMCMD>ls-l
TypeRedundStripedTimeSysName
YARCHIVELOG/
YCONTROLFILE/
YDATAFILE/
YONLINELOG/
YPARAMETERFILE/
YTEMPFILE/
Narch/
Ncontrol01.ctl=>+ORADATA/RACDB/CONTROLFILE/Current.256.739387263
Ncontrol02.ctl=>+ORADATA/RACDB/CONTROLFILE/Current.257.739387265
Nredo01.log=>+ORADATA/RACDB/ONLINELOG/group_1.258.739387273
Nredo02.log=>+ORADATA/RACDB/ONLINELOG/group_2.259.739387281
Nredo03.log=>+ORADATA/RACDB/ONLINELOG/group_3.266.739393547
Nredo04.log=>+ORADATA/RACDB/ONLINELOG/group_4.267.739393547
Nspfileracdb.ora=>+ORADATA/RACDB/PARAMETERFILE/spfile.268.739393555
ASMCMD>
--归档路径ARCHIVELOG:
通过oracle数据库参数文件看到归档路径:
SQL>showparameterlog_archive_dest
log_archive_dest_1stringLOCATION=+ORADATA/RACDB/arch
可以看到归档路径"+ORADATA/RACDB/arch",那么+ORADATA/RACDB/ARCHIVELOG
下面又是保存的什么文件呢?
ASMCMD>pwd
+oradata/racdb/arch--查看参数中的归档路径
ASMCMD>ls-l
TypeRedundStripedTimeSysName
N1_204_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_28/thread_1_seq_204.478.768441403
N1_205_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_205.481.768443221
N1_206_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_206.482.768443233
N1_207_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_207.483.768443239
N1_208_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_208.485.768443253
N1_209_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_209.486.768443277
N1_210_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_210.487.768443293
N1_211_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_211.489.768443319
N1_212_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_212.490.768511059
N1_213_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_213.493.768512249
N1_214_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_214.494.768512253
N1_215_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_1_seq_215.499.768522885
N1_216_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_30/thread_1_seq_216.500.768530093
N2_100_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_2_seq_100.488.768443301
N2_101_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_2_seq_101.491.768511059
N2_102_739387258.arc=>
+ORADATA/RACDB/ARCHIVELOG/2011_11_29/thread_2_seq_102.492.768511065
ASMCMD>
用户自己定义的归档格式以及归档路径指向ASM自动建立的目录及文件,自定义生成的
归档相当于快捷方式(或软连接),实际文件为ASM默认路径的归档文件。
也可以手工为OracleASMFilename生成别名:
ALTERDISKGROUPORADATAADDALIAS
'+oradata/racdb/datafile/test01.dbf'
FOR'+oradata/racdb/datafile/TEST.340.740166807';
ASMCMD>pwd
+oradata/racdb/ARCHIVELOG--查看ASM系统自己默认的归档路径
ASMCMD>ls-l
TypeRedundStripedTimeSysName
Y2011_11_28/
Y2011_11_29/
Y2011_11_30/
归档文件是按照每天来进行存储的,并且有目录分开.
ASMCMD>cd2011_11_29/
ASMCMD>ls-l
TypeRedundStripedTimeSysName
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_205.481.768443221
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_206.482.768443233
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_207.483.768443239
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_208.485.768443253
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_209.486.768443277
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_210.487.768443293
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_1_seq_211.489.768443319
ARCHIVELOGUNPROTCOARSENOV2919:
00:
00Ythread_1_seq_212.490.768511059
ARCHIVELOGUNPROTCOARSENOV2919:
00:
00Ythread_1_seq_213.493.768512249
ARCHIVELOGUNPROTCOARSENOV2919:
00:
00Ythread_1_seq_214.494.768512253
ARCHIVELOGUNPROTCOARSENOV2922:
00:
00Ythread_1_seq_215.499.768522885
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_2_seq_100.488.768443301
ARCHIVELOGUNPROTCOARSENOV2919:
00:
00Ythread_2_seq_101.491.768511059
ARCHIVELOGUNPROTCOARSENOV2919:
00:
00Ythread_2_seq_102.492.768511065
ARCHIVELOGUNPROTCOARSENOV2922:
00:
00Ythread_2_seq_103.495.768522877
ARCHIVELOGUNPROTCOARSENOV2922:
00:
00Ythread_2_seq_104.496.768522879
ARCHIVELOGUNPROTCOARSENOV2922:
00:
00Ythread_2_seq_105.497.768522883
ARCHIVELOGUNPROTCOARSENOV2922:
00:
00Ythread_2_seq_106.498.768522883
ARCHIVELOGUNPROTCOARSENOV2900:
00:
00Ythread_2_seq_99.484.768443235
ASMCMD>
--控制文件路径CONTROLFILE:
ASMCMD>cdracdb
ASMCMD>ls-l
TypeRedundStripedTimeSysName
Ncontrol01.ctl=>+ORADATA/RACDB/CONTROLFILE/Current.256.739387263
Ncontrol02.ctl=>+ORADATA/RACDB/CONTROLFILE/Current.257.739387265
查看初始化参数文件位置,实际文件也是类似软链接方式:
SQL>showparametercontrol
NAMETYPEVALUE
---------------------------------------------------------------------------
control_filesstring+ORADATA/racdb/control01.ctl,
+ORADATA/racdb/control02.ctl
如何增加控制文件呢?
例子如下:
SQL>alterdatabasemountdatabase;
SQL>alterdatabasebackupcontrolfileto'+ORADATA/racdb/control02.ctl';
SQL>alterdatabasebackupcontrolfileto'+ORADATA/racdb/control03.ctl';
SQL>altersystemsetcontrol_files='+ORADATA/racdb/control01.ctl','+DATA/racdb/control02.ctl','+DATA/racdb/control03.ctl'
scope=spfile;
SQL>startupmount
--数据文件路径DATAFILE:
ASMCMD>pwd
+oradata/racdb/datafile
ASMCMD>
ASMCMD>ls-l
TypeRedundStripedTimeSysName
DATAFILEUNPROTCOARSEOCT2918:
00:
00YBASE_DATA.317.754072293
DATAFILEUNPROTCOARSEOCT2918:
00:
00YBASE_DATA.318.754072319
DATAFILEUNPROTCOARSEOCT2918:
00:
00YBASE_DATA.319.754072331
DATAFILEUNPROTCOARSEOCT2918:
00:
00YBASE_DATA.320.754072345
NBASE_DATA01.DBF=>+ORADATA/RACDB/DATAFILE/BASE_DATA.317.754072293
NBASE_DATA02.DBF=>+ORADATA/RACDB/DATAFILE/BASE_DATA.318.754072319
NBASE_DATA03.DBF=>+ORADATA/RACDB/DATAFILE/BASE_DATA.319.754072331
NBASE_DATA04.DBF=>+ORADATA/RACDB/DATAFILE/BASE_DATA.320.754072345
DATAFILEUNPROTCOARSEOCT2918:
00:
00YLOGMNRTS.321.754072935
DATAFILEUNPROTCOARSEOCT2918:
00:
00YLOG_DATA.322.754072961
DATAFILEUNPROTCOARSEOCT2918:
00:
00YLOG_DATA.323.754072985
DATAFILEUNPROTCOARSEOCT2918:
00:
00YLOG_DATA.324.754073009
DATAFILEUNPROTCOARSEOCT2918:
00:
00YLOG_DATA.325.754073035
DATAFILEUNPROTCOARSEOCT2918:
00:
00Ynone=>SYSAUX.261.754063795
DATAFILEUNPROTCOARSEOCT2918:
00:
00Ynone=>SYSTEM.263.754063787
DATAFILEUNPROTCOARSEOCT2918:
00:
00Y+ORADATA/RACDB/DATAFILE/undotbs101.dbf=>
UNDOTBS1.260.758741005
DATAFILEUNPROTCOARSEOCT2918:
00:
00Ynone=>UNDOTBS2.258.754063807
DATAFILEUNPROTCOARSEOCT2918:
00:
00Ynone=>UNDOTBS3.257.754063809
DATAFILEUNPROTCOARSEOCT2918:
00:
00Ynone=>USERS.256.754063811
可以看出链接的名称及对应的实体文件名称都出现在datafile目录下,当然并
不是每个实体文件都有一个对应的链接的文件名称,例如初始建立数据库的
时候指定+ORADATA磁盘组后,数据库会自动建立初始数据文件,那时候的
文件就只有系统生成的,而没有用户指定的名称(链接名称),例如上面例子中
的LOGMNRTS.321.754072935,SYSTEM.263.754063787等.
在加入datafile或建立表空间时,如果只是指定+ORADATA,而不明确写出路径
及文件名称,那么就会生成系统自建文件而没有链接名称,如果文件使用类似
'+ORADATA/racdb/datafile/mytbs01.dbf',那么就会生成系统自建文件及对应的
链接文件.同样可以利用ALTERDISKGROUPXXADDALIAS增加别名.
----------------------------------------------------------------------------
--认识文件名
BASE_DATA.317.754072293是表空间名+文件编号.Incarnation.
+group/dbname/filetype/tag.file.incarnation格式中分别是磁盘组名,filetype是Oracle
文件类型,tag一般对一个表空间名,file.incarnation结合在一起确保唯一性.
----------------------------------------------------------------------------
测试:
新增数据文件或表空间
CREATETABLESPACEMYTBSDATAFILE
'+ORADATA/racdb/datafile/mytbs01.dbf'SIZE10MAUTOEXTENDOFF
LOGGING
ONLINE
PERMANENT
EXTENTMANAGEMENTLOCALUNIFORM.SIZE5M
BLOCKSIZE16K
SEGMENTSPACEMANAGEMENTAUTO
FLASHBACKON;
或者
CREATETABLESPACETONYTBSDATAFILE
'+ORADATA'SIZE10MAUTOEXTENDONNEXT5MMAXSIZE1024M
LOGGING
ONLINE
PERMANENT
EXTENTMANAGEMENTLOCALUNIFORM.SIZE5M
BLOCKSIZE16K
SEGMENTSPACEMANAGEMENTAUTO
FLASHBACKON;
增加数据文件
ALTERTABLESPACEMYTBSADDDATAFILE
'+ORADATA'SIZE10MAUTOEXTENDOFF;
如果设置了初始化参数db_create_file_dest(默认为空),连磁盘组名都不
需要写了,例如:
SQL>