Re: ASM Aliases - Virtual Beer for Kenny

From: David Barbour <david.barbour1_at_gmail.com>
Date: Wed, 12 Aug 2015 12:51:07 -0500
Message-ID: <CAFH+iff0G0ZVKohF5OSKNoW+yjmux3P4rWLaSfKRdkJwhhUHsQ_at_mail.gmail.com>

Worked like a charm. I love RMAN. Thanks to all.

Example:

RMAN> catalog start with '+S00DATA05';

searching for all files that match the pattern +S00DATA05

List of Files Unknown to the Database

File Name: +s00data05/S00/DATAFILE/PSAPCOEPD.358.883759173
File Name: +s00data05/S00/DATAFILE/PSAPCOEPD.373.883759173
File Name: +s00data05/S00/DATAFILE/PSAPCOEPI.377.883759503

Do you really want to catalog the above files (enter YES or NO)? yes cataloging files...
cataloging done

List of Cataloged Files

File Name: +s00data05/S00/DATAFILE/PSAPCOEPD.358.883759173
File Name: +s00data05/S00/DATAFILE/PSAPCOEPD.373.883759173
File Name: +s00data05/S00/DATAFILE/PSAPCOEPI.377.883759503

RMAN> SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 391 - see DBWR trace file ORA-01110: data file 391: '+S00DATA05/psapcoepd.data12'

RMAN> switch datafile 391 to copy;

datafile 391 switched to datafile copy
"+S00DATA05/s00/datafile/psapcoepd.373.883759173" starting full resync of recovery catalog full resync complete

Eventually ...................

SQL> alter database open;

Database altered.

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 Wed Aug 12 2015 - 19:51:07 CEST

This message: [ Message body ]
Next message: Michael Calisi: "MySQL"
Previous message: Tim Gorman: "Re: SCN/ora_rowscn Questions"

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

Original text of this message