data pump is much efficient than imp/exp , which help me import 19Gb within one hour without generating redo logs.
LIQY@ADMP> @chk_session
SUBSTR(USERNAME,1,15) SID SERIAL# SQL_HASH_VALUE SERVER STATUS OSUSER
--------------------------------------------- ---------- ---------- -------------- --------- -------- ------------------------------
SUBSTR(MACHINE,1,20)
------------------------------------------------------------
SUBSTR(PROGRAM,1,25) TO_CHAR(LOGON_TIME,'DD-MON CPU_MINUTES
--------------------------------------------------------------------------- -------------------------- -----------
SYSMAN 1079 9 4281219134 DEDICATED ACTIVE
bill07
OMS 08-apr-2010 02:09:47 2.56233333
DBSNMP 1101 3 0 DEDICATED ACTIVE oracle1
bill07
emagent@bill07 (TNS V1-V3 08-apr-2010 02:11:20 2.78166667
SYS 187 64288 3732391352 DEDICATED ACTIVE oracle1
bill07
udi@bill07 (TNS V1-V3) 19-apr-2010 15:54:17 .011
SYS 269 16294 0 DEDICATED ACTIVE oracle1
bill07
oracle@bill07 (DM00) 19-apr-2010 15:54:17 .011833333
SYS 1069 3981 0 DEDICATED ACTIVE oracle1
bill07
oracle@bill07 (DW01) 19-apr-2010 15:54:18 1.69966667
LIQY 359 32 2579827540 DEDICATED ACTIVE liqy
bill07
sqlplus@bill07 (TNS V1-V3 19-apr-2010 15:55:18 .002
6 rows selected.
--SID 187, 269 and 1069 belong to datapump import, directly write to data file by DB writer.
LIQY@ADMP> @chk_session_wait
SID SUBSTR(EVENT,1,25) P1 SUBSTR(P1TEXT,1,10)
---------- --------------------------------------------------------------------------- ---------- ------------------------------
P2 SUBSTR(P2TEXT,1,10) P3 SUBSTR(P3TEXT,1,10)
---------- ------------------------------ ---------- ------------------------------
187 wait for unread message o 1.3835E+19 channel co
1.3835E+19 channel ha 0
269 wait for unread message o 1.3835E+19 channel co
1.3835E+19 channel ha 0
283 Streams AQ: qmn slave idl 0
0 0
534 Streams AQ: waiting for t 0
0 0
536 Streams AQ: qmn coordinat 0
0 0
1017 Streams AQ: qmn slave idl 0
0 0
1069 Datapump dump file I/O 1 count
256 intr 4294967295 timeout
1079 wait for unread message o 1.3835E+19 channel co
1.3835E+19 channel ha 0
1101 Streams AQ: waiting for m 602323 queue id
1.3835E+19 process# 5 wait time
1102 Streams AQ: waiting for t 0
0 0
10 rows selected.
--SID 187, 269 and 1069 belong to datapump import
2. insert data first , then rebuild indexes. Howerver, during data is written to data file, indexes remain valid and usable (not like directly path loading by sql*loader)
LIQY@ADMP> select segment_name, bytes from dba_segments where segment_name like '%NONVOICE03%';
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
IMPDP_M1_SUREPAY_NONVOICE03 524288
M1_SUREPAY_NONVOICE03 1.5781E+10
M1_SUREPAY_NONVOICE03_IDX1 1090519040
M1_SUREPAY_NONVOICE03_IDX2 1237319680
M1_SUREPAY_NONVOICE03_IDX3 20971520
M1_SUREPAY_NONVOICE03_IDX4 20971520
6 rows selected.
--notice the indexes's size keep changing one by one.
LIQY@ADMP> /
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
M1_SUREPAY_NONVOICE03 1.5781E+10
M1_SUREPAY_NONVOICE03_IDX1 1090519040
M1_SUREPAY_NONVOICE03_IDX2 1384120320
M1_SUREPAY_NONVOICE03_IDX3 1048576000
M1_SUREPAY_NONVOICE03_IDX4 901775360
-rw-rw---- 1 oracle1 dba1 26440704 Apr 19 13:31 ADMP_0001_525208771_80943.arc
-rw-rw---- 1 oracle1 dba1 55296 Apr 19 13:31 ADMP_0001_525208771_80944.arc
-rw-rw---- 1 oracle1 dba1 24170496 Apr 19 13:59 ADMP_0001_525208771_80945.arc
drwxrwxr-x 2 oracle1 dba1 16384 Apr 19 14:22 oldarch
-rw-rw---- 1 oracle1 dba1 195064832 Apr 19 16:25 ADMP_0001_525208771_80946.arc
--no log file generated
-rw-rw-r-- 1 oracle1 dba1 13533204480 Apr 19 15:32 expdp_M1_SUREPAY_NONVOICE03.dmp
-rw-rw-r-- 1 oracle1 dba1 698085376 Apr 19 15:32 expdp_M1_SUREPAY_OTHER03.dmp
-rw-rw-r-- 1 oracle1 dba1 19535446016 Apr 19 15:38 expdp_M1_SUREPAY_VOICE03.dmp
bill07:ADMP:/csa074/oradata/surepayimp> cat impdp_M1_SUREPAY_VOICE03.par
userid='/ as sysdba'
directory=surepay_dump_dir
dumpfile=expdp_M1_SUREPAY_VOICE03.dmp
logfile=impdp_M1_SUREPAY_VOICE03.log
tables=dbam1.M1_SUREPAY_VOICE03
CONTENT=data_only # whit table & indexes exist (truncate before import)
#CONTENT=metadata_only
#REMAP_SCHEMA=dbam1:liqy
REMAP_TABLESPACE=surepay_tbl:m1_surepay_tbl
REMAP_TABLESPACE=surepay_idx:m1_surepay_idx
job_name=impdp_M1_SUREPAY_VOICE03
bill07:ADMP:/csa074/oradata/surepayimp> ll expdp_M1_SUREPAY_VOICE03.dmp
-rw-rw-r-- 1 oracle1 dba1 19535446016 Apr 19 15:38 expdp_M1_SUREPAY_VOICE03.dmp
bill07:ADMP:/csa074/oradata/surepayimp> impdp parfile=impdp_M1_SUREPAY_VOICE03.par^C
bill07:ADMP:/csa074/oradata/surepayimp> cat impdp_M1_SUREPAY_NONVOICE03.log
;;;
Import: Release 10.2.0.2.0 - 64bit Production on Monday, 19 April, 2010 15:54:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYS"."IMPDP_M1_SUREPAY_NONVOICE03" successfully loaded/unloaded
Starting "SYS"."IMPDP_M1_SUREPAY_NONVOICE03": parfile=impdp_M1_SUREPAY_NONVOICE03.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBAM1"."M1_SUREPAY_NONVOICE03" 12.60 GB 41019272 rows
Job "SYS"."IMPDP_M1_SUREPAY_NONVOICE03" successfully completed at 16:25:32
bill07:ADMP:/csa074/oradata/surepayimp> impdp parfile=impdp_M1_SUREPAY_VOICE03.par
Import: Release 10.2.0.2.0 - 64bit Production on Monday, 19 April, 2010 16:53:35
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYS"."IMPDP_M1_SUREPAY_VOICE03" successfully loaded/unloaded
Starting "SYS"."IMPDP_M1_SUREPAY_VOICE03": parfile=impdp_M1_SUREPAY_VOICE03.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBAM1"."M1_SUREPAY_VOICE03" 18.19 GB 57052084 rows
Job "SYS"."IMPDP_M1_SUREPAY_VOICE03" successfully completed at 17:41:12
Popular Posts
-
configured "CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK; " when RMAN> delete noprompt archivelog u...
-
I have one PT env using storage sync for database sync. Thought need to re-register DB after DB refresh, so unregister and register again....
-
Encountered this problem during import data from 9i to 10g database. Table structure was already laid out. . . importing table &...
-
[symptoms] orarac2poc:ORCL2:/home/oracle> . ./oms_agent_env orarac2poc:ORCL2:/home/oracle> $AGENT_HOME/bin/emctl start agent Oracl...
-
SQL> select * from dba_data_files where tablespace_name='TESTBS'; FILE_NAME ...
-
I hit the error when using RMAN duplicate command to create a physical standby database, with purpose of using same directory structure in...
-
As I have CPU issue with physical standby database in previous post, I have to remove it before I have the solution. In 12c Enterprise Man... -
Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second f...
-
after rounds of recovery testing, I believer there are tons of obsolete files in FRA. There are some stubborn datafile copy can't be de...
-
RMAN> show all; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # de...