How To : logfile(redo) resize

[sree_sri]

Hi Guys,
When i am creating the database i created logfiles(redo) size 500k. But when i am running my backup script i am getting problems with check sums. Now i want to change logfiles size from 500k to 2M. So if any body done this job before please mail me the procedure. If you send me each step it will help me a lot. Thanks




[Edited by sree_sri on 05-15-2001 at 02:02 PM]

[hudson]

set ORACLE_SID=help
d:\oracle\ora8i\bin\oradim -new -sid HELP -startmode manual -pfile "d:\oracle\ora8i\admin\help\pfile\inithelp.ora"
d:\oracle\ora8i\bin\svrmgrl @D:\temp\helprun.sql
d:\oracle\ora8i\bin\svrmgrl @D:\temp\helprun1.sql
d:\oracle\ora8i\bin\svrmgrl @D:\temp\helpalterTablespace.sql
d:\oracle\ora8i\bin\oradim -edit -sid help -startmode auto


spool d:\oracle\ora8i\admin\help\create\createdb
set echo on
connect INTERNAL/oracle
startup nomount pfile="d:\oracle\ora8i\admin\help\pfile\inithelp.ora"
CREATE DATABASE help
LOGFILE 'd:\oracle\ora8i\oradata\help\redo01.log' SIZE 1024K,
'd:\oracle\ora8i\oradata\help\redo02.log' SIZE 1024K,
'd:\oracle\ora8i\oradata\help\redo03.log' SIZE 1024K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'd:\oracle\ora8i\oradata\help\system01.dbf' SIZE 264M REUSE AUTOEXTEND ON NEXT 10240K
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1;
spool off



spool d:\oracle\ora8i\admin\help\create\createdb1
set echo on
connect INTERNAL/oracle

REM ********** ALTER SYSTEM TABLESPACE *********
ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
ALTER TABLESPACE SYSTEM
MINIMUM EXTENT 64K;

REM ********** TABLESPACE FOR ROLLBACK **********
CREATE TABLESPACE RBS DATAFILE 'd:\oracle\ora8i\oradata\help\rbs01.dbf' SIZE 520M REUSE
AUTOEXTEND ON NEXT 5120K
MINIMUM EXTENT 512K
DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);

REM ********** TABLESPACE FOR USER **********
CREATE TABLESPACE USERS DATAFILE 'd:\oracle\ora8i\oradata\help\users01.dbf' SIZE 108M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

REM ********** TABLESPACE FOR TEMPORARY **********
CREATE TABLESPACE TEMP DATAFILE 'd:\oracle\ora8i\oradata\help\temp01.dbf' SIZE 72M REUSE
AUTOEXTEND ON NEXT 640K
MINIMUM EXTENT 64K
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;

REM ********** TABLESPACE FOR Tools **********
CREATE TABLESPACE TOOLS DATAFILE 'd:\oracle\ora8i\oradata\help\tools01.dbf' SIZE 12M REUSE
AUTOEXTEND ON NEXT 320K
MINIMUM EXTENT 32K
DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

REM ********** TABLESPACE FOR INDEX **********
CREATE TABLESPACE INDX DATAFILE 'd:\oracle\ora8i\oradata\help\indx01.dbf' SIZE 58M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

CREATE PUBLIC ROLLBACK SEGMENT RBS0 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS1 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS2 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS3 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS4 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS5 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS6 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
ALTER ROLLBACK SEGMENT "RBS0" ONLINE;
ALTER ROLLBACK SEGMENT "RBS1" ONLINE;
ALTER ROLLBACK SEGMENT "RBS2" ONLINE;
ALTER ROLLBACK SEGMENT "RBS3" ONLINE;
ALTER ROLLBACK SEGMENT "RBS4" ONLINE;
ALTER ROLLBACK SEGMENT "RBS5" ONLINE;
ALTER ROLLBACK SEGMENT "RBS6" ONLINE;
alter user sys temporary tablespace TEMP;
@d:\oracle\ora8i\Rdbms\admin\catalog.sql;
@d:\oracle\ora8i\Rdbms\admin\catexp7.sql
@d:\oracle\ora8i\Rdbms\admin\catproc.sql
@d:\oracle\ora8i\Rdbms\admin\caths.sql
connect system/manager
@d:\oracle\ora8i\sqlplus\admin\pupbld.sql
connect INTERNAL/oracle
spool off


connect internal/oracle
alter user system default tablespace TOOLS;
alter user system temporary tablespace TEMP;




[Edited by uday on 05-15-2001 at 05:52 PM]

[jdorlon]

1) Create a new redo log group that has a file size of 2M
(repeat this step for the number of groups you wish to have)

2) Force a couple of log switches so that the current redo log is one of the 2M ones.

3) Drop your 500K redo log groups.

[rsuri]

1. create new redo log groups with bigger log members
2. force Log switches to make smaller logs INACTIVE
3. drop the smaller logs

- Rajeev

[sree_sri]

Hi Dorlon,jsuri,

LOGFILE
GROUP 1 (
'/hfs/oracle/disk04/oradata/AGI/redoAGI_101.log',
'/hfs/oracle/disk03/oradata/AGI/redoAGI_102.log'
) SIZE 500k,
GROUP 2 (
'/hfs/oracle/disk04/oradata/AGI/redoAGI_201.log',
'/hfs/oracle/disk03/oradata/AGI/redoAGI_202.log'
) SIZE 500k,
GROUP 3 (
'/hfs/oracle/disk04/oradata/AGI/redoAGI_301.log',
'/hfs/oracle/disk03/oradata/AGI/redoAGI_302.log'
) SIZE 500k

These are the redo logs i have in the production database. You told me to create new redolog groups,

ALTER DATABASE AGI
ADD LOGFILE
GROUP 4 (
'/hfs/oracle/disk04/oradata/AGI/redoAGI_401.log',
'/hfs/oracle/disk03/oradata/AGI/redoAGI_402.log'
) SIZE 2M,
GROUP 5 (
'/hfs/oracle/disk04/oradata/AGI/redoAGI_501.log',
'/hfs/oracle/disk03/oradata/AGI/redoAGI_502.log'
) SIZE 2M,
GROUP 6 (
'/hfs/oracle/disk04/oradata/AGI/redoAGI_601.log',
'/hfs/oracle/disk03/oradata/AGI/redoAGI_602.log'
) SIZE 2M;

Then Force a couple of log switches so that the current redo log is one of the 2M ones.


ALTER SYSTEM SWITCH LOGFILE;

Now the group 4 is current and all other 5 groups are not active
then

alter database agi
drop logfile group 1;

alter database agi
drop logfile group 2;

alter database agi
drop logfile group 3;

I am assuming that these are the steps i have to follow. This database is on production, so any other steps i have to cosider?

[rsuri]

yes, these are the steps. The only thing to watch out is that you are not dropping an ACTIVE or CURRENT log group.

- Rajeev

[sree_sri]

Hi rsuri & jdorlon ,

Thanks. It worked.

[leila]

rajeev - I don't think Oracle will let you do that...

Originally posted by rsuri
yes, these are the steps. The only thing to watch out is that you are not dropping an ACTIVE or CURRENT log group.

- Rajeev

[rsuri]

leila,
you are right !!!

- Rajeev