RE: Strange problem with controlfile
- From: "CRISLER, JON A" <JC1706@xxxxxxx>
- To: Andy Klock <andy@xxxxxxxxxxxxxxx>, Sreejith S Nair <sreejithsna@xxxxxxxxx>
- Date: Thu, 29 Dec 2011 20:14:54 +0000
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@xxxxxxxxx [mailto:andyklock@xxxxxxxxx] On Behalf Of Andy Klock
Sent: Thursday, December 29, 2011 3:11 PM
To: Sreejith S Nair
Cc: CRISLER, JON A; Sreejith.Sreekantan@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx<mailto:andy@xxxxxxxxxxxxxxx>> wrote:
On Thu, Dec 29, 2011 at 12:05 PM, Sreejith S Nair
<sreejithsna@xxxxxxxxx<mailto:sreejithsna@xxxxxxxxx>> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
