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

Database Cloning

1.1 Purpose
This Oracle clone procedure can be use to quickly migrate a system from one Windows server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy an Oracle database.

1.2 Scope
The document covers Database related information

1.3 Audience
database administrator prepares this document for so and so




2 CLONING PROCEDURE

PROD system, go into SQL*plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”.

This will create a create controlfile text in the udump directory

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

SQL>show parameter user_dump_test

.TRC file will be under the directory that is displayed by the above command

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MUAT" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORADATA\MUAT\REDO01.LOG’ SIZE 100M,
GROUP 2 'D:\ORADATA\MUAT\REDO02.LOG’ SIZE 100M,
GROUP 3 'D:\ORADATA\MUAT\REDO03.LOG’ SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORADATA\MUAT\SYSTEM01.DBF',
'D:\ORADATA\MUAT\SYSTEM02.DBF',
'D:\ORADATA\MUAT\DRSYS01.DBF',
'D:\ORADATA\MUAT\INDX01.DBF',
'D:\ORADATA\MUAT\TOOLS01.DBF',
'D:\ORADATA\MUAT\USERS01.DBF',
'D:\ORADATA\MUAT\USER_DATA.DBF',
'D:\ORADATA\MUAT\USER_INDEX.DBF',
'D:\ORADATA\MUAT\ROLLBACK_DATA.ORA',
'D:\ORADATA\MUAT\USER_INDEX2.DBF',
'D:\ORADATA\MUAT\USER_DATA2.DBF',
'D:\ORADATA\MUAT\RMAN01.DBF',
'D:\ORADATA\MUAT\USER_DATA3.DBF',
'D:\ORADATA\MUAT\USER_INDEX3.DBF',
'D:\ORADATA\MUAT\USER_DATA4.DBF',
'D:\ORADATA\MUAT\USER_INDEX4.DBF',
'D:\ORADATA\MUAT\USER_INDEX5.DBF',
'D:\ORADATA\MUAT\USER_DATA5.DBF',
'D:\ORADATA\MUAT\USERDATA\USER_DATA.DBF',
'D:\ORADATA\MUAT\USER_INDEX6.DBF'
CHARACTER SET UTF8
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# No tempfile entries found to add.


This will be the script used to recreate the control file in the Target Machine .



Shutdown the OLD Database.

SQL>SHUTDOWN IMMEDIATE


Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
Files to be copied
Datafiles
Controlfiles (For the safer side)
Redolog File
Init.ora File
PWD file




Copy and Edit the Control file – Using the output syntax from 2.2, modify the controlfile creation script by changing the following:Old: CREATE CONTROLFILE REUSE DATABASE "MUAT" NORESETLOGS
New: CREATE CONTROLFILE REUSE DATABASE "MUATDEV" NORESETLOGS

Note: The above step is needed only if we are going to change the Instance Name.



Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored# backups, or if the last shutdown was not normal or immediate.RECOVER DATABASE# Database can now be opened normally.ALTER DATABASE OPEN;


Copy the files to any directory and edit the control file where the file has been moved physically. This is needed while starting the DB or else you will get ora- error stating missing file.
Save as Create_Control.sql.Old:
DATAFILE'D:\oradata\muat\system01.dbf','D:\oradata\muat\mydatabase.dbf'


New:
DATAFILE'G: \oradata\muat\system01.dbf','G: \oradata\muat\mydatabase.dbf’


Start the database in nomount state and recreate the control file created from the earlier script in 2.2.This is will create the control file and to opened.
SQL>Startup mount
SQL>@create_controlfile.sql

SQL>Alter database open

Put the Database in archivelog mode


This will help us to recover the Database if a file has been corrupted. Since the Database runs in ARCHIVELOG MODE we can recover in any kind of situation.




Hope this helps a bit