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.
In this scenario, I was aware of the time the table was dropped. It was dropped in at 11:17 AM 5th April 2014.
Let me check when was the last full backup happened successfully.
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.
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.
Here is the PFILE that I’m using to create the dummy database.
Add the below TNS entry for the tmp database in the TNSNAMES.ora file of the 10gnode1.
Add a static entry in the listener.ora file of the 10gnode1 server as shown below and reload the listener.
Create a password file for the temporary database.
Startup the TMP database in nomount mode using the password that was created in the previous post.
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.
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.
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.
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.
Move the dumpfile “test_table.dmp” from location “/u01/app/oracle/diag/rdbms/tmp/tmp/dpdump/” to “/u01/app/oracle/diag/rdbms/srprim/srprim1/”.
Now, let’s proceed with the import of TEST table from the dumpfile to the SRPRIM database.
Let’s check if the table is available with all the rows in the SRPRIM database.
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).