Undo Data Introduction

What is Undo Data

When a transaction modifies data, Oracle Database copies the original data before modifying it. The original copy of the modified data is called undo data. Saving this information is necessary for the following reasons:

- To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user who wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

- To provide read consistency, which means that each user can get a consistent view of data, even while other changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query lasts for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m., regardless of update or insert operations by other users during the query.

- To enable certain Oracle Flashback features, such as Oracle Flashback Query and Oracle Flashback Table, which enable you to view or recover data to a previous point in time.

Beginning with Oracle Database Release 11g, for a default installation, Oracle Database automatically manages the undo data. There is typically no need for DBA intervention. However, if your installation uses Oracle Flashback operations, you may need to perform some undo management tasks to ensure the success of these operations.

The amount of undo data that is retained and the time for which it is retained depend on the amount of the database activity and the database configuration. When a transaction starts, it is assigned to an undo segment. Throughout the life of the transaction when data is changed, the original values – that existed before the change are copied into the undo segment.

Undo segments are specialized segments that are automatically created by the instance as needed to support transactions. Like all segments, undo segments are made up of extents, which, in turn, consists of data blocks.

Undo segments automatically grow and shrink as needed, acting as a circular storage buffer for their assigned transactions. Transactions fill extents in their undo segments until a transaction is completed or all space is consumed. If an extent fills up and more space is needed, the transaction acquires that space from the next extent in the segment. After all extents have been consumed, the transaction either wraps around back into the first extent or requests a new extent to be allocated to the undo segment. (If you are performing parallel DML and DDL operations it can actually cause a transaction to use more than one undo segment).

Undo segments can exists only in specialized form of tablespace called an undo tablespace. Another types of segment (like table or index) cannot be store int this undo tablespace.

If you have high connection database you may create undo tablespace with many data files to prevent bottleneck in the data file header.

Although a database may have many undo tablespaces, only one of them at a time can be designated as the current undo tablespace for any instance in the database. By using following command

ALTER SYSTEM SET UNDO_TABLESPACE the_name_of_undo_tablespace;

For example if you have two, and you switch from the old one to the other new one for a brief period of time, both are ACTIVE even though all new transactions go to the new setting. This is important because if you want to move the undo tablespace from the mount point you need to wait until all the transactions are cleared in the tablespace.

Following is the explanation with example what is the undo data:

In this I have two sessions open top window is login with DBA privilege and bottom window is the Oracle database end user or application user)

The user name is TESTUSER, test user has a table my_table with only 1 column col. TESTUSER inserted 1 record a commit the transaction.

TESTUSER executing DML (Data Manipulation Language) to update the table set the column value to 2 (originally is 1). TESTUSER never commit the transaction.

While there is uncommited transaction, with DBA privilege you can query the database to find out the undo segment by combining v$transaction, v$rollstat, v$session, dba_data_files.

Following is the SQL command that I execute:

COL username FORM A15
COL tablespace_name FORM A20
COL filename FORM A40

SELECT ses.username username, txn.used_ublk blk_used,
df.tablespace_name tablespace_name, df.file_name filename
FROM v$rollstat rs,
v$transaction txn,
v$session ses,
dba_data_files df
WHERE rs.usn=txn.xidusn AND
txn.ses_addr = ses.saddr AND
df.file_id = txn.ubafil
/

The result show that
TESTUSER has 1 block of undo segment, in the UNDOTBS1 tablespace and the the datafile is /datafiles/edba01/edba01/undotbs01.dbf

Now I issue SHUTDOWN ABORT to stimulate the database is crash while TESTUSER still have uncommited transaction.

I startup the database.

The record in MY_TABLE is now restore to its original value.

The above scenario is also true, if TESTUSER exit the session (window) while there is uncommited transaction.

Undo information is retained for all transactions, at least until the transaction is ended in one of these ways:

- User undoes a transaction – transaction roll back.
- User ends a transactions – transaction commits.
- User executes a DDL (Data Definition Language) statement. If the current transaction contains any DML (Data Manipulation Language) statements, Oracle first commits the transaction and then executes and commits the DDL as a new transaction.
- User session terminates abnormally – transaction roll back. (Above example)
- User session terminates normally with an exit – transaction commits.

NOTE:
This script can be use to query the rollback, for example when you perform shutdown while there is big number of undo segment block need to be rollback, the number will count down until 0 then the database is continue to close the database, it is handy script to check you database in case some one or some software (e.g. Veritas Cluster) perform shutdown abort after it time out while waiting DB to perform rollback.

And you must connect as sysdba if shutdown in progress.

COL username FORM A15
COL tablespace_name FORM A20
COL filename FORM A40

SELECT ses.username username, txn.used_ublk blk_used,
df.tablespace_name tablespace_name, df.file_name filename
FROM v$rollstat rs,
v$transaction txn,
v$session ses,
dba_data_files df
WHERE rs.usn=txn.xidusn AND
txn.ses_addr = ses.saddr AND
df.file_id = txn.ubafil
/

Changing Database To Archive Log Mode

To configure database to be run on Archive Mode is simply set following parametes

1. LOG_ARCHIVE_DEST
2. LOG_ARCHIVE_FORMAT
3. While database at mounted state issue command ALTER DATABASE ARCHIVELOG;

LOG_ARCHIVE_DEST is applicable only if you are running the database in ARCHIVELOG mode or are recovering a database from archived redo logs. LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, and must be defined as the null string (“”) or (‘ ‘) when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported onall operating systems.) The value cannot be a raw partition.

If LOG_ARCHIVE_DEST is not explicitly defined and all the LOG_ARCHIVE_DEST_n parameters have null string values, LOG_ARCHIVE_DEST is set to an operating system-specific default value on instance startup.

To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the SQL*Plus statement ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination. To permanently change the destination, use the statement ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.

Neither LOG_ARCHIVE_DEST nor LOG_ARCHIVE_FORMAT have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.

LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format follows: LOG_ARCHIVE_FORMAT = ‘log%t_%s_%r.arc’

Archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID) to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file, then make sure the parameter value contains the %s, %t, and %r elements. Otherwise, the following error is displayed at the time of instance startup: ORA-19905: log_archive_format must contain %s, %t and %r

Neither LOG_ARCHIVE_DEST nor LOG_ARCHIVE_FORMAT have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.

1. To find out whether your database is archive mode or not archive you can issue following command

ARCHIVE LOG LIST;

Currently my database is No Archive Mode.

2. Set the value of LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT

ALTER SYSTEM SET log_archive_format = ‘edba.blogspot_%t_%s_%r.arc’ SCOPE = SPFILE;

ALTER SYSTEM SET log_archive_dest = ‘/datafiles/edba01/archived_log’ SCOPE = SPFILE;

Again, please note that for the log_archive_format you must have at least %t, %s and %r in the file this is compulsory requirement.

After you have set the both parameter accordingly, you must start the database at mounted state.

3. Issue command ALTER DATABASE ARCHIVELOG; to enable the archiving.

After that you can open the database with ALTER DATABASE OPEN.

Note: To make the database no archive log the command is

ALTER DATABASE NOARCHIVELOG;

and to be executed at the database at mounted state.

4. Now we check the status with ARCHIVE LOG LIST.

The database is now in Archive Mode.

Addition Information.

The LOG_ARCHIVE_START parameter is old parameter that use to tell Oracle database to start the archival automatically each time database is started, however this parameter is no longer required, as you can see the LOG_ARCHIVE_START is FALSE but the information in ARCHIVE LOG LIST suggested that it is ENABLED.

To view the status of the archive log mode by querying dictionary view.

SELECT name, log_mode FROM v$database;

cron Scheduler

What is cron job?

A Unix/Linux background program or daemon that runs continuously, starting other programs at specified times. These programs are identified and scheduled by crontab.
Cron is normally started automatically, so you never have to type this command.

In order for the oracle user to run cronjob the account should not be deny which can be set in the /etc/cron.deny (different Linux or unix will store the configuration file differently)

To micro manage the cron is via following files, by listing user that allow or deny the use of cron.
/etc/cron.allow
/etc/cron.deny

crontab command

A utility that specifies jobs to be run at regularly scheduled times.

crontab -option

• -e = Open an editor on your crontab file so you can create, add, delete, or change entries.
• -l = List the contents of your crontab file
• -r = Removes your crontab file

To backup your crontab entries you can use following command

crontab -l > crontable.bak

Crontab Arguments

Each crontab entry stats with five fields that specify the time when the command should run, followed by the command itself. An entry must be in the form:

M H D m d /path/command

where:

M = Minutes, from 0 to 59
H = Hour, from 0 to 23
D = Day, Day of the month, from 1 to 31
m = Month, from 1 to 12
d = Day of the week, starting with 0 for Sunday
command = The command (or script) you want to execute at the speficied time.

If you place an asterisk (*) in one of the fields instead of a number, crontab interprets that as a wildcard for all possible values. Use a comma to separate multiple values and a hyphen to indicate a range; you can also include comments by preceding them with the pound (#) character.

Making Oracle Database Autostart

To make this simple, I just say there are three main tasks need to be completed
1. Edit oratab
2. Create dbora script
3. Create the symbolic links

Task 1
To make the Oracle database autostart every time the server is started is to edit the oratab file.

oratab file is located in /etc/oratab

vi /etc/oratab

There will be list of Oracle instances in the oratab file, for my case I only have 1.
Locate the instance you want to be autostart, the setting is

(Instance Name):(The oracle home):(N for not auto start and Y for Yes autostart)

Replace the N with Y to make it autostart.

Task 2
Following Task need to be perform with root account.
We prepare the dbora file and place the file in following directory /etc/init.d
For
Solaris also in /etc/init.d
AIX in /etc
HPUX in /sbin/init.d

Following is the dbora script

#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
#
ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
ORA_OWNER=oracle

case “$1″ in

’start’)
# Start the TNS Listener
su – $ORA_OWNER -c “$ORA_HOME/bin/lsnrctl start”
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values

su – $ORA_OWNER -c $ORA_HOME/bin/dbstart

touch /var/lock/subsys/dbora
;;

’stop’)
# Stop the TNS Listener
su – $ORA_OWNER -c “$ORA_HOME/bin/lsnrctl stop”

# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c $ORA_HOME/bin/dbshut

rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora

Change following accordingly

ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
ORA_OWNER=oracle

$ORA_HOME/bin/lsnrctl start listener_name (specify your listener name if it is not default)

$ORA_HOME/bin/lsnrctl stop listener_name (specify your listener name if it is not default)

For above run level:

0 = halt
1 = Single-user mode
2 = Basic multi-user mode (without networking)
3 = Full (text based) multi-user mode
4 = X11 with KDM/GDM/XDM (session managers) multi-user mode for slackware Linux
5 = Full (GUI based) multi-user mode
6 = reboot

Our run level is 3 and 5 with priority 10 to start and 99 to kill.

change the group owner of the file to dba and the permission to 750

chgrp dba dbora
chmod 750 dbora

To test above script execute it with root account

test start
./dbora start

test shutdown
./dbora stop

Task 3
Create the service link

chkconfig –del dbora
chkconfig –add dbora
chkconfig dbora on
chkconfig –list |grep dbora

If you issue the chkconfig on dbora, will then overwirte the run level become 2 3 4 5, so we just leave it there.

Now you have the oracle database start and shutdown automated whenever you start and shutdown your server.

Top Ten Mistakes Found in Oracle Systems

This section lists the most common mistakes found in Oracle systems. By following the Oracle performance improvement methodology, you should be able to avoid these mistakes altogether. If you find these mistakes in your system, then re-engineer the application where the performance effort is worthwhile.

1. Bad Connection Management

The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.

2. Bad Use of Cursors and the Shared Pool

Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.

3. Bad SQL

Bad SQL is SQL that uses more resources than appropriate for the application requirement. This can be a decision support systems (DSS) query that runs for more than 24 hours or a query from an online application that takes more than a minute. SQL that consumes significant system resources should be investigated for potential improvement. ADDM identifies high load SQL and the SQL tuning advisor can be used to provide recommendations for improvement.

4. Use of Nonstandard Initialization Parameters

These might have been implemented based on poor advice or incorrect assumptions. Most systems will give acceptable performance using only the set of basic parameters. In particular, parameters associated with SPIN_COUNT on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.

Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed together as a group to ensure consistency of performance.

5. Getting Database I/O Wrong

Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth.

6. Redo Log Setup Problems

Many sites run with too few redo logs that are too small. Small redo logs cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If there are too few redo logs, then the archive cannot keep up, and the database will wait for the archive process to catch up.

7. Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

This is particularly common on INSERT-heavy applications, in applications that have raised the block size above 8K, or in applications with large numbers of active users and few rollback segments. Use automatic segment-space management (ASSM) and automatic undo management to solve this problem.

8. Long Full Table Scans

Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.

9. High Amounts of Recursive (SYS) SQL

Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Use locally managed tablespaces to reduce recursive SQL due to extent allocation. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.

10. Deployment and Migration Errors

In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

Although these errors are not directly detected by ADDM, ADDM highlights the resulting high load SQL.

Oracle Certified Professional (OCP) 11g Materials

OCP all in one

OCP: Oracle Database 11g Administrator Certified Professional Certification Kit: (1Z0-051, 1Z0-052, and 1Z0-053)

List Price: $139.98
Price: $88.19 & this item ships for FREE with Super Saver Shipping (Follow this link and click How To Buy -> Amazon.com)
You Save: $51.79 (37%)

To become an Oracle Certified Professional (OCP), one must be proficient in the latest update to Oracle’s industry-leading database product: Oracle 11g—this guide prepares you for all three exams that comprise the Oracle 11g OCP certification. Each level of certification is broken down, beginning with the prerequisite Oracle Certified Administrator certification, and concluding with the OCP certification. You’ll get helpful insight for fulfilling the requirement of taking one of the approved courses offered by Oracle University.

OCA

OCA: Oracle Database 11g Administrator Certified Associate Study Guide: (Exams1Z0-051 and 1Z0-052)

List Price: $89.99
Price: $56.69 & this item ships for FREE with Super Saver Shipping (Follow this link and click How To Buy -> Amazon.com)
You Save: $33.30 (37%)

Candidates for the Oracle Certified Associate in Oracle Database 11g need to pass Exams 1Z0-051 and 1Z0-052 to achieve their certification—this guide prepares you for taking both those exams. This two-in-one study guide covers the Oracle Certified Associate certification for Oracle database 11g and reviews exam topics such as restricting and sorting data, using conversion functions and conditional expressions, displaying data from multiple tables, and exploring the Oracle database architecture. Plus, you’ll learn how to create and maintain an Oracle database, perform database backups and recovery, move and manipulate data, prepare the database environment, and more.

OCP

OCP: Oracle Database 11g Administrator Certified Professional Study Guide: (Exam 1Z0-053)

List Price: $69.99
Price: $44.09 & this item ships for FREE with Super Saver Shipping (Follow this link and click How To Buy -> Amazon.com)
You Save: $25.90 (37%)

This updated study guide for the latest release of the most popular database software in the world—Oracle Database 11g— reviews using the RMAN recovery catalog, handling Flashback technology, managing memory and resources, automating tasks, diagnosing the database, and much more. Plus, more than 100 pages of workbook exercises help prepare you to take the 1Z0-053 exam.

Oracle Database 11g DBA Handbook

The Essential Resource for Oracle DBAs–Fully Updated and Expanded

Manage a flexible, highly available Oracle database with help from the expert information contained in this exclusive Oracle Press guide. Fully revised to cover every new feature and utility, Oracle Database 11g DBA Handbook shows how to perform a new installation, upgrade from previous versions, configure hardware and software for maximum efficiency, and employ bulletproof security. You will learn to automate the backup and recovery process, provide transparent failover capability, audit and tune performance, and distribute your enterprise databases with Oracle Net.

• Plan and deploy permanent, temporary, and bigfile tablespaces
• Optimize disk allocation, CPU usage, I/O throughput, and SQL queries
• Develop powerful database management applications
• Guard against human errors using Oracle Flashback and Oracle Automatic Undo Management
• Diagnose and tune system performance using Oracle Automatic Workload Repository and SQL Tuning Sets
• Implement robust security using authentication, authorization, fine-grained auditing, and fine-grained access control
• Maintain high availability using Oracle Real Application Clusters and Oracle Active Data Guard
• Respond more efficiently to failure scenarios by leveraging the Oracle Automatic Diagnostic Repository and the Oracle Repair Advisor
• Back up and restore tables, tablespaces, and databases with Oracle Recovery Manager and Oracle Data Pump Export and Import
• Work with networked databases, data warehouses, and VLDBs
• Put the latest Oracle Database 11g tools to work–Oracle Total Recall, Oracle Flashback Data Archive, and more

Find Cause And Solution For Oracle Error With oerr

The oerr utility (Oracle Error) is provided only with Oracle databases on UNIX and Linux platforms only. oerr is not an executable, but instead, a shell script that retrieves messages from installed message files. The utility is not provided on Windows systems, since it uses awk commands to retrieve the requested text from the file.

oerr is read the $ORACLE_HOME/mesg/oraus.msg file

Usage: oerr facility error

Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, “ora” is the facility and “7300″
is the error. So you should type “oerr ora 7300″.

If you get LCD-111, type “oerr lcd 111″, and so on.

My example is

[oracle@e-dba01 ~]$ oerr ora 1555
01555, 00000, “snapshot too old: rollback segment number %s with name \”%s\” too small”
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments

to see what is the ORA-1555 all about

Increase Concurrency By Indexing Foreign Key

Concurrency Control, Indexes, and Foreign Keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

No Index on the Foreign Key

In the following circumstances, the database acquires a table lock on the child table:

• No index exists on the foreign key column of the child table. For example, assume that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id.

• A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges data into the parent table. Inserts into the parent table do not acquire table locks on the child table. For example, a database session deletes row 3 from the departments table.

In above diagram, an unindexed foreign key column in the child table causes the deletion of row 3 in the parent to acquire a share table lock on the child table. This lock enables other transactions to query but not update the table. For example, phone numbers in employees cannot be updated while the departments row is being deleted. The table lock releases immediately after the DML on the departments table completes. If multiple rows are affected, then the lock is obtained and released once for each row.

Note: DML on a child table does not acquire a table lock on the parent table.

Index on the Foreign Key

If a foreign key column in the child table is indexed, then DML on the parent table acquires a table lock on the parent table. This lock prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or the child table while the DML on the parent table occurs. This situation is preferable if updates or deletions occur on the parent table while updates occur on the child table.

Bellow diagram shows a scenario in which the foreign key column in the child table is indexed. The parent table is departments and the child table is employees. A session updates row 3 in departments. The DML on departments does not prevent updates to employees, although updates and deletions of rows in departments must wait for row-level locks on the indexes of employees to clear.

If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, a deletion of a record from departments can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!