How To Diagnose And Troubleshoot Import Or Datapump Import Hung Scenarios [ID 795034.1]
|
|
|
Modified 10-JUN-2009Type HOWTOStatus PUBLISHED
|
|
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7
Information in this document applies to any platform.
This article discusses theimport hung (hanging) scenario and the method to diagnose and troubleshootsuch issues.
Please make a note that this article is applicable only to import or datapump importhanging issue and not to the entire databasehanging case.
The example given in this article demonstrates import was waiting for a particular session which was blocking the import activity and killing that session helped resolving the issue.
( The entire action plan below should be executed as "sysdba" )
A) Please make sure that this is the import (datapump import ) hanging scenario ( Entire database is "not" hanging).
Also make sure that you are able to connect to the database using SQLPLUS ( as sysdba )
B)
i) Make sure DBA_REGISTRY is clean i.e. all the components are valid.
SELECT COMP_NAME , VERSION , STATUS FROM DBA_REGISTRY;
ii) Make sure there are "no" invalid objects in this database.
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';
iii) Please execute utlrp script in ORACLE_HOME/rdbms/admin to compile all invalid objects and make them valid.
C) Check if the same export dump file can be imported on source database itself or other database such as test or not.
D) For IMPDP ( Datapump Import ) , check if ACCESS_METHOD=DIRECT_PATH works or not.
Note 552424.1
Title: Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ?
E) If you can restart the instance , please try restarting the instance and check if that resolves the case.
F) If you can not restart the instance , please flush the shared pool and buffer cache by following command and then check.
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
G) Check Import log and see at which object import is hanging on. Check if you can avoid that object being imported using methods like EXCLUDE in IMPDP or specify other objects in parameter such as TABLES in case of conventional IMPORT.
H) Check if you can import the dumpfile into new schema ( create new test schema and try importing into it ).
I) If nothing above helps then please set the 10046 trace at level 12 to get more details during IMPORT
You can refer
1)Note 21154.1 Subject: EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
2)Note 1058210.6 Subject: How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug
J) Please take TKPROF output of 10046 trace file and check if you are hitting any known bug or not.
Note 760786.1 Title: TKProf Interpretation (9i and above)
K) We will generate HANGANALYZE trace files and we will also generate a system state dump if above 10046 trace or TKPROF output is not revealing further details.
Please do this activity couple of times when import is hanging.
Note 175006.1 Title: Steps to generate HANGANALYZE trace files
Note 121779.1 Title: Taking Systemstate Dumps when You cannot Connect to Oracle
( as sysdba )
oradebug setmypid
oradebug unlimit
oradebug dump hanganalyze 3 << execute it when import is hanging
oradebug dump hanganalyze 3 << execute it when import is hanging
oradebug dump systemstate 266
Please monitor the trace file generated during this time.
After generating hanganalyze trace files , it showed following
==============
HANG ANALYSIS:
==============
Found 13 objects waiting for <cnode/sid/sess_srno/proc_ptr/ospid/wait_event>
<0/566/15515/0xc5003fb8/12709/undo segment tx slot>
Please make a note of SID 566 and Operating System PID 12709 in above line
L) Now take the errorstack of the same process. Please do it couple of times
Note 215858.1Title: Interpreting HANGANALYZE trace files to diagnose hanging and performance problems
In our example it is as below
( as sysdba )
oradebug setospid 12709
oradebug unlimit
oradebug dump errorstack 3
Please monitor the trace file generated during this time.
M) Now analyze this trace file which will point to the session which was blocking the Import
Dump file ( 12709.trc ) showed following
====================================================================
Received ORADEBUG command 'unlimit' from process Unix process pid: 14321, image:
Received ORADEBUG command 'dump errorstack 3' from process Unix process pid: 14321, image:
*** 2009-03-21 16:09:05.902
ksedmp: internal or fatal error
Current SQL statement for this session:
update ts$ set name=:2,online$=:3,contents$=:4,undofile#=:5,undoblock#=:6,
blocksize=:7,dflmaxext=:8,dflinit=:9,dflincr=:10,dflextpct=:11,dflminext=:12,dflminlen=:13,owner#=:1
4,scnwrp=:15,scnbas=:16,pitrscnwrp=:17,pitrscnbas=:18,dflogging=:19,bitmapped=:20,inc#=:21,flags=:22
,plugged=:23,spare1=:24,spare2=:25 where ts#=:1
SO
----------
SO: 3c50f2820, type: 4, owner: 3c5003fb8, flag: INIT/-/-/0x00
(session) sid: 566 trans: 3c55a8160, creator: 3c5003fb8, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0025-001B6F27, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 41, prv: 0, sql: ffffffff7b41fc08, psql: 3acf8d470, user: 0/SYS
O/S info: user: oracle, term: pts/107, ospid: 12708, machine: optimus
program: sqlplus@optimus (TNS V1-V3)
application name: sqlplus@optimus (TNS V1-V3), hash value=0
waiting for 'undo segment tx slot' blocking sess=0x0 seq=3781 wait_time=0 seconds since wait
started=2653
----------------------------------------
Cursor#2(ffffffff7b4317d0) state=BOUND curiob=ffffffff7b446f48
curflg=4c fl2=400 par=0 ses=3c50f2820
child cursor: 3
sqltxt(ffffffff7b41fc08)=drop tablespace MCGLS_TEMP including contents and datafiles
hash=00000000000000000000000000000000
parent=ffffffff7b41f988 maxchild=01 plk=ffffffff7b41fde8 ppn=y
cursor instantiation=ffffffff7b446f48 used=1237340686
child#0(ffffffff7b41e5d0) pcs=ffffffff7b41eae8
clk=ffffffff7b41e720 ci=ffffffff7b41e368 pn=ffffffff7b41e7d8 ctx=ffffffff7b41d5a0
kgsccflg=0 llk[ffffffff7b446f50,ffffffff7b446f50] idx=0
xscflg=c0802276 fl2=c000400 fl3=2202008 fl4=100
====================================================================
1) Search the keyword "program" in this 12709.trc file.
2) This will give us state object details ( SO ) which will have SQL details ( sql: ffffffff7b41fc08 )
3) Search this SQL ( "ffffffff7b41fc08") in same trace file ( 12709.trc ) which will give us the complete SQL statement which has blocked the Import.
In this case it was
(sqltxt(ffffffff7b41fc08)=drop tablespace MCGLS_TEMP including contents and datafiles)
N) Now kill this session ( In example sid: 566 ) and then try IMPORT which should work.
-------------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
聊天 群:40132017 聊天2群:69087192
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
分享到:
相关推荐
How to Diagnose and Fix Everything Electronic Fully Revised
Suddenly users complains slow response of database. You look at task manager, ORACLE.EXE is taking high...Unlike UNIX, OSPID is generic for ORACLE.EXE and doesn't represent server processes individually.
Whether you are new to 3D printing, or you have dozens of successful prints under your belt, this book is going to help you! Sean Aranda and David Feeney have hundreds of thousands of successful 3d ...
How to Use AWR Reports to Diagnose Database Performance Issues
Learn how to set up and configure networks to create robust connections, and how to quickly diagnose and repair problems should something go wrong. Whatever version of Windows you are using, you will ...
Starting with an introduction to OpenStack troubleshooting tools, we'll walk through each OpenStack service and how you can quickly diagnose, troubleshoot, and correct problems in your OpenStack....
This book is your toolkit to teach you how to keep your cluster in good health, and show you how to diagnose and treat unexpected issues along the way. You will start by getting introduced to Elastic...
making it perfectly suited for both small projects and large big data warehouses with petabytes of unstructured data.This book is your toolkit to teach you how to keep your cluster in good health, and...
This book is your toolkit to teach you how to keep your cluster in good health, and show you how to diagnose and treat unexpected issues along the way. You will start by getting introduced to Elastic...
Determine if you have performance problems and how to diagnose them Use common performance tools such as Keynote and Gomez Explore web performance challenges outside of the US Dives into mobile web ...
How to Use AWR reports to Diagnose Database Performance Issues
As companies keep their existing hardware and operating systems for more years than ever before, the need to diagnose and repair problems is becoming ever more important for IT Pros and system ...
You will know how to choose the appropriate data structure for a specific parallel algorithm to achieve scalability and performance. Further, you'll learn about server scalability, asynchronous I/O, ...
by former Intel Worldwide Project Management Director and agile expert Rick Freedman, author of Amazon best-seller The IT Consultant, shows how to overcome transition challenges and move beyond team-...
diagnose_enc.tar
This book shows you how to use PowerShell to quickly pinpoint, diagnose, and solve problems with your Windows Server environment, reducing the need for external tools, and ensuring you stay up-to-date...
Use tools like Esxtop to diagnose performance issues and vmkfstools to import and export . Install and Use VMware Scripting APIs Develop programs to help automate and ease administration-even with a ...
you how to connect to and interact with these servers, and even other operating systems, including Mac OS X, Linux, and older variants of Windows, over a local area network. Because of space ...
You will know how to choose the appropriate data structure for a specific parallel algorithm to achieve scalability and performance. Further, you’ll learn about server scalability, asynchronous I/O,...