Mathias Zarick's Blog

Recover from Lost Online Redo Logs with Data Guard

Mathias Zarick — Fri, 06 Nov 2009 12:09:00 GMT

Hi there!

In my last Data Guard course this week I gave my participants the task to recover from a situation where they lost the online redo log files of the primary database. They had a Maximum Availability Setup with Synchronous Log Transfer. Of course due to this setup there was no loss of any data when performing the failover to the standby site. The participants were able to failover the right way. Some also used an observer for doing this. The harder task after the failover was the reinstatement of the former primary, the one with the lost logs. Reinstatement means using it again as standby instantly and to save the time to set it up again. To let this work you need to have flashback enabled before.

In this blog entry I want to summarize the needed steps for the whole scenario.

Environment looked like this:
10.2.0.4.0, Linux x86_64, flashback is enabled on Primary and Standby

Data Guard Configuration Overview:

DGMGRL> connect sys@DG_SITE1
Password:
Connected.
DGMGRL> show configuration

Configuration
Name:                DG
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
    DG_SITE1 - Primary database
    DG_SITE2 - Physical standby database

Current status for "DG":
SUCCESS

Step One – Lose the Online Logs at Primary
Oh yeah that’s the funny part!

I will delete 'em, they are important. But of course i made them very important. After a bunch of nonsense transactions all my three Online Log Groups are ACTIVE, which means they contain information not yet written down by Database Writer:

SQL> select group#, sequence#, members, status from v$log;

    GROUP# SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         75          2 ACTIVE
         2         76          2 ACTIVE
         3         77          2 CURRENT

Now please go away :-)

select 'rm ' || member from v$logfile f, v$log l
where f.group# = l.group#;

SQL> select 'rm ' || member from v$logfile f, v$log l
2 where f.group# = l.group#;

'RM'||MEMBER
-----------------------------------
rm /u00/oradata/DG/redog1m1DG.dbf
rm /u01/oradata/DG/redog1m2DG.dbf
rm /u00/oradata/DG/redog2m1DG.dbf
rm /u01/oradata/DG/redog2m2DG.dbf
rm /u00/oradata/DG/redog3m1DG.dbf
rm /u01/oradata/DG/redog3m2DG.dbf

6 rows selected.

After calling those 6 statements, I switched the logfiles, until the db was hanging. It was the third switch. The alert log shows a couple of such errors:

Fri Nov 6 10:58:45 2009
Errors in file /u00/app/oracle/admin/DG/bdump/dg_arc1_21328.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/DG/redog3m2DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u00/oradata/DG/redog3m1DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Step Two – Failover
Okay now it is time to let our users work again on our standby site. So lets activate the standby.

DGMGRL> connect sys@DG_SITE2
Password:
Connected.
DGMGRL> failover to 'DG_SITE2'
Performing failover NOW, please wait...
Failover succeeded, new primary is "DG_SITE2"

I also shut down old primary with "shutdown abort".

That’s all. The users are happy and can connect to the new primary database. No data loss occurred due to the synchronous setup.

Step Three – Rebuild the Redundancy with Reinstatement

The Data Guard Configuration looks now like this:

DGMGRL> connect sys@DG_SITE2
Password:
Connected.
DGMGRL> show configuration;

Configuration
Name:                DG
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
    DG_SITE1 - Physical standby database (disabled)
    DG_SITE2 - Primary database

Current status for "DG":
SUCCESS

Note: We are automatically in Max Performance Mode due to the fact that we are on a single instance solution for the moment.

Next thing is to restart the former primary in mount mode to get it reinstated. After this the broker says how to proceed:

DGMGRL> connect sys@DG_SITE1
Password:
Connected.
DGMGRL> show configuration;
Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

The broker demands the reinstatement of DG_SITE1, which would do the observer automatically. Let’s do this now:

DGMGRL> connect sys@DG_SITE2
Password:
Connected.
DGMGRL> reinstate database 'DG_SITE1';
Reinstating database "DG_SITE1", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "DG_SITE1" failed

The reinstatement failed, let's have a look at the alert log:

FLASHBACK DATABASE TO SCN 335290
Fri Nov 6 11:20:45 2009
Errors in file /u00/app/oracle/admin/DG/bdump/dg_rsm0_21445.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/DG/redog1m2DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u00/oradata/DG/redog1m1DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri Nov 6 11:20:45 2009
Errors in file /u00/app/oracle/admin/DG/bdump/dg_rsm0_21445.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/DG/redog2m2DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u00/oradata/DG/redog2m1DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri Nov 6 11:20:45 2009
Errors in file /u00/app/oracle/admin/DG/bdump/dg_rsm0_21445.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/DG/redog3m2DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u00/oradata/DG/redog3m1DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-38754 signalled during: FLASHBACK DATABASE TO SCN 335290...

The Log Message from drcDG.log which is located in the same directory is even more frustating:

DG 2009-11-06-11:20:45        0 2 0 Physical RSM: Reinstatement failed. Flashback database to SCN 335290 fails. If there is insufficient flashback log to flashback this database, reinstatement will never succeed. You will have to do a full physical standby re-instantiation of this database.

Okay flashback operation which was called by the reinstatement failed due to the missing logs.

Hmm, let's clear the logs:

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance DG (thread 1)
ORA-00312: online log 2 thread 1: '/u00/oradata/DG/redog2m1DG.dbf'
ORA-00312: online log 2 thread 1: '/u01/oradata/DG/redog2m2DG.dbf'

SQL> alter database clear unarchived logfile group 3;

Database altered.

Okay I could not clear logfile group 2 because it is know from controlfile that in any case it is needed to recover from it. And that's why also the flashback would still fail. So we need this redo log information. Hey, we should find it at our new primary, right!? Let's first query what we need:

SQL> select first_change# from v$log where group#=2;

FIRST_CHANGE#
-------------
       335153

On the new primary we search for this scn in the archived redo logs after a log switch:

SQL> select name from v$archived_log where FIRST_CHANGE#>=335153;

NAME
-----------------------------------------------------
/u00/app/oracle/admin/DG/arch/DG_1_79_702056159.arc
/u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc

So I copy those 2 logs from 2 different incarnations (cause of the resetlogs of the failover) from the new primary to the former primary and register it there with RMAN:

RMAN> catalog start with '/u00/app/oracle/admin/DG/arch';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u00/app/oracle/admin/DG/arch

List of Files Unknown to the Database
=====================================
File Name: /u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc
File Name: /u00/app/oracle/admin/DG/arch/DG_1_79_702056159.arc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc
File Name: /u00/app/oracle/admin/DG/arch/DG_1_79_702056159.arc

Now I tried the flashback on the former primary again, I used the same statement which I found in the alert log before:

SQL> FLASHBACK DATABASE TO SCN 335290
2 ;
FLASHBACK DATABASE TO SCN 335290
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 78 in thread 1, incarnation 6 could not be accessed

Nope, we need obviously more logs. So lets get all which are missing in comparison with the new primary:
In my case this were the logs DG_1_77_702056159.arc and DG_1_78_702056159.arc.

After registering them with the same method like above, we can try the flashback again:

SQL> FLASHBACK DATABASE TO SCN 335290;
FLASHBACK DATABASE TO SCN 335290
*
ERROR at line 1:
ORA-38770: FLASHBACK DATABASE failed during recovery.
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/DG/redog2m2DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u00/oradata/DG/redog2m1DG.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Ooops I forgot the clearing of logfile group 2, let's do this now and retry then:

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> FLASHBACK DATABASE TO SCN 335290;

Flashback complete.

Cool, now we can reissue the reinstatement:

DGMGRL> connect sys@DG_SITE2
Password:
Connected.
DGMGRL> reinstate database 'DG_SITE1';
Reinstating database "DG_SITE1", please wait...
Operation requires shutdown of instance "DG" on database "DG_SITE1"
Shutting down instance "DG"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DG" on database "DG_SITE1"
Starting instance "DG"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "DG_SITE1" ...
Reinstatement of database "DG_SITE1" succeeded
DGMGRL> show configuration

Configuration
Name:                DG
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
    DG_SITE1 - Physical standby database
    DG_SITE2 - Primary database

Current status for "DG":
SUCCESS

Step Four – Check the Setup with a Switchover

Now we come to the end by reenabling Maximum Availability. Then we check everything by performing a switchover.

DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
DGMGRL> show configuration

Configuration
Name:                DG
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
    DG_SITE1 - Physical standby database
    DG_SITE2 - Primary database

Current status for "DG":
SUCCESS

I made some log switches at the new primary and checked the reception and recovery at the new standby (former primary).

SQL> select name, applied from v$archived_log
2 where first_change# >=335153 order by first_change#;

NAME                                                    APP
------------------------------------------------------- ---
/u00/app/oracle/admin/DG/arch/DG_1_79_702056159.arc     NO
/u00/app/oracle/admin/DG/arch/DG_1_79_702056159.arc     YES
/u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc      NO
/u00/app/oracle/admin/DG/arch/DG_1_2_702212594.arc      NO
/u00/app/oracle/admin/DG/arch/DG_1_3_702212594.arc      NO
/u00/app/oracle/admin/DG/arch/DG_1_4_702212594.arc      NO
/u00/app/oracle/admin/DG/arch/DG_1_5_702212594.arc      NO
/u00/app/oracle/admin/DG/arch/DG_1_6_702212594.arc      NO
/u00/app/oracle/admin/DG/arch/DG_1_7_702212594.arc      NO

9 rows selected.

It seems that the standby receives the logs but does not recover in our new incarnation.. Hmm.
Alert Log says:

Fri Nov 6 11:54:26 2009
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Fri Nov 6 11:54:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_79_702056159.arc
Identified End-Of-Redo for thread 1 sequence 79
Fri Nov 6 11:54:26 2009
Incomplete Recovery applied until change 335292
MRP0: Background Media Recovery applied all available redo. Recovery will be restarted once new redo branch is registered
Fri Nov 6 11:54:26 2009
Errors in file /u00/app/oracle/admin/DG/bdump/dg_mrp0_21557.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Fri Nov 6 11:54:27 2009
Errors in file /u00/app/oracle/admin/DG/bdump/dg_mrp0_21557.trc:
ORA-19906: recovery target incarnation changed during recovery

Let's fix this by creating a new standby controlfile. At the new primary:

SQL> alter database create standby controlfile as '/tmp/stbyctlDG.ctl';

Database altered.

I shut down (immediate) the new standby, copied the standby controlfile which I created right now to the right locations and started it again in mount mode.
Alert Log:

Fetching gap sequence in thread 1, gap sequence 1-7
Fri Nov 6 12:02:28 2009
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc'
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_2_702212594.arc'
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_3_702212594.arc'
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_4_702212594.arc'
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_5_702212594.arc'
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_6_702212594.arc'
RFS[3]: Archived Log: '/u00/app/oracle/admin/DG/arch/DG_1_7_702212594.arc'
Fri Nov 6 12:02:30 2009
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
RFS[1]: Successfully opened standby log 22: '/u00/oradata/DG/redog22m1DG.dbf'
Fri Nov 6 12:02:58 2009
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_2_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_3_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_4_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_5_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_6_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_7_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_8_702212594.arc
Media Recovery Log /u00/app/oracle/admin/DG/arch/DG_1_9_702212594.arc
Media Recovery Waiting for thread 1 sequence 10 (in transit)
Fri Nov 6 12:02:59 2009
Recovery of Online Redo Log: Thread 1 Group 22 Seq 10 Reading mem 0
Mem# 0: /u00/oradata/DG/redog22m1DG.dbf
Mem# 1: /u01/oradata/DG/redog22m2DG.dbf

Same Query like above:
SQL> select name, applied from v$archived_log
2 where first_change# >=335153 order by first_change#;

NAME                                                    APP
------------------------------------------------------- ---
/u00/app/oracle/admin/DG/arch/DG_1_1_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_2_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_3_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_4_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_5_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_6_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_7_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_8_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_9_702212594.arc      YES
/u00/app/oracle/admin/DG/arch/DG_1_10_702212594.arc     YES

10 rows selected.

What is with the flashback? Is it still switched on on my current standby database?
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

Okay, we fix this quickly:

DGMGRL> connect sys@DG_SITE1
Password:
Connected.
DGMGRL> edit database 'DG_SITE1' set state='LOG-APPLY-OFF';
Succeeded.

SQL> alter database flashback on;

Database altered.

DGMGRL> connect sys@DG_SITE1
Password:
Connected.
DGMGRL> edit database 'DG_SITE1' set state='ONLINE';
Succeeded.

Yes - now I'm happy and ready for the switchover which again changes the roles. But this time gracefully. It switches back the DG_SITE1 as a primary and DG_SITE2 as a standby database.

DGMGRL> switchover to 'DG_SITE1';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "DG" on database "DG_SITE2"
Shutting down instance "DG"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "DG" on database "DG_SITE1"
Shutting down instance "DG"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DG" on database "DG_SITE2"
Starting instance "DG"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "DG" on database "DG_SITE1"
Starting instance "DG"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "DG_SITE1"
DGMGRL> show configuration;

Configuration
Name:                DG
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
    DG_SITE1 - Primary database
    DG_SITE2 - Physical standby database

Current status for "DG":
SUCCESS

Everything is fine now again. And we saved the time for resetting up the standby from a backup or a clone of the new primary, which could have been essential if you think about TB databases.

Dear Reader!
Never lose your online redo log files! Never ever lose your online redo log files! Have fun with the big repertoire of possibilties in recovery scenarios with Data Guard!

Mathias

Does Change Data Capture need Partitioning?

Mathias Zarick — Wed, 17 Jun 2009 12:38:00 GMT

In last days I was confrontated with a Change Data Capture (CDC) Setup.
CDC is a nice feature which is based on Oracle Streams.
With CDC it is possible to track all the changes on a source table in a change table.
This information can be used by ETL processes for loading your data warehouses.

So it is obviously a data warehouse feature. Probably that is the reason
Oracle put it in the Data Warehousing Guide (Chapter 16).
More info on CDC can be found here.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/cdc.htm#i1028295
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_cdc_cookbook_0206.pdf

One prerequisite is that you have Java / JServer loaded into your database at
least at the sites where you have publishers and subscribers.
So if you use downstream capturing with autolog mode, you will need it
at the target site with the change tables.
JServer comes with standard dbca setups and costs no extra licence.

To check whether Java is installed properly have a look at dba_registry.

select status,version,comp_name
from dba_registry
where upper(comp_name) like '%JAVA%';

To install it later if it is missing use

connect / as sysdba
set feedback on termout off
spool install_java.log
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
spool off

CDC is installed through the script initcdc.sql from OH/rdbms/admin
which is called by catjava.sql. This loads CDC.jar from OH/rdbms/jlib,
installs 4 database triggers in sys schema which are named
cdc_alter_ctable_before, cdc_create_ctable_after,
cdc_create_ctable_before, cdc_drop_ctable_before.
These triggers are created disabled, and are enabled implicitly through
a CDC setup.

If you haven't installed Java you will experience following error, when
you try to create a change table.

ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Okay ... lets go to some questions regarding Oracle products and licenses,
as theses are of importance nowadays.
Synchronous CDC (does this really anybody need out there?) works also with
Standard Edition, Partitioning is not available for Standard Edition,
so change tables are not partitioned ones there.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - Production

SQL> rem NOTICE! banner does not contain the word "Enterprise" so its Standard Edition!
SQL> rem
SQL> rem test user is scott
SQL> grant dba to scott identified by tiger;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create table cdc_test ( a number, b date, c varchar2(20) );

Table created.

SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3      owner             => 'SCOTT',
4      change_table_name => 'CDC_TEST_CT',
5      change_set_name   => 'SYNC_SET',
6      source_schema     => 'SCOTT',
7      source_table      => 'CDC_TEST',
8      column_type_list => 'A NUMBER,
9                            B DATE,
10                            C VARCHAR2(20)',
11      capture_values    => 'BOTH',
12      rs_id             => 'Y',
13      row_id            => 'Y',
14      user_id           => 'Y',
15      timestamp         => 'Y',
16      object_id         => 'N',
17      source_colmap     => 'Y', -- only allowed in sync mode
18      target_colmap     => 'Y',
19      options_string    => 'PCTFREE 0',
20      ddl_markers       => 'N' -- new as of 11g but must be 'N' in sync env
21    );
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> rem lets have a look at the table
SQL> set long 10000 pages 30
SQL> select dbms_metadata.get_ddl('TABLE','CDC_TEST_CT') from dual;

DBMS_METADATA.GET_DDL('TABLE','CDC_TEST_CT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."CDC_TEST_CT"
   (    "OPERATION$" CHAR(2),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "RSID$" NUMBER,
        "ROW_ID$" ROWID,
        "USERNAME$" VARCHAR2(30),
        "TIMESTAMP$" DATE,
        "SOURCE_COLMAP$" RAW(128),
        "TARGET_COLMAP$" RAW(128),
        "A" NUMBER,
        "B" DATE,
        "C" VARCHAR2(20)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

We have a table that is not partitioned.

When I implemented asynchronous CDC on Enterprise Edition I noticed, that the
change tables were created partitioned.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> rem NOTICE! banner says that this is Enterprise Edition!
SQL> rem Partitiong is also mentioned there
SQL> select value from v$option where parameter = 'Partitioning';

VALUE
----------------------------------------------------------------
TRUE

SQL> rem test user is scott
SQL> create user scott identified by tiger;

User created.

SQL> rem cdc needs a some privileges for a publisher
SQL> grant create session, create table, create sequence, create job to scott;

Grant succeeded.

SQL> grant select_catalog_role, execute_catalog_role, dba to scott;

Grant succeeded.

SQL> grant execute on dbms_cdc_publish to scott;

Grant succeeded.

SQL> exec dbms_streams_auth.grant_admin_privilege(grantee => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> rem async cdc is based on streams which needs supplemental logging
SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> connect scott/tiger
Connected.
SQL> create table cdc_test ( a number, b date, c varchar2(20) );

Table created.

SQL> rem table needs supplemental logging and instantiation
SQL> alter table cdc_test add supplemental log data (all) columns;

Table altered.

SQL> exec dbms_capture_adm.prepare_table_instantiation(table_name => 'scott.cdc_test');

PL/SQL procedure successfully completed.

SQL> rem we need a change set in this example for asynchronous hotlog mode
SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
3      change_set_name    => 'MY_TEST_CHANGE_SET',
4      description        => 'async hotlog change set',
5      change_source_name => 'HOTLOG_SOURCE'
6      );
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> rem and now the change table
SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3      owner             => 'SCOTT',
4      change_table_name => 'CDC_TEST_CT',
5      change_set_name   => 'MY_TEST_CHANGE_SET',
6      source_schema     => 'SCOTT',
7      source_table      => 'CDC_TEST',
8      column_type_list => 'A NUMBER,
9                            B DATE,
10                            C VARCHAR2(20)',
11      capture_values    => 'BOTH',
12      rs_id             => 'Y',
13      row_id            => 'Y',
14      user_id           => 'Y',
15      timestamp         => 'Y',
16      object_id         => 'N',
17      source_colmap     => 'N',
18      target_colmap     => 'Y',
19      options_string    => 'PCTFREE 0',
20      ddl_markers       => 'Y' -- new as of 11g, now we can make use of it
21    );
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> rem and how does it look like?
SQL> set long 10000 pages 120
SQL> select dbms_metadata.get_ddl('TABLE','CDC_TEST_CT') from dual;

DBMS_METADATA.GET_DDL('TABLE','CDC_TEST_CT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."CDC_TEST_CT"
   (    "OPERATION$" CHAR(2),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "XIDUSN$" NUMBER,
        "XIDSLT$" NUMBER,
        "XIDSEQ$" NUMBER,
        "RSID$" NUMBER,
        "ROW_ID$" ROWID,
        "USERNAME$" VARCHAR2(30),
        "TIMESTAMP$" DATE,
        "TARGET_COLMAP$" RAW(128),
        "DDLOPER$" NUMBER,
        "DDLDESC$" CLOB,
        "DDLPDOBJN$" NUMBER,
        "A" NUMBER,
        "B" DATE,
        "C" VARCHAR2(20)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("DDLDESC$") STORE AS BASICFILE (
ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(
BUFFER_POOL DEFAULT))
PARTITION BY RANGE ("CSCN$")
(PARTITION "P1" VALUES LESS THAN (281474976710656)
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("DDLDESC$") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) NOCOMPRESS )

Yes! We have range partitioning, partition key is CSCN$.
So ... we would have to buy the partitioning licence.
But does this mean that we have to, when using CDC?
No! You get it running also without partitioning, but the feature must not be installed
or at least not linked into the oracle binary when creating the change tables.

To relink the oracle binary without partitioning use following commands
when all databases that use that oracle home are stopped.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk part_off ioracle

To relink into the other direction use part_on instead of part_off.
Relinking cannot be done on Windows.

So again, the CDC Setup after relinking without partitioning:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the OLAP, Data Mining and Real Application Testing options

SQL> rem NOTICE! banner says that this is Enterprise Edition!
SQL> rem Partitiong is NOT mentioned any more
SQL> select value from v$option where parameter = 'Partitioning';

VALUE
----------------------------------------------------------------
FALSE

SQL> rem test user is scott
SQL> rem we recreate him
SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by tiger;

User created.

SQL> grant create session, create table, create sequence, create job to scott;

Grant succeeded.

SQL> grant select_catalog_role, execute_catalog_role, dba to scott;

Grant succeeded.

SQL> grant execute on dbms_cdc_publish to scott;

Grant succeeded.

SQL> exec dbms_streams_auth.grant_admin_privilege(grantee => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
Connected.
SQL> create table cdc_test ( a number, b date, c varchar2(20) );

Table created.

SQL> alter table cdc_test add supplemental log data (all) columns;

Table altered.

SQL> exec dbms_capture_adm.prepare_table_instantiation(table_name => 'scott.cdc_test');

PL/SQL procedure successfully completed.

SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
3      change_set_name    => 'MY_TEST_CHANGE_SET',
4      description        => 'async hotlog change set',
5      change_source_name => 'HOTLOG_SOURCE'
6      );
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3      owner             => 'SCOTT',
4      change_table_name => 'CDC_TEST_CT',
5      change_set_name   => 'MY_TEST_CHANGE_SET',
6      source_schema     => 'SCOTT',
7      source_table      => 'CDC_TEST',
8      column_type_list => 'A NUMBER,
9                            B DATE,
10                            C VARCHAR2(20)',
11      capture_values    => 'BOTH',
12      rs_id             => 'Y',
13      row_id            => 'Y',
14      user_id           => 'Y',
15      timestamp         => 'Y',
16      object_id         => 'N',
17      source_colmap     => 'N',
18      target_colmap     => 'Y',
19      options_string    => 'PCTFREE 0',
20      ddl_markers       => 'Y' -- new as of 11g, now we can make use of it
21    );
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> rem and again, how does the change table look like?
SQL> set long 10000 pages 120
SQL> select dbms_metadata.get_ddl('TABLE','CDC_TEST_CT') from dual;

DBMS_METADATA.GET_DDL('TABLE','CDC_TEST_CT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."CDC_TEST_CT"
   (    "OPERATION$" CHAR(2),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "XIDUSN$" NUMBER,
        "XIDSLT$" NUMBER,
        "XIDSEQ$" NUMBER,
        "RSID$" NUMBER,
        "ROW_ID$" ROWID,
        "USERNAME$" VARCHAR2(30),
        "TIMESTAMP$" DATE,
        "TARGET_COLMAP$" RAW(128),
        "DDLOPER$" NUMBER,
        "DDLDESC$" CLOB,
        "DDLPDOBJN$" NUMBER,
        "A" NUMBER,
        "B" DATE,
        "C" VARCHAR2(20)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("DDLDESC$") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

Interesting, now the CDC change table is not partitioned.

Summary: CDC needs Java in the database, CDC does not need partitioning
but uses it if available. So if you want to avoid the usage of partitioning
you should not install or deactivate it.
Of course CDC is a warehousing feature, and warehouses probably need
partitioning for some other reason.

Have a nice day! Mathias

ASM Disk Groups / Redundancy at Diskgroup Level vs. Redundancy at Template Level. Are there differences?

Mathias Zarick — Mon, 04 May 2009 19:36:00 GMT

Hi folks,

in last days I was confrontated with some ASM specific questions and so this should be the topic I start my blogging "career" with.

I tested the following with VMWare Workstation. My "DB Server" was a Solaris box.
uname -a output: SunOS lnxzam12 5.10 Generic_137138-09 i86pc i386 i86pc.
Oracle was 10.2.0.4.0.
I tested the following three scenarios, in which each time one of the two asm disks
broke. I had iscsi and local disks, the iscsi disks broke by suspending the VM.
In all three scenarios I broke the disk when running some read write load.

Scenarios.
1. A DB on a mirrored diskgroup
2. A DB on an unprotected diskgroup
3. A DB on a mirrored diskgroup with datafiles based on unprotected asm templates

Scenario 1.
a normal mirrored diskgroup.

My Diskgroup was named DG1 and created on asm disks /asm_disks/local.disk1 and /asm_disks/iscsi.lun1
which are symbolic links:
ls -la /asm_disks/local.disk1 /asm_disks/iscsi.lun1
lrwxrwxrwx   1 root     root          18 May 4 11:33 /asm_disks/iscsi.lun1 -> /dev/rdsk/c4t7d0s0
lrwxrwxrwx   1 root     root          18 May 1 14:49 /asm_disks/local.disk1 -> /dev/rdsk/c2t0d0s0

create diskgroup dg1 disk '/asm_disks/local.disk1','/asm_disks/iscsi.lun1';
Diskgroup created.

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       1987
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       1987

asmcmd lsdg Output:

ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N      N         512   4096 1048576      4076     3974                0            1987              0 DG1/

No asm templates were manipulated or added. So everything default:

select dg.name group_name, t.name template_name, dg.type, t.redundancy, t.stripe
from v$asm_diskgroup dg, v$asm_template t
where dg.group_number = t.group_number and dg.name='DG1';

GROUP_NAME TEMPLATE_NAME   TYPE   REDUND STRIPE
----------- --------------- ------ ------ ------
DG1         PARAMETERFILE   NORMAL MIRROR COARSE
DG1         DUMPSET         NORMAL MIRROR COARSE
DG1         CONTROLFILE     NORMAL HIGH   FINE
DG1         ARCHIVELOG      NORMAL MIRROR COARSE
DG1         ONLINELOG       NORMAL MIRROR FINE
DG1         DATAFILE        NORMAL MIRROR COARSE
DG1         DATAGUARDCONFIG NORMAL MIRROR COARSE
DG1         BACKUPSET       NORMAL MIRROR COARSE
DG1         AUTOBACKUP      NORMAL MIRROR COARSE
DG1         XTRANSPORT      NORMAL MIRROR COARSE
DG1         CHANGETRACKING NORMAL MIRROR COARSE
DG1         FLASHBACK       NORMAL MIRROR FINE
DG1         TEMPFILE        NORMAL MIRROR COARSE

13 rows selected.

My DB named SATURN resided on DG1, all controlfile, datafiles, tempfiles and online redo logs. To produce a little read and write load on the database, I invalidated all packages with utlirp.sql and and started a recompile with utlrp.sql for them. In parallel another session was busy with some nonsense DML on a table. Then i crashed the iscsi LUN, and watched messages file as well as the ASM and SATURN alert logs.

entries in /var/adm/messages:
May 4 15:49:15 lnxzam12 iscsi: [ID 286457 kern.notice] NOTICE: iscsi connection(12) unable to connect to target iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 (errno:145)
May 4 15:52:07 lnxzam12 last message repeated 6 times
May 4 15:52:07 lnxzam12 iscsi: [ID 328943 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 offline
May 4 15:52:07 lnxzam12 scsi: [ID 107833 kern.warning] WARNING: /iscsi/disk@0000iqn.2009-05.com.trivadis%3Astorage.oel5.lnxzam04FFFF,1 (sd7):
May 4 15:52:07 lnxzam12        transport rejected fatal error
May 4 15:52:08 lnxzam12 scsi: [ID 107833 kern.warning] WARNING: /iscsi/disk@0000iqn.2009-05.com.trivadis%3Astorage.oel5.lnxzam04FFFF,1 (sd7):
May 4 15:52:08 lnxzam12        drive offline

the highlights from the alert log of the database instance:

Mon May 4 15:52:07 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_j000_5402.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 478944
Additional information: -1
Mon May 4 15:52:07 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3

alert log of asm instance:
Mon May 4 15:52:07 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 4088
Additional information: -1
Mon May 4 15:52:08 2009
NOTE: PST update: grp = 1
Mon May 4 15:52:08 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May 4 15:52:08 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 15:52:08 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3
Mon May 4 15:52:08 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3
Mon May 4 15:52:08 2009
WARNING: offlining disk 1.4043124392 (DG1_0001) with mask 0x3
Mon May 4 15:52:18 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May 4 15:52:18 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May 4 15:52:18 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May 4 15:52:18 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_5415.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May 4 15:52:18 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_5416.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May 4 15:52:18 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_5412.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May 4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May 4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May 4 15:52:18 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
Mon May 4 15:52:18 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
Mon May 4 15:52:18 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
NOTE: cache closing disk 1 of grp 1: DG1_0001
NOTE: cache closing disk 1 of grp 1: DG1_0001
NOTE: cache closing disk 1 of grp 1: DG1_0001
Mon May 4 15:52:21 2009
SUCCESS: refreshed membership for 1/0x8b6ddef8 (DG1)
Mon May 4 15:52:23 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May 4 15:52:23 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May 4 15:53:14 2009
WARNING: PST-initiated drop disk 1(2339233528).1(4043124392) (DG1_0001)
Mon May 4 15:53:14 2009
NOTE: PST update: grp = 1
Mon May 4 15:53:14 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 15:53:14 2009
NOTE: requesting all-instance membership refresh for group=1
Mon May 4 15:53:14 2009
NOTE: membership refresh pending for group 1/0x8b6ddef8 (DG1)
SUCCESS: refreshed membership for 1/0x8b6ddef8 (DG1)
Mon May 4 15:53:16 2009
SUCCESS: PST-initiated disk drop completed
Mon May 4 15:53:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May 4 15:53:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_2856.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May 4 15:53:19 2009
NOTE: starting rebalance of group 1/0x8b6ddef8 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=19, OS id=5420
Mon May 4 15:53:23 2009
NOTE: assigning ARB0 to group 1/0x8b6ddef8 (DG1)
Mon May 4 15:53:34 2009
NOTE: stopping process ARB0
Mon May 4 15:53:35 2009
SUCCESS: rebalance completed for group 1/0x8b6ddef8 (DG1)
Mon May 4 15:53:35 2009
SUCCESS: rebalance completed for group 1/0x8b6ddef8 (DG1)
NOTE: PST update: grp = 1
Mon May 4 15:53:36 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 15:53:36 2009
WARNING: offline disk number 1 has references (2009 AUs)
NOTE: PST update: grp = 1
Mon May 4 15:53:36 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)

The recompile and dml sessions experienced no problems. The database instance stayed up.

here again the query for the asm diskgroup, which hangs for a while while trying to read from the broken disk:

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038         27
DG1_0001         DG1                                       2038         27

What is DG1_0001's state?

select state, mode_status, mount_status from v$asm_disk where name='DG1_0001';

STATE    MODE_STATUS   MOUNT_STATUS
-------- ------------- -------------
HUNG     OFFLINE       MISSING

asmcmd lsdg Output:
ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N      N         512   4096 1048576      2038       27                0              13              1 DG1/

This means ASM has offlined the disk and tried to drop it. Obviously it could not drop as it was last disk in its failure group. Afterwards I resumed the iscsi target server and let /asm_disks/iscsi.lun1 come back.

/var/adm/messages:
May 4 16:17:11 lnxzam12 iscsi: [ID 240218 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 online

So now I need to repair the diskgroup, but how?
A naive adding back gave an error:

SQL> ALTER DISKGROUP DG1 ADD DISK '/asm_disks/iscsi.lun1';
ALTER DISKGROUP DG1 ADD DISK '/asm_disks/iscsi.lun1'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15033: disk '/asm_disks/iscsi.lun1' belongs to diskgroup "DG1"

so I cleared the disk before I was able to add it back
dd if=/dev/zero of=/asm_disks/iscsi.lun1 bs=8192 count=100

ALTER DISKGROUP DG1 ADD DISK '/asm_disks/iscsi.lun1';
Diskgroup altered.

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0002         DG1         /asm_disks/iscsi.lun1         2038       1922
DG1_0000         DG1         /asm_disks/local.disk1        2038         27
DG1_0001         DG1                                       2038        140

rebalancing actually mirroring starts as also shown in asm instances alert log

Mon May 4 16:21:24 2009
NOTE: starting rebalance of group 1/0x8b6ddef8 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=16, OS id=5497

after a while both disks are in sync again as we can see in alert log and by the free_mb column:

Mon May 4 16:26:14 2009
SUCCESS: rebalance completed for group 1/0x8b6ddef8 (DG1)

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0002         DG1         /asm_disks/iscsi.lun1         2038         27
DG1_0000         DG1         /asm_disks/local.disk1        2038         27
DG1_0001         DG1                                       2038       2033

Notice: With 11g there is a feature called fast mirror resync, which makes it possible to incrementally update the failed disk with all needed and changed data to bring it in sync again.

Scenario 2.
an unprotected diskgroup.

For this test, the diskgroup was dismounted, asm disks cleared and diskgroup recreated.

create diskgroup dg1 external redundancy disk '/asm_disks/local.disk1','/asm_disks/iscsi.lun1';

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       2011
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       2013

asmcmd lsdg Output:

ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N      N         512   4096 1048576      4076     4024                0            4024              0 DG1/

Notice: We have twice the storage capacity as in first scenario, as we do not mirror any more.

No asm templates were manipulated or added.
Notice: Anyway it is not possible to "upgrade" a template in a unprotected diskgroup to mirrored.

ALTER DISKGROUP DG1 ALTER TEMPLATE DATAFILE ATTRIBUTES (MIRROR);
gives ORA-15067: command or option incompatible with diskgroup redundancy

So everything default:

select dg.name group_name, t.name template_name, dg.type, t.redundancy, t.stripe
from v$asm_diskgroup dg, v$asm_template t
where dg.group_number = t.group_number and dg.name='DG1';

GROUP_NAME TEMPLATE_NAME   TYPE   REDUND STRIPE
----------- --------------- ------ ------ ------
DG1         PARAMETERFILE   EXTERN UNPROT COARSE
DG1         DUMPSET         EXTERN UNPROT COARSE
DG1         CONTROLFILE     EXTERN UNPROT FINE
DG1         ARCHIVELOG      EXTERN UNPROT COARSE
DG1         ONLINELOG       EXTERN UNPROT FINE
DG1         DATAFILE        EXTERN UNPROT COARSE
DG1         DATAGUARDCONFIG EXTERN UNPROT COARSE
DG1         BACKUPSET       EXTERN UNPROT COARSE
DG1         AUTOBACKUP      EXTERN UNPROT COARSE
DG1         XTRANSPORT      EXTERN UNPROT COARSE
DG1         CHANGETRACKING EXTERN UNPROT COARSE
DG1         FLASHBACK       EXTERN UNPROT FINE
DG1         TEMPFILE        EXTERN UNPROT COARSE

13 rows selected.

My DB named SATURN again resided on DG1. Same load: invalidation with utlirp.sql and utlrp.sql as well as the dml session. Then I crashed the iscsi LUN by suspending the VM.

of course we get similar entries in /var/adm/messages as in first scenario:
May 4 17:14:25 lnxzam12 iscsi: [ID 286457 kern.notice] NOTICE: iscsi connection(12) unable to connect to target iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 (errno:145)
May 4 17:17:17 lnxzam12 last message repeated 6 times
May 4 17:17:17 lnxzam12 iscsi: [ID 328943 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 offline
May 4 17:17:17 lnxzam12 scsi: [ID 107833 kern.warning] WARNING: /iscsi/disk@0000iqn.2009-05.com.trivadis%3Astorage.oel5.lnxzam04FFFF,1 (sd7):
May 4 17:17:17 lnxzam12        transport rejected fatal error
May 4 17:17:40 lnxzam12 iscsi: [ID 286457 kern.notice] NOTICE: iscsi connection(12) unable to connect to target iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 (errno:145)
May 4 17:19:04 lnxzam12 last message repeated 1 time

database instance alert log:
Mon May 4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_ckpt_6089.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 58496
Additional information: -1
Mon May 4 17:17:17 2009
KCF: write/open error block=0x1073 online=1
     file=4 +DG1/saturn_site1/datafile/users.265.685990577
     error=27063 txt: ''
Mon May 4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_ckpt_6089.trc:
ORA-00202: control file: '+DG1/saturn_site1/controlfile/current.256.685990541'
ORA-15080: synchronous I/O operation to a disk failed
Mon May 4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_lgwr_6087.trc:
ORA-00345: redo log write error block 17973 count 1854
ORA-00312: online log 3 thread 1: '+DG1/saturn_site1/onlinelog/group_3.260.685990545'
ORA-27063: number of bytes read/written is incorrect
Mon May 4 17:17:17 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_lgwr_6087.trc:
ORA-00340: IO error processing online log 3 of thread 1
ORA-00345: redo log write error block 17973 count 1854
ORA-00312: online log 3 thread 1: '+DG1/saturn_site1/onlinelog/group_3.260.685990545'
ORA-27063: number of bytes read/written is incorrect
Mon May 4 17:17:17 2009
LGWR: terminating instance due to error 340
Mon May 4 17:17:18 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_ckpt_6089.trc:
ORA-00204: error in reading (block 316, # blocks 1) of control file
ORA-00202: control file: '+DG1/saturn_site1/controlfile/current.256.685990541'
ORA-15080: synchronous I/O operation to a disk failed
Mon May 4 17:17:18 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_rbal_6106.trc:
ORA-00340: IO error processing online log of thread
Instance terminated by LGWR, pid = 6087

ASM alert log:
nothing special ... hmm
Mon May 4 17:14:33 2009
NOTE: ASMB process exiting due to lack of ASM file activity

The recompile and dml sessions crashed. The database instance crashed.

Here again the query for the asm diskgroup, which hangs for a while while trying to read from the broken disk:

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       1030
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       1035

What is DG1_0001's state?

select state, mode_status, mount_status from v$asm_disk where name='DG1_0001';

STATE    MODE_STATUS   MOUNT_STATUS
-------- ------------- -------------
NORMAL   ONLINE        CACHED

asmcmd lsdg Output:
ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N      N         512   4096 1048576      2038     1030                0            1030              0 DG1/

This means ASM stays stubborn and still relies on external redundancy as we said when creating the diskgroup. This also means reading files in ASM leads to I/O errors which means database instances crash and are not startable as longs as those disks are not readable again.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4

After this test I resumed the iscsi target server and let /asm_disks/iscsi.lun1 come back.
/var/adm/messages showed
May 4 17:31:38 lnxzam12 iscsi: [ID 240218 kern.notice] NOTICE: iscsi session(11) iqn.2009-05.com.trivadis:storage.oel5.lnxzam04 online

queries from above show same output, we have a difference when using asmcmd lsdg,
there the whole storage is visible again:
ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N      N         512   4096 1048576      4076     2065                0            2065              0 DG1/

Now I could startup the database and everything was repaired again.

Scenario 3.
a mirrored diskgroup with datafiles based on unprotected asm templates

For this test, the diskgroup was dismounted, asm disks cleared and diskgroup recreated.

create diskgroup dg1 disk '/asm_disks/local.disk1','/asm_disks/iscsi.lun1';

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038       1987
DG1_0001         DG1         /asm_disks/iscsi.lun1         2038       1987

asmcmd lsdg Output:

ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N      N         512   4096 1048576      4076     3974                0            1987              0 DG1/

I manipulated the default templates to UNPROTECTED, which saves space:

ALTER DISKGROUP DG1 ALTER TEMPLATE DATAFILE ATTRIBUTES (UNPROTECTED);
ALTER DISKGROUP DG1 ALTER TEMPLATE TEMPFILE ATTRIBUTES (UNPROTECTED);

select dg.name group_name, t.name template_name, dg.type, t.redundancy, t.stripe
from v$asm_diskgroup dg, v$asm_template t
where dg.group_number = t.group_number and dg.name='DG1';

GROUP_NAME TEMPLATE_NAME   TYPE   REDUND STRIPE
----------- --------------- ------ ------ ------
DG1         PARAMETERFILE   NORMAL MIRROR COARSE
DG1         DUMPSET         NORMAL MIRROR COARSE
DG1         CONTROLFILE     NORMAL HIGH   FINE
DG1         ARCHIVELOG      NORMAL MIRROR COARSE
DG1         ONLINELOG       NORMAL MIRROR FINE
DG1         DATAFILE        NORMAL UNPROT COARSE
DG1         DATAGUARDCONFIG NORMAL MIRROR COARSE
DG1         BACKUPSET       NORMAL MIRROR COARSE
DG1         AUTOBACKUP      NORMAL MIRROR COARSE
DG1         XTRANSPORT      NORMAL MIRROR COARSE
DG1         CHANGETRACKING NORMAL MIRROR COARSE
DG1         FLASHBACK       NORMAL MIRROR FINE
DG1         TEMPFILE        NORMAL UNPROT COARSE

13 rows selected.

Same procedure as in last both scenarios:
My DB named SATURN I created again on DG1. Same load: invalidation with utlirp.sql and utlrp.sql as well as the dml session. Then I crashed the iscsi LUN by suspending the VM.

similar entries in /var/adm/messages

alert log of the database instance:

Mon May 4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May 4 20:38:46 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_lgwr_7177.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 110688
Additional information: -1
Mon May 4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May 4 20:38:56 2009
Thread 1 advanced to log sequence 58 (LGWR switch)
Current log# 1 seq# 58 mem# 0: +DG1/saturn_site1/onlinelog/group_1.258.685993517
Mon May 4 20:38:57 2009
KCF: write/open error block=0x6a24 online=1
     file=1 +DG1/saturn_site1/datafile/system.261.685993521
     error=15062 txt: ''
Mon May 4 20:38:58 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_dbw0_7175.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 1 (block # 27172)
ORA-01110: data file 1: '+DG1/saturn_site1/datafile/system.261.685993521'
ORA-15062: ASM disk is globally closed
Mon May 4 20:38:58 2009
Thread 1 cannot allocate new log, sequence 59
Checkpoint not complete
Current log# 1 seq# 58 mem# 0: +DG1/saturn_site1/onlinelog/group_1.258.685993517
Mon May 4 20:38:58 2009
DBW0: terminating instance due to error 1242
Mon May 4 20:38:58 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_smon_7181.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
Mon May 4 20:38:58 2009
Errors in file /u00/app/oracle/admin/SATURN/bdump/saturn_mman_7173.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
Instance terminated by DBW0, pid = 7175

alert log of asm instance
Mon May 4 20:38:46 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 4088
Additional information: -1
NOTE: PST update: grp = 1
Mon May 4 20:38:46 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May 4 20:38:46 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May 4 20:38:46 2009
WARNING: offlining disk 1.4041040168 (DG1_0001) with mask 0x3
Mon May 4 20:38:56 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May 4 20:38:56 2009
WARNING: kfk failed to open a disk[/asm_disks/iscsi.lun1]
Mon May 4 20:38:56 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_7420.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May 4 20:38:56 2009
Errors in file /u00/app/oracle/admin/+ASM/udump/+asm_ora_7418.trc:
ORA-15025: could not open disk '/asm_disks/iscsi.lun1'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 3
Mon May 4 20:38:56 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Mon May 4 20:38:56 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 20:38:56 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
Mon May 4 20:38:56 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 20:38:56 2009
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
NOTE: cache closing disk 1 of grp 1: DG1_0001
NOTE: cache closing disk 1 of grp 1: DG1_0001
Mon May 4 20:38:57 2009
SUCCESS: refreshed membership for 1/0x232d91d7 (DG1)
Mon May 4 20:38:58 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May 4 20:38:58 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May 4 20:40:14 2009
WARNING: PST-initiated drop disk 1(590189015).1(4041040168) (DG1_0001)
NOTE: PST update: grp = 1
Mon May 4 20:40:14 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 20:40:14 2009
NOTE: requesting all-instance membership refresh for group=1
Mon May 4 20:40:14 2009
NOTE: membership refresh pending for group 1/0x232d91d7 (DG1)
SUCCESS: refreshed membership for 1/0x232d91d7 (DG1)
Mon May 4 20:40:16 2009
SUCCESS: PST-initiated disk drop completed
Mon May 4 20:40:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2048
Additional information: -1
Mon May 4 20:40:17 2009
Errors in file /u00/app/oracle/admin/+ASM/bdump/+asm_gmon_6999.trc:
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Intel SVR4 UNIX Error: 5: I/O error
Additional information: 4
Additional information: 2056
Additional information: -1
Mon May 4 20:40:19 2009
NOTE: starting rebalance of group 1/0x232d91d7 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=13, OS id=7605
Mon May 4 20:40:19 2009
NOTE: assigning ARB0 to group 1/0x232d91d7 (DG1)
Mon May 4 20:40:20 2009
......
!!!!!950 LINES WITH CONTENTS LIKE!!!!!!!
ERROR: group 1, file 265, extent 1013: filling extent with BADFDATA
......
Mon May 4 20:41:04 2009
NOTE: stopping process ARB0
Mon May 4 20:41:04 2009
SUCCESS: rebalance completed for group 1/0x232d91d7 (DG1)
Mon May 4 20:41:04 2009
SUCCESS: rebalance completed for group 1/0x232d91d7 (DG1)
NOTE: PST update: grp = 1
Mon May 4 20:41:04 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
Mon May 4 20:41:04 2009
WARNING: offline disk number 1 has references (119 AUs)
NOTE: PST update: grp = 1
Mon May 4 20:41:04 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)

The recompile and dml sessions crashed. The database instance crashed.

Here again the query for the asm diskgroup, which again hangs for a while:

select d.name disk_name,g.name group_name,d.path,d.total_mb,d.free_mb
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number (+)
and g.name='DG1'
;

DISK_NAME        GROUP_NAME PATH                      TOTAL_MB    FREE_MB
---------------- ----------- ----------------------- ---------- ----------
DG1_0000         DG1         /asm_disks/local.disk1        2038         28
DG1_0001         DG1                                       2038       1917

What is DG1_0001's state?

select state, mode_status, mount_status from v$asm_disk where name='DG1_0001';

STATE    MODE_STATUS   MOUNT_STATUS
-------- ------------- -------------
HUNG     OFFLINE       MISSING

asmcmd lsdg Output:
ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N      N         512   4096 1048576      2038       28                0              14              1 DG1/

So what is going on here? ASM Diskgroup stayed mounted and marked some asm file extents with BADFDATA. This is obviously somewhat of a corruption in the asm file. And of course certain I/O failed which led to the instance crash. So how can we repair this situation? Lets first resume the iscsi target server and so the /asm_disks/iscsi.lun1 device. And now the iscsi disk must be synced, but there were files which we did not mirror. I do not want to just clear and add back the disk. No chance for the data on the disk? I restarted asm instance and hoped for its intelligence. Nope that did not help! Same situation! So I added another disk to the diskgroup and afterwards I should at least be able to drop the failed disk.

Mon May 4 21:03:09 2009
SQL> alter diskgroup dg1 add disk '/asm_disks/local.disk2'
Mon May 4 21:03:09 2009
WARNING: ignoring disk /asm_disks/iscsi.lun1 in deep discovery
NOTE: initializing header on grp 1 disk DG1_0002
WARNING: ignoring disk /asm_disks/iscsi.lun1 in deep discovery
NOTE: cache opening disk 2 of grp 1: DG1_0002 path:/asm_disks/local.disk2
NOTE: PST update: grp = 1
Mon May 4 21:03:10 2009
NOTE: group DG1: relocated PST to: disk 0000 (PST copy 0)
NOTE: group DG1: relocated PST to: disk 0002 (PST copy 1)
Mon May 4 21:03:10 2009
NOTE: requesting all-instance disk validation for group=1
Mon May 4 21:03:10 2009
NOTE: disk validation pending for group 1/0x30d05295 (DG1)
SUCCESS: validated disks for 1/0x30d05295 (DG1)
Mon May 4 21:03:11 2009
NOTE: PST update: grp = 1
NOTE: requesting all-instance membership refresh for group=1
Mon May 4 21:03:11 2009
NOTE: membership refresh pending for group 1/0x30d05295 (DG1)
SUCCESS: refreshed membership for 1/0x30d05295 (DG1)
Mon May 4 21:03:14 2009
NOTE: requesting all-instance membership refresh for group=1
Mon May 4 21:03:14 2009
NOTE: membership refresh pending for group 1/0x30d05295 (DG1)
SUCCESS: refreshed membership for 1/0x30d05295 (DG1)
Mon May 4 21:03:20 2009
NOTE: starting rebalance of group 1/0x30d05295 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=12, OS id=7723
Mon May 4 21:03:20 2009
NOTE: assigning ARB0 to group 1/0x30d05295 (DG1)
Mon May 4 21:03:20 2009
NOTE: F1X0 copy 2 relocating from 1:2 to 2:2
NOTE: F1X0 copy 3 relocating from 65534:4294967294 to 65534:4294967294
Mon May 4 21:05:45 2009
NOTE: stopping process ARB0
Mon May 4 21:05:48 2009
SUCCESS: rebalance completed for group 1/0x30d05295 (DG1)
Mon May 4 21:05:48 2009
SUCCESS: rebalance completed for group 1/0x30d05295 (DG1)
NOTE: PST update: grp = 1
WARNING: offline disk number 1 has references (3 AUs)
NOTE: PST update: grp = 1

Yes but now I realized that contents of disk /asm_disks/iscsi.lun1 are lost forever. No problem for all files with mirrored asm templates (redo logs, controlfiles, spfile in my case). But a big problem for datafiles and tempfiles. The latter I could manage, the first not. I have asm file extents in there with the BADFDATA marks.

I started the database instance and I saw that my bad apprehension was so so true:

SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size                  1286488 bytes
Variable Size              71306920 bytes
Database Buffers          134217728 bytes
Redo Buffers                2904064 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+DG1/saturn_site1/datafile/system.261.685993521'
ORA-01251: Unknown File Header Version read for file number 1

So in production this would mean full database restore.

Summary:

I have tested 3 scenarios:
1. A DB on a mirrored diskgroup
2. A DB on an unprotected diskgroup
3. A DB on a mirrored diskgroup with datafiles based on unprotected asm templates

I brought load on the database and simulated a crash of one out of two asm disks, which can be compared with a partial LUN failure in production systems.

In first scenario, database instance stayed available as expected. Redundancy could be restored later on. In second scenario, the database instance crashed, but could be started up again without problems after "repairing" the failed LUN. In the third scenario, ASM marked file extents with a magic "BADFDATA" entry and corrupted the files that were not mirrored. All data was lost.

So what can we learn for productive systems:
UNPROTECTED asm templates in diskgroups with normal mirrored redundancy can lead to data loss. It is not a good idea to implement diskgroups like this. If you cannot afford mirroring the datafiles use external redundancy and mirror controlfiles, redo logs by multiplexing on the upper layer. Controlfiles can be mirrored as usual with multiple entries in init.ora parameter control_files. Redo log files are mirrored by defining multiple members for each group.

Have fun and take care Mathias