DB-hub Technology Oracle Lab – ASM DB Grid Install 19.3
Oracle Lab – ASM DB Grid Install 19.3
1.环境准备
Linux 主机安装，请参考实验一和实验二。
主机名：orapg
IP地址：192.168.1.199
数据库：HAMSTER
内存: 8GB
Oracle Database 19.3
OS: CentOS Linux 7.9
安装包：
LINUX.X64_193000_grid_home.zip
LINUX.X64_193000_db_home.zip
vi /etc/hosts
192.168.1.199 orapg orapg.lab.com
1.1.用户，组，安装目录
groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin
groupadd asmdba
groupadd asmoper
usermod -g oinstall -G dba,oper,asmadmin,asmdba,asmoper oracle
1.2.Prepare directory
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/19.3/db
GRID_HOME=/oracle/app/oracle/product/19.3/grid
cd /
mkdir -p /oracle/app/oracle/product/19.3/db
mkdir -p /oracle/app/oracle/product/19.3/grid
chown -R oracle:oinstall /oracle
chmod -R 775 /oracle
1.3.oracle profile
登录oracle
vi .bash_profile
export ORACLE_SID=HAMSTER
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=#ORACLE_BASE/product/19.3/db
export GRID_HOME=#ORACLE_BASE/product/19.3/grid
export TNS_ADMIN=#ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:#LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=#ORACLE_HOME/bin:#GRID_HOME/bin:#PATH
export DISPLAY=localhost:10.0
export PS1='[\u@\h:`pwd`]#'
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
2.配置共享存储
2.1.共享磁盘
打开Command Prompt窗口，CD到VMware安装目录，运行以下命令：
vmware-vdiskmanager.exe -c -s 5g -a lsilogic -t 2 C:\VMWare\orapg-vdisk\data_asm.vmdk
vmware-vdiskmanager.exe -c -s 5g -a lsilogic -t 2 C:\VMWare\orapg-vdisk\fra_asm.vmdk
2.2.虚拟机加载共享磁盘
关闭虚拟机，关闭orapg窗口。
用记事本打开orapg.vmx
添加以下：
#shared disks configure
disk.locking = "FALSE"
diskLib.dataCacheMaxSize = "0"
diskLib.dataCacheMaxReadAheadSize = "0"
diskLib.dataCacheMinReadAheadSize = "0"
diskLib.maxUnsyncedWrites = "0"
scsi1.present = "TRUE"
scsi1.virtualDev = "lsilogic"
scsil.sharedBus = "VIRTUAL"
scsi1:0.present = "TRUE"
scsi1:0.mode = "independent-persistent"
scsi1:0.fileName = "C:\VMWare\orapg-vdisk\data_asm.vmdk"
scsi1:0.deviceType = "disk"
scsi1:0.redo = ""
scsi1:1.present = "TRUE"
scsi1:1.mode = "independent-persistent"
scsi1:1.fileName = "C:\VMWare\orapg-vdisk\fra_asm.vmdk"
scsi1:1.deviceType = "disk"
scsi1:1.redo = ""
2.3.重新打开虚拟机vmx文件
3.安装ASM
3.1.ASM安装包
$yum search oracleasm
Loaded plugins: langpacks, ulninfo
============================ N/S matched: oracleasm ============================
kmod-oracleasm.x86_64 : oracleasm kernel module(s)
oracleasm-support.x86_64 : The Oracle Automatic Storage Management support
: programs.
3.2.安装oracleasm包
yum -y install kmod-oracleasm.x86_64
yum -y install oracleasm-support.x86_64
rpm -ivh oracleasmlib-2.0.12-1.el7.x86_64.rpm
3.3.初始化ASM
#sudo oracleasm configure -i
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
#sudo oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
查看设置结果：
$oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=oracle
ORACLEASM_GID=asmadmin
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_SCAN_DIRECTORIES=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
3.4.ASM磁盘分区
cd /dev
$ls -l sd*
brw-rw---- 1 root disk 8, 0 Jan 22 22:19 sda
brw-rw---- 1 root disk 8, 1 Jan 22 22:19 sda1
brw-rw---- 1 root disk 8, 2 Jan 22 22:19 sda2
brw-rw---- 1 root disk 8, 16 Jan 22 22:19 sdb
brw-rw---- 1 root disk 8, 32 Jan 22 22:19 sdc
$fdisk sdb
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x906c3ce9.
Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
g create a new empty GPT partition table
G create an IRIX (SGI) partition table
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-10485759, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-10485759, default 10485759):
Using default value 10485759
Partition 1 of type Linux and of size 5 GiB is set
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
虚拟磁盘sdc执行相同的操作。
3.5.创建ASM磁盘
$ls -l sd*
brw-rw---- 1 root disk 8, 0 Jan 22 22:19 sda
brw-rw---- 1 root disk 8, 1 Jan 22 22:19 sda1
brw-rw---- 1 root disk 8, 2 Jan 22 22:19 sda2
brw-rw---- 1 root disk 8, 16 Jan 22 23:21 sdb
brw-rw---- 1 root disk 8, 17 Jan 22 23:21 sdb1
brw-rw---- 1 root disk 8, 32 Jan 22 23:22 sdc
brw-rw---- 1 root disk 8, 33 Jan 22 23:22 sdc1
# sudo oracleasm createdisk DATA sdb1
Writing disk header: done
Instantiating disk: done
# sudo oracleasm createdisk FRA sdc1
Writing disk header: done
删除磁盘命令,仅供参考：
oracleasm deletedisk FRA
Instantiating disk: done
3.6.验证
#sudo oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
#sudo oracleasm listdisks
DATA
FRA
#sudo oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
#sudo oracleasm listdisks
DATA
FRA
4.预安装检查
If you do not use an Oracle Preinstallation RPM, and you want to use the Cluster Verification Utility, then you must install the cvuqdisk RPM.
Without cvuqdisk, Cluster Verification Utility cannot discover shared disks, and you receive the error message “Package cvuqdisk not installed” when you run Cluster Verification Utility. Use the cvuqdisk RPM for your hardware (for example, x86_64).
4.1.解压Grid安装包
解压缩安装包到$GRID_HOME目录
unzip /home/oracle/LINUX.X64_193000_grid_home.zip -d $GRID_HOME
rm /home/oracle/LINUX.X64_193000_grid_home.zip
4.2.安装cvuqdisk
Locate the cvuqdisk RPM package, which is located in the directory Grid_home/cv/rpm. Where Grid_home is the Oracle Grid Infrastructure home directory. Ex: /oracle/product/12.2.0.1/grid
Copy the cvuqdisk package to each node on the cluster. You should ensure that each node is running the same version of Linux.
Set the environment variable CVUQDISK_GRP to point to the group that owns cvuqdisk, typically oinstall.
For example:
export CVUQDISK_GRP=asmadmin
Log in as root. In the directory where you have saved the cvuqdisk RPM, install the cvuqdisk package. For example:
rpm -ivh cvuqdisk-1.0.10-1.rpm
If you have an existing version of cvuqdisk, then enter the following command to deinstall the existing version:
rpm -e cvuqdisk
cd $GRID_HOME/cv/rpm
export CVUQDISK_GRP=asmadmin
sudo rpm -ivh cvuqdisk-1.0.10-1.rpm
4.3.运行runcluvfy.sh
cd $GRID_HOME
./runcluvfy.sh stage -pre crsinst -n orapg root -verbose
Pre-check for cluster services setup was successful.
CVU operation performed: stage -pre crsinst
Date: Mar 2, 2020 11:39:05 AM
CVU home: /home/grid/cvuqdisk/
User: grid
或者提供root password：
./runcluvfy.sh stage -pre crsinst -n orapg -method root -verbose
问题1： 内存不足，安装19c grid 需要至少8GB内存。2GB内存出现以下错误：
PRVE-0426: /dev/shm less than the require
sudo mount -o remount,size=2112M /dev/shm
df -h
tmpfs 2.1G 16K 2.1G 1% /dev/shm
增加到8GB:
tmpfs 3.9G 16K 3.9G 1% /dev/shm
问题2：
Verifying /dev/shm mounted as temporary file system ...FAILED
orapg: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm
CVU在不适用的RHEL7上执行了/dev/shm安装检查。
MOS：如果在RHEL7上执行/dev/shm安装检查，可忽略此问题。
问题3：
Verifying Swap Size ...FAILED
orapg: PRVF-7573 : Sufficient swap size is not available on node "orapg"
[Required = 7.7771GB (8154932.0KB) ; Found = 4GB (4194300.0KB)]
可以忽略,但是如果内存和swap空间不足，可能会导致安装失败。
问题4：
Verifying Daemon "avahi-daemon" not configured and running ...FAILED
orapg: PRVG-1360 : Daemon process "avahi-daemon" is running on node "orapg"
可以关闭avahi-daemon，安装GI时出错可以SKIP。
sudo systemctl stop avahi-daemon.socket
sudo systemctl stop avahi-daemon.socket
问题5：
Verifying Network Time Protocol (NTP) ...FAILED
orapg: PRVG-1017 : NTP configuration file "/etc/chrony.conf" is present on
nodes "orapg" on which NTP daemon or service was not running
sudo systemctl stop chronyd
sudo systemctl disable chronyd
sudo mv /etc/chrony.conf /etc/chrony.conf.bak
问题6：
Verifying RPM Package Manager database ...INFORMATION
PRVG-11250 : The check "RPM Package Manager database" was not performed because
it needs 'root' user privileges.
可以忽略，或者提供root权限：
./runcluvfy.sh stage -pre crsinst -n orapg -method root -verbose
5.安装Grid软件
5.1.启动安装程序
$GRID_HOME/gridSetup.sh &
选择为独立服务器配置集群件:
5.2.磁盘组
disk group name： DATA
Set the redundancy to “External” and select your disks
NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware RAID or mirroring
If you don’t see your disks straight away then click the “Change Discovery Path” button and set the path to “/dev/oracleasm/disks/*”
5.3.ASMSNMP
ASMSNMP Password: oracle
5.4.Enterprise Manager (EM)
5.5.System Group
5.6.Installation Location
5.7.Create Inventory
5.8.Root Script Execution
5.9.Prerequisite Checks
5.10.Summary
5.11.Install
6.配置ASM磁盘组
6.1.启动asmca
运行： asmca
6.2.Create Disk Group
7.安装Oracle 19c
7.1.上传oracle 数据库安装文件
解压缩到$ORACLE_HOME
unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME
rm –f LINUX.X64_193000_db_home.zip
7.2.启动安装程序
$ORACLE_HOME/runInstaller &
7.3.Database Installation Option
7.4.Database Edition
7.5.Installation Location
7.6.System Groups
7.7.Root Script Execution
7.8.Prerequisite Checks
Ignore this warning:
7.9.Summary
7.10.Install
8.建数据库
8.1.启动dbca
运行dbca
8.2.creation Mode
8.3.Deployment Type
8.4.Database Identification
8.5.Storage Option
Database files storage type: ASM
Database files location: +DATA/{DB_UNIQUE_NAME}
Multiplex redo logs and control files:
File Location Variables:
8.6.Fast Recovery Option
Archive log destinations use FRA by default:
8.7.Network Configuration
8.8.Database Options
8.9.Configuration Options
Use Automatic Shared Memory Management:
Process: 150
Character sets: default
Connection mode:default
Sample schemas: default
如果选AMM，出错：
解决方法:使用Automatic Shared Memory Management(ASMM)。
官方的Administrator guide上的说明: 11g的文档中，官方建议使用自动内存管理(AMM)。12c的官方文档上，官方也建议使用自动内存管理(AMM)，但是 SGA+PGA 不要超过4G。
8.10.Management Options
8.11 User Credentials
8.12.Creation Option
8.13.Summary
8.14.Progress Page
8.15.Finish
9.数据库管理
9.1.检查cluster所有资源状态
[oracle@orapg:/home/oracle]$crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE orapg STABLE
ora.FRA.dg
ONLINE ONLINE orapg STABLE
ora.asm
ONLINE ONLINE orapg Started,STABLE
ora.ons
OFFLINE OFFLINE orapg STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE orapg STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE orapg STABLE
ora.hamster.db
1 ONLINE ONLINE orapg Open,HOME=/oracle/ap
p/oracle/product/19.
3/db,STABLE
--------------------------------------------------------------------------------
9.2.查看数据库
#srvctl status database -d HAMSTER
Database is running.
#srvctl config database -d HAMSTER -a
Database unique name: HAMSTER
Database name: HAMSTER
Oracle home: /oracle/app/oracle/product/19.3/db
Oracle user: oracle
Spfile: +DATA/HAMSTER/PARAMETERFILE/spfile.266.1062603593
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
Database is enabled
OSDBA group:
OSOPER group:
Database instance: HAMSTER
9.3.启动和停止数据库实例
srvctl stop database -d HAMSTER
srvctl start database -d HAMSTER
srvctl stop database -d HAMSTER -o immediate
srvctl start database -d HAMSTER -o nomount
SQL> set linesize 500
SQL> col host_name format a10;
SQL> select host_name,inst_id,instance_name,version from gv$instance;
HOST_NAME INST_ID INSTANCE_NAME VERSION
---------- ---------- ---------------- -----------------
orapg 1 HAMSTER 19.0.0.0.0
9.4.查看ASM信息
#srvctl status asm
ASM is running on orapg
#srvctl status asm -a
ASM is running on orapg
ASM is enabled.
9.5.启动和停止磁盘组
srvctl start diskgroup -g DATA
srvctl start diskgroup -g FRA
9.6.数据库和ASM的启动顺序
- CSS(Cluster Ready Services)，HAS(High Availability Service)服务启动
crsctl check css
crsctl check has
crsctl status resource -t
crs_stat -p ora.cssd
CRS-275: This command is not supported in Oracle Restart environment.
crs_stat -p ora.diskmon
CRS-275: This command is not supported in Oracle Restart environment.
crsctl start has
crsctl stop has
- ASM(Automatic Storage Management)启动
-- startup ASM
sqlplus / as sysasm
startup
-- shutdown ASM
sqlplus / as sysasm
shutdown immediate
select instance_name,status from v$instance;
- 数据库实例启动
sqlplus / as sysdba
startup
9.7.查看数据库文件
select name from v#datafile
union
select member from v#logfile
union
select name from v#controlfile
union
select name from v#tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/HAMSTER/CONTROLFILE/current.257.1062601719
+DATA/HAMSTER/DATAFILE/sysaux.262.1062601727
+DATA/HAMSTER/DATAFILE/system.261.1062601723
+DATA/HAMSTER/DATAFILE/undotbs1.263.1062601727
+DATA/HAMSTER/DATAFILE/users.265.1062601735
+DATA/HAMSTER/ONLINELOG/group_1.258.1062601719
+DATA/HAMSTER/ONLINELOG/group_2.259.1062601721
+DATA/HAMSTER/ONLINELOG/group_3.260.1062601721
+DATA/HAMSTER/TEMPFILE/temp.264.1062601727
+FRA/HAMSTER/CONTROLFILE/current.256.1062601719
+FRA/HAMSTER/ONLINELOG/group_1.257.1062601721
+FRA/HAMSTER/ONLINELOG/group_2.258.1062601721
+FRA/HAMSTER/ONLINELOG/group_3.259.1062601721
13 rows selected.
9.8.Space used in ASM Disk Groups
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a10 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_gb FORMAT 999,999.9 HEAD 'Total(GB)'
COLUMN used_gb FORMAT 999,999.9 HEAD 'Used(GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Used%'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_gb used_gb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb/1024 total_gb
, (total_mb - free_mb) /1024 used_gb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA 512 4,096 4,194,304 CONNECTED EXTERN 5,116 2,800 54.73
FRA 512 4,096 4,194,304 CONNECTED EXTERN 5,116 736 14.39
--------------- --------------
Grand Total: 10,232 3,536
9.9.Disks contained within Disk Groups
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a25 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a30 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v#asm_diskgroup a RIGHT OUTER JOIN v#asm_disk b USING (group_number)
ORDER BY
a.name
/
9.10.List of ASM files as well as its volume information
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN size_gb FORMAT 999,999.9 HEAD 'Size MB'
COLUMN allocated FORMAT 999,999.9 HEAD 'Allocated MB'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF size_gb allocated ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF size_gb allocated ON report
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.size_gb
, db_files.allocated
, NVL(LPAD(db_files.type, 18), '<DIRECTORY>') type
, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes/1024/1024 size_gb
, f.space/1024/1024 allocated
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v#asm_file f RIGHT OUTER JOIN v#asm_alias a USING (group_number, file_number)
JOIN v#asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path
, volume_files.size_gb
, volume_files.allocated
, NVL(LPAD(volume_files.type, 18), '<DIRECTORY>') type
, volume_files.creation_date
, volume_files.disk_group_name
, null
FROM
( SELECT
g.name disk_group_name
, v.volume_name volume_name
, v.volume_device volume_device
, f.bytes/1024/1024 size_gb
, f.space/1024/1024 allocated
, f.type type
, TO_CHAR(f.creation_date, 'YYY-MON-DD HH24:MI:SS') creation_date
FROM
v#asm_file f RIGHT OUTER JOIN v#asm_volume v USING (group_number, file_number)
JOIN v#asm_diskgroup g USING (group_number)
) volume_files
WHERE volume_files.type IS NOT NULL
/
f.bytes: Number of bytes in the file (f.bytes/1024/1024 size_gb)
f.space: Number of bytes allocated to the file(f.space/1024/1024 allocated)
9.11.Disks performance metric
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 256
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_path FORMAT a30 HEAD 'Disk Path'
COLUMN reads FORMAT 999,999,999,999 HEAD 'Reads'
COLUMN writes FORMAT 999,999,999,999 HEAD 'Writes'
COLUMN read_errs FORMAT 999,999,999 HEAD 'Read|Errors'
COLUMN write_errs FORMAT 999,999,999 HEAD 'Write|Errors'
COLUMN read_time FORMAT 999,999,999,999 HEAD 'Read|Time'
COLUMN write_time FORMAT 999,999,999,999 HEAD 'Write|Time'
COLUMN mb_read FORMAT 999,999,999 HEAD 'Read|MB'
COLUMN mb_written FORMAT 999,999,999 HEAD 'Written|MB'
BREAK ON report ON disk_group_name SKIP 2
COMPUTE sum LABEL "" OF reads writes read_errs write_errs read_time write_time mb_read mb_written ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF reads writes read_errs write_errs read_time write_time mb_read mb_written ON report
SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read/1024/1024 mb_read
, b.bytes_written/1024/1024 mb_written
FROM
vasm_diskgroup a JOIN vasm_disk b USING (group_number)
ORDER BY
a.name
/
Disk Group Name Disk Path Reads Writes Errors Errors Time Time MB MB
-------------------- ------------------------------ ---------------- ---------------- ------------ ------------ ---------------- ---------------- ------------ ------------
DATA /dev/oracleasm/disks/DATA 83,971 285,382 0 0 43 9,944 1,286 9,441
******************** ---------------- ---------------- ------------ ------------ ---------------- ---------------- ------------ ------------
83,971 285,382 0 0 43 9,944 1,286 9,441
FRA /dev/oracleasm/disks/FRA 4,239 216,928 0 0 2 110 98 3,901
******************** ---------------- ---------------- ------------ ------------ ---------------- ---------------- ------------ ------------
4,239 216,928 0 0 2 110 98 3,901
---------------- ---------------- ------------ ------------ ---------------- ---------------- ------------ ------------
Grand Total: 88,210 502,310 0 0 44 10,054 1,384 13,342
9.12.find orphaned ASM files
SET VERIFY OFF
SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000
SET TRIMSPOOL ON
BEGIN
FOR c IN (SELECT name Diskgroup
FROM V#ASM_DISKGROUP)
LOOP
FOR l
IN (SELECT 'rm ' || files files
FROM
(SELECT '+' || c.Diskgroup || files files, TYPE
FROM ( SELECT UPPER (SYS_CONNECT_BY_PATH (aa.name, '/'))
files
, aa.reference_index
, b.TYPE
FROM (SELECT file_number
, alias_directory
, name
, reference_index
, parent_index
FROM v#asm_alias
where ALIAS_INDEX NOT IN (
select ALIAS_INDEX from v#asm_alias
where file_number IN (
select file_number from v#asm_alias
where SYSTEM_CREATED = 'N'
and group_number =
(SELECT group_number
FROM v#asm_diskgroup
WHERE name = c.Diskgroup
)
)
and SYSTEM_CREATED = 'Y'
and group_number =
(SELECT group_number
FROM v#asm_diskgroup
WHERE name = c.Diskgroup
)
)
and group_number =
(SELECT group_number
FROM v#asm_diskgroup
WHERE name = c.Diskgroup
)
) aa
, (SELECT parent_index
FROM (SELECT distinct parent_index
FROM v#asm_alias
WHERE group_number =
(SELECT group_number
FROM v#asm_diskgroup
WHERE name = c.Diskgroup)
AND alias_index<50
)
) a
, (SELECT file_number, TYPE
FROM (SELECT file_number, TYPE
FROM v#asm_file
WHERE group_number =
(SELECT group_number
FROM v#asm_diskgroup
WHERE name = c.Diskgroup)
)
) b
WHERE aa.file_number = b.file_number(+)
AND aa.alias_directory = 'N'
AND aa.file_number != 4294967295
AND b.TYPE IN
('DATAFILE'
, 'ONLINELOG'
, 'CONTROLFILE'
, 'TEMPFILE')
START WITH aa.PARENT_INDEX = a.parent_index
CONNECT BY PRIOR aa.reference_index = aa.parent_index
)
WHERE SUBSTR
(
files
, INSTR (files, '/', 1, 1)
, INSTR (files, '/', 1, 2)
- INSTR (files, '/', 1, 1)
+ 1
) =
(SELECT dbname
FROM (SELECT '/'
|| UPPER (db_unique_name)
|| '/'
dbname
FROM v#database)
)
MINUS
(
SELECT UPPER (name) files, 'DATAFILE' TYPE FROM v#datafile
UNION ALL
SELECT UPPER (name) files, 'TEMPFILE' TYPE FROM v#tempfile
UNION ALL
SELECT UPPER (name) files, 'CONTROLFILE' TYPE FROM v#controlfile WHERE name LIKE '+' || c.Diskgroup || '%'
UNION ALL
SELECT UPPER (name), 'CONTROLFILE' TYPE FROM v#datafile_copy WHERE deleted = 'NO'
UNION ALL
SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE FROM v#logfile WHERE MEMBER LIKE '+' || c.Diskgroup || '%'
)
)
)
LOOP
DBMS_OUTPUT.put_line (l.files);
END LOOP;
END LOOP;
END;
/
9.13. A simple script dfdg
Used to check ASM diskgroup usage or list directories and files.
Put dfdg into the ORACLE_HOME/bin directory and make the script executable “chmod +x dfdg” .(replace all “^” to “$”)
#!/bin/bash
# ------------------------------------------------------------------------------
# FUNCTION
# Displays ASM diskgroup information, space usage. Displays usage by DISKS.
# Displays ongoing operations and list of files on diskgroup.
# NOTES
# Developed for 11g Oracle Version. The entry must be in the /etc/oratab
# for ASM instance
# CREATED
# Aychin Gasimov 03/2011 aychin.gasimov@gmail.com
#
# MODIFIED
# Xavier Picamal 08/2012
# Added -r key
# Xavier Picamal 09/2012
# NEW flag -p for Diskgroup Partners query.
# NEW flag -fg for FailGroups, Diskgroups and Headers
# Aychin Gasimov 08/2014
# Removed reading ASM instance name and GI home info from oratab.
# Now script automatically identifies all required variables.
# Prints information about ASM and GI on execution
# ------------------------------------------------------------------------------
# set_crshome Author AG
# Function for setting global variable CRS_HOME in the script, not in the environment.
# It also set CRS_INSTALL_TYPE variable to RESTART or CLUSTER depending on installation type.
declare -r set_crshome_NO_OLRLOC=102
declare -r set_crshome_NO_HOMEINV=103
function set_crshome {
local -i retCode=0
# Get CRS_HOME from olr.loc
local olrFile
local PLATFORM=(/bin/uname)
case ^PLATFORM in
Linux) olrFile="/etc/oracle/olr.loc" ;;
HP-UX) olrFile="/var/opt/oracle/olr.loc" ;;
SunOS) olrFile="/var/opt/oracle/olr.loc" ;;
AIX) olrFile="/etc/oracle/olr.loc" ;;
esac
if [[ -f ^olrFile ]]; then
CRS_HOME="^(grep -i crs_home ^olrFile)" && CRS_HOME=^{CRS_HOME#*=}
[[ -z ^CRS_HOME ]] && return ^set_crshome_NO_OLRLOC
else
return ^set_crshome_NO_OLRLOC
fi
# Now identify is it Cluster Installation or Oracle Restart
local -i is_cluster
if [[ -f ^CRS_HOME/inventory/ContentsXML/oraclehomeproperties.xml ]]; then
is_cluster=^(grep -i "" ^CRS_HOME/inventory/ContentsXML/oraclehomeproperties.xml | wc -l)
[[ ^is_cluster -eq 0 ]] && CRS_INSTALL_TYPE="RESTART" || CRS_INSTALL_TYPE="CLUSTER"
else
return ^set_crshome_NO_HOMEINV
fi
return 0
}
# Setting environment for ASM instance
set_crshome
set_crshome_RES=^?
mess_no_olr="No olr.loc file on the system or corrupt entry! Grid Infrastructure not installed or have configuration problems!"
mess_no_homeinv="No oraclehomeproperties.xml in ^CRS_HOME/inventory/ContentsXML folder, can not identify Cluster software installation type!"
[[ ^set_crshome_RES -eq ^set_crshome_NO_OLRLOC ]] && echo -e ^mess_no_olr && exit ^set_crshome_NO_OLRLOC
[[ ^set_crshome_RES -eq ^set_crshome_NO_HOMEINV ]] && echo -e ^mess_no_homeinv && exit ^set_crshome_NO_HOMEINV
t_sid=( ps -eo args | grep -v grep | grep asm_pmon )
if [[ -z ^t_sid ]]; then
echo "ASM instance is not running, can not get pmon process of ASM instance!"
exit 1
fi
t_sid={t_sid##*_}
echo -e "\n ASM Instance name: "t_sid
echo " GI home: "^CRS_HOME
echo "GI installation type: "^CRS_INSTALL_TYPE
export ORACLE_HOME=^CRS_HOME
export ORACLE_BASE="^(^CRS_HOME/bin/orabase)"
export ORACLE_SID=t_sid
# End setting environment
SQLPLUS=^ORACLE_HOME/bin/sqlplus
dispinfo () {
echo "Use -d key to display usage by disks"
echo "Use -o key to display asm operations in progress (disk rebalancing)"
echo "Use -r key to display min, max and avergage free megabytes by diskgroups"
echo "Use -f to list files and directories of the disk group"
echo "Use -fg to list failgroups, diskgroups and headers"
echo "Use -p to list partner disks"
}
case "^1" in
-d)
^SQLPLUS -S '/ as sysasm' << EOF
set linesize 200
set pagesize 50000
col name format a10
col path format a50
col free_pct format a8
select group_number,name,path,state,os_mb,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v\^asm_disk where header_status='MEMBER';
EOF
dispinfo;
;;
-o)
^SQLPLUS -S '/ as sysasm' << EOF
set linesize 200
select * from v\^asm_operation;
EOF
dispinfo;
;;
-f)
if [ -e ^2 ]; then
echo "Please specify diskgroup name after -f key"
else
^SQLPLUS -S '/ as sysasm' << EOF
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
set linesize 200
set pagesize 50000
variable pindx number;
exec select group_number into :pindx from v\^asm_diskgroup where upper(name)=upper('2');
col reference_index noprint
break on reference_index skip 1 on report
compute sum label "Total size of all files in MBytes on diskgroup ^2" of mb on report
col type format a15
col files format a80
select decode(aa.alias_directory,'Y',sys_connect_by_path(aa.name,'/'),'N',lpadaa.aa.name) files, aa.REFERENCE_INDEX,
b.type, b.blocks, round(b.bytes/1024/1024,0) mb, b.creation_date, b.modification_date
from (select * from v\^asm_alias order by name) aa,
(select parent_index from v\^asm_alias where group_number = :pindx and alias_index=0) a,
(select * from v\^asm_file where group_number = :pindx) b
where aa.file_number=b.file_number(+)
start with aa.PARENT_INDEX=a.parent_index
connect by prior aa.REFERENCE_INDEX=aa.PARENT_INDEX;
EOF
dispinfo;
fi;
;;
-r)
^SQLPLUS -S '/ as sysasm' << EOF
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
set linesize 200
set pagesize 5000
col name format a10
select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min,
max(d.free_mb) Max, round(avg(d.free_mb),2) as Avg
from gv\^asm_disk d, gv\^asm_diskgroup dg
where d.group_number = dg.group_number
group by dg.name, dg.allocation_unit_size/1024/1024;
EOF
dispinfo;
;;
# XPA 2012-11-21 -fg flag BEGINS here
-fg)
^SQLPLUS -S '/ as sysasm' << EOF
set linesize 200
set pagesize 300
col mount_status a15
col header_status format a15
col state format a15
col redundancy format a15
col failgroup format a15
col path format a50
select mount_status,header_status,state,redundancy,failgroup,path from v\^asm_disk order by failgroup;
EOF
dispinfo;
;;
# XPA 2012-11-21 -fg flag ENDS here
# XPA 2012-09 -p flag begins here
-p)
^SQLPLUS -S '/ as sysasm' << EOF
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
set linesize 200
set pagesize 50000
col "Partner Disks" format a80
select d||' => '||listagg(p, ',') within group (order by p) "Partner Disks"
from (
select ad1.failgroup||'('||to_char(ad1.disk_number, 'fm00')||')' d,
ad2.failgroup||'('||listagg(to_char(p.number_kfdpartner, 'fm00'), ',') within group (order by ad1.disk_number)||')' p
from gv\^asm_disk ad1, x\^kfdpartner p, v\^asm_disk ad2
where ad1.disk_number = p.disk
and p.number_kfdpartner=ad2.disk_number
and ad1.group_number = p.grp
and ad2.group_number = p.grp
group by ad1.failgroup, ad1.disk_number, ad2.failgroup
)
group by d
order by d;
EOF
dispinfo;
;;
-h)
dispinfo;
;;
*)
^SQLPLUS -S '/ as sysasm' << EOF
set linesize 200
set pagesize 50000
col free_pct format a8
col name format a10
select group_number,name,sector_size,block_size,allocation_unit_size,state,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v\^asm_diskgroup;
EOF
dispinfo;
esac
exit 0