Archive for June, 2012

Oracle Database Redo Log Switch Frequency

I found this handy script to check out Oracle Log Switch Frequency from link.


--COL DAY FORMAT a15;
--COL HOUR FORMAT a4;
--COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;

Continue reading

Advertisements

Oracle Automatic Diagnostic Repository (adrci) Useful Commands

Automatic Diagnostic Repository (adrci) is one of the new features in Oracle 11g. it’s a handy Command Line Utility to deal with Database Alert Log. Followings are some useful adrci Commands.


-- To list Oracle Base
adrci> show base;

-- To list ADR HOMEs
adrci> show homes;

-- To set Single Home Path Location
adrci> set homepath 'location';

-- To tail Alert Log File
adrci> show alert -tail -f;

-- To search a Specific Message
adrci> show alert -p "message_text like 'ORA-00600'" -term

-- To search within Specific Time Limit
adrci> show alert -p "message_text like '%ORA-%' and originating_timestamp >= systimestamp - 1/24" -term

-- To purge 10080 Minutes old alert 
adrci> purge -alert 10080 -type alert;

-- To list Incidents
adrci> show incident;

-- To list Problems
adrci> show problem;

Thanks
A Rahim Khan

Creating Directory for Oracle Datapump

This small script shows how to create a directory and assign/grant appropriate permissions for Datapump Job or expdp/impdp job.


CREATE OR REPLACE DIRECTORY EXPDP AS '/u01/oracle/dump';

GRANT READ, WRITE ON DIRECTORY SYS.EXPDP TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY SYS.EXPDP TO IMP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY SYS.EXPDP TO SYSTEM;

Thanks
A Rahim Khan

Oracle SQL to find out Blocker and Reasons

Execute this script to find out Blocker/Waiter Session.


SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess_sid,inst_id
FROM GV$LOCK 
WHERE (id1, id2, type) IN ( SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY sess_sid, request;

This one is to determine why.


SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
 AND event NOT IN ('smon timer','pmon timer','rdbms ipc message', 
    'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;

Thanks
A Rahim Khan

Rebuild All Indexes for specific User/Schema

Here, I will generate script to rebuild all index for a specific Schema. First of all, let’s all Index for a specific Owner.


SQL> select index_name from dba_indexes where owner=upper('hr');

INDEX_NAME
------------------------------
LOC_CITY_IX
LOC_ID_PK
REG_ID_PK
COUNTRY_C_ID_PK
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

19 rows selected.

Continue reading

sqlnet.ora Modification for connection timeout and Invited Nodes

To set a Connection Time out in SQLNET.ORA put this line in file and reload Listener.


sqlnet.expire_time = 10

To enable valid Node checking


tcp.validnode_checking=yes
tcp.invited_nodes=(ip1,ip2,ip3)

Thanks
A Rahim Khan

Job Scheduling in ORACLE

Lets start with creating a stored procedure that we will use to create a job.


CREATE PROCEDURE hr.test_proc1  AS
BEGIN
 insert into test1 values(25,'baju');
END;

Use this following script to create a Scheduled Job.


BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'job_test1',
   JOB_TYPE           =>  'STORED_PROCEDURE',
   JOB_ACTION         =>  'hr.test_proc1',
   START_DATE         =>  '10-JUN-12 12.23.00 PM',
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=2');
END;

Continue reading