Re: ASM Aliases - Do I Care About Matching When I Catalog?

From: Kenny Payton <k3nnyp_at_gmail.com>
Date: Tue, 11 Aug 2015 12:09:01 -0400
Message-ID: <CAEidWqM=K56MfVKv_hfr7SXskSGDCkKt2UG4GG+tg=Hp+YGZGQ_at_mail.gmail.com>

If you catalog a file with RMAN it will read the file header and know which datafile it is and verity that it belongs to the current database. The switch datafile to copy command doesn't require a file location. So if file 100 is a missing file, and you catalog the actual file, the "switch datafile 100 to copy;" command should know the location and do the switch. I'm sure there is an RMAN list command that will list copies of datafiles but don't know it off the top of my head.

Good luck if you're forced into heading down this route.

Kenny

On Tue, Aug 11, 2015 at 11:21 AM, David Barbour <david.barbour1_at_gmail.com> wrote:

> If I proceed with cataloging the datafiles using RMAN and there's no way
> to tell which ASM datafile relates specifically to the database alias -
> does it really matter? As long as I have all the ASM OMF datafiles that
> comprise the tablespace, and switch the missing database datafile file_ids
> to one of the OMF files, I'm thinking that if they're all associated with a
> file, the numbering doesn't matter?
>
> On Mon, Aug 10, 2015 at 8:52 PM, David Barbour <david.barbour1_at_gmail.com>
> wrote:
>
>> 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 - 18:09:01 CEST