Re: Oracle 8i crashed: system ts needs more recovery - how do I recover from this?

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 31 Oct 2013 19:03:44 +1000
Message-ID: <52721CF0.5040403_at_tpg.com.au>



It occurred to me that the date of the change number was around the date of the ancient backup file, so I decided to recreate the controlfile again without that file. The change number did indeed change, but now I am out of options - all remaining files are the files that were part of the database at the time of the crash on 22/10/13 ...

The date of the change number that recover wants now, is the date that the original control file said that the tablespace was checkpointed (9 Aug 13, 01:48), which strikes me as odd, since I was called to this site a month ago to recover a datafile. Surely it was checkpointed then... The database was also restarted every morning. Did NT4 have shadowcopy? I thought not...

Latest attempt:



SQL> _at_recreate_controlfile.dba
ORACLE instance started.

Total System Global Area 70723612 bytes

Fixed Size                    75804 bytes
Variable Size              29609984 bytes
Database Buffers           40960000 bytes
Redo Buffers                  77824 bytes

Control file created.

ORA-00279: change 84406636 generated at 08/09/2013 01:48:39 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORA81\RDBMS\ARC07062.001
ORA-00280: change 84406636 for thread 1 is in sequence #7062


ORA-00308: cannot open archived log 'REM'
ORA-27041: unable to open file

OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORADATA\SUN\SYS1SUN.DBF'

SQL> recover database until cancel;

ORA-00279: change 84406636 generated at 08/09/2013 01:48:39 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORA81\RDBMS\ARC07062.001
ORA-00280: change 84406636 for thread 1 is in sequence #7062


Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\ORADATA\SUN\SYS1SUN.DBF'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'E:\ORADATA\SUN\SYS1SUN.DBF'



TS and database information from old controlfile (note that the control file time is from just before the crash on 22/10/13):

SQL> select * from v$database;

       DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_ LOG_MODE CHECKPOINT_CHANGE#

---------- --------- ---------------- ----------------- ---------------- ----------------------- ---------------- ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFILE_CREA CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETL VERSION_TIME OPEN_MODE
--------------- ------- ---------------- --------------------- ------------------- ---------------- ----------- ---------------- ----------
1514712354 SUN       11-03-2003 14:36                 1 24-08-2000 11:18                       0          NOARCHIVELOG 85971384
        85950214 CURRENT 11-03-2003 14:36 35582            85971384 22-10-2013 02:12 NOT ALLOWED 11-03-2003 14:36 MOUNTED

SQL> select ts#, file#, name, checkpoint_time from v$datafile order by ts#, file#;

        TS# FILE# NAME CHECKPOINT_TIME

---------- ---------- --------------------------------------------- ----------------
          0          1 E:\ORADATA\SUN\SYS1SUN.DBF 09-08-2013 01:48
          1          2 D:\ORACLE\ORA81\DATABASE\SUN\RBS1SUN.ORA 09-08-2013 01:48
          2          3 E:\ORADATA\SUN\TMP1SUN.DBF 09-08-2013 01:48
          3          4 E:\ORADATA\SUN\TOOLSUN.DBF 09-08-2013 01:48
          4          5 E:\ORADATA\SUN\USR1SUN.DBF 09-08-2013 01:48
          4          7 E:\ORADATA\SUN\USER2.DBF 09-08-2013 01:48
          4          8 D:\ORACLE\ORA81\DATABASE\SUN\USR1SUN02.ORA 09-08-2013 01:48
          6          6 E:\ORADATA\SUN\SUN2.DBF 09-08-2013 01:48



Cheers,
Tony

On 31/10/13 18:23, De DBA wrote:
> G'day,
>
> I've got this old 8i database, that was quietly chugging away on an old NT4 server, until the server finally and irretrievably crashed a few days ago. The sysadmin has succeeded in copying the entire database (and oracle installation) off the old disk. Now we have a "new" Windows 2000 installation with Oracle installed.
>
> Version info:
> - Windows 2000 TS, SP4
> - Oracle 8.1.7 SE
>
> We have a backup of 24 July, which is not an attractive option for this billing system...
>
> The rescued files have a number of problems:
> - the rollback tablespace file header is corrupt
> - one redo logfile header is corrupt
> - one datafile is missing
> - the datafiles are newer than the controlfile
>
> I've copied the missing datafile from the ancient backup, hoping (with some reason) that it contained no data. The logfile was not current, so I could drop and recreate it easily. The rollback tablespace should also not give too much grief, if we accept that some transactions may be lost (we do that, it's way better than loosing 3 months of financial data).
>
> The controlfile is also not too difficult - I've simply created a new one. The newest problem is this:
>
> <..snip..>
> _________________
> Yep, it did, and that's where I got stuck. Can I even recover from this? No backups, no archived redologs, the online redo logs were too new, and are now stale anyway (due to controlfile recreate). On the positive side, it did not complain about the old datafile that I used to replace the missing one (yet... perhaps it doesn't look that far? )
>
> All thoughts are welcome
>
> Thanks,
> Tony
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 31 2013 - 10:03:44 CET

Original text of this message