How to increase the Redolog File Size

Hi

I encountered some problems with the huge number archiving files being generated (ie seconds)during peak hours.
So i decided to increase the size of the redologs

The redolog size cannot be increased ,In order to do that We have to add new log groups with bigger size and drop the older smaller ones...


This is how i did it...




Procedure to Increase the REDOLOG size in ORACLE

The Redolog file size cannot be increased online it can be done by creating new Redolog groups
A bigger size and then drop the exiting old smaller log files.

Login to Database as SYSDBA

1.sys/pwd@TNS as sysdba

Note: We got 3 redolog groups in the DB with 3 members each

2.Create 3 new redolog groups with 3 members each for a group

SQL> alter database add logfile group 4
(E:\oradata\SID\redo41.log, E:\oradata\SID\redo42.log, D:\oradata\SID\redo43.log) size 100M;

Here we have multiplexed the group member for data protection, which is already in place

SQL> alter database add logfile group 5
(E:\oradata\SID\redo51.log, E:\oradata\SID\redo52.log, D:\oradata\SID\redo53.log) size 100M;

SQL> alter database add logfile group 6
(E:\oradata\SID\redo61.log, E:\oradata\SID\redo62.log, D:\oradata\SID\redo63.log) size 100M;


Now the bigger redolog files are created.

3.Query from the views and make sure that status of any of the group is not ‘current’

SQL>select group#, status from v$log;


4.If any group found to be ‘current’ do the following

SQL>alter system switch logfile;
Repeat this step and make sure nothing is current.

Once all the group become inactive we can drop the existing older smaller files.




5.Drop the log files

SQL>alter database drop logfile group 1;
SQL>alter database drop logfile group 2;
SQL>alter database drop logfile group 3;

6.Query the status of the log files.

7.Backup the DB.

8.Delete the files physically from the DB


That’s it..


Regards
Elamaran

Prevent password change

Normally the Passwords are very sensitive and should be protected and prevented from changing by the users.It is trivial but this is what i found from the net and how i deployed in oracle


REM CONNECT AS SYSDBA
CREATE OR REPLACE FUNCTION verify_function_pwd_nochange (username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
RETURN boolean
ISBEGIN
raise_application_error(-20900, 'SORRY, PASSWORD CANNOT BE CHANGED') ;
END ;
/
drop profile pass_profile cascade;

create profile pass_profile limitfailed_login_attempts unlimited;

alter profile pass_profile limitpassword_verify_function verify_function_pwd_nochange;

alter user system profile pass_profile;

Once the above command is executed the SYSTEM user password cannot be changed..

Drawback

but if you have a user who has dba privilege then he can drop the profile


Comments are welcome
Maran