Search This Blog

Total Pageviews

Thursday 26 May 2011

LOG_ARCHIVE_MAX_PROCESSES

ORA-16146 [ID 1073217.1]
799353.1
ORA-03135 : connection lost contact when shipping redo log to standby database
ORA-03135 : connection lost contact
ARC2 : Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
ORA-16146



- LOG_ARCHIVE_MAX_PROCESSES -> default value (2) -> least to 4, ->better even to 10 <- more process more PGA_USED_MEM
-- value (2) may cause ORA-16146



select * from V$PGASTAT;



set linesize 200
set pagesize 100
col PROGRAM format a30
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOC(MB)",PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM
FROM V$PROCESS
where PROGRAM like '%ARC%'
order by pga_alloc_mem asc

PROGRAM PGA_USED_MEM ALLOC(MB) FREEABLE PGA_MAX_MEM
------------------------------ ------------ ---------- ---------- -----------
oracle@ln-ora-01 (ARC1) 10863713 21.6902475 0 22743873
oracle@ln-ora-01 (ARC4) 10883529 22.1277475 458752 48171841
oracle@ln-ora-01 (ARC9) 10884577 32.2527475 11075584 48171841
oracle@ln-ora-01 (ARC7) 22419761 43.8152475 0 48171841
oracle@ln-ora-01 (ARC3) 22420553 43.8152475 0 48171841
oracle@ln-ora-01 (ARC2) 22418937 43.8152475 0 48171841
oracle@ln-ora-01 (ARC6) 22417889 43.8152475 0 48171841
oracle@ln-ora-01 (ARC8) 22419761 44.8777475 1114112 48171841
oracle@ln-ora-01 (ARC0) 22461409 44.8777475 1114112 48171841
oracle@ln-ora-01 (ARC5) 22419761 44.8777475 1114112 48171841

10 rows selected.




col ERROR format a20
col ERROR format a20

1* SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2

DESTINATION STATUS ERROR
------------------------------ --------------------------- --------------------
cccprod VALID



SYS AS SYSDBA>alter system set LOG_ARCHIVE_MAX_PROCESSES=4 scope=both ;

System altered.


SYS AS SYSDBA>col PROGRAM format a30
SYS AS SYSDBA>/

PROGRAM PGA_USED_MEM ALLOC(MB) FREEABLE PGA_MAX_MEM
------------------------------ ------------ ---------- ---------- -----------
oracle@ln-ora-01 (ARC1) 10863713 21.6902475 0 22743873
oracle@ln-ora-01 (ARC6) 10883529 43.8152475 0 48171841
oracle@ln-ora-01 (ARC3) 22420553 43.8152475 0 48171841
oracle@ln-ora-01 (ARC7) 10885401 43.8152475 0 48171841
oracle@ln-ora-01 (ARC2) 22418937 43.8152475 0 48171841
oracle@ln-ora-01 (ARC0) 22461409 44.8777475 1114112 48171841
oracle@ln-ora-01 (ARC5) 10885401 44.8777475 1114112 48171841

7 rows selected.



SYS AS SYSDBA>r
1 SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOC(MB)",PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM
2 FROM V$PROCESS
3 where PROGRAM like '%ARC%'
4* order by pga_alloc_mem asc

PROGRAM PGA_USED_MEM ALLOC(MB) FREEABLE PGA_MAX_MEM
------------------------------ ------------ ---------- ---------- -----------
oracle@ln-ora-01 (ARC1) 10863713 21.6902475 0 22743873
oracle@ln-ora-01 (ARC6) 10883529 43.8152475 0 48171841
oracle@ln-ora-01 (ARC3) 22420553 43.8152475 0 48171841
oracle@ln-ora-01 (ARC7) 10885401 43.8152475 0 48171841
oracle@ln-ora-01 (ARC2) 22418937 43.8152475 0 48171841
oracle@ln-ora-01 (ARC0) 22461409 44.8777475 1114112 48171841
oracle@ln-ora-01 (ARC5) 10885401 44.8777475 1114112 48171841

7 rows selected.

No comments:

Oracle DBA

anuj blog Archive