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; /
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>
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