Sunday 30 March 2014

AIX NFS mount: ORA-39000: bad dump file specification

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/mksysb/dumps/HILALB_30032014.dmp" for read
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 2


Solution:
mount -o hard,rw,noac,rsize=32768,wsize=32768,proto=tcp,vers=3 nim_server1:/mksysb /mksysb

Thursday 13 March 2014

ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

Solution:

SQL> alter database clear unarchived logfile group 1;
SQL> alter database clear unarchived logfile group 2;
SQL> alter database clear unarchived logfile group 3;
SQL>  alter database open RESETLOGS;

Monday 3 March 2014

Oracle Database: Session PGA Memory Usage




A helpful script to find sessions using large amounts of system memory:
SET LINESIZE 140
SET PAGESIZE 100
COL session      HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB'     FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
SELECT TO_CHAR(ssn.sid, '9999') || ' - ' || 
       NVL(ssn.username, NVL(bgp.name, 'background')) || ' - ' ||
       NVL(lower(ssn.machine), ins.host_name) "SESSION",
       TO_CHAR(prc.spid, '999999999') "PID/THREAD",
       se1.value/1024/1024 current_size,
       se2.value/1024/1024 maximum_size
 FROM  v$sesstat se1, 
       v$sesstat se2, 
       v$session ssn, 
       v$bgprocess bgp, 
       v$process prc,
       v$instance ins,  
       v$statname stat1, 
       v$statname stat2
 WHERE se1.statistic# = stat1.statistic# 
   AND stat1.name = 'session pga memory'
   AND se2.statistic# = stat2.statistic# 
   AND stat2.name = 'session pga memory max'
   AND se1.sid = ssn.sid
   AND se2.sid = ssn.sid
   AND ssn.paddr = bgp.paddr (+)
   AND ssn.paddr = prc.addr  (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = ''
ORDER BY 3 desc

Number of Visitors