RE: Strange problem with controlfile

From: CRISLER, JON A <JC1706_at_att.com>
Date: Thu, 29 Dec 2011 20:14:54 +0000
Message-ID: <9F15274DDC89C24387BE933E68BE3FD315B216_at_MISOUT7MSGUSR9D.ITServices.sbc.com>

Hey, we have all had issues we slaved over for hours, pulled out hair, kicked the dog, beat the server with a hammer, only to find out it was a stupid typo ? Right ? Or is that just me ? From: andyklock_at_gmail.com [mailto:andyklock_at_gmail.com] On Behalf Of Andy Klock Sent: Thursday, December 29, 2011 3:11 PM To: Sreejith S Nair
Cc: CRISLER, JON A; Sreejith.Sreekantan_at_ibsplc.com; oracle-l_at_freelists.org Subject: Re: Strange problem with controlfile

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<mailto:andy_at_oracledepot.com>> wrote:

On Thu, Dec 29, 2011 at 12:05 PM, Sreejith S Nair <sreejithsna_at_gmail.com<mailto: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:14:54 CST