Oracle 10g data pump hang @Aix

今天嘗試備份資料庫到NAS 去,
發現data pump job 整個hang 住,
查了一下,發現都在 DEFINING 狀態



SQL> select job_name, state from dba_datapump_jobs; 




JOB_NAME                       STATE 
------------------------------ ------------------------------ 
EXPEAID                        DEFINING 


最後發現nfs mount point 要加上 llock 此參數
再用mount 重新掛載, 重新執行就可以了


/etc/filesystems:



/nas:
        dev             = "/nasvol"
        vfs             = nfs
        nodename        = 10.1.1.1
        mount           = true
        options         = cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tc
p,vers=3,timeo=600,noac,llock
        account         = false


done~

11g exp bug發現 = =||

今天使用以下指令備份時
exp \'/ as sysdba\' full=y file=full_exp.dmp log=full_exp.log recordlength=65535 direct=y buffer=512000000 rows=y statistics=none feedback=300000





. exporting materialized views
. exporting snapshot logs
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

查了一下

發現是11g(11.2.0.1) 的tratitional export 不支援snapshot log ,Bug 9306119 - Export fails with ORA-1455 when exporting snapshot logs [ID 9306119.8]
, 要升版到11.2.0.2才可解決

或改採data pump...

ref.
https://cn.forums.oracle.com/forums/thread.jspa?threadID=2240726

Oracle data pump 指令參照

預測dmp檔佔多少空間
expdp test/test directory=expdp_dir logfile=test_dmp.log estimate_only=y
                ESTIMATE=BLOCKS  (預設)
                ESTINATE=STATISTICS  (依照表格的STATISTICS預測空間大小)
指定dump時用幾個執行緒
expdp test/test  directory=expdp_dir dumpfile=test.dmp logfile=test_dmp.log parallel=4

排除某個表格
expdp test/test schemas=TEST exclude=TABLE:"in\('TEST'\)" directory=expdp_dir dumpfile=expdp.dmp logfile=expdp.log

排除某個USER
expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('TEST'\)" directory=dp dumpfile=full.dmp logfile=full.log

排除一
expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('WKSYS'\)" directory=dp dumpfile=full.dmp logfile=full.log CONTENT=METADATA_ONLY

expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('WKSYS','OLAPSYS','TSMSYS','SYSMAN','OUTLN','WK_TEST'\)" directory=dp dumpfile=full.dmp logfile=full.log


匯出所有METADATA
expdp \'/ as sysdba\' SCHEMAS=TEST,JAYCHU directory=dp dumpfile=full.dmp logfile=full.log CONTENT=METADATA_ONLY
by default, the datapump points to $ORACLE_BASE_admin/SID/dpdump

--export table
drop directory expdp_dir ;
create directory expdp_dir as '/na02/sysage/0825';
expdp \'/ as sysdba\' tables=MISUSER.ST02PF directory=expdp_dir content=all dumpfile=ST02PF.dmp logfile=ST02PF.log

create directory expdp_dir as '/na02/sysage/0825';
impdp \'/ as sysdba\' tables=MISUSER.ST02PF directory=expdp_dir content=all dumpfile=ST02PF.dmp logfile=ST02PF_imp.log TABLE_EXISTS_ACTION=replace
--

impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123  SQLFILE=JAYCHU123.sql LOGFILE=impdp.log DUMPFILE=full.dmp INCLUDE=GRANT,TABLE
                       ESTIMATE=STATISTICS
test:
impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123 LOGFILE=impdp.log DUMPFILE=full.dmp INCLUDE=GRANT,TABLE  ESTIMATE=STATISTICS


(PS: CONTENT=DATA_ONLY is invalid for SQL_FILE jobs.)
   
impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123  PARALLEL=4 LOGFILE=impdp.log DUMPFILE=full.dmp
                       CONTENT= ALL, (METADATA_ONLY), DATA_ONLY
                       INCLUDE=TABLE_DATA or TABLE or PROCEDURE or FUNCTION or TRIGGER or GRANT or INDEXES or CONSTRAINT
                       EXCLUDE=TABLE_DATA or TABLE or PROCEDURE or FUNCTION or TRIGGER or GRANT or INDEXES or CONSTRAINT(可自行斟酌須排除schema object type)
                       remap_tablespace=old_tablespace:new_tablespace
                       TABLE_EXISTS_ACTION   匯入之物件已經存在時所要採取的動作. : (SKIP , TRUNCATE , REPLACE)
                                                                                    ^^^^ default

create directory expdp_dir as '/na02/sysage/0825';
expdp \'/ as sysdba\' full=y  PARALLEL=4 flashback_time=\"TO_TIMESTAMP\(\'2010-07-19 20:40:00\', \'YYYY-MM-DD HH24:MI:SS\'\)\"  dumpfile=full0719.dmp

create directory expdp_dir as '/na02/sysage/0719';
impdp \'/ as sysdba\' DIRECTORY=expdp_dir PARALLEL=4 dumpfile=full0719.dmp logfile=venus_impdp_1.log CONTENT=DATAONLY TABLE_EXISTS_ACTION=REPLACE INCLUDE=INDEXES&



查看job
col OWNER_NAME for a10
col JOB_NAME for a20
col OPERATION for a6
col JOB_MODE for a6
col STATE for a10
col DEGREE for 9.9
col ATTACHED_SESSIONS for 9
col DATAPUMP_SESSIONS for 9

select * from dba_datapump_jobs;

查看expdp進度
col opname for a30
select opname , sofar , totalwork , ROUND(SOFAR/TOTALWORK*100,2) "%_Complete" from v$session_longops where sofar !=totalwork and opname like '%EXPORT%';

impdp進度
col opname for a30
select opname , sofar , totalwork , ROUND(SOFAR/TOTALWORK*100,2) "%_Complete" from v$session_longops where sofar !=totalwork and opname like '%IMPORT%';

Oracle import 心得

imp error _其中某些index無法成功import


. . importing table    "WP_SUB_XXX"    7609037 rows imported

IMP-00017: following statement failed with ORACLE error 1658:

 "CREATE INDEX "WP_SUB_PICT_PIC_IDX" ON "WP_SUB_PAID_INFO_CODE_TBL" ("PAID_IN"

 "FO_CODE" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 346161152 NE"

 "XT 131072 MINEXTENTS 1 MAXEXTENTS 8192 PCTINCREASE 0 FREELISTS 1 FREELIST G"

 "ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" LOGGING"

IMP-00003: ORACLE error 1658 encountered

ORA-01658: unable to create INITIAL extent for segment in tablespace INDX

IMP-00017: following statement failed with ORACLE error 20000:

 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"WP_SUB_PICT_PIC_IDX"',NULL,NULL,NU"

 "LL,5915993.14285714,37008,78,474,2,724299,2,0); END;"

IMP-00003: ORACLE error 20000 encountered

''ORA-20000: Unable to set values for index WP_SUB_PICT_PIC_IDX: does not exist or insufficient privileges''

ORA-06512: at "SYS.DBMS_STATS", line 3718

ORA-06512: at "SYS.DBMS_STATS", line 3823

ORA-06512: at line 1




-
1.要確定target 資料庫裡面 user 是否有create index 的權限  (grant resource to bvuser;)

2.或是User 在tablespace 裡面有無quota?  (grant unlimited tablespace to user; )

3.或有可能遇到tablespace datafile大小達到上限
ex:
8k block size , 單一 datafile size : 32GB

4.當初建index 的時候是用特定USER連到資料庫建的，所以匯入時，DDL 中不會顯示出index owner，而造成misleading....

例如create index INDEX_NAME on... 要改成 create index USER_NAME.INDEX_NAME...

Oracle export 心得

時間估算
1.100GB datafile 做schema export buffers=512MB , 10min鐘 16GB dmp

預計full export 需要 15min


2.
1192GB datafile (hp ux rp74做full export buffers=512MB 144 min 約101GB
200GB datafile (Sun V1280) buffers=128MB ,時間2.5hr (行通三樓-BV) 41945.25MB


3.
時間估算
Import should be minimum 2 to 2.5 times 
the export time.(Note:93763.1)
Export 每1.6- 3gb的dmp file 需要1min 時間

Export 每0.7- 3gb的dmp file 需要1min 時間(20090701)


Import 會比export時間來的久一點


4.

EXP-00009 : no privilege to export schema's table
-
full database mode
grant EXP_FULL_DATABASE to user;
grant IMP_FULL_DATABASE to user;


user mode
grant dba to user; 可匯出所有使用者的物件
grant sysdba to user;


-其他權限
grant connect ,resource to user;  (grant resource 可讓使用者在tablespace 底下擁有使用空間的quota : )
-
grant select any dictionary to test; 賦予使用者查詢data dictionary 的權限.



5.consistent=y 
會保證在export 那個時間點的所有資料都被正確匯出,但rollback segment

必須要足以放的下每一筆原始資料,此時可以限定transaction 只在某個rollback segment

且該segment 大小為unlimited


--

查看import進度

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi: ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

此附件為 imp utility 於import 時可看到

http://forums.oracle.com/forums/thread.jspa?messageID=6495966&tstart=0

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,

 rows_processed,

 round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,

 trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi: ss'))*24*60)) rows_per_min

 from sys.v_$sqlarea

 where sql_text like 'INSERT %INTO "%'

 and command_type = 2

 and open_versions > 0;