Join us at the OUG Ireland 2014!
The Oracle User Group Ireland has their annual conference in Dublin with an interesting agenda.
Speakers like Tom Kyte and Timothy Hall stand as a guarantee for high quality.
Oracle University is also showing presence there with Joel Goodman and me, not only as speakers on Tuesday but also with two Master Classes on Wednesday during the OUG Ireland 12c Workshop.
Hope to see you there 🙂
The 12c Workshop got cancelled, sorry.
New member of The OakTable: me!
I am an Oakie now 🙂
To be a part of this highly respected circle of Oracle Scientists is something that I consider one of the greatest achievements of my professional life.
Speed up Import with TRANSFORM=DISABLE_ARCHIVE_LOGGING in #Oracle 12c
A very useful 12c New Feature is the option to suppress the generation of redo during Data Pump import. I was talking about it during my recent 12c New Features class in Finland and like to share that info with the Oracle Community here. My usual demo user ADAM owned a table named BIG with one index on it. Both were in LOGGING mode when I exported them. The Data Pump export did not use any 12c New Feature and is not shown therefore.
SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING ------------ --------------------------------------- ARCHIVELOG NO 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 0 0 0 0 BACKUP PIECE 0 0 0 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 database is not in force logging mode – else the new Data Pump parameter would be ignored. Archive log mode was just turned on, therefore no archive log file yet. First I will show the redo generating way to import, which is the default. Afterwards the new feature for comparison.
SQL> host impdp adam/adam directory=DPDIR tables=big Import: Release 12.1.0.1.0 - Production on Mon Jan 20 11:50:42 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.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/******** directory=DPDIR tables=big Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "ADAM"."BIG" 660.1 MB 5942016 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 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 Mon Jan 20 11:54:32 2014 elapsed 0 00:03:48 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 13.47 0 12 0 BACKUP PIECE 0 0 0 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 conventional way with redo generation took almost 4 minutes and generated 12 archive logs – my online logs are 100 megabyte in size. Now let’s see the new feature:
SQL> drop table big purge; Table dropped. SQL> host impdp adam/adam directory=DPDIR tables=big transform=disable_archive_logging:y Import: Release 12.1.0.1.0 - Production on Mon Jan 20 11:57:19 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.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/******** directory=DPDIR tables=big transform=disable_archive_logging:y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "ADAM"."BIG" 660.1 MB 5942016 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 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 Mon Jan 20 11:58:21 2014 elapsed 0 00:01:01 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 13.47 0 12 0 BACKUP PIECE 0 0 0 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 table_name,logging from user_tables; TABLE_NAME LOG ------------------------------------------------------------ --- BIG YES SQL> select index_name,logging from user_indexes; INDEX_NAME LOG ------------------------------------------------------------ --- BIG_IDX YES
Note that the segment attributes are not permanently changed to NOLOGGING by the Data Pump import.
The comparison shows a striking improvement in run time – because the 2nd run did not generate additional archive logs, we still see the same number as before the 2nd call.
Another option is to suppress redo generation only for the import of indexes, in my example with the command
impdp adam/adam directory=DPDIR tables=big transform=disable_archive_logging:y:index
That is a safer choice because indexes are always reproducible. Keep in mind that any NOLOGGING operation is a risk – that is the price to pay for the speed up.
As always: Don’t believe it, test it! 🙂
Pickleball spielen auf YouTube
Pickleball spielen 