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

From: David Barbour <david.barbour1_at_gmail.com>
Date: Mon, 10 Aug 2015 20:52:29 -0500
Message-ID: <CAFH+ifdydC3sMpi2_CiFG_5k77A-U4tRze30dSSWOsQ0fXtjAw_at_mail.gmail.com>

Got the SR in with Oracle. Copied off a control file and backed one up to trace. From looking at it, I figure this puppy is not going to start up if it gets shut down. Absolutely no way I can see to get to the old alias using sys_connect_by_path. Even looked in the recovery catalog tables to see if there was some type of parent/child relationship. Hadn't thought about the RMAN catalog. Let you know how it goes. I think I may still have the problem of identifying which ASM OMF goes with which database file_id.

Seems almost like an oversight that there wouldn't be some reference somewhere to the alias in the database. Even for those files that still have aliases, I can't find any way to see what ASM OMF file they relate to unless you use asmcmd and run ls -al.

On Mon, Aug 10, 2015 at 6:57 PM, Kenny Payton <k3nnyp_at_gmail.com> wrote:

> Andrew has probably the best answer but my first thought is that you could
> catalog the datafiles, Oracle will know what they are from the headers.
> Let’s just say for some reason you did shut the database down, or the power
> went out and you had no choice. If the control files have the aliases and
> the aliases are no longer there then they would be missing after the
> bounce. First I’d copy off one of my control files. You could then try
> cataloging the files with RMAN
>
> catalog start with '+S00DATA01’;
> catalog start with '+S00DATA02’;
> etc…
>
> Then for each of the missing files, issue a “switch datafile X to copy;”
> in rman where X is the file_id of the missing datafile. Then verify you
> are no longer missing any datafiles. If you’re confident all of the
> datafiles are now registered correctly in the controlfile then open the
> database. If not, shutdown and restore your control files from the one you
> copied off and you’re back where you started.
>
>
> Kenny
>
>
>
> On Aug 10, 2015, at 6:54 PM, Andrew Kerber <andrew.kerber_at_gmail.com>
> wrote:
>
> 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 - 03:52:29 CEST

This message: [ Message body ]
Next message: Kenny Payton: "Re: ASM Aliases - Do I Care About Matching When I Catalog?"
Previous message: Kenny Payton: "Re: ASM Aliases - Cannot find a Cross Reference - SYS_CONNECT_BY_PATH?"
In reply to: 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