I see "checkpoint not complete and resizing of system db files continuously in alert log file"
I have 9 redo groups with two members each of 8gb & dropping existing 512m redo's ..
SGA from 15G to 70 G and PGA as well resized optimally
Undo datafiles have been added as cannot extend errors were shown in alert log file
db writer is set to 1 as the CPU count is not more than 8
source has small file tbs but target has bigfile based on the DB Volume which is around 4TB+
DB set in Noarchive mode
Alert log file shows:
Checkpoint not complete
Current log# 7 seq# 2545 mem# 0: /u01/app/oracle/oradata//onlinelog/redo07_1.log
Current log# 7 seq# 2545 mem# 1: /u01/app/oracle/orafra//onlinelog/redo07_2.log
2022-09-25T07:22:12.649377+00:00
Thread 1 advanced to log sequence 2546 (LGWR switch), current SCN: 255570406
Current log# 8 seq# 2546 mem# 0: /u01/app/oracle/oradata//onlinelog/redo08_1.log
Current log# 8 seq# 2546 mem# 1: /u01/app/oracle/orafra//onlinelog/redo08_2.log
2022-09-25T07:22:12.670892+00:00
Resize operation completed for file# 28, fname /u01/app/oracle/oradata//datafile/system04.dbf, old size 2461696K, new size 2527232K
SQL> show parameter check
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string FALSE
db_block_checksum string TYPICAL
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 0
SQL> show parameter mttr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY;
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
0 4625 11185900
log_checkpoints_to_alert boolean FALSE
Environment :
DB : Oracle 19c, Non-CDB
Expdp dumpsize - 577 GB
OS - AWS Ec2 linux
CPU - 4
RAM - 125 GB,
SGA - 70 GB , swap - 15gb
DB Options:
SQL> show parameter pga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 32000M
pga_aggregate_target big integer 16000M
NOTE: Tried with pre-12c -- ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;
# free -g
total used free shared buff/cache available
Mem: 125 2 0 66 121 54
Swap: 15 1 14
#lscpu
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 2
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model name: Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
imp filters/options used,
rerun to speed up : Previous impdp which was taking too long [processing-DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX]
exclude=user
TABLE_EXISTS_ACTION=APPEND
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
metrics=yes
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
EXCLUDE=INDEX,STATISTICS
LOGTIME=ALL
dumpfile=1_exp_01.dmp, -------------- 1_exp_10.dmp
schemas=schema1, ------ schema11
ENCRYPTION_PASSWORD=$y~KG}g6b&
parallel=8
cluster=N
SQL> SELECT OPNAME, SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
F 2 ROM V$SESSION_LONGOPS WHERE OPNAME in
( select d.job_name from v$sess 3 ion s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr )
AND OPNAME NOT LIKE ' 4 5 %aggregate%' AND
TOTALWORK != 0 AND SOFAR <> TOTALWORK; 6
OPNAME SID
---------------------------------------------------------------- ----------
SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ----------
IMP_xxxx 712
29829 0 5747 1788493 .32