Some thing about Undo

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database.

 Such information consists of records of the actions of transactions, primarily before they are committed. These records

 are collectively referred to as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued

Recover the database

Provide read consistency

Analyze data as of an earlier point in time by using Oracle Flashback Query

Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the

uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied

from the redo log to the data files. Undo records provide read consistency by maintaining

the before image of the data for users who are accessing the data at the same time that another user is changing it.

• There are two methods for managing undo data:

– Automatic Undo Management

– Manual Undo Management

Managing Undo Data

Automatic Undo Management

The Oracle server automatically manages the creation, allocation, and tuning of undo segments.

Manual Undo Management

You manually manage the creation, allocation, and tuning of undo segments. It was the only

method available prior to Oracle9i.

Undo Segment

An undo segment is used to save the old value (undo data) when a process changes data in a

database. It stores the location of the data and the data as it existed before being modified.

The header of an undo segment contains a transaction table where information about the

current transactions using the undo segment is stored.

A serial transaction uses only one undo segment to store all of its undo data.

Many concurrent transactions can write to one undo segment.

Undo Segments: Purpose

Transaction Rollback

When a transaction modifies a row in a table, the old image of the modified columns (undo

data) is saved in the undo segment. If the transaction is rolled back, the Oracle server restores

the original values by writing the values in the undo segment back to the row.

Transaction Recovery

If the instance fails while transactions are in progress, the Oracle server needs to undo any

uncommitted changes when the database is opened again. This rollback is part of transaction

recovery. Recovery is possible only because changes made to the undo segment are also

protected by the online redo log files.

Read Consistency

While transactions are in progress, other users in the database should not see any uncommitted

changes made by these transactions. In addition, a statement should not see any changes that

were committed after the statement begins execution. The old values (undo data) in the undo

segments are also used to provide the readers a consistent image for a given statement.

Shared Pool

Shared Pool

The Shared Pool environment contains both fixed and variable structures. The fixed

structures remain relatively the same size, whereas the variable structures grow and shrink

based on user and program requirements. The actual sizing for the fixed and variable

structures is based on an initialization parameter and the work of an Oracle internal

algorithm.

Benefits of Using the Shared Pool

Proper use and sizing of the shared pool can reduce resource consumption in at least four ways:

1-If the SQL statement is in the shared pool, parse overhead is avoided, resulting in reduced CPU resources on the system and elapsed time for the end user.

2-Latching resource usage is significantly reduced, resulting in greater scalability.

3-Shared pool memory requirements are reduced, because all applications use the same pool of SQL statements and dictionary resources.

4-I/O is reduced, because dictionary elements that are in the shared pool do not require disk access

There are two parts of Shared Pool

1-Library Cache

2-Data Dictionary Cache

1- Library cache

                The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code.The Library Cache is managed by an LRU algorithm.It contains -

               

Shared SQL: The Shared SQL stores and shares the execution plan and parse tree for

SQL statements run against the database. The second time that an identical SQL

statement is run, it is able to take advantage of the parse information available in the

shared SQL to expedite its execution. To ensure that SQL statements use a shared SQL

area whenever possible, the text, schema, and bind variables must be exactly the same.

Shared PL/SQL: The Shared PL/SQL area stores and shares the most recently

executed PL/SQL statements. Parsed and compiled program units and procedures

(functions, packages, and triggers) are stored in this area.

2- Data Dictionary Cache

The Data Dictionary Cache is also referred to as the dictionary cache or row cache.

Information about the database (user account data, data file names, segment names, extent

locations, table descriptions, and user privileges) is stored in the data dictionary tables.

When this information is needed by the server, the data dictionary tables are read, and the

data that is returned is stored in the Data Dictionary Cache.

Cold backup and hot backup in Oracle

Cold Backup,

A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process.It is  Copying the three sets of files (database files, redo logs, and control file) when the instance is shut down.You must shut down the  instance to guarantee a consistent copy.

If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.

 Hot Backup:

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode.We use to setup host back where cannot shut down the database while making a backup copy of the files.Or the cold backup is not an available option.

If a data loss failure does occur, the lost database files can be restored using the hot backup and the online and offline redo logs created since the backup was done. The database is restored to the most consistent state without any loss of  committed transactions.The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and

 you can recover the database to any point in time.

Some Oracle Sql Scripts ,

                    Helpful script to check running sql if we know SID detail.

Alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Select   .spid,s.sid,s.serial#, s.p1,s.p1text,s.username,s.status, s.last_call_et,p.program,
p.terminal,s.logon_time,s.module,s.osuser,l.SQL_TEXT from V$process p,V$session s,v$sql l where s.paddr = p.addr and l.SQL_ID=s.SQL_ID and l.HASH_VALUE=s.SQL_HASH_VALUE and  .ADDRESS=s.SQL_ADDRESS and s.MODULE='&SID';


                 Helpful script to check running session detail from Toad, 

Alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select  p.spid,s.sid,s.serial#,s.p1,s.p1text,s.username,s.status,
s.last_call_et,p.program,p.terminal,s.logon_time,s.module,s.osuser,l.SQL_TEXT
,s.TERMINAL from V$process p,V$session s,v$sql l
where s.paddr = p.addr and l.SQL_ID=s.SQL_ID and l.HASH_VALUE=s.SQL_HASH_VALUE  and l.ADDRESS=s.SQL_ADDRESS
and s.MODULE='T.O.A.D.';

               Helpful script to check total number of sql from user .

Select count(SID) "Total number of session",USERNAME,status from v$session where type='USER' group by USERNAME,status ;

                 Helpful script to check session detail if we have PID /OS Process id.

SELECT
           'USERNAME   : ' || s.username     || CHR(10) ||
           'SCHEMA     : ' || s.schemaname   || CHR(10) ||
           'OSUSER     : ' || s.osuser       || CHR(10) ||
           'PROGRAM    : ' || s.program      || CHR(10) ||
           'SPID       : ' || p.spid         || CHR(10) ||
           'SID        : ' || s.sid          || CHR(10) ||
           'SERIAL#    : ' || s.serial#      || CHR(10) ||
           'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || ''''  || CHR(10) ||
           'MACHINE    : ' || s.machine      || CHR(10) ||
           'TYPE       : ' || s.type         || CHR(10) ||
           'TERMINAL   : ' || s.terminal     || CHR(10) ||
           'SQL ID     : ' || q.sql_id       || CHR(10) ||
           'SQL TEXT   : ' || q.sql_text
           FROM v$session s  ,v$process p ,  v$sql     q
WHERE s.paddr  = p.addr
AND   p.spid   = '&PID_FROM_OS'
AND   s.sql_id = q.sql_id(+);

                We can find out Plan for any session if we know sqlid.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));

TableSpace Script

Below script is helpful to check tablepsace  detail.
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

Select
   fs.tablespace_name                          "Tablespace",
  df.totalspace                               "TOT_SIZE",
  fs.freespace                                "TOT_FREE",
  (round(100 * (fs.freespace / df.totalspace))) pct_Free
from
   (select      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name  ) fs
where   (df.tablespace_name = fs.tablespace_name ) and fs.tablespace_name like '%&TBS%' ORDER BY pct_free DESC ;

Linux Command

This article provides practical examples for 50 most frequently used commands in Linux / UNIX.

This is not a comprehensive list by any means, but this should give you a jumpstart on some of the common Linux commands. Bookmark this article for your future reference.

Did I miss any frequently used Linux commands? Leave a comment and let me know.

1-mkdir - make directories
cd - change directories

Use cd to change directories. Type cd followed by the name of a directory to access that directory.Keep in mind that you are always in a directory and can navigate to directories hierarchically above or below.

2mv- change the name of a directory

Type mv followed by the current name of a directory and the new name of the directory.

 Ex: mv testdir newnamedir

3pwd - print working directory

will show you the full path to the directory you are currently in. This is very handy to use, especially when performing some of the other commands on this page

 4rmdir - Remove an existing directory

 rm -r

Removes directories and files within the directories recursively.

5chown - change file owner and group

6-ls - Short listing of directory contents

-a        list hidden files

-d        list the name of the current directory

-F        show directories with a trailing '/'

            executable files with a trailing '*'

-g        show group ownership of file in long listing

-i        print the inode number of each file

-l        long listing giving details about files  and directories

-R        list all subdirectories encountered

-t        sort by time modified instead of name

7-cp - Copy files

8-top - Prints a display of system processes that's continually updated until the user presses the q key.

9-uptime - Prints the system uptime.

10-w - Prints the current system users.

11-reboot - Reboots the system (requires root privileges).

12-head files - Prints the first several lines of each specified file.
13-cal month year - Prints a calendar for the specified month of the specified year.

14-cat files - Prints the contents of the specified files.

15-clear - Clears the terminal screen.
16-mv- moves the file data1 to the folder newdata and deletes the old one.
17-df-Shows the disk usage. This will tell you how much disk space you have left on your hard drive as well as the floppy.
18-pwd-Shows what directory (folder) you are in.
In Linux, your home directory is /home/particle

19-man -This command brings up the online Unix
manual.

20-kill-Use kill command to terminate a process

SAR - System Activity Reporter

System Activity Reporter is an important tool that helps system administrators to get an overview of the server machine with status of different important metrics at different points of time.

If suppose you are having an issue with the system currently, Like some of your customers are unable to list some data from the

Database. The first thing that most of the Linux system administrators do is to recall the same issue when it previously occurred,

 and If you remember the day of its previous occurrence then you can easily compare the internal system statistics with the current  statistics.

SAR is very much helpful in doing exactly that.

The first thing that we need to do is check and confirm whether you have SAR utility installed on the machine.

 Which can be checked by listing all rpm's and finding for this utility.

1 CPU Usage of ALL CPUs (sar -u)

 This gives the cumulative real-time CPU usage of all CPUs. “1 3″ reports for every 1 seconds a total of 3 times.

 Most likely you’ll focus on the last field “%idle” to see the cpu load.

 01:10:01 AM       all     12.92      0.00      0.21      1.37      0.00     85.50

01:20:01 AM       all     12.56      0.00      0.23      0.88      0.00     86.33

01:30:01 AM       all     13.80      0.00      0.21      0.78      0.00     85.21

01:40:01 AM       all      8.15      0.00      0.13      0.39      0.00     91.34

01:50:01 AM       all      4.89      0.00      0.11      0.24      0.00     94.76

02:00:01 AM       all      7.01      0.00      0.14      0.36      0.00     92.49

02:10:01 AM       all     13.55      0.00      0.27      1.85      0.00     84.33

02:20:01 AM       all      9.95      0.00      0.21      0.64      0.00     89.20

02:30:01 AM       all      7.02      0.00      0.16      0.87      0.00     91.95

2-. CPU Usage of Individual CPU or Core (sar -P)

If you have 4 Cores on the machine and would like to see what the individual cores are doing, do the following.

“-P ALL” indicates that it should displays statistics for ALL the individual Cores.

In the following example under “CPU” column 0, 1, 2, and 3 indicates the corresponding CPU core numbers.

01:30:01 AM       all     13.80      0.00      0.21      0.78      0.00     85.21

01:40:01 AM       all      8.15      0.00      0.13      0.39      0.00     91.34

01:50:01 AM       all      4.89      0.00      0.11      0.24      0.00     94.76

02:00:01 AM       all      7.01      0.00      0.14      0.36      0.00     92.49

02:10:01 AM       all     13.55      0.00      0.27      1.85      0.00     84.33

02:20:01 AM       all      9.95      0.00      0.21      0.64      0.00     89.20

02:30:01 AM       all      7.02      0.00      0.16      0.87      0.00     91.95

3- Memory Free and Used (sar -r)

This reports the memory statistics. “1 3″ reports for every 1 seconds a total of 3 times.

Most likely you’ll focus on “kbmemfree” and “kbmemused” for free and used memory.

12:00:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree kbswpused  %swpused  kbswpcad

12:10:01 AM    113948  12112648     99.07     35492   9002588  16200712    571064      3.40    204928

12:20:03 AM     81892  12144704     99.33     34468   9061424  16201388    570388      3.40    204120

12:30:01 AM    137436  12089160     98.88     37432   9099808  16201640    570136      3.40    204216

12:40:02 AM    138400  12088196     98.87     38592   9128920  16201872    569904      3.40    204076

12:50:01 AM    122772  12103824     99.00     40156   9139808  16201944    569832      3.40    204632

01:00:01 AM    169380  12057216     98.61     41868   9061508  16202004    569772      3.40    205168

4-Swap Space Used (sar -S)

This reports the swap statistics. “1 3″ reports for every 1 seconds a total of 3 times.

 If the “kbswpused” and “%swpused” are at 0, then your system is not swapping.

 Linux 2.6.18-194.el5 (milli.tenongroove.com)     02/13/2014

 5-Overall I/O Activities (sar -b)

                This reports I/O statistics. “1 3″ reports for every 1 seconds a total of 3 times.

Following fields are displays in the example below.

tps – Transactions per second (this includes both read and write)

rtps – Read transactions per second

wtps – Write transactions per second

bread/s – Bytes read per second

bwrtn/s – Bytes written per second

12:00:01 AM       tps      rtps      wtps   bread/s   bwrtn/s

12:10:01 AM    159.91    108.09     51.82   3141.91   1284.71

12:20:03 AM    359.40    300.74     58.66  41838.06  22521.18

12:30:01 AM    314.82    238.17     76.65  23390.49  13952.02

12:40:02 AM    277.36    255.01     22.34  33625.52   1036.20

12:50:01 AM     99.54     73.69     25.85   2332.04   1779.16

01:00:01 AM    110.22     86.61     23.61   3601.47    946.98

6-6. Individual Block Device I/O Activities (sar -d)

01:00:01 AM    dev8-0     94.28    639.08   1167.44     19.16      0.65      6.85      4.66     43.91

01:00:01 AM   dev8-16    243.98     67.84   2272.06      9.59      1.85      7.57      3.62     88.41

01:10:01 AM    dev8-0     96.15    609.59    675.88     13.37      0.64      6.67      4.62     44.40

01:10:01 AM   dev8-16    114.92   1887.28    751.40     22.96      0.77      6.69      3.54     40.67

01:20:01 AM    dev8-0     93.70    539.33    895.29     15.31      0.58      6.21      4.41     41.28

01:20:01 AM   dev8-16     96.87    144.94   1030.79     12.14      0.72      7.39      3.62     35.05

01:20:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

01:30:01 AM    dev8-0     88.23    501.38   1042.23     17.50      0.63      7.18      5.02     44.33

01:30:01 AM   dev8-16     80.55    126.41   1034.64     14.41      0.57      7.07      3.50     28.15

01:40:01 AM    dev8-0     72.41    613.69   1002.74     22.32      0.52      7.23      4.88     35.36

01:40:01 AM   dev8-16    260.85  34977.00  27110.38    238.02     70.49    267.99      2.52     65.80

01:50:01 AM    dev8-0     87.83   1140.87    924.01     23.51      0.59      6.69      4.30     37.76

To identify the activities by the individual block devices (i.e a specific mount point, or LUN, or partition), use “sar -d”

7-7. Display context switch per second (sar -w)

This reports the total number of processes created per second, and total number of context switches per second.

 “1 3″ reports for every 1 seconds a total of 3 times.

 12:00:01 AM   cswch/s

12:10:01 AM    839.84

12:20:03 AM   1150.19

12:30:01 AM   1160.66

12:40:02 AM   1340.34

12:50:01 AM    852.09

01:00:01 AM    826.40

01:10:01 AM    804.37

01:20:01 AM    793.43

8. Reports run queue and load average (sar -q)

This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.

“1 3″ reports for every 1 seconds a total of 3 times.

12:00:01 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15

12:10:01 AM         3       476      3.98      3.92      3.07

12:20:03 AM         1       475      6.53      5.21      3.90

12:30:01 AM         3       474      3.05      3.47      3.72

12:40:02 AM         2       467      3.04      3.20      3.36

12:50:01 AM         2       472      1.76      2.27      2.79

01:00:01 AM         4       476      2.52      2.52      2.61

01:10:01 AM         3       473      2.05      2.35      2.49

01:20:01 AM         4       472      2.32      2.30      2.36

09. Report Sar Data Using Start Time (sar -s)

When you view historic sar data from the /var/log/sa/saXX file using “sar -f” option, it displays all the sar data for

 that specific day starting from 12:00 a.m for that day.

Using “-s hh:mi:ss” option, you can specify the start time. For example, if you specify “sar -s 08:00:00″,

 it will display the sar data starting from 10 a.m (instead of starting from midnight) as shown below.

You can combine -s option with other sar option.

For example, to report the load average on 26th of this month starting from 10 a.m in the morning, combine the -q and -s option as shown below.

08:00:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle

08:10:02 AM     all     19.72      0.00      2.46     10.92      0.00     66.90

08:20:01 AM     all     22.18      0.00      3.13     10.57      0.00     64.12

Average:        all     20.95      0.00      2.80     10.75      0.00     65.51