ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?

From: David Barbour <david.barbour1_at_gmail.com>
Date: Mon, 10 Aug 2015 16:56:43 -0500
Message-ID: <CAFH+ifdUoHPHgrARjoQzbvkqyatrK-U03-MJTc7uog1W_a9jCw_at_mail.gmail.com>

May just sort of be up the proverbial creek on this. I cannot tie together any data file info from ASM to the alias registered in the database controlfile.

For instance, I know in the +S00DATA05 diskgroup, the aliases that were dropped were +S00DATA05/S00/PSAPCOEPD.DATA11 and
+S00DATA05/S00/PSAPCOEPD.DATA12. I know we've got two OMF files in the
diskgroup - +S00DATA05/S00/DATAFILE/PSAPCOEPD.373.883759173 and
+S00DATA05/S00/DATAFILE/PSAPCOEPD.358.883759173.
But how to tell which is which? I suppose I could guess. Got a 50/50 chance here.

Trying to figure out if perhaps sys_connect_by_path could be of use.

This is what the controlfile shows:

1 select file_name from dba_data_files where tablespace_name = 'PSAPCOEPD'
2* order by file_name
SQL> / FILE_NAME

+S00DATA01/psapcoepd.data01
+S00DATA01/psapcoepd.data02
+S00DATA01/psapcoepd.data03
+S00DATA01/psapcoepd.data21
+S00DATA01/psapcoepd.data26
+S00DATA02/psapcoepd.data04
+S00DATA02/psapcoepd.data10
+S00DATA02/psapcoepd.data22
+S00DATA02/psapcoepd.data27
+S00DATA02/s00/datafile/psapcoepd.791.883760245
+S00DATA03/psapcoepd.data05

FILE_NAME

+S00DATA03/psapcoepd.data06
+S00DATA03/psapcoepd.data23
+S00DATA04/psapcoepd.data07
+S00DATA04/psapcoepd.data08
+S00DATA04/psapcoepd.data09
+S00DATA05/psapcoepd.data11
+S00DATA05/psapcoepd.data12
+S00DATA06/psapcoepd.data13
+S00DATA06/psapcoepd.data14
+S00DATA06/psapcoepd.data15
+S00DATA06/s00/datafile/psapcoepd.304.883760117

FILE_NAME

+S00DATA07/psapcoepd.data16
+S00DATA07/psapcoepd.data17
+S00DATA07/psapcoepd.data18
+S00DATA07/psapcoepd.data24
+S00DATA09/psapcoepd.data19
+S00DATA09/psapcoepd.data20
+S00DATA10/psapcoepd.data25
+S00DATA10/s00/datafile/psapcoepd.307.883758635

30 rows selected.

Here's what I can find in ASM:

+S00DATA01/S00/DATAFILE/PSAPCOEPD.657.883758977

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:36:17
S00DATA01          Y


+S00DATA01/S00/DATAFILE/PSAPCOEPD.658.883759017

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:36:57
S00DATA01          Y


+S00DATA01/S00/DATAFILE/PSAPCOEPD.659.883759047

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:37:27
S00DATA01          Y


+S00DATA01/S00/DATAFILE/PSAPCOEPD.660.883759409

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:43:29
S00DATA01          Y


+S00DATA01/S00/DATAFILE/PSAPCOEPD.661.883759451

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:44:10
S00DATA01          Y


+S00DATA02/S00/DATAFILE/PSAPCOEPD.782.883759077

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:37:56
S00DATA02          Y


+S00DATA02/S00/DATAFILE/PSAPCOEPD.783.883759173

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:39:32
S00DATA02          Y


+S00DATA02/S00/DATAFILE/PSAPCOEPD.784.883759411

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:43:30
S00DATA02          Y


+S00DATA02/S00/DATAFILE/PSAPCOEPD.791.883760245

1.5032E+10 1.5034E+10           DATAFILE 30-JUN-2015 16:57:24
S00DATA02          Y


+S00DATA02/S00/DATAFILE/PSAPCOEPD.794.883760385

1.2885E+10 1.2887E+10           DATAFILE 30-JUN-2015 16:59:44
S00DATA02          Y


+S00DATA03/S00/DATAFILE/PSAPCOEPD.814.883759087

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:38:06
S00DATA03          Y


+S00DATA03/S00/DATAFILE/PSAPCOEPD.815.883759087

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:38:07
S00DATA03          Y


+S00DATA03/S00/DATAFILE/PSAPCOEPD.816.883759421

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:43:41
S00DATA03          Y


+S00DATA04/S00/DATAFILE/PSAPCOEPD.705.883759117

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:38:36
S00DATA04          Y


+S00DATA04/S00/DATAFILE/PSAPCOEPD.706.883759117

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:38:37
S00DATA04          Y


+S00DATA04/S00/DATAFILE/PSAPCOEPD.707.883759123

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:38:42
S00DATA04          Y


+S00DATA05/S00/DATAFILE/PSAPCOEPD.358.883759173

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:39:32
S00DATA05          Y


+S00DATA05/S00/DATAFILE/PSAPCOEPD.373.883759173

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:39:32
S00DATA05          Y


+S00DATA06/S00/DATAFILE/PSAPCOEPD.304.883760117

1.6106E+10 1.6108E+10           DATAFILE 30-JUN-2015 16:55:16
S00DATA06          Y


+S00DATA06/S00/DATAFILE/PSAPCOEPD.491.883759191

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:39:50
S00DATA06          Y


+S00DATA06/S00/DATAFILE/PSAPCOEPD.492.883759229

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:40:28
S00DATA06          Y


+S00DATA06/S00/DATAFILE/PSAPCOEPD.514.883759237

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:40:37
S00DATA06          Y


+S00DATA07/S00/DATAFILE/PSAPCOEPD.339.883759265

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:41:04
S00DATA07          Y


+S00DATA07/S00/DATAFILE/PSAPCOEPD.340.883759293

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:41:32
S00DATA07          Y


+S00DATA07/S00/DATAFILE/PSAPCOEPD.341.883759361

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:42:40
S00DATA07          Y


+S00DATA07/S00/DATAFILE/PSAPCOEPD.342.883759439

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:43:59
S00DATA07          Y


+S00DATA09/S00/DATAFILE/PSAPCOEPD.335.883759361

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:42:40
S00DATA09          Y


+S00DATA09/S00/DATAFILE/PSAPCOEPD.336.883759409

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:43:29
S00DATA09          Y


+S00DATA10/S00/DATAFILE/PSAPCOEPD.307.883758635

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:30:34
S00DATA10          Y


+S00DATA10/S00/DATAFILE/PSAPCOEPD.312.883759451

2.1475E+10 2.1477E+10           DATAFILE 30-JUN-2015 16:44:10
S00DATA10          Y

On Mon, Aug 10, 2015 at 12:58 PM, David Barbour <david.barbour1_at_gmail.com> wrote:

> Oracle 11.2.0.3, RHEL 6.6
>
> Several Oracle ASM aliases were removed using the ALTER DISKGROUP DROP
> ALIAS command.
>
> Now the backups on the database are failing because the controlfile is
> still using the alias name.
>
> Any suggestions?
>
> The database is running fine because the underlying OMF files are there.
> So somewhere there's a link.  I need to match the OMF ASM file with the
> database alias(es).  If I could find the link, then I could take the
> tablespace offline and run the set newname in Rman - I think.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 10 2015 - 23:56:43 CEST

This message: [ Message body ]
Next message: Nik Tek: "EXECUTE IMMEDIATE v_sql_stmt is not executing"
Previous message: Iggy Fernandez: "OT: NoCOUG Journal"
Next in thread: Andrew Kerber: "Re: ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?"
Reply: Andrew Kerber: "Re: ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message