impdp 导入在PROC_SYSTEM_GRANT时停滞，不断生成JAVA$POLICY$SHARED$00...

wangqi

Oracle 10.2.0.4 ,  EBS R11i  ,  AIX  ,      要升级到  Oracle 11.2.0.3 ,  EBS R12.1.3 ,  Linux  

其中有一个步骤是按照Oracle EBS 文档 (10g Release 2 ExportImport Process for Oracle Applications Release 11i (文档 ID 362205.1))， 在做一些特殊处理后,   从Oracle 10.2.0.4 (AIX) 导出整库， 然后导入到 Oracle 11.2.0.3 （Linux）下， 导入的过程中出现一些问题：

数据库只有 600多 G,  采用全库导出，全库导入，导入并行 5  ， 导入参数
directory=dmpdir
dumpfile=aexp%U.dmp
parallel=5
full=y
transform=oid:n  
logfile=impebsapps.log  

以上都是按照文档导入参数模板来做的， 只是自己为了提高速度，加入了一个参数 parallel=5 , 其他 都是文档给出的。

导入命令在11.2.0.3 是   impdp   "' / as  sysdba'"  parfile = 参数文件 。 在后台运行。

导入过程中在 Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT 这一步时，等了近3个小时， 没有动静，  SYSTEM 空间不停增加， 查看发现有锁表， lock的object  是 JAVA$POLICY , 还有JAVA$POLICY$SHARED$TABLE ， 眼看还在不停生成 JAVA$POLICY$SHARED$00000XXX 表,  查看源库只有30多个类似的表，在导入的库中有5000多个， kill这个session后，impdp同时也停止了。 因为到这一步 PROC_SYSTEM_GRANT ， 还没有开始导入EBS的表结构及数据，所以可以重新导入，重新导入时候就不会这样了，但是如果恢复一个新的空库，然后再导入，还是会出现这种情况。 查看alert log 没有报错现象， 这个是什么原因呢  ？  Metalink 找了也没有很类似的说明。   

有问过其他论坛上的人，说 可能会在SYSTEM表空间生成临时段 ，但是后来重新恢复新库，重新导入，同样有lock , 查了一下，不是临时段.  


后来测试了AIX 下10.2.0.4 到Linux下 10.2.0.4 的导出导入(也是参考metalink的文档做的，EBS比较特殊，需要用它自己的脚本建立库及表空间等)，同样报这样的问题 。

wangqi

某大牛的回复 .

I don't have a solution to your problem. But these are definitely not temporary tables, which would take the form <file#>.<block#>. If the problem is reproducible, you may want to open an SR.

JAVA$POLICY is the base table under dba_java_policy. JAVA$POLICY$SHARED$00000XXX objects are of type "java data". The XXX is a sequence number (see Doc 415428.1). I guess it's using sequence JAVA$POLICY$SEQUENCE$. They match sys.JAVA$POLICY$SHARED$TABLE.version (convert them to decimal to compare). Dba_java_policy.seq may be using that same sequence. If so, each "java data" entry is a java security policy grant, probably done by exec dbms_java.grant_policy_permission (or grant_permission?).

Are you saying during the import, there're lots of JAVA$POLICY$SHARED$00000XXX but the source database does not have many at all? So it appears the import process itself makes grants, only during the import period?


已提交SR ,  具体回复(目前暂无环境，还未回复)：


The mentioned JAVA$POLICY$SHARED$xxx objects are JVM objects, so we would need to check this DB option in the DB where the problem is being reproduced.
To do so, please follow the indications under:
Script to Check the Status of the JVM within the Database (Doc ID 456949.1)

Also, and given you mentioned the problem can be reproduced, could you please try to reproduce it again and gather the following information?:

(1) To gather more information during the export process, run the impdp command with the following parameters: trace=480300 metrics=yes

(2)Attach event 10046 at level 12 to the DW process/es to trace their executions when the import hangs:
To do so:
(2.1) ALTER SYSTEM SET timed_statistics = TRUE SCOPE = memory;
(2.2) Identify the OSPID of the DW process while the process is hanging.
(2.3) In sqlplus, use oradebug to attach to the DW processes (O)SPID:
SQL> oradebug setospid xxx (xxx = ospid)
SQL> oradebug unlimit
SQL> oradebug Event 10046 trace name context forever, level 12
-- wait 20 minutes
SQL> oradebug Event 10046 trace name context off
SQL> oradebug dump errorstack 10
SQL> oradebug tracefile_name

(3) Gather the output from the following query several times a minute:
.
select s.sid, p.spid, w.event, w.wait_time,w.seconds_in_wait, w.p1,w.p2,w.p3
from v$session s, v$process p, v$session_wait w
where s.paddr=p.addr and s.module='Data Pump Worker' and s.sid=w.sid;


(4) Create standard tkprof output files for Data Pump Master and Worker SQL traces taken in step #2 above, for example:

% cd /oracle/admin/ORCL/BDUMP
% tkprof orcl_dw01_17294.trc tkprof__dw01_nnnn.out waits=y sort=exeela

(5) Upload:
* a copy of the alert.log with ONLY the information from the last database start up to the time of these errors.
* trace files from step#2
* tkprof output files from step #5

(6) Output of query in a txt file (please do not copy it to SR directly):

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
-- locate Data Pump jobs:
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

(7) Please provide output of:
opatch lsinventory -detail
show parameter
in separate txt files.

(8) Provide an AWR report taken at this moment (with the hanging impdp)
How to generate AWR report:
The AWR snapshots are by defaulted gathered every hour unless customer has disabled this feature or the jobs have trouble.
Only need to create AWR report based on the AWR snapshot.
To generate an AWR report simply execute the following script and pick the two snapshots you want to use for the sample (Oracle recommends using snapshots whose timestamps are no more than 60 minutes apart from each other):
$ORACLE_HOME/rdbms/admin/awrrpt.sql

Maclean Liu(刘相兵

1、你的新库 有建JVM吗？


2、

select comp_name,status from dba_server_registry;

3、

数据库只有 600多 G,  采用全库导出，全库导入，导入并行 5  ， 导入参数


==》如果你按照 schema 导入 能否绕过这个问题吗？

Maclean Liu(刘相兵

PS；看到你的SR了

wangqi

Maclean Liu(刘相兵 发表于 2013-8-28 12:55
1、你的新库 有建JVM吗？

1、你的新库 有建JVM吗？

A:   新的数据库是普通安装的Oracle11.2.0.3 RDBMS软件，默认安装企业版(4.5G大小)，然后通过参考“10g Release 2 Export/Import Process for Oracle Applications Release 11i (文档 ID 362205.1)” 文档生成EBS 特有的建库脚本创建实例，数据库实例不是我们通过DBCA建的，都是参考文档中的EBS特有脚本生成的。企业版RDBMS应该含有JVM 组件 。  


2、 select comp_name,status from dba_server_registry;

R12 +  Oracle11.2.0.3    (已经升级)  

COMP_NAME        STATUS

JServer JAVA Virtual Machine        VALID
OLAP Analytic Workspace                VALID
OLAP Catalog                        VALID
Oracle Database Catalog Views        VALID
Oracle Database Java Packages        VALID
Oracle Database Packages and Types        VALID
Oracle Expression Filter        VALID
Oracle Multimedia        VALID
Oracle OLAP API                VALID
Oracle Real Application Clusters        INVALID
Oracle Text        VALID
Oracle XDK        VALID
Oracle XML Database        VALID
Spatial                        VALID



R11i + Oracle10.2.0.4  （原EBS R11i）

COMP_NAME        STATUS

JServer JAVA Virtual Machine        VALID
OLAP Analytic Workspace                VALID
OLAP Catalog                        VALID
Oracle Data Mining                VALID
Oracle Database Catalog Views        VALID
Oracle Database Java Packages        VALID
Oracle Database Packages and Types        VALID
Oracle OLAP API                                VALID
Oracle Real Application Clusters        INVALID
Oracle Text        VALID
Oracle XDK        VALID
Oracle XML Database        VALID
Oracle interMedia        VALID
Spatial                        VALID



3、数据库只有 600多 G,  采用全库导出，全库导入，导入并行 5  ， 导入参数
==》如果你按照 schema 导入 能否绕过这个问题吗？

A:   因为导出参数文件也是 EBS 文档提供的 ，$AU_TOP/patch/115/import/auexpdp.dat ，全库导出，
directory=dmpdir
dumpfile=aexp%U.dmp
parallel=4
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
logfile=expdpapps.log

EBS Schema 大概有200多个，exclude 的是上面几个，按照schema导入未测试，近期找时间测试一下。