Increased REDO Log File Size To 500m
Aug 31, 2012
I have a standalone DB of version 10.2.04.I am facing log file parallel write as one of the top events.I have increased the REDO log file size to 500m.But even then REDO switching is happening frequently.
select group#, bytes, archived, status, first_change#, first_time from v$log order BY first_change#;
GROUP# BYTES ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- --- ---------------- ------------- ----------
10 262144000 NO INACTIVE 8509999 30-08-2012
12 524288000 NO INACTIVE 8612142 30-08-2012
11 262144000 NO INACTIVE 8676390 30-08-2012
9 262144000 NO CURRENT 8706330 30-08-2012
[code]....
View 2 Replies
ADVERTISEMENT
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
Feb 10, 2011
I would like to make a change on the live system!I have read a book and found a information about REDO log file size is impact on DB performance.My DB current log file size is 100 MB. But, Oracle 10g's Redo Logfile Sizing Advisor offer the optimal log file size is 1845 MB.What REDO log file size is best for my Oracle database?
#Optimal log file size:
select optimal_logfile_size
from v$instance_recovery
----------------------------
OPTIMAL_LOGFILE_SIZE
1842
[code]....
View 9 Replies
View Related
Nov 19, 2011
I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.
Please see the column below.
Quote:
FILE_IDTABLESPACE_NAMESIZE(M)USED(M)FREE(M)MAX(M)AUTOEXTENSIBLE% USED
59DATA13276732741.1325.7532767YES100
60DATA13276732741.6925.1932767YES100
61DATA1327673274026.8832767YES100
76DATA13270032193.31506.560NO98
77DATA13270030858.51841.380NO94
78DATA13270029476.563223.310NO90
View 25 Replies
View Related
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
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
Jun 8, 2012
We have one primary oracle database 10.2 and standby by database with no data guard. Initially we have 2 redo log group in primary and standby database.
We have recently add 2 more redo log and increase the size of log member from 50m to 200m in primary database. We don't have any problem in primary database.but in standby database we face a problem because we cannot open it. It always in mount stage in which . How we change the size of current redo log because we can't run. Alter system switch logfile command in mount stage.
View 3 Replies
View Related
Apr 13, 2011
I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .
View 13 Replies
View Related
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
Aug 20, 2010
redo generation. As I found the below statement in another forum."Undo segment generates the redo data also, because undo segment is database changes, so it generates the redo data also."
How a Undo segment can generate Redo and Undo datas.
View 12 Replies
View Related
Feb 25, 2011
I try to current redo log file location to multiplex redo log configuration. I did do this on the DB.I import a backup file to DB when I made this change, but its too slow running import process from old configuration. Approximately it is 4 times slow.
when I recovery old configuration for redo log file, import process is normal running...Why this changes hit to DB import process performance? The old redo log file location is below:
GROUP#MEMBERBYTES
4"/crtest1/oradata/redo04a.log"524288000
4"/crtest1/oradata/redo04b.log"524288000
3"/crtest1/oradata/redo03a.log"524288000
3"/crtest1/oradata/redo03b.log"524288000
2"/crtest1/oradata/redo02a.log"524288000
2"/crtest1/oradata/redo02b.log"524288000
1"/crtest1/oradata/redo01a.log"524288000
1"/crtest1/oradata/redo01b.log"524288000
The new multiplex redo log file location is below:
GROUP#MEMBERBYTES
4"/crtest1/oradata/redo04a.log"524288000
4"/opt/redolog/redo04b.log"524288000
3"/opt/redolog/redo03a.log"524288000
3"/usr/redolog/redo03b.log"524288000
2"/usr/redolog/redo02a.log"524288000
2"/disk1/redolog/redo02b.log"524288000
1"/disk1/redolog/redo01a.log"524288000
1"/crtest1/oradata/redo01b.log"524288000
I think that new configuration is better for old configuration at security issue.Here is the disk partitions on the server:
-bash-3.00$ df -lh
Filesystem size used avail capacity Mounted on
/dev/dsk/c0t0d0s0 9.6G 2.2G 7.3G 23% /
/devices 0K 0K 0K 0% /devices
ctfs 0K 0K 0K 0% /system/contract
[code]....
View 4 Replies
View Related
Apr 10, 2013
Although i have removed current online redo log file in linux os (Oracle Linux),when i type "commit" it says that "commit complete".
Is this fair for this princip?*:" if Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed."*
I think that it can lead to loss of data in some cases..I'm using Oracle 11g R2 on OEL (x64)..
P.S : I haven't multiplexed current ORL group files...
View 9 Replies
View Related
Aug 6, 2012
I'm planning to decrease the time taken to execute data by managing the redo log file but I'm kinda stuck in some aspect : > Why is my OPTIMAL_LOGFILE_SIZE is showing NULL ? > I'm trying to resize the LOGFILE capacity from 100M to 200M and I'm also adding 1 more LOG GROUP with 200M capacity too but turned out that didn't decrease my execution time.
View 12 Replies
View Related
Mar 29, 2011
I've got a legacy SAP system with oracle 8i on Tru64. No changes at all are made but for legal reasons we have to keep it up and running.
we currently do a full backup monthly by shutting down oracle and doing a backup of all the files to tape and that takes around 12 hours.
If I stop doing the full backup and I only backup the control file and the archived redo log files every month and I had to restore the full database years from now, would I be able to restore the database using the last full monthly backup and use the latest control file and archived redo log files?
View 5 Replies
View Related
Oct 1, 2013
I'm using Oracle10G (10.1.0.2) Enterprise Edition. The size of the listener.log file approximately 2.46GB. Is this responsible for any performance problem of any service in my current instance...
View 9 Replies
View Related
Jul 30, 2013
1.2.0.2 on RHL.. 3 Log Groups with 1 member each. db_recovery_file_dest string /oracle/oraarch For the purpose of increasing log file size, if i use ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 300M; but it creates Log Group with 2 member. one is at /oracle/oraarch location and other at /oracle/oradata (db_create_file_dest).
We are using ORACLE MANAGED FILE SYSEM . I want only 1 member at /oracle/oraarch (to keep the previous setting intact ...just increasing the size from 100 to 300M). If I manually give the path where to create the logfile member, I get this error:
ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M;
ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/oraarch/DB/onlinelog/' - file cannot be created
ORA-27038: created file already exists
Additional information: 1
View 8 Replies
View Related
Sep 1, 2010
I am getting the following error when i try to re size the data file.But the data file is having lot of free space.
ORA-03297: file contains used data beyond requested RESIZE value
Presently table space size is 220GB with 8 data files.As the process of the performance tuning we moved data to different table spaces.Now used space of the tablespace is 90GB.So I am trying to resize the datafile but it throw an error.
View 1 Replies
View Related
Oct 2, 2012
I noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.
Is there anything else I can look into to see how to make the .arc files larger?
SQL> select group#, thread#, bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800
select blocks, block_size, blocks*block_size from v$archived_log where sequence# between 63876 and 72851 and thread# = 1
BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160
[code]...
View 18 Replies
View Related
Dec 28, 2010
we have an environment with many Oracle databases in different versions (v8, V9, V10 and V11). Besides that, there are many versions of the Oracle Client in use, ranging from V6 to V11. Many of the clients have a TNSnames.ORA and as was to eb expected, many of these are old as well with a lot of (by now) invalid information in there.
Is there any reasonable maximum size to the TNSnames.ORA file?What are the consequences from going over the max size? Are there alternatives to using TNSnames.ora which you would recommend?
View 4 Replies
View Related
Feb 13, 2012
I am storing customer's snaps in a table ( column's data type as LONG RAW) using oracle forms Webutil. Now there are 250 snaps in the table. The file type of these snaps is JPG with the average size 30KB.
I made a backup using export utility before storing these snaps and the exported DMP file's size was 36MB. Now after storing these just 250 snaps of 30KB the DMP file's size is gone over 300MB.
i need to change column's datatype? or some where in oracle forms's image item. Because on window's file system the size of these files is just 8MB.
View 8 Replies
View Related
Nov 24, 2011
I am having I/O issues if i create 20 GB DATAFILES on SMALL TABLE SPACE. guide me with the maximum size limit of data file that I can create in Windows 2003 32 bit server.
View 3 Replies
View Related
Sep 29, 2010
I am writing a program for doing some file transfer between the client machine and the application server.I am using Webutil_File_Transfer.Client_To_AS to do the transfer and also using Webutil_File.File_Size to check on the file size at the source.
Once the transfer is complete, I also need to check on the destination file size (the application server running on Linux) for verification purposes and can't find the way to do it.
View 1 Replies
View Related
Jan 5, 2012
What's the maximum size of the control file in one database ?
i calculated it according to the following steps:
SQL>SELECT (BLOCK_SIZE/1024/1024)*20000 MB FROM V$CONTROLFILE WHERE ROWNUM = 1;
MB
------
312.5
The maximum number of the data block in one control file is 20000.
View 4 Replies
View Related
Oct 24, 2011
SQL> select block_size from v$controlfile;
BLOCK_SIZE
----------
16384
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
the 2 can have difference block size?
View 3 Replies
View Related
Dec 16, 2010
I'm facing problem with archive log file size, Archive logs are generated with only of 90m or 92m or 94m(Variable sizes of less than 100m), Although i had set 100m for each of my redo log file. Here i'm providing my create db script for your reference. I want to know why the log switches before it reaches 100m.Is there any connection of intial 10m for my .dbf files.
create database mydev
maxlogmembers 3
maxloghistory 100
maxdatafiles 50
maxinstances 1
logfile
[Code]....
View 14 Replies
View Related
Jul 22, 2011
Our product uses Oracle11gR1 and in new release we are going to use Oracle11gR2. For this we are performing following steps:
(1) Install Oracle11gR2 on a machine where our product (Oracle11gR1) is already installed.
(2)Upgrade Oracle11gR1 schema to Oracle11gR2.
(3)For using upgraded schema in our product installer we create clone of upgraded schema.
(4)For creating clone we are using Oracle11gR2 DBCA utility.
(5) Clone files are successfully created (DBC, CTL, DBF).
Now we performed same steps from another machine and DBF file size changed very much. On one machine it was 89 MB and on second machine it was 150 MB. There is no different in schema and both machines are Windows 7 machines.
Tor educe size of schema we tried different space reclamation commands but size is not changing.
View 3 Replies
View Related
Nov 7, 2007
I want to export the oracle data into an excel sheet. I have written the code by using UTL_FILE package. but i am getting the output as shown in the screen shot(without formatting the column size as the width of the data it has). But I want the output column width to be set according to the size of the data automatically.
View 5 Replies
View Related
May 8, 2013
We are planning to export the table data to a file pipedelimited. How do i estimate the size of the FlatFile based on the table size? or avg rowlength
View 3 Replies
View Related
Sep 9, 2012
While increasing the tablespace i am getting below error. How to handle this
SQL> set lin 300
SQL> col TABLESPACE_NAME for a25
SQL> col FILE_NAME for a65
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB
2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;
TABLESPACE_NAME FILE_ID FILE_NAME AUT MB
------------------------- ---------- ----------------------------------------------------------------- --- ----------
SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300
SQL> Alter database datafile 3 RESIZE 60000M;
Alter database datafile 3 RESIZE 60000M
*
ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block
View 3 Replies
View Related
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
View Related
