Change in Oracle Parameters and Log file size

[Former Member]

Hello All,

We have scheduled DB Check job and the log file showed few errors and warnings in the oracle parameter that needs to be corrected. We have also gone through the SAP Note #830576 – Oracle Parameter Configuration to change these parameters accordingly. However we need few clarifications on the same.

1.Can we change these parameters directly in init<SID>.ora file or only in SP file. If yes can we edit the same and change it or do we need to change it using BR tools.

2.We have tried to change few parameters using DB26 tcode. But it prompts for maintaining the connection variables in DBCO tcode. We try to make change only in default database but it prompts for connection variables.

Also we get check point error. As per note 309526 can we create the new log file with 100MB size and drop the existing one. Or are there any other considerations that we need to follow for the size of log file and creating new log file. Kindly advise on this. Our Environment is as follows.

OS: Windows 2003 Server

DB: Oracle 10g

regards,

Madhu

[Former Member · Accepted solution]

Hi,

Madhu, We can change oracle parameters at both the levels that is init<SID> as well as SPFILE level.

1. If you do the changes at init<SID> level then you have to generate the SPFILE again and then database has to be restarted for the parameters to take effect.

If you make the changes in SPFILE then the parameters will take effect depending on the parameter type whether it is dynamic or static. You also need to generate the PFILE i.e init<SID>.ora

2. If possible do not change the oracle parameters using the tcode. I would say it would be better if you do it via the database and it would be much easier.

3. Well its always good to have a larger redo log size. But only one thing to keep in mind is that once you change the size of the redolog the size of the archive log also changes although the number of files will decrease.

Apart from that there wont be any issues.

Regards,

Suhas

[former_member204746]

to check if you are using an SPFILE:

show parameter SPFILE

if result is empty, this means that you must change initSID.ora

if you have a result, you must change SPFILE with commands such as:

Alter system set db_2k_cache_size=100m SCOPE=both;

if using an SPFILE, the next time you uise any BRTOOLS such as BRSPACE or BRBACKUP< it will generate an initSID.ora file for you. you should never edit it as it will be overwritetn thee next time you run BRTOOLS.

Good luck.

[Former Member]

Hi,

First make sure that how the instance has been started ,ie using PFILE or SPFILE?

SQL> select count(*) from v$spparameter where value is not null;

If the result of this query is zero, the instance was started using a PFILE.

If the result is a non-zero value, the instance was started using an SPFILE.

Based on this you can do the changes,if its through SPFILE do it through the command line or if its PFILE edit the init file.

You can increase the log file size,but the consideration is that if you have a smaller log file size the log switching happens very frequently(based on the transactions).

Regards,

sam