?
Solved

Oracle 10g Initialization parameter question:- log_archive_format

Posted on 2006-04-11
4
Medium Priority
?
6,166 Views
Last Modified: 2012-08-13
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
Comment
Question by:ballioballi
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16425391
1) no
2) no (but you can have 2 destinations)
0
 
LVL 2

Expert Comment

by:virtual_void
ID: 16431876
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
 

Author Comment

by:ballioballi
ID: 16434719
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 16434768
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question