ASM Useful Commands ASMCMD

ASM Commands:

ASM Cheatsheet

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

ASM usefull sql commands:

sqlplus / as sysasm
show parameter asm;
SQL> !ls -ls /dev/asm*
show parameter asm_diskstring;

alter system set asm_diskstring='/dev/asm*' scope=memory;
show parameter asm_diskstring;

select name,state from v$asm_diskgroup;
alter diskgroup DATA mount;
select name,state from v$asm_diskgroup;

show parameter spfile;

sqlplus / as sysasm

col diskgroup for a10

col diskname for a12

col path for a50

set linesize 200;

setpagesize 100;

select a.name DiskGroup,b.name DiskName, b.total_mb, b.free_mb,b.path, b.header_status

from v$asm_disk b, v$asm_diskgroup a where a.group_number (+) =b.group_number order by

b.group_number,b.name;

Check the Version:

crsctl query has releaseversion
crsctl query has softwareversion
crsctl query has releasepatch
crsctl query has softwarepatch

crsctl stop res -all

crsctl start res -all

crsctl stop has
crsctl stat res -t
crsctl start has
crsctl status has
crsctl enable has

unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID

srvctl status listener -l LISTENER
srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER
srvctl add listener -l LISTENER -p "TCP:1521" -o $ORACLE_HOME
srvctl add listener -l LISTENER -s -p 1521
srvctl remove listener -l LISTENER
or
lsnrctl stop LISTENER
lsnrctl start LISTENER

Listener login aktivieren:
lsnrctl status

lsnrctl show log_status

lsnrctl set log_status on

lsnrctl show log_status

Listener Log File /oracle19c/diag/tnslsnr/tstdb/listener/alert/log.xml

# you can deactivate as follow

lsnrctl set log_status off

---

srvctl status asm
srvctl stop asm -f
srvctl start asm
srvctl config asm

srvctl config asm -a

srvctl stop database -d DBNAME

srvctl start database -d DBNAME

srvctl stop database -d DBNAME -o immediate

srvctl start database -d DBNAME -o mount

srvctl stop instance -d DBNAME -i INSTANCENAME

srvctl add database -db KARDBPRD -oraclehome /oracle/product/12.2.0.1/db_12c

srvctl add database -d KARDBPRD -o /oracle12.2/product/12.2.0.1/db -p '/oracle12.2/product/12.2.0.1/db/dbs/spfileKARDBPRD.ora'

srvctl modify asm -spfile '/oracle12.2/product/12.2.0.1/grid/dbs/spfile+ASM.ora'

srvctl modify asm -pwfile '/oracle12.2/product/12.2.0.1/grid/orapw+ASM'

srvctl upgrade database -db DBNAME -oraclehome /oracle/product/12.2.0.1/db12c

srvctl upgrade database -d DBNAME -o /apps/oracle/product/12.1.0/db_12102

srvctl config database -db DBNAME

srvctl modify database -db DBNAME -p '$ORACLE_HOME/spfileDBNAME.ora'

Dependencies between database and diskgroups

$ srvctl modify database -d <db_unique_name> -a "<diskgroup_list>"

$ srvctl modify database -d db112 -a "DATA1,DATA2"

From Grid_Home:

srvctl modify database -d KARDBPRD -a "DATA01"

Modify Command

srvctl modify asm -spfile +DATA/asm/asmparameterfile/registry.253.895312769 -diskstring '/dev/asm*'

srvctl modify asm -spfile '/oracle12.2/product/12.2.0.1/grid/dbs/spfile+ASM.ora'

srvctl modify asm -listener -l LISTENER -s -p 1521 -spfile +DATA/asm/asmparameterfile/registry.253.895312769 -diskstring '/dev/asm*'

srvctl modify database -db DBNAME -p '$ORACLE_HOME/spfileDBNAME.ora'

Copy spfile from diskgroup to the Oracle_Home:

asmcmd
spget
spcopy +DATA/ASM/ASMPARAMETERFILE/registry.142.1018520159 $ORACLE_HOME/dbs/spfile+ASM.ora

Check diskgroup including the dismounted DGs

asmcmd lsdg -g --discovery

Check ASM disk status

asmcmd lsdsk -k -g

Check ASM disk status for candidate disk

asmcmd lsdsk -k -g --candidate

Clients accessing ASM instance:

asmcmd lsct

Files opened by ASM instance:

asmcmd lsof

Check total and free space.

sqlplus / as sysasm

set linesize 100

col path format a15

select name, path from v$asm_disk where name is not null;

select name, type, total_mb, free_mb from v$asm_diskgroup;

SELECT name, header_status, path FROM V$ASM_DISK;

Create ASM spfile

Create a temporary initialization parameter file (e.g. /tmp/init+ASM.ora)

+ASM.asm_diskgroups='DATA01','DATA02'

asm_diskstring='/dev/sd*
large_pool_size=12M

remote_login_passwordfile='EXCLUSIVE'

or:

*.asm_diskstring='/dev/mapper/*'
*.asm_power_limit=1
*.diagnostic_dest='/oracle'
*.instance_type='asm'
*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

sqlplus / as sysasm

create spfile='+DATA' from pfile='/tmp/init+ASM.ora';

show parameter spfile

If you want change the diskstring.

alter system set ASM_DISKSTRING= '/dev/sd*';

Check the compatibil parameter.

col COMPATIBILITY form a10
col DATABASE_COMPATIBILITY form a10
col NAME form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

ASM restricted

You can mount diskgroup in restricted mode and change the compatiblity

alter diskgroup DATA01 mount restricted;

alter diskgroup DATA01 set attribute 'compatible.asm'='11.2.0.2.0';

SQL> select name, state from v$asm_diskgroup;

NAME STATE
-------------------- -----------
DATA MOUNTED

DATA01 RESTRICTED

alter diskgroup DATA01 dismount;

alter diskgroup DATA01 mount;

srvctl remove diskgroup -g DATA

========

Change Hostname of a Standalone Oracle ASM DB

deconfigure Oracle Restart and High Availability Services

As root user:

setdb_home +ASM

cd $GRID_HOME/crs/install

./roothas.pl -deconfig -force

or:

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl -deconfig -force -verbose

If you get error do the following:

DIA-48188: user missing read, write, or exec permission on specified directory
Linux-x86_64 Error: 13: Permission denied
cd /oracle/diag/crs
chown -R oracle:dba *
or:
Remvod the old hostname under the directory.

cd /oracle/diag/crs

change the hostname

cat /etc/hosts
cat /etc/sysconfig/network-scripts/

hostnamectl set-hostname [your hostname]

Configure High Availability Services again

setdb_home +ASM
cd $GRID_HOME/crs/install
./roothas.pl

or:

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

Edit the following files as well as.

listener.ora tnsnames.ora

srvctl add listener

srvctl start listener

Change Auto Start parameter of ora.cssd ( Cluster Syncronization Services Daemon ) as 1 like following.

crsctl modify resource "ora.cssd" -init -attr "AUTO_START=1"

crsctl stop has

crsctl start has

sqlplus / as sysasm

SQL> startup

asmcmd lsdg

Creating password file:

11g:

orapwd file='$ORACLE_HOME/dbs/orapwKARDB01' password=oracle entries=10;

12c:

orapwd file='$ORACLE_HOME/dbs/orapwKARDB01' entries=10 force=y password='xxxxxx' force=y format=12

From Grid_HOME

orapwd file='$ORACLE_HOME/dbs/orapw+ASM' ENTRIES=10 FORMAT=12

srvctl modify asm -pwfile '$ORACLE_HOME/dbs/orapw+ASM'

Oracle DBA's

Tags

Monday, March 15, 2021