Forum Stats

  • 3,872,915 Users
  • 2,266,490 Discussions
  • 7,911,382 Comments

Discussions

impdp takes very long ... Continuous Checkpoint not complete,resize system file

Ken18
Ken18 Member Posts: 142 Red Ribbon

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



Answers

  • asahide
    asahide Expert Technical Engineer Member Posts: 1,492 Gold Trophy

    Hi,

    I think that you can increase db_writer_processes.

    If you can, please check it up

    Regards,

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,927 Red Diamond

    Datapump uses streams. Check:

    show parameter streams_pool_size
    

    SY.

    asahide
  • User_GSQTY
    User_GSQTY Member Posts: 133 Silver Badge
    edited Sep 25, 2022 4:19PM

    Very likely your import hanged because UNDO tablepspace was out of space . You can verify alert log to see the date and time when that error was written. So you can determine how long your import was stopped. You need to substract that time (when import hang completely) from your total import operation until now .

    You need to monitor your alert log and import log continously to detect potential new errors.

    You can also monitor dba_segments to see how fast the import goes. For example your import log says "import table ABC" , you then monitor to see how that segment goes

    select segment_name, bytes from dba_segments where owner = <OwnerName> and segment_name = 'ABC' ;
    

    You also want to monitor IO wait on your file system especially on mount point(s) that has oracle datafiles.

  • User_GSQTY
    User_GSQTY Member Posts: 133 Silver Badge
    edited Sep 25, 2022 4:58PM

    Your database log switch is very fast. ( 2022-09-25T07:22:12.649377+00:00 - 2022-09-25T07:22:12.670892+00:00 )

    You might want to add new bigger online redo log : for example double their size to see . You need increase the time of log switch between 2 groups : what I mean is to reduce the number of log switch / elapse of time . The less often log switch, the better it is for your import.

    If I'm not mistaken your db log switch occurs within second and it's about 500mb / second ; this also mean that the import is going fast.