No question at this time
DBA Top 10
1 M. Cadot 10400
2 A. Kavsek 9400
3 B. Vroman 4800
4 P. Wisse 4300
5 J. Schnackenberg 2900
6 J. Péran 2000
7 . Lauri 1000
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
9 T. Boles 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48524
Total active users1409
Act. users last 24h3
Act. users last hour0
Registered user hits last week77
Registered user hits last month415
Go up

restore fails
Next thread: restore possibilities - until time before backup end
Prev thread: Oracle to stop spilled transaction

Message Score Author Date
Hi, An oracle exadata x86/64 failed due to ? powe...... Alain Bourgeois Jun 16, 2020, 22:46
Hi Alain, I hope you had a quite good night t...... Joël Péran Jun 17, 2020, 08:18
Alain, you also have this one <pre>(DB42) Possib...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Joël Péran Jun 17, 2020, 08:19
problem occurred in April. Customer tried to recov...... Alain Bourgeois Jun 17, 2020, 08:26
@Joël: the exadata failed (although redundancy hig...... Alain Bourgeois Jun 17, 2020, 08:36
Hi Alain, Did they try to create a new empty...... Joël Péran Jun 17, 2020, 08:43
I don't know if they tried an empty new instance, ...... Alain Bourgeois Jun 17, 2020, 08:56
Hi Alain, first let me express my deep sympathy...... Ales Kavsek Jun 17, 2020, 15:22
Hi Ales :) Nice to hear/read from you :) As...... Joël Péran Jun 17, 2020, 15:31
Hi Joel, I'm not "patch-man" either...even wors...... Ales Kavsek Jun 17, 2020, 15:51
Every patchset fixes some bugs... and introduces o...... Alain Bourgeois Jun 17, 2020, 15:51
Hii Alain, I agree with most of what you said, ...... Ales Kavsek Jun 17, 2020, 18:23
1. Even I can afford 160TB storage, enterprise gra...... Alain Bourgeois Jun 17, 2020, 19:03
Hi, re 1) 10 days?? For 5TB (three db’s conc...... Ales Kavsek Jun 17, 2020, 22:20
Hi Ales and Alain, The only way I have found...... Joël Péran Jun 18, 2020, 10:02
Anyway: * as I told, I am not the owner of this d...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Alain Bourgeois Jun 18, 2020, 13:49
@Joël: thanks, we are probably hitting bug referen...... Alain Bourgeois Jun 19, 2020, 16:43

Follow up by mail Click here


Subject: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 16, 2020, 22:46, 51 days ago
Os info: linux 6
Oracle info: 11.2.0.4 / 12.2.0.1
Error info: ORA-00756
ORA-10567
ORA-10564
RMAN-03002
ORA-01426
Message: Hi,
An oracle exadata x86/64 failed due to ? power outage on storage box ?, we don't know exactly, but lots of blocks corruption.
We have a backup of an 11.2.0.4 EE db with archive logs. This db holds a 23Tb datafile + some much smaller files.

1. Impossible to restore using oracle 11.2.0.4, error messages after 2 hours dur to bug:
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=229963926656405&parent=DOCUMENT&sourceId=1611785.1&id=20169408.8&_afrWindowMode=0&_adf.ctrl-state=10i0yc4wij_232, which I can't download.

2. Try to restore->recover from new oracle 12.2.
No issue at restore, but recover fails:
RMAN-03002: failure of recover command at 06/16/2020 20:25:47
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '+RECOC/cnamcs/archivelog/2020_06_16/thread_1_seq_250071.558.1043228585'
ORA-00756: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 5, block# 2381343626, file offset is 225525760 bytes)
ORA-10564: tablespace MAINTBS
ORA-01110: data file 5: '+DATAC/CNAMCS/DATAFILE/maintbs.266.1042218047'
ORA-10560: block type '0'
.

I tried
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 2381343626 FROM BACKUPSET;

Starting recover at 16-JUN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2020 11:20:06
ORA-01426: numeric overflow.

Any hint?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Joël Péran, France
Date: Jun 17, 2020, 08:18, 51 days ago
Message: Hi Alain,

I hope you had a quite good night though it seems the problem appeared yesterday ...
You may have query the MOS site for a solution, but in case you have not, it seems there are patches for 12.2 releases that may help you ... See this note :
(RA9) Bug 22302666 - REDO INCONSISTENT WITH DATA BLOCK FOLLOWING DATABASE RECOVERY FROM FINAL_CHANGE# (Doc ID 2101817.1)

I hope this will help you a bit.
Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Joël Péran, France
Date: Jun 17, 2020, 08:19, 51 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Alain, you also have this one
(DB42) Possible logical data corruption when recovering a datafile with an incremental backup on Exadata (Doc ID 2426886.1)


Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 17, 2020, 08:26, 51 days ago
Message: problem occurred in April. Customer tried to recover, told they couldn't, provided the last backup and asked for help.
Their company is down for >1 month, and they are supposed to send us data to produce. So our subsidiary is also down because of this.

Anyway:
* we are just trying to restore the backup outside exadata, it fails.
* the problem you mention happens after an open resetlogs. Here we didn't even open resetlogs, we could not even do recovery.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 17, 2020, 08:36, 51 days ago
Message: @Joël: the exadata failed (although redundancy high), and they already tried to restore this backup on the exadata. They have the same kind of errors and so they have no running database anymore. the option to take a new backup is not valid.

I thought the problem was due to exadata storage but restoring the database on a new system doesn't work neither. I don't know if issue is due to wrong backup (bug) or wrong restore (bug) or wrong storage.

What could be tried (source db is oracle 11.2.0.4): is it possible to restore and recover an oracle 11.2.0.4 using oracle 12.2.0.1 patchset?
If yes, I will try to restore and recover using oracle 12.2.0.1 with jan 2020 patchset and hope this will work, but it is the only thing I can do.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Joël Péran, France
Date: Jun 17, 2020, 08:43, 51 days ago
Message: Hi Alain,

Did they try to create a new empty 12.2 instance and to restore the backup, then using recover with backed up controlfile and so on ? I bet yes, but I think that 11.2 database and 12.2 rman ...
Regards,


Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 17, 2020, 08:56, 51 days ago
Message: I don't know if they tried an empty new instance, but 11.2 restore doesn't work (see above, a 23Tb datafile with 217 sections, bug during restore). The customer didn't pay extended support (they never have patched the system anyway), and oracle 11 patches are not downloadable anymore.

As we don't trust the Exadata storage (for now), because it crashed (that's why they want to restore a backup), we are trying to bring back data from last backup and archivelogs on a different system using oracle 12.2, to be able to recover and a definitive solution will be found afterwards to repair exadata or replace hardware. But we need the data, and 12.2 (without patches) couldn't do the job. If we can't, they will have to start from scratch (with another database than oracle in that case, for sure), or go bankruptcy.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Ales Kavsek, Slovenia
Date: Jun 17, 2020, 15:22, 51 days ago
Message: Hi Alain,

first let me express my deep sympathy...it got me sick just reading this thread. :( I hope you'll find a solution.

If I were you I would:

- definitely contact Oracle support and ask for help (ACS - Advanced Customer Support). Sure, company will have to pay them for the assistance, but what's the alternative?

- if you hit bug 28305362 then what you intend to do (restore level 0 + apply archive logs instead of RMAN incremental backups which they're corrupt anyway) might very well be your best option to try out

And after all this is over, I would ask DBA team a few questions:
- do you think it's "smart" to rely on single 23TB datafile? (No matter what Oracle claims)

- do you think it would be a nice practice to excercise regular restores on some spare machine with big SATA drives (personally, I was doing this regularly, once per month - moreover after successful restore I backuped this known good RMAN backup set to extra LTO tape which was then locked in a safe -- I was recently replaced by true professionals who knows better, they simply abandoned this practice....I guess more in a spirit of "Viva Las Vegas" song rather my favorite one, evergreen "Yesterday" ....https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=56760#177245 ;-)

- I'm not even opening the subject of regular patching - patching is for sissies ;)

Good luck Alain, I wish you all the best!

Regards,
Ales
ex-Oracle DBA
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Joël Péran, France
Date: Jun 17, 2020, 15:31, 51 days ago
Message: Hi Ales :)
Nice to hear/read from you :)

As for the patching thing, you know, you can't do what you want to or need to do. It's not that simple. For example, if I have to patch all my Oracle servers every three months, it would take alot of time (not the problem), but also impact all the applications dealing with my 35 oracle databases. And theses applications are not compliant with an oracle unavailability. Plus, they must test and test again every single part of their applications (which could take hours for a single application). Even the windows patching is a mess : we are doing it two times per year instead of every two or three months. It depends on a lot of facts ... And so I keep the "best" PSU for the version I have (which is 12.1.0.2.17.10.17 for example).
For our poor Alain, I just have the same feelings you have :)

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Ales Kavsek, Slovenia
Date: Jun 17, 2020, 15:51, 51 days ago
Message: Hi Joel,

I'm not "patch-man" either...even worse, would you believe me that I have (had) one particular Windows 2003 R2 server (Fujitsu from 2009!) running non-stop since January 2012 to at least January 2020? Yes, 8+ years non-stop. Not a single reboot, not a single patch.
(I have a screenshot from uptimei command line tool, showing remarkable 100% of uptime - now, you know how crazy I really am -- it was about time someone finally fired me ;-))).
And what about malware? No worries, even malware couldn't run on this system anymore, because they would need to backport the code :)).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 17, 2020, 15:51, 51 days ago
Message: Every patchset fixes some bugs... and introduces other ones! (even the last ones).

Oracle is expensive, and is chosen to be supposed reliable.
Customer doesn't have 120Tb (25 for current backups, 25 for previous backup, 25 for prod, and 25 for restore test).
Customer doesn't also have a separate exadata/licence to test the restore (it would be the case he would have data guard).
Furthermore, error occur during recover, not during restore.

The backup we have is a full backup, not an incremental.

"- do you think it's "smart" to rely on single 23TB datafile? (No matter what Oracle claims)" -> I also prefer lots of 50Gb datafiles... but BIGFILE tablespace exists for a long time and is recommended by oracle. So a restore should work.

oracle 11.2.0.4 is 3 sub-releases after 11.2.0.1, and is not even capable of restore/recovery of a full backup, a functionality that MUST work out-of-the-box with any database (even free ones allow this). Patches to restore using 11.2.0.4 bigfile with many sections were published in 2019/06, 6 months after extended support. A shame.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Ales Kavsek, Slovenia
Date: Jun 17, 2020, 18:23, 51 days ago
Message: Hii Alain,

I agree with most of what you said, but not with your conclusion that customer can not afford storage for testing backups. Even I can afford 160TB storage, enterprise grade SATA drive with 16TB capacity is around 700€. Customer would need a spare server in the range of 3K - 4K€ that would allow full restore of 25TB database over a weekend. You don't need spare Exadata for that. I put together my own server for the backup/restore testing purpose. About single file big tablespace I can say only that this was silly Oracle idea from the very beginning, it's just asking for trouble. Unfortunately, you (I'm not meaning personally you) learnt the lesson the hard way.
Also your assumption that full restore should simply work just because Oracle is so expensive. Remember that backup and restore tools are just computer piece of code, with possibility of bugs. The only way to properly test if backup is Ok is with regular restore & recovery (using rman validate command doesn't count, I don't trust them). As I said in my old post, the yesterday is always the most important day in the life of DBA.

I wish you all the best.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 17, 2020, 19:03, 51 days ago
Message: 1. Even I can afford 160TB storage, enterprise grade SATA drive with 16TB capacity is around 700€. Customer would need a spare server in the range of 3K - 4K€ that would allow full restore of 25TB database over a weekend.

=> sorry, in our case a restore took 10 days. Don't know yet it it is due to rman or infra, or both.


2. If you do this, you must have a separate with automatic restore-recover scripts, so extra licenses! If you have this, you have a data guard.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Ales Kavsek, Slovenia
Date: Jun 17, 2020, 22:20, 50 days ago
Message: Hi,

re 1)

10 days?? For 5TB (three db’s concurrently) on Quad core server from ~2008, from compressed backup set I needed ~8-9h. With 5x2TB sata drives on RAID 5. (I’m talking about 11gr2 ee). So, on this ancient PC I could easily restore ~200TB in 10 days (with larger disks, of course). Rman restore from compressed backup is cpu intensive, but easy on i/o.

re 2)

well, this is partially true. You can test Oracle restore four times per calendar year for the period of two days for free. I believe two days is more than enough for 25TB, if you need 10 days for mere 25TB then something fundamentally wrong is in place at your site.
You don’t really need fully licensed server for monthly test routine, license for 1 CPU is more than enough. Consider that cost with the cost your company is facing right now - you just lost database (if level 1 backup is corrupt it means you don’t have backup at all). In this case Oracle can help you dump data from raw datafiles, whatever can be recovered (or perhaps somehow fix corrupted blocks - basically skip them, which really means your db might become inconsistent).

regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Joël Péran, France
Date: Jun 18, 2020, 10:02, 50 days ago
Message: Hi Ales and Alain,

The only way I have found to test our rman backup is ... to restore production backups on integration/test database. And then change SID of the database with NID. SO I can check that my backup was successfully taken :)

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 18, 2020, 13:49, 50 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Anyway:
* as I told, I am not the owner of this db nor responsible of thus stuff, just a fireman tring to find a solution,
* customer doesn't have 25Tb on disks of the same type, he has to mix fast and slow disks in the diskgroup to restore (for now),
* restore took 10 days on this hardware, I can't do anything to it and don't have other hardware available in this production site (in africa), and shipping 25Tb over the internet is not an option,
* if you see the first message, it seems blockrecover is not supported for a block nr > 2^31,
* testing is a good thing... but sure restore worked fine when db was small. At some time it doesn't work anymore, this is unaccaptable (and bugfix arrive in June 2019, so in extended support).
* So I will retry once more, and if no solution, we will discuss with customer to restart from scratch... but this customer will never use oracle anymore (and from my point of view it is a good thing, after such a mess on an exadata with "high redundancy". What a joke! Hi failure redundancy!).
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: restore fails
Author: Alain Bourgeois, Belgium
Date: Jun 19, 2020, 16:43, 49 days ago
Message: @Joël: thanks, we are probably hitting bug referenced in 2426886.1 . Incremental backups were taken after the full backup.

Do you know any procedure to restore Level 0 + archivelogs and tell rman to not use level 1?

Regards.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here