Posts

Showing posts from 2012

Oracle information about package , function , procedure

To find out information about package , function , procedure etc :-

SQL> set long 100000
SQL> select TEXT from  dba_source where name = '<package_name>';
SQL> SELECT REFERENCED_NAME,REFERENCED_OWNER,REFERENCED_TYPE,DEPENDENCY_TYPE FROM DBA_DEPENDENCIES WHERE NAME= '<package_name>';

SQL> SELECT REFERENCED_NAME,REFERENCED_OWNER,REFERENCED_TYPE,DEPENDENCY_TYPE FROM DBA_DEPENDENCIES WHERE NAME= '<package_name>' AND REFERENCED_TYPE IN ('TABLE','SYNONYM');

Oracle DB Health Scripts 1 - physical I/O, logical I/O, memory and CPU processes,,Top CPU usage by users,etc.Database Version

This single script provides the overall picture of the database in terms of Waits events, Active/Inactive killed sessions, Top Processes (physical I/O, logical I/O, memory and CPU processes), Top CPU usage by users,etc.Database Version :-

set serveroutput on declare  cursor c1 is select version from v$instance; cursor c2 is     select           host_name        ,  instance_name        ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime        ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime      from v$instance; cursor c4 is select * from (SELECT count(*) cnt, substr(event,1,50) event FROM v$session_wait WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client') GROUP BY event ORDER BY 1 DESC) where rownum <6; cursor c5 is select round(sum(value)/1048576) as sgasize from v$sga; cursor c6 is select round(sum(bytes)/1048…

Oracle Expdp error ORA-39070,ORA-39002,ORA-29283,ORA-06512,ORA-29283

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation


Solution : You have to manually create directory  and give writes to specified users on specified path then after you try your operation.


SQL> create directory expbkp as '/opt/oracle/expbkp';
SQL> grant read, write on directory expbkp to <user_name>;
SQL>select * from dba_directories where directory_name = 'EXPBKP';
Otherwise you can use DATA_PUMP_DIR which is defalut in Oracle 10g and Oracle 11g.

A query to show you current sessions generating redo

A query to show you current sessions generating redo:-

select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
  join v$statname n on n.statistic# = s.statistic#
  join v$session sn on sn.sid = s.sid
where name like '%redo entries%'
order by value desc;


Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a  session generating lots of redo.
SQL> SELECT s.sid, s.serial#, s.username, s.program,      i.block_changes      FROM v$session s, v$sess_io i      WHERE s.sid = i.sid      ORDER BY 5 desc, 1, 2, 3, 4;

Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the  USED_UBLK and USED_UREC columns).
SQL> SELECT s.sid, s.serial#, s.username, s.program,       t.used_ublk, t.used_urec      FROM v$session s, v$transaction t      WHERE s.taddr = t.addr      ORDER…

Linux count the number of the files in that directory and its subdirectories

count  the number of the files in that directory and its sub directories is:

ls -laR | grep -v ^[.dlt] | grep -v ^$ | wc -l

OR run either of  the below codes  :-

for t in files links directories; do echo `find . -maxdepth 1 -type ${t:0:1} | wc -l` $t; done 2> /dev/null


50 files
0 links
74 directories


for t in files links directories; do echo `find . -type ${t:0:1} | wc -l` $t; done 2>/dev/null
6781 files 0 links 814 directories

converting RAC archive log mode to non archive log mode.

( Note All these steps are just indicative , run these on your test environment , before production )

- shutdown all instance except one

- restart your one instance in mount state which you have left in read - write mode on which you are going to
  perform this

- alter system set cluster_database=false scope=spfile sid='*';

- shutdown immediate

- startup mount;

- alter database noarchivelog;

- alter system set log_archive_dest='' scope=spfile sid='*';

- alter system set cluster_database=true scope=spfile sid='*' ;

- Startup all instances

monitoring audit

select user_name, audit_option, success, failure
        from sys.dba_stmt_audit_opts;

select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,
userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff
from sys.aud$ where userid='<schema_name>';

select username, priv_used, ses_actions from dba_audit_object;

select action, action_name, username from dba_audit_trail ;

select * from stmt_audit_option_map;

Some ASH (Active Session History) views

select * from  DBA_HIST_ACTIVE_SESS_HISTORY;
select * from  DBA_HIST_BASELINE;
select * from  DBA_HIST_BG_EVENT_SUMMARY;
select * from  DBA_HIST_BUFFERED_QUEUES;
select * from  DBA_HIST_BUFFERED_SUBSCRIBERS;
select * from  DBA_HIST_BUFFER_POOL_STAT;
select * from  DBA_HIST_COMP_IOSTAT;
select * from  DBA_HIST_CR_BLOCK_SERVER;
select * from  DBA_HIST_CURRENT_BLOCK_SERVER;
select * from  DBA_HIST_DATABASE_INSTANCE;
select * from  DBA_HIST_DATAFILE;
select * from  DBA_HIST_DB_CACHE_ADVICE;
select * from  DBA_HIST_DLM_MISC;
select * from  DBA_HIST_ENQUEUE_STAT;
select * from  DBA_HIST_EVENT_NAME;
select * from  DBA_HIST_FILEMETRIC_HISTORY;
select * from  DBA_HIST_FILESTATXS;
select * from  DBA_HIST_INSTANCE_RECOVERY;
select * from  DBA_HIST_INST_CACHE_TRANSFER;
select * from  DBA_HIST_JAVA_POOL_ADVICE;
select * from  DBA_HIST_LATCH;
select * from  DBA_HIST_LATCH_CHILDREN;
select * from  DBA_HIST_LATCH_MISSES_SUMMARY;
select * from  DBA_HIST_LATCH_NAME;
select * from  DBA_HIST_LATCH_PAREN…

Linux Error Argument list too long

If you get this  error by deleting log files in any directory :- /bin/rm: Argument list too long

then run the following command :-

 find . -name '*' | xargs rm

 find . -name 'spam-*' | xargs rm

  find . -name '*.trc' | xargs rm

find . -name 'core*' | xargs rm -rf

  find /rpt01/dbs/log/ -name '*.log' -mtime +4 -exec rm {} \; (This will delete 4 days old log files)


how much archive generate per day

SQL> select to_char(completion_time,'DD/MM/YYYY') HOUR,count(*),round(sum(blocks*block_size)/1024/1024/1024,2) size_g from v$archived_log where completion_time>sysdate-30 group by to_char(completion_time,'DD/MM/YYYY') order by to_char(completion_time,'DD/MM/YYYY') asc;


SQL> show parameter db_recovery_file_dest
NAME                                 TYPE                             VALUE ------------------------------------ -------------------------------- ------------------------------ db_recovery_file_dest                string                           /ora/db001/dbfra001/<db_name> db_recovery_file_dest_size           big integer                      500G
SQL>!df -h | grep /ora/db001/dbfra001           2.5T  348G  2.1T  15% /ora/db001/dbfra001
redo logs are located on "/ora/db001/redo001/FPD1AP/" and "/ora/db001/redo002/<db_name>/" ,total 15 redo logfiles with total size 92GB. 

SQL> select sum(bytes)/1024/1024 siz…

ANALYZE statement with the VALIDATE STRUCTURE option

To verify structure of a table, index, materialized view, use the ANALYZE statement with the VALIDATE STRUCTURE option. If the structure is valid, no error is returned. However, if the structure is corrupt, you receive an error message.
For example, in rare cases such as hardware or other system failures, an index can become corrupted and not perform correctly. If index is corrupt, you can drop and re-create it.
If a table, index is corrupt, you should drop it and re-create it. If a materialized view is corrupt, perform a complete refresh and ensure that you have remedied the problem. If the problem is not corrected, drop and re-create the materialized view.
The following statement analyzes the table:
ANALYZE TABLE <table_name> VALIDATE STRUCTURE; You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option. The following statement validates the table and all associated indexes: ANALYZE TABLE <table_name>  VALIDATE STRUCTURE …

disable password_verify_function

Sometimes user getting locked again and again after running some procedure or function , then in that case either disable  password_verify_function or alter profile or unlock the user by providing password manually .


select * from dba_profiles;

select * from dba_users where USERNAME like '<USERNAME>';

ALTER PROFILE DEFAULT limit PASSWORD_GRACE_TIME unlimited;


to disable password_verify_function:-


SQL> ALTER PROFILE DEFAULT limit password_verify_function null;


SQL> select profile from dba_users where username='<user_name>';


SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE PROFILE='<PROFILE_NAME>' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';


SQL>
ALTER PROFILE <PROFILE_NAME> LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.


SQL> alter user <username> identified by <password> account unlock;

User altered.

alter index command for partition table

alter table <schema_name>.<table_name>
  add constraint <contraint_name> primary key (<column_name>, <column_name>)
  using index
  tablespace <tablespce_name>
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  )LOCAL;

some event and wait events of session

SQL> select count(*),event from v$active_session_history where session_id=331 group by event order by count(*) desc;
  COUNT(*) EVENT ---------- ----------------------------------------------------------------        323 log file sync        220 db file sequential read        132 log file switch (checkpoint incomplete)        110 buffer busy waits
2.            Today, Log File related wait events are not there. Below are the wait events:
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=331 order by time_waited desc; EVENT                                                            TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- SQL*Net message from dblink                                            32615     1785828 local write wait                                                        2048      199947 enq: RO - fast object reuse                                              154       45023 e…

creating agent in Oracle

[oracle@cint ~]$ orapwd file=$ORACLE_HOME/dbs/orapwevt01 password=sys entries=5
[oracle@cint ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 19 10:41:00 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> Select username from dba_users where username='SYSMAN';

USERNAME
------------------------------
SYSMAN

SQL> alter user sysman identified by sys account unlock;

User altered.

SQL> alter user dbsnmp identified by sys account unlock;

User altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@cint ~]$ emca -repos create (or run the command
./emca -config dbcontrol db -repos create)


STARTED EMCA at Mar 19, 2012 10:44:12 AM
EM Configuration Assistant, Version 10.2.0.1.0 Prod…