加载中…impdp导入时卡住ORA-16014ORA-00312报错处理方法
(2018-11-14 16:54:48)| 分类： Oracle |
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)
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
Thread 1 advanced to log sequence 104 (LGWR switch)
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,
2. Back up files to tertiary device such as tape using
RMAN
3. Add disk space and increase db_recovery_file_dest_size
parameter to
4. Delete unnecessary files using RMAN DELETE command. If an
operating
************************************************************************
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
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
[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!!!
喜欢
0
赠金笔