Daily check on the database before business volume picks up

Session Count

SQL>     SELECT a.inst_id, COUNT (1) "Session Count"      FROM sys.gv_$session a     GROUP BY a.inst_id SQL> /    INST_ID Session Count ---------- -------------          1            67          2           105

Long Operation – Time Remaining is greater than Zero.

SQL> l     SELECT * FROM sys.gv_$session_longops     WHERE  TIME_REMAINING > 0

DBMS Jobs

SELECT job,        SUBSTR (what, 1, 30),        schema_user,        last_date,        next_date,        broken,        NVL2 (this_date, 'Y', 'N') running   FROM dba_jobs s ORDER BY schema_user, last_date

Buffer Gets

Replace the v.sql text with the frequent SQL statements important to your application.

SELECT v.inst_id,        ROUND (v.buffer_gets / v.executions, 1) avggets,        v.executions,        v.module,        SUBSTR (v.sql_text, 1, 60) querysql   FROM sys.gv_$sql v WHERE v.buffer_gets > 0    AND v.executions > 0    AND v.executions IS NOT NULL    AND DECODE (executions, 0, NULL, (v.buffer_gets / v.executions)) > 10    AND (v.sql_text LIKE 'FROM TBL_ABC%'      OR  v.sql_text LIKE 'FROM TBL_XYZ%')  ORDER BY v.inst_id, avggets DESC;

Oracle 11g Interactive Quick Reference guide

 

I just came across an essential Oracle 11g Interactive Quick Reference guide – which has the key dictionary views, background processes and architecture diagram.
Hope you may find it useful.  It can be downloaded from

http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=9575302&src=7027600&Act=54

What is High Water Mark in Oracle?

 

The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

For example'

if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,

At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.

with 10g there are two different HWM for a table

After deleting rows high water mark remain same, what will happen if i insert new rows now.where these new rows will be stored? above the HWM or below?
The space freed by deletions is available for new insertions, so it will be below, until this free space is filled.
Otherwise; Above the HWM when using the APPEND hint in the INSERT statement (or using DIRECT=Y in sql*loader)

They will remain under the HWM until the object is rebuilt, truncated, or shrunk (shrinking of a segment is a new Oracle 10g feature that is supported only if the segment is in an ASSM tablespace).

Sequence of steps performed when opening up a database

After issuing of SHUTDOWN ABORT command, when opening up database following step are performed

1. SPFile / PFile:             Server Parameter file ( SP File ) or the initialization parameter (initSID.ora / PFILE ) file is read
2. Memory :                      SGA is allocated
3. Background Process: Background process are started
4. Control File :                Control file is read
5. Redo Log File:              Redo Log file is read
6. Data File:                       Data files are checked for consistency
7. Instance Recovery:   Instance Recovery is started

How to delete control file

 

Verify the file specification information for the current control file(s): SQL> show parameters control_files

If your are using pfile, change CONTROL_FILES to point to the remaining control files. Make sure that the full file specification is used. vi $ORACLE_HOME/dbs/init<SID>.ora. CONTROL_FILES = (/oradata/<sid>/data1/control_01.ctl, /oradata/<sid>/data3/control_02.ctl) If you are using spfile, you may modify the CONTROL_FILES parameter using the ALTER SYSTEM SET command. SQL> alter system set control_files = /oradata/<sid>/data1/control_01.ctl, /oradata/<sid>/data3/control_02.ctl scope=spfile;

Shutdown and startup the database.

Verify the file specification information for the current control file(s): SQL> show parameters control_files If desired, delete the control file from the O/S.

OEM OMS 10g Server Commands to start, stop and know the status (opmnctl)

Check status of the GRID management server

1) export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn 2) cd $ORACLE_HOME/bin 3) ./opmnctl status

oracle@omsrvrap$  export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn
oracle@omsrvrap$  cd $ORACLE_HOME/bin
oracle@omsrvrap$  opmnctl status

Processes in Instance: EnterpriseManager0.omsrvrap.dbosmsrvr.siva.com
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status 
-------------------+--------------------+---------+---------
LogLoader          | logloaderd         |     N/A | Down   
HTTP_Server        | HTTP_Server        |    9146 | Alive  
dcm-daemon         | dcm-daemon         |     N/A | Down   
OC4J               | home               |    9147 | Alive  
OC4J               | OC4J_EM            |    9148 | Alive  
OC4J               | OC4J_EMPROV        |    9150 | Alive  
OC4J               | OCMRepeater        |    9151 | Alive  
WebCache           | WebCache           |    9152 | Alive  
WebCache           | WebCacheAdmin      |    9153 | Alive  
DSA                | DSA                |     N/A | Down   

Stop the GRID management server

1) export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn 2) cd $ORACLE_HOME/bin 3) ./opmnctl stopall

oracle@omsrvrap$  export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn
oracle@omsrvrap$  cd $ORACLE_HOME/bin
oracle@omsrvrap$  opmnctl status

opmnctl: stopping opmn and all managed processes...

Start the GRID management server

1) export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn 2) cd $ORACLE_HOME/bin 3) ./opmnctl startall

oracle@omsrvrap$  export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn
oracle@omsrvrap$  cd $ORACLE_HOME/bin
oracle@omsrvrap$  opmnctl status

opmnctl: starting opmn and all managed processes...

OEM Grid 10g Agent commands to start, stop and know the status. ( emctl )

Oracle10g OEM GRID Agent ( emctl )Commands

Check status of the GRID control agent

1) export ORACLE_HOME=/app/oracle/product/10g/agent10g 2) cd $ORACLE_HOME/bin 3) ./emctl status agent

oracle@dbsrvr1$ export ORACLE_HOME=/app/oracle/product/10g/agent10g
oracle@dbsrvr1$ cd $ORACLE_HOME/bin
oracle@dbsrvr1$ ./emctl status agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.5.0
OMS Version : 10.2.0.5.0
Protocol Version : 10.2.0.5.0
Agent Home : /app/oracle/product/10g/agent10g
Agent binaries : /app/oracle/product/10g/agent10g
Agent Process ID : 20124
Parent Process ID : 20080
Agent URL : https://dbsrvr1.dbsrvr.siva.com:3872/emd/main/
Repository URL : https://hp6600ap.dbsrvr.siva.com:1159/em/upload
Started at : 2010-03-09 12:04:07
Started by user : oracle
Last Reload : 2010-03-09 12:08:39
Last successful upload : 2009-07-09 14:26:42
Total Megabytes of XML files uploaded so far : 291.89
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 35.13%
Last successful heartbeat to OMS : 2010-03-09 12:26:07
Agent is Running and Ready

STOP the GRID control agent

1) export ORACLE_HOME=/app/oracle/product/10g/agent10g 2) cd $ORACLE_HOME/bin 3) ./emctl stop agent

oracle@dbsrvr1$ export ORACLE_HOME=/app/oracle/product/10g/agent10g
oracle@dbsrvr1$ cd $ORACLE_HOME/bin
oracle@dbsrvr1$ ./emctl stop agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

START the GRID control agent

1) export ORACLE_HOME=/app/oracle/product/10g/agent10g 2) cd $ORACLE_HOME/bin 3) ./emctl start agent

.

oracle@dbsrvr1$ export ORACLE_HOME=/app/oracle/product/10g/agent10g
oracle@dbsrvr1$ cd $ORACLE_HOME/bin
oracle@dbsrvr1$ /emctl start agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Starting agent ................................ started.

OEM Agent Log Files

The OEM Agent files are placed on AGENT_HOME/sysman/log/ by default.

OEM Agent Log File	Management Agent log file	emagent.log
OEM Agent Trace File	Management Agent trace file	emagent.trc
OEM Agent startup log file	Management Agent startup log file	emagent.nohup

What is mounting the databases in Oracle?

After starting the instance, the Oracle software associates the instance with specific database. This is called mounting the database.

Oracle Database Memory Structure

Oracle Database Storage Structure

 

Oracle DB Storage Structure

Relationship of Database, Tablespace and Data files

 

How table data is stored in Oracle Database

Content of Database Data Block