Wednesday 10 June 2015

Completed checkpoint up to RBA [0x1a1fd.2.10]

In my case using this because, I have oracle standard edition oracle software and implement manual standby sync from main server with the gap of 2 min. Main server frequently log switch giving error message on alert.log ,"CHECKPOINT NOT COMPLETED" and some process will be hang  check the status of logfile,

V$log show 2 ACTIVE and 1 CURRENT.

Parameter archive_lag_target=120   # means every 2 minute switch a log.


# log transfer with the help of shell script and os cron jobs.
crontab -l
*/2 * * * * /home/oracle/SYNC_LOG/sync.sh

echo "--------=======>>>> Sync start At <<<<<<<<======-----" >> /home/oracle/sync.log
date>>/home/oracle/sync.log
rsync -e ssh -Pazv /u04/archivedup/*.dbf 10.128.44.61:/u04/archivedup >> /home/oracle/sync.log
echo "--------=======>>>> Sync End at <<<<<======---- " >> /home/oracle/sync.log
date>>/home/oracle/sync.log
echo " ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" >> /home/oracle/sync.log



Then i have decide checkpoint tunning below the steps.


1.  Set some parameter 

log_checkpoint_interval              integer       120
log_checkpoint_timeout               integer      140
log_checkpoints_to_alert             boolean     TRUE
archive_lag_target                        integer       120


---- checkpoint Every 2 minutes + generate archive log every 2 minute.
alter system set log_checkpoint_interval=140 scope=both;
alter system set log_checkpoint_timeout=180 scope=both;
alter system set log_checkpoints_to_alert=TRUE scope=both;

alter system set archive_lag_target=120 scope=both;


2.  Add more online redo logfile.


3. alert.log throw noising message in case set manual checkpoint.


ALTER SYSTEM SET log_checkpoint_timeout=140 SCOPE=BOTH;
Sat May 23 16:13:51 2015
Completed checkpoint up to RBA [0x1a1fd.2.10], SCN: 2215100085321
Completed checkpoint up to RBA [0x1a1fc.2.10], SCN: 2215100085254
Completed checkpoint up to RBA [0x1a1fb.2.10], SCN: 2215100085153
Completed checkpoint up to RBA [0x1a1fa.2.10], SCN: 2215100085095
Completed checkpoint up to RBA [0x1a1f9.2.10], SCN: 2215100085015
Completed checkpoint up to RBA [0x1a1f8.2.10], SCN: 2215100084739
Sat May 23 16:14:00 2015
Incremental checkpoint up to RBA [0x1a1fd.2b.0], current log tail at RBA [0x1a1fe.18.0]
Sat May 23 16:15:15 2015
Beginning log switch checkpoint up to RBA [0x1a1ff.2.10], SCN: 2215100085452
Thread 1 advanced to log sequence 107007
  Current log# 8 seq# 107007 mem# 0: /u01/app/oracle/oradata/sarathi/redo08.log
Sat May 23 16:15:30 2015
Completed checkpoint up to RBA [0x1a1fe.2.10], SCN: 2215100085395





RELATED NOTE:

Diagnosing Excessive Redo Generation - Limit with Nologging (Doc ID 199298.1)
SQL: How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1)
Diagnosing Excessive Redo Generation - Limit with Nologging (Doc ID 199298.1)

No comments: