Recovering disk failures that comprise undo segements

This is just a snapshot of some sitation that happened quite a while ago on a 10g oracle database. I’m not going to dive into the details that much, just providing some selects to decipher the scenario and show up one of the probable ways of resolving the problem.

Jep, due to a disk failure, as i noticed during the analysis, some statements and things complained about datafiles being offline. An attempt to re-online some datafile resulted in an ORA-00376 error as follows:

SQL> ALTER DATABASE DATAFILE 'H:\app_ORACLE\SAN_4\ORADATA\appxyz\stuff\app_UTL_06.DBF' ONLINE;
ALTER DATABASE DATAFILE 'H:\app_ORACLE\SAN_4\ORADATA\appxyz\stuff\app_UTL_06.DBF' ONLINE
*
FEHLER in Zeile 1:
ORA-00376: Datei 6 kann zur Zeit nicht gelesen werden
ORA-01110: Datendatei 6: 'H:\app_ORACLE\SAN_3\ORADATA\appxyz\UNDOTBS03.DBF'

Looking up dba_data_files produced this output:

Simulating and resolving a dataguard archivelog gap

I was wondering what will happen iff some archivelog gap really happens in a dataguard environment. How to resolve it? What has to be done manually and what happens automatically due to the remote file service (fal), for example.

Well, my attempt in producing the problem goes like this:

• verify normal operation by doing manual log switches on primary and watching the alert.log on primary and standby what happens
• defering the remote log destination (usually #2) on primary
• doing another manual log switches on primary that do now, don’t get shipped to standby
• backup primary with removing all backed up archive logs from the recovery destination
• bounce primary and standby
• activating the remote log destination (usually #2) on primary again, doing another manual log switches on primary, watching the alert.log on primary and standby what happens

Ok, let’s follow the alert.log on primary and standby …

Uncataloging a backupset from the rman repository

Ever seen this error message?

RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET

Seems you’re trying the get clean of backup sets that still exists on disk or tape but should no longer be regarded as a part of this database. What for? Well, this is quite a common requirement for rman-cloned or -duplicated databases where the rman repository (controlfile copy) originates from a seed database. Think of a standby database creation for example.

rman duplicating an oracle database in a new sid / two host scenario

introduction

there are many ways to clone an oracle database. you may just copy and init a cold backup, do a rman restore / recover or employ datapump. according to the number of possibilities there is another multitude number of things you have to evaluate in advance, prepare, configure, keep in mind, postprocess etc., for every clone procedure. me, i mostly use cloning by cold backup, preferably if cloning to another host is requested and if the database system identifier (sid) remains the same. although this way of cloning nearly needs no additional work, you have to take into account that copying the (cold) files from source to destination may take a considerable amount of time, especially for large databases (i’m going to show in another post how to accelerate that by san-snapshotting, a technique becoming deservedly popular lately).

if you cannot go with source database downtimes at all, the rman duplicate functionality might be the preferred choice. it is sort of rman restore / recover, can therefore live on a hot backup, but saves yourself from playing around with controlfile backups / scripts or the nid tool for example. much more far from it, rman duplicate even uses a live, the source, controlfile for automated database structure setup on the destination. but now let’s see how a rman duplicate may work for a given scenario.

A restore and recovery duration test

the following information is something one hardly finds on the net or in printed material. that is, this information is not really tricky to examine or even secret. it is just difficult to describe and to express because it depends on such a multitude of other information, mainly system power and configuration, that no rule of thumb can be established.

the information is: how long does it take to fully restore and recover my database (did you ever try?).

i’m not going to discuss performance optimizations here. i’ll only show up with a test lab report that someone else may or may not reuse for a personal examination or comparison.

this is what i had:

• dell pe6850, 4 xeon 7130 (dual core, 3.2 ghz), 8 gb ram, win 2003 enterprise r2 sp2
• oracle 10.2.0.3 enterprise
• 400 gb online database size
• 110 gb compressed backup set size (85 gb level 0 backup, 20 gb level 1 backup, 5 gb archivelog backup)
• 3 active rman channels to disk
• nearly no secondary load on the system

this is what i achived:

• 5 hours

have fun.