Re: Strange problem with controlfile

From: Andy Klock <andy_at_oracledepot.com>
Date: Thu, 29 Dec 2011 15:10:50 -0500
Message-ID: <CADo_RaN8w-NVUSprEQm+BV=z2BTaL2=NO=0BXXYxyZnHTmMQjA_at_mail.gmail.com>

Dang and dang. I hang my head in shame while retracting my earlier response. I just reviewed my notes from when I had originally come across this same error (back in February of 2010). In my defense, I really had been believing this all this time... What I had thought was an issue with system generated aliases doesn't actually seem to be warranted. Rather, Sreejith and I both had the same typo when setting the control_files parameter.
Control files work the same way on ASM as they always did and theoretically, Sreejith's method of using asmcmd cp should have worked just fine. I just unknowingly fixed my typo last year and only did a copy and paste today.

I apologize to those I had led astray.

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     DEC 29 14:00:00  Y    current.264.765465815
                                               N

current.275.765465817.test =>
+DATA/ASM/CONTROLFILE/current.275.765465817.test.272.771158129

SQL> !grep control_files init.test
*.control_files = '+DATA/ora11gr2/controlfile/current.264.765465815, +DATA/ora11gr2/controlfile/current.275.765465817.test'

SQL> startup nomount pfile=init.test
ORA-15124: ASM file name '+DATA/ora11gr2/controlfile/current.264.765465815, +DATA/ora11gr2/controlfile/current.275.765465817.test' contains an invalid alias name

SQL> !vi init.test

SQL> !grep control_files init.test
#*.control_files = '+DATA/ora11gr2/controlfile/current.264.765465815, +DATA/ora11gr2/controlfile/current.275.765465817.test' *.control_files = '+DATA/ora11gr2/controlfile/current.264.765465815', '+DATA/ora11gr2/controlfile/current.275.765465817.test'

Notice how I also didn't wrap the control file name in single quotes.....Come on, you've done that before too right? It's just the error made more sense previous to ASM:

ORA-00205: error in identifying controlfile, check alert log for more info

SQL> startup nomount pfile=init.test
ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size                  2228200 bytes
Variable Size             339738648 bytes
Database Buffers          184549376 bytes
Redo Buffers                7946240 bytes

SQL> sho parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
control_files                        string
+DATA/ora11gr2/controlfile/cur
                                                 rent.264.765465815,

+DATA/ora1

1gr2/controlfile/current.275.7

                                                 65465817.test

SQL> alter database mount;

Database altered.

Happy New Year.

On Thu, Dec 29, 2011 at 12:09 PM, Andy Klock <andy_at_oracledepot.com> wrote:

>
> On Thu, Dec 29, 2011 at 12:05 PM, Sreejith S Nair <sreejithsna_at_gmail.com>wrote:
>
>> I have mounted the instance using pfile with one good copy o control
>> file. Database is up and running for the users. However, I do not
>> understand why oracle complains for control file when I gave startup no
>> mount. As per my understanding control file is read only when database is
>> mounted. This sounds very strange after all !
>>
>>
>> Hi Sreejith,
>
> The problem is you have set a control_file that does not have an alias
> that was system generated.
>
> The rules about nomount have changed a little in regards to ASM. Oracle
> does check if the controlfiles have a system generated alias (sometimes)
> when starting with NOMOUNT, as you experienced.
>
> -- +DATA/ora11gr2/controlfile/current.andy doesn't exist
> SQL> alter system set control_files =
> '+DATA/ora11gr2/controlfile/current.264.765465815,
> +DATA/ora11gr2/controlfile/current.andy' scope=spfile;
>
> System altered.
>
> SQL> shutdown immediate;
> ORA-01507: database not mounted
>
>
> ORACLE instance shut down.
> SQL> startup nomount;
> ORA-15124: ASM file name
> '+DATA/ora11gr2/controlfile/current.264.765465815,
> +DATA/ora11gr2/controlfile/current.andy' contains an invalid alias name
>
> Since +DATA/ora11gr2/controlfile/current.andy doesn't exist (and even if
> it did, it would need to have been system generated anyway). You can get
> around this check by referencing a non ASM controlfile at the beginning of
> the parameter values (it doesn't need to exist either, but it disables the
> alias check)
>
> SQL> !grep control init.test
> *.control_files='/oracle/andy.ctl,
> +DATA/ora11gr2/controlfile/current.264.765465815,+DATA/ora11gr2/controlfile/current.andy'
>
>
> SQL> startup nomount pfile=init.test
> ORACLE instance started.
>
> Total System Global Area 534462464 bytes
> Fixed Size 2228200 bytes
> Variable Size 339738648 bytes
> Database Buffers 184549376 bytes
> Redo Buffers 7946240 bytes
> SQL> sho parameter control_files
>
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> control_files string /oracle/andy.ctl,
> +DATA/ora11g
>
> r2/controlfile/current.264.765
>
> 465815,+DATA/ora11gr2/controlf
> ile/current.andy
> SQL> !ls /oracle/andy.ctl
> ls: /oracle/andy.ctl: No such file or directory
>
>
> Not really useful, but a useful hack in a pinch. As for the "aliases",
> these can be queried with V$ASM_ALIAS. The control files need to be system
> generated.
>
> Now I did what you did and did a copy of a controlfile with asmcmd:
>
> ASMCMD> cp current.275.765465817 current.275.765465817.test
> copying +DATA/ORA11GR2/controlfile/current.275.765465817 ->
> +DATA/ORA11GR2/controlfile/current.275.765465817.test
> ASMCMD> rm current.275.765465817
> ASMCMD> ls
> current.264.765465815
> current.275.765465817.test
>
> This file is not "system generated" but Oracle will create a system
> generated alias for me.
>
> SQL> select name , system_created from v$asm_alias where name like
> 'current%';
>
> NAME S
> ---------------------------------------------------------------------- -
> current.275.765465817.test.272.771158129 Y
> <-- notice the Y
> current.264.765465815 Y
> current.275.765465817.test N
>
> You can see this also if you "ls -l" the file I cp'd:
>
> ASMCMD> ls -l current.275.765465817.test
> Type Redund Striped Time Sys Name
> N
> current.275.765465817.test =>
> +DATA/ASM/CONTROLFILE/current.275.765465817.test.272.771158129
>
>
> I can't use current.275.765465817.test as a control_file, but I can use
> the alias current.275.765465817.test.272.771158129.
>
> SQL> !grep control_files init.test
> *.control_files='+DATA/asm/controlfile/current.275.765465817.test.272.771158129',
> '+DATA/ora11gr2/controlfile/current.264.765465815'
>
> SQL> startup nomount pfile=init.test
> ORACLE instance started.
>
> Total System Global Area 534462464 bytes
> Fixed Size 2228200 bytes
> Variable Size 339738648 bytes
> Database Buffers 184549376 bytes
> Redo Buffers 7946240 bytes
>
> All the above is sort of convoluted and should be avoided following the
> doc to restore a controlfile to the new diskgroup and let Oracle come up
> with the name.
>
> SQL> alter system set
> control_files='+DATA1/fdstg2/controlfile/current.260.676234913','+DATA2'
> scope=spfile sid='*';
>
> RMAN> restore controlfile from
> '+DATA1/fdstg2/controlfile/current.260.676234913';
>
> RMAN will tell you where on +DATA2 it placed the restored controlfile.
> Then set the control_files to this new path.
>
> SQL> alter system set
> control_files='+DATA1/fdstg2/controlfile/current.260.676234913','+DATA2/new_path...'
> scope=spfile sid='*';
>
>
> Hope this helps clear up controlfiles on ASM.
>

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

Received on Thu Dec 29 2011 - 14:10:50 CST