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.
0 Comments:
Post a Comment