Oracle 19c Import with disable_archive_logging Tips
Oracle19c feature is the ability to run in nologging mode in import execution, disabling archive logging during a large import. Only for table and index Not for tablespace.
transform=disable_archive_logging:Y
transform=disable_archive_logging:Y:tablename
transform=disable_archive_logging:Y:indexname
Test
Create directory and user;
SYS@orcl> create directory imp_home as '/home/oracle';
Directory created.
SYS@orcl>
SYS@orcl> show parameter user_pre
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix string C##
SYS@orcl> create user c##lin identified by oracle;
User created.
SYS@orcl> grant dba to c##lin;
Grant succeeded.
SYS@orcl>
Generate data
SYS@orcl> conn c##lin/oracle
Connected.
C##LIN@orcl>
C##LIN@orcl> select count(*) from dba_objects;
COUNT(*)
----------
72549
C##LIN@orcl> select count(*) from cdb_objects;
COUNT(*)
----------
72549
C##LIN@orcl> create table imp_test as select * from dba_objects;
Table created.
C##LIN@orcl> insert into imp_test (select * from imp_test);
72549 rows created.
C##LIN@orcl> /
145098 rows created.
C##LIN@orcl> /
290196 rows created.
C##LIN@orcl> /
580392 rows created.
C##LIN@orcl> /
1160784 rows created.
C##LIN@orcl> commit;
Commit complete.
C##LIN@orcl>
C##LIN@orcl> col segment_name for a20
C##LIN@orcl> select bytes/1024/1024 mb, segment_name, segment_type from user_segments;
MB SEGMENT_NAME SEGMENT_TYPE
---------- -------------------- ------------------
360 IMP_TEST TABLE
Export the test data;
C##LIN@orcl> !expdp \"/as sysdba\" directory=imp_home dumpfile=imp_test.dmp logfile=imp_test.log schemas="C##LIN"
Export: Release 19.0.0.0.0 - Production on Thu Aug 22 14:03:58 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=imp_home dumpfile=imp_test.dmp logfile=imp_test.log schemas=C##LIN
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "C##LIN"."IMP_TEST" 305.8 MB 2321568 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/imp_test.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Aug 22 14:07:32 2019 elapsed 0 00:03:10
C##LIN@orcl>
C##LIN@orcl> !ls -ltr /home/oracle/imp_test.dmp
-rw-r-----. 1 oracle oinstall 321089536 Aug 22 14:07 /home/oracle/imp_test.dmp
C##LIN@orcl> !du -sm /home/oracle/imp_test.dmp
307 /home/oracle/imp_test.dmp
Test import transform_enable mode: redo log from 724mb to 1081mb / elapsed 0 00:02:20
C##LIN@orcl> drop table c##lin.imp_test purge;
Table dropped.
C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;
NAME MB
---------------------------------------------------------------- ----------
redo size 724.179874
C##LIN@orcl> !impdp \"/as sysdba\" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas="C##LIN"
Import: Release 19.0.0.0.0 - Production on Thu Aug 22 14:22:11 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas=C##LIN
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##LIN"."IMP_TEST" 305.8 MB 2321568 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Aug 22 14:24:39 2019 elapsed 0 00:02:20
C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;
NAME MB
---------------------------------------------------------------- ----------
redo size 1081.06575
C##LIN@orcl>
Test import transform_disable mode: redo log from 1081mb to 1084mb / elapsed 0 00:01:28
C##LIN@orcl> drop table c##lin.imp_test purge;
Table dropped.
C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;
NAME MB
---------------------------------------------------------------- ----------
redo size 1081.18863
C##LIN@orcl>
C##LIN@orcl> !impdp \"/as sysdba\" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas="C##LIN" transform=disable_archive_logging:Y
Import: Release 19.0.0.0.0 - Production on Thu Aug 22 14:28:48 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas=C##LIN transform=disable_archive_logging:Y
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##LIN"."IMP_TEST" 305.8 MB 2321568 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Aug 22 14:30:23 2019 elapsed 0 00:01:28
C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;
NAME MB
---------------------------------------------------------------- ----------
redo size 1084.21175
C##LIN@orcl>
The Result of importing with transform/disable_archive_logging
| Data | transform(archive_logging) | Generated Redo log size | Import elapsed |
|---|---|---|---|
| 360MB | enable(default) | 357 MB | 02:20 |
| 360MB | disable | 3.1 MB | 01:28 |
No comments:
Post a Comment