How to check SGA resize operations?

select
    component,
    oper_type,
    oper_mode,
    initial_size/1024/1024 "Initial",
    TARGET_SIZE/1024/1024  "Target",
    FINAL_SIZE/1024/1024   "Final",
    status,
    to_char(start_time,'dd-mon hh24:mi:ss') start_time,
    to_char(end_time,'dd-mon hh24:mi:ss')   end_time
 from
    v$sga_resize_ops;

select
    component,
    current_size/1024/1024 "CURRENT_SIZE",
    min_size/1024/1024 "MIN_SIZE",
    user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
    last_oper_type "TYPE",
    to_char(LAST_OPER_TIME,'dd-mon hh24:mi:ss') "LAST_OPER_TIME"
 from
    v$sga_dynamic_components;

How to resolve ora-00845 ?

SQL> startup;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> !df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_bodb-lv_root
                       26G   14G   12G  55% /
tmpfs                 5.7G   72K  5.7G   1% /dev/shm  <-- increase size of tmpfs
/dev/xvda1            477M   72M  376M  17% /boot
/dev/mapper/BODB_ORADATA-ORADATA
                      688G   12G  642G   2% /oradata
/dev/mapper/BODB_FRA-BODB_FRA
                       98G   75M   93G   1% /orafra

vi /etc/fstab
existing line :-
tmpfs                   /dev/shm                tmpfs   defaults        0 0
new line :-
tmpfs                   /dev/shm                tmpfs   size=8G        0 0
 

how to resolve xauthority file is empty ?

[root@sugap01 vivv]# ls -ldZ /weblogic
drwx------ weblogic weblogic ?                                /weblogic
[root@sugap01 vivv]# chcon -R -t user_home_dir_t /weblogic
chcon: can't apply partial context to unlabeled file `.bash_logout'
chcon: can't apply partial context to unlabeled file `extensions'
chcon: can't apply partial context to unlabeled file `plugins'
chcon: can't apply partial context to unlabeled file `.mozilla'
chcon: can't apply partial context to unlabeled file `.gnome2'
chcon: can't apply partial context to unlabeled file `.bashrc'
chcon: can't apply partial context to unlabeled file `.bash_profile'
chcon: can't apply partial context to unlabeled file `/weblogic'

Solution :-

[root@sugap01 vivv]# chcon system_u:object_r:user_home_dir_t /weblogic
[root@sugap01 vivv]# ls -ldZ /weblogic
drwx------. weblogic weblogic system_u:object_r:user_home_dir_t /weblogic

Now you can

ssh -X user@host

.Xauthority file will be written properly.

rman backup retention policy

Recovery Window-Based Retention Policy ==>
RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window. Additionally, RMAN retains all archived logs and level 1 incremental backups that are needed to recover to a random point within the window.


Redundancy-Based Retention Policy==>
The REDUNDANCY parameter of the CONFIGURE RETENTION POLICY command specifies how many full or level 0 backups of each datafile and control file that RMAN should keep. If the number of full or level 0 backups for a specific datafile or control file exceeds the REDUNDANCY setting, then RMAN considers the extra backups as obsolete. The default retention policy is REDUNDANCY 1.

How to analyze OSW ( os watcher )logs ???

from oswbb directory where there is oswbba.jar file
$ORACLE_HOME/jdk/bin/java -jar -Xmx512M oswbba.jar -i /home/kartik/CODS2_30_oct
then select P
then select A
then select Q

How to set path for sql scripts in sqlplus ???

[oracle@testdb ~] <svbo> $ echo $SQLPATH
/home/oracle/scripts:/oracle/11.2.0.4/rdbms/admin:.
[oracle@testdb ~] <svbo> $

In the RMAN command-line interface the Data Recovery Advisor commands

LIST FAILURE;
ADVISE FAILURE;
REPAIR FAILURE;

How to copy tablespace to different diskgroup ???

CONFIGURE DEVICE TYPE DISK PARALLELISM 40 BACKUP TYPE TO BACKUPSET;
run
{
BACKUP AS COPY TABLESPACE SMALL_IX format '+PROD_DATA01';
}

How to check run queue on linux???

sar -q 1 10

Oracle UNION VS UNION ALL

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows,
but usually you do not want the duplicates (especially when developing reports).
 

oracle how to disable AMM (Automatic Memory Management) ???

Assuming that you have already set values for SGA_MAX_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE etc.

alter system reset memory_target scope=spfile ;
alter system reset memory_max_target scope=spfile ;
shutdown immediate;
startup;
 

How to do dynamic select cursor bulk collect into and forall insert ???

This one is my favorite :-


CREATE OR REPLACE PROCEDURE cur_to_table (CUR_SQL VARCHAR2, P_out_OWNER_name varchar2, P_out_tab_name varchar2) AS
  TYPE t_col_name IS TABLE OF DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
  v_col_name   t_col_name;
  var_all_columns    Varchar2(4000);
  var_sel_all_columns    Varchar2(4000);
  v_cnt     NUMBER          := 0;
  V_CUR_SQL varchar2(4000);
  v_output_table varchar2(75);
  l_sql VARCHAR2(10000);
BEGIN
v_cnt:=0;
v_output_table := '';
select count(1) into v_cnt from dba_tables where owner = upper(P_out_OWNER_name) and table_name = upper(P_out_tab_name);
IF v_cnt <> 1 then
 raise_application_error(-20101, 'ERROR :- Please check out put table name and owner name.');
END IF;
v_output_table := trim(upper(P_out_OWNER_name)||'.'||upper(P_out_tab_name));
V_CUR_SQL := upper(CUR_SQL);
--v_cnt:=0;
--execute immediate 'select count(1) from '||v_output_table ||' where rownum < 2'  into v_cnt;
--IF v_cnt > 0 then
-- raise_application_error(-20101, 'ERROR :- Target table not empty.');
--END IF;
-- DBMS_OUTPUT.PUT_LINE(V_input_table);
-- DBMS_OUTPUT.PUT_LINE(v_output_table);
var_all_columns := '';
var_sel_all_columns := '';
EXECUTE IMMEDIATE 'SELECT COLUMN_NAME from DBA_TAB_COLUMNS where OWNER = '''||upper(P_out_OWNER_name)||''' AND TABLE_NAME = '''||upper(P_out_tab_name)||''' order by COLUMN_ID' bulk collect INTO v_col_name;
         FOR i IN v_col_name.FIRST .. v_col_name.LAST LOOP
  IF i = v_col_name.last THEN
   var_sel_all_columns := var_sel_all_columns ||' '||v_col_name(i);
   var_all_columns := var_all_columns ||' r_cnt.'|| v_col_name(i);
  ELSE
   var_sel_all_columns := var_sel_all_columns||v_col_name(i)||', ';
   var_all_columns := var_all_columns ||' r_cnt.'||  v_col_name(i)||', ';
  END IF;  
  END LOOP; 
-- DBMS_OUTPUT.PUT_LINE(var_sel_all_columns);
    l_sql := 'DECLARE '||
        'TYPE tab_row_type IS TABLE OF '|| v_output_table ||'%ROWTYPE;'||
        'var_tab    tab_row_type  := tab_row_type ();'||
        'r_cnt number;'||
               '   CURSOR c1 IS '||V_CUR_SQL||'; '||
               'BEGIN '||
        '   OPEN c1;'||
        '   LOOP'||
        '   FETCH c1 BULK COLLECT INTO var_tab LIMIT 50000;'||
        '   FORALL r_cnt IN 1..var_tab.count'||
        '         insert into '||v_output_table||' VALUES var_tab(r_cnt); '||
               '   COMMIT; '||
               '   EXIT WHEN c1%NOTFOUND;'||
               '   END LOOP; '||
               '   CLOSE c1; '||
               '   COMMIT; '||
               'END; ';
-- DBMS_OUTPUT.PUT_LINE(l_sql);
      EXECUTE IMMEDIATE l_sql;
END;
/


sample out put :-



SQL> select count(1) from temp_dba_tables;
  COUNT(1)
----------
    880736
1 row selected.
SQL> set lines 180 pages 5000
SQL> execute cur_to_table ('select * from temp_dba_tables','SYS','TEMP_DBA_TABLES');
PL/SQL procedure successfully completed.
SQL> select count(1) from temp_dba_tables;
  COUNT(1)
----------
   1761472
1 row selected.
 

How to find out oracle_home from database ?? find oracle_home

Declare
OHM varchar2(100);
begin
OHM := NULL;
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) INTO OHM FROM
dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
IF length(OHM) < 4 THEN
 dbms_system.get_env('ORACLE_HOME', :OHM) ;
END IF;
dbms_output.put_line('ORACLE_HOME : '||:OHM);
END;
/

How to find out top enqeue waits ??? top enqueue sql ( top contention )

set lines 182 pages 5000
col event format a35
SELECT sql_id,
  event,
  p1,
  mod(p1,16) AS "mode",
  SUM(TIME_WAITED) T_waited
FROM gv$active_session_history
WHERE event LIKE 'enq%' and sql_id is not null
GROUP BY sql_id,
  event,
  p1,
  mod(p1,16) order by 5 desc;

DB user activity tracking

set lines 480
set pages 50000
col SAMPLE_TIME format a25
col sql_text format a75
col program format a25
col machine format a25
col event format a30
SELECT sample_time,
  gvash.inst_id,
  session_id,
  session_serial#,
  user_id,
  gvash.sql_id,
  substr(event,0,30) event,
  substr(program,0,25) program,
  substr(sql_text,0,75) sql_text
FROM gv$active_session_history gvash,
  gv$sql gvs
WHERE gvash.user_id     =58
AND gvs.sql_id    = gvash.sql_id
AND gvash.inst_id = gvs.inst_id and sql_text is not null and sample_time like '31-JUN-13 %';
 

Drop partition VS truncate partition

SQL> SELECT owner,table_name, index_name, partitioned, status FROM   dba_indexes where table_name = 'HCPT_LOGGING_ERRORHANDLING' and index_name = 'IDX_GLOBAL_HCPT_ERR_HANDLING' ORDER BY 1,2;
                               Table                               Index                                   Index
OWNER                          Name                                Name                                Par Status
------------------------------ ----------------------------------- ----------------------------------- --- --------
SYS                            HCPT_LOGGING_ERRORHANDLING          IDX_GLOBAL_HCPT_ERR_HANDLING        NO  VALID
Elapsed: 00:00:00.01
SQL> select count(1) from HCPT_LOGGING_ERRORHANDLING partition(HLE_11082013);
  COUNT(1)
----------
      2140
Elapsed: 00:00:00.01
SQL> select count(1) from HCPT_LOGGING_ERRORHANDLING partition(HLE_12082013);
  COUNT(1)
----------
      2140
Elapsed: 00:00:00.00
SQL> alter table HCPT_LOGGING_ERRORHANDLING truncate partition HLE_11082013 UPDATE INDEXES;
Table truncated.
Elapsed: 00:00:00.67
SQL> SELECT VALUE redo_size FROM v$mystat, v$statname WHERE v$mystat.STATISTIC# = v$statname.STATISTIC# AND name = 'redo size';
 REDO_SIZE
----------
    761600
Elapsed: 00:00:00.00
SQL> alter table HCPT_LOGGING_ERRORHANDLING drop partition HLE_12082013 UPDATE INDEXES;
Table altered.
Elapsed: 00:00:00.45
SQL> SELECT VALUE redo_size FROM v$mystat, v$statname WHERE v$mystat.STATISTIC# = v$statname.STATISTIC# AND name = 'redo size';
 REDO_SIZE
----------
   1687492
Elapsed: 00:00:00.00
SQL> SELECT owner,table_name, index_name, partitioned, status FROM   dba_indexes where table_name = 'HCPT_LOGGING_ERRORHANDLING' and index_name = 'IDX_GLOBAL_HCPT_ERR_HANDLING' ORDER BY 1,2;
                               Table                               Index                                   Index
OWNER                          Name                                Name                                Par Status
------------------------------ ----------------------------------- ----------------------------------- --- --------
SYS                            HCPT_LOGGING_ERRORHANDLING          IDX_GLOBAL_HCPT_ERR_HANDLING        NO  VALID
Elapsed: 00:00:00.01
SQL> explain plan for select key_id from HCPT_LOGGING_ERRORHANDLING where key_id ='API_Name2410201319';
Explained.
Elapsed: 00:00:00.03

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 137249159
-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                              |  1492 | 23872 |     9   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_GLOBAL_HCPT_ERR_HANDLING |  1492 | 23872 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("KEY_ID"='API_Name2410201319')
13 rows selected.

Elapsed: 00:00:00.34
SQL> select key_id from HCPT_LOGGING_ERRORHANDLING where key_id ='API_Name2410201319';
KEY_ID
------------------------------------------------------------------------------------------------------------------------------------
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
19 rows selected.
Elapsed: 00:00:00.03
SQL>

How to find out locking details of history ??? lock history history lock historical locks

set lines 380 pages 5000
col B_Time format a24
col W_SID format 999999
col B_SID format 999999
col W_SERIAL format 9999999
col B_SERIAL format 9999999
col W_SQLTEXT format a55 WRAP
col B_SQLTEXT format a55 WRAP
SELECT w.sample_time B_Time,
  w.session_id W_SID,
  w.SESSION_SERIAL# W_SERIAL,
  w.sql_text W_SQLTEXT,
  b.session_id B_SID,
  b.SESSION_SERIAL# B_SERIAL,
  b.sql_text B_SQLTEXT
FROM
  (SELECT DISTINCT TO_CHAR(a.sample_time,'yyyy-mon-dd hh24:mi:ss') sample_time,
    a.sql_id ,
    a.inst_id,
    a.SESSION_ID,
    a.SESSION_SERIAL#,
    a.blocking_session,
    a.blocking_session_serial#,
    a.user_id,
    s.sql_text,
    a.module
  FROM GV$ACTIVE_SESSION_HISTORY a ,
    gv$sql s
  WHERE a.sql_id        =s.sql_id
  AND blocking_session IS NOT NULL
  AND a.user_id        <> 0
  AND a.sample_time    >= TO_DATE('2013-jun-25 11:50:00','yyyy-mon-dd hh24:mi:ss')
  AND a.sample_time    <= TO_DATE('2013-jun-25 12:10:00','yyyy-mon-dd hh24:mi:ss')
--  AND a.sql_id          = 'd0nzggkn5ahzr'
  ) w,
  (SELECT DISTINCT TO_CHAR(a.sample_time,'yyyy-mon-dd hh24:mi:ss') sample_time,
    a.sql_id ,
    a.inst_id,
    a.SESSION_ID,
    a.session_Serial#,
    a.blocking_session,
    a.blocking_session_serial#,
    a.user_id,
    s.sql_text,
    a.module
  FROM GV$ACTIVE_SESSION_HISTORY a ,
    gv$sql s
  WHERE a.sql_id                                        =s.sql_id
  AND blocking_session                                 IS NOT NULL
  AND (a.inst_id,a.blocking_session, a.blocking_session_serial#) IN
    (SELECT inst_id,blocking_session,
      blocking_session_serial#
    FROM GV$ACTIVE_SESSION_HISTORY
    WHERE blocking_session       IS NOT NULL
    AND blocking_session_serial# IS NOT NULL
    )
  AND a.sample_time >= TO_DATE('2013-jun-25 11:50:00','yyyy-mon-dd hh24:mi:ss')
  AND a.sample_time <= TO_DATE('2013-jun-25 12:10:00','yyyy-mon-dd hh24:mi:ss')
  ) b
WHERE w.inst_id = b.inst_id
AND b.inst_id = 3
AND w.blocking_session       = b.session_id
AND w.blocking_session_serial# = b.SESSION_SERIAL#
AND w.sample_time              = b.sample_time;

How to find out oracle_home from database ??

Declare
OHM varchar2(100);
begin
OHM := NULL;
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) INTO OHM FROM
dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
IF length(OHM) < 4 THEN
 dbms_system.get_env('ORACLE_HOME', :OHM) ;
END IF;
dbms_output.put_line('ORACLE_HOME : '||:OHM);
END;
/

How switch back from xml listener.log to normal log ?

In the DB :-
diag_adr_enabled_listener = off
lsnrctl
set log_file_listener = listener_<name>.log
set log_directory_listener = $ORACLE_HOME/network/log
 

Restore RAC to single instance RMAN backup

Step 1 :- Create pfile from production database and modify following parameters
control_files
log_archive_dest_1
cluster_database_instances

Step 2 :- Startup no mount
startup nomount
Step 3 :- rman connect target=/  catalog rman/rman@rmandb
set dbid=137714854
list backup;
Step 4 :- pick any backup identify tag information and restore control file
run {
 allocate channel 'dev_0' type 'sbt_tape'
 parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oradb, OB2BARLIST=ONLINE_my-pc_oradb)';
 restore controlfile from tag 'TAG20130701T001553';
}
Step 5 :- startup mount
Step 6 :- Restore database & Recover database :-
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oradb, OB2BARLIST=ONLINE_my-pc_oradb)';
sql 'alter database rename file "+data/dbf/PROD/redo01.log" to "/data/scratch/dbf/PROD/redo01.log"';
sql 'alter database rename file "+/data/dbf/PROD/redo02.log to  "/data/scratch/dbf/PROD/redo02.log"';
sql 'alter database rename file "+/data/dbf/PROD/redo03.log to "/data/scratch/dbf/PROD/redo03.log"';
set NEWNAME for datafile 1 to '/oradb_restore/data/oradb/datafile/system.260.800209871’;
set NEWNAME for datafile 2 to '/oradb_restore/data/oradb/datafile/sysaux.261.800209873’;
set NEWNAME for datafile 3 to '/oradb_restore/data/oradb/datafile/undotbs1.262.800209875’;
set NEWNAME for datafile 4 to '/oradb_restore/data/oradb/datafile/users.264.800209883’;
set NEWNAME for datafile 5 to '/oradb_restore/data/oradb/datafile/oradb_data_ts.267.800305713’;
set NEWNAME for datafile 6 to '/oradb_restore/data/oradb/datafile/oradb_data_ts.268.800305741’;
set NEWNAME for datafile 7 to '/oradb_restore/data/oradb/datafile/oradb_data_ts.269.800305769’;
set NEWNAME for datafile 8 to '/oradb_restore/data/oradb/datafile/oradb_idx_ts.270.800311977’;
set NEWNAME for datafile 9 to '/oradb_restore/data/oradb/datafile/oradb_idx_ts.271.800312003’;
set NEWNAME for datafile 9 to '/oradb_restore/data/oradb/datafile/undotbs1.278.806582679’;
restore database from tag 'TAG20130701T001553';
switch datafile all;
recover database;
}

Step 7 :-
alter database open resetlogs;
If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:
_no_recovery_through_resetlogs=TRUE

Step 8 :- Disable other threads i.e. used for RAC other instances and drop redo log groups
select THREAD#, STATUS, ENABLED from v$thread;
select group# from v$log where THREAD#=2;
alter database disable thread 2;
alter database clear unarchived logfile group 2;  
alter database drop logfile group 2 ;

Step 9 :- You can drop Undo database space for other instances
show parameters undo
select tablespace_name from dba_tablespaces where contents='UNDO';
drop tablespace UNDOTBS2 including contents and datafiles;

Step 10 :- Add temp file
alter tablespace temp add tempfile '/oradb_restore/data/oradb/tempfile/temp02.dat' size 200m reuse autoextend on next 100m  maxsize 400m;
 

How to read data from oracle datafiles?

hexdump t1_luis1.dbf -C | grep 'S'
hexdump -C file1

How to prevent Parallel slave process from spawning to other nodes?

In a RAC setup when parallel slave process spawns to other node it adversely impacts performance because of "GC BUFFER" wait events.

In order to prevent that you can use below :-

ON node 1 :-
ALTER SYSTEM SET instance_groups = 'rac01';
ALTER SESSION SET parallel_instance_groups = 'rac01';

ON node 2 :-
ALTER SYSTEM SET instance_groups = 'rac02';
ALTER SESSION SET parallel_instance_groups = 'rac02';

ON node 3 :-
ALTER SYSTEM SET instance_groups = 'rac03';
ALTER SESSION SET parallel_instance_groups = 'rac03';

ON node 4 :-
ALTER SYSTEM SET instance_groups = 'rac04';
ALTER SESSION SET parallel_instance_groups = 'rac04';

Oracle PSU VS CPU patch???

1)CPU(Critical Patch Update):(includes all critical patches)
Critical Patch Update, quarterly delivered by Oracle to fix security issues.

2)PSU(Patch Set Update): (includes only "recommended" and "proactive" patches)
PatchSet Update, also quarterly delivered by Oracle,it includes CPU and a bunch of other one-off patches.

What is difference between paging & swapping???

Swapping: Whole process is moved from the swap device to the main memory for execution. And vise-versa post execution. Process size must be less than or equal to the available main memory. It is easier to implement and overhead to the system. Swapping is are rigid, paging more flexible.

Paging: Only the required memory pages (data areas, but not instructions which are available from local or remote file systems) are moved to main memory from the paging disk device for execution.
Process size does not matter. Gives the concept of the virtual memory.  It provides greater flexibility in mapping the virtual address space into the physical memory of the machine.  Allows more number of processes to fit in the main memory simultaneously. Allows the greater process size than the available physical memory. Demand paging systems handle the memory more flexibly.

How to resolve Fails-ORA-12545 TNS: Host or Object Does not Exist ???

Check output of following :-
1) If TNS entry is using hostname or Physical IP address? It should use Virtual IP address
2) If local_listener parameter is using hostname or VIP ?? show parameters local_listener --> It should use VIP (not hostname or not physical IP or not virtual hostname should strictly use Virtual IP)
3) If listener configuration is using anything VIP or not???--> It should use VIP (not hostname or not physical IP or not virtual hostname should strictly use Virtual IP)

What is data pump master table???

Oracle table that is used to store information about the Data Pump job
 –Export/import parameters
 –Current status
 –Object information
Master table can be accessed in SQLPLUS
Not deleted if job is stopped
Deleted after job is complete or killed

How to find out cpu cores from within oracle???

SELECT
 MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)) CPUS,
 MAX(DECODE(STAT_NAME,'NUM_CPU_CORES',VALUE,1)) CPU_CORES,
 max(decode(STAT_NAME,'PHYSICAL_MEMORY_BYTES',round(VALUE/1024/1204),1)) Memory_MB
FROM V$OSSTAT;

BCV Hot backup with RMAN incremental backup

Yes it is possible to restore database using combination of BCV hot backup and RMAN incremental backup. I tried below in my test environment and it worked.

summary :-
=====================================================================================================================================
take SCN number before
alter database begin backup;

Take hot backup
next day take rman incremental backup from above SCN
after backup copy control file

restore hot backup
restore control file
startup database
in RMAN :-
recover database noredo;

when asked for recovery for system1.dbf

alter database recover database;

if you get below error :-
SQL> alter database recover database;
alter database recover database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 38 doesn't match 5
ORA-00312: online log 2 thread 1: 'D:\ORCL\ORADATA\ORCL\REDO02.LOG'
try :-
ALTER DATABASE CLEAR LOGFILE GROUP 2;
if it fails
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

How to use dbms_compression.get_compression_ratio in 11g???

** Ensure you set compatible parameter to current version. i.e. atleast 11.*.*.*

create or replace procedure get_esti_comp(level number)
is
begin
declare BLKCNT_CMP BINARY_INTEGER;
BLKCNT_UNCMP BINARY_INTEGER;
ROW_CMP BINARY_INTEGER;
ROW_UNCMP BINARY_INTEGER;
CMP_RATIO NUMBER;
COMPTYPE_STR VARCHAR2(100);
ORG_TYPE NUMBER;
begin
case level
when 1 then org_type := DBMS_COMPRESSION.COMP_FOR_QUERY_LOW;
when 2 then org_type := DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH;
when 3 then org_type := DBMS_COMPRESSION.COMP_FOR_ARCHIVE_LOW;
when 4 then org_type := DBMS_COMPRESSION.COMP_FOR_ARCHIVE_HIGH;
else org_type := DBMS_COMPRESSION.COMP_FOR_QUERY_LOW;
end case;
dbms_compression.get_compression_ratio('TABLESPACE_TBS','SCOTT','INVENTORY','',org_type, BLKCNT_CMP,BLKCNT_UNCMP,ROW_CMP,ROW_UNCMP, CMP_RATIO, COMPTYPE_STR);
dbms_output.put_line('COMPRESSED_TYPE = '||COMPTYPE_STR);
dbms_output.put_line('COMPRESSED_BLOCKS = '||BLKCNT_CMP);
dbms_output.put_line('UNCOMPRESSED_BLOCKS = '||BLKCNT_UNCMP);
dbms_output.put_line('COMPRESSED_ROWS = '||ROW_CMP);
dbms_output.put_line('UNCOMPRESSED_ROWS = '||ROW_UNCMP);
dbms_output.put_line('COMPRESSION_RATIO = '||CMP_RATIO);
end;
end;
/
SQL> execute get_ehcc_cr(1);
SQL> execute get_ehcc_cr(2);
SQL> execute get_ehcc_cr(3);
SQL> execute get_ehcc_cr(4);
Sample output :-

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
COMPRESSED_TYPE = "Compress For Query Low"
COMPRESSED_BLOCKS = 8606
UNCOMPRESSED_BLOCKS = 27595
COMPRESSED_ROWS = 116
UNCOMPRESSED_ROWS = 39
COMPRESSION_RATIO = 3.2
PL/SQL procedure successfully completed.

How to get server IP or host IP from ORACLE???

select  UTL_INADDR.get_host_address('mypc1') from dual;

SELECT UTL_INADDR.get_host_name('192.168.0.1') FROM dual;

How to find out server operating system from ORACLE???

select dbms_utility.port_string from dual;

How to trace errorsin oracle????

alter system set events '1031 trace name errorstack level 8'

alter system set events '1031 trace name context off';

How to print special character in sqlplus?

SET ESCAPE ON
select 'TEST1\&TEST2' from dual;


SET ESCAPE ON
UPDATE TABLE1 SET FIELD1='TEST1\&TEST2';

cron job standard output and error out

If the standard-output and standard-errors are not redirected anywhere, the STANDARD O/p and ERROR will go in mqueue . This will cause huge activity in /var/spool/mqueue and is one of the reason of slowness in the server due to huge activity on /var/spool/mqueue.
Example :-

05 03 * * *  /SECUPW/UPW_SCRIPT_AREA/zip_Log_proc.sh 2>/dev/null 1>/dev/null

How to create user if data vault is installed????

Steps followed :-

Connect as Data Vault manager and create user :-

create user OTH_USR identified by othusr_123 TEMPORARY TABLESPACE temp;

connect sys as sysdba :-

grant create session, unlimited tablespace to OTH_USR;

ORACLE RAC dos and donts?????

Primary purpose of RAC is to provide continuous availability (NOT HIGH AVAILABILITY) additionally it also provides the potential for faster performance by allowing potentially more CPUs to be employed.

What it means ?

For OLTP :-
In an OLTP application, SQL(s) are usually very efficient, with queries accessing data via a very selective index; most of data blocks are cached at either the Oracle or SAN level and hence application is CPU-LIMITED. With more CPU(s) available in RAC environment OLTP systems will gain more performance.

For Batch Jobs :-
Most batch jobs tend to select large chunks of data and generally heavy on disk i/o, or I/O LIMITED. Hence, it is trickier to analyse and more trickier to benefit from RAC using more number of threads (different from parallelism, which is different thing.)

Understanding potential performance road blocks :-

Queries Of Objects Spanning Nodes May Degrade A Little :-
Here’s why: Every time a session accesses an Oracle block, RAC must ensure that the session is provided the correct version of the block—which may actually reside on a different node. When this occurs, there is a slight, but not negligible delay, as the block is transferred via the cache fusion technology. Additionally, if the sending node has modified the block, but not yet written the transaction to the redo log, there is a further delay whilst the redo block is written.

Undo Considerations :-
When RAC nodes are variously updating, inserting, or querying the same data block it adds complication. Any node doing a query of these active blocks must undo the uncommitted changes—wherever they originated from, local or remote. Thus, the requesting node must request, wait for, combine, and apply undo changes from multiple nodes. Shipping of undo blocks can thus potentially add significant delays.

Full Table Scans Issue :-
Delays due to full table scans can be exacerbated in RAC. When a block is not found cached in the local node, Oracle first attempts to find the block in a remote node (before resorting to a disk read.) Potentially, these extra calls lead to longer delays.

Important :- If you plan to use RAC database as source for GOLDEN GATE replication purpose please note that XA and DISTRIBUTED transactions are not
supported.

How to find out top wait events for active sessions????

col sid format 999999
col username format a12
col event format a45
col Sec_wait format 999999
col total_waits format 999999
col time_wait_sec format 999999

select s.sid,s.username, se.event, s.seconds_in_wait Sec_Wait, total_waits,time_waited/100 time_wait_sec from v$session_event se, v$session s where s.sid=se.sid and s.status = 'ACTIVE' and s.username is not null and s.seconds_in_wait > 2 and se.time_waited > 100 and se.event not in ('SQL*Net message from client','SQL*Net message to client','events in waitclass Other') order by s.seconds_in_wait, s.sid,s.serial#, se.time_waited desc, se.total_waits desc;

How to setup secure database connection???

To use encryption for connection modify sqlnet.ora and add below lines :-

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5)
SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.CRYPTO_CHECKSUM_CLIENT = required

To confirm if encryption is working enable client level trace with value higher than 10 by adding below lines in sqlnet.ora

trace_level_client = 16
trace_unique_client = on
trace_directory_client = D:\oracle\network\trace

Check trace file :-

If encryption is active :-

[24-OCT-2006 10:00:32:087] na_tns: encryption is active, using RC4_256

If encryption is not active it will instead look like:

[24-OCT-2006 10:07:34:707] na_tns: encryption is not active

In my trace extract from my trace file :-

[28-MAR-2012 18:12:20:638] naeecom: The server chose the 'RC4_256' encryption algorithm
[28-MAR-2012 18:12:20:638] naeecom: exit
[28-MAR-2012 18:12:20:638] naeccom: entry
[28-MAR-2012 18:12:20:638] naeccom: The server chose the 'MD5' crypto-checksumming algorithm
[28-MAR-2012 18:12:20:638] naeccom: exit
[28-MAR-2012 18:12:20:638] na_tns: entry
[28-MAR-2012 18:12:20:638] na_tns: Secure Network Services is available.
[28-MAR-2012 18:12:20:638] nau_adi: entry
[28-MAR-2012 18:12:20:638] nau_adi: exit
[28-MAR-2012 18:12:20:638] na_tns:  authentication is not active
[28-MAR-2012 18:12:20:638] na_tns:  encryption is active, using RC4_256
[28-MAR-2012 18:12:20:638] na_tns:  crypto-checksumming is active, using MD5
[28-MAR-2012 18:12:20:638] na_tns: exit
[28-MAR-2012 18:12:20:638] na_coco: exit
[28-MAR-2012 18:12:20:638] naconnect: exit
[28-MAR-2012 18:12:20:638] nsnafinishconn: entry
[28-MAR-2012 18:12:20:638] nagetctxinfo: entry
[28-MAR-2012 18:12:20:638] nagetctxinfo: exit
[28-MAR-2012 18:12:20:638] nsnafinishconn: normal exit
[28-MAR-2012 18:12:20:638] nsnadoconn: normal exit
[28-MAR-2012 18:12:20:638] nsnaconn: normal exit
[28-MAR-2012 18:12:20:638] nscall: normal exit
[28-MAR-2012 18:12:20:638] niotns: niotns: passing ns handle back up...

How to get server IP or host IP from ORACLE???

select  UTL_INADDR.get_host_address('odsserver') from dual;

SELECT UTL_INADDR.get_host_name('129.52.168.172') FROM dual;

for ORACLE 11g with data vault :-

SELECT GET_FACTOR('DATABASE_IP') FROM DUAL;

How to list events set???

SQL>oradebug setmypid
Statement processed.
SQL>oradebug tracefile_name
Statement processed.
SQL>oradebug eventdump system 600 trace name systemstate level 266
10411 trace name context forever, level 1

Database Vault step by step example....

Violation of following should be captured :-

1) CDR_ADMIN should be able to perform DDL only but not DML on CDR_16_12_2011
2) CDR_USR should be able to perform DML on CDR_16_12_2011
3) OTH_USR should not be able to perform DML on CDR_16_12_2011

Steps followed :-

create user CDR_ADMIN identified by CDR_ADMIN TEMPORARY TABLESPACE temp;
grant create session, create table, unlimited tablespace to CDR_ADMIN;

create user CDR_USR identified by CDR_USR TEMPORARY TABLESPACE temp;
grant create session, unlimited tablespace to CDR_USR;

create user OTH_USR identified by OTH_USR TEMPORARY TABLESPACE temp;
grant create session, unlimited tablespace to OTH_USR;

a) Prepare REALM :-

BEGIN
DVSYS.DBMS_MACADM.CREATE_REALM(
realm_name =>'TEST_CDR_REALM',
description =>'Realm to protect tables cdr_data_1, app_data_1 in CDR_ADMIN schema' ,
enabled =>DBMS_MACUTL.G_YES, --realm enabled
audit_options =>DBMS_MACUTL.G_REALM_AUDIT_FAIL); --audit enabled
END;
/

b) Add tables to above realm

BEGIN
DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name =>'TEST_CDR_REALM',
object_owner => 'CDR_ADMIN',
object_name => '%',
object_type =>'TABLE');
END;
/


c) Add users authorized to use the objects in the realm

BEGIN

DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
   realm_name =>'TEST_CDR_REALM',
   grantee =>'CDR_USR',
   auth_options =>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

-- set as participant

DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
   realm_name =>'TEST_CDR_REALM',
   grantee =>'OTH_USR',
   auth_options =>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

-- set as participant

DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
   realm_name =>'TEST_CDR_REALM',
   grantee =>'CDR_ADMIN',
   auth_options =>DBMS_MACUTL.G_REALM_AUTH_OWNER);

-- set as owner, to be able to grant, revoke (only owners can grant, revoke on realm protected objects)

END;
/

d) Create rule set for CDR_USR

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'RULE_SET_CDR_USR',
description => 'Rule Set enabled for CDR_USR',
enabled => DVSYS.DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message => '',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL
);

END;
/

e) create rule to check username CDR_USR

BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_CDR_USR',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''CDR_USR'''
);
END;
/


f) Add rule to rule set

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'RULE_SET_CDR_USR',
rule_name => 'Rule_CDR_USR'
);
END;
/

g) Create command rules for SELECT, INSERT, UPDATE AND DELETE (DML for Tables) for CDR_USR :-

BEGIN
   DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'SELECT',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'INSERT',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'UPDATE',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command => 'DELETE',
   rule_set_name => 'RULE_SET_CDR_USR',
   object_owner => 'CDR_ADMIN',
   object_name => '%',
   enabled => DVSYS.DBMS_MACUTL.G_YES
);

END;
/

h) Create rules for CREATE TABLE, ALTER TABLE, DROP TABLE AND TRUNCATE TABLE (DDL for Tables) for CDR_ADMIN :-

BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'RULE_SET_CDR_ADMIN',
description => 'Rule Set enabled for the CDR_ADMIN user',
enabled => DVSYS.DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message => '',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL
);
END;
/


i) Create rule for rule set - check if username is CDR_ADMIN

BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_CDR_ADMIN',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''CDR_ADMIN'''
);
END;
/

j) Add rule to rule set.

BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'RULE_SET_CDR_ADMIN',
rule_name => 'Rule_CDR_ADMIN'
);
END;
/

k) add command to rule set.

BEGIN
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'CREATE TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'ALTER TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'DROP TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'TRUNCATE TABLE',
rule_set_name => 'RULE_SET_CDR_ADMIN',
object_owner => 'CDR_ADMIN',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
END;
/


set lines 132 pages 2300
col os_username format a15
col username format a15
col userhost format a15
col ACTION_COMMAND format a45
col audit_option format a10
alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

select audit_option,os_username,username,userhost,timestamp,action_command from dvsys.audit_trail$;

How to trace goldengate extract???

1. add trace/trace2 parameter to the paramfile
e.g.,
trace ./my_trace1
trace2 ./my_trace2

2. under OGG installation home directory, create an xml file named gglog-<EXT_OR_REP_NAME>.xml
for example, for replicate R1, the file name is gglog-R1.xml.
gglog-<EXT_OR_REP_NAME>.xml should look like this.... :-
<?xml version="1.0"?>
<configuration reset="true">
<appender name="myTraceFile" class="FileAppender">
<param name="File" value="myTrace-%I.log"/>
<param name="Append" value="true"/>
<layout class="PatternLayout"/>
</appender>
<logger name="ggstd.ptrace">
<level value="all"/>
</logger>
</configuration>

3. start (or restart) the extract/replicat
4. the trace will be generated.
5. after collecting the trace, remove the xml file and trace parameters. then restart the extract or replicat.

How to pass parameters to view ????

1. First create view like follow
---------------------------------
CREATE OR REPLACE VIEW scott.test_parameter_view_date
AS
SELECT *
FROM emp
WHERE hiredate = TO_DATE (USERENV ('client_info'), 'dd-mon-yyyy');
--to_char(timstamp,'ddmmrrrr') =userenv('client_info')
--To_Date(SYS_CONTEXT ('userenv','client_info'),'dd-mon-yyyy')

2. then execute the following ...
--------------------------------
exec dbms_application_info.set_client_info('22-Feb-1981');

3. Done your view , now select the view
----------------------------------------
SELECT * FROM scott.test_parameter_view_date;

Hot backup if tablespace is in nologging mode???

We can surely put the NOLOGGING tablespace is BACKUP mode and make a operating system copy of the file in question.

However, if there are any NOLOGGING changes made to the tablespace after the backup was taken and if recovery is issued beyond this time, media recovery will soft-corrupt all blocks that are
affected by the NOLOGGING changes.

GoldenGate performance tuning

Based on my experience I would recommend following :-
a) Always have only one extract process running
b) Never put any conditions in extract
c) If you have any conditions put it in pump process
d) Split one table per replicat (if it has more than 500K DML(s) in a day)
e) Split one table into multiple replicate (if it has huge DML(s) in a day) (increase INITRANS for table and indexes in that case)

How to find all users in a group???

For :- staff group
lsgroup -a users staff |cut -f2 -d= |tr -s ',' '\n'
For :- unix12 group
lsgroup -a users unixl2 |cut -f2 -d= |tr -s ',' '\n'

How to find all users group info??? userwise group / groupwise user

#!/bin/ksh
for LOOP in $(cat /etc/passwd|cut -f1,4 -d':')
do
  USRNAM=$(echo ${LOOP} |cut -f1 -d':')
  GRPID=$(echo ${LOOP} |cut -f2 -d':')
  GRPNAM=$(cat /etc/group |grep ":${GRPID}:"|cut -f1 -d':')
  echo "User \"${USRNAM}\" belongs to \"${GRPNAM}\" group"
done

How to resolve "cannot restore segment prot after reloc: Permission denied"?

[oracle@MyUnixBox admin]$ sqlplus "/ as sysdba"
sqlplus: error while loading shared libraries: /opt/ora11g/lib/libsqlplus.so: cannot restore segment prot after reloc: Permission denied
Error post relink all :-

[oracle@MyUnixBox admin]$ sqlplus "/ as sysdba"
sqlplus: error while loading shared libraries: /opt/ora11g/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

[oracle@MyUnixBox admin]$ lsnrctl status
opt/ora11g/bin/tnslsnr: error while loading shared libraries: /opt/ora11g/lib/libons.so: cannot restore segment prot after reloc: Permission denied
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 32: Broken pipe

Solution :-

as oralce :- relink all

as root :-

[root@MyUnixBox ~]# getenforce
Enforcing
[root@MyUnixBox ~]# setenforce 0
[root@MyUnixBox ~]# getenforce
Permissive

as oracle :-

[root@MyUnixBox ~]# /usr/sbin/semanage fcontext -a -t textrel_shlib_t $ORACLE_HOME/lib/libclntsh.so.11.1
[root@MyUnixBox ~]# /sbin/restorecon /opt/ora11g/lib/libclntsh.so.11.1

How to write multiple case in select statement with alias???

select username,expiry_date,
  (CASE
    WHEN expiry_date IS NOT NULL and expiry_date > sysdate then 'OPEN'
    WHEN expiry_date IS NOT NULL and expiry_date < sysdate then 'LOCKED'
    WHEN expiry_date IS NULL then account_status
  END) "account_st",account_status
from dba_users where username in ('ABC','XYZ');

Account_Status Is Open after Expiry Date in DBA_USERS??

This is the expected behaviour.
The data dictionary doesn't update the account information until the user attempts to logon.

How to create profile??

CREATE PROFILE TMPPROF LIMIT
PASSWORD_LIFE_TIME 1/24 -- 1 hour
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 10/24 -- 10 hours
PASSWORD_GRACE_TIME 2/24; -- 2 hours

Meaning of different status in DBA_USERS

Basically there are only three different status :-
Open :- Account is open
Expired :- Password has expired and needs to be changed
Locked :- Explicitly locked by DBA or locked because of wrong password being enterd for login for "N" number of times.

Account_status in DBA_USERS

OPEN :- No DBA action required

     Open Account

LOCKED :- DBA needs to unlock the account

     An account locked by a DBA

EXPIRED(GRACE) & LOCKED :- DBA needs to unlock the account

     This is caused grace period defined by PASSWORD_GRACE_TIME and the administrator has locked the account.

EXPIRED & LOCKED :- DBA needs to unlock the account

     This is caused by the password not being changed before the PASSWORD_LIFE_TIME profile hence limit is exceeded and the administrator has locked the account.

LOCKED(TIMED) :- No DBA action required based on profile

     Locked automatically after a number of failed login attempts. When a PASSWORD_LOCK_TIME is defined, the account unlocks automatically after the set time.

EXPIRED(GRACE) & LOCKED(TIMED) :- No DBA action required based on profile :-

     This is caused grace period defined by PASSWORD_GRACE_TIME and locked automatically after number of failed logins.

EXPIRED(GRACE) :- No DBA action required based on profile

     During the grace period defined by PASSWORD_GRACE_TIME in profile a warning is issued at time of login and the ACCOUNT_STATUS is set to EXPIRED(GRACE).

EXPIRED & LOCKED(TIMED) :- No DBA action required based on profile

     This is caused by the password not being changed before the PASSWORD_LIFE_TIME profile limit is exceeded and account is locked after number of failed login attempts.

EXPIRED :- No DBA action required based on profile

User is prompted for a new password upon the next login attempt.

How to resolve below errors in ASM???

Tue Jul 12 14:52:57 2011
Errors in file /oracle/admin/ADMOSD/bdump/ADMOSD1_dbw0_786540.trc:
ORA-01157: cannot identify/lock data file 150 - see DBWR trace file
ORA-01110: data file 150: '+ADM_OSD_DATA_03_NEW/ADMOSD/datafile/t_large_data_2.256.741582251'
ORA-17503: ksfdopn:2 Failed to open file +ADM_OSD_DATA_03_NEW/ADMOSD/datafile/t_large_data_2.256.741582251
ORA-15001: diskgroup "ADM_OSD_DATA_03_NEW" does not exist or is not mounted
ORA-15001: diskgroup "ADM_OSD_DATA_03_NEW" does not exist or is not mounted

Check status of disk and disk group:-

select name, state, type, offline_disks from v$asm_diskgroup;

select name, mount_status, mode_status, header_status, read_errs, write_errs from v$asm_disk;

Manually Mount diskgroup in ASM :-

alter diskgroup ADM_OSD_DATA_03_NEW mount

If you are getting errors :-

alter diskgroup AMD_OSD_DATA_03_NEW check all repair

How to resolve OGG-01738 BOUNDED RECOVERY: CHECKPOINT:??

Error :-

2011-02-06 09:15:46 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p7268_Redo Thread 1:
Solution :-

START EXTRACT EXT2 BRRESET

How to find out used database features?

Set feedback off
Set linesize 122
Col name             format a45     heading "Feature"
Col version          format a10     heading "Version"
Col detected_usages  format 999,990 heading "Detected|usages"
Col currently_used   format a06     heading "Curr.|used?"
Col first_usage_date format a10     heading "First use"
Col last_usage_date  format a10     heading "Last use"
Col nop noprint
Break on nop skip 1 on name
Select decode(detected_usages,0,2,1) nop,
       name, version, detected_usages, currently_used,
       to_char(first_usage_date,'DD/MM/YYYY') first_usage_date,
       to_char(last_usage_date,'DD/MM/YYYY') last_usage_date
from dba_feature_usage_statistics
order by nop, 1, 2

How to enable supplement logging at table or column level?

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG GROUP tab1_grp1 (col1,col2) ALWAYS;
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG GROUP tab1_grp1;

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

How find out top 10 file with IO wait?

select * from (select b.file_name,
       a.singleblkrds,
       a.singleblkrdtim,
       a.singleblkrdtim/a.singleblkrds avg_wait
from   v$filestat a, dba_data_files b
where  a.file# = b.file_id  
and    a.singleblkrds > 0
order by avg_wait desc) where rownum < 11;

How to parallel DDL statement?

ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

How to fix invalid "oracle database packages and types" ?

1)  exec dbms_registry.valid('CATPROC');
2)  @utlrp.sql
3)  SHUTDOWN IMEMDIATE
4)  STARTUP UPGRADE
5)  @catalog.sql
6)  @catproc.sql
7)  @utlrp.sql
8)  SHUTDOWN IMMEDIATE

9)  STARTUP

10) exec dbms_registry.valid('CATPROC');

OR

10)

set serverout on feedback on

BEGIN
dbms_registry.update_schema_list('CATPROC',   dbms_registry.schema_list_t('SYSTEM', 'OUTLN', 'DBSNMP'));
dbms_registry.loaded('CATPROC');
dbms_registry_sys.validate_catproc;
dbms_registry_sys.validate_catalog;
END;
/

Find out oldest OS processes?

AIX :-
ps -A -o user,pid,etime,group,args | grep "-" | sort -rn +2 | head -50

HPUX :-
UNIX95= ps -e -o ruser,pid,ppid,etime,args | grep "-" | sort -rn +3 | head -50

How to check warnings at time of compilation ? (plsql warnings)

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

set serverout on feedback on verify on

select PLSQL_WARNINGS from DBA_PLSQL_OBJECT_SETTINGS where where owner='ABC' and name='XYZ';

How to resolve GGS ERROR Lock currently held by process id ??

Error on source :-

2010-12-13 12:23:34  GGS ERROR       112  Oracle GoldenGate Capture for Oracle, dp4.prm:  There is a problem in network communication, a remote file problem, encryption keys for target
and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /oracleGG/dirdat/r4000081, reply received is Unable to lock file "/oracleGG/dirdat/r4000081" (error 13,
Permission denied). Lock currently held by process id (PID) 1822770).

Resolution on target :-

mailto:oracle@DOXODS1:/oracleGG/dirdat%3Eps -ef | grep 1822770
  oracle 1822770 2003026   0 10:10:24      -  0:00 ./server -p 8192 -k -l /oracleGG/ggserr.log

How to find out objectwise undo usage ???

set lines 132 pages 2300
col object_name format a30
col username format a13
col name format a14
col EXTENTS format 9999999
col SID_SERIAL format a15
col sql_text format a40 wrap
col undo_MB format 99,999.99
select object_name,sum(undo_mb) from (select  s.sid||','||s.serial# SID_SERIAL, s.username, round(t.used_ublk * TO_NUMBER(x.value)/1024/1024,2) undo_MB, sq.sql_text ,do.object_name
        from    v$transaction   t
               ,v$session       s
               ,v$rollname      rn
               ,v$rollstat      rs
               ,v$locked_object lo
               ,dba_objects     do
               ,v$sql           sq
               ,v$parameter   x
        where  t.addr        = s.taddr
        and    t.xidusn      = rn.usn
        and    rn.usn        = rs.usn
        and    t.xidusn      = lo.xidusn(+)
        and    do.object_id  = lo.object_id
        and    s.sql_id      = sq.sql_id(+)
        AND    x.name  = 'db_block_size'
        order by used_ublk desc) group by object_name order by 2 desc;

How to copy file to and from ASM?

create or replace directory SOURCE_DIR as '+DSKGRP1/ORA10G/ORADATA';

create or replace directory ORACLE_DEST as '/oracle/ORA10G/ORADATA';

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'system.721.728614431',
destination_directory_object => 'ORACLE_DEST',
destination_file_name => 'system.dbf');
END;
/

Golden gate ALTER EXTRACT ETROLLOVER in RAC environment

When running golden gate extract in RAC environment extract processes may abend with message asking to rollover extract because of SCN issue, following THREADOPTIONS parameters can be used in extract parameter file

TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD oracle

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 45000 IOLATENCY 90000

COMMENT MAXCOMMITPROPAGATIONDELAY must be greater than 0 and less than 90000 milliseconds.

COMMENT IOLATENCY must be between 0 and 180000 milliseconds (3 minutes).

PLEASE NOTE :- You will still have to perform ETROLLOVER for starting extract.

Salvage database / recover from incomplete fuzzy backup

PLEASE NOTE :-

NEVER TRY THIS ON PRODUCTION DATABASE

DO NOT TRY THIS without consultation with Support on any database.

THERE IS NO GURANTEE OF DATA RECOVERY.

Case :- I received below errors on my home use practice database when some cleanup script deleted "redo-log" files and archive files. And to add to it last database shutdown was "shutdown abort" :-(

 ------------------------------------------ Error Message 1) ------------------------------------------

WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
.....

 ------------------------------------------ Solution to error 1) ------------------------------------------

_ALLOW_RESETLOGS_CORRUPTION= TRUE
_ALLOW_READ_ONLY_CORRUPTION=TRUE

 ------------------------------------------ Error Message 2) ------------------------------------------

ALTER DATABASE OPEN
ORA-1589 signalled during: ALTER DATABASE OPEN...
Sat May 28 19:28:56 2011

 ------------------------------------------ Solution to error 2) ------------------------------------------

alter database open resetlogs

You can ignore below warnings :-

Sat May 28 19:30:10 2011
Errors in file e:\oracle\admin\tdms\udump\tdms_ora_4008.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'E:\ORACLE\ORADATA\TDMS\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Sat May 28 19:30:50 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
Assigning activation ID 2799243297 (0xa6d91021)
Thread 1 opened at log sequence 1
  Current log# 2 seq# 1 mem# 0: E:\ORACLE\ORADATA\TDMS\REDO02.LOG
Successful open of redo thread 1.

 ------------------------------------------ Error Message 3) ------------------------------------------

ORA-00600: internal error code, arguments: [2662], [0], [160681026], [0], [160681068], [4194313], [], []

 ------------------------------------------ Solution to error 3) ------------------------------------------

Event = "10619 trace name context forever, level 1"

With each shutdown and restart the gap between 3rd and 5th argument should decrease. Shutdown and restart database till error is resolved.

 ------------------------------------------ Error Message 4) ------------------------------------------

ORA-00600: internal error code, arguments: [4193], [4228], [4232], [], [], [], [], []

Solution to error 4) :-

shutdown database

UNDO_MANAGEMENT=MANUAL

startup database

create undo tablespace UNDOTBS01 datafile 'E:\oracle\oradata\tdms\UNDOTBS1.DBF' size 500M;

drop old undo tablespace.

drop tablespace UNDOTBS1 including contents and datafiles;

shutdown database

UNDO_MANAGEMENT=AUTO
undo_tablespace = UNDOTBS01

startup database

How to find out processwise open files ??? (in AIX) (in HPUX)

lsof | perl -lane '$x{"$F[0]:$F[1]"}++; END { print "$x{$_}\t$_" for sort {$x{$a}<=>$x{$b}} keys %x}'| sort -rn +0 | head -25

How to find out userwise open files ??? (in AIX) (in HPUX)

lsof | awk '{ print $3}' | uniq -c

how to see non-printable characters in file name? (AIX)

ls -lb

Multiple listeners with fail over and load balancing?

Listener.ora

LISTENER1 =
(ADDRESS_LIST =
 (ADDRESS= (PROTOCOL= TCP)(Host= mypc1)(Port= 1526))
)
LISTENER2 =
(ADDRESS_LIST =
 (ADDRESS= (PROTOCOL= TCP)(Host= mypc1)(Port= 1536))
)
SID_LIST_LISTENER1=
(
SID_LIST = (SID_DESC = (GLOBAL_DBNAME= oradb1) (ORACLE_HOME= /u01/816) (SID_NAME = oradb1) )
)

SID_LIST_LISTENER2=
(
SID_LIST = (SID_DESC = (GLOBAL_DBNAME= oradb1) (ORACLE_HOME= /u01/816) (SID_NAME = oradb2) )
)


TNS Entry :-

oradb1 =
(
   DESCRIPTION = (FAILOVER=on) (LOAD_BALANCE=on)
   (ADDRESS = (PROTOCOL = TCP)(HOST = mypc1)(PORT =1526))
   (ADDRESS = (PROTOCOL = TCP)(HOST = mypc1)(PORT =1536))
   (CONNECT_DATA = (SERVICE_NAME = oradb1))
)

How to resolve below impdp errors in RAC environment ?

error :
ORA-31693: Table data object %S failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for load

Solution
1. Create the Data Pump directory on a shared storage accessible from all the instances in the RAC cluster.
OR
2. Run the Data Pump import job with parallel=1 (default)
OR
3. Run the Data Pump import job from one instance and shutdown all reaining instances.

How to create library in oracle?

create library ordvirlibS as '${ORACLE_HOME}/lib/libordvir.so';
/
OR
create library ordvirlibS as '${ORACLE_HOME}/lib/libordvir.so' AGENT '<agent_db_link>';
/

Oracle Messaging Gateway setup step by step

1) Install WMQ Client on same machine as Oracle database
 set LD_LIBRARY_PATH in .profile
LD_LIBRARY_PATH = [ORACLE_HOME]/lib:[ORACLE_HOME]/lib32:[ORACLE_HOME]/jlib:/usr/dt/lib:/usr/openwin/lib:[ORACLE_HOME]/ctx/lib

2) Execute $ORACLE_HOME/mgw/admin/catmgw.sql has to be with SYS privileges.

3) Create a user MGWADM with role MGW_ADMINISTRATOR_ROLE
CREATE USER MGWADM IDENTIFIED BY MGWADM;
GRANT CREATE SESSION TO MGWADM;
GRANT CONNECT, RESOURCE TO MGWADM;
GRANT MGW_ADMINISTRATOR_ROLE TO MGWADM;
GRANT MGW_AGENT_ROLE TO MGWADM;

4) Add following entries in Listener.ora. Update the host and port accordingly.
<<DB_NAME>> =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.87.137.228)(PORT=1526))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))
    )
  )
SID_LIST_<<DB_NAME>> =
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=<<DB_NAME>>)
      (ORACLE_HOME= [ORACLE_HOME])
      (SID_NAME=<<DB_NAME>>)
    )
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=[ORACLE_HOME])
      (PROGRAM=extproc)
    )
  )
LISTENER_MGW =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 10.87.137.228) (PORT = 7112))
      (ADDRESS = (PROTOCOL = ipc) (key=extproc2))
    )
  )
SID_LIST_LISTENER_MGW =
  (SID_LIST =
    (SID_DESC=
      (SID_NAME = MGWEXTPROC2)
      (ENVS="EXTPROC_DLLS=[ORACLE_HOME]/lib32/libmgwagent.so,LIBPATH=[ORACLE_HOME]/jdk/jre/bin:[ORACLE_HOME]/jdk/jre/bin/classic:[ORACLE_HOME]/lib32,LDR_CNTRL=USERREGS")
      (ORACLE_HOME=[ORACLE_HOME])
      (PROGRAM = extproc32)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = <<DB_NAME>>)
      (ORACLE_HOME = [ORACLE_HOME])
      (SID_NAME = <<DB_NAME>>)
    )
  )

6) Add following entries in TNSnames.ora. Update the host and port accordingly.

MGW_AGENT =
 (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL=IPC)(KEY=extproc2))
    )
    (CONNECT_DATA =
       (SID=MGWEXTPROC2) (PRESENTATION=RO)
    )
)

AGENTMGW_SERVICE =
 (DESCRIPTION =
    (ADDRESS_LIST=
       (ADDRESS=
          (PROTOCOL=IPC)(KEY=extproc2)))
    (CONNECT_DATA=
       (SID=<<DB_NAME>>)(PRESENTATION=RO)
    )
 )

extproc_connection_data =
    (DESCRIPTION=
         (ADDRESS_LIST =
             (ADDRESS=(PROTOCOL=IPC)(KEY=extproc2))
        )
        (CONNECT_DATA=
            (SID=PLSExtProc)
            (PRESENTATION = RO)
        ) )

7) Edit $ORACLE_HOME/mgw/admin/ mgw.ora file. 
=======================================================
#MGW_PRE_PATH=/.2.0/jdk/jre/1.4.2/bin/client
#set CLASSPATH=/myOracleHome/jdbc/lib/ojdbc14.jar:/myOracleHome/jdk/jre/lib/i18n.jar:/myOracleHome/jdk/jre/lib/rt.jar:/myOracleHome/sqlj/lib/runtime12.jar:/myOracleHome/jlib/orai18n.jar:/myOracleHome/jlib/jta.jar:/myOracleHome/rdbms/jlib/jmscommon.jar:/myOracleHome/rdbms/jlib/aqapi13.jar:/opt/mqm/java/lib/com.ibm.mqjms.jar:/opt/mqm/java/lib/com.ibm.mq.jar:/opt/mqm/java/lib:/opt/mqm/java/lib/connector.jar
CLASSPATH=[ORACLE_HOME]/mgw/jlib/mgw.jar:[ORACLE_HOME]/mgw/classes/mgw.jar:[ORACLE_HOME]/jdbc/lib/ojdbc14.jar:[ORACLE_HOME]/jlib/orai18n.jar:[ORACLE_HOME]/jdk/jre/lib/rt.jar:[ORACLE_HOME]/sqlj/lib/runtime12.jar:[ORACLE_HOME]/oc4j/sqlj/lib/translator.jar:[ORACLE_HOME]/jlib/jta.jar:[ORACLE_HOME]/rdbms/jlib/jmscommon.jar:[ORACLE_HOME]/rdbms/jlib/aqapi13.jar:/usr/mqm/java/lib/com.ibm.mq.jar:/usr/mqm/java/lib/com.ibm.mqbind.jar:/usr/mqm/java/lib/com.ibm.mqjms.jar:/usr/mqm/java/lib/jms.jar:/usr/mqm/java/lib/jndi.jar:/usr/mqm/java/lib/jta.jar:/usr/mqm/java/lib/connector.jar:/usr/mqm/java/lib:/usr/mqm/java/lib/com.ibm.jms.jar

set LIBPATH=[ORACLE_HOME]/jdk/jre/bin:[ORACLE_HOME]/jdk/jre/bin/classic:[ORACLE_HOME]/rdbms/lib32:[ORACLE_HOME]/lib32

set log_level = 3
log_directory=[ORACLE_HOME]/mgw/log
=======================================================
8) Start the listener:-
lsnrctl start LISTENER_MGW

9) Configure and start MGW via following command.
SQLPLUS <MGWADM> / <MGWADM_PASSWORD>
EXEC DBMS_MGWADM.DB_CONNECT_INFO ('<MGWADM>','<MGWADM_PASSWORD>','<DB_TNS_ALIAS>');
EXEC DBMS_MGWADM.STARTUP;
SELECT AGENT_STATUS FROM MGW_GATEWAY;
EXEC DBMS_MGWADM.SHUTDOWN;

10) Trouble shooting
If you get below output it’s correct.

$ORACLE_HOME/bin/extproc
Oracle Corporation --- TUESDAY   MAR 01 2011 10:41:22.389
Heterogeneous Agent Release 10.2.0.4.0 - 64bit Production

$ORACLE_HOME/bin/extproc32
exec(): 0509-036 Cannot load program /oracle/ORAHOME/bin/extproc32 because of the following errors:
        0509-150   Dependent module /oracle/ORAHOME/lib/libnnz10.so could not be loaded.
        0509-103   The module has an invalid magic number.

AIX health check script

#!/bin/sh
cd /
echo "====================================="
echo ">>>>> Server Name, Date, UPtime <<<<<"
echo "====================================="
echo "Date :- `date`"
echo " "
echo "Host Name :- `hostname`"
echo " "
echo " OS Version"
oslevel -g
echo " "
echo " UPTIME :- "
uptime
echo " "
echo " "
echo "====================================="
echo ">>>>>    CPU and Memory Info.   <<<<<"
echo "====================================="
echo " "
echo "**************************************************************************************"
echo "          CPU :- `lsdev | grep Processor | wc -l`"
echo " "
echo "       Memory :- `lsattr -El mem0 | tail -1`"
echo "**************************************************************************************"
echo " "
echo " "
echo "====================================="
echo ">>>>> Important Kernel Params.  <<<<<"
echo "====================================="
echo " "
echo "****************************************"
echo " "
lsattr -El aio0
echo " "
echo "****************************************"
echo " "
echo "****************************************"
echo " "
lsattr -E -l sys0
echo " "
echo "****************************************"
echo " "
echo "****************************************"
echo " "
ulimit -a
echo " "
echo "****************************************"
echo " "
echo " "
echo "====================================="
echo ">>>>>   CPU Usage Information   <<<<<"
echo "====================================="
echo " "
vmstat -w 3 3 | tail -1 | awk '{print "CPU Used :- "($14+$15)"%"}'
echo " "
echo " "
echo "====================================="
echo ">>>>> Memory Usage Information  <<<<<"
echo "====================================="
um=`svmon -G | head -2|tail -1| awk {'print $3'}`
um=`expr $um / 256`
tm=`lsattr -El sys0 -a realmem | awk {'print $2'}`
tm=`expr $tm / 1024`
fm=`expr $tm - $um`
ump=`expr $um \* 100`
ump=`expr $ump / $tm`
echo " "
echo "Memory Used :- "$ump"%"
echo " "
echo "----------------------";
echo "Memory Information\n";
echo "total memory = $tm MB"
echo "free memory = $fm MB"
echo "used memory = $um MB"
echo "-----------------------\n";
echo " "
echo " "
echo "====================================="
echo ">>>>>   Page file usage info.   <<<<<"
echo "====================================="
echo " "
lsps -s
echo " "
echo " "
echo "====================================="
echo ">>>>>No. of processes on SERVER <<<<<"
echo "====================================="
echo " "
echo "Total processes on server :- `ps -ef | wc -l`"
echo " "
echo " "
for i in `ps -ef -o args | grep pmon | grep -v grep | cut -d_ -f3`
do
 echo "Processes for $i :- `ps -ef | grep $i | grep -v grep | wc -l`"
done
echo " "
echo " "
echo "====================================="
echo ">>>>>  Oldest oracle processes  <<<<<"
echo "====================================="
echo " "
echo " Should ideally be DBWR, PMON "
ps -A -o user,pid,etime,group,args | grep " oracle" | grep "-" | sort -rn +2 | head -20
echo " "
echo " "
echo "====================================="
echo ">>>>>      Mail queue size      <<<<<"
echo "====================================="
echo " "
echo "x *" |mailx -u oracle | grep messages |awk '{print $2}'
echo " "
echo " "
echo "====================================="
echo ">>>>>     <defunct> processes   <<<<<"
echo "====================================="
echo " "
ps -el | grep 'Z'
echo " "
echo " "
echo "====================================="
echo ">>>>> Top CPU, Memory, IO pro.  <<<<<"
echo "====================================="
echo " "
echo "Top 10 CPU processes"
echo " "
ps auxgw |head -1; ps auxgw |sort -rn +2 | head -10
echo " "
echo " "
echo "Top 10 MEMORY processes"
echo " "
ps aux | head -1; ps aux | sort -rn +4 | head -10
echo " "
echo " "
echo "Top 10 DISKS by I/O"
echo " "
iostat | head -7; iostat | sed "1d;2d;3d;4d;5d;6d;7d;\$d" | sort -rn +1 | head -10
echo " "
echo " "
echo "====================================="
echo ">>>>>  Mount Point Information  <<<<<"
echo "====================================="
echo " "
df -g | head -1 | awk '{printf("%-38s %-9s %-9s %-7s %-7s %-4s\n",$1,$2,$4,$5,$7,$8)}' ; df -g | sed "1d;\$d" | sort -rn +3 | awk '{printf("%-38s %-9s %-9s %-7s %-7s %-4s\n",$1,$2,$3,$4,$6,$7)}'
echo " "
echo " "
echo "====================================="
echo ">>>>>     Network statistics    <<<<<"
echo "====================================="
echo " "
netstat -rni | head -1 | awk '{printf("%-5s %-6s %-12s %-17s %-10s %-5s %-10s %-5s %-4s\n",$1,$2,$3,$4,$5,$6,$7,$8,$9)}' ; netstat -rni | sed "1d;\$d" | awk '{printf("%-5s %-6s %-12s %-17s %-10s %-5s %-10s %-5s %-4s\n",$1,$2,$3,$4,$5,$6,$7,$8,$9)}'
echo " "
echo " "

DB review script

SET TRIMSPOOL ON;
SET HEADING OFF;
SET FEEDBACK OFF;
SET SERVEROUT ON;
SET PAGESIZE 9999;
SET VERIFY   off;
SET LINES 2500;
COLUMN cpulogfile NEW_VALUE cpulogfile NOPRINT;
SELECT 'D:\db_check\'||Name || '_DB_REPORT_' || TO_CHAR(sysdate,'DDMonYYYY_hh24_mi') || '.log' AS cpulogfile FROM V$DATABASE;
COLUMN OWNER FORMAT A20
COLUMN SEGMENT_NAME FORMAT A35
COLUMN SEGMENT_TYPE FORMAT A20
COLUMN COMP_NAME FORMAT A35
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
spool &cpulogfile
prompt ************************************************************************
prompt                            Database information
prompt ************************************************************************
prompt
set head on
select name,to_char(created,'DD.MM.YYYY HH24:MI:SS') Created_Date, OPEN_MODE, LOG_MODE, DATABASE_ROLE, DBID from v$database;
prompt
prompt
select instance_name,to_char(startup_time,'DD.MM.YYYY HH24:MI:SS'),status,logins,instance_role from v$instance;

prompt
prompt

select comp_name, version,status,modified from dba_registry order by 1;
prompt
prompt
select ' Size in GB :- ' || round(sum(totalspace)/1024) from (
select sum(trunc(bytes/1024/1024)) as TotalSpace from dba_data_files
UNION
select round(BYTES/1024/1024) as TotalSpace from v$TEMPFILE
UNION
select round(sum(bytes/1024/1024)) as TotalSpace from v$log);
select sessions_highwater from v$license;

prompt
prompt
set head off
SELECT 'Database Character Set :- '||a.VALUE || '_' || b.VALUE || '.' || c.VALUE
  FROM (SELECT *
          FROM nls_database_parameters
         WHERE parameter = 'NLS_LANGUAGE') a,
       (SELECT *
          FROM nls_database_parameters
         WHERE parameter = 'NLS_TERRITORY') b,
       (SELECT *
          FROM nls_database_parameters
         WHERE parameter = 'NLS_CHARACTERSET') c;
prompt
prompt
select * from v$version;
prompt
prompt

set head on
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for DMT and LMT
prompt ************************************************************************
prompt
select extent_management,count(1) from dba_tablespaces group by extent_management;
select TABLESPACE_NAME from dba_tablespaces where EXTENT_MANAGEMENT ='DICTIONARY';

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking users with non default TEMP tablespace
prompt ************************************************************************
prompt
select username,TEMPORARY_TABLESPACE from dba_users where TEMPORARY_TABLESPACE not in (select name from v$tablespace  where TS# in (SELECT distinct TS# from v$tempfile)) and TEMPORARY_TABLESPACE not in (select GROUP_NAME from DBA_TABLESPACE_GROUPS);

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for users with weak or default passwords
prompt ************************************************************************
prompt
 SELECT username, password, account_status
   FROM dba_users
  WHERE password IN ('402B659C15EAF6CB', '71E687F036AD56E5', '24ABAB8B06281B4C', 'A13C035631643BA0', '72979A94BAD2AF80', 'D728438E8A5925E0', '2FFDCBB4FD11D9DC', '447B729161192C24', '1B9F1F9A5CB9EB31', 'D4DF7931AB130E37', '2D594E86F93B17A1', '4861C2264FB17936', '970BAA5B81930A40', '135176FFB5BA07C9', 'E4519FCD3A565446', '66A490AEAA61FF72', '10B0C2DA37E11872', 'D5DD57A09A63AA38', '69C27FA786BA774C', '86FDB286770CD4B9', 'B171042374D7E6A2', 'D7C18B3B3F2A4D4B', '4438308EE0CAFB7F', 'FAAD7ADAF48B5F45', '685657E9DC29E185', '49B70B505DF0247F', 'B49C4279EBD8D1A8', '604101D3AACE7E88', '02AB2DB93C952A8F', '203CD8CF183E716C', '1FA22316B703EBDD', '12CFB5AE1D087BA3', '380E3D3AD5CE32D4', '2563EFAAE44E785A', 'E7686462E8CD2F5E', '691C5E7E424B821A', '355CBEC355C10FEF', '80294AE45A46E77B', 'E74B15A3F7A19CA8', 'D4C5016086B2DC6A', '43BE121A2A135FF3', '8A8F025737A9097A', '4DE42795E66117AE', '66BC3FF56063CE97', '57D7CFA12BB5BABF', 'A9A57E819B32A03D', '2905ECA56A830226', '64074AF827F4B74A',
  '41B328CA13F70713', '0B4409DDD5688913', '6CFF570939041278', '3522F32DD32A9706', 'BE29E31B2B0EDA33', '5AC333703DE0DBD4', '639C32A115D2CA57');

prompt
prompt
prompt
prompt ******************************************************************************
prompt Checking for objects not analyzed in 10 days
prompt ******************************************************************************
prompt
select owner,count(1) from dba_tables where LAST_ANALYZED < (sysdate - 10) or last_analyzed is NULL having count(1) > 100 and owner not in ('SYS','SYSTEM') group by owner order by 2 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for invalid SYS, SYSTEM objects
prompt ************************************************************************
prompt
col owner format a10
col OBJECT_NAME format a28
col OBJECT_TYPE format a20
SELECT OWNER, object_name, OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER IN ('SYS','SYSTEM')  and status !='VALID' order by 1;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for MLOG$ larger than 500 MB
prompt ************************************************************************
prompt

col onwer format a15
col segment_name format a30
select owner,segment_name,round(bytes/1024/1024,2) Size_MB from dba_SEGMENTS where segment_name like 'MLOG$%' and round(bytes/1024/1024,0) > 500 ;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for MVIEW not refreshed in last 15 days
prompt ************************************************************************
prompt
select owner,mview_name,to_char(last_refresh_date,'DD.MM.YYYY HH24:MI:SS') Rfrsh_Date from dba_mviews where trunc(last_refresh_date) < trunc(sysdate-15) order by 3;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for non system objects in system tablespace.
prompt ************************************************************************
prompt
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSTEM' AND OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP');
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top wait events in database
prompt ************************************************************************
prompt
col event format a30
Select distinct * from (SELECT a.event,
  a.total_waits,
  a.time_wait_sec,
  ROUND(100 * (a.total_waits / b.sum_waits),2) pct_waits
FROM
  (SELECT event,
    total_waits,
    ROUND((time_waited / 100),2) time_wait_sec,
    total_timeouts,
    ROUND((average_wait / 100),2) average_wait_sec
  FROM v$system_event) a,
  (SELECT SUM(total_waits) sum_waits,
    SUM(total_timeouts) sum_timeouts,
    SUM(ROUND((time_waited / 100),2)) sum_time_waited
  FROM v$system_event
  ) b WHERE a.EVENT NOT IN ('SQL*Net more data from dblink', 'SQL*Net message to dblink', 'SQL*Net message from dblink','SQL*Net more data from client','SQL*Net more data to client','SQL*Net message from client','SQL*Net message to client','rdbms ipc message')) ab where pct_waits > 1 order by 4 desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking archive log generation for last 24 hours
prompt ************************************************************************
prompt
Select max(GBytes) Max_GB, min(GBytes) Min_GB ,round(sum(GBytes)/24,2) AVg_GB, sum(GBytes) total_GB  from (SELECT Start_Date,
       Start_Time,
       Num_Logs,
       Round(Num_Logs * (Vl.Bytes / (1024 * 1024 * 1024)), 2) AS GBytes,
       Vdb.NAME AS Dbname
  FROM (SELECT To_Char(Vlh.First_Time,
                       'YYYY-MM-DD') AS Start_Date,
               To_Char(Vlh.First_Time,
                       'HH24') || ':00' AS Start_Time,
               COUNT(Vlh.Thread#) Num_Logs
          FROM V$log_History Vlh
         GROUP BY To_Char(Vlh.First_Time,
                          'YYYY-MM-DD'),
                  To_Char(Vlh.First_Time,
                          'HH24') || ':00') Log_Hist,
       V$log Vl,
       V$database Vdb
 WHERE Vl.group# = (select min(group#) from v$log)
 ORDER BY Log_Hist.Start_Date desc, Log_Hist.Start_Time desc) where rownum < 25;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking total UNDO usage
prompt ************************************************************************
prompt
select a.TABLESPACE_NAME,
 nvl(round((a.BYTES/1024)/1024,2),0) MB_assigned,
 nvl(round((b.BYTES/1024)/1024,2),0) MB_free,
 nvl(round(((a.BYTES-b.BYTES)/a.BYTES)*100,2),0) percent_used
from
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES
  from  dba_data_files
  group  by TABLESPACE_NAME
 )
 a,
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES
  from  dba_free_space
  group  by TABLESPACE_NAME
 )
 b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.tablespace_name in (select tablespace_name from dba_rollback_segs) order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking total TEMP usage
prompt ************************************************************************
prompt
select tablespace_name, total_gb,gb_used, round((gb_used*100)/total_gb,2) Prcnt_Used from (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) Total_GB from dba_temp_files group by tablespace_name) a, (select TABLESPACE,round(sum(blocks)*8192/1024/1024/1024,2) GB_USED from v$sort_usage group by tablespace) b where a.tablespace_name=b.tablespace;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking SGA Statistics
prompt ************************************************************************
prompt
prompt
set heading off
Select 'Total of SGA Size :- '||round(sum(value)/1024/1024) from v$sga;
prompt
prompt
set heading on
select name,round(value/1024/1024) MB_Size from v$sga;
prompt
prompt
set serverout on termout on heading off

prompt ====================== DB  Cache Hit Ratio ===========================
prompt = Consider increasing DB_CACHE_SIZE if hit ratio is less than 70%    =
prompt ======================================================================
prompt
SELECT round(((P1.value + P2.value - P3.value) / (P1.value + P2.value))*100) "DB Cache Hit Ratio %"
     FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
 WHERE  P1.name = 'db block gets'
   AND    P2.name = 'consistent gets'
   AND    P3.name = 'physical reads';
prompt
prompt
prompt
prompt ==================== Libray Cache Hit Ratio ==========================
prompt = Consider increasing SHARED_POOL_SIZE if hit ratio is less than 99% =
prompt ======================================================================
prompt
SELECT 'Library Cache Hit Ration for :-  ',ROUND(100*(SUM(pins)-SUM(reloads))/SUM(pins)) "Near to 100 is good" FROM v$librarycache;
prompt
prompt
prompt
prompt ================== Dictionary Cache Hit Ratio ========================
prompt = Consider increasing SHARED_POOL_SIZE if hit ratio is less than 99% =
prompt ======================================================================
prompt
SELECT 'Dictionary Cache Hit Ration for :- ', ROUND(100*(SUM(gets)-SUM(getmisses))/SUM(gets)) "Near to 100 is good" FROM v$rowcache;
prompt
prompt
prompt
prompt ======================= Redo Buffer Requests =========================
prompt = Consider increasing LOG_BUFFERS if below value is less then 5000:1 =
prompt ======================================================================
prompt
select 'Redo Log buffer requests for :- ', Round(e.value/s.value) ">5000 is good",'  : 1' " " from v$sysstat s, v$sysstat e Where s.name = 'redo log space requests' and e.name = 'redo entries';
prompt
prompt
prompt
prompt ======================= Memory Vs. Disk Sort =========================
prompt = Consider increasing PGA_AGGREGATE_TARGET / SORT_AREA_SIZE if > 1   =
prompt ======================================================================
prompt
select 'Disk Sort % on :- ', round((b.value*100)/a.value,4) " should be < 1" from v$sysstat a, v$sysstat b where a.name like 'sort%memory%' and b.name like 'sorts%disk%';

prompt
prompt
prompt
prompt ************************************************************************
prompt           Packages Functions Procedures being reloaded more often
prompt ************************************************************************
prompt
set head on
col owner format a15
col type format a15
col name format a30
select * from (SELECT 
      owner,
      type,
      name,
      loads, 
      executions,
      round((loads/executions)*100,2) Percent_Load
FROM
      v$db_object_cache
WHERE
      executions>100 and type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE','INVALID TYPE')
      AND executions>100 AND loads>1 AND kept='NO' and owner not in ('SYS','SYSTEM')
ORDER BY 6 desc) where percent_load > 3 order by percent_load desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt       SQL statements that may cause errors like OutOfMemoryError
prompt ************************************************************************
prompt
Set head on
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;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Total sessions in database
prompt ************************************************************************
prompt

select count(1) "No. of Sessions" from v$session where username is not null;
select count(status) "No. of Sessions",status from v$session  where username is not null group by status order by 1,2 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking sessions idle for > 24 hours
prompt ************************************************************************
col username format a13
select username, count(username) from v$session where round(last_call_et/3600) > 24 and username is not null group by username order by 2 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 CPU using sessions as of now
prompt ************************************************************************
prompt
col sid format 999999
col username format a10
col osuser format a12
col program format a30
col CPU_MINS format 99.99
col logon_time format a20
col machine format a30
select distinct * from (select ss.sid, se.username,se.osuser ,nvl(se.program,se.module) program , se.status, round(((ss.VALUE*.01)/60),2) CPU_Mins,to_char(logon_time,'DD.MM.YYYY HH24:MI:SS') LOGON_TIME, NVL(MACHINE,TERMINAL) Machine from v$sesstat ss , v$statname sn, v$session se where sn.name like( '%CPU%by%') and ss.STATISTIC# = sn.STATISTIC# and ss.sid = se.sid and se.username is not null and se.status='ACTIVE' and round(((ss.VALUE*.01)/60),2) > .5 order by ss.value desc) where rownum < 11;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for transactions under recovery
prompt ************************************************************************
prompt
select state, undoblocksdone, undoblockstotal, undoblocksdone / undoblockstotal * 100 from gv$fast_start_transactions where state != 'RECOVERED';

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 memory using sessions
prompt ************************************************************************
prompt
SET LINESIZE       380
SET PAGESIZE       5000
SET FEEDBACK       on
SET VERIFY         on
SET SERVEROUTPUT   ON
SET TRIMSPOOL      ON
COL "SESSION"      FORMAT A50
COL "PID/THREAD"   FORMAT A10
COL "      CURRENT SIZE" FORMAT A18
COL "      MAXIMUM SIZE" FORMAT A18
select * from (SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||' - ' ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session pga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
 AND      ssn.username is NOT NULL
ORDER BY 3 DESC) where rownum < 11;
set lines 2300
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 redo generating sessions
prompt ************************************************************************
prompt
col sid form 9999
col username form a10
col value Head "Redo|Generated|in MB" form 9999999999.99
col program form a20
col logtime head "Logon Time" form a15
col sql_text format a45 wrap
select distinct a.sid,a.username,a.logtime,a.program,a.value,sq.sql_text from (SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') logtime, nvl(se.program,se.module) program, round((value/1048576),2) VALUE, se.sql_address FROM v$sesstat st, v$statname sn, v$session se WHERE sn.name = 'redo size' AND value > 104857600
AND sn.statistic# = st.statistic# AND st.sid = se.sid ORDER BY 5 desc ) a, v$sql sq where a.sql_address=sq.address and rownum < 11 order by 5 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 UNDO using sessions
prompt ************************************************************************
prompt

col object_name format a30
col username format a13
col name format a14
col EXTENTS format 9999999
col SID_SERIAL format a15
col sql_text format a40 wrap
col undo_MB format 99,999.99
select * from (select  s.sid||','||s.serial# SID_SERIAL, s.username, t.used_ublk * TO_NUMBER(x.value)/1024/1024 undo_MB, sq.sql_text ,do.object_name
        from    v$transaction   t
               ,v$session       s
               ,v$rollname      rn
               ,v$rollstat      rs
               ,v$locked_object lo
               ,dba_objects     do
               ,v$sql           sq
               ,v$parameter   x
        where  t.addr        = s.taddr
        and    t.xidusn      = rn.usn
        and    rn.usn        = rs.usn
        and    t.xidusn      = lo.xidusn(+)
        and    do.object_id  = lo.object_id
        and    s.sql_id      = sq.sql_id(+)
        AND    x.name  = 'db_block_size'
        order by used_ublk desc) where rownum < 11;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 TEMP using sessions
prompt ************************************************************************
prompt
COLUMN sid_serial FORMAT A15
COLUMN osuser FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A28
COLUMN TABLESPACE FORMAT a15
COLUMN sql_text FORMAT A45
select * from (select distinct * from (select TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial, s.osuser,s.username, s.program, u.tablespace, (u.blocks*8192/1024/1024) TEMP_MB, sq.sql_text
from   v$session s, v$sort_usage u, v$sql sq where  s.saddr = u.session_addr and s.sql_id = sq.sql_id order by u.blocks desc) where rownum < 11) order by temp_mb desc;

prompt
prompt
prompt ************************************************************************
prompt   Checking for resource consuming Sql statements
prompt ************************************************************************
prompt
COLUMN USERNAME         FORMAT a10         HEADING 'User Name'
COLUMN avgdiskreads     FORMAT 9999999     HEADING 'Avg.Disk.Reads'
COLUMN disk_reads       FORMAT 999,999,999 HEADING 'Block Reads'
COLUMN sql_text         FORMAT a60         HEADING 'Statement' WORD_WRAPPED
select * from (SELECT   *
    FROM (SELECT   username, ROUND (disk_reads / executions) avgdiskreads,
                   executions, disk_reads, sql_text
              FROM v$sqlarea, dba_users
             WHERE disk_reads > 25000
               AND EXECUTIONS > 0
               AND command_type != 47
               AND user_id = parsing_user_id
               AND username not in ('SYS','SYSMAN')
          ORDER BY (executions*disk_reads)) a
   WHERE a.avgdiskreads > 10000
ORDER BY (a.executions*a.avgdiskreads) DESC) where rownum < 11;
prompt
prompt
prompt
COLUMN USERNAME         FORMAT a10         HEADING 'User Name'
COLUMN avgbuffgets     FORMAT 99,99,99,999     HEADING 'Avg.Buff.Reads'
COLUMN buffer_gets       FORMAT 99,99,99,99,999 HEADING 'Buffer Gets'
COLUMN sql_text         FORMAT a60         HEADING 'Statement' WORD_WRAPPED
select * from (SELECT   *
    FROM (SELECT   username, ROUND (buffer_gets / executions) avgbuffgets,
                   executions, buffer_gets, sql_text
              FROM v$sqlarea, dba_users
             WHERE buffer_gets > 50000
               and EXECUTIONS > 0
               AND command_type != 47
               AND user_id = parsing_user_id
               AND username not in ('SYS','SYSMAN')
          ORDER BY (executions*buffer_gets)) a
   WHERE a.avgbuffgets > 100000
ORDER BY (a.executions*a.avgbuffgets) DESC) where rownum < 11;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for active sessions as of now
prompt ************************************************************************
prompt

col sid format 999999
col osuser format a15
col username format a12
col sql_text format a45
col event format a28
col program format a28
select distinct b.sid, c.osuser, c.username, d.sql_text, b.event, nvl(nvl(c.program,c.module),c.machine) Program   from v$session_wait b, v$session c, v$sql d where b.sid = c.sid and c.sql_address = d.address and c.last_call_et > 1 and c.status = 'ACTIVE';
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for locks on database
prompt ************************************************************************
prompt

column blocker format 9999999
column blockee format 9999999
column os_user format a15
column username format a12
column table_name format a32
column program format a18
column SQL_STATEMENT_WAITER format a45 wrap
column SQL_STATEMENT_BLOCKER format a45 wrap
column Program_Waiter format a20 wrap
column Program_Blocker format a20 wrap
select s2.sid Blockee, s2.username, s2.osuser Os_User,nvl(s2.program,s2.module) Program_Waiter, sa2.sql_text sql_statement_waiter, s1.sid Blocker, s1.username, s1.osuser Os_User,nvl(s1.program,s1.module) Program_Blocker, sa1.sql_text sql_statement_blocker,tbl.owner || '.' || tbl.object_name Table_Name, DBMS_ROWID.rowid_create (1,s2.row_wait_obj#,s2.row_wait_file#,s2.row_wait_block#,s2.row_wait_row#) Waiting_Row_ID
    from v$lock l1, v$session s1, v$lock l2, v$session s2, dba_objects tbl, v$sqlarea sa2, v$sqlarea sa1
    where s1.sid=l1.sid and s2.sid=l2.sid
    and s2.sql_address= sa2.address and s1.sql_address= sa1.address
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2
    and s2.last_call_et > 0
    and s2.status = 'ACTIVE'
    and tbl.object_id = s2.row_wait_obj#;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for long running transactions
prompt ************************************************************************
prompt

col username format a12
col program format a18
col osuser format a10
col sql_text format a45
select distinct * from (SELECT to_date(start_time,'MM.DD.YY HH24:MI:SS') ST_DT, s.sid,s.serial#,s.username,nvl(s.program,s.module) program, t.status T_Status, s.status S_Status, sq.sql_text FROM v$transaction t, v$session s, v$sql sq where t.addr = s.taddr and to_date(start_time,'MM.DD.YY HH24:MI:SS') < sysdate-4/24 and s.sql_id=sq.sql_id (+)) order by 1;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Jobs running on database
prompt ************************************************************************
prompt

col schema_user format a15
col what format a65
select a.this_date, sid,a.job,schema_user,what,a.LAST_DATE  from dba_jobs_running a, dba_jobs b where a.job=b.job order by this_date;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for long running queries
prompt ************************************************************************
prompt

col sql_text format a45
col target format a38
col opname format a15
col sql_start_Time format a19
col PCT format 999.99
col RUN_TIME format a8
col sofar format 99999999
col totalwork format 99999999
select distinct * from (select l.sid ssid, sq.sql_text, substr(OPNAME,1,15) opname,
target, to_char(sysdate-(last_call_et/60/60/24),'DD.MM.YYYY HH24:MI:SS') sql_start_Time, round(l.sofar *100/ (l.totalwork+.0000000001),1) pct,
  lpad(mod( trunc(last_call_et/60/60),60*24),2,'0') || ':' ||
  lpad(mod( trunc(last_call_et/60),60),2,'0') || ':' ||
  lpad(mod(last_call_et,60),2,'0') Run_Time,
sofar, totalwork
from v$session_longops l, v$session s, v$sql sq
where s.sid = l.sid
and s.sid in (select distinct sid from v$session_longops)
and s.serial# = l.serial#
and s.sql_id = sq.sql_id
and l.sofar < l.totalwork
and l.start_time >= sysdate - ( (s.last_call_et+2)/60/60/24)
order by to_char(sysdate-(last_call_et/60/60/24),'DD.MM.YYYY HH24:MI:SS'), l.sid, decode(l.sofar,l.totalwork,1,2), l.start_time, l.last_update_time) order by 7 desc;

set heading on
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking parallel degree queries
prompt ************************************************************************
prompt
col sql_text format a45 wrap
select qcsid, username, asgnd_degree,Reqstd_Degree, sql_text from (select QCSID, max(sid), sum(degree)/count(QCSID) Asgnd_Degree, sum(req_degree)/count(QCSID) Reqstd_Degree from V$PX_SESSION where sid!=qcsid group by qcsid) a, v$session b, v$sqlarea sa where a.qcsid=b.sid and b.sql_address= sa.address;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking pending distributed transaction
prompt ************************************************************************
prompt
col LOCAL_TRAN_ID format a15
col GLOBAL_TRAN_ID format a15
col HOST format a15
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT# FROM DBA_2PC_PENDING;

prompt
prompt
prompt
prompt ************************************************************************
prompt  List of tablespace used upto 90% or more and SYSTEM tablespaces.
prompt ************************************************************************
prompt

select a.TABLESPACE_NAME,
 nvl(round((a.BYTES/1024)/1024,2),0) MB_assigned,
 nvl(round((b.BYTES/1024)/1024,2),0) MB_free,
 nvl(round((b.largest/1024),2),0) KB_Chunk,
 nvl(round(((a.BYTES-b.BYTES)/a.BYTES)*100,2),0) percent_used
from
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES
  from  dba_data_files
  group  by TABLESPACE_NAME
 )
 a,
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES ,
   nvl(max(BYTES),0) largest
  from  dba_free_space
  group  by TABLESPACE_NAME
 )
 b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and (a.tablespace_name in ('SYSTEM','SYSAUX') or round((b.BYTES/1024)/1024,2) < 1000 OR round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) > 90)
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt    Checking for probable next_extent failures
prompt ************************************************************************
prompt

column SEGMENT_NAME format a35
column TABLESPACE_NAME format a30
column owner format a15
select a.owner,a.segment_name, a.tablespace_name, Round(a.next_extent/1024/1024) MB_NEXT_EXT, round(b.largest_chunk/1024/1024) MB_LARGEST_CHUNK from dba_segments a, (select tablespace_name,max(bytes) Largest_Chunk from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name and a.next_extent > b.largest_chunk;

prompt
prompt
prompt ************************************************************************
prompt        Tables with high number of chained rows.
prompt ************************************************************************
prompt
column c1 heading "Owner"   format a9;
column c2 heading "Table"   format a25;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows"    format 999,999,999;
column c7 heading "Chains"  format 999,999,999;
column c8 heading "Lst_Analyzed"  format A12
column c9 heading "Pct"     format 999.99;
set heading off;
set heading on;
select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   last_analyzed      c8,
   round(chain_cnt/num_rows*100,2) c9
from dba_tables
where
owner not in ('SYS','SYSTEM') and chain_cnt > 0 and num_rows > 0
and (chain_cnt/num_rows*100) > 9
order by (chain_cnt/num_rows*100) desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for fragmented tables
prompt ************************************************************************
prompt

COL SEG_NAME FORMAT A40
COL TOTAL_MB FORMAT 999999999
COL ACTUAL_MB FORMAT 999999999
COL FRAGMENTED_MB FORMAT 999999999
SELECT SEG_NAME     ,
  TOTAL_MB      ,
  ACTUAL_MB     ,
  FRAGMENTED_MB,
  ROUND(FRAGMENTED_MB*100/TOTAL_MB,2) PERCENT_FRAG,
  trunc(Last_analyzed)
   FROM
  (SELECT OWNER
    ||'.'
    ||TABLE_NAME SEG_NAME                                   ,
    ROUND(((BLOCKS *8192/1024/1024)),2) TOTAL_MB       ,
    ROUND((NUM_ROWS*AVG_ROW_LEN/1024/1024),2) ACTUAL_MB,
    ROUND(((BLOCKS *8192/1024/1024)-(NUM_ROWS*AVG_ROW_LEN/1024/1024)),2) FRAGMENTED_MB,
    Last_analyzed
     FROM DBA_TABLES
    WHERE OWNER NOT IN ('SYS','SYSTEM')
  AND BLOCKS         > 12800
  )
  WHERE ROUND(FRAGMENTED_MB*100/TOTAL_MB,2) > 75
ORDER BY 5 DESC,2 desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt     Checking for queries doing full table scan and executed > 1000
prompt ************************************************************************
prompt

col operation format a15
col object_name format a30
col object_owner format a12
col options format a15
col executions format 999,999,999
col sql_text format a35
select distinct * from (select a.object_owner,a.object_name, a.operation, a.options, b.executions, b.SQL_TEXT from v$sql_plan a, v$sqlarea b
where a.address=b.address and a.operation IN ('TABLE ACCESS','INDEX') and round(b.ROWS_PROCESSED/b.executions,0) > 100000 and a.bytes > 10485760
and a.options in ('FULL','FULL SCAN') and a.object_owner not in ('SYS','SYSTEM','PERFSTAT') and b.executions > 100
order by b.executions*a.bytes);

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for Tables with concurrent requests > 50
prompt ************************************************************************
prompt
set lines 132 pages 2000
col object format a35
col owner format a25
col object_type format a15
select count(object),owner,object,type from v$access where type in ('TABLE','VIEW') and owner not in ('SYS','SYSTEM') group by owner,object,type having count(object) > 50 order by 1 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for Tablespaces with top file IO waits
prompt ************************************************************************
prompt
column tablespace_name format a25
column Read_Time format 99999
column Write_Time format 99999
Select * from (select * from (
Select tablespace_name, round(sum(MAXIORTM)/100) Read_Time , round(sum(MAXIOWTM)/100) Write_Time from v$filestat fs, dba_data_files ddf where fs.file# = ddf.file_id group by tablespace_name
UNION
Select tablespace_name, round(sum(MAXIORTM)/100) Read_Time , round(sum(MAXIOWTM)/100) Write_Time from v$tempstat ts, dba_temp_files dtf where ts.file# = dtf.file_id group by tablespace_name) order by 2 desc, 3 desc) where rownum < 6;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 file IO waits
prompt ************************************************************************
prompt

col file_name format a75
select tablespace_name,file_name,b.maxiortm,b.maxiowtm from dba_data_files a, (select * from (select file#,MAXIORTM,MAXIOWTM from v$filestat order by 2 desc, 3 desc) where rownum < 11) b where a.file_id=b.file# order by 3 desc, 4 desc;


prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for corruption on database
prompt ************************************************************************
prompt
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
prompt
prompt
prompt
prompt
set head on
show parameters
SPOOL OFF
EXIT