Re: Strange problem with controlfile

From: Andy Klock <andy_at_oracledepot.com>
Date: Thu, 29 Dec 2011 12:09:55 -0500
Message-ID: <CADo_RaOHpyMZONqrPK-nTBBYT5jTs+cxZ5K77==2nLFGxYApvQ_at_mail.gmail.com>

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 - 11:09:55 CST