Monday, June 12, 2006

the magic couple:ORA-02097 and ORA-01261

Lately i've been rather busy setting up backup and recovery test procedures on a Linux RAC.
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 because
specified value is invalid
ORA-01261: Parameter db_recovery_file_dest
destination string cannot be
translated
So i looked up both codes in the error message book with little success, the explanation was too generic.

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.ora


Once 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:

Abid Malik said...

I was having the error, after reading your blog, I got it as well.
Thanks
dba_abid@yahoo.com

Byte64 said...

Excellent, that's exactly why this blog exists Abid.

bye

Anonymous said...

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.

Anonymous said...

great stuff! you made my dr test successful

4gb micro sd karte said...

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.

Byte64 said...

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

Penny said...

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.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles