Expdp ile no rows backup almaya çalışırken alınan bir hata ve çözümü ile ilgili detaylar ;

expdp “‘/ as sysdba’” DIRECTORY=expdp DUMPFILE= metadata_15032011.dmp LOGFILE=metadata_15032011.log CONTENT=METADATA_ONLY FULL=Y

Yukarıdaki script ile almaya çalıştığımda ;

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/JOB
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [JOB]
ORA-04030: out of process memory when trying to allocate 281280 bytes (callheap,temporary memory)


—– PL/SQL Call Stack —–
object line object
handle number name
7000003852a3c60 13615 package body SYS.KUPW$WORKER
7000003852a3c60 5898 package body SYS.KUPW$WORKER
7000003852a3c60 2090 package body SYS.KUPW$WORKER
7000003852a3c60 6417 package body SYS.KUPW$WORKER
7000003852a3c60 1264 package body SYS.KUPW$WORKER
70000037dd83788 2 anonymous block

Job “SYS”.”DWDATA_METADATA_EXPORT” stopped due to fatal error at 12:59

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [JOB]
ORA-04030: out of process memory when trying to allocate 281280 bytes (callheap,temporary memory)

Hatası aldım. Hatanın içeriğinde zaten JOB’ lar ile ilgili bir problem olduğunu gösteriyor. Sorun yaratan jobları bulmak için ;

———————— problemli nesneyi bulmak için

connect / as sysdba

select object_id, owner, object_name, object_type
from all_objects
where object_id not in (select distinct obj# from source$) and
object_type = ‘JOB’
order by owner;

OBJECT_ID OWNER OBJECT_NAME
———- —————————— ——————————
1448959 KAMIL ADV_SHRINK_1567422
1448962 KAMIL SQLSCRIPT_7073194
1448961 KAMIL ADV_MIZANIDX_seg_adv
1047787 ORACLE_OCM MGMT_STATS_CONFIG_JOB
1047786 ORACLE_OCM MGMT_CONFIG_JOB

5 rows selected.

Problem çıkaran jobları buldum. Sonrasında artık kullanılmayacak olan joblar olduğundan hepsini drop etmeye karar verdim. (ki metalinkde de hatanın çözüm yolu olarak önce drop edip no rows backupı başarılı olarak alıp, ihtiyaç var ise tekrar create etmek şeklinde ifade edilmiş, yani gerekiyorsa ddl’ ini alıp sonrasında tekrar create edebilirsiniz)

Drop scripti için aşağıdaki sql’ i kullanabilirsiniz;

select ‘execute DBMS_SCHEDULER.DROP_JOB(‘||owner||’.’||object_name|| ‘);’ drop_komut
from all_objects
where object_id not in (select distinct obj# from source$) and
object_type = ‘JOB’
order by owner;

DROP_KOMUT
———————————————————————————————–
execute DBMS_SCHEDULER.DROP_JOB(KAMIL.ADV_SHRINK_1567422);
execute DBMS_SCHEDULER.DROP_JOB(KAMIL.ADV_MIZANIDX_seg_adv);
execute DBMS_SCHEDULER.DROP_JOB(KAMIL.SQLSCRIPT_7073194);
execute DBMS_SCHEDULER.DROP_JOB(ORACLE_OCM.MGMT_STATS_CONFIG_JOB);
execute DBMS_SCHEDULER.DROP_JOB(ORACLE_OCM.MGMT_CONFIG_JOB);

5 rows selected.

Hazırladığımız drop komutlarını çalıştıralım ;

execute DBMS_SCHEDULER.DROP_JOB(KAMIL.ADV_SHRINK_1567422);
Job Dropped
execute DBMS_SCHEDULER.DROP_JOB(KAMIL.ADV_MIZANIDX_seg_adv);
Job Dropped
execute DBMS_SCHEDULER.DROP_JOB(KAMIL.SQLSCRIPT_7073194);
Job Dropped
execute DBMS_SCHEDULER.DROP_JOB(ORACLE_OCM.MGMT_STATS_CONFIG_JOB);
Job Dropped
execute DBMS_SCHEDULER.DROP_JOB(ORACLE_OCM.MGMT_CONFIG_JOB);
Job Dropped

Tekrar kontrol edelim;

select object_id, owner, object_name, object_type
from all_objects
where object_id not in (select distinct obj# from source$) and
object_type = ‘JOB’
order by owner;
0 rows selected.

Artık backupımızı alabiliriz.

Be Sociable, Share!

• 
•