Thursday, September 6, 2012

Dataguard Administration

SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );

-- Check that Archive Logs are being Shipped
-- This query needs to be run on the Primary database

 SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);



Find archive generation over last one month


SELECT  TRUNC(FIRST_TIME)
,       COUNT(*)
FROM    V$ARCHIVED_LOG
WHERE   FIRST_TIME BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
AND   DEST_ID = 1
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1

To check the gap history (last 24 hours) in DR run the below query:


select thread#, sequence#, to_char(next_time,'DD-HH24:MI'), to_char(completion_time,'DD-HH24:MI'), round((completion_time-next_time)*24*60) as delta_minutes from v$archived_log where completion_time>SYSDATE-1 and (completion_time-next_time)*24*60>30;


Run the primary and see the any Gap in the sequence

select thread#, applied, max(sequence#) from gv$archived_log group by thread#, applied order by thread#, applied desc;

 Last applied and Last received status

select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log 
 where sequence# = (select max(sequence#) from v$archived_log where applied='YES') union select 'Last received : 
' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);


- Verify that the last sequence# received and the last sequence# applied to standby
-- database.

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;

The following query
can be used on the primary database to identify peak archive times for a specified day:

SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME)

Archive log gaps can be monitored by examining the low and high sequence numbers in the
V$ARCHIVE_GAP view, as shown here:

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;

When you detect a redo transport delay, execute the following query on the primary database
to identify archive logs that have not made it to the standby destination:

SELECT L.THREAD#, L.SEQUENCE#
FROM
(SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1) L
WHERE L.SEQUENCE# NOT IN
(SELECT SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=2
AND THREAD# = L.THREAD#);

Archive log genarating per hour

select day,  MAX(DECODE(thread#, 1, switches_per_thread, NULL)) thread_1,  MAX(DECODE(thread#, 2, switches_per_thread, NULL)) thread_2,MAX(DECODE(thread#, 3, switches_per_thread, NULL)) thread_3 from (select trunc(first_time) day, thread#, sum(1) switches_per_thread from v$log_history group by trunc(first_time), thread#) group by day order by 1

how to calculate the amount of redo per day and thread.  To find out, use this query:

SELECT TRUNC(first_time),  thread#,  ROUND(SUM(blocks * block_size) / 1024/ 1024,2) m FROM v$archived_log WHERE dest_id = 1 GROUP BY TRUNC(first_time),  thread #ORDER BY 1

standby logs are being used by running following query :



set lines 155 pages 9999
col thread# for 9999990
col sequence# for 999999990
col grp for 990
col fnm for a50 head "File Name"
col "Fisrt SCN Number" for 999999999999990
break on thread
# skip 1
select a.thread#
,a.sequence#
,a.group# grp     
, a.bytes/1024/1024 Size_MB     
,a.status     
,a.archived     
,a.first_change# "First SCN Number"     
,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"   
,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"  from
 v$standby_log a  order by 1,2,3,4
 /



column name format a45 trunc
set lines 300
set pages 999
set trimspool on
column dest_id format 9999
column thread# format 9999
alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
select sequence#, name, dest_id, thread#, first_time,
round(sum((blocks*block_size/1024/1024))) mbytes
from v$archived_log
where first_time >=trunc(sysdate-3)
group by sequence#, name, dest_id, thread#, first_time
order by sequence# desc, dest_id asc;


PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT
col time format a40
select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);


PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;




SELECT 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;
Posted by at No comments:

EManager scripts to monitor

EManager scripts to monitor

set linesize 200
col BEGIN_INTERVAL_TIME format a70
select * from (select snap_id,begin_interval_time from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3;
---------------------------------------------
Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
and a.description like '%4782%'
Order by b.impact, d.rank;
----------------------------------------------------------- cat

get_addm_report.sql which gets each task from the last snapshot from dba_advisor_tasks

set long  10000000
set pagesize 50000
column get_clob format a80

select dbms_advisor.get_task_report (task_name) as ADDM_report
from dba_advisor_tasks
where task_id = (
        select max(t. task_id)
        from dba_advisor_tasks t, dba_advisor_log l
        where t.task_id = l.task_id
        and t.advisor_name = 'ADDM'
        and l.status = 'COMPLETED');


---------------------------------------------------------------
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
sqlplus -S / as sysdba <<oogy
set echo off
set lines 100
set pages 200
set trimspool on
set termout off
set feedback off
column dcol new_value mydate noprint
select to_char(sysdate,'YYMMDD') dcol from dual;
spool /home/oraprd/scripts/dbreport_$1_&mydate..txt
ttitle 'Average Active Sessions in the last week: Instance $1'
column sample_hour format a16
select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour
,    round(avg(sub1.on_cpu),1) as cpu_avg
,    round(avg(sub1.waiting),1) as wait_avg
,    round(avg(sub1.active_sessions),1) as act_avg
,    round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - 7
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24');

ttitle 'Most expensive queries in the last week: Instance $1'
-- gets most expensive queries
-- (by time spent, change "order by" to use another metric)
-- after a specific date
select
   sub.sql_id,
   sub.seconds_used,
   sub.executions,
   sub.gets
from
   ( -- sub to sort before rownum
     select
        sql_id,
        round(sum(elapsed_time_delta)/1000000) as seconds_used,
        sum(executions_delta) as executions,
        sum(buffer_gets_delta) as gets
     from
        dba_hist_snapshot natural join dba_hist_sqlstat
     where
        begin_interval_time > sysdate - 7
     group by
        sql_id
     order by
        2 desc
   ) sub
where
   rownum < 30
;

set long 32768
ttitle 'Text for most expensive SQL in the last week: Instance $1'
select sql_text
from dba_hist_sqltext
where sql_id =
(
select sub.sql_id
from
   ( -- sub to sort before rownum
     select
        sql_id,
        round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
        sum(executions_delta) as execs_since_date,
        sum(buffer_gets_delta) as gets_since_date
     from
        dba_hist_snapshot natural join dba_hist_sqlstat
     where
        begin_interval_time > sysdate - 7
     group by
        sql_id
     order by
        2 desc
   ) sub
where
   rownum = 1
);
spool off;
exit
 
Posted by at No comments:

User Privilege

User Privilege

SELECT USERNAME, ACCOUNT_STATUS,CREATED,profile FROM DBA_USERS WHERE USERNAME ='&A';

SELECT USERNAME, ACCOUNT_STATUS,CREATED FROM DBA_USERS WHERE USERNAME LIKE '%&A%';

SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='&A';
select owner,table_name, privilege from dba_tab_privs where grantee='&a';

select * from dba_sys_privs where grantee='&A';

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='&A';


SELECT DISTINCT GRANTEE,OWNER,TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS
WHERE OWNER='NORSNAPADM' AND GRANTEE LIKE 'SELECT_%_%'
AND TABLE_NAME IN ('DEALER_PROFILE');

------------------------------------------------------
select * from dba_role_privs
where granted_role in ('ADMIN_ROLE','CONNECT','RESOURCE') and
grantee not in ('SYS','SYSTEM');
Log on  in trigger

CREATE OR REPLACE TRIGGER sysadm_logon_trg_jay
after logon on Raj.schema
BEGIN
  EXECUTE IMMEDIATE
          'declare begin ' ||
          'dbms_application_info.set_client_info ( 101 ); end;';
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =Scott';
end;
/


select * from DBA_SYS_PRIVS -- users

select * from ROLE_SYS_PRIVS -- roles

select * from DBA_TAB_PRIVS -- obj

System Privileges:
select * from DBA_SYS_PRIVS where GRANTEE='USERNAME';
Object Privileges:
select * from DBA_TAB_PRIVS where GRANTEE='USERNAME'


select
     lpad(' ', 2*level) || granted_role "User, his roles and privileges"
    from
     (
     /* THE USERS */
       select
        null     grantee,
        username granted_role
        from
         dba_users
      where
         username like upper('%&enter_username%')
   /* THE ROLES TO ROLES RELATIONS */
     union
       select
         grantee,
        granted_role
         from
        dba_role_privs
    /* THE ROLES TO PRIVILEGE RELATIONS */
      union
       select
        grantee,
          privilege
      from
        dba_sys_privs
    )
   start with grantee is null
  connect by grantee = prior granted_role



select grantee, granted_role
from dba_role_privs drp
where drp.grantee in (select username from dba_users)
order by granted_role


Script: To clone or Duplicate Oracle User

select username from dba_users order by username
/
undefine user
accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '
select username, created from dba_users
where  lower(username) = lower('&newuser')
/
accept poo prompt 'Continue? (ctrl-c to exit)'
spool D:\TEST\clone.sql
select 'create user ' || '&newuser' || ' identified by ' || '&passwd' ||
       ' default tablespace ' || default_tablespace ||
       ' temporary tablespace ' || temporary_tablespace || ';' "user"
from   dba_users
where  username = '&userid'
/
select 'alter user &newuser quota '||
       decode(max_bytes, -1, 'unlimited', ceil(max_bytes / 1024 / 1024) || 'M') ||
       ' on ' || tablespace_name || ';'
from   dba_ts_quotas where username = '&&userid'
/
select 'grant ' ||granted_role || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from   dba_role_privs where  grantee = '&&userid'
/
select 'grant ' || privilege || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from   dba_sys_privs where  grantee = '&&userid'
/
spool off
undefine user
set verify on
set feedback on
set heading on
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL>@D:\TEST\clone.sql

Posted by at No comments:

ASM Administration

ASM Administration

select path, disk_number, mount_status, header_status, mode_status, state, name,label from v$asm_disk
Select mount_status, header_status, mode_status, state, total_mb, free_mb, name, path, create_date from v$asm_disk
set wrap off
set lines 120
set pages 999
col “Group Name”   form a25
col “Disk Name”    form a30
col “State”  form a15
col “Type”   form a7
col “Free GB”   form 9,999
 select group_number  "Group",  name "Group Name",state  "State" ,type  "Type",total_mb/1024 "Total GB" , free_mb/1024  "Free GB" from   v$asm_diskgroup
 /
col “Instance” form a8
 select c.group_number  "Group"
 ,      g.name          "Group Name"
 ,      c.instance_name "Instance"
 from   v$asm_client c
 ,      v$asm_diskgroup g
 where  g.group_number=c.group_number

/


select *
from   v$asm_operation
/
select header_status , mode_status, path from V$asm_disk where header_status in ('FORMER','CANDIDATE');

/
ol “Instance” form a8
 select c.group_number  "Group"
 ,      g.name          "Group Name"
 ,      c.instance_name "Instance"
 from   v$asm_client c
 ,      v$asm_diskgroup g
 where  g.group_number=c.group_number

/


select *
from   v$asm_operation
/
select header_status , mode_status, path from V$asm_disk where header_status in ('FORMER','CANDIDATE');

/
col "Group"          form 999
 col "Disk"           form 999
 col "Header"         form a9
 col "Mode"           form a8
 col "Redundancy"     form a10
 col "Failure Group"  form a10
 col "Path"           form a19
 
select group_number  "Group"
 ,      disk_number   "Disk"
 ,      header_status "Header"
 ,      mode_status   "Mode"
 ,      state         "State"
 ,      redundancy    "Redundancy"
 ,      total_mb      "Total MB"
 ,      free_mb       "Free MB"
 ,      name          "Disk Name"
 ,      failgroup     "Failure Group"
 ,      path          "Path"
 from   v$asm_disk  order by group_number,disk_number
spool asm1.html 
SET MARKUP HTML ON 
set echo on 
set pagesize 200 
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; 
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual; 
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%'; 
select * from v$asm_diskgroup; 
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER; 
SELECT * FROM V$ASM_CLIENT; 
select * from V$ASM_ATTRIBUTE; 
select * from v$asm_operation; 
select * from gv$asm_operation; 
select * from v$version; 
show parameter asm 
show parameter cluster 
show parameter instance_type 
show parameter instance_name 
show parameter spfile 
show sga 
spool off 
exit 


sqlplus "/ as sysasm" 

spool /tmp/asm.out 
set pages 1000 
set linesize 160 
col path format a45 
col name format a35 
select group_number,disk_number,name,path,state,header_status,mode_status,mount_status from v$asm_disk order by 1,2,3; 
select failgroup,header_status,count(*) from v$asm_disk group by failgroup,header_status order by 1,2; 
spool off 
Posted by at No comments:

Resolve the reasons for the hung database

 resolve the reasons for the hung database

How to Collect Diagnostics for Database Hanging Issues [ID 452358.1]


use the command sqlplus -prelim "/ as sysdba" to log in with the -prelim
option.
execute the oradebug hanganalyze command to analyze a hung
database
SQL>oradebug hanganalyze 3
In RAC
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
oradebug hanganalyze command a couple of times to generate dump files for
varying process states.
get a systemstate dump from a non-RAC system by executing the following set of
commands.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL>
Issue the following commands to get a systemstate dump in a RAC environment:
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all dump systemstate 266
take the systemstate dumps a few times,

You can also gather a hanganalyze trace file to identify hung sessions with SQL*Plus when connected as SYSDBA:

SQL> oradebug hanganalyze 3
Wait at least 2 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3
Open a separate SQL session and immediately generate a system state dump.
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';
You can also gat a trace dump to identify hung session details in Oracle Real Application Clusters (RAC) as follows:
SQL> oradebug setmypid
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
... Wait at least 2 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3

Non-RAC:
Sometimes, database may actually just be very slow and not actually hanging. It is therefore recommended,  where possible to get 2 hanganalyze and 2 systemstate dumps in order to determine whether processes
are moving at all or whether they are "frozen".
Hanganalyze
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
oradebug tracefile_name
exit
Systemstate
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug dump systemstate 266
oradebug tracefile_name
exit
Posted by at No comments:

Tuesday, September 4, 2012

ORA-19809: limit exceeded for recovery files

To verify this run the following query. It will show the size of the recovery area and how full it is:

set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used  / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/

 To fix the problem, you need to either make the flash recovery area larger, or remove some files from it.

If you have the disk space available, make the recovery area larger:
 alter system set db_recovery_file_dest_size=<size> scope=both
/


In RAC

ALTER SYSTEM SET db_recovery_file_dest_size='6G' SCOPE=BOTH SID='*';

 qlplus "/as sysdba"

archive log list;

!df -h or !df -kgt

show parameter db_recovery;

NAME                      TYPE         VALUE
----                      ----         -----
db_recovery_file_dest     string       +FLASHDG
db_recovery_file_dest_size big integer 150G

if +FRA,
select * from V$FLASH_RECOVERY_AREA_USAGE;
(see what kind of files are available in the Flash Recovery Area)

set pages 9999 lines 300
col name format a40
select name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---- ----------- ---------- ----------------- ---------------


if SPACE_USED is full, then increase the db_recovery_file_dest_size

rman target /
crosscheck archivelog all;
delete expired archivelog all;
crosscheck backup;

SQL>alter system set db_recovery_file_dest_size=200G scope=both;
System altered.


rman target /

show all;
LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1'; ------List all archivelog backups for the past 24 hours
list backup of archivelog all completed before 'sysdate -1';

list backup of archivelog all;
list backup of archivelog all tag 'TAGXXX';
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';


crosscheck archivelog all;
list expired archivelog ALL;
delete expired archivelog all;

delete obsolete device type disk;
crosscheck backup;
delete expired backup device type disk;
delete expired backupset device type disk;


ls -ltr archivebackup.cmd

nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &
nohup: appending output to `nohup.out'

tail -f archivebackup_dbname_DDMONYY.log

+FRA/+RECO01 filled with archivelogs
====================================

Backup archivelogs to disk
--------------------------
run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}

Backup archivelogs to disk (specified location,if there is no space to take bakup at archive default location)
--------------------------------------------------------------------------------------------------------------
RUN
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL format '/location/arch_%d_%p_%s.rman' FILESPERSET 10 DELETE INPUT;
}

Backup archivelogs to tape (PROD Environment)
--------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}

+FRA filled with Bacupsets (PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;
}

if required,
BACKUP BACKUPSET ALL FILESPERSET 10 DELETE INPUT;

Delete BACKUPSET backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-14' DEVICE TYPE DISK;

+FRA filled with Bacupsets (NON-PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
DELETE BACKUPSET COMPLETED BEFORE 'SYSDATE-4';
}

Delete archivelogs backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;

Delete archivelogs backed up to tape/disk (NON-PROD Environment)
-----------------------------------------
DELETE archivelog all BACKED UP 1 TIMES TO DEVICE TYPE DISK;

list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
delete archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';

backup archivelog [all] [until time 'sysdate']| [sequence between 100 to 110 thread 1] as filesperset 5 delete

input;

backup archivelog from sequence 100 until sequence 110 thread 1 delete input;

backup archivelog from logseq 100 until logseq 110 thread 1 delete input;

report obsolete;
DELETE OBSOLETE REDUNDANCY = 3;
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;
Posted by at No comments:
Subscribe to: Posts (Atom)

About Me

My photo
Sydney, NSW, Australia
An experienced IT professional (14+ years) worked in multiple industries such as consulting, education, financial, retail sectors. Carries good work experience in relational database design, analysis, modeling, development, administration, implementation, trouble shooting, support etc. Experienced in Oracle/SQL Server/MySQL DBA involving setup, configuration, tuning, backups, disaster recovery, high availability Oracle 11g/12C/19C RAC clusters; SQL server 2008/2012/2016 clusters, Oracle Engineered Systems such as EXADATA, ODA and Oracle and Azure Cloud. Performed Software Installations, Migrations, Database Capacity Planning, Automation of backup implementation, Cloud migration Tuning Oracle in windows/Unix platforms. In addition, experienced in UNIX administration, Shell scripting. PowerShell scripting A team player with communication skills.