Import option in Oracle Database

 Import option in Oracle Database  / tip & trick🧶

As a DBA sometimes you have a challenging when loading large data into a target database ,specially when existing many relations between tables or large partitions tables dependencies.

By this parameters you can control how to handle constraint violations during import operations.

DATA_OPTIONS

The Oracle Data Pump Import command-line mode DATA_OPTIONS parameter designates how you want certain types of data to be handled during import operations.

Default

There is no default. If this parameter is not used, then the special data handling options it provides simply do not take effect.

Purpose

The DATA_OPTIONS parameter designates how you want certain types of data to be handled during import operations.

Syntax and Description

DATA_OPTIONS = [DISABLE_APPEND_HINT | SKIP_CONSTRAINT_ERRORS | 

REJECT_ROWS_WITH_REPL_CHAR | GROUP_PARTITION_TABLE_DATA | TRUST_EXISTING_TABLE_PARTITIONS | 

VALIDATE_TABLE_DATA | ENABLE_NETWORK_COMPRESSION |

CONTINUE_LOAD_ON_FORMAT_ERROR]

DISABLE_APPEND_HINT: Specifies that you do not want the import operation to use the APPEND hint while loading the data object. Disabling the APPEND hint can be useful to address duplicate data. For example, you can use DISABLE_APPEND_HINT when there is a small set of data objects to load that exists already in the database, and some other application can be concurrently accessing one or more of the data objects.
DISABLE_APPEND_HINT: Changes the default behavior, so that the APPEND hint is not used for loading data objects. When not set, the default is to use the APPEND hint for loading data objects.

GROUP_PARTITION_TABLE_DATA: Tells Oracle Data Pump to import the table data in all partitions of a table as one operation. The default behavior is to import each table partition as a separate operation. If you know that the data for a partition will not move, then choose this parameter to accelerate the import of partitioned table data. There are cases when Oracle Data Pump attempts to load only one partition at a time. It does this when the table already exists, or when there is a risk that the data for one partition might be moved to another partition.

♨️Tells Datapump to load the information from a partitioned table in a single operation, instead of loading the partitions separately.   

The definition of the table is not taken into account when importing, which allows the task to be carried out much faster.

It is important to clarify that the structure of the table remains unchanged. This option only allows you to speed up the export and import operation.

We execute the EXPDP command without the GROUP_PARTITION_TABLE_DATA option and observe how it exports the partitions of a table.

&expdp system@ORCL DUMPFILE=SH_SALES.dmp LOGFILE=exp_SH_SALES.log DIRECTORY=EXP_DIR TABLES=SH.SALES DATA_OPTIONS=

GROUP_PARTITION_TABLE_DATA

REJECT_ROWS_WITH_REPL_CHAR: Specifies that you want the import operation to reject any rows that experience data loss because the default replacement character was used during character set conversion.
If REJECT_ROWS_WITH_REPL_CHAR is not set, then the default behavior is to load the converted rows with replacement characters.

SKIP_CONSTRAINT_ERRORS: Affects how non-deferred constraint violations are handled while a data object (table, partition, or subpartition) is being loaded. 
If deferred constraint violations are encountered, then SKIP_CONSTRAINT_ERRORS has no effect on the load. Deferred constraint violations always cause the entire load to be rolled back.
The SKIP_CONSTRAINT_ERRORS option specifies that you want the import operation to proceed even if non-deferred constraint violations are encountered. It logs any rows that cause non-deferred constraint violations, but does not stop the load for the data object experiencing the violation. 
SKIP_CONSTRAINT_ERRORS: Prevents roll back of the entire data object when non-deferred constraint violations are encountered. 
If SKIP_CONSTRAINT_ERRORS is not set, then the default behavior is to roll back the entire load of the data object on which non-deferred constraint violations are encountered.

TRUST_EXISTING_TABLE_PARTITIONS: Tells Data Pump to load partition data in parallel into existing tables. 
Use this option when you are using Data Pump to create the table from the definition in the export database before the table data import is started. Typically, you use this parameter as part of a migration when the metadata is static, and you can move it before the databases are taken off line to migrate the data. Moving the metadata separately minimizes downtime. If you use this option, and if other attributes of the database are the same (for example, character set), then the data from the export database goes to the same partitions in the import database.
You can create the table outside of the data pump. However, if you create tables as a separate option from using Oracle Data Pump, then the partition attributes and partition names must be identical to the export database.

The TRUST_EXISTING_TABLE_PARTITIONS option tells Datapump that the structure of a partitioned table already exists in the destination database and is equal to the source database. In this way, Datapump can import data in 📍parallel 📍into different partitions.  

If the structure is not the same, a loading error could occur.

ORA-31693: Table data object "SH"."SALES ":"SALES_Q3_2001" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-14401: inserted partition key is outside specified partition

To explain it better, we review the following example.  

♨️When we perform export and import operations on a partitioned table, this operation is performed serially, even if one defines parallelism. 

📍Parallelism helps on different tables, but not on partitions of a table.

We can see this in the following export task with the option METRICS=Y (an import task works in the same way)

expdp system@ORCL DUMPFILE=SH_SALES3_%U.dmp LOGFILE=exp_SH_SALES3.log DIRECTORY=EXP_DIR TABLES=SH.SALES METRICS=Y PARALLEL=2

Now, we run the import again in the database, but with the   TRUST_EXISTING_TABLE_PARTITIONS option , we also use the TABLE_EXISTS_ACTION = TRUNCATE option .

impdp system@PRD DUMPFILE=SH_SALES_%U.dmp LOGFILE=imp_SH_SALES.log DIRECTORY=IMP_DIR TABLES=SH.SALES METRICS=Y PARALLEL=2 TABLE_EXISTS_ACTION = TRUNCATE DATA_OPTIONS=

TRUST_EXISTING_TABLE_PARTITIONS

VALIDATE_TABLE_DATA: Directs Oracle Data Pump to validate the number and date data types in table data columns. 
If the import encounters invalid data, then an ORA-39376 error is written to the .logfile. The error text includes the column name. The default is to do no validation. Use this option if the source of the Oracle Data Pump dump file is not trusted.

ENABLE_NETWORK_COMPRESSION: Used for network imports in which the Oracle Data Pump ACCESS_METHOD parameter is set to DIRECT_PATH to load remote table data. 
When ENABLE_NETWORK_COMPRESSION is specified, Oracle Data Pump compresses data on the remote node before it is sent over the network to the target database, where it is decompressed. This option is useful if the network connection between the remote and local database is slow, because it reduces the amount of data sent over the network.
Setting ACCESS_METHOD=AUTOMATIC enables Oracle Data Pump to set ENABLE_NETWORK_COMPRESSION automatically during the import if Oracle Data Pump uses DIRECT_PATH for a network import.
The ENABLE_NETWORK_COMPRESSION option is ignored if Oracle Data Pump is importing data from a dump file, if the remote data base is earlier than Oracle Database 12c Release 2 (12.2), or if an INSERT_AS_SELECT statement is being used to load data from the remote database.

CONTINUE_LOAD_ON_FORMAT_ERROR: Directs Oracle Data Pump to skip forward to the start of the next granule when a stream format error is encountered while loading table data. 
Stream format errors typically are the result of corrupt dump files. If Oracle Data Pump encounters a stream format error, and the original export database is not available to export the table data again, then you can use CONTINUE_LOAD_ON_FORMAT_ERROR. If Oracle Data Pump skips over data, then not all data from the source database is imported, which potentially skips hundreds or thousands of rows.

Restrictions

• If you use DISABLE_APPEND_HINT, then it can take longer for data objects to load.
• If you use SKIP_CONSTRAINT_ERRORS, and if a data object has unique indexes or constraints defined on it at the time of the load, then the APPEND hint is not used for loading that data object. Therefore, loading such data objects can take longer when the SKIP_CONSTRAINT_ERRORS option is used.
• Even if SKIP_CONSTRAINT_ERRORS is specified, it is not used unless a data object is being loaded using the external table access method.

Example

This example shows a data-only table mode import with SKIP_CONSTRAINT_ERRORS enabled:

$impdp hr TABLES=employees CONTENT=DATA_ONLY 

DUMPFILE=dpump_dir1:table.dmp DATA_OPTIONS=skip_constraint_errors

If any non-deferred constraint violations are encountered during this import operation, then they are logged. The import continues on to completion.

Another example:

$impdp hr/pass dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORS

Best regards,

Alireza Kamrani

🔴Oracle ASM Filter Driver & ASMLIB & UDEV🔴

A complete review, recommendation usage 

About Oracle ASM Filter Driver

Oracle ASM Filter Driver (Oracle ASMFD) is a kernel module that resides in the I/O path of the Oracle ASM disks. Oracle ASM uses the filter driver to validate write I/O requests to Oracle ASM disks. 

Oracle ASMFD simplifies the configuration and management of disk devices by eliminating the need to rebind disk devices used with Oracle ASM each time the system is restarted.

Oracle ASM Filter Driver rejects any I/O requests that are invalid. This action eliminates accidental overwrites of Oracle ASM disks that would cause corruption in the disks and files within the disk group. For example, the Oracle ASM Filter Driver filters out all non-Oracle I/Os which could cause accidental overwrites.

Note : AFD is not compatible with ASMLIB and these packages can't exist together on a OS.

1) Starting with Oracle Grid Infrastructure 12C Release 1 (12.1.0.2), Oracle ASM Filter Driver (Oracle ASMFD) is installed with an Oracle Grid Infrastructure installation.

2) Oracle Automatic Storage Management Filter Driver (Oracle ASMFD) rejects write I/O requests that are not issued by Oracle software. This filter helps to prevent users with administrative privileges from inadvertently overwriting Oracle ASM disks, thus preventing corruption in Oracle ASM disks and files within the disk group. For disk partitions, the area protected is the area on the disk managed by Oracle ASMFD, assuming the partition table is left untouched by the user.

3) Oracle ASMFD simplifies the configuration and management of disk devices by eliminating the need to rebind disk devices used with Oracle ASM each time the system is restarted.

4) ASMFD is a superset of ASMLIB; therefore it includes base-ASMLIB features (permissions persistence & sharing open handles).

5) The Oracle ASM filter driver (ASMFD) is a kernel module that resides in the I/O path of the Oracle ASM disks. Logically, ASMFD provides an interface between Oracle binaries and the underlying operating environment which includes the storage hardware interfaces. Following are descriptions of the key capabilities of ASMFD:

• Reject non-Oracle I/Os.

 As a manager of Oracle storage, ASM is exposed to the capabilities of the Operating System (OS) when it comes to dealing with storage devices. In particular, non-Oracle commands have the ability to overwrite the contents of ASM disks which may lead to unrecoverable data loss. ASMFD only allows writes using an Oracle-specific interface and prevents non-Oracle applications from writing to ASM disks. This protects ASM from accidental corruption. 

• Reduce OS resource usage.

 An ASM instance contains numerous processes, or threads on Windows. Without ASMFD, each process that is I/O capable needs to have its own dedicated open file descriptor for each disk. When a database has thousands of processes accessing hundreds of disks, there is an explosion of file descriptors leading to considerable OS resource consumption. ASMFD exposes a portal device that can be used for all I/O on a particular host. The same portal device can be shared by all the processes associated with multiple database instances. The result is that using ASMFD, the required number of open file descriptors is drastically reduced. 

• Enable device name persistence.

 ASMFD do not require additional configuration to ensure device name persistence by using udev rules or third-party storage drivers. 

• Faster node recovery.

With the current implementation of Oracle Clusterware, init.d scripts panic the node in situations when Cluster Synchronization Services (CSS) is not functioning correctly. Using this mechanism, nodes are fenced to ensure the integrity of the rest of the cluster. While effective, this solution is costly because of the time required to reboot the node and restart all the required processes. ASMFD allows Oracle Clusterware to perform node level fencing without a reboot. So with ASMFD, it is possible to achieve the same result by restarting the Oracle software stack instead of rebooting the entire node. This process is just as effective, but far quicker.

----------------------

ASMLIB:

What is ASMLib? 

ASMLib is an optional set of tools and a kernel driver that can be inserted between ASM and the hardware, as well as an application library used by the Oracle database software to access ASM disks. It is a support library for the ASM feature of Oracle 10g and higher ,single instance database servers as well as RAC installations. ASM and regular database instances can use ASMLib as an alternative interface for disk access. ASMLib has three components: 

Kernel driver – oracleasm is a Linux kernel driver also known as the Oracle ASMLib kernel driver. This is an open-source (GPL) kernel driver and is available from Oracle as source and binary RPMs. Note that although this driver is provided under an open source license, it has not been accepted into the mainline Linux kernel. 

Support tools – oracleasm-support provides the utilities to manage the ASM library driver. Oracleasm-support is an open-source package (GPL) and is available from Oracle as source and binary RPMs.  

Application library – oracleasmlib package provides the actual ASM library. This is a closed source, binary-only RPM, available as a free download from Oracle. Oracle introduced ASMLib in 2004 to provide Oracle workloads on Linux with performance and stability comparable to equivalent workloads on UNIX. ASMLib addressed deficiencies – including a lack of async and direct I/O -- that existed in the 2.4 and higher Linux kernel. The Linux community addressed these deficiencies in the 2.6 Linux kernel with the addition of udev and device-mapper multipath which together with LVM provided native multipathing, scalable native volume management, and persistent device naming.  Additionally, the 2.6 Linux kernel added tools for handling large numbers of disks, especially SAN-attached disks. How is ASMLib delivered? ASMLib is available as a free download from the Oracle ULN website. Unlike ASM, ASMLib is not included with the Oracle database product.  

ASMLib is available for Linux only; it does not exist for any other platform. 

When would I want to use ASM and ASMLib together? 

What are the advantages and disadvantages? 

Oracle recommends using ASM with ASMLib or AFD together for better manageability and persistent device naming.  Note that Oracle makes no claims that ASM with ASMLib delivers performance benefits over ASM without ASMLib. 

Advantages 

• Perceived better manageability. 

• Well documented and recommended by Oracle. 

• Some Oracle DBAs and SysAdmins are trained in how to use ASM with ASMLib and are comfortable with this environment. 

• Optimized for database applications via direct and async I/O provided by the ASMLib kernel driver. 

Note Red Hat Enterprise Linux kernel 2.6.X and higher supports optimized direct and async I/O for all supported filesystem EXT3/4, XFS, GFS and NFS.

Disadvantages

 • Requires ASMLib kernel driver that is not included in the mainline Linux kernel. 

• ASMLib delivers no known performance benefits. 

• Red Hat Enterprise Linux with ASMLib does not have government security certification. 

• ASMLib is not compatible with SELinux. 

• ASMLib is not multipath aware and can silently choose to work through a single path, causing performance and reliability issues. 

• Non POSIX system calls for device access lead to issues with trouble-shooting and performance monitoring. 

Can I run an Oracle single instance database with ASM and without ASMLib? 

What are the advantages and disadvantages? What are the alternatives? 

Yes.  It is possible to run an Oracle Single Instance database with ASM and without ASMLib by specifying udev rules and/or using device mapper multipathing to achieve persistent device naming, both of which are a standard part of Red Hat Enterprise Linux 5 and Red Hat Enterprise Linux 6 and higher . 

ASM can use the following for storage resources: 

• Block devices (SATA, SAS, FC, iSCSI, FCoE, regular LUNs and LUNs with thin provisioning) with udev naming and/or multipathing. 

• Raw Devices. • NFS v3 with verified NAS vendors. 

Advantages 

• Using ASM without ASMLib avoids the overhead resulting from an extra, non-mainline Linux kernel driver.  

• Supported and documented by Oracle. 

• Using ASM without ASMLib provides the ability to use operating system's native I/O multipathing. 

• Using POSIX system calls for disk access enables improved performance monitoring and troubleshooting. 

Disadvantages • 

Customers may have standardized on Oracle deployments using ASMLib.

Can I use the Oracle single instance database without ASM or ASMLib ? What are my alternatives? How do I set it up? 

Yes. There are two alternatives to using the Oracle single instance database without ASM or ASMLib: 

• Use a local file system such as ext4. 

• Use logical volumes such as provided by lvm2 with dm-multipath or an optional third party commercial multipathing software. Red Hat will address this configuration in a forthcoming reference architecture whitepaper. 

Can I use Oracle Real Application Clusters (RAC) with ASM and without ASMLib? 

What are my alternatives? How do I set it up? 

ASMLib is an optional component of either an Oracle single instance or RAC configuration. Below are alternatives to using ASMLib for RAC: 

• ASM with LVM:  ASM + block devices + udev naming + multipathing. 

• Cluster file systems with cluster volume manager: gfs/clvm (Red Hat Enterprise Linux 5) or Symantec Cluster File System and Symantec Cluster Server (http://www.symantec.com/business/storage-foundation-for-oracle-rac). 

• NFS v3 with verified NAS vendors. 

Red Hat will address this configuration in a forthcoming reference architecture whitepaper. 

Can I use Oracle Real Application Clusters (RAC) without ASM or ASMLib? What are my alternatives? How do I set it up? 

Yes a preferred option is Udev rules.

Although most Oracle RAC installations with SAN-attached storage use ASM, RAC can be deployed without ASM if database files are located on an NFS server, certified cluster file system, or raw  devices. (Note that Oracle discourages the use of raw devices.) Red Hat will address this configuration in a forthcoming reference architecture whitepaper. 

I already use ASM and ASMLib. How do I migrate from using them to something else? 

This is a simple migration.  

Perform the following: 

1. The DBA stops all database instances using storage through ASM and ASMLib. 

2. The system administrator configures udev (or multipath) to assign permissions allowing ASM processes to have read and write access to ASM devices 

3. The DBA changes one ASM parameter to point ASM to the assigned devices. 

4. The DBA restarts the ASM instance and databases. 

5. As with all data-related changes, a backup is highly recommended.

Some theoretical benefits of ASMLIB API:

• always uses direct, async i/o 
• solves persistent device naming, even if underlying device moves across reboots
• solves file permissions and ownership
• reduced user mode to kernel mode context switches during I/O, possibly reducing CPU usage
• reduced file handle usage
• pass metadata such as I/O prioritization to storage device (don’t think this is implemented in the Linux version)

Note :

using direct i/o and async i/o have dependencies on Oracle parameters Disk_asynch_io =true and filesystemio_options=Set All .

----------------------------------

UDEV Rules:

What is Udev?

Udev is the mechanism used to create and name /dev device nodes corresponding to the devices that are present in the system. Udev uses matching information provided by sysfs with rules provided by the user to dynamically add the required device nodes.

Udev rules doesn't Linux kernel limitations on upgrade time instead of ASMLIB and AFD kernel dependencies.

All udev file should be under /etc/udev/rules.d/

basic format for udev rule is.

key1=”value”, key2=”value”, … keyN=”value”, name=”value”, symlink=”value”

You can find the key value by using udevadm command. Lets understand the environment key.

# multipath -ll mpathxy
mpathxy (wwn) dm-6 (vendor)
size=600G features=’1 queue_if_no_path’ hwhandler=’0′ wp=rw
`-+- policy=’service-time 0′ prio=1 status=active
|- 1:0:0:6 sdj 8:1 active ready running
|- 1:0:1:6 sdk 65:2 active ready running
|- 2:0:0:6 sdl 65:14 active ready running
`- 2:0:1:6 sdm 66:24 active ready running

So we have multipath mpathxy and reference device mapper is dm-6.

# udevadm info –query=all –path=/devices/virtual/block/dm-6

Above command will give you list of variables which you can use in udev rules.

You can make the following entry in /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL==”dm-*”,ENV{DM_UUID}==”mpath-wwn”,OWNER=”username”,GROUP=”groupname”,MODE=”0660″

Or you can use following script to create the /etc/udev/rules.d/99-oracle-asmdevices.rules

Generate udev rules for Physical and virtual server

 

/etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="dm-*",ENV{DM_UUID}=="mpath-wwn",OWNER="oracle",GROUP="oinstall",MODE="0660"

KERNEL=="dm-*",ENV{DM_UUID}=="mpath-wwn1",OWNER="oracle",GROUP="oinstall",MODE="0660"

# /sbin/udevadm control –reload-rules

# /sbin/udevadm trigger –type=devices –action=change

Now verify the udev rules via

# ls -l /dev/dm*

UDEV Rules Advantages:
• Using ASM without ASMLib avoids the overhead resulting from an extra, non-mainline Linux kernel driver.
• Supported and documented by Oracle.
• Using ASM without ASMLib provides the ability to use operating system’s native I/O multipathing.
• Using POSIX system calls for disk access enables improved performance monitoring and troubleshooting.

. There are no issues linux kernel upgrades on Asmlin & Asm filter drive.

UDEV Rules Disadvantages:

• Customers may have standardized on Oracle deployments using ASMLib.

-----------------------------------

♨️My experience:

You may find i/o tests and some benchmarks on using ASMLIB or ASM Filter Drive that speaks about performance better as a result .

Normally these i/o testing leads to AFD has better performance instead of ASMLIB. And ASMLIB usage is better performance instead of UDEV Rules.

Generally, ASM Filter Drive is very dependent on Grid version and not always available to you, but its features is noticeable and seductive for DBA.

Although isolation levels , security features on AFD is a good feature, But you should consider that these performance benchmarks is very dependent on hardware and resources, so this  not meaning that using them also is better performance for your environment, finally with attention to linux kernel and oracle grid upgrades and issues on AFD and ASMLIB, you can consider to use only UDEVs + Multipathing on Linux and DBA must consider maintenance cost of there tools with respect to its features also.

Therefore , on new kernel and Grid versions that support AFD, you can use it and enjoy its features and when Grid version not supported you can use ASMLIB, but it is possible you force to change and using Udev rules in the specific time in future cause of upgrade your linux or database.

So dont worry about performance effective on using Udev rules instead of ASMLIB or AFD, if realy there tools give us a performance rate, this feature instead of minimizing maintenance cost on future is a right and rational decision.

 

About Migrating to Oracle ASM Filter Driver From ASMLIB

If Oracle ASMLIB was installed, but not used earlier, you must create disk labels to enable migration of Oracle ASM disk groups to Oracle ASM Filter Driver (Oracle ASMFD) after installing Oracle Grid Infrastructure 12c Release 1 (12.1.0.2).

Oracle recommends that you temporarily move Oracle Cluster Registry (OCR) and voting files to another disk group if one is available, as described in "https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ostmg/administer-filter-driver.html#GUID-9C370239-C67D-4813-BE83-B0528397C02D

and migrate the disk group to use Oracle ASMFD. After migrating the disk group to use Oracle ASMFD, move OCR and voting files back to the disk group. You can similarly migrate any other disk groups if they contain OCR or voting files to ensure online migration of all disk groups to Oracle ASMFD.

Regarding 

Alireza Kamrani.