Best practices and recommendations for preparing SQL Scripts

We have code release for our Oracle databases frequently. Typically it is implemented by executing various SQL or PL/SQL scripits on servers from SQL*Plus.  Many times I have observed there are simple syntax errors in the scripts we received from development team and also the format of the script is not consistent. I thus tried to compile a list of best practices and recommendations for preparing the release scripts for the team. Here are some points:

• Add spool to every script in the following format as minimum:

             set echo on timing on
             spool log/script_name.log
              ...
             spool off

• Separate DDL and DML SQL statements into different scripts.

              Recommend using suffix to distinguish DDL and DML script: e.g. script_ddl.sql, script_dml.sql.

• Separate different type of object creation into different scripts:

           i.e. table, index, package etc. Recommend using the following suffix to distinguish different script in   the format: script_xxx.sql. The ‘xxx’can be:

                      Package specification              pks
                      Package body                         pkb
                      Package sepc and body          pkg
                      Procdueure                             pro
                      Trigger                                    trg
                      Function                                  fun
                      Create table script(s)               tab or ddl
                      Synonym creation statements   syn
                      Index definition                        idx
                      Constraint definitions               con

• Use lower case ".sql" as a suffix for all scripts

        [Mar 16,2010 Update - I tried to do: grep -i create *.sql yesterday, I failed to find a new table creation  due to the script ended with ".SQL". ]

• Recommend consolidating the script in the execution sequence of DDL first followed by DML.

• Recommend using some GUI tools such as TOAD, SQL Developer etc to minimize the syntax errors.

                   At minimum, everyone should do the following:
                   - Check if missing "schema" prefix for table, index names etc.
                   - Check if missing ";" in SQL
                   - Check if missing "/" as the end the PL/SQL begin/end block
                   - Check if comma used properly in the table column list for example
                   - Check if upper or lower cases are used consistently

• Recommend including the back out method (CTAS) for DML script

• Recommend formatting and aligning SQL statements properly whenever possible for improving readability

• Add "show error;" at the end of PL/SQL procedure, function, packages units.

• Avoid using ampersand character ‘&’ within any comment lines in the SQL scripts.

• Avoid using space in the script file name.

• Avoid adding storage clause in the table or index creation statement

           Note: defer to primary DBA to make such a decision if defaults are not desirable

• Avoid using more than 30 characters for table name or column name.

• Avoid using system-generated constraint names.

           For example the following method to add a primary key constraint is not acceptable:
              alter table myschema.t modify (id primary key);

• Recommend using the following methods to create primary key

         (1) Within CREATE TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40),
                constraint t_pk primary key (id) using index tablespace tablespace_name
              ) tablespace  tablespace_name;

        (2) Through ALTER TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40)
               ) tablespace tablespace_name ;

              ALTER TABLE t
              add constraint t_pk primary key(id) using index tablespace tablespace_name;

Complete recovery after lost of all control files and data files.

Assuming all of the control files and one or more data files are lost during media failure, but the online redo logs are intact, can we do a complete recovery? In a previous post, I have demonstrated that it is possible when I have backup control files though I have to open database by resetlogs. In this post, I will demonstrate in this case we can do complete recovery without resetlogs by re-creating the control files.

Note: the scripts used see previous post.

Steps

1. perform hot backup

#> hot_backup.sh

2. Create a table with one row and switch logfile

SQL> drop table system.t;
SQL> create table system.t as select * from all_tables where rownum <=1; SQL> alter system switch logfile;

3. Insert the second row and switch logfile
SQL> insert into system.t select * from all_tables where rownum <=1; SQL> commit;
SQL> alter system switch logfile;

4. Insert the third row and switch logfile
5. Insert the forth row WITHOUT switch logfile

At this point, we should see total 4 rows in the table t:

sys@DBWRKEV1> select count(*) from system.t ;

COUNT(*)
----------
4

6. Delete all data files to simulate media failure, shutdown instance

rm /db1/u01/oradata/dbwrkev1/system01.dbf
rm /db1/u04/oradata/dbwrkev1/undotbs01.dbf
rm /db1/u02/oradata/dbwrkev1/sysaux01.dbf
rm /db1/u02/oradata/dbwrkev1/users01.dbf

7. Restore data files

#> restore_data.sh

8. Re-create control file

idle> conn / as sysdba
Connected to an idle instance.
idle> startup nomount;
ORACLE instance started.
 
Total System Global Area  473956352 bytes
Fixed Size                  1979520 bytes
Variable Size             314575744 bytes
Database Buffers          150994944 bytes
Redo Buffers                6406144 bytes
idle> @ctl
 
Control file created.

Below is the content of ctl.sql

CREATE CONTROLFILE REUSE DATABASE "DBWRKEV1" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/db1/u05/archive/dbwrkev1/redo01a.log',
    '/db1/u06/archive/dbwrkev1/redo01b.log'
  ) SIZE 50M,
  GROUP 2 (
    '/db1/u05/archive/dbwrkev1/redo02a.log',
    '/db1/u06/archive/dbwrkev1/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/db1/u05/archive/dbwrkev1/redo03a.log',
    '/db1/u06/archive/dbwrkev1/redo03b.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/db1/u01/oradata/dbwrkev1/system01.dbf',
  '/db1/u04/oradata/dbwrkev1/undotbs01.dbf',
  '/db1/u02/oradata/dbwrkev1/sysaux01.dbf',
  '/db1/u02/oradata/dbwrkev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

Then I did:

idle> recover database;
ORA-00279: change 10467469165231 generated at 01/27/2010 13:36:56 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_7_709302414.arc
ORA-00280: change 10467469165231 for thread 1 is in sequence #7
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469165563 generated at 01/27/2010 13:44:35 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_8_709302414.arc
ORA-00280: change 10467469165563 for thread 1 is in sequence #8
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_7_709302414.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
Log applied.
Media recovery complete.
idle> alter database open;
 
Database altered.
 
idle> select count(*) from system.t;
 
  COUNT(*)
----------
         4
 

Updated Feb 3, 2010 - It seems RMAN recovery can automatically apply changes in the online redo logs. See: http://oracle-study-notes.blogspot.com/2007/07/exercise-using-control-file-autobackup.html

NetApp Snapshot backup and recovery for Oracle databases

In my current working environment, we use NetApp Snapshot technology to backup some of  our Oracle databases. I have recently participated in a Snapshot backup and recovery test for three production databases that were migrated to new servers. In the tests, the Storage Admin team performed Snapshot backup and restore, the Unix Admin team performed diskgroup deport and import operations, and we, DBAs, were responsible for the database  recovery.

The databases have file layout in such a way that all of  data files and control files is in the volumn /db1,  online redo log files are in the volumn /db1redo, and archived logs are saved in volumn  /db1arch. The Snapshot backups are performed on all three volumns mentioned. We tested two recovery scenarios:

1. Restore /db1 and /db1redo
2. Restore /db1 only

I think if you are an experienced DBA, you will immediately question the practise of backup and restore of online redo logs. You are right. I had doubts at the time. Today, I have done simulations of these two scenarios in a test envrinment. I have been more clear that we should not do that at all.

Below I will describe the test cases in more details, which are the simulations of what we did in the Snapshot backup and recoery test in the production servers a few days ago.

Case 1 - Restore data files, control files and redo log files (e.g. /db1 and /db1redo)
 
1. Perform hotbackup
    #> hot_backp.sh
    Note: script see Appendix

2. Create a table with one row and switch logfile
    SQL> drop table system.t;
    SQL> create table system.t as select * from all_tables where rownum <=1;
    SQL> alter system switch logfile;

3. Insert the second row and switch logfile
   SQL> insert into system.t select * from all_tables where rownum <=1;
   SQL> commit;
   SQL> alter system switch logfile;

4. Insert the third row and switch logfile

5. Insert the forth row WITHOUT switch logfile
    At this point, we should see total 4 rows in the table t:
    sys@DBWRKEV1> select count(*) from system.t ;
    COUNT(*)
     ---------
     4

6. Delete all data files to simulate media failure, shutdown instance
     rm /db1/u01/oradata/dbwrkev1/system01.dbf
     rm /db1/u04/oradata/dbwrkev1/undotbs01.dbf
     rm /db1/u02/oradata/dbwrkev1/sysaux01.dbf
     rm /db1/u02/oradata/dbwrkev1/users01.dbf

7. Restore data files, control files and redo log file
   #> restore_data.sh
   #> restore_ctl.sh
   #> restore_redo.sh

8. Recover database
   After mount the database, I did:

    idle> recover database;
    Media recovery complete.
    idle> alter database open;
    Database altered.

    So here we observed the same in the real test a few days ago, we thought we did a complete recovery, but really?

     idle> select count(*) from system.t;
              select count(*) from system.t
                *
               ERROR at line 1:
              ORA-00942: table or view does not exist

   We can see the table t does not exist at all.

Case 2 Restore data file and control files (e.g. /db1)

Note: due to our data files and control files are both in /db1, with Snapshot restore we can not restore only one of them. This is one of the disavantages of NetApp Snapshot as I can see compared to RMAN backup and restore.

1-6. same as in case 1

7. Restore data files and control files
    #> restore_data.sh
    #> restore_ctl.sh

8. Recover database
   After mount the database, I did:

idle> recover database until cancel using backup controlfile;
ORA-00279: change 10467469061882 generated at 01/26/2010 10:45:48 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_27_708088679.arc
ORA-00280: change 10467469061882 for thread 1 is in sequence #27
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469062192 generated at 01/26/2010 10:51:40 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_28_708088679.arc
ORA-00280: change 10467469062192 for thread 1 is in sequence #28
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_27_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469064362 generated at 01/26/2010 11:43:01 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_29_708088679.arc
ORA-00280: change 10467469064362 for thread 1 is in sequence #29
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_28_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469064390 generated at 01/26/2010 11:43:54 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_30_708088679.arc
ORA-00280: change 10467469064390 for thread 1 is in sequence #30
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_29_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469064400 generated at 01/26/2010 11:44:17 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_31_708088679.arc
ORA-00280: change 10467469064400 for thread 1 is in sequence #31
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_30_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00308: cannot open archived log '/db1/archive/dbwrkev1/dbwrkev1_1_31_708088679.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
 
 
idle> recover database until cancel using backup controlfile;
ORA-00279: change 10467469064400 generated at 01/26/2010 11:44:17 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_31_708088679.arc
ORA-00280: change 10467469064400 for thread 1 is in sequence #31
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
idle> alter database open resetlogs;
 
Database altered.
 
idle> select count(*) from system.t;
 
  COUNT(*)
----------
        3

So, obviously we lost the changes recorded in the online redo log. This is an imcomplete recvoery, corresponding to lost all control files and (some of) data files, but online redo logs are intact. However, if we know the current online redo log at the time of media failure ( We can find it out in the alert log file), we probably can recover more.

For example, in my test, I knew the current online redo logs are those of group 2. So I did:

#> cp redo02a.log /db1/archive/dbwrkev1/dbwrkev1_1_31_708088679.arc

Then I repeated the steps 6,7,8 as in the test case 2, let's see what we got:

idle> recover database until cancel using backup controlfile;
ORA-00279: change 10467469061882 generated at 01/26/2010 10:45:48 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_27_708088679.arc
ORA-00280: change 10467469061882 for thread 1 is in sequence #27
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469062192 generated at 01/26/2010 10:51:40 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_28_708088679.arc
ORA-00280: change 10467469062192 for thread 1 is in sequence #28
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_27_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469064362 generated at 01/26/2010 11:43:01 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_29_708088679.arc
ORA-00280: change 10467469064362 for thread 1 is in sequence #29
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_28_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469064390 generated at 01/26/2010 11:43:54 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_30_708088679.arc
ORA-00280: change 10467469064390 for thread 1 is in sequence #30
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_29_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469064400 generated at 01/26/2010 11:44:17 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_31_708088679.arc
ORA-00280: change 10467469064400 for thread 1 is in sequence #31
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_30_708088679.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
Log applied.
Media recovery complete.
idle> alter database open resetlogs;
 
Database altered.
 
idle> select count(*) from system.t;
 
  COUNT(*)
----------
         4
 

Bingo! It seemed I did a complete recovery!

[Jan 27,2010 updated: this post shows another way to do a complete revcovery in this situation]

Another thing I want to point out is that if we only lost data files whereas the control files and online redo logs are intact, we can do a complete recovery easily as long as we have archived logs. However, in our envionment when we restore snapshot backup, we also overwrite the current control files. So we must be cautious and it is thus advisable to copy the intact control files to some other location. It is probaly a good idea that we should not place all the control files in the same volumn as data files.

BTW, the information in the following two links gave me some ideas about this topic.

http://freekdhooge.wordpress.com/2007/12/03/disaster-recovery-troubles/
http://download.oracle.com/docs/cd/B10501_01/server.920/a96519/strategy.htm#1004884


Appendix - Scripts used:

#!/bin/ksh
# script:  hot_backup.sh
#
sqlplus "/ as sysdba" <<EOF
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS    begin backup;
alter tablespace SYSTEM   begin backup;
alter tablespace SYSAUX   begin backup;
EOF
echo " -- tablespace begin backup mode "

cp /db1/u01/oradata/dbwrkev1/system01.dbf    /db1/backup
cp /db1/u04/oradata/dbwrkev1/undotbs01.dbf   /db1/backup
cp /db1/u02/oradata/dbwrkev1/sysaux01.dbf    /db1/backup
cp /db1/u02/oradata/dbwrkev1/users01.dbf     /db1/backup
cp /db1/u05/archive/dbwrkev1/redo03a.log     /db1/backup
cp /db1/u06/archive/dbwrkev1/redo03b.log     /db1/backup
cp /db1/u05/archive/dbwrkev1/redo02a.log     /db1/backup
cp /db1/u06/archive/dbwrkev1/redo02b.log     /db1/backup
cp /db1/u05/archive/dbwrkev1/redo01a.log     /db1/backup
cp /db1/u06/archive/dbwrkev1/redo01b.log     /db1/backup
cp /db1/u01/oradata/dbwrkev1/control01.ctl   /db1/backup 
cp /db1/u02/oradata/dbwrkev1/control02.ctl   /db1/backup 
cp /db1/u03/oradata/dbwrkev1/control03.ctl   /db1/backup


echo " -- data file, control file and redo log backup done "

sqlplus "/ as sysdba" <<EOF
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS    end backup;
alter tablespace SYSTEM   end backup;
alter tablespace SYSAUX   end backup;
EOF

echo " -- tablespace end backup mode "

sqlplus "/ as sysdba" <<EOF
alter system archive log current;
alter database backup controlfile to '/tmp/backupctrl.ctl' reuse;
EOF
echo " -- archive current redo log and backup controlfile done"

#!/bin/ksh
# script: restore_data.sh
#

cp /db1/backup/system01.dbf  /db1/u01/oradata/dbwrkev1 
cp /db1/backup/undotbs01.dbf /db1/u04/oradata/dbwrkev1 
cp /db1/backup/sysaux01.dbf  /db1/u02/oradata/dbwrkev1 
cp /db1/backup/users01.dbf   /db1/u02/oradata/dbwrkev1 

#!/bin/ksh
#   restore_ctl.sh 
#

cp /db1/backup/control01.ctl /db1/u01/oradata/dbwrkev1   
cp /db1/backup/control02.ctl /db1/u02/oradata/dbwrkev1   
cp /db1/backup/control03.ctl /db1/u03/oradata/dbwrkev1   

#!/bin/ksh
# script: restore_redo.sh
#
cp /db1/backup/redo03a.log   /db1/u05/archive/dbwrkev1    
cp /db1/backup/redo03b.log   /db1/u06/archive/dbwrkev1    
cp /db1/backup/redo02a.log   /db1/u05/archive/dbwrkev1    
cp /db1/backup/redo02b.log   /db1/u06/archive/dbwrkev1     
cp /db1/backup/redo01a.log   /db1/u05/archive/dbwrkev1     
cp /db1/backup/redo01b.log   /db1/u06/archive/dbwrkev1  

Clone an online database - applying standby feature

In a previous post, I described how to clone an online database by transferring data files through network. Since the cloned database should not be opened immediately after the copy, instead, it needed to be opened one week later. This was to minimize the maintenance window needed to configure the cloned database. In this scenario, we need to ship the archived logs from source to target, and also apply them in the target during the whole week in order to keep the source and target database in sync. For that purpose, I wrote a shell script and put it in the cron to automatically transfer the archived logs and then manually applying the logs.

Inspired by a fellow DBA recently, I have found that we can take advantage of Oracle standby technology to achieve the log transportation and apply purpose automatically. I thus did a test on a testing 10g database to demonstrate the concept, which consisted of the following steps:

1. Copied selected tablespaces from source to target, using the same method as in the previous post
    Tablespaces: SYSTEM, SYSAUX, USERS and UNDOTBS1

2. Created standby control file in source and copied them to the target.e.g
     a. At source, alter database create standby controlfile as '/tmp/stby.ctl';
     b. scp from source to target server
     c. At target, issue:
        cp /tmp/stby.ctl /db1/u01/oradata/orasid1/control01.ctl
        cp /tmp/stby.ctl /db1/u02/oradata/orasid1/control02.ctl
        cp /tmp/stby.ctl /db1/u03/oradata/orasid1/control03.ctl

3. Added in the init.ora a parameter: standby_file_management = manual
    Note: this is to take into account that data file directory may be different between source and targer server.

4. Mount the target database with the standby controlfile.
     I then did Offline drop all those data files that are not copied
   e.g.  alter database datafile 'filename' offline drop;

5. In mount mode, issue:
    SQL> RECOVER STANDBY DATABASE UNTIL CANCEL;
    Note: this is to apply all available archived logs upto the current time manually.

6. Started the managed recovery of the standby database at target server
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

7. In the source db issue:
    SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORASID1_STBY LGWR ASYNC';

8. Verify archived log are shiped and applied automatically.
    for examples:
     SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

9. Opened the standby database as a regular operational database
    a. At source issue: ALTER SYSTEM SET log_archive_dest_2=''

    b. At target, cancel Redo Apply:
        ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    c. At target issue: alter database activate physical standby database
    d. ALTER DATABASE OPEN;
    e. Drop those tablespace not copied and needed in the target server.

Some hige-level concepts about transaction processing

I once read in a forum someone proposed an interview question for Oracle DBA. The question was like 'What kind of things Oracle do when issuing a 'COMMIT' ?'. I was very vague at that time. Now I am feeling better. I went through a chapter called "Transaction Processing" in an old Oracle University guide - Oracle 9i database: Space and Transaction Management (Student Guide). Here are some high-level concepts about transaction process, including something Oracle will do when it commits a transaction.

Sytem commit number (SCN)
- identifies the committed version of a database

Change vector
- contains the information necessary to change one block from one consistent state to another consistent state

Begin transaction

1. Undo segment binding
2. Slot allocation in the transaction table
3. Undo block allocation

Transaction Commit

1.  Finds an SCN value
2.  Updates the transaction table
3.  Puts the current undo block into the free block pool (under some condition)
4.  Creates a commit record in the redo log buffer
5.  Flushes the redo log buffer to disk (for durability)
6.  Releases locks held on rows and tables

[Dec 26,2009 Ed.]
Tom Kyte wrote a section "What Does a COMMIT Do?" in his book "Expert Oracle Database Architecture" (pp 292-98). There are two points I would like to add based on his book.

1. When Oracle issues a COMMIT, LGWR writes all of the remaining redo log entries to disk.  Considering commit after inserting one row into a table compared to after inserting 1M rows, the time for commit in these two cases is almost same, why? Because redo info is being written to the disk during the inserting process batch by batch. At COMMIT, the remaining redo entries  be flushed is thus small. The cost of COMMIT is not a function of the transaction size.

2. When COMMIT, some of the blocks the transaction modified will be visited and "cleaned out" in a fast mode if they are still in the buffer cache. Block cleanout refers to the cleaning out the transaction information stored in the database block header.

Modifying a data block

1.  Find an interested transaction list (ITL) available in the block
2.  Lock the row you are modifying
3.  Generate the redo part of data describing the changes to the data block
4.  Generate the undo part of data describing

•  The inverse operation of data block user changes
•  The undo block changes in the undo segment
•  The undo segment block changes (if necessary)

    5.   Create the redo record and apply the changes to the blocks.