Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

How to speed up Data Pump Import by suppressing redo generation in #Oracle 12c

With your database in archive log mode, a Data Pump Import may be severely slowed down by the writing of much redo into online logs and the the generation of many archive logs. A 12c New Feature enables you to avoid that slow down by suppressing redo generation for the import only. You can keep the database in archive log mode the whole time. Let’s see that in action!

First without the new feature:

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:25:25 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:24:55 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select log_mode from v$database;

LOG_MODE
------------------------------------
ARCHIVELOG

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    15.37			 15		 10	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ impdp adam/adam tables=sales directory=DPDIR

Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:26:45 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** tables=sales directory=DPDIR 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."SALES"                              510.9 MB 10000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:29:46 2016 elapsed 0 00:03:00
[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:30:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:26:45 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    30.63			 15		 17	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

The import took 3 minutes and generated 7 archive logs. Now with the new feature:

SQL> drop table sales purge;

Table dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ impdp adam/adam tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y

Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:31:20 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."SALES"                              510.9 MB 10000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:32:15 2016 elapsed 0 00:00:54

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:32:25 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:31:20 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    30.63			 15		 17	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

SQL> select logging from user_tables where table_name='SALES';

LOGGING
---------
YES

About three times faster and no archive log generated! The table is still in logging mode after the import. Keep in mind to take a backup of the datafile that contains the table now, though! For the same reason you should take a backup after a NOLOGGING operation</p />
</p></div>

    	  	<div class=