• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6202
  • Last Modified:

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.


0
ballioballi
Asked:
ballioballi
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) no
2) no (but you can have 2 destinations)
0
 
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>
0
 
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now