Check Oracle 9i single instance database

This applies for all Compass, Harp nodes and rls1 database.

Check database status

This tutorial shows how to check the status of a database. This can be useful in case of any problem with the database.

• connect as oracle to the database node
• check if database is up

• this is as it should like is db is up:

orapdm 1457 1 0 Oct 31 ? 0:35 ora_smon_<DB>
orapdm 13383 13348 0 10:08:48 pts/1 0:00 grep smon

• Check if the ORACLE_SID is correct.

bash-2.05$ env | grep SID
ORACLE_SID=rls1

• Then you connect locally to the database.

bash-2.05$ sqlplus "/ as sysdba"

SQL> select host_name, instance_name from gv$instance;

HOST_NAME       INSTANCE_NAME
--------------- ----------------
lxshare071d     rls1

• Check the listener

bash-2.05$ lsnrctl service

LSNRCTL for Linux: Version 9.2.0.7.0 - Production on 06-DEC-2005 18:12:01

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lxfs6061.cern.ch)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "compdb9" has 1 instance(s).
  Instance "compdb9", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:352 refused:0
         LOCAL SERVER
Service "compdb9.cern.ch" has 1 instance(s).
  Instance "compdb9", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:760497 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Start database if it is completely down

• To startup the database there are two things to do:
  1. Startup the listener
  2. Startup the instance

• Starting the listener

bash-2.05$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 9.2.0.3.0 - Production on 10-DEC-2003 19:34:14

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Starting /ORA/dbs01/oracle/product/rdbms9.2.0.3/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.3.0 - Production
   System parameter file is /ORA/dbs01/oracle/product/rdbms9.2.0.3/network/admin/listener.ora
   Log messages written to /ORA/dbs01/oracle/product/rdbms9.2.0.3/network/log/listener.log
   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxshare069d.cern.ch)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lxshare069d)(PORT=1521)))
   STATUS of the LISTENER
   ------------------------
   Alias LISTENER
   Version TNSLSNR for Linux: Version 9.2.0.3.0 - Production
   Start Date 10-DEC-2003 19:34:14
   Uptime 0 days 0 hr. 0 min. 0 sec
   Trace Level off
   Security OFF
   SNMP OFF
   Listener Parameter File /ORA/dbs01/oracle/product/rdbms9.2.0.3/network/admin/listener.ora
   Listener Log File /ORA/dbs01/oracle/product/rdbms9.2.0.3/network/log/listener.log
   Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxshare069d.cern.ch)(PORT=1521)))
   Services Summary...
   Service "PLSExtProc" has 1 instance(s).
   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this   service...
   Service "dgtst01" has 1 instance(s).
   Instance "dgtst01", status UNKNOWN, has 1 handler(s) for this service...
   Service "rept01" has 1 instance(s).
   Instance "rept01", status UNKNOWN, has 1 handler(s) for this service...
   Service "rept02" has 1 instance(s).
   Instance "rept02", status UNKNOWN, has 1 handler(s) for this service...
   Service "rlshs1" has 1 instance(s).
   Instance "rlshs1", status UNKNOWN, has 1 handler(s) for this service...
   The command completed successfully

• Starting the database

bash-2.05$ env | grep SID

ORACLE_SID=rls1

bash-2.05$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Fri Nov 7 10:30:14 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
   Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
   With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining   options
   JServer Release 9.2.0.3.0 - Production

SQL> startup

ORACLE instance started.

Total System Global Area 361304880 bytes
   Fixed Size 451376 bytes
   Variable Size 218103808 bytes
   Database Buffers 134217728 bytes
   Redo Buffers 8531968 bytes
   Database mounted.
   Database opened.
  SQL>

High Load problem on Compass servers

We have established some time ago that a missing or outdated statistics on a large table will cause server overload. Therefore if there is a HIGHLOAD error on a COMPASS server, the following steps should be done:

Via SQL Plus:

1. sqlplus "/ as sydba"
2. @selprocess (and check which user name is making most of the connections)
3. @what_sql (give the user name checked at point 2 and check which tables are being queried)
4. SELECT TABLE_NAME, OWNER, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER LIKE 'COMP%' ORDER BY OWNER, TABLE_NAME;
5. check if the tables that are queried in point 3 were analyzed recently (less than 1 week ago), if not:
6. ANALYZE TABLE schema.table_name ESTIMATE STATISTICS SAMPLE 1 PERCENT;

• If you prefer to analyze all tables (could take very long in a high loaded node) you can do:

To analyze DST tables for problematic period:

./analyze_compass_tables.sh --period 03P1G --sid compdb5 --dst

To analyze RAW data schema:

./analyze_compass_tables.sh --period 03P1G --sid compdb5 --raw

Via Entreprise Manager:

1. Connect with the enterprise console manager to the database and check what sessions are active
2. Find several active connections to a given user, i.e. COMPDST_03P1C
3. Go to the schema section of this user and check if there is statistics for the DST table
4. If it is not existing, choose the "analyze" option on the DST table, follow the wizard, enter 1 in the percentage field. You can add all other tables and indexes of the given user to the selection field in the wizard. They are very small anyhow. Then start the analysis.