About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, June 29, 2013

EXPDP and IMPDP utility


Expdp and Impdp utility are datapump utilities They are used to take logical backup of the database. They can be used to take database level, schema level, object level , tablespace level backups.

Command for Full Database export :-

C:\Users\NewAdmin> expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y &   ----- (if database is 11g)

C:\Users\NewAdmin> expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=METADATA_ONLY full=y &   ----- (if database is 10g)

############# Compression = all is not available in 10g version##############

Command for Full Database import :-

1. First create all the tablespaces which are there in old database either with same name or with different name. If tablespace names are different then remap_tablespace has to be used.

2. If you want to create new users then while importing remap_schema has to be used, otherwise it will create users with the same name.

Schema with same name and tablespaces with same name as they are in old database then PFB command to import full database  :-

C:\Users\NewAdmin> impdp system/test directory=datatest dumpfile=expdp_full_29062013.dmp logfile=impdp_full_29062013.log full=y

Schema and tablespaces with different name then PFB command to import full database :-

C:\Users\NewAdmin> impdp system/test directory=datatest dumpfile=expdp_full_29062013.dmp logfile=impdp_full_29062013.log full=y remap_schema= amit:test,amit1:test1,amit2:test2 remap_tablespace=amit_tbs:test_tbs,amit1_tbs:test1_tbs,amit2_tbs:test2_tbs &

Schema Level Export :-

C:\Users\NewAdmin>expdp amit/amit directory=datapump dumpfile=expdp_schema_29062013.dmp logfile=expdp_schema_29062013.log compression=all &

Schema level import :-

If we want to import Amit schema contents to test schema contents and that to in tablespace of test schema the PFB command :-

C:\Users\NewAdmin>impdp test/test directory=datapump dumpfile=expdp_schema_29062013.dmp logfile=impdp_schema_29062013.log remap_schema=srilanka:test remap_tablespace=amit_tbs:test_tbs &

Schema Level export without data :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_without_data_29062013.dmp logfile=expdp_without_data_29062013.log content=METADATA_ONLY compression=all &

Schema Level export without metadata :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_without_data_29062013.dmp logfile=expdp_without_data_29062013.log content=DATA_ONLY compression=all &

Table Level Export :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_table_29062013.dmp logfile=expdp_table_29062013.log tables=temp,temp1 compression=all &  

Table Level Import :-

C:\Users\NewAdmin> impdp test/test directory=datapump  dumpfile=expdp_table_29062013.dmp logfile=impdp_table_29062013.log remap_schema=amit:test &

Table Level Import from a schema or database level dump :-

impdp test/test schemas=amit directory=datapump dumpfile=exp_am.dmp remap_schema=amit:test INCLUDE=TABLE:\"IN \(\'DB_T_ALARMS_TTT\'\)\"

Exclude some table or index from a schema level export :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_exclude_29062013.dmp logfile=expdp_exclude_29062013.log exclude=table:\"in\(\'temp\',\'temp1\'\)\" compression=all &

Exclude Some Schema and some tables from a Full Database Export :-

C:\Users\NewAdmin>expdp system/orcl directory=datapump dumpfile=expdp_exclude_29062013.dmp logfile=expdp_exclude_29062013.log exclude=table:\"in\(\'temp\',\'temp1\'\)\" exclude=schema:\"in\(\'AMIT\'\)\" compression=all full=y &

I hope this article helped you.

Regards,
Amit Rath

How to kill, cancel, restart, stop data pump jobs

Datapump jobs (expdp, impdp) can be stopped, killed or resumed from the database level. Killing or stopping a datapump job from Oslevel does not kill or stop it, we have to do it from database . PFB :-

Exporting a Full database backup :-

C:\Users\NewAdmin>expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y &

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 19:46:41 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Stop a datapump job :-

Datapump jobs can be stopped in two ways, either press CTRL + C you will get EXPDP prompt or attach to a already running job and after that write STOP_JOB

If datapump job is not running in background then once you press CTRL+C you will get EXPDP prompt . PFB :-

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): y

Datapump job is stopped.

if datapump is running in background then you have to attach a datapump job, find out the datapump job name by using below query :-

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------- -----------------------------
SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      EXECUTING

Now attach to a running job:-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:01:45 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 42902560C22D46BDA6924724126F59FD
  Start Time: Saturday, 29 June, 2013 20:01:08
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
     COMPRESSION           ALL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\DATAPUMP\EXPDP_FULL_29062013_1.DMP
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): y

you will get a message like this :-

Job "SYSTEM"."SYS_EXPORT_FULL_02" stopped by user request at 20:02:01

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------
SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      NOT RUNNING

Restart a Stopped datapump Job :-

Attach to a stopped datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:08:17 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 42902560C22D46BDA6924724126F59FD
  Start Time: Saturday, 29 June, 2013 20:08:20
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
     COMPRESSION           ALL
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\datapump\expdp_full_29062013_1.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED

Export> START_JOB

Export> CONTINUE_CLIENT
Job SYS_EXPORT_FULL_02 has been reopened at Saturday, 29 June, 2013 20:08
Restarting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
Processing object type DATABASE_EXPORT/TABLESPACE

Job is again restarted.

Kill a datapump job :-

Attach to a running datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:12:55 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 452263D9B00047A9BCD6E97DE83878F4
  Start Time: Saturday, 29 June, 2013 20:12:57
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y
     COMPRESSION           ALL
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\datapump\expdp_full_29062013.dmp
    bytes written: 5,570,560

Worker 1 Status:
  Process Name: DW00
  State: RUNNING

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): y

DIfference between KILLING and STOPPING a datapump job is that, once you killed a datapump job you cannot able to restart it . But if we stop a datapump job we can restart it. Killing a datapump job will delete the dump and log files also.

I hope this article helped you.

Regards,
Amit Rath

Wednesday, June 5, 2013

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

Today I faced below error when I was trying to connect to RMAN prompt :-

bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 12:56:32 2013

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

connected to target database: AMIT (DBID=913650087)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded

I checked that these errrors(RMAN-06900, RMAN-06901) were just warnings from RMAN, whenever we get this error check for the additional error message. Here we got additional error message as  "ORA-19921: maximum number of 128 rows exceeded". Now our solution depends on ORA-19921 error which says that Oracle is not able to write output in V$RMAN_OUTPUT due to too many RMAN connections , check for the number of RMAN connections :-

bash-3.2$ ps -ef |grep rman
  oracle 24314072        1  50   Jun 03      - 369:15 rman target /
  oracle 35258568 37748790   0 13:00:59  pts/1  0:00 grep rman
  oracle 55115904 28311624   0 12:59:30  pts/2  0:00 rman target /

From output of above command we come to know that two rman sessions are there. If any backup is not going on then kill these RMAN sessions through kill command.

bash-3.2$ kill -9 55115904
bash-3.2$ kill -9 24314072
bash-3.2$ ps -ef |grep rman
  oracle 33685564 37748790   0 13:01:34  pts/1  0:00 grep rman

Now try to connect to RMAN :-

bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 13:01:38 2013

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

connected to target database: AMIT (DBID=903650087)

RMAN>


I hope this article helped you.

Regards,
Amit Rath