CODEWITHSUNDEEP
oracle-databasebackuporacle-apexrecoverytablespace
mindwalkr
mindwalkr

Reputation: 98

Recovering an Oracle tablespace (APEX) from file system backups of database tablespace files

I'm trying to recover the applications of an Oracle APEX workspace deleted accidentally. The database is 12c and APEX 18.1

What would be the best way to go about it, if the only backup available is an OS level backup of the oradata folder (with all Tablespace files) ?. My APEX schema lives on its own tablespace. Can I simply copy over last night's copy of the APEX table space file over the current one to restore ?

There are no RMAN backups, and the database is installed with all default options, no archive log and no flashback. I also don't have any dump produced with expdp.

I've already tried to use the dbms_flashback package to go back a few hours but to no avail, as I get an error about rollback segment too small. The earliest I can make it work, is already at a state after the desired recovery point.

Upvotes: 0

Views: 910

Answers (1)

Chris Maggiulli
Chris Maggiulli

Reputation: 3814

Clarification

I am assuming you only lost the APEX tablespace but your database is currently functioning. If this is the case, and assuming your APEX tablespace does not span multiple datafiles, you can attempt to swap out the datafile. Please force a backup in rman before trying any of this.

There are a few different options here. All you really need are the following

  1. Datafile
  2. Control file
  3. Archive / redologs (if you want to move forward or backward in time)

I'm going to outline two options because I don't have all the pertinent information. The first option attempts to actually restore the datafiles through rman, the second one simply swaps it out. The first is obviously preferential but may not be achievable.

RMAN Restore

First set the following parameter in your init.ora file

_allow_resetlogs_corruption=TRUE

Move your entire oradata backup directory to /tmp/oradata. Locate then location of your dbf and ctl files in that directory.

Then run rman target / from bash terminal. In rman run the following. 

RESTORE CONTROLFILE FROM '/tmp/oradata/your_ctrl_file_dir'
ALTER TABLESPACE apex OFFLINE IMMEDIATE';
SET NEWNAME FOR DATAFILE '/tmp/oradata/apex01.dbf' TO 
RESTORE TABLESPACE apex;
SWITCH DATAFILE ALL; 
RECOVER TABLESPACE apex;

Swap out Datafile

First find the location of your datafiles. You can find them by running the following in sqlplus / as sysdba or whatever client you use

spool '/tmp/spool.out'
select value from v$parameter where name = 'db_create_file_dest';
select tablespace name from dba_data_files;

View the spool.out file and

  1. Verify the location of your datafiles
  2. See if the datafile still is associated with that tablespace.

If the tablespace is still there run

select file_name, status from dba_data_files WHERE tablespace name = < name >

You want your your datafile to be available. Then you want to set the tablespace to read only and take it offline

alter tablespace < name > read only;
alter tablespace < name > offline;

Now copy your dbf file the directory returned from querying db_create_file_dest value. Don't overwrite the old one, then run.

alter tablespace < name > rename datafile '/u03/waterver/oradata/yourold.dbf' to '/u03/waterver/oradata/yournew.dbf'

This updates your controlfile to point to the new datafile.

You can then bring your tablespace back online and back in read write mode. You may also want to verify the status of the tablespace status, the name of the datafile associated with that tablespace, etc.

Upvotes: 1

Related Questions