Hi,

I will share all Oracle DBA Scripts ( Oracle RAC, Dataguard, Performance Tuning, Monitoring and etc.. ) in this tutorial series.

Oracle DBA Scripts

Oracle DBA ( Database Administrator ) needs useful scripts to monitor, analyze and check Oracle database for routine database operations and monitoring.

Oracle RAC Scripts

Before Oracle DBA Scripts, If you don’t know Oracle database and want to learn, click following link to start learning Oracle database with this tutorial.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

Oracle RAC Administration and Monitoring Scripts

To check clusterware status, execute following command.

[root@MehmetSalih1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@MehmetSalih ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[root@MehmetSalih ~]$

To check cluster status, execute following command.

[root@MehmetSalih ~]$ crsctl check cluster  
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[root@MehmetSalih ~]$

To check all cluster resource status, execute following command.

[root@Devecidbadm01 ~]# crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
               ONLINE  ONLINE       Devecidbadm01              STABLE
               ONLINE  ONLINE       Devecidbadm02              STABLE
ora.DBFS_DG.dg
               ONLINE  ONLINE       Devecidbadm01              STABLE
               ONLINE  ONLINE       Devecidbadm02              STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       Devecidbadm01              STABLE
               ONLINE  ONLINE       Devecidbadm02              STABLE
ora.RECOC1.dg
               ONLINE  ONLINE       Devecidbadm01              STABLE
               ONLINE  ONLINE       Devecidbadm02              STABLE
ora.asm
               ONLINE  ONLINE       Devecidbadm01              Started,STABLE
               ONLINE  ONLINE       Devecidbadm02              Started,STABLE
ora.net1.network
               ONLINE  ONLINE       Devecidbadm01              STABLE
               ONLINE  ONLINE       Devecidbadm02              STABLE
ora.ons
               ONLINE  ONLINE       Devecidbadm01              STABLE
               ONLINE  ONLINE       Devecidbadm02              STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       Devecidbadm02              STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       Devecidbadm01              169.254.94.159,STABL
                                                             E
ora.cvu
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.dbm01.db
      1        ONLINE  OFFLINE                               STABLE
      2        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.ebys.db
      1        ONLINE  ONLINE       Devecidbadm01              Open,STABLE
      2        ONLINE  ONLINE       Devecidbadm02              Open,STABLE
ora.ebytest.db
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.mgmtdb
      1        ONLINE  ONLINE       Devecidbadm01              Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.repdb.db
      1        ONLINE  ONLINE       Devecidbadm01              Open,STABLE
      2        ONLINE  ONLINE       Devecidbadm02              Open,STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       Devecidbadm02              STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.test.db
      1        OFFLINE OFFLINE                               STABLE
      2        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.test.db
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.Deveci.db
      1        ONLINE  ONLINE       Devecidbadm01              Open,STABLE
      2        ONLINE  ONLINE       Devecidbadm02              Open,STABLE
ora.Devecidbadm01.vip
      1        ONLINE  ONLINE       Devecidbadm01              STABLE
ora.Devecidbadm02.vip
      1        ONLINE  ONLINE       Devecidbadm02              STABLE
--------------------------------------------------------------------------------
[root@Devecidbadm01 ~]#

RAC Cluster Command ( Scripts )

To stop Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@MehmetSalih1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@MehmetSalih ~]$ crsctl stop crs

To start Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@MehmetSalih1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@MehmetSalih ~]$ crsctl start crs

To disable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@MehmetSalih ~]$ crsctl disable crs

To enable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@MehmetSalih ~]$ crsctl enable crs

To Query Voting disk location, execute following command. Set ASM profile before executing crsctl command.

[oracle@MehmetSalih ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE bd81ab8e27a64fbebf7b6b326c5f614d (/dev/mapper/HDD_E0_S03_1418288932p1) [DATA]
2. ONLINE 450684e276874ff9bff52d481befa809 (/dev/mapper/HDD_E0_S15_1420185788p1) [DATA]
3. ONLINE fd1efd6de5204f6bbfe9793a480c517f (/dev/mapper/HDD_E0_S06_1423843312p1) [DATA]
Located 3 voting disk(s).

To find OCR files location, execute following command.

[root@MehmetSalih ~]# cat /etc/oracle/ocr.loc
#Device/file +DATA getting replaced by device +DATA/Deveci-c/OCRFILE/registry.255.923150247
ocrconfig_loc=+DATA/Deveci-c/OCRFILE/registry.255.923150247
local_only=false

[root@MehmetSalih ~]#
[root@MehmetSalih ~]#

To check the status of the Oracle Cluster registry , execute following command.

[root@MehmetSalih ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 2412
Available space (kbytes) : 407156
ID : 2022573354
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@MehmetSalih ~]#

To add voting disk, execute following command. Set ASM profile before executing crsctl command.

crsctl add css votedisk new_vote_disk_path

To Delete voting disk, execute following command. Set ASM profile before executing crsctl command.

crsctl delete css votedisk new_vote_disk_path

To Add voting disk to ASM Disk, execute following command.

crsctl add votedisk asm_disk_group

To Replace or Migrate voting disk, execute following command.

crsctl replace votedisk asm_diskgroup / vote_disk_path

To find OCR backup location,  execute following command. Set ASM profile before executing crsctl command.

ocrconfig –showbackup auto

To change OCR Backup location, execute following command.

ocrconfig –backuploc Shared_PATH

To Add OCR files location, execute following command.

ocrconfig –add Location_PATH

To Replace or Change OCR files location, execute following command.

ocrconfig –replace New_PATH

To Repair OCR files location, execute following command. Cluster or Oracle RAC Services should be power off.

ocrconfig –repair –add +DATAC1

There are lots of options used with SRVCTL command, these options and their descriptions are as follows.

-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Status and Config Database

You can check all instance status of database as follows.

srvctl status database -d DB_NAME




[oracle@MSDBdbadm01 ~]$ srvctl status database -d MSDB
Instance MSDB1 is running on node MSDBdbadm01
Instance MSDB2 is running on node MSDBdbadm02
Instance MSDB3 is running on node MSDBdbadm03
Instance MSDB4 is running on node MSDBdbadm04
[oracle@MSDBdbadm01 ~]$

You can display the configurations (instances name, nodes and oracle home) of any RAC Database as follows.

srvctl config database -d DB_NAME



srvctl config database -d MSDB

Shutdown RAC Database

You can shutdown all instances of any database as follows.

srvctl stop database -d db_name [-o stop_options]


srvctl stop database -d DB_NAME

srvctl stop database -d DB_NAME -o normal
srvctl stop database -d DB_NAME -o immediate
srvctl stop database -d DB_NAME -o transactional
srvctl stop database -d DB_NAME -o abort




[oracle@MSDBdbadm01 ~]$ srvctl stop database -d MSDB

Startup RAC Database

You can start all instances of any database as follows.

srvctl start database -d db_name [-o start_options]

srvctl start database -d DB_NAME

srvctl start database -d DB_NAME -o nomount
srvctl start database -d DB_NAME -o mount
srvctl start database -d DB_NAME -o open

[oracle@MSDBdbadm01 ~]$ srvctl start database -d MSDB

Shutdown An Instance

You can shutdown only specific Instance of RAC database as follows.

srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]

srvctl stop instance -d DB_NAME -i INSTANCE_NAME




[oracle@MSDBdbadm01 ~]$ srvctl stop instance -d MSDB -i MSDB1

Startup and Status An Instance

You can startup only specific Instance(s) of RAC database as follows.

srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]




srvctl start instance -d DB_NAME -i INSTANCE_NAME




[oracle@MSDBdbadm01 ~]$ srvctl start instance -d MSDB -i MSDB1

You can display any instance of RAC database as follows.

srvctl status instance -d DB_NAME -i INSTANCE_NAME




[oracle@MSDBdbadm01 ~]$ srvctl status instance -d MSDB -i MSDB1

Add and Remove Database from CRS

You can add new DB to CRS as follows.

srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]




srvctl add database -d MSDB -o /u01/app/product/12.1.0.2/dbhome_1 -p +DATAC1/MSDB/parameterfile/spfileMSDB.ora

You can remove existing DB from CRS as follows.

srvctl remove database -d db_unique_name [-f] [-y] [-v]




srvctl remove database -d DB_NAME -f -y

Stop, Start and Check ASM via SRVCTL

You can shutdown an ASM instance as follows.

srvctl stop asm -n NODE_NAME -o immediate

srvctl stop asm -n msdbdbadm01 -o immediate

You can startup an ASM instance as follows.

srvctl start asm -n NODE_NAME

srvctl start asm -n msdbdbadm01

You can display configuration and status an ASM instance as follows.

srvctl status asm -n NODE_NAME
srvctl config asm -n NODE_NAME

[oracle@msdbdbadm01 ~]$ srvctl status asm -n msdbdbadm01
ASM is running on msdbdbadm01
[oracle@msdbdbadm01 ~]$
[oracle@msdbdbadm01 ~]$
[oracle@msdbdbadm01 ~]$ srvctl config asm -n msdbdbadm01
PRKO-2207 : Warning:-node option has been deprecated and will be ignored.
ASM home: <CRS home>
Password file: +DBFS_DG/orapwASM
ASM listener: LISTENER
[oracle@msdbdbadm01 ~]$

Stop, Start and Check Service via SRVCTL

You can start, stop and check any service on Database as follows.

srvctl status service -d db_name
srvctl config service -d db_name
srvctl start service -d dbname -s service_name
srvctl stop service -d dbname -s service_name

srvctl add service -d {DB_NAME} -s {SERVICE_NAME} -r {"preferred_list"} -a {"available_list"} [-P {BASIC | NONE | PRECONNECT}]

srvctl relocate service -d db_name -s service_name -i instance_name -t new_instance_name [-f]

You can remove any service as follows.

srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}



srvctl remove service -d MSDB -s MSDB_SALES

Enable/disable Autorestart of Instance and Database

You can enable and disable auto restart of the database as follows.

srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME

You can enable and disable auto restart of the instance

srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME

Start, Stop and Check status,configurations of NodeApps

srvctl status nodeapps -n nodename

srvctl config nodeapps -n nodename

srvctl start nodeapps -n nodename

srvctl stop nodeapps -n nodename

You can access the second Oracle DBA scripts related to Dataguard with following link.

Dataguard ( Standby ) Monitoring Lag Command and Scripts | Oracle DBA Scripts All in One -2

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database