Thinking Out Loud

December 1, 2010

Partial Database Recovery

Filed under: 11g,Recovery — mdinh @ 6:10 am

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
Advertisements

2 Comments »

  1. hallo,
    I did not understand why the database did not open at the first time , !

    Comment by walid kaakati — November 16, 2012 @ 4:01 pm | Reply

    • It looks like the database did not open the first time since I did not execute recover database.

      Comment by mdinh — November 30, 2012 @ 5:23 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: