Question

Suggestions on how to restore a set of tables from a backup to a Production DB

  • 15 March 2021
  • 3 replies
  • 1136 views
J
Rank
Userlevel 5
Badge +9

Using all available options to find support.  Running IFS Apps10 UPD8

We recently moved our document management repository from Database to Shared Directory.  

An unexpected problem came out of that.  A scheduled migration job we were using to clean up- bad doc man entries prior to the move (because they caused the migration to fail) was run in PROD and while it worked with the database repository, under Shared Directory - it deleted all of our document management entries.

This happened over the weekend.  When it was discovered this morning, we shut all users out and we are trying to recover.  I have an open case with IFS but I’m going on 3 hours with no response.  We did confirm the documents are still in the shared directory, so it looks like on the db records were cleared.

I do have IT restoring a weekend back that happened just prior to this happening.  From what I can tell, the views (really their underlying tables) I need to recover are:

  • DOC_ISSUE_REFERENCE
  • DOC_REFERENCE_OBJECT
  • DOCUMENT_ISSUE_ACCESS
  • DOC_TITLE
  • APPROVAL_ROUTING
  • DOC_STRUCTURE
  • DOC_TRANSMITTAL_PROJ_INFO
  • EDM_FILE
  • DOC_ISSUE_ORIGINAL
  • DOCUMENT_ISSUE_HISTORY

I have the additional challenge that about 100 documents were uploaded before the problem was found.

 

I’m hoping I can use the backup to restore the data from these tables,, without requiring a full database restore which would lose the work done this morning.

Any and all suggestions on the best way to do this would be welcome.

This topic has been closed for comments

3 replies

S
Rank
Userlevel 7
Badge +21

Hi @jhooperyan ,

 

You will not be able to restore single tables using RMAN backups directly in to the primary database  (this is of course assuming you using RMAN to backup the database in the first place as its most commonly done). Your best bet in this scenario would be to use the latest full backup from before the incident to create a new separate database instance. You can do this using the RMAN duplication with no target connection. (target in this case being your primary database). if you have the all the required backups and archive logs, you can even do a point in time duplication to get close the the time of the incident as much as possible. 

 

Once you have the DB clone setup, then you have the option of reverse engineering the cleanup job  migration job to identify only the ones that are cleared out incorrectly and send them back in to your primary database through a database link pointing to the clone.  Doing this manually will allow you much more control over what goes in to production and identifying potential reference issues.

 

Cheers

alanbclark
Rank
Userlevel 5
Badge +7

My opinion is that you can restore one or more tables from an Oracle RMAN backup. Before Oracle 12c, this operation was not possible.

You can recover them directly into the database or recover them to an Oracle data pump file that you can then use to import the data. Note: When you restore a table using RMAN it will also restore the associated objects, like indexes.

For example,

The following command recovers the table CUSTOMER_INFO_TAB to the state that it was in 4 days before the current date. IFSAPP is the name of the schema that contains the table. The recovered table is renamed to CUSTOMER_INFO_RECVR. By recovering the data to a separate table you can review the data and move data from the recover table to the permanent table. Alternatively, you can choose not to remap the table and recover data straight into the original table name.

Make sure the mount point /tmp/backups exists. What happens ? Oracle will create a pseudo database under /tmp/backups with SYSTEM SYSAUX TEMP UNDO and data tablespaces, then it restores the IFSAPP.CUSTOMER_INFO_TAB table at the specified date and renames it with the specified new name. Finally Oracle deletes the pseudo database.

Now to the RMAN command...

RECOVER TABLE IFSAPP.CUSTOMER_INFO_TAB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups'
REMAP TABLE 'IFSAPP'.'CUSTOMER_INFO_TAB':'CUSTOMER_INFO_RECVR';

 

Take a look at, https://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686 for a more length explanation. Bets of luck with your recovery operation.

Here’s an independent link with sample command and expected output, https://blog.dbi-services.com/rman-pitr-recover-table-oracle-12c/ 

-Alan
S
Rank
Userlevel 7
Badge +21

Good one @alanbclark.

completely forgot about the new PITR options that was introduced in 12c.

Cheers.

Terms & ConditionsCookie settings