Home » Posts tagged 'redo log'

Tag Archives: redo log

Oracle 9i : Some of limitation on Oracle 9i

February 23, 2011 8:21 am / 1 Comment on Oracle 9i : Some of limitation on Oracle 9i


Physical Database Limits

Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block size
Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment. 2 blocks
Maximum per datafile Platform dependent; typically 222-1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommended
Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system dependent; usually 1022
Maximum per database 65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents Maximum 4 GB, regardless of the maximum file size allowed by the operating system
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum Unlimited
Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement

Control file can be resized to allow more entries; ultimately an operating system limit
Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 50 KB
Maximum size Operating system limit; typically 2 GB
Tablespaces Maximum number per database 64 K

Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file

 

source (http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch43.htm#287916)

Resize redo log groups in Oracle

January 28, 2011 2:52 pm / Leave a comment


Here is the procedure to resize the redo log group in SAP/Oracle
To check all Redolog files Run command:
Select * from v$log;

Now add a group, To add a log group
Alter database add logfile group 5 (‘/oracle/SID/origlogA/log_g15m1.dbf’,’/oracle/SID/mirrlogA/log_g15m2.dbf’) size 200M;

alter database add logfile group 6 (‘/oracle/SID/origlogB/log_g16m1.dbf’,’/oracle/SID/mirrlogB/log_g16m2.dbf’) size 200M;

alter database add logfile group 7 (‘/oracle/SID/origlogA/log_g17m1.dbf’,’/oracle/SID/mirrlogA/log_g17m2.dbf’) size 200M;

alter database add logfile group 8 (‘/oracle/SID/origlogB/log_g18m1.dbf’,’/oracle/SID/mirrlogB/log_g18m2.dbf’) size 200M;

After these commands, Force System for log switch
Alter system switch logfile;
Drop Existing Log groups files (Make sure no group has status active or current)
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;
Alter database drop logfile group 4;

Remove these files from OS level
Now again add same Group

alter database add logfile group 1 (‘/oracle/SID/origlogA/log_g11m1.dbf’,’/oracle/SID/mirrlogA/log_g11m2.dbf’) size 200M;

alter database add logfile group 2 (‘/oracle/SID/origlogB/log_g12m1.dbf’,’/oracle/SID/mirrlogB/log_g12m2.dbf’) size 200M;

alter database add logfile group 3 (‘/oracle/SID/origlogA/log_g13m1.dbf’,’/oracle/SID/mirrlogA/log_g13m2.dbf’) size 200M;

alter database add logfile group 4 (‘/oracle/SID/origlogB/log_g14m1.dbf’,’/oracle/SID/mirrlogB/log_g14m2.dbf’) size 200M;

We can drop Log group 5,6,7,8
In case any of these groups are Active or current stage use “Alter system switch logfile”
Alter database drop logfile group 5;
Alter database drop logfile group 6;
Alter database drop logfile group 7;
Alter database drop logfile group 8;