DBA Hub

📋Steps in this guide1/4

How to import data with nologgin option in oracle DBACLASS

We can import data with nologgin option by using TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y parameter.

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

A new feature has been added in datapump of oracle 12c. We can import data with nologgin option i.e without generating logs. We sometimes face issue while importing big tables, as it generates lot of archive logs. TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y – This parameter can be used to make the impdp nologging. check the archive status before import

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     232
Next log sequence to archive   234
Current log sequence	       234
2

Section 2

Import a table: Check the archive status after import( No change observed)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[oracle@localhost ~]$ impdp dumpfile=dbaclass.dmp logfile=dbaclass.log directory=DUMP tables=test.TEST TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Import: Release 12.1.0.2.0 - Production on Sat Aug 22 03:40:41 2015

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

Username: sys/oracle@ORCL as sysdba

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 "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  sys/********@ORCL AS SYSDBA dumpfile=dbaclass.dmp logfile=dbaclass.log directory=DUMP tables=test.TEST TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TEST"                               10.66 MB   93925 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 22 03:41:23 2015 elapsed 0 00:00:30

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     232
Next log sequence to archive   234
Current log sequence	       234
3

Section 3

Check the alert log We can observe in the alert log that no archives has been generated as part of the import. We can use nologging for specific object_types like TABLE or INDEX.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
Sat Aug 22 03:36:06 2015
Resize operation completed for file# 10, old size 224000K, new size 226560K
Sat Aug 22 03:40:53 2015
DM00 started with pid=72, OS id=10034, job SYS.SYS_IMPORT_TABLE_01
Sat Aug 22 03:40:54 2015

DW00 started with pid=76, OS id=10036, wid=1, job SYS.SYS_IMPORT_TABLE_01
4

Section 4

Code/Command (click line numbers to comment):

1
2
3
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE      

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

Comments (0)

Please to add comments

No comments yet. Be the first to comment!