Redo Log Sizing

Dec 28, 2012

I look after a database that contains GIS mapping data. We do not use Oracle Spatial - it's just a plain Oracle Standard DB. It is running in Noarchivelog mode (I know - it's not a good idea, but will be sorted when our new Sun T4-1 arrives).

There are only a couple of users who actually edit data in the database, but about 100 simultaneous users who access it. In day to day use we have no performance issues. The DB has 3 50Mb redo log groups, and these switch about every hour or so during normal use.

Every few weeks we do a bulk update of our underlying map data. This involves putting about 4Gb of data into the database (which is about 15Gb in total). This takes about 5 hours and whilst I'm sure our old Sun v240 server lacking power is a substantial cause, I think the lack of redo space makes matters worse. Last time we did this, the system clocked just over 200 redo log switches in 5 hours. There were lots of "Checkpoint Incomplete" messages in the log file too.

The software we use to load the map data doesn't allow the data to be loaded with a nologging switch.

I could resize the redo logs, but if I size them for the update workload - 3x 500Mb - we'll have some days where we don't get a redo log switch at all. Is this necessarily a problem?

The alternative I'm thinking of is prior to performing this update, we add an extra redo log group with a 1Gb file, run the update, then remove the redo log group and delete the file afterwards. Is there anything wrong with this approach?

View 1 Replies


ADVERTISEMENT

Get Redo-log Group Sizing?

Aug 22, 2012

What is the optimal redo log size for the database and how many log files required if desired to enable archive log mode.what can be the value for fast_start_mttr_target..?i think if it this parameter set we can have redo log advisor for optimal redolog size.we have 2 redolog groups with 2 members each size of 1 GB. Will it degrade db performance..?

Database version 11.1.0.7
Oracle apps R12
OS : Linux Redhat 5.5

View 1 Replies View Related

Server Administration :: Sizing Undo Tablespace And Setting Undo-retention?

Jan 30, 2004

regarding sizing undo tablespace and undo_retention parameter.we have to implement the database in production system with 40 users but how much space should be allocated to undo tablespace is there any propotions related to virtual memory and the
parameter.i have gone thru oracle doc's and some related sites.its an ERP aplications that contains 20 modules .I am an new one to this dba level

View 8 Replies View Related

What Exactly Redo Log File Contains

Oct 1, 2011

what exactly redo log file contains.? whether they contained the ddl or dml statements or more than that.........?

View 1 Replies View Related

How To Calculate Redo Rate

Nov 14, 2006

how would I be able to calculate the Redo rate for using in the Required bandwidth Formula as seen below :

Required bandwidth Formula ((Redo rate in bytes per second / 0.7) * 8) / 1,000,000 = bandwidth in Mbps

Example: 385 KB/sec peak rate would require an available network bandwidth of at least

((394253 / 0.7) * 8) / 1,000,000 = 4.5 Mbps.

SOURCE OF FORMULA Network Bandwidth Implications of Oracle Data Guard...I'm using Oracle 10g

View 3 Replies View Related

Advantages Of Redo Log Threads

Sep 16, 2010

tell me the advantages of using more than one redo log thread? Currently i am setup with 3 logfile groups and 3 members in each group.Ie

logfile group 1 ('/u01/oradata/pri/redo01a.dbf',
'/u02/oradata/pri/redo01b.dbf',
'/u03/oradata/pri/redo01c.dbf') size 30M,
group 2 ('/u01/oradata/pri/redo02a.dbf',
'/u02/oradata/pri/redo02b.dbf',
'/u03/oradata/pri/redo02c.dbf' ) size 30M,
group 3 ('/u01/oradata/pri/redo03a.dbf',
'/u02/oradata/pri/redo03b.dbf',
'/u03/oradata/pri/redo03c.dbf' ) size 30M

View 4 Replies View Related

REDO Log Member Multiplex

Jun 13, 2012

I wanna know if the redo log members are mirror copies.All member files from a same redo group have the same data?Are there any different in mirror or multiplex a file?

View 4 Replies View Related

Redo Log Group Resize

Mar 20, 2012

I want to re-size redo log group on my production database .i have 10 redo log groups of 50mb each having 2 members.i want 4 redo groups to be of 250 mb each having 2 members and then i will drop that old 10 redo log group(50 mb ) , so that i will have only 4 redo log group of 250MB each having 2 members.But i have physical standby and logical standby configured on production database .

find attached file for redo log configuration of production database(CBSPROD),Logical standby database(CBSMIS), Physical standby database(CBSDR).

View 1 Replies View Related

Resizing Redo Logs

Aug 8, 2012

I'm using Oracle 10gR2 (10.2.0.4.0) 64 bits.

I got many times oracle ORA-00494 error and the database went down but since 29th of july the database have not been killed.
The error message is below :

ORA-00494: mise en file d'attente [CF] d�tenue pendant trop longtemps ( (more than 900 seconds)) par inst 1, osid 176484
ORA-00028: votre session a �t� ferm�e

My database is used for datawarehouse of many terabytes.

Initially the redo log size was 500Mbytes and I've set it to 3Gbytes. The maximum log switch is after 5 minutes. I want log to be switched every 20 minutes or every 30 minutes.

To obtain the size of redo logs I've executed this query :

SQL> select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;

OPTIMAL_LOGFILE_SIZE
--------------------
54763

53,5 Gbytes is it not very big as redo log size? What's the maximum size of redo log? To set very big redo log size what are the requirements? Which precautions should I take before? What are the risks? Are any other ways to change the log switch frequency?

View 1 Replies View Related

Performance Tuning :: No Redo For DML?

Mar 17, 2006

How can I disable redo generation for DML statements.

View 25 Replies View Related

Redo Generated In Last 2 Hour

Feb 7, 2013

Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic

1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.

total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.

View 7 Replies View Related

Redo Logs Size

May 28, 2013

is it Any way I can put the size of my redo log (During Install Oracle DB 11.1.0.7 )

I mean during installtion .??? becouse its by default 50 MB I need to be 200MB

View 10 Replies View Related

Redo Multiplexing And Archiving

Aug 21, 2012

Database version
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Productionredo logs multiplexed.
[code]....

+ when the redolog 5 was archived how the archiving process works ?

let say both the log members are clean in case which one will be archived 5a or 5b ?

+ I can also see only one archive log is creating during log swtich

View 2 Replies View Related

Redo Log Information - Arch Vs LGWR?

Oct 25, 2012

Redo log information can be transmitted in one of two ways from the primary database to the standby database: either by ARCH or LGWR.

1. when ARCH involves
2. when LGWR involves
FAL_Client = (Should i enter net service name or Db name or service_name
FAL_Server = Should i enter net service name or Db name or service_name )
FAL_CLIENT='whichone'

[code]...

View 9 Replies View Related

Unable To Find Redo Log Files Specified?

Jul 15, 2011

I am creating database instance from template. I have specified the location of redo log files. When I run the dbca utility it does creates the redo log files in specified directory. But the installation fails . When I checked the trace file. it says unable to locate the specified file(redo.log). But when i check in directory they are created.

I am using windows 32 bit oracle 11g

View 1 Replies View Related

DataGuard - Standby Redo Log Files

Sep 3, 2012

What is the purpose of Standby Redo Log Files in Data Guard configuration? When it is utilized by the database?

View 4 Replies View Related

Add Changed Rows To Redo Log Files

Aug 29, 2012

I learned that Oracle uses supplemental logging mechanısm to add the changed rows to redo log files and identify the changed rows on target replication database? Is that mechanism mandatory to handle the replication of data between updated and back up databases?

View 1 Replies View Related

Backup & Recovery :: Redo Log Volume

May 22, 2011

I was asked by my systems administrator if I could tell him how much redo log volume, on average, do I figure we generate in a day?

Just wondering how I might calculate this?

We have several production databases. If I wanted to calculate the above for one of them, would it be take all the redo logs for a day and total up the size in bytes? Maybe take a 5 day work week and take the average over the 5 days?

View 4 Replies View Related

SQL & PL/SQL :: Redo Log Generated In Materialized View

Dec 17, 2012

As we know that, MV is generating more redo logs during the FAST refresh. but i need more clarifications on that.

see the below examples:

exec dbms_mview.REFRESH ( LIST => 'mv_test', method=>'F');

PL/SQL procedure successfully completed.

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 147144

see the redo size is 147144 bytes. Immediately, i refreshed in MV view. now there is no update or insert or delete stats happened in source tables. but i do see redo log generation is high for NO DATA refresh.

select a.name, b.value-147144
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 42352

For no rows refresh, it takes 42352 bytes.. why oracle generated redo logs when there is no DML operations happened in source table.

View 1 Replies View Related

Dirty Versus Redo Buffer?

Mar 3, 2010

What's the difference between a dirty buffer and a redo buffer?

My understanding is that a dirty buffer is a changed buffer or whenever data changes in the buffer cache, it's marked as dirty. Also, a redo buffer keeps track of changes that were made to the data, so it's also referring to changed data as well...DWBn writes dirty buffers to disk and LGWR writes redo data to redo log filesHow can we differentiate between the two?

View 2 Replies View Related

Performance Tuning :: Redo Size?

Jun 18, 2012

In one of our envirnoment i could see the redo size is high.Trying to understand why this is more

View 18 Replies View Related

Server Administration :: Redo Log Generation?

Jul 26, 2010

I have oracle 9i running on HP-UX, I would like to find how much redo we are generating in a given period of time, is there any script that I can use to get this information?

View 3 Replies View Related

Server Administration :: Redo Log Buffer Using?

May 27, 2011

I learnt that logWriter writes in the redo log files when redo log buffer is 1/3 full, it means that 66 % of redo log buffer are always empty and never used,

if no, isn't a waste of memory (66 % always empty !)

View 5 Replies View Related

Smart Flash Cache And Redo With SSD?

Oct 23, 2012

11.2.0.2.0
OEL.

Design stage of New SAN. I can put a lot of the database, including redo on SSD.

1. Hardware sub tiering enabled. Has anyone ever gauged performance with enabling 11g Smart flash cache using the SSD as the flash device even with the hardware subtiering on? ie, if the hardware subtiering is moving hotblocks to most performant disk, is there much significant benefit having the flash cache on. Im guessing yes as the flash cache is working at the SGA level.

2. To redo or not to redo on SSD. Many notes say not to so as to avoid log file sync waits. Is there a way to keep it on the SSD and avoid the waits? Is there any risk of enhanced degradation of the disks over time with having so much sequential writes from the redo if I leave it on there?

How to Minimise Waits for 'Log File Sync' [ID 857576.1] (discusses keeping redo off ssd but only generally)

[URL]

View 1 Replies View Related

Difference Between Redo Logs And Undo Tablespace

Jan 17, 2007

I'm an Oracle novice and from what I've read so far, it seems that you should be able to do rollbacks and data recovery using the redo logs. I'm having a difficulty understanding the need for the undo tablespace.

View 2 Replies View Related

Difference Between Redo Logs And Undo Tablespace?

Apr 25, 2013

it seems that you should be able to do rollbacks and data recovery using the redo logs. I'm having a difficulty understanding the need for the undo tablespace.

View 3 Replies View Related

Performance Tuning :: Redo Log File Size

Apr 6, 2011

The REDO log file size is important DB performance issues when DB is run archivelog mode.If DB run noarchivelog mode, REDO log file size not impact to DB performance.

View 3 Replies View Related

Server Administration :: About Oracle ReDo Log Groups

May 21, 2013

I've a situation where I've very less redo logs generated. Let us say 10MB. Which solution will be better ?

1. Create one redo log group about 12 MB in size.
2. Create two redo log groups about 5 MB each in size as recommended by Oracle.

Even though solution 1 is also appropriate for me because I've less redo generated than the redo log group size. My whole redo will fit in this and I can raise checkpoint forcefully after certain period of time let us say every 3 seconds.

In one of our DB I found scenario one is implemented. So I want to know pros and cons of both of these practices.

View 7 Replies View Related

Server Administration :: Contents Of Redo Log Files

Feb 1, 2011

I have some doubts about redo log files,

1) Can we fetch 'select statements' from redo log files through the use of log miner utility or any other?
(I think redo log file contains only insert,update,delete and DDL/DCL commands only)

2) If "No" to the above answer then how can i fetch all select statements fired on the system for a day or particular time.
(setting of sql_trace may be the one of them, but can it be possible for system level)

View 4 Replies View Related

Performance Tuning :: How To Limit Oracle Redo

Mar 24, 2013

how do you limit oracle redo?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved