impdp导入时卡住ORA-16014 ORA-00312报错处理方法

导入时的部分日志如下：

[oracle@zaibei-db db_transport]$

/home/oracle/db_transport/duporcl_db_impdp.sh

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 10:07:48

2018

Copyright (c) 1982, 2013, Oracle. All rights

reserved.

...

...

Import: Release 11.2.0.4.0 - Production on Thu May 10 10:07:54

2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release

11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application

Testing options

Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully

loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@DUP_ORCL DIRECTORY=exp_dir

DUMPFILE=expdp_full_20180510.dmp SCHEMAS=proenv_prepaid,a_onlinepay

LOGFILE=impdp_20180510.log

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type

DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type

DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type

DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type

DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type

DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type

DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type

DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type

DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

^C

Import> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

^CUDI-00001: user requested cancel of current operation

alter日志中的内容如下：

Thu May 10 10:07:54 2018

XDB installed.

XDB initialized.

Thu May 10 10:07:55 2018

DM00 started with pid=30, OS id=30233, job

SYSTEM.SYS_IMPORT_SCHEMA_01

Thu May 10 10:07:55 2018

DW00 started with pid=31, OS id=30235, wid=1, job

SYSTEM.SYS_IMPORT_SCHEMA_01

Thu May 10 10:07:56 2018

Starting background process SMCO

Thu May 10 10:07:56 2018

SMCO started with pid=35, OS id=30237

Thu May 10 10:07:56 2018

Thread 1 advanced to log sequence 103 (LGWR switch)

Current log# 1 seq# 103 mem# 0:

/u01/app/oracle/oradata/DUPORCL/onlinelog/group_1.266.973698031

Current log# 1 seq# 103 mem# 1:

/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_1.260.973698033

Thu May 10 10:07:56 2018

Archived Log entry 103 added for thread 1 sequence 102 ID

0x6425e2bb dest 1:

Thread 1 cannot allocate new log, sequence 104

Checkpoint not complete

Current log# 1 seq# 103 mem# 0:

/u01/app/oracle/oradata/DUPORCL/onlinelog/group_1.266.973698031

Current log# 1 seq# 103 mem# 1:

/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_1.260.973698033

Thread 1 advanced to log sequence 104 (LGWR switch)

Current log# 2 seq# 104 mem# 0:

/u01/app/oracle/oradata/DUPORCL/onlinelog/group_2.267.973698035

Current log# 2 seq# 104 mem# 1:

/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_2.261.973698035

Thu May 10 10:08:01 2018

Errors in file

/u01/app/oracle/diag/rdbms/duporcl/duporcl/trace/duporcl_arc3_29764.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824

bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery

area:

1. Consider changing RMAN RETENTION POLICY. If you are using

Data Guard,

then consider changing

RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using

RMAN

BACKUP RECOVERY AREA

command.

3. Add disk space and increase db_recovery_file_dest_size

parameter to

reflect the new

space.

4. Delete unnecessary files using RMAN DELETE command. If an

operating

system command was used

to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED

commands.

************************************************************************

ARC3: Error 19809 Creating archive log file to

'/u01/app/oracle/fast_recovery_area/DUPORCL/archivelog/2018_05_10/o1_mf_1_103_%u_.arc'

ARCH: Archival stopped, error occurred. Will continue

retrying

ORACLE Instance duporcl - Archival Error

ORA-16038: log 1 sequence# 103 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1:

'/u01/app/oracle/oradata/DUPORCL/onlinelog/group_1.266.973698031'

ORA-00312: online log 1 thread 1:

'/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_1.260.973698033'

Thu May 10 10:08:02 2018

ARCH: Archival stopped, error occurred. Will continue

retrying

ORACLE Instance duporcl - Archival Error

ORA-16014: log 1 sequence# 103 not archived, no available

destinations

ORA-00312: online log 1 thread 1:

'/u01/app/oracle/oradata/DUPORCL/onlinelog/group_1.266.973698031'

ORA-00312: online log 1 thread 1:

'/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_1.260.973698033'

Thread 1 cannot allocate new log, sequence 105

Checkpoint not complete

Current log# 2 seq# 104 mem# 0:

/u01/app/oracle/oradata/DUPORCL/onlinelog/group_2.267.973698035

Current log# 2 seq# 104 mem# 1:

/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_2.261.973698035

ORACLE Instance duporcl - Cannot allocate log, archival

required

Thread 1 cannot allocate new log, sequence 105

All online logs need archiving

Examine archive trace files for archiving errors

Current log# 2 seq# 104 mem# 0:

/u01/app/oracle/oradata/DUPORCL/onlinelog/group_2.267.973698035

Current log# 2 seq# 104 mem# 1:

/u01/app/oracle/fast_recovery_area/DUPORCL/onlinelog/group_2.261.973698035

[oracle@zaibei-db db_transport]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 10:16:03

2018

Copyright (c) 1982, 2013, Oracle. All rights

reserved.

SQL> conn / as sysdba;

Connected.

检查job

SELECT * FROM dba_datapump_jobs;

检查flash recovery area的使用情况

SET LINESIZE 200

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE  PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

NUMBER_OF_FILES

-------------------- ------------------

------------------------- ---------------

CONTROL FILE  0 0 0

REDO LOG  0 0 0

ARCHIVED LOG  4.28 0

1

BACKUP PIECE  0 0 0

IMAGE COPY  0 0 0

FLASHBACK LOG  94.89 0  25

FOREIGN ARCHIVED LOG  0 0 0

7 rows selected.

SQL> show parameter DB_RECOVERY;

NAME  TYPE VALUE

------------------------------------ -----------

------------------------------

db_recovery_file_dest  string /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size  big integer 1G

修改FLASH_RECOVERY_AREA的空间修改为4GB，修改前确认磁盘有足够空间

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G

SCOPE=both;

现在来清理一下archivelog归档日志，生产环境建议备份.

查询日志目录位置

show parameter recover;

删除归档日志,USERDB是数据库实例名

cd /u01/app/oracle/fast_recovery_area/duporcl/archivelog

使用root帐户删除该目录下的文件或者备份其它地方

使用rman 操作,内容太多，只做部分截图

[oracle@userbeta archivelog]$ rman

RMAN> connect target sys/sys_passwd

crosscheck backup;

delete obsolete;

delete expired backup;

crosscheck archivelog all;

delete expired archivelog all;

//此步会有提示，输入 YES 回车

host; //退出rman

确认是否操作成功

SET LINESIZE 200

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE  PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

NUMBER_OF_FILES

-------------------- ------------------

------------------------- ---------------

CONTROL FILE  0 0  0

REDO LOG  0 0  0

ARCHIVED LOG  4.34 0

4

BACKUP PIECE  0 0  0

IMAGE COPY  0 0  0

FLASHBACK LOG  26.16 0  27

FOREIGN ARCHIVED LOG  0 0  0

再次用impdp导入

[oracle@zaibei-db db_transport]$

/home/oracle/db_transport/duporcl_db_impdp.sh

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 10:37:49

2018

Copyright (c) 1982, 2013, Oracle. All rights

reserved.

...

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s)

at Thu May 10 10:39:10 2018 elapsed 0 00:01:15

!!!The end!!!