Server Administration :: Sudden Increase In Archive Log Size
Mar 26, 2010
We had a database (DB A)that is having version 9.2.0.6.0.This DB is having multiple tables and volume of 6 million in individual tables.Another database is also 9.2.0.6.0 (DB B), this DB has Mviews pointing to DB A. Mviews are refreshed in every 15 mins, with fast refresh option in 90% cases and remaining having complete refresh.
Last weekend we have migrated DB 2 to version 10.2.0.4.0 - 64bi and on another server.After version upgrade and DB migration complete refresh was done once for all mviews.
Now DB A is generating huge amount of archive log and also it's UNDO space is getting fully consumed causing performance issue and DB hang.what has gone wrong and what we can do to improve response of DB A and also to reduce size of Archive log ?
View 3 Replies
ADVERTISEMENT
Jan 31, 2011
How to know DB size increase per hour or day on the Oracle?
View 3 Replies
View Related
Feb 2, 2012
I am trying to increase the size of sga or you can say that i want to make my sga in automatic memory management...Following is the steps i am trying
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 96M
SQL>
after that i am trying to increase the size
SQL> alter system set sga_max_size = 200m;
alter system set sga_max_size = 200m
*
ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified
View 1 Replies
View Related
Jan 31, 2013
I have Oracle 11gR2 running on windows xp machine. Windows xp has total size of 150 GB and free space of 95 GB.
I checked the size of the database that I created. It showed the total size of the database as 2 GB and used space as 2 GB. If I want to increase the total size of the database to 50 GB, what should i do? Now which is the disk space size? Windows or Oracle?
View 4 Replies
View Related
Jun 4, 2013
I want to increase my shared pool. my SGA values are as under below.
SELECT NAME,SUM(VALUE)/1024/1024/1024 gb FROM V$SGA
GROUP BY NAME;
Database Buffer 4.8125
Redo Buffers 0.0782623291015625
Variable Size 19.0000001564622
Fixed Size 0.00208648294210434
View 5 Replies
View Related
Jul 8, 2010
I am oracle DBA (trainee) new in this field.
Some time my temporary tablespace size suddenly increase from 1gb to 5-6gb why this happened ? I want only reason why it suddenly increase?
View 6 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
Oct 21, 2011
i am using oracle 10g on solaris 10 os.currently archived log is generated by size wise 52 mb.i want to know whar is the best practice for archive log generation . it should be time interval or size wise.
View 1 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
Jul 22, 2010
SQL> alter system set sga_max_size=1G scope=spfile;
System altered.
SQL> startup force
ORA-12545: Connect failed because target host or object does not exis
SQL> show parameter sga
ERROR:
ORA-03114: not connected to ORACLE
SQL> startup force
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> startup force
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
View 3 Replies
View Related
Jul 30, 2013
Tengo instalado el Oracle 9i en un windows server 2003 a 32 bits pero solo reconoce 2 gb de ram de memoria, como puedo hacer que reconozca mas memoria?
I have installed Oracle 9i on windows server 2003 32 bit but only recognizes 2 gb ram memory, what can i do to recognize more memory?
View 6 Replies
View Related
Oct 14, 2012
I have table and it's size is full when i'm inserting records , records are not inserting , How can i increase table size
View 8 Replies
View Related
Jun 3, 2010
I am studying Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2),
ORA-14767: Cannot specify this interval with existing high boundsby giving an example other than given in the above mentioned document.
the example given in the document is as follows:
To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)
[code]/...
View 3 Replies
View Related
Aug 18, 2010
When i takeind export i got error like this..
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_DATA:"POS"."TBK_POS_FACT":"KROATL200404"]
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small"
from this can we increase the undo_retention value or undo tablespace size?
View 2 Replies
View Related
Feb 28, 2013
I am executing a plsql procedure and trying to increase buffer size to display all characters, procedure is given below:
create or replace procedure prc_p(prm_t1 in VARCHAR2,
prm_t2 in VARCHAR2,
prm_tab in varchar2
)
AUTHID CURRENT_USER
as
str_sql VARCHAR2 (4000);
[code]..........
View 1 Replies
View Related
Feb 14, 2013
We have a table emp_details with 23772889 records. Our requirement is to increase few of the columns size in the table emp_details. We are following the below alter statement which is taking around 2 hours of time.
ALTER TABLE emp_details
MODIFY
(
address char(90)
,department char(30)
)
/
Is there any way to improve the above query performance?
View 2 Replies
View Related
Apr 22, 2013
I have got the following error yesterday
ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):
following are the existing settings
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 96000
undo_tablespace string undo
[code]....
following are the details from v$undostat
select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat
where trunc(begin_time)=trunc(sysdate)-1 order by begin_time;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
-------------- -------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
21-04-13 00:08 21-04-13 00:18 1 12733 378446 duqnawh32hp4u 91152 7068448 225440 345600
21-04-13 00:18 21-04-13 00:28 1 8951 379047 duqnawh32hp4u 99344 7072800 225440 345600
21-04-13 00:28 21-04-13 00:38 1 14073 379650 duqnawh32hp4u 90128 7075872 234656 345600
[code]....
Following are the details in AWR report (00:00 til 01:00 of 21-Apr-2013) .... not thet the error was produced at 00:42
Undo Segment Summary DB/Inst: DBCPY/dbcpy01 Snaps: 18853-18854
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, your - unexpired Released, uU - unexpired reUsed
[code]....
Undo Advisor information taken 'now' is as following
SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
379650
SQL> select dbms_undo_adv.required_retention from dual;
[code]....
In above situation what should be my first choice (assuming increasing space is not an issue) - increase undo tablespace or increase undo retention?
If latter is the choice then what should be the value? Because as I understand present 96000 value is taken as lower limit and because of auto tuning the actual value (TUNED_UNDORETENTION) being used was 345600 In that case shall I set it to something > max(maxquerylen) i.e 379,650 + X?Or I shall increase the undo tablespace size?
From Undo Advisor output it looks to me that even if I increase the undo retention to 379650 current undo size will be able to support it (may be at the expense of DMLs)Is that right?
View 13 Replies
View Related
Aug 1, 2012
I am using apex 4.1. I have this interactive report and i would like to make the text inside the report bigger and bolder. I have seen different examples of using css but none have worked for me. If you have any other way to do this or can give me a detailed way of using css!
View 4 Replies
View Related
Mar 16, 2012
on weekends we have too many archive logs generated .i have taken the data of a week and found that average archive log generated from monday to friday is 7 files per day but on satuarday and sunday the average is 60 files and FG1 gets full. on weekends we have all type of backups running like incremental,archival and logical backups and on sunday we have full physical backup
what is the reason of too many archive log files generations at weekends. is it due to hot and logical backups , if yes then how ?
View 9 Replies
View Related
Mar 6, 2012
I have a small question to be clarified. Is there any way to find out the "Applied Archive log files" in DR and "Deleting them through .
View 1 Replies
View Related
Mar 1, 2012
Our database was generating archivelog(50MB) every 30seconds! I think this is not normal because what I did is open our database, I was the only one who is connected, I'm not running anything, but our database is still generating archivelogs!
Our redo logs: 6groups 3members.
This are the things I saw on our alert logs:
- advanced to log sequence
- cannot allocate new log, sequence
- checkpoint not complete
- private strand flush not complete
What I did is change the log mode of our database to noarchivelog then open the database, then returned it to archivelog mode then that fixed the problem. But the thing is after 6hours its abnormal behavior goes back again.
View 3 Replies
View Related
Dec 28, 2011
what is the techniques to set the archive mode in oracle database on???????
View 4 Replies
View Related
Jan 10, 2011
I want to know how many archive log generating in One hour at the peak time. We have 6 nodes RAC multiplex 2.
Is there are any query through which I can achieve the above purpose.
Note: As this is a prod instance client is not happy to implement Log Miner utility.
View 9 Replies
View Related
Jul 6, 2011
Just to validate from you experts if in case I change the destination file of my archive logs, does it require a restart of the database?
View 10 Replies
View Related
Aug 23, 2011
I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);
So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.
create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;
create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;
alter table teta_admin.t_prac flashback archive audit_flash_archive;
and everything works fine but on sys user.
i can query this table using "as of timestamp" clause
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')
but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.
interface user have such sys privs:
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO
and table privs:
SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT
what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.
View 9 Replies
View Related
Oct 31, 2011
I want to drop a datafile in my test db which is in no archive mode,at first, i want to offline the datafile,but it failed,is there any way to do it?
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3237
[code].......
View 8 Replies
View Related
Aug 5, 2010
I have read it in books that flashback uses undo data to create the flashback data or to flashback the database to a time in the past.Then, what is the role of archive files in flashback operation. Why it is mandatory to turn on archiving before turning on flashback. Also, if you remove the latest archive files, you can NOT flashback the data to a time in past (Oracle complains of missing archive files).
View 8 Replies
View Related
Apr 13, 2011
we are getting below error:
ora-00257 archiver error. connect internal only until freed
when we tried to remove the unwanted arc files thro ASMCMD,we are getting below error:
ASMCMD> rm -ef 2011_04_05/
Unknown option: e
usage: rm [-rf] <name1 name2 . . .>
ASMCMD> rm -rf 2011_04_05/
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_2_seq_27215.1143.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_3_seq_21762.826.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
further we checked FRA size:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 3.19 0 38
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
and checked any arc processing holding lock on arc files:
> ps -ef | grep -i ora_arc*
oracle 6989 1 0 15:07 ? 00:00:00 ora_arc0_TXCOM1
oracle 6991 1 0 15:07 ? 00:00:00 ora_arc1_TXCOM1
oracle 12246 12164 0 15:17 pts/4 00:00:00 grep -i ora_arc*
oracle 13452 1 0 Mar23 ? 00:01:07 ora_arc0_TWEBAPPS1
oracle 13454 1 0 Mar23 ? 00:00:30 ora_arc1_TWEBAPPS1
oracle 15402 1 0 Mar23 ? 00:00:50 ora_arc0_SXCOM1
[Code] ........
but we were not able to remove those .arc files from that folder. finally we have down all the instances and deleted those files manually.
View 1 Replies
View Related
Mar 28, 2012
We are facing a different issue in our database. From yesterday night, the archive log generated with 5 digit. But it supposed to be 6 digit. Hence we are not able to apply the logs in DR Location.
View 4 Replies
View Related
Apr 2, 2012
In normal days size of archives generated in a day is 14-15GB. But since yesterday morning, almost 150GB of archives have been generated and are still getting generated(200MB every 1-2 minutes).
There was a sudden reboot of server yesterday morning. At that time there was heavy load of transactions on database. Can it be a reason that smon is still doing recovery? (I am not sure on this). Also, Undo tablespace is increased from 18 GB to 50 GB since yesterday (autoextend on).
Now we are running out of space for archive file system (can't delete them also until they are transferred to DR) Size of redo log is 200MB. This database supports around 2500 users.
performance wise I don't see any hit. Also wait events are normal. (only few db file sequential read) finding the query/session which are causing this much huge amount of archives?
View 7 Replies
View Related
