It has been kind of an adventure as this is my first attempt to set up a RAC on this OS.
Moreover it was decided to try out all the state-of-the-art features that Oracle has charged version 10R2 with, that is ASM, OMF, block change tracking, flashback recovery area and so forth.
Setting up the flashback recovery area has not been hasslefree, as i've been hitting an annoying problem until i found a metalink note explaining the issue.
I was trying to set DB_RECOVERY_FILE_DEST to '+ARCHLOG/ORCL1' using the following command:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+ARCHLOG/ORCL1'
SCOPE=BOTH SID='*';
But all i was getting was this "self-explanatory" error message:
ORA-02097: parameter cannot be modified becauseSo i looked up both codes in the error message book with little success, the explanation was too generic.
specified value is invalid
ORA-01261: Parameter db_recovery_file_dest
destination string cannot be
translated
At first i thought i had a problem with the size of the area, but changing DB_RECOVERY_FILE_DEST_SIZE didn't fix the problem.
Then i thought the problem was in the SCOPE=BOTH clause, so i changed it to SCOPE=SPFILE;
This was an extremely bad idea indeed.
This time the ALTER SYSTEM command went through without complaints, but troubles started soon after bouncing the db.
The instances won't come up again and error ORA-01261 was reported when trying to startup a single instance from sqlplus.
So, i ended up with a bad SPFILE on an ASM diskgroup, that you cannot change unless the db is running!
Fortunately i had a good copy of the SPFILE before the last changes (this is a really good practice!), so i could reopen the instance by specifying the PFILE instead.
startup pfile=location_of_a_good_init.oraOnce the instance was up and running, i overwrote the corrupt SPFILE by issuing:
CREATE SPFILE='+DBFILES/spfile.ora'
FROM PFILE=location_of_a_good_init.ora
At this point i bounced the db again and all was back to normal.
So, now i was back to the original problem.
I decided to search metalink for some solution and it was fairly easy to find the real reason of the problem in document ID 329899.1
Under ASM you cannot specify a subfolder for the flash recovery area, you can only specify a diskgroup.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+ARCHLOG'
SCOPE=BOTH SID='*';
and voila', the flashback recovery area was finally set.
7 comments:
I was having the error, after reading your blog, I got it as well.
Thanks
dba_abid@yahoo.com
Excellent, that's exactly why this blog exists Abid.
bye
I liked your war story! especially since I'm too chicken to try ASM myself.
This coincided nicely with the fact that I just opened a blog, and I decided to write my very first post about this.
http://prodlife.wordpress.com/2007/06/15/why-im-still-not-using-asm/
As you can probably see, I'm very very new to this blog thing, so if you have any suggestions on what I should do different, I'd really appreciate it. Even scathing criticism will be welcome at this point.
great stuff! you made my dr test successful
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\oradata\flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> host mkdir E:\oradata\flash_recovery_area2
SQL> alter system set db_recovery_file_dest='E:\oradata\flash_recovery_area2' scope=spfile;
System altered.
Ehm, may be i'm getting too old for this job, i don't see the point, could you elaborate a bit more this concept?
Thank you
And it gets even better!
Download.oracle.com:
"LOG_ARCHIVE_FORMAT is ignored if a disk group is specified for LOG_ARCHIVE_DEST (for example, LOG_ARCHIVE_DEST = +dgroup1)."
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/storeman.htm)
Put this together with what you demonstrate above, and the result is that once you start using ASM, you are forced to let your archive logs have Oracle-managed filenames like "thread_2_seq_2.274.761232151". You can no longer use a template like _%r_%s_%t.arc. You can specify it, but it is ignored. No longer any place in the name for your SID so it is human-identifiable for instance, which is handy for instance if you are moving archive log files around prior to a recover operation.
Post a Comment