Re: Checkpoint not complete error
Date: Wed, 29 Apr 2009 08:12:31 -0700 (PDT)
It is better to start with understanding which job is causing high redo generation and optimize that job before making changes to the redo log file size, checkpoint related init parameters, etc.
select parsing_user_id, command_type, sum(executions), sum(rows_processed) from V$sql where command_type not in (0, 3) group by parsing_user_id, command_type order by 4;
Above query can give you high level view about amount and frequency of changes happening on your system.
things to check:
- Check if you have any LOBS and DML's on LOBS, if their data is not needed on DR standby, make them nocache and nologging
- Check if you have any batch jobs doing mass inserts or deletes, if so, look into the possibility of dropping index, DML, and creating index.
- If you are creating any temporary or staging tables, check if they can be made nologging or global temporary tables.
- Convert statements like, delete * from emp, to truncate table emp; ......etc..
You can also look into top waitevents, top sql, etc from awr report and go from there.