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


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

Server Administration :: Initialization Parameter File - Currently In Effect For Session

Jan 31, 2012

I have a question about spfile.

SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;

v$parameter displays information about the initialization parameters that are currently in effect for the session,but v$spparameter displays information about the contents of the spfile.I want to know why the count of parameters whose values are not null in v$parameter is greater.where are they from? My initialization parameter file is spfile.

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


View 12 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 :: 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);

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 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 :: How To Reset Parameter Value

Dec 9, 2011

I often use "alter session set xxx=xxx;" command to change parameter value temporarily. After that, how to reset parameter value to default and I don't want logout sqlplus.

View 3 Replies View Related

Server Administration :: Increase SGA Parameter

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

Server Administration :: How To Change DB Timezone Parameter

Aug 31, 2010

How to change db timezone parameter. My application team has asked me to change the db timzone parameter by following sql.

ALTER database SET TIME_ZONE = '+10:00';

Is this way is the correct one , and do we have any impact on database and does it require a db bounce.

View 2 Replies View Related

Server Administration :: Use Large Pages Parameter And AMM?

Jul 29, 2011

It always used to be that Automatic Memory Management and Linux huge pages were incompatible: you had to use one or the other. But has new parameter, USE_LARGE_PAGES. This isn't documented apart from a few articles on metalink, but Googling it suggests that if it is on TRUE (the default) or ONLY then I can use AMM with huge pages.

View 3 Replies View Related

Server Administration :: Which Initialization Parameter Can't Be Changed

Apr 29, 2012

which initializaion parameter cant be change after creating the database?

View 5 Replies View Related

Server Administration :: Database Parameter Comparison

Nov 4, 2013

We have migrated database data from physical servers to virtual servers. i want to ensure all database parameters are set correctly in both physical and vblock servers. My question is what are all the parameters need to check and compare in both servers to ensure database from both servers ( physical/vblock ) are in sync.

Database used -- RAC database
OS used -- RHL 5.7

View 5 Replies View Related

Server Administration :: Hash_Area_Size Unit In Show Parameter

Aug 27, 2012

When I list the parameters with "show parameter", I get only numeric value for hash_area_size with no unit, what is the unit for that value and conversion of it to MegaBytes.

View 3 Replies View Related

Server Administration :: Change Parameter Value In Oracle 11gR2?

May 11, 2012

I want to change cursor_sharing, open_cursors and session_cached_cursors parameter values for SPFILE (Only SPFILE) using SQL Query.

I have found following SQL Queries, but I don't know these queries are only for SPFILE or not.

ALTER SESSION SET cursor_sharing='EXACT';
ALTER system set open_cursors=2500;
ALTER SESSION set session_cached_cursors=70;

I need SQL Queries only for SPFILE.

View 3 Replies View Related

Server Administration :: Database Sessions - Setting Value Of Parameter

Mar 26, 2013

I have a simple question about database sessions. The value of parameter "sessions" is set to 500 and the users connect to database through an application server(Jboss). There are more than 500 users connect to the database through application.

My question is, how more than 500 users can connect to the database without any issue, if we set the value of "sessions" parameter to 500?

View 2 Replies View Related

Server Administration :: Quick Check On Hidden Parameter?

Mar 20, 2013

quick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.

Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.

orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';

orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
ERROR at line 1: ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be from among false, true, never, auto, always

update: added 11.1.x.x to my wishlist

View 10 Replies View Related

Server Administration :: ORA-32004 / Obsolete And / Or Deprecated Parameter(s) Specified

Nov 9, 2011

I am getting the following error while starting database.

ORA-32004:obsolete and/or deprecated parameter(s) specified

View 25 Replies View Related

Server Administration :: Differences In Versions According To Init-parameter Compatible

Dec 15, 2010

How oracle deals with the setting of init.ora parameter compatible.

e.g. we have
DB Version=

what is the possible leak for us ? Is there any description about the features changed in 10.2 up to in all steps - so that we can check out which functions we do not have.

View 3 Replies View Related

Server Administration :: Deprecated Parameter Not In Spfile And Cannot Reset In Memory?

Apr 12, 2013

i try to startup a database, and get that obsolete/deprecated parameter specified(ORA-32004: obsolete and/or deprecated parameter(s) specified)so i try to reset the parameter, but it seems it is already reset(not in spfile), so maybe this is in memory. SO now i try to reset with scope = memory or both, it gives this error(ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported).

what to do, because now i cannot reset with scope = memory and in spfile it is not there but still it comes in deprecated parameter.

View 7 Replies View Related

Server Administration :: ORA-01111 / Name For Data File 636 Is Unknown - Rename To Correct File

Nov 3, 2012

i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors

SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS

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

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 :: Identify Tnsnames.ora File?

Jun 10, 2011

How to find out the database TNSNAMES.ora file if there are multiple .ora files with different names in Oracle_home/network/admin directory ?

View 6 Replies View Related

Server Administration :: ARC File Size Too Small?

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;
---------- ---------- ----------
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

---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160


View 18 Replies View Related

Server Administration :: How To Check Alertlog File

Mar 3, 2012

you have some 500 Db how will u check the alertlog file. do u do it manually or is their any tool to check the alertlog files. how to check it.

View 7 Replies View Related

Server Administration :: Archive Log File Generating?

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

Copyrights 2005-15, All rights reserved