deepa, March 24, 2003 - 11:47 pm UTC
How do i calculate the size of the redo log files.
Suppose my database is about 5GB.
And about 100 transactions per minute,
Sometimes during the peak hours i get cannot allocate redo log error.
I know adding redolog files or changing the log checkpoint interval parameter would help.
Is there any strategy to calculate the redolog file size .
March 25, 2003 - 7:54 am UTC
changing the log checkpoint interval won't really affect this.
do you generate 100 50byte transactions or 100 500k transactions per minute.
You figure out your redo generation rate and use that to help you size it. I generally just add logs until it goes away. Not very scientific but hey, brutally effecient.
What is capacity planning? How much is per session memory requirement?
Tony, July 15, 2003 - 7:18 am UTC
1.What is capacity planning and how to do that for a database? please give me a detailed answer or point to any useful link or white papers. It will be better if your new book covers this topic.
2. How much is the per session memory requirement for 8i and 9i databases?
3. What should be the minimum memory size if there are 100 concurrent users for a database?
July 15, 2003 - 9:58 am UTC
1) you'll have to research that on yourself. there are books written on the subject, college courses given on it, careers built around it.
what I'm saying is "bigger then a breadbox".
2) somewhere between 1 byte and 1000gigabytes. It totally depends on what you do, how you do it. I would feel safe with at least 1meg/connected session.
3) how much money do you have? I would think at least 512m at a minimum
Redolog --urgent help
friend, February 10, 2005 - 12:53 pm UTC
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
ARCHIVE LOG MODE
SQL> select * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
T_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
--------- ---------
1 1 47028 5242880 2 NO CURRENT 7
.2383E+12 10-FEB-05
2 1 47027 5242880 2 YES ACTIVE 7
.2380E+12 10-FEB-05
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------
-------------------------------------------------------
-------------------------------------------------------
-------------------------------------------------------
2 ONLINE
/oracle/pdaprd/oralog02/log_g2_m1.dbf
2 ONLINE
/oracle/pdaprd/oralog01/log_g2_m2.dbf
1 ONLINE
/oracle/pdaprd/oralog01/log_g1_m1.dbf
1 ONLINE
/oracle/pdaprd/oralog02/log_g1_m2.dbf
SQL> l
1* SELECT * FROM V$SYSTEM_EVENT WHERE event LIKE '%log%'
SQL> /
EVENT TOTAL_WAITS TOT
AL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- ---
----------- ----------- ------------ -----------------
log file sequential read 2559
0 1384 1 13839642
log file single write 1346
0 328 0 3281372
log file parallel write 191006
0 33610 0 336098203
log buffer space 16
0 16 1 164262
log file switch (checkpoint incomplete) 4
2 256 64 2556493
switch logfile command 888
82 125420 141 1254196932
log file switch completion 36
1 170 5 1702627
log file sync 178717
8 31453 0 314526862
8 rows selected.
SQL>
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string /oracle/pdaprd/oraarch/arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
log_archive_trace integer 0
log_buffer integer 32768
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
log_parallelism integer 1
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string NONE
SQL>
Please suggest to avoid wait events...
Thanks
February 11, 2005 - 6:59 pm UTC
to avoid wait events, simply stop all processing in the database. You will have none.
maybe you want more log -- two tiny ones is ok for testing in single user mode perhaps.
(but you see, you don't give "alot of information" here -- at least not in a readable format. And you don't say over what period of time this is for.....
but looks like you need more log *MAYBE*