asked on

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.

Guy Hengel [angelIII / a3]

1) no
2) no (but you can have 2 destinations)

virtual_void

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>

ballioballi

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial