Basics commands for DBA part 1 - finding instance

Hi,
In the next few blogs I will discuss on scripting technics that DBA usually needs.
I am concentrating in the commands, that are usually part of larger script.
Later on I will submit some scripts that uses these technics.

Identifying all instances running on the current machine

Option 1: 

# ps -ef |grep smon
oracle    3025     1  0  2016 ?        00:00:48 asm_smon_+ASM
oracle   11459     1  0 17:24 ?        00:00:00 ora_smon_fdb
root     12763 11332  0 17:52 pts/1    00:00:00 grep --color=auto smon
SID is fdb and process id is 11459   

Option 2: 

# pgrep  -lf _pmon_
3025 asm_pmon_+asm
11459 ora_pmon_fdb
SID is fdb and process id is 11459 

 Option 3: 

Cleaner way for sid:
$ ps -ef |grep 'ora_smon_.*$' | grep -v grep | awk -F_ '/ora_smon/{print $NF}'
fdb
SID is fdb

Option 4: 

When we already know ASM home (grid infrastructure) we can use the cluster commands:
$ /oracle/product/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"|awk '/^ora./ {l=$0;} !/^ora./ { if ( l > "" ) l=l " " $0; print l;l="";}'|grep  ${HOSTNAME%%.*}
 ora.fdb.db       1        ONLINE  ONLINE       dbhost                Open,STABLE
SID is fdb

Option 5:

Using /etc/oratab file
$ cat /etc/oratab | grep -v ^'#' | grep -v ^$ | awk -F ":" ' { print $1 }' | grep -v ASM | uniq
fdb
SID is fdb

Oratab location can be in the following locations:
Solaris: /var/opt/oracle/oratab
/var/opt/oracle
/etc/oratab

Identifying ORACLE_HOME of instance running on the current machine

Option 1:

Since we know the process id of the database, we can look at the environment variable was used when started the database
$ grep -z ^ORACLE_HOME  /proc/11459/environ
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1#
ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Option 2:

pwdx - report current working directory of a process
$ pwdx 11459
11459: /oracle/product/12.1.0.2/dbhome_1/dbs
ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Option 3:

$ strings /proc/11459/environ | grep ORACLE_HOME
ORACLE_HOME_LISTNER=/oracle/product/12.1.0.2/grid
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1
ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Option 4:

Using /etc/oratab file
$ cat /etc/oratab | grep ^${DB}: | grep -v ^$ | cut -d":" -f 2 | grep -v ASM | uniq
/oracle/product/12.1.0.2/dbhome_1
 ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1


Please provide your own way if I missed some options
Thanks
Yossi

Comments

Post a Comment

Popular posts from this blog

Silent Installation

Some times there are problems using installation with GUI, the reason can be: 1. Firewall 2. Unix security 3. Client software (missing or not working( and maybe some more reasons If your environment is cooperative helping you solve this - it is simple - go for it. But if you are on your own and can't reach "the right guys" - do it yourself " Silently " Look for the proper response file fits for your needs in /database/response/ Edit it and run the following command: $ ./runInstaller -force -invPtrLoc /oraInst.loc -silent -noconfig -ignoreSysPrereqs -responseFile /database/response/enterprise.rsp You can also run the installer without a response file as mentioned in Metalink Note 782918.1 $ ./runInstaller -silent -force -debug \ FROM_LOCATION="/mount/dvd/database/stage/products.xml" \ ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1" \ ORACLE_HOME_NAME="Ora11gDb1" ORACLE_BASE="/u01/app/oracle" \ TOPLEVEL_COMPONENT='{&q
Read more

Data Guard - Changing IP Addresses

Image
When changing IP of a host we should update/recheck the following places: /etc/hosts or DNS listener.ora tnsnames.ora Database parameters (local_listener, remote_listener) Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts. In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration Show the environment DGMGRL> show configuration Configuration - dr   Protection Mode: MaxAvailability   Members:   pdb7 - Primary database     fdb7 - Far sync instance       sdb7 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS   (status updated 351 seconds ago) Show detailed information of the Primary
Read more

Fixing & Registering ORACLE_HOMES in Central Inventory

Central Inventory location is determined by oraInst.loc located in: HP/Solaris /var/opt/oracle/oraInst.loc Linux /etc/oraInst.loc Windows \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc To check if all oracle homes are registered within the Central Inventory run the following command: ${ORACLE_HOME}/oui/bin/opatch lsinventory -all Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /software/oracle/OEM10gR2/agent10g Central Inventory : /software/oracle/oraInventory from : /var/opt/oracle/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /software/oracle/OEM10gR2/agent10g/oui Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt ----------------------------------... List of Oracle Homes:
Read more