génération des archivelogs

Pour avoir des informations sur la génération des archivelog :

select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v$log_history
group by to_char(first_time,'YYYY-MON-DD');



select count(recid), trunc(sum(blocks*block_size)/1024/1024) "Size Mo", trunc(first_time,'HH24') "Time by Hour"
from v$archived_log
where creator = 'ARCH'
group by trunc(first_time,'HH24')
order by trunc(first_time,'HH24') desc;

Suivre les activités du backup RMAN

Voici quelques requêtes bien utiles pour suivre les activités les activités d'un job de backup  RMAN :

Select   sid,   start_time,   totalwork  sofar,  (sofar/totalwork) * 100 pct_done
from   v$session_longops
where
   totalwork > sofar
AND    opname NOT LIKE '%aggregate%'
AND    opname like 'RMAN%';

Select    sid,    spid,    client_info,    event,    seconds_in_wait,    p1, p2, p3
 from    v$process p,    v$session s
 where    p.addr = s.paddr
 and    client_info like 'rman channel=%';

Pour suivre le status d’un job de backup avec RMAN :

REM RMAN Progress
alter session set nls_date_format='dd/mm/yy hh24:mi:ss'
/
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

REM RMAN wiats
set lines 120
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
SELECT SID, SPID, CLIENT_INFO, event, seconds_in_wait secs, p1, p2, p3
  FROM V$PROCESS p, V$SESSION s
  WHERE p.ADDR = s.PADDR
  and CLIENT_INFO like 'rman channel=%'
/

This script by Osama Mustafa will monitor the progress of a running RMAN job:

Une autre requête pour suivre l’avancement d’un job de backup RMAN :

select    to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
   sofar,
   totalwork,
   elapsed_seconds/60 "ELAPSE (Min)",
   round(sofar/totalwork*100,2) "Complete%"
from    sys.v_$session_longops
where  compnam = 'dbms_backup_restore';

Requête ASM pour suivre l'opération du rebalance

 select INST_ID, GROUP_NUMBER, OPERATION, PASS, STATE, ACTUAL, EST_MINUTES, EST_RATE  from  gv$asm_operation where state = 'RUN'

actuellement : 

   INST_ID GROUP_NUMBER OPERATION       PASS                        STATE            ACTUAL EST_MINUTES   EST_RATE

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

         2            1 REBAL           REBALANCE                   RUN                   4         288       5476

Normalement le ESt_MINUTES doit baisser, une fois le rebalance est fini il n'y aura aucune ligne 

Les sessions avec des LOCKs

Requête pour avoir des infos sur les sessions qui lock :


select blocking_session,blocked_session,script from
( select distinct
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null, s2.LAST_CALL_ET/60)
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;


select LAST_CALL_ET/60 , blocking_session,blocked_session,script from
( select distinct s2.LAST_CALL_ET,
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null )
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;

Automatically and Manual Purging Alert Log and Trace files with Automatic Diagnostic Repository Command Interpreter (ADRCI)

Sometimes we are facing with space related issues due to the huge number of trace file generation. Automatic purging can help us in this situations if set short retention time for trace files.

adrci> show homes
ADR Homes:
diag/tnslsnr/GMCLIMODA01N2PLP/listener_scan2
diag/tnslsnr/GMCLIMODA01N2PLP/asmnet1lsnr_asm
diag/tnslsnr/GMCLIMODA01N2PLP/mgmtlsnr
diag/tnslsnr/GMCLIMODA01N2PLP/asmnet2lsnr_asm
diag/tnslsnr/GMCLIMODA01N2PLP/listener_scan1
diag/tnslsnr/GMCLIMODA01N2PLP/listener
diag/apx/+apx/+APX2
diag/crs/gmclimoda01n2plp/crs
diag/crs/172/crs
diag/asm/+asm/+ASM2
diag/rdbms/_mgmtdb/-MGMTDB

adrci>

adrci> set homepath diag/tnslsnr/GMCLIMODA01N2PLP/asmnet1lsnr_asm

adrci> show control

ADR Home = /u01/app/grid/diag/tnslsnr/GMCLIMODA01N2PLP/asmnet1lsnr_asm:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

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

2978292604           720                  8760                 2015-08-13 16:48:09.227855 +02:00                                                 2017-12-24 05:00:02.312601 +01:00        1                    2                    82                   1                    2015-08-13 16:48:09.227855 +02:00

1 rows fetched

adrci>

adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/db12c_stby/db12c_stby:
*************************************************************************
SHORTP_POLICY        LONGP_POLICY         
-------------------- -------------------- 
720                  8760                
1 rows fetched

1. By default retention is 720 hours for short policy and 8760 hours for long policy.
   Short policy include the following files :(Trace files, Core dump files, Packaging information)
   Long policy include the following files:(Incident information, Incident dumps, Alert logs)
2. To change the retention policy :

adrci> set control (SHORTP_POLICY=120); -- 5 days

adrci> set control (LONGP_POLICY=720); -- 1 week


--check changes

adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL; 

ADR Home = /u01/app/oracle/diag/rdbms/db12c_stby/db12c_stby:
*************************************************************************
SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME LAST_MANUPRG_TIME 
-------------------- -------------------- ---------------------------------------- ---------------------------------------- 
120 720 2016-07-07 23:27:37.789953 +05:00 2016-07-16 22:37:52.700123 +05:00 
1 rows fetched

adrci> 

purger l'alert log d'une base on line :
dans l'exemple ici la taille de l'alert log est de 4G :
cat alert_oibko121.log |gzip > alert_oibko121.log.20180129.gz; echo > alert_oibko121.log

résultat :

4.0K    alert_oibko121.log

53M     alert_oibko121.log.20180129.gz

24K     oibko121_cjq0_11163.trc

SCRIPT pour retrouver les infos d'un user : mot de passe et droit

set echo off;

set feedback off;

set heading off;

set verify off;

set linesize 1000

set pagesize 0

set long 20000

set longchunksize 20000

set autoprint on

variable x clob

execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

execute  dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('USER','${_USERNAME}') FROM dual;

declare

no_grant exception;

pragma exception_init( no_grant, -31608 );

begin

:x := dbms_metadata.get_granted_ddl( 'ROLE_GRANT','${_USERNAME}') || ';';

exception

when no_grant then :x := '-- no role grants';

end;

/

declare

no_grant exception;

pragma exception_init( no_grant, -31608 );

begin

:x := dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE','${_USERNAME}');

exception

when no_grant then :x := '-- no default role';

end;

/

declare

no_grant exception;

pragma exception_init( no_grant, -31608 );

begin

:x := dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT','${_USERNAME}');

exception

when no_grant then :x := '-- no system grants';

end;

/

declare

no_grant exception;

pragma exception_init( no_grant, -31608 );

begin

:x := dbms_metadata.get_granted_ddl( 'OBJECT_GRANT','${_USERNAME}');

exception

when no_grant then :x := '-- no object grants';

end;

/

declare

no_grant exception;

pragma exception_init( no_grant, -31608 );

begin

:x := dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA','${_USERNAME}');

exception

when no_grant then :x := '-- no tablespace quota';

end;

/

exit

Purge des stats sur les bases dans SYSAUX

Purge des stats sur les bases en 10g dans SYSAUX

select sum(bytes/1024/1024) Mb, segment_name,segment_type, tablespace_name

from dba_segments

where  tablespace_name = 'SYSAUX'

and segment_name like 'WRI$_OPTSTAT%'

and segment_type='TABLE'

group by segment_name,segment_type,tablespace_name order by 1 asc;

select sum(bytes/1024/1024) Mb, segment_name,segment_type,tablespace_name

from dba_segments

where  tablespace_name = 'SYSAUX'

and segment_name like '%OPT%'

and segment_type='INDEX'

group by segment_name,segment_type,tablespace_name

order by 1 asc;

Affichage de la retention des stats:

select dbms_stats.get_stats_history_retention from dual;

Passage de la retention des stats à 10 jours (par défaut 31):

exec dbms_stats.alter_stats_history_retention(10);

Date des stats les plus anciennes:

select dbms_stats.get_stats_history_availability from dual;

Requêtes SQL pour MOVE TABLE concernées:

select 'alter table '||segment_name||'  move tablespace SYSAUX;'

from dba_segments where tablespace_name = 'SYSAUX'

and segment_name like '%OPT%'

and segment_type='TABLE';

Requêtes SQL pour Rebuild Index concernés:

select 'alter index '||segment_name||'  rebuild online parallel (degree 4);'

from dba_segments

where tablespace_name = 'SYSAUX'

and segment_name like '%OPT%'

and segment_type='INDEX';

Vérif de l'état des index:

select  di.index_name,di.index_type,di.status  from  dba_indexes di where  di.tablespace_name = 'SYSAUX' and di.index_name like '%OPT%' order by 1 asc;


 SELECT t.OWNER "OWNER", t.TABLESPACE_NAME "TABLESPACE", SUM(ROUND(t.BLOCKS*p.value/1024/1024,2)) "SIZE (Mo)"
FROM DBA_SEGMENTS t, v$parameter p
WHERE t.TABLESPACE_NAME <> 'SYSTEM'
AND upper(p.name) = upper('db_block_size')
 GROUP BY t.OWNER, t.TABLESPACE_NAME

ORDER BY 3;