Advertisements
Skip to content
April 8, 2014 / Shivananda Rao P

Recovering a Dropped Table using RMAN and Datapump Options

Here is a scenario where a table is dropped from a database and we know the time the able was dropped. The table needs to be recovered until time the table was dropped from the database. In my previous post, I had demonstrated this using “Tablespace Point In Time Recovery” but I had mentioned the disadvantage of using this method.

In this post, I’m demonstrating on how to recover a dropped table or a schema using the traditional method which involves the below steps.

1. Create a dummy database from the original database and recover it until time the table was not dropped.
2. Export the table from the dummy database.
3. Import the table into the original database.

Database name: SRPRIM
RAC : YES
NODES: 10gnode1 (instance : SRPRIM1), 10gnode2 (instance : SRPRIM2)
Table Name: TEST
Table Owner: SHIVU

Dummy Database name: TMP

In this scenario, I was aware of the time the table was dropped. It was dropped in at 11:17 AM 5th April 2014.


11:17:28 SQL> drop table shivu.test;

Table dropped.

Elapsed: 00:00:01.65

Let me check when was the last full backup happened successfully.

TO_CHAR(START_TIME,' TO_CHAR(END_TIME,'DD STATUS    INPUT_TYPE
-------------------- -------------------- --------- -------------
05-APR-2014 10:47:11 05-APR-2014 10:49:03 COMPLETED DB INCR

From the above outcome, we could notice that the DB incremental level 0 backup was successfully occurred on 5th April 10:49 AM.

Let’s check the status of the RAC database and the services running on it.


[oracle@10gnode1 ~]$ srvctl status database -d srprim -v -f

Instance srprim1 is running on node 10gnode1 with online services newsrv. Instance status: Open.
Instance srprim2 is running on node 10gnode2 with online services newsrv. Instance status: Open.

Create a pfile from one of the instances. This pfile would be used to create a new database called “TMP”.
Here I’m using the node 10gnode1 for my demonstrations.


[oracle@10gnode1 ~]$ sqlplus sys/oracle@srprim1 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 7 11:38:38 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create pfile='/u02/bkp/initsrprim.ora' from spfile;

File created.

The pfile is created under location “/u02/bkp/” in the node 10gnode1. Remove the below cluster parameters and create a dummy pfile called inittmp.ora:


*.cluster_database=TRUE
srprim2.instance_number=2
srprim1.instance_number=1
srprim2.thread=2
srprim1.thread=1
srprim1.undo_tablespace='UNDOTBS1'
srprim2.undo_tablespace='UNDOTBS2'

Here is the PFILE that I’m using to create the dummy database.


[oracle@10gnode1 bkp]$ cat inittmp.ora
tmp.__db_cache_size=436207616
tmp.__java_pool_size=16777216
tmp.__large_pool_size=16777216
tmp.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
tmp.__pga_aggregate_target=520093696
tmp.__sga_target=754974720
tmp.__shared_io_pool_size=0
tmp.__shared_pool_size=268435456
tmp.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/tmp/adump'
*.audit_trail='db'
*.cluster_database=FALSE
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+FRA'
#*.control_files='+DATA/srprim/controlfile/current.260.835353389','+FRA/srprim/controlfile/current.256.835353391'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tmp'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srprimXDB)'
#srprim2.instance_number=2
#srprim1.instance_number=1
*.memory_target=1275068416
*.open_cursors=300
*.processes=150
#*.remote_listener='node-scan:1521'
*.remote_login_passwordfile='exclusive'
#srprim2.thread=2
#srprim1.thread=1
#srprim1.undo_tablespace='UNDOTBS1'
tmp.undo_tablespace='UNDOTBS1'

Add the below TNS entry for the tmp database in the TNSNAMES.ora file of the 10gnode1.


TMP =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10gnode1)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = tmp) (UR = A)
  )
)

Add a static entry in the listener.ora file of the 10gnode1 server as shown below and reload the listener.

SID_LIST_LISTENER=
(SID_LIST =
  (SID_DESC=
    (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3)
    (SID_NAME=tmp)
  )
)

Create a password file for the temporary database.


[oracle@10gnode1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwtmp password=oracle

Startup the TMP database in nomount mode using the password that was created in the previous post.

[oracle@10gnode1 bkp]$ sqlplus sys/oracle@tmp as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 7 13:27:30 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u02/bkp/inittmp.ora';
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes

Connect to RMAN with target database as the RAC database “SRPRIM” and the auxiliary database as the dummy database “TMP”. Set the time until which the database TMP needs to be recovered. This time would be the time until before the table was dropped.

Here I’m using the RMAN duplicate database method to create the TMP database and not the traditional “RMAN restore/recover database until time ” option. The reason for using this method is that, there is already a database called SRPRIM running on node 10gnode1 and 10gnode2 and if I would be using the traditional method of restore/recover on the same node, then I’ll have to restore the database with the same name as SRPRIM and later change the name which is not possible due to the SRPRIM database which is already running.

Instead, if I would be using the RMAN duplicate database method, then I can create the dummy database with my own name on the same node (10gnode1) on the fly and no requirement to restore/recover the database with the same name.

Here, the backups of SRPRIM database is stored under location “/u02/bkp” on the node 10gnod1.

[oracle@10gnode1 ~]$ rman target sys/oracle@srprim auxiliary sys/oracle@tmp

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 7 13:54:13 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rightsreserved.

connected to target database: SRPRIM (DBID=251294829)
connected to auxiliary database: TMP (not mounted)

RMAN> run
2> {
3> set until time "to_date('05-04-2014 11:15:00','DD-MM-YYYY HH24:MI:SS')";
4> allocate auxiliary channel dup1 device type disk;
5> allocate auxiliary channel dup2 device type disk;
6> duplicate target database to TMP nofilenamecheck;
7> }

executing command: SET until clause

using target database control file instead of recovery catalog
allocated channel: dup1
channel dup1: SID=32 device type=DISK

allocated channel: dup2
channel dup2: SID=33 device type=DISK

Starting Duplicate Db at 07-APR-14

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1272213504 bytes

Fixed Size 1344680 bytes
Variable Size 838863704 bytes
Database Buffers 419430400 bytes
Redo Buffers 12574720 bytes
allocated channel: dup1
channel dup1: SID=29 device type=DISK
allocated channel: dup2
channel dup2: SID=30 device type=DISK

contents of Memory Script:
{
set until scn 2139313;
sql clone "alter system set control_files =
''+DATA/tmp/controlfile/current.272.844264497'', ''+FRA/tmp/controlfile/current.324.844264497'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''SRPRIM'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TMP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set control_files = ''+DATA/tmp/controlfile/current.272.844264497'',
''+FRA/tmp/controlfile/current.324.844264497'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''SRPRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''TMP'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1272213504 bytes

Fixed Size 1344680 bytes
Variable Size 838863704 bytes
Database Buffers 419430400 bytes
Redo Buffers 12574720 bytes
allocated channel: dup1
channel dup1: SID=29 device type=DISK
allocated channel: dup2
channel dup2: SID=30 device type=DISK

Starting restore at 07-APR-14

channel dup1: starting datafile backup set restore
channel dup1: restoring control file
channel dup1: reading from backup piece +FRA/srprim/backupset/2014_04_05/ncnnf0_tag20140405t104856_0.333.844080539
channel dup1: piece handle=+FRA/srprim/backupset/2014_04_05/ncnnf0_tag20140405t104856_0.333.844080539 tag=TAG20140405T104856
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:08
output file name=+DATA/tmp/controlfile/current.272.844264497
output file name=+FRA/tmp/controlfile/current.324.844264497
Finished restore at 07-APR-14

database mounted

contents of Memory Script:
{
set until scn 2139313;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07-APR-14

channel dup1: starting datafile backup set restore
channel dup1: specifying datafile(s) to restore from backup set
channel dup1: restoring datafile 00002 to +DATA
channel dup1: restoring datafile 00003 to +DATA
channel dup1: restoring datafile 00004 to +DATA
channel dup1: restoring datafile 00006 to +DATA
channel dup1: reading from backup piece /u02/bkp/SRPRIM_inc0_02p4v99g_1_1.bak
channel dup2: starting datafile backup set restore
channel dup2: specifying datafile(s) to restore from backup set
channel dup2: restoring datafile 00001 to +DATA
channel dup2: restoring datafile 00005 to +DATA
channel dup2: restoring datafile 00007 to +DATA
channel dup2: restoring datafile 00008 to +DATA
channel dup2: reading from backup piece /u02/bkp/SRPRIM_inc0_03p4v99g_1_1.bak
channel dup1: piece handle=/u02/bkp/SRPRIM_inc0_02p4v99g_1_1.bak tag=TAG20140405T104711
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:02:25
channel dup2: piece handle=/u02/bkp/SRPRIM_inc0_03p4v99g_1_1.bak tag=TAG20140405T104711
channel dup2: restored backup piece 1
channel dup2: restore complete, elapsed time: 00:02:45
Finished restore at 07-APR-14

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=844264708 file name=+DATA/tmp/datafile/system.279.844264543
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=844264708 file name=+DATA/tmp/datafile/sysaux.280.844264543
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=844264708 file name=+DATA/tmp/datafile/undotbs1.278.844264543
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=844264708 file name=+DATA/tmp/datafile/users.275.844264543
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=844264708 file name=+DATA/tmp/datafile/example.276.844264543
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=844264708 file name=+DATA/tmp/datafile/undotbs2.277.844264543
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=844264708 file name=+DATA/tmp/datafile/users.274.844264543
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=844264708 file name=+DATA/tmp/datafile/mydata.273.844264543

contents of Memory Script:
{
set until time "to_date('APR 05 2014 11:15:00', 'MON DD YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 07-APR-14

starting media recovery

archived log for thread 1 with sequence 103 is already on disk as file
+FRA/srprim/archivelog/2014_04_05/thread_1_seq_103.334.844082183
archived log for thread 2 with sequence 50 is already on disk as file
+FRA/srprim/archivelog/2014_04_05/thread_2_seq_50.331.844082191
channel dup1: starting archived log restore to default destination
channel dup1: restoring archived log
archived log thread=1 sequence=102
channel dup1: restoring archived log
archived log thread=2 sequence=49
channel dup1: reading from backup piece /u02/bkp/SRPRIM_arch_07p4v9ce_1_1.bak
channel dup1: piece handle=/u02/bkp/SRPRIM_arch_07p4v9ce_1_1.bak tag=TAG20140405T104844
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:15
archived log file name=+FRA/tmp/archivelog/2014_04_07/thread_1_seq_102.322.844264715 thread=1 sequence=102
archived log file name=+FRA/tmp/archivelog/2014_04_07/thread_2_seq_49.321.844264715 thread=2 sequence=49
channel clone_default: deleting archived log(s)
archived log file name=+FRA/tmp/archivelog/2014_04_07/thread_2_seq_49.321.844264715 RECID=22 STAMP=844264720
archived log file name=+FRA/srprim/archivelog/2014_04_05/thread_2_seq_50.331.844082191 thread=2 sequence=50
channel clone_default: deleting archived log(s)
archived log file name=+FRA/tmp/archivelog/2014_04_07/thread_1_seq_102.322.844264715 RECID=21 STAMP=844264716
archived log file name=+FRA/srprim/archivelog/2014_04_05/thread_1_seq_103.334.844082183 thread=1 sequence=103
media recovery complete, elapsed time: 00:00:03
Finished recover at 07-APR-14
Oracle instance started

Total System Global Area 1272213504 bytes

Fixed Size 1344680 bytes
Variable Size 838863704 bytes
Database Buffers 419430400 bytes
Redo Buffers 12574720 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''TMP'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''TMP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1272213504 bytes

Fixed Size 1344680 bytes
Variable Size 838863704 bytes
Database Buffers 419430400 bytes
Redo Buffers 12574720 bytes
allocated channel: dup1
channel dup1: SID=29 device type=DISK
allocated channel: dup2
channel dup2: SID=30 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TMP" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M
DATAFILE
'+DATA/tmp/datafile/system.279.844264543'
CHARACTER SET WE8MSWIN1252

sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE 'i2'
GROUP 3 SIZE 50 M ,
GROUP 4 SIZE 50 M

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/tmp/datafile/sysaux.280.844264543",
"+DATA/tmp/datafile/undotbs1.278.844264543",
"+DATA/tmp/datafile/users.275.844264543",
"+DATA/tmp/datafile/example.276.844264543",
"+DATA/tmp/datafile/undotbs2.277.844264543",
"+DATA/tmp/datafile/users.274.844264543",
"+DATA/tmp/datafile/mydata.273.844264543";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/sysaux.280.844264543 RECID=1 STAMP=844264790
cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/undotbs1.278.844264543 RECID=2 STAMP=844264790
cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/users.275.844264543 RECID=3 STAMP=844264791
cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/example.276.844264543 RECID=4 STAMP=844264791
cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/undotbs2.277.844264543 RECID=5 STAMP=844264791
cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/users.274.844264543 RECID=6 STAMP=844264791
cataloged datafile copy
datafile copy file name=+DATA/tmp/datafile/mydata.273.844264543 RECID=7 STAMP=844264791

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=844264790 file name=+DATA/tmp/datafile/sysaux.280.844264543
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=844264790 file name=+DATA/tmp/datafile/undotbs1.278.844264543
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=844264791 file name=+DATA/tmp/datafile/users.275.844264543
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=844264791 file name=+DATA/tmp/datafile/example.276.844264543
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=844264791 file name=+DATA/tmp/datafile/undotbs2.277.844264543
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=844264791 file name=+DATA/tmp/datafile/users.274.844264543
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=844264791 file name=+DATA/tmp/datafile/mydata.273.844264543

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 07-APR-14
released channel: dup1
released channel: dup2

RMAN>

Now that the duplicate until time is completed and the TMP database is opened, let’s check if the user SHIVU exists and also the table TEST with the contents.

[oracle@10gnode1 ~]$ sqlplus sys/oracle@tmp as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 7 14:02:07 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select username,account_status from dba_users where username='SHIVU';

USERNAME ACCOUNT_STATUS
-------- ---------------
SHIVU    OPEN

SQL> conn shivu/oracle@tmp
Connected.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
----- ------- ----------
TEST  TABLE
TEST1 TABLE

SQL> select * from test;

CODE
----------
100
101
102
103
104
105
106
107
108
109
110

CODE
----------
111
112
113
114

15 rows selected.

SQL> create directory DP_DIR as '/u01/app/oracle/diag/rdbms/tmp/tmp/dpdump';

Directory created.

SQL> grant read,write on directory DP_DIR to shivu;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

We could see that the table TEST exists with all the rows. Now that this table needs to be exported and imported to the original database SRPRIM. I’m using the DataPump option to proceed with the export and import.

I’m creating a database directory called DP_DIR with the path “/u01/app/oracle/diag/rdbms/tmp/tmp/dpdump” which is used to place the dumpfile and logfile.


[oracle@10gnode1 ~]$ export ORACLE_SID=tmp
[oracle@10gnode1 ~]$ expdp shivu/oracle directory=DP_DIR dumpfile=test_table.dmp logfile=test_table.log tables=test

Export: Release 11.2.0.3.0 - Production on Mon Apr 7 14:14:45 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rightsreserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options
Starting "SHIVU"."SYS_EXPORT_TABLE_01": shivu/******** directory=DP_DIR dumpfile=test_table.dmp logfile=test_table.log
tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SHIVU"."TEST" 5.117 KB 15 rows
Master table "SHIVU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SHIVU.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/diag/rdbms/tmp/tmp/dpdump/test_table.dmp
Job "SHIVU"."SYS_EXPORT_TABLE_01" successfully completed at 14:15:14

Export of Table TEST is taken from the TMP database and the dumpfile as well as logfile are located under the location “/u01/app/oracle/diag/rdbms/tmp/tmp/dpdump/”.

Similarly, I’m creating a database directory called DP_DIR with location “/u01/app/oracle/diag/rdbms/srprim/srprim1/dpdump” on node 10gnode1 for SRPRIM database.


[oracle@10gnode1 ~]$ sqlplus sys/oracle@srprim1 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 7 14:16:58 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select status,instance_name from gv$instance;

STATUS INSTANCE_NAME
------ ---------------------
OPEN   srprim1
OPEN   srprim2

SQL> create directory DP_DIR as '/u01/app/oracle/diag/rdbms/srprim/srprim1/dpdump';

Directory created.

SQL> grant read,write on directory DP_DIR to shivu;

Grant succeeded.

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
----- -------------- ---------------------------------------------
SYS   DATA_PUMP_DIR  /u01/app/oracle/product/11.2.0/db3/rdbms/log/
SYS   SUBDIR         /u01/app/oracle/product/11.2.0/db_1/demo/sche                     ma/order_entry//2002/Sep
SYS   SS_OE_XMLDIR   /u01/app/oracle/product/11.2.0/db_1/demo/sche                     ma/order_entry/
SYS   LOG_FILE_DIR   /u01/app/oracle/product/11.2.0/db_1/demo/sche                     ma/log/
SYS   DATA_FILE_DIR  /u01/app/oracle/product/11.2.0/db_1/demo/sche                     ma/sales_history/
SYS   XMLDIR         /ade/b/1191423112/oracle/rdbms/xml
SYS   MEDIA_DIR      /u01/app/oracle/product/11.2.0/db_1/demo/sche                     ma/product_media/
SYS   DP_DIR         /u01/app/oracle/diag/rdbms/srprim/srprim1/dpd                     ump
SYS   ORACLE_OCM_    /u01/app/oracle/product/11.2.0/db3/ccr/state
      CONFIG_DIR 

9 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Move the dumpfile “test_table.dmp” from location “/u01/app/oracle/diag/rdbms/tmp/tmp/dpdump/” to “/u01/app/oracle/diag/rdbms/srprim/srprim1/”.


[oracle@10gnode1 dpdump]$ mv /u01/app/oracle/diag/rdbms/tmp/tmp/dpdump/test_table.dmp
/u01/app/oracle/diag/rdbms/srprim/srprim1/dpdump/
[oracle@10gnode1 dpdump]$ cd /u01/app/oracle/diag/rdbms/srprim/srprim1/dpdump/
[oracle@10gnode1 dpdump]$ ls -lrt
total 92
-rw-r----- 1 oracle oinstall 90112 Apr 7 14:15 test_table.dmp

Now, let’s proceed with the import of TEST table from the dumpfile to the SRPRIM database.

[oracle@10gnode1 dpdump]$ export ORACLE_SID=srprim1
[oracle@10gnode1 dpdump]$ impdp shivu/oracle directory=DP_DIR dumpfile=test_table.dmp logfile=test_table_imp.log tables=TEST

Import: Release 11.2.0.3.0 - Production on Mon Apr 7 15:18:02 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rightsreserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SHIVU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SHIVU"."SYS_IMPORT_TABLE_01": shivu/******** directory=DP_DIR dumpfile=test_table.dmp logfile=test_table_imp.log
tables=TEST
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SHIVU"."TEST" 5.117 KB 15 rows
Job "SHIVU"."SYS_IMPORT_TABLE_01" successfully completed at 15:18:51

Let’s check if the table is available with all the rows in the SRPRIM database.


[oracle@10gnode1 dpdump]$ sqlplus shivu/oracle@srprim

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 7 15:22:45 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show user
USER is "SHIVU"

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
----- ------- -----------
TEST  TABLE
TEST1 TABLE

SQL> select * from test;

CODE
----------
100
101
102
103
104
105
106
107
108
109
110

CODE
----------
111
112
113
114

15 rows selected.

SQL> select owner,object_name,to_char(CREATED,'DD-MON-YYYY HH24:MI:SS') from dba_objects where object_name='TEST' and
owner='SHIVU';

OWNER OBJECT_NAME TO_CHAR(CREATED,'DD-
----- ----------- -------------------------
SHIVU TEST        07-APR-2014 15:18:47

SQL> !date
Mon Apr 7 15:39:25 IST 2014

We could see that all the rows of the TEST table have been imported. Also, we could just crosscheck from the view “dba_objects” as to when the table was created (that is after the import).

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

 

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: