We're here to help!

 Create a standalone non-ASM standby for a 2-node RAC, ASM primary. When I try to create the standby it is still trying to create standby using ASM Aliases and therefore fails..

Problem Details

ORA-19504: failed to create file "+DATADG/prod/datafile/dotcom01"
ORA-17502: ksfdcre:3 Failed to create file +DATADG/prod/datafile/dotcom01
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Servic

When trying to create a standalone non-ASM standby for a 2-node RAC, ASM primary. When I try to create the standby it is still trying to create standby using ASM Aliases and therefore fails.

Tracefile message:

channel C_DBVISIT: starting datafile backup set restore
channel C_DBVISIT: specifying datafile(s) to restore from backup set
channel C_DBVISIT: restoring datafile 00012 to +DATADG/prod/datafile/dotcom01
channel C_DBVISIT: reading from backup piece E:\DEEPAK_BACKUP_STANDBY\DBV_PROD2_CSD_DBF_12_HOPMBO2F_1_1.RMAN
channel C_DBVISIT: ORA-19870: error while restoring backup piece E:\DEEPAK_BACKUP_STANDBY\DBV_PROD2_CSD_DBF_12_HOPMBO2F_1_1.RMAN
ORA-19504: failed to create file "+DATADG/prod/datafile/dotcom01"
ORA-17502: ksfdcre:3 Failed to create file +DATADG/prod/datafile/dotcom01
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Servic
failover to previous backup

 Solution

Looks like when using ASM Aliases . It appears its  hitting an issue which was resolved in the 7.0.22 release.

From the Change Log:

http://www.dbvisit.com/products/standby_latest_changes/

7.0.22 (9 September 2014) Fixes:

1. Fix a Create Standby Database (CSD) issue with creating a file system based standby database datafile when a primary datafile is an ASM alias.

The files in question are:

+DATADG/prod/datafile/dotcom01

+DATADG/prod/datafile/dotcom02

From the trace file we see the following:

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: ------------------------------------------------------------------------------------

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/system.256.774108411;1; 8103395328;SYSTEM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/sysaux.257.774108411;2; 2147483648;SYSAUX;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/undotbs1.258.774108411;3; 18519949312;UNDOTBS1;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/users.259.774108411;4; 1073741824;USERS;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/example.269.774108535;5; 328335360;EXAMPLE;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/undotbs2.270.774108705;6; 4836032512;UNDOTBS2;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom.638.775197229;7; 34359721984;DOTCOM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/itsscheduler.639.775197711;8; 536870912;ITSSCHEDULER;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom.5700.817468979;9; 34359721984;DOTCOM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom.5245.817469009;10; 34359721984;DOTCOM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom.5698.817469021;11; 34359721984;DOTCOM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat:

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: HHH

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: -----------------------------------------------------------------------------------

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom01;12; 34359721984;DOTCOM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom02;13; 11811160064;DOTCOM;

20141030 09:22:14 Standby::Auxiliary::trace-> main::cmn_cat: DATFILE;+DATADG/prod/datafile/dotcom.13078.823248777;14; 104857600;DOTCOM;

Explanation

When using ASM, and adding datafiles or creating tablespaces, it is best to not explicitly specify datafile names when adding them.

Example

Adding a datafile to tablespace prod_data:

SQL> alter tablespace prod_data add datafile '+DATA/dev/datafile/prod_data_02' size 10M;

Instead of the above, the following should be done:

SQL> alter tablespace prod_data add datafile '+DATA' size 10M;

Note: The file path and name is not specified, Oracle will automatically create it for you.

Now the following happens:

You add a datafile:

SQL> alter tablespace prod_data add datafile '+DATA/dev/datafile/prod_data_02' size 10M;

Now look at ASM:

ASMCMD> ls -l +DATA/dev/datafile/

Type Redund Striped Time Sys Name

DATAFILE UNPROT COARSE OCT 31 13:00:00 Y DATA.260.858702465

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y DATA.269.858702497

DATAFILE UNPROT COARSE OCT 31 16:00:00 Y PROD_DATA.262.862417457

DATAFILE UNPROT COARSE OCT 31 16:00:00 Y PROD_DATA.285.862417525

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y SYSAUX.275.858702333

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y SYSTEM.276.858702273

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y UNDOTBS1.274.858702383

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y UNDOTBS2.272.858702445

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y UNDOTBS3.263.858702455

DATAFILE UNPROT COARSE OCT 30 20:00:00 Y USERS.273.858702403

N prod_data_02 => +DATA/DEV/DATAFILE/PROD_DATA.285.862417525

As can be seen above, Oracle actually creates an OMF file +DATA/DEV/DATAFILE/PROD_DATA.285.862417525 but then there is an alias (link) to this file so prod_data_2 now points to the file.

Ideally this file should not be created in this way but during the create statement. Just specify the disk group and Oracle will do the rest.

We introduced a change in 7.0.22 to cater for this (Alias files).

References

Other Oracle references on ASM Aliases can be found at:

• 11g:

  http://docs.oracle.com/cd/B28359_01/server.111/b31107/asmfiles.htm#OSTMG139

• 12c:

  http://docs.oracle.com/database/121/OSTMG/GUID-BBCDD96B-B9D5-49D9-AE38-A6C3C097DD54.htm#OSTMG139

Donna Zehl November 01, 2014 04:26