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 to 2018. 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.
Shivananda Rao 
Leave a Reply