Basic DB and MT Health Checks and Start/Stop DB and MT Services

 SQL Queries for quick  DB and MT checks  to get the basic information .

Use
         For General information on Machine and the User
(To get the complete instance level informations)
        $ date;id;hostname -f;echo $TWO_TASK

Before login to any DB or Application user ,we should know what are the users available with this node.(Server)
To get that details issue a command like
        $ ps -ef|grep tns
Here tns is a binary which is under $ORACLE_HOME.
SQL> select name,status from v$instance;

NAME            STATUS
 ----------      ------------
PLEPET          OPEN


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PLEPET    READ WRITE

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------     ------------
PLEPET   ARCHIVELOG

Similarly for RAC(Real Application Cluster) databases

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
PLEPET    READ WRITE
PLEPET    READ WRITE

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
--------- ------------
PPFIZI    ARCHIVELOG

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS

---------------- ------------
PLEPET1          OPEN
PLEPET2          OPEN


Listener Status

----------------------

SQL> !lsnrctl status $ORACLE_SID

To Find DB/Instance Name in RAC env.

sql> show parameter cluster;

Note:If it is TRUE,then its RAC enabled.
Also you can check in the init<SID>.ora file under $ORACLE_HOME/dbs location to verify the same.

bash-3.2$sqlplus "/as sysdba"

SQL >col host_name for a20
select inst_id,instance_name,host_name,instance_role from gv$instance;
  INST_ID INSTANCE_NAME    HOST_NAME            INSTANCE_ROLE
---------- ---------------- -------------------- ------------------
         1 PLEPET1          server1          PRIMARY_INSTANCE
         2 PLEPET2          server2          PRIMARY_INSTANCE

To check from the operating system

# lsnodes


File System Space Utilization
$ df -h
$ df -hP

Operating System - eBusiness DB and Concurrent Process/es
$ ps -ef |grep pmon|grep -v grep
$ ps -ef |grep FNDLIBR|grep -v grep

e-Business DB Instance
SQL> SELECT instance_name,host_name,TO_CHAR(startup_time,'DD-MON-YYY HH:MM:SS AM') startdate FROM v$instance;

Invalid Objects
SQL> select owner,object_name,object_type from dba_objects where status='INVALID';

Inactive Users
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status like 'INACTIVE';

Active Sessions
SQL> select inst_id,count(*) from gv$session group by inst_id;

Active Users Detail
SQL> select username,command,status,osuser,process,machine,terminal,program,module,action from v$session where status='INACTIVE';

Data Files Status
SQL> select name,status from v$datafile;

Log Files Status
SQL> select * from v$log;

Archiving Errors
SQL> select error from v$archive_dest;

Tablespace Free Space
SQL> select tablespace_name,round (sum(bytes)/1024/1024) Free_Space_MB from dba_free_space group by (TABLESPACE_NAME);

Invalid Indexes
SQL> select index_name,table_name from dba_indexes where status ='INVALID' and owner='APPS';

Startup & Shutdown Procedure in EBSO

----------------------------------------------------

Startup Procedure

-------------------------

1)Login to the Database Server as 'Oracle' User

               cd $TNS_ADMIN

                 ls -lrt

              tnsnames.ora

              listener.ora

2)Either Vi the listener.ora file to get the listener name or do

        ps -ef|grep -i lsnr 

3)check the listener status.If it is not started 

lsnrctl start $ORACLE_SID

lsnrctl start <listener_name>

4)Now start the database.

   sqlplus "/as sysdba"

   startup

5)Now Login to Mid-Tier Services as Applmgr User

 cd $ADMIN_SCRIPTS_HOME      (in R12)

 cd $SCRIPT_TOP                             (in 11i )

./adstrtall.sh start apps/apps123

apps-Username

apps123-appspassword

Shutdown Procedure

---------------------------

This is exactly the reverse process of startup

1)Login to Mid-Tier as  as 'Applmgr' User

 cd $ADMIN_SCRIPTS_HOME      (in R12)

 cd $SCRIPT_TOP                             (in 11i )

./adstpall.sh apps/apps123

2)Wait for some time to cleanly shuts all the services with exit status 0.

Use Command ps -ucx|wc -l to count the number of processes still running.

Find the processes like 

ps -ef|grep -i fndlibr

ps -ef|grep -i forms

ps -ef|grep -i java 

..

You can use 'Kill -9 <pid> to kill the processes if some processes taking more time

Remember don't kill any background processes.

Once neatly shuts down the MT services.

3)Log in to DB Tier as 'Oracle' User 

          sqlplus "/as sysdba"

          shut immediate