Check general database status

I recommend you first step is to check if database processes are running, to do that on shell(Unix): 
$ ps -ef | grep pmon
  orauser1 17289     1  0  May 11  ?        292:33 ora_pmon_DATABASE1
  orauser2 15095     1  0  Sep 10  ?         2:00  ora_pmon_DATABASE2
  oracle 22362       1  0  May  8  ?        118:48 ora_pmon_DATABASE3
this command will let us know which databases are running on the machine and his startup times, also it gives you which oracle users are starting each database. For example, oracle user started DATABASE3 database on May 8. To check if listeners are running: 
$ ps -ef | grep tns
  oracle 25141     1  0  May  8  ?        82:59 /oracle/HPQC/oracle/DATABASE3/11204/bin/tnslsnr LISTENER -inherit
  orauser1 17265     1  0  May 11  ?        59:17 /oracle/JP0/112_64/bin/tnslsnr LISTENER_DATABASE1 -inherit
  orauser2 16189     1  0  Oct 10  ?        100:26 /oracle/SM2/112_64/bin/tnslsnr LISTENER_DATABASE2 -inherit
And to check deeply if a particular listener is running OK, you should run the below command with the user who starts the listener (oracle user to check listener called LISTENER). 
$ id
uid=110(oracle) gid=110(oinstall) groups=108(dba)

$ lsnrctl status LISTENER

LSNRCTL for HPUX: Version 11.2.0.4.0 - Production on 12-SEP-2017 13:44:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for HPUX: Version 11.2.0.4.0 - Production
Start Date                08-MAY-2017 22:24:50
Uptime                    126 days 15 hr. 19 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/HPQC/oracle/DATABASE3/11204/network/admin/listener.ora
Listener Log File         /oracle/HPQC/oracle/diag/tnslsnr/host100/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host100)(PORT=1522)))
Services Summary...
Service "DATABASE3" has 1 instance(s).
  Instance "DATABASE3", status READY, has 1 handler(s) for this service...
Service "DATABASE3XDB" has 1 instance(s).
  Instance "DATABASE3", status READY, has 1 handler(s) for this service...
The command completed successfully
With this query you can check if database is open and when was started. it should be executed from command shell. 
echo "##########################################################################"
echo "make a connection to database and check if there are any session connected"

echo "##########################################################################"
sqlplus " / as sysdba "<<_EOF
set linesize 190
col HOST_NAME for a20
col MACHINE for a20
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
select INST_ID, INSTANCE_NAME, HOST_NAME, STARTUP_TIME, INSTANCE_ROLE, DATABASE_STATUS from gv\$instance;
select INST_ID, MACHINE, PROGRAM, USERNAME, LOGON_TIME from gv\$session;
_EOF
The second query executed list all sessions connected to the database, if you can see any sessions connected from other machines, you can say that network connection to database is working properly from these machines. Selecting gv$resource_limit you can see if database reached some configured limit. (see post Check sessions and processes limit in Oracle) To check if there were any database error you should check alert.log, to find this file location 

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oracle/HPQC/oracle/diag/rdbms
                                                 /datanbase3/DATABASE3/trace
on shell: 

cd /oracle/HPQC/oracle/diag/rdbms/datanbase3/DATABASE3/trace
tail -1000 alert_DATABASE3.log | grep ORA- | wc -l
This command counts ORA- errors present on last 1000 lines on alert.log, to list last 1000 errors simply: 
tail -1000 alert_DATABASE3.log

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more