Server Administration :: Query On Redo And Undo Basic Concept

Nov 17, 2010

understanding a redo/undo concept . Refer following data

create table t(n number);
insert into t values(10);
commit;

now I update as following

update t set n=20;

As per my understanding the before image i.e. n=10 is stored in undo (to be used for rollback, transaction recovery and even in instance recover but not in media recovery) and after image n=20 is stored in redo (to be used for various recovery purposes including media recovery in case of consistent backup).

So it is redo logs for rolling forward and undo for rolling back making transaction, db consistent . If my above understanding is true then what is meant by the term 'redo required for undo'?

Also, if there are 2 database db1 and db2 connected using database link where we are populating t1 table in db1 using t2 table in db2 using db link where redo and undo will be updated db1 or db2?

View 9 Replies


ADVERTISEMENT

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

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

Undo Table Space Not Working - REDO Being Generated?

Feb 14, 2013

I am using Oracle 10.2.0.3. Since yesterday i am seeing a session with sid 1160 using undo tablespace but not able to find how much it is using .I need to know which session and from which module and how much is the Undo being used by those sessions. I have tried searching but all the queries provide me with some different results each time.

Also i need the same information for REDO being generated .

View 20 Replies View Related

Server Administration :: Roll Of Undo In Recovery?

Aug 4, 2011

what is the role of undo cache or undo tablespace in recovery?

View 3 Replies View Related

Server Administration :: How To Undo Tablespace Size

Feb 7, 2011

As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?

As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?

In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.

As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?

SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space

[code]....

View 12 Replies View Related

Server Administration :: UNDO In Parameter File?

Dec 9, 2011

I have a question ragarding undo tablespace. I want to ask that why only undo tablespace information we need to specify in parameter file. We do not specify any other tablespace information. Not even for temporary tablespace. Then why we need to give undo tablespace name while instance is creating.

View 5 Replies View Related

Server Administration :: Undo Info Of Insert Statement

Jun 24, 2012

If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.

I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:

" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "

My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.

View 2 Replies View Related

Server Administration :: Undo Tablespace Which Has Autoextend On Feature

May 18, 2011

you have an undo tablespace which has autoextend on feature.after a timegap your undo tablespace presently is 100GB

here as a DBA what you will do?

View 3 Replies View Related

Server Administration :: How Oracle Implements Undo Management

Jul 31, 2010

I have been reading various articles about the undo management. This basic concept of undo management is simple but how oracle implements it is bit harder for me to grasp.

What i have read and understood is that whenever a DML(Update, Delete, Insert) statement is issued by a user, the data is fetched from datafile to database buffer cache and at the same time a copy of the original data is saved in undo segment. Now if other users requests the same data, they are presented with the unchanged copy in the undo segment.

Now I have the following questions:
1) In case of Insert statement, what data is saved in undo segment. Is it the complete data in the table to which we want to insert the new row?
2)When the user issues DML statement, there are three copies of the same data, one in Memory (which is changed and not the same as original data), second in Undo segment (Which is unchanged copy of original data) and third in datafile file ( which is original data). What is the difference in the data in undo segment and data in datafile at this stage. Why are the other users presented with the data from undo segment rather than original data from the datafile to maintain read consistency.
3)When the user issues rollback, the changes made to the copy of data in memory are undone.The copies of data in memory and undo segment are now same?. What happens to the before change copy in undo segment. Is it still there or deleted.

View 5 Replies View Related

Server Administration :: Undo Tablespace 100 Percent Full With Offline Extended?

Jul 26, 2010

i'm facing a problem while i'm inserting millions of record from table to table that undo tablespace reach 100% full and execution aborted. , how can free the undo tablespace ??? many of extendes are offline. will it flush automatically ??? or what i should do

View 4 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

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

Server Administration :: Redo Log Switches In Every 3 Minutes

Oct 5, 2012

Today i noticed one problem with my database,my redologs switches in every 3mins,i also noticed there is no more transaction changes happening in database but still redo switches.

Fri Oct 05 06:10:05 2012
Thread 1 advanced to log sequence 79244
Current log# 2 seq# 79244 mem# 0: D:ORADATAORACIREDO02.LOG
Fri Oct 05 06:12:16 2012
Thread 1 advanced to log sequence 79245
Current log# 1 seq# 79245 mem# 0: D:ORADATAORACIREDO01.LOG
Fri Oct 05 06:14:28 2012
[code]......

why redo switch happening,any internal problem causes redo to switch .

View 5 Replies View Related

Server Administration :: Generation Of Redo Log File

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

Server Administration :: High REDO Generation?

Jan 7, 2013

Redo is getting generated very high. how to find out the reason ? database kept under 2 node cluster. chcked alert log trace and log writer trace files. pasted the content as below:

--alert log trace from node1 ( node2 also has same type of message ). Archive destination disk group - TXCOM_BACKUP_01 having enough space ( 80gb )

Mon Jan 7 00:49:10 2013
Thread 1 advanced to log sequence 448546 (LGWR switch)
Current log# 1 seq# 448546 mem# 0: +TXCOM_DATA_01/txcom/onlinelog/group_1.274.785770579
Current log# 1 seq# 448546 mem# 1: +TXCOM_DATA_01/txcom/onlinelog/group_1.302.802265189
Mon Jan 7 00:49:10 2013

[code]...

In the alert log, I am able to see the archive destination disk group ( TXCOM_BACKUP_01 ) is getting DISMOUNTED and again getting MOUNTED during every archive file generation. .

Mon Jan 7 00:49:20 2013
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted

archive destination parameter in both nodes are not configured. it should read diskgroup name. ( +TXCOM_BACKUP_01 ) and corresponding size limit. Should i configure this ?

SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0

[code]...

should i bring the database to mount stage and set log_archive_max_proesses to high count ? now value is 2 ( default )

View 2 Replies View Related

Server Administration :: Uncommit Change And Redo Record?

Feb 22, 2013

SQL> update t set a = 1 where b = 2; -- must have redo record
2 rows updated.
SQL> rollback;

the above redo record that uncommit changed must be written from redo buffer to the online redo logfile. why Oracle write the redo record that uncommit changed to the online redo logfile ? when it will be used?

View 10 Replies View Related

Server Administration :: Access Redo Generation After Migrating From 9.2.0 To 10.2.0.4

Sep 14, 2010

We had our production database hosted on Oracle 9.2.0. Few months back we have migrated it to Oracle 10.2.0.4.0. After Migration I have noticed that redo generation has become very very high. In earlier case no. of log files generating in production hours were around 30 where as after migration it become around 200 files per day. I have run statspack report on this database. Report is saying that db block change & disk write is become very high. Parameter timed_statistics has also been set to FALSE. Even then there is not any reduction on no. of log file generation. I had used import export for upgrading the databases.

View 13 Replies View Related

Server Administration :: Can Select Statement Generates Redo

Dec 7, 2011

Whenever any transaction happen in database redo has generated for this transaction. Do select statement treat as a transaction as it doesn't modify any thing in database. And If select statement should not be a transaction, there should not be any redo generation for select statement.

So is select statement generate redo? If yes then Why ?

View 1 Replies View Related

Server Administration :: Ora-00333 / Redo Log Read Error Block 203 Count 8192

Apr 17, 2010

When i was starting my database .there was an error

ora-00333:redo log read error block 203 count 8192.

View 1 Replies View Related

Server Administration :: Change Current Redo Log Size In Oracle Mount Stage

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

Undo Segments In Undo Table Space

Nov 29, 2012

Can we find out no of undo segments in undo Table space ? If so , how to find? what's their max limit ?

View 9 Replies View Related

Server Utilities :: Added A Datafile In Undo Tablespace

Apr 28, 2011

When I am importing, I get these errors

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....

I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.

View -1 Replies View Related

Server Administration :: Not Able To Query User_jobs

Sep 6, 2010

Recently we have refreshed one schema using datapump. we have one table called user_jobs in Schema1 and its not showing in DBA_TABLES for that schema, when I connect to that schema its showing records in that schema.

SQL> show user
USER is "schema1"
SQL> select counT(*) from user_jobs;
COUNT(*)
----------
18
same has been refreshed to schema2
[code]...

View 8 Replies View Related

Server Administration :: How To Query A Tablespace Being Used By Which Object

Jul 6, 2013

My Oracle db have this tablespace named INDX which is 80% full. I tried to query on the table, user using it but there is no one using this tablespace

May I know if there is a query that i can used to find out which dba_object is consuming the 80% of this tablespace.

View 5 Replies View Related

Server Administration :: Query Regarding Multilingual Database

Nov 16, 2011

For setting multilingual database which will have application data in Japanese I am preparing as follows....The database server is Linux and database is 10g (10.2.0.4) For storing Japanese characters in the database I am creating a database with AL32UTF8 character set

1) Do I need to change any settings on the Linux server just for handling Japanese characters? (In case it would have been windows database server would I need to change regional settings and/or any other OS settings to Japan etc?)

2) Assuming that a Japanese character will not fit in a single byte and default for semantic for char and varchar being byte, do...I need to change nls_length_sementic to char?

3) Apart from some more space requirement and performance impact on string functions will it cause any other adverse effect?

4)I would select Japanese character set -say JA16SJIS, instead of super UTF compliant AL32UTF8? What would be advantage of using AL32UTF8 over using Japanese specific character set?

5) Do I need to set nls_lang on the Linux server?

6) Users will be connecting to Linux server using putty or sqlplusw from windows Which operations will need the client to set nls_lang? To what value?

7) Apart from setting nls_lang on the client (if needed) as in question 6), are any settings needed on the client for inserting and displaying Japanese characters? e.g. regional setting, font setup, codepage setup.

View 5 Replies View Related

Server Administration :: Dropped User In Query Result?

Jul 17, 2005

I had droped one user from my oracle database..but when i run this below query i found that user exist

SQL>select username from dba_users;

but when i try to connect that user i get result

SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied

Now tell me how to remove this user from result of this query

SQL>select username from dba_users;

View 11 Replies View Related







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