Everyday Common SQL

DB Size / Tablespace Size / Tablespace Usage -- DB Size, database size free and % used ----------------------------------------- col TABLESPACE_NAME for a30 col NoDbf for a5 select NoDbf, round(tbs.size_tbs, 0) "Size(MB)" , round(nvl(df.size_free, 0), 0) "Free Space(MB)", round((tbs.size_tbs - nvl(df.size_free, 0))/tbs.size_tbs*100, 0) "% Used" from (select sum(bytes)/1024/1024 size_tbs, count(*)||'' NoDbf from dba_data_files ) tbs, (select sum(bytes)/1024/1024 size_free from dba_free_space ) df;

Cluster DB notes

Status of ASM and DB: srvctl status asm srvctl status database -d rac Start / Stop Instance: srvctl start instance -d rac -i rac2 srvctl stop instance -d rac -i rac2 Listener: srvctl status listener srvctl status scan_listener Cluster Auto Start: crsctl stat res -t crsctl status res -f | grep AUTO_START e.g. crsctl status res ora.rac.db -f | grep AUTO_START AUTO_START=restore crsctl status res ora.asm -f | grep AUTO_START AUTO_START=always

Oracle Clusterware Logs

CRS: /u01/app/grid/diag/crs/[DB_NAME]/crs/trace/ alert.log ASM: /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log Force a shutdown of Oracle Clusterware (CRS) : Using root cd /u01/app/19.0.0/grid/bin ./crsctl stop crs -f Check detail of network card e.g. [oracle@srv1 bin]$ nmcli device show enp0s10 GENERAL.DEVICE: enp0s10 GENERAL.TYPE: ethernet GENERAL.HWADDR: 08:00:27:62:8A:33 GENERAL.MTU: 1500 GENERAL.STATE: 100 (connected) GENERAL.CONNECTION: Wired connection 5 GENERAL.CON-PATH: /org/freedesktop/NetworkManager/ActiveConnection/3 WIRED-PROPERTIES.CARRIER: on IP4.ADDRESS[1]: 192.168.1.140/24 IP4.GATEWAY: 192.168.1.1 IP4.ROUTE[1]: dst = 0.0.0.0/0, nh = 192.168.1.1, mt = 102 IP4.ROUTE[2]: dst = 192.168.1.0/24, nh = 0.0.0.0, mt = 102 IP4.DNS[1]: 192.168.1.1 IP6.ADDRESS[1]: fe80::5f91:3967:1437:483f/64 IP6.GATEWAY: -- IP6.ROUTE[1]: dst = fe80::/64, nh = ::, mt = 102 IP6.ROUTE[2]: dst = ff00::/8, nh = ::, mt = 256, table=255 [oracle@srv1 bin]$