recovery 'opportunity'

From: EdStevens <quetico_man_at_yahoo.com>
Date: 4 May 2006 13:37:01 -0700
Message-ID: <1146775021.888573.278750@g10g2000cwb.googlegroups.com>

Oh, by the way, Ed ... while you're trying to figure out how to replicate from 8i to 10g with encryption and a standby database and os granted authorization and privileges, and assist a developer who insists on trying to get an 8i Pro*C compiled app to run against 10g without recompiling, ... and help the non-Oracle dba's to relocate some Oracle db's to new hardware ....

Can you fix Grid Control? Your predecessor set it up, but its repository got corrupted and he wasn't able to recover it.

Sigh...

Platform: Oracle 10.2.0.1.0 on Solaris 5.10

DB will mount but not open. Starting rman nocatalog and LIST BACKUP indicates no rman backups.

Check the archive log dest, and find logs:

Jan 6 00:04 orareppd_1_34_578845402.log thru
Apr 3 14:23 orareppd_1_1256_5788454002.log

As well as a file Jan 4 19:00 1_22_578845402.dbf (could this be an rman backup that was cataloged in the now corrupted repository?

Alert log shows several recovery attempts. The first went like this:

Fri Apr 7 11:48:12 2006
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN... Fri Apr 7 12:03:11 2006
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Fri Apr 7 12:23:50 2006
alter database recover tablespace orareppd_users01 Fri Apr 7 12:23:50 2006
ORA-959 signalled during: alter database recover tablespace orareppd_users01...
Fri Apr 7 12:28:12 2006
alter database open
Fri Apr 7 12:28:12 2006
ORA-1113 signalled during: alter database open... Fri Apr 7 12:33:39 2006
alter database recover tablespace users01 Fri Apr 7 12:33:39 2006
ORA-959 signalled during: alter database recover tablespace users01... Fri Apr 7 12:40:06 2006
alter database recover tablespace users
Fri Apr 7 12:40:06 2006
<etc, etc>

Then we have:

Media Recovery Start
ORA-275 signalled during: alter database recover automatic tablespace users...
Fri Apr 7 16:22:27 2006

Warning: Recovery session ended without issuing ALTER DATABASE RECOVER CANCEL

Mon Apr 10 08:41:21 2006
Starting ORACLE instance (normal)
Mon Apr 10 08:42:45 2006
alter database open
ORA-1113 signalled during: alter database open... Mon Apr 10 08:48:28 2006
alter database recover automatic database Mon Apr 10 08:48:28 2006
Media Recovery Start
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command. WARNING! Recovering data file 5 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command. WARNING! Recovering data file 8 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command. parallel recovery started with 2 processes Mon Apr 10 08:48:28 2006
Media Recovery Log
/db01/app/oracle/admin/orareppd/arch/orareppd_1_975_578845402.log
Mon Apr 10 08:48:36 2006
Media Recovery Log
/db01/app/oracle/admin/orareppd/arch/orareppd_1_976_578845402.log
Mon Apr 10 08:48:40 2006

Mon Apr 10 09:10:30 2006
Media Recovery Log
/db01/app/oracle/admin/orareppd/arch/orareppd_1_1253_578845402.log
Mon Apr 10 09:10:34 2006
Media Recovery Log
/db01/app/oracle/admin/orareppd/arch/orareppd_1_1254_578845402.log
Mon Apr 10 09:10:37 2006
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1255 Reading mem 0 Mem# 0 errs 0: /db02/oradata/orareppd/redoA_01.rdo Mon Apr 10 09:10:38 2006
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1256 Reading mem 0 Mem# 0 errs 0: /db02/oradata/orareppd/redoA_02.log Mon Apr 10 09:10:41 2006
Errors in file
/db01/app/oracle/admin/orareppd/udump/orareppd_ora_2147.trc:
ORA-00314: log 3 of thread 1, expected sequence# 1257 doesn't match 975 ORA-00312: online log 3 thread 1: '/db01/oradata/orareppd/redoA_03.log' Mon Apr 10 09:10:43 2006
Media Recovery failed with error 314
ORA-283 signalled during: alter database recover automatic database... Mon Apr 10 09:19:39 2006
Shutting down instance: further logons disabled

Notice that the last archive log applied was sequence 1254, then it started applying seq. 1255 and 1256 from the online redo, even though there were/are archives available for those. Notice also that the "doesn't match 975" points back to the first sequence applied from archivelogs.

Over the course of several more days there were some attempts to recover to time or to cancel:

Mon Apr 10 10:21:09 2006
alter database recover automatic database until time '2006-04-03:06:00:00'
Mon Apr 10 10:21:09 2006
Media Recovery Start
parallel recovery started with 2 processes Mon Apr 10 10:21:09 2006
Errors in file
/db01/app/oracle/admin/orareppd/udump/orareppd_ora_1373.trc:
ORA-00314: log 3 of thread 1, expected sequence# 1257 doesn't match 975 ORA-00312: online log 3 thread 1: '/db01/oradata/orareppd/redoA_03.log' Recovery interrupted!
Mon Apr 10 10:21:12 2006
Media Recovery failed with error 314
ORA-283 signalled during: alter database recover automatic database until time '2006-04-03:06:00:00'...
Mon Apr 10 10:21:45 2006
alter database recover cancel
Mon Apr 10 10:21:45 2006
ORA-1112 signalled during: alter database recover cancel... Mon Apr 10 10:21:51 2006

Which is where we are now.

I'm thinking my best bet at this point is to startup mount, then alter database open resetlogs, but I don't want to dig myself in any deeper than necessary.

Or maybe a 'recover until change 1254' ? Received on Thu May 04 2006 - 15:37:01 CDT