. oraenv

. oraenv have u heard about this utility?

some time it's happen when you try to do sqlplus, export then also it's give error.it is very simple but .........

in my case,i have used this only today after 4 month experience of DBA.

It sets the Oracle environment on UNIX systems LIKE ORACLE_SID,ORACLE_HOME and PATH. It will prompt for a SID of the database unless ORAENV_ASK is set to N.

DBA world

welcome to the DBA world.........................................

This blog will be helping to start a carrier as an ORACLE DBA.

Oh.....about me......................

I also don't know..................

Keep sharing knowledge here......

Undo & Redo

– Log writer writes both undo and redo for every DML in a transaction (committed/uncommitted) irrespective of whether checkpoint has taken place or not.

– On checkpoint,

•  Undo present in buffers in database buffer cache is written to undo tablespace .
•  Dirty buffers containing committed/uncommitted data are written to datafiles.

– datafiles may contain uncommitted data (buffers dirtied by uncommitted transaction and checkpoint takes place)

– committed data may not be there in datafiles (checkpointing not done after commit)

– redo/undo for committed changes will always be there in redo logs (Lgwr writes on commit).

– redo/undo for Uncommitted changes may be there in redo logs(Lgwr writes every 3 seconds)

– In addition to redo logs, undo information

• . may be present in buffer cache (checkpoint has not taken place after change)
• . will be written to undo tablespace on checkpoint
• . will never be overwritten until a transaction is active

Let’s see what will happen if undo is stored in redo logs only.

A redo log can be reused once changes protected by it have been written to datafiles (and archivelogs if database is in archivelog mode).

It implies that if I make a change and do not commit it
– Change is written to a redo log
– checkpoint takes place
– uncommitted change is written to datafile
– I decide to rollback the change
– If redo log has not been overwritten
. search entire redo log for the undo and then rollback
else (redo log has been overwritten)
. undo information is not available for rollback.

One might argue that if somehow a redo log is not allowed to be overwritten until it contains active undo, we might be able to manage with undo stored in redo logs only. This solution is not feasible as
– size of redo logs will grow enormously large very soon as thet contain both undo and redo (a user might decide not to end a transaction for months)
– to rollback a change, enormous amount of data in redo logs (both redo and undo) will have to be searched leading to degraded performance
– there will be contention on redo logs as they are being used for both
. writing redo and undo
. reading to rollback a change

Hence, undo information has to be stored separately from redo and is used for rolling back uncommited transactions . The undo stored in undo buffers/undo tablespace is additionally used for
– read consistency
– flashback query
– flashback version query

Now, let’s see what will happen if undo is stored in undo buffers and undo tablespace only.

SCENARIO – I

– I make a change and do not commit
– The redo for the change is present in redo logs
– The undo for the change is present in buffer cache
– The undo for the change is not present in undo tablespace as checkpoint has not taken place
– The instance crashes
– undo information present in buffer cache is wiped out
– As part of instance recovery, redo is applied and datafiles contain uncommitted data
– The database cannot be opened as undo information needed to rollback uncommitted change is not available leading to an inconsistent database.

SCENARIO – II

– I take hot backup of a tablespace.
– The datafile for the tablespace is lost or corrupted.
– I take the tablespace offline and restore the datafile from backup.
– I recover the datafile
– The redo logs and archivelogs contain redo data for both committed and uncommitted transactions
– The redo logs and archivelogs do not contain undo data (as per our
assumption).
– As part of recovery, redo for all the committed/uncommitted changes is read from online/archive redo logs and applied
– The tablespace cannot be brought online as the undo information needed to rollback uncommitted changes is not available

Hence, to resolve above problems, undo is stored in redo logs also. During roll forward phase of instance/media recovery, as changes (redo in redo logs) are applied to datafiles, undo stored in redo logs is used to generate undo segments. These segments are subsequently used to rollback uncommitted changes during rollback phase of instance/media recovery.