Partial Database Recovery

I had a request to restore a user schema from a ~2TB data warehouse with 1,137 data files.

GIVEN:  RMAN is not being used for backup, backup is being performed using split mirror technology, and the restore server does not have 2TB space available.

The OS was restored with only the required data files for the database.

I started by taking the unnecessary data files offline using the script below:

set serverout on
declare
 l_sql varchar2(4000);
begin
 for x in (
 select name from v$datafile where not regexp_like(name,'sys|undo|user_data','i')
 ) loop 
 l_sql :='alter database datafile '''||x.name||''' offline';
 execute immediate l_sql;
 dbms_output.put_line(l_sql);
 end loop;
exception
when others then
 dbms_output.put_line ('******* error: ' || l_sql);
 raise;
end;
/

After executing:

alter database end backup;

I proceeded to open the database.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/dw01/system01.dbf'

That did not work. (Because I forgot to recover database). After recover database as shown below, database was able to open.

Just to be safe, I moved the online redo logs to a backup directory, hearing never to backup online redo logs.

oracle@dw01:/oracle/oradata/dw01
> mkdir backup
 
oracle@dw01:/oracle/oradata/dw01
> mv redo0* ./backup/

Next attempt was to recover the database:

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/dw01/redo03b.dbf'
ORA-17503: ksfdopn:4 Failed to open file /oracle/oradata/dw01/redo03b.dbf
ORA-17500: ODM err:File does not exist
ORA-00312: online log 3 thread 1: '/oracle/oradata/dw01/redo03a.dbf'
ORA-17503: ksfdopn:4 Failed to open file /oracle/oradata/dw01/redo03a.dbf
ORA-17500: ODM err:File does not exist

Looks like the online redo logs were needed after all.

Move the redo back to the original location and attempt to recover the database again:

oracle@dw01:/oracle/oradata/dw01
> mv backup/* .
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>

Success!

Query showing status of data files:

SQL> select status, count(*) from v$datafile_header group by status;
 
STATUS    COUNT(*)
------- ----------
ONLINE          21
OFFLINE       1116
 
SQL> select status, count(*) from v$datafile group by status;
 
STATUS    COUNT(*)
------- ----------
ONLINE          20
RECOVER       1116
SYSTEM           1