Re: Strange problem with controlfile
From: Sreejith S Nair <sreejithsna_at_gmail.com>
Date: Fri, 30 Dec 2011 07:05:18 +0530
Message-Id: <B5BA1FE0-D36B-4786-BF8C-516D42C14302_at_gmail.com>
Thank you so much Andy for demonstrating the real issue and pointing out the silly mistake in not including single quotes on each control file entry in init file. I should have figured out myself ! As you said, if it wasn't ASM it would have been easy figuring out. Thanks again and Wish all a Happy and prosperous new Year.
Date: Fri, 30 Dec 2011 07:05:18 +0530
Message-Id: <B5BA1FE0-D36B-4786-BF8C-516D42C14302_at_gmail.com>
Thank you so much Andy for demonstrating the real issue and pointing out the silly mistake in not including single quotes on each control file entry in init file. I should have figured out myself ! As you said, if it wasn't ASM it would have been easy figuring out. Thanks again and Wish all a Happy and prosperous new Year.
Sreejith
--
Sent from my iPhone
On Dec 30, 2011, at 1:40 AM, Andy Klock <andy_at_oracledepot.com> wrote:
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 - 19:35:18 CST