Re: ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Mon, 10 Aug 2015 17:54:41 -0500
Message-Id: <14131361-63E0-4249-AB39-7EFBE6CD3CF2_at_gmail.com>

Sr with oracle. Sev 1. It might not start if you shut it down.

Sent from my iPhone

> On Aug 10, 2015, at 4:56 PM, David Barbour <david.barbour1_at_gmail.com> wrote:
>
> 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 Tue Aug 11 2015 - 00:54:41 CEST

This message: [ Message body ]
Next message: Steve Baldwin: "Re: EXECUTE IMMEDIATE v_sql_stmt is not executing"
Previous message: Nik Tek: "EXECUTE IMMEDIATE v_sql_stmt is not executing"
In reply to: David Barbour: "ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?"
Next in thread: Kenny Payton: "Re: ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?"
Reply: Kenny Payton: "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