Database OAM

How to check that deployed database setup is the RAC(Cluster Database) or NON-RAC(Single Instance) Database?

  1. Attributes of RAC Database

su – oracle

OS# sqlplus / as sysdba

SQL> show parameter cluster_database 		
                          TRUE
SQL> show parameter cluster_database_instances      	
                         2
  1. Attributes of NON-RAC Database
SQL> show parameter cluster_database 		
                              NULL
SQL> show parameter cluster_database_instances       
                              NULL

Note:

If you don’t have DB sys password the you can check from the tnsnames.ora or oratab

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
$ cat /etc/oratab or cat /var/opt/oracle/oratab

How to generate the HTML Report of any SQL Query.

Generate HTML Report from Database

SET SERVEROUTPUT ON
SQL>SPOOL ON;
SQL>SPOOL ‘REPORTNAME.HTML’; --Report Name
SQL>SET PAGESIZE 10000;
SQL>SET MARKUP HTML ON ENTMAP OFF;
SQL>SELECT * FROM DUAL; --Copy SQL Query
SQL>SPOOL OFF;

Find the Specified Report from Home Location

OS PROMPT:
ls -ltr
ftp mget

How to display the SQL Query result in single line?

clear columns
set colsep "|"
set linesize 32767
set pagesize 0
COLUMN MYUSER_IDENTITY 	        FORMAT A20 
COLUMN MYCUI 			FORMAT A20   
COLUMN MYRADIUSPOLICY 		FORMAT A30 
COLUMN MYCUSTOMERREPLYITEM	FORMAT A50 

SELECT MYUSER_IDENTITY,MYCUI,MYRADIUSPOLICY,MYCUSTOMERREPLYITEM FROM MYRADIUSCUSTOMER;

Reference
http://www.adp-gmbh.ch/ora/sqlplus/column.html
http://barrymcgillin.blogspot.in/2011/09/sqlplus-formatting-commands.html

Find the Database Sessions by schema name wise.

SQL> SELECT USERNAME,STATUS,COUNT(1) FROM V$SESSION GROUP BY USERNAME,STATUS;

---RAC Database

SQL> SELECT USERNAME,STATUS,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS;

Find the OSUSER and MACHINE name of sessions

SELECT USERNAME,STATUS,OSUSER,MACHINE,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS,OSUSER,MACHINE;

Note: desc V$SESSION

--RAC Database

SELECT USERNAME,STATUS,OSUSER,MACHINE,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS,OSUSER,MACHINE;

Note: desc GV$SESSION

How to check the Database sessions,process and open cursor parameters?

OS# sqlplus / as sysdba
SQL> show parameter sessions
SQL> show parameter processes
SQL> show parameter open_cursors

--check the database parameters

SQL> show parameter

How to check the Database Alert logs?

Syntax for Oracle10g

tail -100 $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Example
tail -100 /u01/app/oracle/admin/eliteaaa/bdump/alert_eliteaaa.log

Syntax for Oracle11g

tail -100f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log
Example
tail -100f /u01/app/oracle/diag/rdbms/aaadb/aaadb1/trace/alert_airchn1.log

Verify the size of datafiles

sqlplus / as sysdba

SQL> SELECT (BYTES/1024)/1024,NAME FROM V$DATAFILE;                                                

SQL> SELECT FILE_NAME,TABLESPACE_NAME,(BYTES/1024)/1024,STATUS,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

How to find the DDL of any Specific Database Object (e.g TABLE,VIEW,SEQUENCE etc …?)

set long 10000
set pagesize 5000
select dbms_metadata.get_ddl('TABLE','EMP') from dual;

How to find the DDL of INDEX of Specific TABLE?

set long 10000
set pagesize 5000
select dbms_metadata.get_dependent_ddl('INDEX','EMP') from dual;

Check the Database Server Status

Check the Database Mode 

OS#sqlplus / as sysdba

SQL>select open_mode from v$database;
SQL>exit;

    Check Oracle Listener Status 

OS#lsnrctl status

#Check Oracle BG Process
OS# ps -ef | grep ora_
OR
OS# ps -ef | grep -i xe_

How to find the Database Name from any Server?

Linux/Solaris  .bash_profile OR .profile

echo $ORACLE_SID

Solaris  cat /var/opt/oracle/oratab 
Linux    cat /etc/oratab

Tablespace and User Administration

---drop the user

          DROP USER MYUSER CASCADE;

--drop the tablespace

          DROP TABLESPACE MYUSER INCLUDING CONTENTS AND DATAFILES;

---Verify the path of the datafile

          SELECT NAME FROM V$DATAFILE;

          echo $ORACLE_BASE/oradata/$ORACLE_SID/

--Create the tablespace for single instance database

--This is not for RAC Database

         CREATE TABLESPACE MYUSER DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/MYUSER.dbf' size 100M AUTOEXTEND ON;

---Create the User

         CREATE USER MYUSER IDENTIFIED BY MYUSER DEFAULT TABLESPACE MYUSER TEMPORARY TABLESPACE temp 
         QUOTA UNLIMITED ON MYUSER;

--Give the privileges to user.

         GRANT CONNECT,RESOURCE, CREATE ANY VIEW TO MYUSER;

How to find the location of Tablespace’s Datafile

               SELECT NAME FROM V$DATAFILE;

How to check that Tablespace is AutoExtend or not?

  
             SELECT TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

How to Add New Datafile to Existing Tablespace?

     
         ALTER TABLESPACE MYUSER
              ADD DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/MYUSER02.dbf' SIZE 100M AUTOEXTEND ON;

How to find the Specific Oracle Data Dictionary ?

SELECT * FROM DICTIONARY;

SQL> desc DICTIONARY
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

Identifying Open Uncommitted Transactions

Note: RAC Database

select s.INST_ID, s.SID, s.SERIAL#,s.USERNAME,s.MACHINE
from gv$transaction t , gv$session s
where t.INST_ID = s.INST_ID
and t.ses_addr = s.SADDR;

To find database redo load per day.

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
group by trunc(completion_time)
order by 1 desc;

How to monitor the DataPump.

   SQL> select sid, serial#, sofar, totalwork,dp.owner_name, dp.state, dp.job_mode
	from gv$session_longops sl, gv$datapump_job dp
	where sl.opname = dp.job_name and sofar != totalwork;

How to monitor the rman backup.

   SQL>alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

   SQL> select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
	sysdate + TIME_REMAINING/3600/24 end_at
	from v$session_longops
	where totalwork > sofar
	AND opname NOT LIKE '%aggregate%'
	AND opname like 'RMAN%';

How to Kill the app_username sessions:-

SELECT ‘ALTER SYSTEM KILL SESSION ‘ ||chr(39) ||s.sid||chr(44)||s.serial#||chr(39)||’ immediate;’
FROM v$session s JOIN v$process p ON p.addr = s.paddr
WHERE s.type != ‘BACKGROUND’
AND s.username = ‘&app_username’;

SQL> SELECT 'ALTER SYSTEM KILL SESSION  ' ||chr(39) ||s.sid||chr(44)||s.serial#||chr(39)||' immediate;'
  2  FROM   v$session s
  3         JOIN v$process p ON p.addr = s.paddr
  4  WHERE  s.type != 'BACKGROUND'
  5  AND s.username = '&app_username';
Enter value for app_username: TESTING
old   5: AND s.username = '&app_username'
new   5: AND s.username = 'TESTING'

'ALTERSYSTEMKILLSESSION'||CHR(39)||S.SID||CHR(44)||S.SERIAL#||CHR(39)||'IMMEDIAT
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION  '1093,186' immediate;
ALTER SYSTEM KILL SESSION  '1067,260' immediate;

More....

SQL for DBA’s

dataguard
scheduler-jobs
generic-administration
performance

2 thoughts on “Database OAM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s