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
bash-2.05$ ps -ef | grep smon
- 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
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:
- Startup the listener
- Startup the instance
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
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:
- sqlplus "/ as sydba"
- @selprocess
(and check which user name is making most of the connections)
- @what_sql
(give the user name checked at point 2 and check which tables are being queried)
- SELECT TABLE_NAME, OWNER, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER LIKE 'COMP%' ORDER BY OWNER, TABLE_NAME;
- check if the tables that are queried in point 3 were analyzed recently (less than 1 week ago), if not:
- 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:
- Connect with the enterprise console manager to the database and check what sessions are active
- Find several active connections to a given user, i.e. COMPDST_03P1C
- Go to the schema section of this user and check if there is statistics for the DST table
- 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.