Data Infrastructure Insights – For Modern DBAs: June 2017

Wednesday, 7 June 2017

Oracle Export/Import DataPump Parameter ACCESS_METHOD

How to Fix LONG RAW Import Issues Using ACCESS_METHOD in Data Pump

Hello Friends,

While performing an export-import task using Oracle Data Pump, I encountered issues with tables that had the data type LONG RAW. The import would fail or throw warnings.

After investigating, I found that using the ACCESS_METHOD parameter during export and import helps avoid this problem.

✅ Export Using ACCESS_METHOD

expdp dumpfile=your_export.dmp \
logfile=your_export.log \
tables=owner.tablename \
access_method=direct_path

✅ Import Using ACCESS_METHOD

impdp dumpfile=your_export.dmp \
logfile=your_import.log \
access_method=direct_path \
remap_schema=old:new \
remap_tablespace=old_ts:new_ts \
table_exists_action=replace

After using access_method=direct_path, the import completed successfully without any errors or warnings.

🔗 Reference

Oracle Metalink Doc ID:
Export/Import DataPump Parameter ACCESS_METHOD – How to Enforce a Method of Loading and Unloading Data? [ID 552424.1]

For large exports to NFS storage, ensure your NFS mount setup supports high throughput.

📘 You May Also Like

Monday, 5 June 2017

Silent Installation Guide for 12c Release 1 (12.1) for Linux x86-64(Using response file)

Oracle 12c Silent Installation Guide (Step-by-Step with Response File)

This guide will help you perform an Oracle 12c software installation in silent mode using a response file. The steps include creating required users and groups, setting kernel parameters, and running the installer silently.

Step 1: Create Oracle Groups and User

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

Step 2: Set Kernel Parameters

Edit /etc/sysctl.conf and add the following lines:

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

Apply the changes:

sysctl -p

Step 3: Set Shell Limits for Oracle User

Edit the following files:

/etc/security/limits.conf

oracle   soft   nproc    2047
oracle   hard   nproc    16384
oracle   soft   nofile   1024
oracle   hard   nofile   65536
oracle   soft   stack    10240

/etc/pam.d/login

session required pam_limits.so

Step 4: Create Required Directories

mkdir -p /u01/app/oracle
mkdir -p /u01/app/oraInventory
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Step 5: Configure Oracle Environment for User

Update the .bash_profile of the oracle user:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin

Step 6: Prepare the Response File

Edit the db_install.rsp file and ensure the following entries are set:

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=your-hostname
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.BACKUPDBA_GROUP=dba
oracle.install.db.DGDBA_GROUP=dba
oracle.install.db.KMDBA_GROUP=dba
DECLINE_SECURITY_UPDATES=true

Step 7: Run the Installer in Silent Mode

Switch to the directory where you extracted the Oracle software and run:

./runInstaller -silent -responseFile /path/to/db_install.rsp

Follow logs under $ORACLE_BASE/oraInventory/logs for installation progress.

Post-Installation (Root Scripts)

Once the installer finishes, run the following scripts as root when prompted:

/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/12.1.0/dbhome_1/root.sh

Conclusion

Your Oracle 12c software installation using silent mode is now complete. You can proceed with database creation using dbca -silent or create manually.

Pages