Saving database after disk failure

Sunday, October 19, 2008

Last week we had an interesting situation with our development database. Database server experienced disk failure and probably some Windows 2000 Server system files were damaged cause we couldn't boot up OS. Disk was in bad shape so we decided to move database to another machine.

Database was in no archive log mode, there wasn't any backup and all database files were situated on the same partitioned disk. This wasn't critical database, but still we tried to recover it.

First thing to do was to copy from disk all database files that weren't damaged, and we managed to copy all database files to another storage.



I'll try to demonstrate what were our next steps.


Our Oracle 9.2.0.6 database was on Win Server 2000 + SP2, and in my example I'll use Windows XP + SP2 operating system with Oracle 9i to restore and recover affected database.


1. First step for me was to install Oracle 9.2.0.1 database software (Software Only) on Windows XP + SP2 operating system.

2. I copied all database files to "C:\ORACLE\ORADATA" directory.

3. My controlfiles were saved so I've used Unix program "strings" to extract (text) locations of datafiles from binary controlfile.

[oracle@dibidus tmp]$ strings -a CONTROL01.CTL

I've extracted locations of datafiles:
C:\ORACLE\ORADATA\TESTPRD\SYSTEM01.DBF
C:\ORACLE\ORADATA\TESTPRD\UNDOTBS01.DBF
C:\ORACLE\ORADATA\TESTPRD\CWMLITE01.DBF
C:\ORACLE\ORADATA\TESTPRD\DRSYS01.DBF
F:\ORACLE\ORADATA\EXAMPLE01.DBF
F:\ORACLE\ORADATA\INDX01.DBF
C:\ORACLE\ORADATA\TESTPRD\ODM01.DBF
E:\ORACLE\ORADATA\TOOLS01.DBF
E:\ORACLE\ORADATA\USERS01.DBF
E:\ORACLE\ORADATA\XDB01.DBF

Here I saw that database files were situated all around disk partitions.

4. As I had controlfiles saved I could "startup mount" database using them, so I edited init.ora parameter file to put exact locations of controlfiles.
Changed lines:
*.control_files='C:\oracle\oradata\testprd\CONTROL01.CTL','C:\oracle\oradata\testprd\CONTROL02.CTL','C:\oracle\oradata\testprd\CONTROL03.CTL'

to
*.control_files='C:\oracle\oradata\CONTROL01.CTL','C:\oracle\oradata\CONTROL02.CTL','C:\oracle\oradata\CONTROL03.CTL'


5. Then startup mount!

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sub Lis 18 13:36:27 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL>
SQL>
SQL> startup mount pfile='c:\oracle\oradata\inittestprd.ora'
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.


6. Then I've extracted locations of datafiles (again, just in case) from controlfile.
SQL> select name from v$datafile;

NAME
----------------------------------------
C:\ORACLE\ORADATA\TESTPRD\SYSTEM01.DBF
C:\ORACLE\ORADATA\TESTPRD\UNDOTBS01.DBF
C:\ORACLE\ORADATA\TESTPRD\CWMLITE01.DBF
C:\ORACLE\ORADATA\TESTPRD\DRSYS01.DBF
F:\ORACLE\ORADATA\EXAMPLE01.DBF
F:\ORACLE\ORADATA\INDX01.DBF
C:\ORACLE\ORADATA\TESTPRD\ODM01.DBF
E:\ORACLE\ORADATA\TOOLS01.DBF
E:\ORACLE\ORADATA\USERS01.DBF
E:\ORACLE\ORADATA\XDB01.DBF

10 rows selected.


7. As I copied all database files to same directory on new machine, I had to rename locations written in current controlfile.

SQL> set lines 200
SQL> spool c:\rename.sql
SQL> select 'alter database rename file '''||name||''' to '''||name||''';' from v$datafile;

'ALTERDATABASERENAME'''||NAME||'''TO'''||NAME||''';'
-------------------------------------------------------------------------------------------------------------
--------------------------------------------------
alter database rename file 'C:\ORACLE\ORADATA\TESTPRD\SYSTEM01.DBF' to 'C:\ORACLE\ORADATA\TESTPRD\SYSTEM01.DBF';
alter database rename file  'C:\ORACLE\ORADATA\TESTPRD\UNDOTBS01.DBF' to 'C:\ORACLE\ORADATA\TESTPRD\UNDOTBS01.DBF';
alter database rename file  'C:\ORACLE\ORADATA\TESTPRD\CWMLITE01.DBF' to 'C:\ORACLE\ORADATA\TESTPRD\CWMLITE01.DBF';
alter database rename file  'C:\ORACLE\ORADATA\TESTPRD\DRSYS01.DBF' to 'C:\ORACLE\ORADATA\TESTPRD\DRSYS01.DBF';
alter database rename file  'F:\ORACLE\ORADATA\EXAMPLE01.DBF' to 'F:\ORACLE\ORADATA\EXAMPLE01.DBF';
alter database rename file  'F:\ORACLE\ORADATA\INDX01.DBF' to 'F:\ORACLE\ORADATA\INDX01.DBF';
alter database rename file  'C:\ORACLE\ORADATA\TESTPRD\ODM01.DBF' to 'C:\ORACLE\ORADATA\TESTPRD\ODM01.DBF';
alter database rename file  'E:\ORACLE\ORADATA\TOOLS01.DBF' to 'E:\ORACLE\ORADATA\TOOLS01.DBF';
alter database rename file  'E:\ORACLE\ORADATA\USERS01.DBF' to 'E:\ORACLE\ORADATA\USERS01.DBF';
alter database rename file  'E:\ORACLE\ORADATA\XDB01.DBF' to 'E:\ORACLE\ORADATA\XDB01.DBF';

10 rows selected.

10 rows selected.

SQL> spool off


Edited created rename.sql file and executed:
alter database rename file 'C:\ORACLE\ORADATA\TESTPRD\SYSTEM01.DBF' to 'C:\ORACLE\ORADATA\SYSTEM01.DBF';                                                                                            
alter database rename file 'C:\ORACLE\ORADATA\TESTPRD\UNDOTBS01.DBF' to 'C:\ORACLE\ORADATA\UNDOTBS01.DBF';                                                                                          
alter database rename file 'C:\ORACLE\ORADATA\TESTPRD\CWMLITE01.DBF' to 'C:\ORACLE\ORADATA\CWMLITE01.DBF';                                                                                          
alter database rename file 'C:\ORACLE\ORADATA\TESTPRD\DRSYS01.DBF' to 'C:\ORACLE\ORADATA\DRSYS01.DBF';                                                                                              
alter database rename file 'F:\ORACLE\ORADATA\EXAMPLE01.DBF' to 'C:\ORACLE\ORADATA\EXAMPLE01.DBF';                                                                                                          
alter database rename file 'F:\ORACLE\ORADATA\INDX01.DBF' to 'C:\ORACLE\ORADATA\INDX01.DBF';                                                                                                                
alter database rename file 'C:\ORACLE\ORADATA\TESTPRD\ODM01.DBF' to 'C:\ORACLE\ORADATA\ODM01.DBF';                                                                                                  
alter database rename file 'E:\ORACLE\ORADATA\TOOLS01.DBF' to 'C:\ORACLE\ORADATA\TOOLS01.DBF';                                                                                                              
alter database rename file 'E:\ORACLE\ORADATA\USERS01.DBF' to 'C:\ORACLE\ORADATA\USERS01.DBF';                                                                                                              
alter database rename file 'E:\ORACLE\ORADATA\XDB01.DBF' to 'C:\ORACLE\ORADATA\XDB01.DBF'; 


SQL> @c:\rename.sql


8. A I had redo logs also saved I changed locations for redo members too.
SQL> select member from v$logfile;

MEMBER
------------------------------------
------------------------------------
C:\ORACLE\ORADATA\TESTPRD\REDO03.LOG
C:\ORACLE\ORADATA\TESTPRD\REDO02.LOG
C:\ORACLE\ORADATA\TESTPRD\REDO01.LOG


alter database rename file ‘C:\ORACLE\ORADATA\TESTPRD\REDO01.LOG’ to ‘C:\ORACLE\ORADATA\REDO01.LOG’;
alter database rename file ‘C:\ORACLE\ORADATA\TESTPRD\REDO02.LOG’ to ‘C:\ORACLE\ORADATA\REDO02.LOG’;
alter database rename file ‘C:\ORACLE\ORADATA\TESTPRD\REDO03.LOG’ to ‘C:\ORACLE\ORADATA\REDO03.LOG';


SQL> select member from v$logfile;

MEMBER
-------------------------------------
-------------------------------------
C:\ORACLE\ORADATA\REDO03.LOG
C:\ORACLE\ORADATA\REDO02.LOG
C:\ORACLE\ORADATA\REDO01.LOG


9. I didn't have to apply patch from 9.2.0.1 to 9.2.0.6 cause I had all databse files that were allready patched. I also had all my redo logs so there wasn't need for recovery.

After changing all that locations all I had to do was to open database:
SQL> alter database open;

Database altered.


10. Database was up and running. I've created spfile and just run little check test on data.

SQL> create spfile from pfile = 'c:\oracle\oradata\inittestprd.ora';

File created.

SQL> select count(1) from prd.customer;

COUNT(1)
----------
29512

SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------
-------------------------------------------
C:\ORACLE\ORADATA\SYSTEM01.DBF
C:\ORACLE\ORADATA\UNDOTBS01.DBF
C:\ORACLE\ORADATA\CWMLITE01.DBF
C:\ORACLE\ORADATA\DRSYS01.DBF
C:\ORACLE\ORADATA\EXAMPLE01.DBF
C:\ORACLE\ORADATA\INDX01.DBF
C:\ORACLE\ORADATA\ODM01.DBF
C:\ORACLE\ORADATA\TOOLS01.DBF
C:\ORACLE\ORADATA\USERS01.DBF
C:\ORACLE\ORADATA\XDB01.DBF

10 rows selected.


Share/Bookmark

0 comments:

Post a Comment