Script to Gather data from the Linux OS

#####################################################################
###   Unix script os_stats.sh                                     ###
###   Designed to be run periodically to collate information      ###
###   START OF SCRIPT                                             ###
#####################################################################
#
LOG_FILE="OS_`hostname`_`date '+%m%d%y_%H%M'`.txt"
#
echo "**********************************************" >$LOG_FILE
date >> $LOG_FILE
echo "Running as  `id`" >> $LOG_FILE
echo "**********************************************" >>$LOG_FILE
echo "uname -a" >>$LOG_FILE
uname -a >>$LOG_FILE
cat /etc/issue >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ulimit -a" >>$LOG_FILE
ulimit -a >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ulimit -Ha" >>$LOG_FILE
ulimit -Ha >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "Netstat -i" >>$LOG_FILE
netstat -i >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "netstat -an">> $LOG_FILE
netstat -an >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "netstat -s">> $LOG_FILE
netstat -s >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "sar -u 5 3">> $LOG_FILE
sar -u 5 3 >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "sar -q 5 3">> $LOG_FILE
sar -q 5 3 >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ps -e -w -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args" >> $LOG_FILE
ps -e -ww -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "iostat -t -x" >> $LOG_FILE
iostat -t -x >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "df -h" >> $LOG_FILE
df -h >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "mpstat 5 3" >> $LOG_FILE
/usr/bin/mpstat 5 3 >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "vmstat 5 3" >> $LOG_FILE
vmstat 5 3 >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "free -m -s 5 -c 3" >> $LOG_FILE
free -m -s 5 -c 3 >>$LOG_FILE
grep MemTotal /proc/meminfo >>$LOG_FILE
grep SwapTotal /proc/meminfo >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "IPCS data" >> $LOG_FILE
ipcs -l >> $LOG_FILE
echo "            ----------------------------         " >> $LOG_FILE
ipcs -u >> $LOG_FILE
echo "            ----------------------------         " >> $LOG_FILE
ipcs >> $LOG_FILE
echo "            ----------------------------         " >> $LOG_FILE
ipcs -t >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
#####################################################################
###   END OF SCRIPT                                               ###
#####################################################################

Script to Gather data from the database for Process information and User connections

REM
REM START OF SQL
REM
REM  Filename     : mzRunSQL.sql
REM  Author       : Mike Shaw
REM  Date Updated : 29 August 2008
REM  Purpose      : Script to gather data from the database for process
REM                 information and user connections
REM
set serveroutput on size 1000000
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 110
col username form a10
col program form a30
col how_many forma 9999
col machine form a25
col module form a50
--
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') START_TIME from dual
/
rem Instance identification
select *
from v$instance
/
REM Summary of database connections
select s.module, s.machine, s.username, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by rollup(s.module,s.machine,s.username)
/
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM Get count of number of connected Apps 11i users
REM
col user_name format a15
col first_connect format a18
col last_connect format a18
col How_many_user_sessions format 9999999999
col How_many_sessions format 9999999999
REM
REM Summary of how many users
REM
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate  
and counter < limit_connects
/
REM
REM Number of sessions per user
REM
select user_name, count(*) How_many_sessions
from icx_sessions icx, fnd_user u
where icx.user_id = u.user_id
and disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate  
and counter < limit_connects
group by user_name
order by 2 desc
/
REM
REM Quick look for blockers and lockers
REM
col BLOCKER form a5
col Username format A15
col Sid format 9990 heading SID
col Type format A4
col Lmode format 990 heading 'HELD'
col Request format 990 heading 'REQ'
col Id1 format 999999999999
col Id2 format 999999999999
break on Id1 skip 1 dup
--
SELECT M.Sid,
M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1,
M.Id2,
DECODE(block, 0, 'NO', 'YES' ) BLOCKER
FROM V$LOCK M
WHERE
M.Request ! = 0 or (M.Request = 0 and Lmode != 4 and (id1, id2) in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1 = M.Id1 and S.Id2 = M.Id2) )
ORDER BY Id1, Id2, M.Request
/
REM
REM Check for database statement leaks
REM May give symptoms like OutOfMemoryError
REM
set lines 132
set pages 1000
column module format A40
column program format A30
select oc.sid,oc.hash_value,oc.sql_text,count(*) How_Many
from gv$open_cursor oc
group by sid,hash_value,sql_text
having count(*) > 5
order by 4
/
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') END_TIME from dual
/
exit;
REM
REM END OF SQL
REM 

Speech by Chetan Bhagat at Symbiosis

RECOVERY Methods in Oracle

In general there are three steps involved in restoring files:

Ensure that the target database is started in the appropriate mode for the restoration operation. For lost control files, this will be nomount mode. If the entire database needs to be restored, this will be mount mode. If datafiles that don't belong to the SYSTEM tablespace are damaged, you have the option of keeping the database open and taking only the tablespace(s)/datafile(s) that needs to be restored offline.
Start RMAN and connect to the target and recovery catalog if one is being used.
Run the appropriate RMAN RESTORE command to bring back required files. The requested files and the appropriate archived redo log files will be restored.
Once the necessary files are restored, you need to recover your database and open it for use. You can recover the database from either RMAN or SQL*Plus.

ORA-01502 index or partition of such index is in unusable state

ERROR:-
-----
ORA-01502: index "owner.index_name" or partition of such index is in unusable state

SOLUTION:-
--------
REBUILD UNUSABLE INDEXES in Oracle Database

$sqlplus "/as sysdba"

select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER    INDEX_NAME    TABLE_NAME    STATUS
-----    ----------    ----------    ------
owner    index_name     table_name    INVALID

select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER    SEGMENT_NAME    SIZE IN MB   
-----    ------------    ----------   
owner    index_name           3000   

alter session set current_schema='&schema_name';
Session altered.

alter index <index_name> rebuild;
Index altered.

select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER    INDEX_NAME    TABLE_NAME    STATUS
-----    ----------    ----------    ------
owner    index_name     table_name    VALID

select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER    SEGMENT_NAME    SIZE IN MB   
-----    ------------    ----------   
owner    index_name            800

------------------------------------  OR  ------------------------------------------------------

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

This will output statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.

Oracle DBA Interview Questions and Answers - Oracle 11g Database New Features

Oracle 11g Database New Features Interview Questions and Answers

  1. Database Replay
  2. The SQL Performance Analyzer
  3. Online Patching in Oracle Database Control
  4. Automatic Diagnostic Repository (ADR)
  5. Data Recovery Advisor
  6. Automatic Memory Management
  7. Invisible Indexes
  8. Read-Only Tables
  9. Shrinking Temporary Tablespaces and Tempfiles
10. Server Result Cache
11. SQL Tuning Automation
12. SQL Plan Management
13. Database ADDM
14. New SYSASM Privilege for ASM Administration
15. Enhanced Block Media Recovery
16. VALIDATE Command
17. Configuring an Archived Redo Log Deletion Policy
18. Active Database Duplication
19. Virtual Private Catalogs
20. ASM Restricted Mode
21. Checking Diskgroup
22. The FORCE option with Drop Diskgroup Command
23. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition

ASM Rolling Upgrades

Database Replay
Database Replay (sometimes named as Workload Replay) feature in Oracle11g allows you to reproduce the
production database conditions in a testing environment.In other words, with this feature you can capture the
actual workload on a production system and replay it in a test system. This way, you can analyze the condition of the production database without working on the actual production database.

This feature enables you to test the impact of applying changes on a production database. These changes could be database upgrades, switching to RAC, application upgrades, operating system upgrades or storage system changes.

The SQL Performance Analyzer
The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL statements, it provides you recommendations on how to improve their performance.

This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it to study the effect of upgrading an Oracle database 10g release 2 to 11g.

Note:
If you plan to use SPA on a test database, it is highly recommended to make the test database resemble the production database as closely as possible. You can use the RMAN duplicate command for this purpose.

Online Patching in Oracle Database Control
Patching through Database Control is enhanced in Oracle 11g. With Oracle 11g online patching (or called hot patching),you can apply or roll back a database patch while the instance is running. Also it can detect conflicts between two online patches.On the other hand, online patching consumes more memory than the conventional method.

In UNIX systems, you use the script $ORACLE_HOME/OPatch/opatch to invoke the online patching.

Automatic Diagnostic Repository (ADR)
The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.

In Oracle 11g, there is a new framework (named as fault diagnosability infrastructure) consisting of many tools for diagnosing and repairing the errors in the database. All those tools refer to the ADR in their operation.

ADR is developed to provide the following advantages:
1.Diagnosis data, because it is stored in file system, is available even when the database is down.
2.It is easier to provide Oracle support with diagnosis data when a problem occurs in the database.
3.ADR has diagnosis data not only for the database instance. It has troubleshooting data for other Oracle components such as ASM and CRS.

Note:
For each database instance two alert log files are generated: one as text file and one with xml
format. Contents of the xml-formatted file can be examined using adrci tool.

Also the xml-formatted alert log is saved in the ADR and specifically in the directory
$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/alert

Data Recovery Advisor
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options,and executes repairs at the user's request. Data Recovery Advisor can diagnose failures such as the following:

1. Inaccessible components like datafiles and control files.
2. Physical corruptions such as block checksum failures and invalid block header
3. Field values
4. Inconsistent datafiles (online and offline)
5. I/O failures

The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager.

Automatic Memory Management
In Oracle 11g, a new parameter named as MEMORY_TARGET is added to automate memory allocation for both the SGA and PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload.

This parameter is dynamic and can be alter using the ALTER SYSTEM command as shown below:
ALTER SYSTEM SET MEMORY_TARGET = 1024M ;

However, if the database is not configured to use this parameter and you want to use it, you must restart the
database after setting the parameter.

Invisible Indexes
Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries.

When using invisible indexes, consider the following:
- If you rebuild an invisible index, the resulting operation will make the index visible.
- If you want the optimizer to consider the invisible indexes in its operation, you can set the new initialization
parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE (the default is FALSE). You can set the parameter in the system and session levels.

Read-Only Tables
In Oracle 11g, you can set a table to be read only,i.e. users can only query from the table but no DML
statement is allowed on the table.

Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles.

Server Result Cache
In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results. The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course,the target is to obtain high response time. The cached results stored become invalid when data in the dependent database objects is modified.

As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.

SQL Tuning Automation
The SQL Tuning Advisor is run by default every night during the automated maintenance window. Basically, the advisor catches the SQL statements from AWR that are candidate for tuning (they are called buckets) during four different time periods.It then automatically creates SQL profile for any poor SQL statement, if that helps. Tuned plans are automatically added to the SQL plan baselines by the automatic SQL tuning task.

The advisor also may recommend actions like creating  new indexes, refreshing statistics or re-writing the statement. These actions, however, are not automatically implemented by the advisor.

On Oracle 11g Release 2 (11.2.0.2), a new package named as DBMS_AUTO_SQLTUNE should be used instead of the DBMS_SQLTUNE package. The new package provides more restrictive access to the Automatic SQL Tuning feature.

To use the DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS.

SQL Plan Management
SQL plan management (SPM), is a new feature in Oracle 11g that prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.Changes to the execution plan may be resulted from database upgrades,system and data changes, application upgrade or bug fixes.

When SPM is enabled, the system maintains a plan history that contains all plans generated by the optimizer and store them in a component called plan baseline. Among the plan history in the plan baseline, plans that are verified not to cause performance regression are marked as acceptable. The plan baseline is used by the optimizer to decide on the best plan to use when compiling a SQL statement.

Repository stored in data dictionary of plan baselines and statement log maintained by the optimizer is called
SQL management base(SMB).

SQL Plan management is implemented by undertaking the following phases:
1.Capturing SQL Plan Baselines: this can be done automatically or manually.
2.Selecting SQL Plan Baselines by the optimizer
3.Evolving SQL Plan Baselines

Database ADDM
Oracle Database 11g has added a new layer of analysis to ADDM called Database ADDM. The mode ADDM was working in Oracle 10g is now called instance ADDM. The main target of database ADDM is to analyze and report on RAC environment. To enable Database ADDM, you set the parameter INSTANCES in DBMS_ADVISOR.

New SYSASM Privilege for ASM Administration
SYSASM is a new privilege introduced in Oracle 11g. Users who are granted this privilege can perform ASM administration tasks. The idea behind this privilege is to separate database management and the storage management responsibilities.

Backup and Recovery New Features:-

Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ...
blockcommand replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.

While the block media recovery is going on, any attempt by users to access data in the corrupt blocks will result in an error message, telling the user that the data block is corrupt.

VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles,backup sets, and even individual data blocks. The comma nd by default checks for physical corruption. You can optionally specify CHECK LOGICAL . Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.

Configuring an Archived Redo Log Deletion Policy
You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for
deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When the policy is configured, it applies on the automatic deletion of the logs in the flash recovery area and the manual deletion by the BACKUP ... DELETE and DELETE ... ARCHIVELOG commands.

To enable an archived redo log deletion policy, run the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP n TIMES command with the desired options.

Active Database Duplication
In Oracle Database 11g, you can directly duplicate a data base over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance.

Virtual Private Catalogs
In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog. The subset that a user has read/write access to is termed as virtual private catalog, or just virtual catalog. The central or source recovery catalog is now called the base recovery catalog.

ASM Restricted Mode

In Oracle 11g, you can start the ASM instance in restricted mode. When in restricted mode, databases will not be permitted to access the ASM instance. Also, individual diskgroup can be set in restricted mode.

Checking Diskgroup

Starting from Oracle Database 11g, you can validate the internal consistency of ASM diskgroup metadata using the ALTER DISKGROUP ... CHECK command. Summary of errors is logged in the ASM alert log file.

The FORCE option with Drop Diskgroup Command

If a disk is destroyed beyond repair, you want to drop it. But because the disk is practically damaged, you cannot mount it and thus you cannot issue the DROP DISKGROUP command against it. In such a condition, Oracle 11g provides the FORCE INCLUDING CONTENTS option to drop the diskgroup even if it is not mounted.

SQL>DROP DISKGROUP <DISKGROUP_NAME> FORCE INCLUDING CONTENTS;

ASM Rolling Upgrades
ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting
database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions.

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster.  Oracle Active Data Guard enables read-only access to a physical standby database.

With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.

Active Dataguard Features:
1.  Physical Standby with Real-time Query
2.  Fast Incremental Backup on Physical Standby.
3.  Automatic Block Repair.


http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf

http://pocketoracle.blogspot.com/search/label/10g%20New%20Features

http://pocketoracle.blogspot.com/search/label/11g%20New%20Features

How to find Tablespace Fragmentation in Oracle Database

How to find Tablespace Fragmentation in Oracle Database

To determine if your tablespaces are having a problem with fragmentation, you can use the below script:

set pages 50000 lines 32767
select tablespace_name,count(*) free_chunks,decode(round((max(bytes) / 1024000),2),null,0,
round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
from sys.dba_free_space group by tablespace_name order by 2 desc, 1;

Here,fragmentationindex column will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.

The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.

How to find Table Fragmentation in Oracle Database

How to find Table Fragmentation in Oracle Database

What is Oracle Table Fragmentation?
If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

What are the reasons to reorganization of table?

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to find Table Fragmentation?

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.

Steps to Check and Remove Table Fragmentation:-
=============================================
1. Gather table stats:
---------------------
To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats('&schema_name','&table_name');

2. Check Table size:
-------------------
Now again check table size using and will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';

3. Check for Fragmentation in table:
-----------------------------------
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.

set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
/
Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. How to reset HWM / remove fragemenation?
---------------------------------------
We have four options to reorganize fragmented tables:

1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
   (Depends upon the free space available in the tablespace)
2. Export and import the table:- (difficult to implement in production environment)
3. Shrink command (fron Oracle 10g)
   (Shrink command is only applicable for tables which are tablespace with auto segment space management)

Here, I am following Options 1 and 3 option by keeping table availability in mind.


Option: 1 Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
------------------------------------------------------------------------------------------
Collect status of all the indexes on the table:-
----------------------------------------------
We will record Index status at one place, So that we get back them after completion of this exercise, 

select index_name,status from dba_indexes where table_name like '&table_name';

Move table in to same or new tablespace:
---------------------------------------
In this step we will move fragmented table to same tablespace or from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from dba_segments and check if same or any other tablespace has same free space available. So, that we can move this table to same or new tablespace.

Steps to Move table in to same tablespace:
-----------------------------------------
alter table <table_name> move;   ------> Move to same tablespace

OR

Steps to Move table in to new tablespace:
----------------------------------------
alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

Now,Rebuild all indexes:
-----------------------
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

SQL> select status,index_name from dba_indexes where table_name = '&table_name';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_NAME                            -------> Here, value in status field may be valid or unusable.

SQL> alter index <INDEX_NAME> rebuild online;  -------> Use this command for each index
Index altered.

SQL> select status,index_name from dba_indexes where table_name = '&table_name';

STATUS INDEX_NAME
-------- ------------------------------
VALID INDEX_NAME                               -------> Here, value in status field must be valid.

Gather table stats:
------------------
SQL> exec dbms_stats.gather_table_stats('&owner_name','&table_name');
PL/SQL procedure successfully completed.

Check Table size:
-----------------
Now again check table size using and will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';

Check for Fragmentation in table:
--------------------------------
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
 /
==================================================================================================================
Option: 3 Shrink command (fron Oracle 10g):-
------------------------------------------

Shrink command:
--------------
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table <table_name> enable row movement;
Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:
-----------------------------------
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table <table_name> shrink space compact;
Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table <table_name> shrink space;
Table altered.

2. Directly reset the HWM:
-------------------------
SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.

Advantages over the conventional methods are:
--------------------------------------------
1. Unlike "alter table move ..",indexes are not in UNUSABLE state.After shrink command,indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.

Query is running slow for long time for SQL_ID

Query is running slow for long time for SQL_ID captured

Run SQL Tuning Adviosor for the sql_id
--------------------------------------
Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID.
So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID.Recommendations from sqltrpt.sql will be providing the best Explain Plan. We can implement these profiles/index rebuild/... suggested after checking with the SME of the database.

Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql

$sqlplus "/as sysdba"

Query to see current running sqls
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
--------------------------------------------------------------------
SQL> execute dbms_sqltune.accept_sql_profile(task_name => '<TASK_NAME>',task_owner => 'SYS', replace => TRUE, FORCE_MATCH => TRUE);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:
----------------------------------------------------
SQL> execute dbms_sqltune.drop_sql_profile('<SQL_PROFILE_NAME>');

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

Command to Alter SQL Profile in Oracle for the SQL_ID:
-----------------------------------------------------
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('<SQL_PROFILE_NAME>','STATUS','DISABLED');

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

If you don't know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%TABLE%NAME%';

Query
-----
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and sql_profile like nvl('&sql_profile_name',sql_profile)
and sql_profile is not null
order by 1, 2, 3
/

For More:-
--------
Migrate Oracle SQL Profile
http://www.allguru.net/database/migrate-oracle-sql-profile/

Swapping SQL Profiles
http://www.dba-oracle.com/t_swapping_sql_profiles.htm

Tuning queries with SQL Tuning Advisor and SQL Profiles
http://troubleshooot.wordpress.com/2011/01/14/43/

Data Guard Sync Status

Method :1
=========
PRIMARY
========

sqlplus "/ as sysdba"
set pages 50000 lines 32767col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

==================================================================================================================

Method :2
=========

STANDBY
========
sqlplus "/ as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT

col time format a40
select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

==================================================================================================================

Method :3
=========
PRIMARY
========
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
                                                                   0 OR min
==================================================================================================================

Method :4
=========
PRIMARY
========
Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;

STANDBY
========
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;

==================================================================================================================

Method :5
=========

STANDBY
========
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0      N/A            1000 APPLIED_LOG

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;  ------ the status of redo log files
LGWR RFS 1000 CURRENT

Both the above current values must be same.