Search This Blog

Total Pageviews

Wednesday 3 August 2011

Oracle 11g adrci purge

Oracle adrci purge

adrci purge


7days

1hr=60 minutes
1day = 24 hr
1 week =7 days

select 60*24*7 week_to_min from dual;

60*24*7
----------
10080 <<<------- min




oracle_sid=aptdb

adrci> set home aptdb
adrci> purge -age 10080 -type ALERT
adrci> purge -age 10080 -type TRACE
adrci> purge -age 10080 -type incident
adrci> purge -age 10080 -type hm
adrci> purge -age 10080 -type utscdmp
adrci> purge -age 10080 -type cdump



to set auto purge

select 168/7 from dual;

168/7
----------
24 ----- hr

168 is hr

adrci>set control (SHORTP_POLICY = 168) now set to 7 days
adrci>set control (LONGP_POLICY = 168 now set to 7 days

Oracle 11g adrci from prompt

Oracle 11g alert through adrci

Oracle 11g adrci

# adrci.cmd <<<<<
--------------------------------

# ADRCI script to find alert log errors
SPOOL /tmp/alert_log_errors.txt
ECHO "ALERT LOG ERRORS:"; SET HOME diag/rdbms/aptdb/aptdb; SHOW ALERT -TERM -P "MESSAGE_TEXT LIKE '%ORA-%'"
SPOOL OFF

---------------------------------


$adrci -script=adrci.cmd

-bash-3.2$ ls -ltr /tmp/alert_log_errors.txt
-rw-r----- 1 oracle dba 9766 Aug 3 14:50 /tmp/alert_log_errors.txt

What is a Oracle thread ?

Oracle thread
What is a Oracle threads ?



set of redo log files calls thread and redo written by an instance is called a thread of redo .
in case of RAC each instance mush have own threads ( redo logs ).

V$THREAD displays thread information from the control file.


col INSTANCE format a20
SELECT thread#, instance, status FROM v$thread

THREAD# INSTANCE STATUS
---------- -------------------- ------
1 aptdb OPEN




select thread#, sequence#, status from v$log;

THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 343 CURRENT
1 341 INACTIVE
1 342 INACTIVE


select thread#, status, enabled, sequence#,checkpoint_time from v$thread ;

THREAD# STATUS ENABLED SEQUENCE# CHECKPOIN
---------- ------ -------- ---------- ---------
1 OPEN PUBLIC 343 03-AUG-11





to disable unwanted threads in case RAC to single instance

ALTER DATABASE DISABLE THREAD 2;
ALTER DATABASE DISABLE THREAD 3;




SELECT thread#, first_change#,TO_CHAR(first_time,'MM-DD-YY HH12:MIPM'),next_change# FROM v$log_history;

THREAD# FIRST_CHANGE# TO_CHAR(FIRST_TI NEXT_CHANGE#
---------- ------------- ---------------- ------------
1 777615 06-01-11 03:26PM 797997
1 797997 06-01-11 03:27PM 804296
1 804296 06-01-11 03:27PM 807110
1 807110 06-01-11 03:28PM 813496
1 813496 06-01-11 03:29PM 821860
1 821860 06-01-11 03:30PM 833008
1 833008 06-01-11 03:31PM 839603
1 839603 06-01-11 03:32PM 852610
1 852610 06-01-11 03:33PM 864323
1 864323 06-01-11 03:34PM 877186
1 877186 06-01-11 03:35PM 889533
1 889533 06-01-11 03:36PM 893633
1 893633 06-01-11 03:38PM 898156
1 898156 06-01-11 03:40PM 902166
1 902166 06-01-11 03:42PM 912259



select item,units,sofar from v$recovery_progress
where start_time = ( select max(start_time) from v$recovery_progress)
and item = 'Checkpoint Time per Log' ;




select THREAD#,GROUPS,CURRENT_GROUP#,SEQUENCE#,CHECKPOINT_CHANGE#,ENABLE_CHANGE#,DISABLE_CHANGE# LAST_REDO_SEQUENCE#,TO_CHAR(LAST_REDO_TIME,'MM-DD-YY HH12:MIPM') last_redo , to_char(OPEN_TIME , 'MM-DD-YY HH12:MIPM') open_time from v$thread ;

THREAD# GROUPS CURRENT_GROUP# SEQUENCE# CHECKPOINT_CHANGE# ENABLE_CHANGE# LAST_REDO_SEQUENCE# LAST_REDO OPEN_TIME
---------- ---------- -------------- ---------- ------------------ -------------- ------------------- ---------------- ----------------
1 3 1 343 5847523 1 0 08-03-11 11:52AM 07-11-11 02:05PM

Oracle DBA

anuj blog Archive