7 Essential Oracle DBA Commands

Though, Oracle DBA Commands is a huge list and vary upon requirements. Here, I am listing 7 essential Oracle DBA commands, which are bread and butter for a remote DBA.

1. List of Database Instances running on DB server

-bash-4.1$ ps -ef | grep smon

    root   1147      1   0   Mar 30 ?                 484:50 /u01/app/11.2.0.3/grid/bin/osysmond.bin
  oracle   1818      1   0   Mar 30 ?                0:25 asm_smon_+ASM1
  oracle   2662      1   0   Mar 30 ?                1:34 ora_smon_remotedba1
  oracle   4487   4449   0 08:43:17 pts/1       0:00 grep smon

This is the command which i use as soon as i log in to Database server. SMON is the must run database process. So check no of smon's listed by above command and you will come to know number of DB instances running. From above example there are two DB instances running remotedba1 and +ASM1 (this is an Automatic Storage Management instance).

If database is not running, Remote DBA can see error like "ORA-01034 : ORACLE not available. ORA-27101 : shared memory realm does not exist" This can be resolve by starting the database.

2. Setting $ORACLE_HOME and $ORACLE_SID environment Variable

To log into the Database, $ORACLE_HOME environment variable must be set, you can set this each time you log in or can fix this permanently in .bash_profile file. A DBA can fix this permanently.

-bash-4.1$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

If you don't know, your environment ORACLE_HOME path, use simple techniques to find out.

-bash-4.1$ export ORACLE_SID=remotedba1

Since, you might have more than one instance running on a server, so you have to set ORACLE_SID
variable to work on a specific instance.

-bash-4.1$ cd $ORACLE_HOME/bin
-bash-4.1$ ./sqlplus sys as sysdba
SQL>

3. Cross Check Instance Name After login

This is the most important Oracle DBA command, When you work on more than one instances on single server. So you frequently switch between Database Instances and suppose you want to shutdown DB instance DB1 but by mistake you forgot to set ORACLE_SID for test_DB1 and ORACLE_SID was set to prod_db1 and you shutdown the database.

I believe you have understood, what i want to say. So, i would strongly suggest to check instance
name before doing any change in to database.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prod_db1

In case of RAC, Suppose there are two instances.

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
prod_db1
prod_db2
 
4. Verify Instance Status

A database instance can be in no mount, mount and open status. Important to notice is user can only
connect to the database if it's status is "open". So if, you have done some changes in Database
and restarted the DB instance make sure it's status is "open"

SQL> select STATUS from v$instance;

STATUS
------------
OPEN

In RAC env. Suppose there are two instances.

SQL> select STATUS from gv$instance;

STATUS
------------
OPEN
OPEN
 
5. Listener is Up and Running

Though, everything is fine and Database is also running, Network between client and server is responding even then Remote DBA is not able to connect to Database Instance. In this case Listener could be the problem. If Listener is not up and running, you can't connect to database instance using tnsname from a client system.

Command to check Listener status

[oracle@test instance]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-MAY-2011 11:46:47

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.***)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                24-MAY-2011 10:38:30
Uptime                    1 days 1 hr. 8 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.***)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test01" has 1 instance(s).
  Instance "test01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully.


if your output is like above than at least there is no problem from listener running point of view , otherwise DBA has to trouble shoot this.

6. Available free Disk Space

-bash-4.1$ df -h

Filesystem             Size   Used  Available Capacity  Mounted on
rpool/ROOT/solaris_11
                        30G    18G         0K   100%    /
/devices                 0K     0K         0K     0%    /devices
/dev                     0K     0K         0K     0%    /dev
ctfs                     0K     0K         0K     0%    /system/dba
swap                    26G   1.3M        26G     1%    /system/swap

Though, this is a three letter command, but can save remote DBA from so many problem and can resolve same number of problems as well.Suppose, Database server mount point having UDUMP / BDUMP / CDUMP is full and you restart the database.

Database will not start and will give "ORA-09817: Write to audit file failed No space left on device" . However, deleting some old trace files can solve this. But, i would suggest to keep an eye on all mount points using some cron job etc.

7. Ping and Tnsping

These are basic commands for Remote DBA to trouble shoot Client server database connection issues. Sometimes, set up is absolutely fine even then client and server connection doesn't happen. To resolve these issues DBA can use these commands.

C:\Users\umesharm>ping 192.168.4.50

Pinging remote_dba.dba-oracle.com [192.168.4.50] with 32 bytes of data:
Reply from 192.168.4.50: bytes=32 time=432ms TTL=245
Reply from 192.168.4.50: bytes=32 time=424ms TTL=245
Reply from 192.168.4.50: bytes=32 time=446ms TTL=245

If you are getting reply from Database Server as shown in above output. This means networking is not the issue now, you have to look at some other directions like tnsname.ora, sqlnet.ora configuration etc and use tnsping for further investigate.

C:\Users\umesharm>ping 192.168.4.50

Pinging remote_dba.dba-oracle.com [192.168.4.50] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.


Ping statistics for 192.168.4.50:
    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),


Above command shows that, client is not able to connect to Database Server. In this case DBA has to take help from Networking team.

Below command show, How to check client server connection using tnsping {service name} 

-bash-4.1$ /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/tnsping remote_db1

TNS Ping Utility for Solaris: Version 11.2.0.3.0 - Production on 26-APR-2013 10:08:08
Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = remote_db1-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = remote_db1)))
OK (270 msec)

If you got OK at last of the output it is fine. Otherwise, you might get error like below

-bash-4.1$ /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/tnsping remote_db2

TNS Ping Utility for Solaris: Version 11.2.0.3.0 - Production on 26-APR-2013 11:18:42
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

In this case, look at tnsname.ora configured on client system.

Please share your views about this article.

No comments:

Post a Comment