Oracle Database Administration

Tuesday, January 14, 2025

delete archive log from RMAN of last 8 days

delete force archivelog until time 'SYSDATE-08';

Thursday, November 28, 2024

Linux Memory

ps -A --sort -rss -o comm, pcpu, pmem, pid, user| head -n 15

free | grep Mem | awk '{print $3/$2 * 100.0}'

Wednesday, August 7, 2024

DR scripts

select max(sequence#) from v$log_history ;

set lines 200

SELECT thread#, process, pid, status, client_process, sequence#, block# FROM gv$managed_standby ORDER BY thread#, process;

col FIRST for a25

col NEXT for a25

col COMPLETION for a25

select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion

from v$archived_log where thread# = 1 order by first_time;

SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

select max(sequence#) from v$archived_log;

SELECT /*+ rule */ ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

archive generation - compact script Oracle

select trunc(COMPLETION_TIME,'DD') Day, thread#,

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Tuesday, August 6, 2024

archive generation per hour Oracle !

set linesize 140
set feedback off
set timing off
set pagesize 1000
col ARCHIVED format a8
col ins format 99 heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type format a10
col group# format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format a3
col h01 heading "H01" format a3
col h02 heading "H02" format a3
col h03 heading "H03" format a3
col h04 heading "H04" format a3
col h05 heading "H05" format a3
col h06 heading "H06" format a3
col h07 heading "H07" format a3
col h08 heading "H08" format a3
col h09 heading "H09" format a3
col h10 heading "H10" format a3
col h11 heading "H11" format a3
col h12 heading "H12" format a3
col h13 heading "H13" format a3
col h14 heading "H14" format a3
col h15 heading "H15" format a3
col h16 heading "H16" format a3
col h17 heading "H17" format a3
col h18 heading "H18" format a3
col h19 heading "H19" format a3
col h20 heading "H20" format a3
col h21 heading "H21" format a3
col h22 heading "H22" format a3
col h23 heading "H23" format a3
col total format a6
col date format a10
select * from v$logfile order by group#;
select * from v$log order by SEQUENCE#;
select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY
instance,log_date
ORDER BY
log_date ;

select trunc(min(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval,
trunc(max(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;
set feedback on
set timing on

Thursday, April 25, 2024

As seen below obsolete EXTRACT1_NAME is showing REQ_SCN = 0.

SQL>SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;

CAPTURE_NAME    CAPTURE_TYPE STATUS          REQ_SCN      OLDEST_SCN
-------------- ------------ -------        --------      ----------
OGG$CAP_EXTRACT-NAME LOCAL        ENABLED        64983227        64983227
OGG$CAP_EXTRACT1-NAME LOCAL        ENABLED               0               0

After unregistering as follows RMAN was able to delete the old archive logs:

UNREGISTER EXTRACT EXTRACT-NAME DATABASE

Thursday, November 16, 2023

find /home -xdev -type f -size +1G -exec ls -lrth {} \;

find . -name '*.trc' -mtime +2 -exec rm -f {} \;

Monday, October 23, 2023

UNDO required size - using Stats

SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

Query to get 10 Large Object in Oracle Database

SELECT * FROM (select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB,TABLESPACE_NAME from dba_segments order by 3 desc) WHERE ROWNUM <= 10;

Saturday, October 21, 2023

Recovery file usage

col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;

Meta data tablespace

set lines 190
set pages 5000
set long 50000
set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces where tablespace_name='TSTDOCMGMT01';

Wednesday, October 11, 2023

killing inactive session from Oracle database

SELECT    'ALTER SYSTEM KILL SESSION '''
      || s.sid
      || ','
      || s.serial#
      || ''' IMMEDIATE;'
         AS ddl
FROM v$session s
WHERE s.status='INACTIVE';

Wednesday, September 27, 2023

UNDO Main

SET SERVEROUTPUT ON
-- Not wrriten by me but its a good script to check details on UNDO tablespace usage and recommendations
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);

DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;

SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/