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
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.
1. asm_smon +ASM1 and 2. remotedba1
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.
No comments:
Post a Comment