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;

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

 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

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

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

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;

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'; 

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';

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;
/

Oracle UNDO related document

SET SERVEROUTPUT ON

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;

/

12c Binaries Installation - Silent Installation Oracle Software 12c

I have prepared silent mode installation of 12c software , please validate


cd /local/dbatemp/software/12102/database
export DISTRIB=`pwd`
./runInstaller -silent \
-responseFile $DISTRIB/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=jbs_sudo \
INVENTORY_LOCATION=/local/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/local/app/oracle/product/12.1.0.2  \
ORACLE_BASE=/local/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=jbs_sudo \
oracle.install.db.OPER_GROUP=jbs_sudo \
oracle.install.db.BACKUPDBA_GROUP=jbs_sudo \
oracle.install.db.DGDBA_GROUP=jbs_sudo \
oracle.install.db.KMDBA_GROUP=jbs_sudo \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true



This has started installation in the background: you need to wait until something similar to the following is displayed:

The installation of Oracle Database 12c was successful.
Please check '/local/app/oraInventory/logs/silentInstallXXX.log' for more details.

As a root user, execute the following script(s):
1. /local/app/oraInventory/orainstRoot.sh
2. /local/app/oracle/product/12.1.0.2/root.sh

Successfully Setup Software.