How To : logfile(redo) resize
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How To : logfile(redo) resize

  1. #1
    Join Date
    Feb 2001
    Posts
    203
    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]
    sree

  2. #2
    Join Date
    Feb 2001
    Location
    Montreal
    Posts
    29
    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]
    Hudson

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    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.

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

    Rajeev Suri

  5. #5
    Join Date
    Feb 2001
    Posts
    203
    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?
    sree

  6. #6
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    yes, these are the steps. The only thing to watch out is that you are not dropping an ACTIVE or CURRENT log group.

    - Rajeev


    Rajeev Suri

  7. #7
    Join Date
    Feb 2001
    Posts
    203
    Hi rsuri & jdorlon ,

    Thanks. It worked.


    sree

  8. #8
    Join Date
    May 2001
    Location
    Melbourne
    Posts
    5
    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



    =====================
    http://come.to/BlackBeauty
    =====================

  9. #9
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    leila,
    you are right !!!

    - Rajeev
    Rajeev Suri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width