Oracle 10g Initialization parameter question:- log_archive_format

Can the "log_archive_format" Initialization parameter is dynamically changed or not..
By default it has taken LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

1.I want to change it to " .arc"  ----can I able to do it without shutng down the database?????

2.Or Can I have 2 Formats ....???
 One remain the same and add second format as LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

Please clarify.


ballioballiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) no
2) no (but you can have 2 destinations)
virtual_voidCommented:
1)      NO, the LOG_ARCHIVE_FORMAT parameter is not Modifiable implying that you cannot modify it in memory while the database is up.
2)      YES, however if you are ready to bounce the database you can change it to a new value, as below. My database had the default format and tehn I changed it to the format in your post.



SQL> show parameter log_archive_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      ARC%S_%R.%T

SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              62915940 bytes
Database Buffers          100663296 bytes
Redo Buffers                2945024 bytes
Database mounted.
SQL>
SQL>  alter database archivelog;

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

C:\TEMP>dir
 Volume in drive C has no label.
 Volume Serial Number is AC4D-23AB

 Directory of C:\TEMP

04/11/2006  04:56 PM    <DIR>          .
04/11/2006  04:56 PM    <DIR>          ..
04/11/2006  04:52 PM         1,270,272 ARC00012_0584382809.001
04/11/2006  04:52 PM             1,024 ARC00013_0584382809.001


SQL>  alter system set log_archive_format='arch_%t_%s_%r.arc' scope=both;
 alter system set log_archive_format='arch_%t_%s_%r.arc' scope=both
                  *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              62915940 bytes
Database Buffers          100663296 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>

C:\TEMP>dir
 Volume in drive C has no label.
 Volume Serial Number is AC4D-23AB

 Directory of C:\TEMP

04/11/2006  04:56 PM    <DIR>          .
04/11/2006  04:56 PM    <DIR>          ..
04/11/2006  04:52 PM         1,270,272 ARC00012_0584382809.001
04/11/2006  04:52 PM             1,024 ARC00013_0584382809.001
04/11/2006  04:52 PM             4,096 ARC00014_0584382809.001
04/11/2006  04:56 PM           190,976 ARCH_1_15_584382809.ARC
04/11/2006  04:56 PM             3,072 ARCH_1_16_584382809.ARC
04/11/2006  03:57 PM                55 crTbl.sql
04/11/2006  04:00 PM                74 report.sql
01/10/2006  10:33 AM                31 s.bat
04/11/2006  03:59 PM                89 test.bat
               9 File(s)      1,469,689 bytes
               2 Dir(s)  24,596,004,864 bytes free

C:\TEMP>
ballioballiAuthor Commented:
I accept your answer with this clue that there can not be 2 format of archive logs for 2 different locations .
There may be 10 locations but ONLY ONE FORMAT. for example:--

log_archive_format                   string      ARC%S_%R.%T
Guy Hengel [angelIII / a3]Billing EngineerCommented:
correct.
the reason is that the list of files generated is only kept once in the control file (ie in the RMAN catalog if online backup are performed), and hence oracle cannot allow different log_archive_format per destination.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.