show point of ASM

How to STOP and START processes in Oracle RAC and Log Directory Structure

You need to follow the below steps to Stop and Start processes in Oracle RAC
Shutdown RAC Database
You need to Shutdown Database instances on each node. You can either use Oracle Enterprise Manager or SVRCTL to shutdown the instances. If you are using EM Grid control then set a blackout in Grid control for processes that you intend to shutdown. So that records for these processes indicate that the shutdown was planned.
Use below command to stop Enterprise Manager/Grid Control
$ORACLE_HOME/bin/emctl stop dbconsole
Use below command to shutdown all oracle RAC instances on all nodes.

• $ ORACLE_HOME/bin/srvctl stop database -d db_name

If you want to stop specific database instances use below command

• $ ORACLE_HOME/bin/srvctl stop database -d db_name –i instance_name

Shutdown Oracle ASM Instance
Once the database is stopped, proceed with ASM Instance shutdown.
Use below command to shutdown ASM instances on all nodes

• $ORACLE_HOME/bin/bin/srvctl stop asm -n node

Shutdown Node applications
Use below command to shutdown node apps on all RAC nodes

• $ORACLE_HOME/bin/bin/srvctl stop nodeapps -n node

Shutdown Oracle Clusterware
You need to Shutdown oracle clusterware or CRS as root and run below command on each node in the cluster.

• #crsctl stop crs

Please note that using above command will stop Oracle High availability services (OHAS) and Clustware stack in a single command
From 11g R2, you can do this in two stops
1. Stop Clustwerware stack on local node

• #crsctl stop cluster

You can stop the clusterware stack on all nodes in the cluster

• # Crsctl stop cluster –all

Where
-all Start clusterware on all nodes
-n Start clusterware on particular nodes
2. Stop Oracle High availability service demon on each node in the cluster.

• # crsctl stop has

Check the Status of Cluster
Once all process stopped run the below command to check the status of CRSD,CSSD,EVMD process.

• # crsctl check crs

If you see any process failed to stop then you can also use Force option to terminate the processes unconditionally.

• $ crsctl stop crs –all –f

Start processes in Oracle RAC
Follow the reverse sequence to start all processes in oracle RAC

• # crsctl start crs
• $ORACLE_HOME/bin/bin/srvctl start nodeapps -n node
• $ORACLE_HOME/bin/bin/srvctl start asm -n node
• $ORACLE_HOME/bin/srvctl start database -d db_name

If you come across any issues during startup orshutdown, check the Oracle Clusterware Component Log Files.
Oracle Clusterware Log Directory Structure
CRS_HOME/log/hostname/crsd/ - The log files for the CRS daemon CRS_HOME/log/hostname/cssd/ - The log files for the CSS daemon
CRS_HOME/log/hostname/evmd/ - The log files for the EVM daemon
CRS_HOME/log/hostname/client/ - The log files for the Oracle Cluster Registry (OCR)
CRS_HOME/log/hostname/racg/ - The log files for the Oracle RAC high availability component
CRS_HOME/log/hostname/racg/ - The log files for the Oracle RAC high availability component
CRS_HOME/log/hostanme/alert.log – The alert.log for Clusterware issues.
Please note that the CRS_HOME is the directory in which the Oracle Clusterware software was installed and hostname is the name of the node
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com
Posted by Satishbabu Gunukula at 2:58 PM 0 comments

Tuesday, February 16, 2010

ORA-600 [kfcNullConvert20] error on ASM instance

I have experienced ORA-600 error on ASM instance (10.2.0.2), which caused ASM instance restart on that particular node. Due to which all databases instances on that node are restarted, because they lost connectivity with ASM.
This issue is a known Bug 4682861 in Oracle and the effected version are 10.1.0.4 ,10.1.0.5 ,10.2.0.1 ,10.2.0.2. This BUG is fixed in 10.2.0.3 Server patch set and 11.1.0.6 Base release.
Check for Possible Error messages in ASM/Database alert.log and trace files.
If you find below SYMPTOMS then you are hitting a BUG 4682861.
ASM alert. log errors:
ORA-00600: internal error code, arguments: [kfcNullConvert20], [], [], [], [], [], [], []
DBW0: terminating instance due to error 471
Trace file (ASM DBWR trace file contains Stack Trace: kfcNullConvert and errors):-
error 600 detected in background process
ORA-00600: internal error code, arguments: [kfcNullConvert20], [], [], [], [], [], [], []
ksuitm: waiting for [5] seconds before killing DIAG
Database alert log errors:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
ASMB: terminating instance due to error 15064
Cause:
Due to Bug 4682861, ASM instance restarted.
Solution:
Option 1:- Upgrade to 10.2.0.3 or higher release
Option 2:-Download the patch from MetaLink:
i) Click on Patches & Updates Link.
ii) Enter patch number: 4682861 and Select your O/S
iii) Click Go.
iv) Download the patch for the Oracle release that you experienced this issue.
Please note that ANY ORA-600 error indicates Oracle has detected an internal inconsistency or a problem which it doesn’t know how best to address. These are *NOT* necessarily bugs and can occur for reasons such as running out of some resource, Operating System IO problems.. etc.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com
Posted by Satishbabu Gunukula at 4:20 PM 0 comments

Monday, October 5, 2009

Create, Drop and Alter ASM disk groups

Crete Disk Group:
Create Disk groups using the CREATE DISKGROUP statement and specify the level of redundancy.

Disk group redundancy types:-
NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware RAID or mirroring.

• SQL> CREATE DISKGROUP data NORMAL REDUNDANCY

FAILGROUP failure_group_1 DISK '/dev/sda1' NAME dataa1,'/dev/sda2' NAME dataa2,
FAILGROUP failure_group_2 DISK '/dev/sdb1' NAME datab1,'/dev/sdb2' NAME datab2;

Drop Disk Group:
Drop disk group using DROP DISKGROUP statement.

• SQL> DROP DISKGROUP data INCLUDING CONTENTS;

Alter Disk Group:
Add or remove disks from disk groups Using ALTER DISKGROUP statement. You can also use wildcard "*" to reference disks.

Add a disk.

• SQL> ALTER DISKGROUP data ADD DISK '/dev/datac1', '/dev/datac2';

Add all reference disks

• SQL> ALTER DISKGROUP data ADD DISK '/dev/datad*;

Drop/remove a disk.

• SQL> ALTER DISKGROUP data DROP DISK datab2;

The UNDROP command used to undo only pending drop of disks. After you drop the disks you cannot revert.

• SQL> ALTER DISKGROUP data UNDROP DISKS;

Diskgroup Rebalance:
Disk groups can be rebalanced manually Using REBALANCE clause and you can modify the POWER clause default value.

• SQL> ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are mounted at ASM instance startup and dismounted at shutdown. Using MOUNT and DISMOUNT options you can make one or more Disk Groups available or unavailable.

• SQL> ALTER DISKGROUP data MOUNT;
• SQL> ALTER DISKGROUP data DISMOUNT;
• SQL> ALTER DISKGROUP ALL MOUNT;
• SQL> ALTER DISKGROUP ALL DISMOUNT;

DiskGroup Check:
Use CHECK ALL to verify the internal consistency of disk group metadata and repair in case of any error.

• SQL> ALTER DISKGROUP data CHECK ALL;

DiskGroup resize:
Resize the one or all disks in the Diskgroup.

Resize all disks in a failure group.

• SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 1024G;

Resize a specific disk.

• SQL> ALTER DISKGROUP data RESIZE DISK dataa1 SIZE 1024G;

Resize all disks in a disk group.

• SQL> ALTER DISKGROUP data RESIZE ALL SIZE 1024G;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 1:14 PM 1 comments

Migrate Database to ASM Using RMAN

We are Using RMAN to relocate non-ASM files to ASM files. The ASM files cannot be accessed through normal OS interfaces.

Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.

• SQL> select * from V$CONTROLFILE;
• SQL> select * from V$LOGFILE;

Step 2: Shutdown the database.

• SQL> SHUTDOWN IMMEDIATE;

Step3: Modify the target database parameter file:
(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.

Step 4: Startup nomount mode and Restore control file

• RMAN> STARTUP NOMOUNT;

Restore the control file into the new location.

• RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';

Step 5: Startup mount mode and backup the Database.

• RMAN> ALTER DATABASE MOUNT;

Backup database into ASM disk group.

• RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';

Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.

• RMAN> SWITCH DATABASE TO COPY;
• RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;

or
Create new redo logs in ASM Disk group and delete the old redo log files.

Step 7: Open Database and create temporary tablespace.
Open database using resetlogs

• SQL> ALTER DATABASE OPEN RESETLOGS;

Create temporary tablespace in ASM disk group.

• SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;

Step 8: Drop old database files.

• 1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;

2. Remove all remaining Non-ASM database files using OS commands

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 12:59 PM 4 comments

Tuesday, September 29, 2009

Createdisk, Deletedisk and Querydisk in ASM

The /etc/init.d/oracleasm script is used to create, delete and query ASM disks and make disks available.

Create and Delete ASM disk:
Run below command to Create ASM disks.

• # /etc/init.d/oracleasm createdisk DATA1 /dev/sdc

Creating Oracle ASM disk "DATA1" [ OK ]

Run below command to Delete ASM disks.

• # /etc/init.d/oracleasm deletedisk DATA1

Deleting Oracle ASM disk "DATA1" [ OK ]

Query/List/Scan ASM disk:
Run the below querydisk command to see if the Disk/Device is used by ASM:

• # /etc/init.d/oracleasm querydisk /dev/sdc

Checking if device "/dev/sdc" is an Oracle ASM disk [ OK ]

• # /etc/init.d/oracleasm querydisk DATA1

Checking for ASM disk "DATA1" [ OK ]

Run below command to list Existing disks

• # /etc/init.d/oracleasm listdisks

DATA1

Run the below command to scan the disks created on another node.

• # /etc/init.d/oracleasm scandisks

Scanning system for ASM disks [ OK ]

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 2:26 PM 0 comments

Monday, August 31, 2009

Delete Archivelog files without using RMAN

Here I am explaining two methods to delete archive logs and other database files with out using RMAN from ASM Disk Group in Oracle 10g/11g.

Method 1: asmcmd - ASM command-line utility

• ASMCMD> rm file_name
• For ex:-ASMCMD> rm ‘+dgroup2/testdb/archivelogs/thread_1_seq_363.510.1’
• Or
• ASMCMD> rm ‘+dgroup2/testdb/datafile/USERS.250.5334166963’
• If you use a wildcard, rm deletes all matches except non-empty directories (unless the -r flag is used). The rm command can delete the file or alias only if the file is not currently in use by a client database.

Method 2: SQLPLUS utility

• SQL> ALTER DISKGROUP DROP file

For ex:- SQL> ALTER DISKGROUP FLASH DROP FILE ‘+FLASH/testdb/archivelog/2009_08_11/thread_1_seq_363.510.1';

The “asmcmd” and “sqlplus” commands will not update the database views (V$ARCHIVED_LOG, V$FLASH_RECOVERY_AREA_USAGE), controlfile, Recovery Catalog that the files have been removed.

To update the Database views, control file or RMAN Catalog about deleted files you need to run the below command from RMAN.

• RMAN> CROSSCHECK ARCHIVELOG ALL;
• RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Click here to learn How to delete archive logs from ASM.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 9:06 AM 0 comments

Monday, August 17, 2009

Install and Configure ASMLib in 10g (Automatic Storage Management)

The Oracle ASM feature was introduced in Oracle 10g Release 1.

There are two methods to configure ASM on Linux.

• 1. Configure ASM with ASMLib I/O: This method creates all Oracle database files on raw block devices, which are managed by ASM using ASMLib calls. ASMLib works with block devices and raw devices are not required with this method.

• 2. Configure ASM with Standard Linux I/O: This method creates Oracle database files on raw character devices, which are managed by ASM using standard Linux I/O system calls. It requires creating RAW devices for all disk partitions used by the ASM.

Here we will “Configure ASM with ASMLib I/O” method.

Step 1: Download and Install ASMLib

Download Oracle “ASMLib” software from below link and follow the link for your platform.
http://www.oracle.com/technology/tech/linux/asmlib/index.html

You must install all three packages for the kernel you are running. Use “uname –r “command to determine the version of your kernel.

oracleasm-support-version.cpu_type.rpm
oracleasm-kernel-version.cpu_type.rpm
oracleasmlib-version.cpu_type.rpm

See the below example to install the packages and run the command as root.

• # rpm -ivh oracleasm-support-2.0.3-1.x86_64.rpm \

> oracleasm-2.6.9-67.ELsmp-2.0.3-1.x86_64.rpm \
> oracleasmlib-2.0.2-1.x86_64.rpm

Step 2: Configure and Enable Oracle ASM

Run the below command to configure the Oracle ASM and it will ask for the user and group that default to owing the ASM drivers access point.

• # /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration [ OK ]
Creating /dev/oracleasm mount point [ OK ]
Loading module "oracleasm" [ OK ]
Mounting ASMlib driver filesystem [ OK ]
Scanning system for ASM disks [ OK ]

This command will load the ASM driver and mount the ASM driver filesystem. By selecting “y” during the configuration, the system will always load the module and mount the file system on system boot.

Run the below command to enable automatic start

• #/etc/init.d/oracleasm enable

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 6:07 PM 0 comments

expdp_procedure

create or replace procedure expdp_clt_perf_p_mod_out(p_start_time in date,
                                                   p_end_time   in date) is
  v_int_sql    varchar2(3000);
  v_table_name varchar2(100);
  v_tab_name   varchar2(100);
begin
  for i in (select distinct table_name
              from user_tab_columns
             where column_name in ('STAMPTIME')
               AND TABLE_NAME LIKE 'CLT%') loop
    v_table_name := i.table_name;
    --
 
    for k in (select 'expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile='||v_table_name||'.dmp query='||v_table_name||
        ':\"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'')'||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \" tables='||v_table_name||'' expdp_select_clt
                from dual) loop
     --
     --
      insert into wt_expdp_sel(expdp_text,table_name) values( k.expdp_select_clt, v_table_name ) ;
      commit;
    end loop;
  end loop;
  -----
  -----
  for f in (select distinct table_name
              from user_tab_columns
             where column_name in ('START_TIME')
               AND (TABLE_NAME LIKE 'PERF%'
               or TABLE_NAME LIKE 'P_%'
               or TABLE_NAME LIKE 'MOD_%')) loop
    v_tab_name := f.table_name;
 
      for m in (select 'expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile='||v_table_name||'.dmp query='||v_table_name||
        ':\"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'')'||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \" tables='||v_table_name||'' expdp_select_more
                   
                from dual) loop
      insert into wt_expdp_sel(expdp_text,table_name) values( m.expdp_select_more, v_tab_name ) ;
      commit;
    end loop;
  end loop;
end expdp_clt_perf_p_mod_out;

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

2.wt_expdp_sel表：

-- Create table
create table WT_EXPDP_SEL
(
  expdp_text   VARCHAR2(3000),
  table_name VARCHAR2(40)
)
tablespace WCDMA ;
------------------------------

3.results:

1    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_2GNCELL.dmp query=CLT_CM_W_HW_2GNCELL:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_2GNCELL   
2    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_ADJNODE.dmp query=CLT_CM_W_HW_ADJNODE:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_ADJNODE   
3    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_AICH.dmp query=CLT_CM_W_HW_AICH:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_AICH  

expdp_query_where

1.USING 'QUERY' parameter on Command line

Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue

$ expdp query=employees:"where salary>10000" tables=employees

This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):

$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary>10000\ order by salary" tables=employees 
--------------------------------------------------------------
success sentence:
1.expdp scott/tiger directory=scottdump dumpfile=scott.dmp logfile=expdpscott.log query=emp:\"where deptno>20\"  tables=emp
--
2.expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_2GNCELL.dmp query=CLT_CM_W_HW_2GNCELL:\"where STAMPTIME
  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS') \" tables=CLT_CM_W_HW_2GNCELL
--------------------------------------------------------------
2.oracle`s answer:
expdp scott/tiger@db10g directory=TEST_DIR dumpfile=scott.dmp logfile=expdpSCOTT.log query=emp:\"where department_id>10\" tables=emp
--------------------------------------------------------------
3.Oracle Data Pump Export/Import

Oracle Data Pump utility is used for exporting data and metadata into set of operating system files and it is newer, faster and flexible alternative to “export/import” utilities.
Oracle Datapump utility introduced in Oracle 10g Release1 and this utility can be invoked using expdp(export) and impdb(import) commands. User need to specify the export/import parameters to determine the operation and you can specify the parameters on the command line or in a parameter file.
The expdp and impdp uses the procedures provided in the DBMS_DATAPUMP package to execute the commands and DBMS_METADATA package is used to move the data.
Please note that it is not possible to start or restart data pump jobs on one instance in Oracle RAC if jobs currently running on other instances.
---------------------------------------------------------------------------
Oracle Data Pump Export :

1. Create directory object as SYS user.
SQL> create or replace directory export_dir as '/oradata/export’;

2. Grant Read/Write privilege on the directory to the user, who invokes the Data pump export.
SQL> grant read,write on directory export_dir to test_user;

3. Take Data Pump Export
Click here to see Roles/privileges required for Export modes.
Oracle data pump export examples for all 5 modes.

(i) Full Database Export
$ expdp test_user/test123 full=y directory=export_dir dumpfile=expdp_fulldb.dmp logfile=expdp_fulldb.log

(ii) Schema Export
$expdp test_user/test123 schemas=test_user directory= export _dir dumpfile=expdp_test_user.dmp logfile=expdp_test_user.log
If you want to export more than one schema then specify the schema names separated by comma.

(iii)Table Export
$ expdp test_user/test123 tables=emp,dept directory= export _dir dumpfile=expdp_tables.dmp logfile=expdp_tables.log
You can specify more than one table.

(iv) Tablespace Export
$ expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log
You can specify more than one tablespace.

(v) Transportable tablespace
$ expdp test_user/test123 transport_tablespaces=test_user_tbs transport_full_check=y directory= export _dir dumpfile=expdp_trans_tbs.dmp logfile=expdp_trans_tbs.log
Click here to learn more on Transportable Tablespace with examples.

Oracle Data Pump Import :
Data Pump Import utility is used for loading an export dump files into a target system and we can load one or more files.
Copy the dump file to the target system where you to import.

1. Create directory object as SYS user.
SQL> create directory import_dir as '/oradata/import';

2. Grant Read/Write privilege on the Directory to the user, who invokes the Data Pump import.
SQL> grant read,write on directory import_dir to test_user;

3. Import the data using Data Pump Import.
Oracle data pump import examples for all 5 modes.

(i) Full Database Import
$ impdp test_user/test123 full=Y directory=imp_dir dumpfile=expdp_fulldb.dmp logfile=imp_fulldb.log

(ii) Schema Import
$impdp test_user/test123 schemas=test_user directory=imp_dir dumpfile=expdp_test_user.dmp Logfile=impdp_test_user.log

(iii) Table Import
$ impdp test_user/test123 tables=emp,dept directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log
From 11g, you can reaname a table during the import
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
$ impdp test_user/test123 remap_table=test_user.emp:emp1 directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log
Tables will not be remapped if they already exist even if the TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND

(iv) Tablespace Import
$ impdp test_user/test123 tablespaces=test_user_tbs directory=imp_dir dumpfile=expdp_tbs.dmp logfile=impdp_tbs.log
Above example imports all tables that have data in tablespaces test_user_tbs and it assumes that the tablespaces already exist.

(v) Transportable Tablespace
Click here to to import data using Transportable Tablespace method.

Common Errors with Data pump import (impdp) utility:-
1. ORA-31631: privileges are required
   ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping
Cause: A user attempted to remap objects during an import but lacked the IMPORT_FULL_DATABASE privilege.
Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

2. ORA-31631: privileges are required
    ORA-39161: Full database jobs require privileges
Cause: Either an attempt to perform a full database export without the EXP_FULL_DATABASE role or an attempt to perform a full database import over a network link without the IMP_FULL_DATABASE role.
Action: Retry the operation in a schema that has the required roles.

3. ORA-01950: no privileges on tablespace "string"
    Cause: User does not have privileges to allocate an extent in the specified tablespace.
   Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.
Click here to learn Roles/ privileges required for Data pump Export and Import.

4. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    IMP-00017: following statement failed with ORACLE error 3113:
    "BEGIN "
    "SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE   SYS.DBMS_RULE_ADM.CREATE_EVALUATIO" "N_CONTEXT_OBJ, 'SYS',TRUE);"
Cause: Import fails while executing the following command.
Action: Login as sys and run the following scripts
$ORACLE_HOME/rdbms/admin/dbmsread.sql
$ORACLE_HOME/rdbms/admin/prvtread.plb

5. Import failed with below errors
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
Cause: The user that you are importing does not have privileages on CTXSYS.DRIIMP package or CTXSYS user does not exists
Action: Create CTXSYS user or grant required permissions
Please see the Data pump Export and Import related documents:
Click here for Data Pump Export modes and Interfaces.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 6:40 PM

exp_procedure

create or replace procedure exp_clt_perf_p_mod_out(p_start_time in date,
                                                   p_end_time   in date) is
  v_int_sql    varchar2(3000);
  v_table_name varchar2(100);
  v_tab_name   varchar2(100);
  --v_sel_sql varchar2(3000) ;
begin
  for i in (select distinct table_name
              from user_tab_columns
             where column_name in ('STAMPTIME')
               AND TABLE_NAME LIKE 'CLT%') loop
 
    v_table_name := i.table_name;
    for k in (select 'exp wcdma/uwaysoft2010@db122 file = ' || v_table_name ||
                     '.dmp tables = ' || v_table_name ||
                     ' query = \"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'') '||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \"'  exp_select_clt
                from dual) loop
     /* v_int_sql := 'insert into wt_exp_sel(exp_text,table_name) values('''||k.exp_select_clt||''','''||v_table_name||''')';
      execute immediate v_int_sql
              USING p_start_time , p_end_time ;
      commit ;*/
      insert into wt_exp_sel(exp_text,table_name) values( k.exp_select_clt, v_table_name ) ;
      commit;
    end loop;
  end loop;
  -----
  -----
  for f in (select distinct table_name
              from user_tab_columns
             where column_name in ('START_TIME')
               AND (TABLE_NAME LIKE 'PERF%'
               or TABLE_NAME LIKE 'P_%'
               or TABLE_NAME LIKE 'MOD_%')) loop
 
    v_tab_name := f.table_name;
    for m in (select 'exp wcdma/uwaysoft2010@db122 file = ' || v_tab_name ||
                     '.dmp tables = ' || v_tab_name ||
                     ' query = \"where START_TIME between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'') '||
                     ' and ' || 'to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')   \" ' exp_select_more
                from dual) loop
      insert into wt_exp_sel(exp_text,table_name) values( m.exp_select_more, v_tab_name ) ;
      commit;
    end loop;
  end loop;
end exp_clt_perf_p_mod_out;
----------------------------------
----------------------------------
1    exp wcdma/uwaysoft2010@db122 file = CLT_CM_W_AL_ALWAYSONTIMER.dmp tables = CLT_CM_W_AL_ALWAYSONTIMER query = \"where STAMPTIME  between to_date('2012-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS')  and to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS')  \"    CLT_CM_W_AL_ALWAYSONTIMER
2    exp wcdma/uwaysoft2010@db122 file = CLT_CM_W_AL_AOONDCHPARAM.dmp tables = CLT_CM_W_AL_AOONDCHPARAM query = \"where STAMPTIME  between to_date('2012-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS')  and to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS')  \"    CLT_CM_W_AL_AOONDCHPARAM