Oracle Undo Data

Undo data provides read consistency, Oracle provides two ways to allocate and manage undo(rollback) space among transactions. If you use the manual approach you will be using traditional rollback segments but is easier to let oracle automatically control the rollback segments which is called AUM (automatic undo management), the only part on the DBA side is to size the undo tablespace, then oracle will automatically create the undo segments within the tablespace.

Using AUM you can take advantage of flashback recovery, flashback query, flashback versions query, flashback transaction query and flashback table - see flashback for further details.

There are three parameters associated with AUM

UNDO_MANAGEMENT (default manual)	This is the only mandatory parameter and can be set to either auto or manual.
UNDO_TABLESPACE (default undo tablespace)	This specifies the tablespace to be used, of course the tablespace needs to be a undo tablespace. If you do not set this value oracle will automatically pick the one available. If no undo tablespace exists then oracle will use the system tablespace which is not a good idea (always create one).
UNDO_RETENTION (seconds)	Once a transaction commits the undo data for that transaction stays in the undo tablespace until space is required in which case it will be over written.

When a transaction commits the undo data is not required anymore, the undo data however will stay in the undo tablespace unless space is required then newer transactions will overwrite it. During a long running query that need to retain older undo data for consistency purposes , there might be a possibility that some data it needs has been over written by other new transactions, this would produce the "snapshot too old" error message, which indicates that the before image has been overwritten. To prevent this oracle uses the undo_retention system parameter which try's and keeps the data in the undo tablespace for as long a possible meeting the undo_retention target, however this is not guaranteed.

Undo data can be in 3 states

State		When is undo data over written
uncommitted undo information	undo data that supports active transactions and required in the event of rollback	never
committed undo information (unexpired)	also known as unexpired undo, required to support undo_retention interval	after undo_retention period or undo tablespace space pressure unless guaranteed option is set (see below)
expired undo information	undo information that is no longer needed	always

There are times when you want to guarantee the undo retention at any cost even if it means transactions fail, the option retention guarantee will guarantee that the data will stay in the undo tablespace until the interval has expired, even if there are space pressure problems in the undo tablespace, the default is not to set the guarantee retention period.

I have have noticed on my travels that once undo is expired it is no longer available even if the undo tablespace is not under any space pressure, the only way to keep it is to increase the undo_retention parameter. You can prove this by checking the oldest undo data avilable via the dba_hist_undostat view, the oldest data will match the undo_retention period you set via the undo_retention parameter.

Undo Sizing

Depending on how much undo data you want to keep will determine the size of the undo tablespace, a simple formula is used when calculating the undo tablespace size

undo tablespace size

UR  *  UPS  *  DB_BLOCK_SIZE UR = undo retention (system parameter undo_retention) UPS = maximum undo blocks used/sec (obtain from v$undostat) DB_BLOCK_SIZE = the default block size (obtained from dba_tablespaces)

The Oracle Enterprise Manager uses the desired time period for undo retention and analyses the impact of the desired undo retention setting.

Undo Commands

Undo System Management
Management	alter system set undo_management=auto;
Setting	alter system set undo_tablespace = 'undotbs02';
Retention	alter system set undo_retention = 43200; (it's in seconds)
Undo Control
Creating	create undo tablespace undotbs2 datafile 'c:\oracle\undo02.dbf' size 2G;
Removing	drop undo tablespace undotbs02;
guarantee	alter tablespace undotbs02 retention guarantee; alter tablespace undotbs02 retention noguarantee;
See current undo blocks	select begin_time, undotsn, undoblks, activeblks, unexpiredblks, expiredblks from v$undostat;
Contains snapshots of v$undostat (use obtain the oldest undo available)	select begin_time, undotsn, undoblks, activeblks, unexpiredblks, expiredblks from dba_hist_undostat; NOTE: If your current undo_retention period is 6 days then the oldest undo data in dba_hist_undo should be 6 days old.
Useful Views
DBA_ROLLBACK_SEGS	describes rollback segments
DBA_TABLESPACES	describes all tablespaces in the database
DBA_UNDO_EXTENTS	describes the extents comprising the segments in all undo tablespaces in the database
DBA_HIST_UNDOSTAT	displays the history of histograms of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. This view contains snapshots of V$UNDOSTAT.
V$UNDOSTAT	displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode.
V$ROLLNAME	lists the names of all online rollback segments. It can only be accessed when the database is open.
V$ROLLSTAT	contains rollback segment statistics
V$TRANSACTION	lists the active transactions in the system

 

Flashback

Flashback recovery, flashback query, flashback versions query, flashback transaction query and flashback table all use undo data for more details see flashback.

Oracle Redo

Redo

All the Oracle changes made to the db are recorded in the redo log files, these files along with any archived redo logs enable a dba to recover the database to any point in the past. Oracle will write all commited changes to the redo logs first before applying them to the data files. The redo logs guarantee that no committed changes are ever lost. Redo log files consist of redo records which are group of change vectors each referring to specific changes made to a data block in the db. The changes are first kept in the redo buffer but are quickly written to the redo log files.

There are two types of redo log files online and archive. Oracle uses the concept of groups and a minimum of 2 groups are required, each group having at least one file. They are used in a circular fashion when one group fills up oracle will switch to the next log group.

The LGWR process writes redo information from the redo buffer to the online redo logs when

• user commits a transaction
• redo log buffer becomes 1/3 full
• redo buffer contains 1MB of changed records
• switch of the log files

The log group can be in one of four states

Current	log group that is being actively being written too.
Active	the files in the log group are required for instance recovery
Inactive	the files in the log group are not required for instance recovery and can be over written
Unused	log group has never been written too, a new group.

A log file can be in one of four states

Invalid	the file is corrupt or missing
Stale	the log file is new and never been used
Deleted	the log file is no longer being used
<blank>	the log file is currently being used

Log group and log files commands

Configuration
Creating new log group	alter database add logfile group 4 ('c:\oracle\redo3a.log','c:\oracle\redo3b.log') size 10M;
Adding new log file to existing group	alter database add logfile member 'c:\oracle\redo3c.log' to group3;
Renaming log file in existing group	shutdown database rename file startup database in mount mode alter database rename file 'old name' to'new name' open database backup controlfile
Drop log group	alter database drop logfile group 3;
Drop log file from existing group	alter database drop logfile member 'c:\oracle\redoc.log'
Maintaining
Clearing Log groups	alter database clear logfile group 3; alter database clear unarchived logfile group 3; Note: used the unarchived option when a loggroup has not ben archived
Logswitch and Checkpointing	alter system checkpoint; alter system switch logfile; alter system archive log current; alter system archive log all; # Difference between them are switch logfile - will switch logfile and return prompt immediately, archiving will take place in the background log current - will switch logfile and return prompt only when logfile has been successfully archived log all - will only archiving full log files Note: I have discussed checkpoints
Display the redo usage	select le.leseq "Current log sequence No",   100*cp.cpodr_bno/le.lesiz "Percent Full",   cp.cpodr_bno "Current Block No",   le.lesiz "Size of Log in Blocks" from x$kcccp cp, x$kccle le where le.leseq =CP.cpodr_seq and bitand(le.leflg,24) = 8 /
Useful Views
V$LOG	displays log file information from the control file.
V$LOGFILE	contains information about redo log files.

Archived Logs

When a redo log file fills up and before it is used again the file is archived for safe keeping, this archive file with other redo log files can recover a database to any point in time. It is best practice to turn on ARCHIVELOG mode which performs the archiving automatically.

The log files can be written to a number of destinations (up to 10 locations), even to a standby database, using the parameters log_archive_dest_n and log_archive_min_succeed_dest you can control how Oracle writes its log files.

Configuration
Enabling	alter system set log_archive_dest_1 = 'location=c:\oracle\archive' scope=spfile; alter system set log_archive_format = 'arch_%d_%t_%r_%s.log' scope=spfile; shutdown database startup database in mount mode alter database archivelog; startup database in open mode Archive format options %r - resetlogs ID (required parameter) %s - log sequence number (required parameter) %t - thread number (required parameter) %d - database ID (not required)
Disabling	alter database noarchivelog;
Displaying	archive log list; select name, log_mode from v$database; select archiver from v$instance;
Maintainance
Display system parameters	show parameter log_archive_dest show parameter log_archive_format show parameter log_archive_min_succeed_dest
Useful Views
V$ARCHIVED_LOG	Display the archived log files
V$INSTANCE	Display if database is in archive mode
V$DATABASE	Display if database is in archive mode

I have a more detailed section on redo in my Data Guard section called Redo Processing.

Oracle Automatic Storage Management (ASM)

Automatic Storage Management (ASM) is oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems or files which can be made to look like disks as long as the device is raw. ASM uses its own database instance to manage the disks, it has its own processes and pfile or spfile, it uses ASM disk groups to manage disks as one logical unit.

The benefits of ASM are

• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
• Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
• Uses redundancy features available in intelligent storage arrays
• The storage system can store all types of database files
• Using disk group makes configuration easier, as files are placed into disk groups
• ASM provides stripping and mirroring (fine and coarse gain - see below)
• ASM and non-ASM oracle files can coexist
• ASM is free!!!!!!!!!!!!!

The three components of ASM are

ASM Instance	is a special instance that does not have any data files, there is only ASM instance one per server which manages all ASM files for each database. The instance looks after the disk groups and allows access to the ASM files. Databases access the files directly but uses the ASM instance to locate them. If the ASM instance is shutdown then the database will either be automatically shutdown or crash.
ASM Disk Groups	Disks are grouped together via disk groups, these are very much like logical volumes.
ASM Files	Files are stored in the disk groups and benefit from the disk group features i.e. stripping and mirroring.
ASM Summary	database is allowed to have multiple disk groups You can store all of your database files as ASM files Disk group comprises a set of disk drives ASM disk groups are permitted to contain files from more than one disk Files are always spread over every disk in an ASM disk group and belong to one disk group only ASM allocates disk space in allocation units of 1MB

• Not Managed by ASM - Oracle binaries, alert log, trace files, init.ora or password file
• Managed by ASM - Datafiles, SPFILES, redo log files, archived log files, RMAN backup set / image copies, flash recovery area.

ASM Processes

There are a number of new processes that are started when using ASM, both the ASM instance and Database will start new processes

ASM Instance
RBAL (rebalance master)	coordinates the rebalancing when a new disk is add or removed
ARB[1-9] (rebalance)	actually does the work requested by the RBAL process (upto 9 of these)
Database Instance
RBAL	opens and closes the ASM disk
ASMB	connects to the ASM instance via session and is the communication between ASM and RBMS, requests could be file creation, deletion, resizing and also various statistics and status messages.

ASM registers its name and disks with the RDBMS via the cluster synchronization service (CSS). This is why the oracle cluster services must be running, even if the node and instance is not clustered. The ASM must be in mount mode in order for a RDBMS to use it and you only require the instance type in the parameter file.

ASM Disk Groups

An ASM disk group is a logical volume that is created from the underlying physical disks. If storage grows you simply add disks to the disks groups, the number of groups can remain the same.

ASM file management has a number of good benefits over normal 3rd party LVM's

• performance
• redundancy
• ease of management
• security

ASM Stripping

ASM stripes files across all the disks within the disk group thus increasing performance, each stripe is called an ‘allocation unit’. ASM offers two types of stripping which is dependent on the type of database file

Coarse Stripping	used for datafile, archive logs (1MB stripes)
Fine Stripping	used for online redo logs, controlfile, flashback files(128KB stripes)

ASM Mirroring

Disk mirroring provides data redundancy, this means that if a disk were to fail Oracle will use the other mirrored disk and would continue as normal. Oracle mirrors at the extent level, so you have a primary extent and a mirrored extent. When a disk fails, ASM rebuilds the failed disk using mirrored extents from the other disks within the group, this may have a slight impact on performance as the rebuild takes place.

All disks that share a common controller are in what is called a failure group, you can ensure redundancy by mirroring disks on separate failure groups which in turn are on different controllers, ASM will ensure that the primary extent and the mirrored extent are not in the same failure group. When mirroring you must define failure groups otherwise the mirroring will not take place.

There are three forms of Mirroring

• External redundancy - doesn't have failure groups and thus is effectively a no-mirroring strategy
• Normal redundancy - provides two-way mirroring of all extents in a disk group, which result in two failure groups
• High redundancy - provides three-way mirroring of all extents in a disk group, which result in three failure groups

ASM Files

The data files you create under ASM are not like the normal database files, when you create a file you only need to specify the disk group that the files needs to be created in, Oracle will then create a stripped file across all the disks within the disk and carry out any redundancy required, ASM files are OMF files. ASM naming is dependent on the type file being created, here are the different file-naming conventions

• fully qualified ASM filenames - are used when referencing existing ASM files (+dgroupA/dbs/controlfile/CF.123.456789)
• numeric ASM filenames - are also only used when referencing existing ASM files (+dgroupA.123.456789)
• alias ASM filenames - employ a user friendly name and are used when creating new files and when you refer to existing files
• alias filenames with templates - are strictly for creating new ASM files
• incomplete ASM filenames - consist of a disk group only and are used for creation only.

Creating ASM Instance

Creating a ASM instance is like creating a normal instance but the parameter file will be smaller, ASM does not mount any data files, it only maintains ASM metadata. ASM normally only needs about 100MB of disk space and will consume about 25MB of memory for the SGA, ASM does not have a data dictionary like a normal database so you must connect to the instance using either O/S authentication as SYSDBA or SYSOPER or using a password file.

The main parameters in the instance parameter file will be

• instance_type - you have two types RDBMS or ASM
• instance_name - the name of the ASM instance
• asm_power_limit - maximum speed of rebalancing disks, default is 1 and the range is 1 - 11 (11 being the fastest)
• asm_diskstring - this is the location were oracle will look for disk discovery
• asm_diskgroups - diskgroups that will be mounted automatically when the ASM instance is started.

You can start an ASM instance with nomount, mount but not open. When shutting down a ASM instance this passes the shutdown command to the RDBMS (normal, immediate, etc)

ASM Configuration
Parameter file (init+asm.ora)	instance_type=’asm’ instance_name=’+asm’ asm_power_limit=2 asm_diskstring=’\\.\f:’,’\\.\g:’,’\\.\h:’ asm_diskgroup= dgroupA, dgroupB Note: file should be created in $ORACLE_HOME/database
Create service (windows only)	c:> oradim –new –asmsid +ASM –startmode manual
Set the oracle_sid environment variable (windows or unix)	c:> set ORACLE_SID=+ASM (windows only) export ORACLE_SID=+ASM (unix only)
Login to ASM instance and start instance	c:> sqlplus /nolog; sql> connect / as sysdba; sql> startup pfile=init+asm.ora Note: sometimes you get a ora-15110 which means that the diskgroups are not created yet.
ASM Operations
Instance name	select instance_name from v$instance;
Create disk group	create diskgroup diskgrpA high redundancy   failgroup failgrpA disk ’\\.\f:’ name disk1   failgroup failgrpB disk ’\\.\g:’ name disk2 force   failgroup failgrpC disk ’\\.\h:’ name disk3; create diskgroup diskgrpA external redundancy Note: force is used if disk has been in a previous diskgroup, external redundancy uses third party mirroring i.e SAN
Add disks to a group	alter diskgroup diskgrpA add disk   '\\.\i:' name disk4;   '\\.\j:' name disk5;
Remove disks from a group	alter diskgroup diskgrpA drop disk disk6;
Remove disk group	drop diskgroup diskgrpA including contents
resizing disk group	alter diskgroup diskgrpA resize disk 'disk3' size 500M;
Undo remove disk group	alter database diskgrpA undrop disks;
Display diskgroup info	select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup; select group_number, disk_number, name, failgroup, create_date, path, total_mb from v$asm_disk; select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
Rebalance a diskgroup (after disk failure and disk has been replaced)	alter diskgroup diskgrpA rebalance power 8; Note: to speed up rebalancing increase the level upto 11, remember that this will also decrease performance, you can also use the wait parameter this will hold the commandline until it is finished
Dismount or mount a diskgroup	alter diskgroup diskgrpA dismount; alter diskgroup diskgrpA mount;
Check a diskgroups integrity	alter diskgroup diskgrpA check all;
Diskgroup Directory	alter diskgroup diskgrpA add directory '+diskgrpA/dir1' Note: this is required if you use aliases when creating databse files i.e '+diskgrpA/dir/control_file1'
adding and drop aliases	alter diskgroup diskgrpA add alias '+diskgrpA/dir/second.dbf' for '+diskgrpB/datafile/table.763.1'; alter diskgroup diskgrpA drop alias '+diskgrpA/dir/second.dbf'
Drop files from a diskgroup	alter diskgroup diskgrpA drop file '+diskgrpA/payroll/payroll.dbf';
Using ASM Disks
Examples of using ASM disks	create tablespace test datafile ‘+diskgrpA’ size 100m; alter tablespace test add datafile ‘+diskgrpA’ size 100m; alter database add logfile group 4 ‘+dg_log1’,’+dg_log2’ size 100m; alter system set log_archive_dest_1=’location=+dg_arch1’; alter system set db_recovery_file_dest=’+dg_flash’;
Display performance	select path, reads, writes, read_time, write_time,        read_time/decode(reads,0,1,reads) "AVGRDTIME",        write_time/decode(writes,0,1,writes) "AVGWRTIME" from v$asm_disk_stat;

RMAN backup

RMAN is the only way to backup ASM disks.

Backup

backup as copy database format ‘+dgroup1’

Oracle Table spaces

Tablespaces are used to organize tables and indexes into manageable groups, tablespaces themselves are made up of one for more data/temp files.

Oracle has 4 different types of tablespace

• Permanent - uses data files and normally contains the system (data dictionary) and users data
• Temporary - is used to store objects for the duration of a users session, temp files are used to create temporary tablespaces
• Undo - is a permanent type of tablespace that are used to store undo data which if required would undo changes of data by users
• Read only - is a permanent tablespace that can only be read, no writes can take place, but the tablespace can be made read/write.

Every oracle database has at least two tablespaces

• System - is a permanent tablespace and contains the vital data dictionary (metadata about the database)
• Sysaux - is an auxiliary tablespaces and contains performance statistics collected by the database.

Tablespace Management

There are two ways to manage a tablespace

Locally (default)

Extents are the basic unit of a tablespace and are managed in bitmaps that are kept within the data file header for all the blocks within that data file. For example, if a tablespace is made up of 128KB extents, each 128KB extent is represented by a bit in the extent bitmap for this file, the bitmap values indicate if the extent is used or free. The bitmap is updated when the extent changes there is no updating on any data dictionary tables thus increasing performance. Extents are tracked via bitmaps not using recursive SQL which means a performance improvement. Locally managed tablespaces cannot be converted into a dictionary managed one. The benefits of using a local managed tablespace relieves contention on the system tablespace free extents are not managed by the data dictionary no need to specify storage parameters

Dictionary Managed

The extent allocation is managed by the data dictionary and thus updating the extent information requires that you access the data dictionary, on heavy used systems this can cause a performance drop. extents are tracked via FET$ and UET$ using recursive SQL. Dictionary managed tablespaces can be converted to a locally managed one.

There are a number of things that you should know about tablespaces.

• Local tablespaces are the default in oracle 10g
• A dictionary tablespace can be changed into a local table but a local tablespace cannot be changed into a dictionary one
• If the system tablespace is locally managed then you can only create locally managed tablespaces, trying to create a dictionary one will fail
• Local tablespaces are better in performance than dictionary managed tablespaces as you have to constantly check the data dictionary during the course of extent management (called recursive SQL).

Extent Management

Anytime an object needs to grow in size space is added to that object by extents. When you are using locally managed tablespaces there are two options that the extent size can be managed

Autoallocate (default)	This means the extent will vary in size, the first extent starts at 64k and progressively increased to 64MB by the database. The database automatically decides what size the new extent will be based on segment growth patterns. Autoallocate is useful if you aren't sure about growth rate of an object and you let oracle decide.
Uniform	Create the extents the same size by specifying the size when create the tablespace. This is default for temporary tablespace but not available for undo tablespaces. Be careful with uniform as it can waste space, use this option you are know what the growth rate of the objects are going to be.

Segment Space Management

Segment space management is how oracle deals with free space with in an oracle data block. The segment space management you specify at tablespace creation time applies to all segments you later create in the tablespace.

Oracle uses two methods to deal with free space

Manual

Oracle manages the free space in the data blocks by using free lists and a pair of storage parameters PCTFREE and PCTUSED. When the block reaches the PCTUSED percentage the block is then removed from the freelist, when the block falls below the PCTFREE threshold the block is then placed back on the freelist. Oracle has to perform a lot of hard work maintaining these lists, a slow down in performance can occur when you are making lots of changes to the blocks as Oracle needs to keep checking the block thresholds.

Automatic (default)

Oracle does not use freelist when using automatic mode, Instead oracle uses bitmaps. A bitmap which is contained in a bitmap block, indicates whether free space in a data block is below 25%, between 25%-50%, between 50%-75% or above 75%. For an index block the bitmaps can tell you whether the blocks are empty or formatted. Bitmaps do use additional space but this is less than 1% for most large objects. The performance gain from using automatic segment management can be quite striking.

Permanent Tablespaces

Tablespaces can be either small tablespaces or big tablespaces

• Small tablespace - The tablespace can be made up of a number of data files each of which can be quite large in size
• Big tablespace - The tablespace will only be made up of one data file and this can get extremely large.

Tablespace commands

Creating	create tablespace test datafile 'c:\oracle\test.dbf' size 2G; create tablespace test datafile 'c:\oracle\test.dbf' size 2G extent management local uniform size 1M maxsize unlimited; create bigfile tablespace test datafile 'c:\oracle\bigfile.dbf' 2G;
Creating non-standard block size	create tablespace test datafile 'c:\oracle\test.dbf' size 2G blocksize 8K;
Removing	drop tablespace test; drop tablespace test including contents and datafiles; (removes the contents and the physical data files)
Modifying	alter tablespace test rename to test99; alter tablespace test [offline|online]; alter tablespace test [readonly|read write]; alter tablespace test [begin backup | end backup]; Note: use v$backup to see tablespace is in backup mode (see below)
Adding data files	alter tablespace test add datafile 'c:\oracle\test02.dbf' 2G;
Dropping data files	alter tablespace test drop datafile 'c:\oracle\test02.dbf';
Autoextending	See Datafile commands below
Rename a data file	alter tablespace test rename datafile 'c:\oracle\test.dbf' to 'c:\oracle\test99.dbf';
Tablespace management	create tablespace test datafile 'c:\oracle\test.dbf' size 2G extent management manual;
Extent management	create tablespace test datafile 'c:\oracle\test.dbf' size 2G uniform size 1M maxsize unlimited;
Segment Space management	create tablespace test datafile 'c:\oracle\test.dbf' size 2G segment space management manual;
Display default tablespace	select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
Set default tablespace	alter database default tablespace users;
Display default tablespace type	select property_value from database_properties where property_name = 'DEFAULT_TBS_TYPE';
Set default tablespace type	alter database set default bigfile tablespace; alter database set default smallfile tablespace;
Get properties of an existing tablespace	set long 1000000 select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;
Free Space	select tablespace_name, round(sum(bytes/1024/1024),1) "FREE MB" from dba_free_space group by tablespace_name;
Display backup mode	select tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id = b.file#;
Useful Views
DBA_TABLESPACES	describes all tablespaces in the database
DBA_DATA_FILES	describes database files
DBA_TABLESPACE_GROUPS	describes all tablespace groups in the database
DBA_SEGMENTS	describes the storage allocated for all segments in the database
DBA_FREE_SPACE	describes the free extents in all tablespaces in the database
V$TABLESPACE	displays tablespace information from the control file
V$BACKUP	displays the backup status of all online datafiles
DATABASE_PROPERTIES	lists Permanent database properties

Datafile Commands

Resizing	alter database datafile 'c:\oracle\test.dbf' resize 3G;
Offlining	alter database datafile 'c:\oracle\test.dbf' offline; Note: you must offline the tablespace first
Onlining	alter database datafile 'c:\oracle\test.dbf' online;
Renaming	alter database rename file 'c:\oracle\test.dbf' to 'c:\oracle\test99.dbf';
Autoexend	alter database datafile 'c:\oracle\test.dbf' autoextend on; alter database datafile 'c:\oracle\test.dbf' autoextend off; select file_name, autoextensible from dba_data_files;

If you create tablespaces with non-standard block sizes you must set the DB_nK_CACHE_SIZE parameter, there are 5 nonstandard sizes 2k, 4k, 8k, 16k and 32k. The DB_CACHE_SIZE parameter sets the default block size for all new tablespace if the block size option is emitted.

Temporary tablespaces

Temporary tablespaces are used for order by, group by and create index. It is required when the system tablespace is locally managed. In oracle 10g you can now create temporary tablespace groups which means you can use multiple temporary tablespaces simultaneously.

The benefits of using a temporary tablespace group are

• SQL queries are less likely to run out of space
• You can specify multiple default temporary tablespaces at the db level
• Parallel execution can utilize multiple temporary tablespaces
• single user can simultaneously use multiple temp tablespaces in different sessions.

Temporary tablespace commands

Creating non temp group	create temporary tablespace temp tempfile 'c:\oracle\temp.dbf' size 2G autoextend on;
Creating temp group	create temporary tablespace temp tempfile 'c:\oracle\temp.dbf' size 2G tablespace group '';
Adding to temp group	alter tablespace temp02 tablespace group tempgrp; Note: if no group exists oracle will create it
Removing from temp group	alter tablespace temp02 tablespace group '';
Displaying temp groups	select group_name, tablespace_name from dba_tablespace_groups;
Make user use temp group	alter user vallep temporary tablespace tempgrp;
Display default temp tbs	select property_value from database_properties where property_name = 'DEFAULT_TEMPORARY_TABLESPACE'; select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
set default temp tbs	alter database default temporary tablespace temp02;
Display free temp space	select tablespace_name, sum(bytes_used), sum(bytes_free) from v$temp_space_header group by tablespace_name;
Who is using temp segments	SELECT b.tablespace,   ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",   a.sid||','||a.serial# SID_SERIAL,   a.username,   a.program FROM sys.v_$session a,   sys.v_$sort_usage b,   sys.v_$parameter p WHERE p.name = 'db_block_size'   AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks;
Useful Tables
DBA_TEMP_FILES	describes database temporary files
DBA_TABLESPACE_GROUPS	describes all tablespace groups in the database
V$SORT_SEGMENT	contains information about every sort segment in a given instance. The view is only updated when the tablespace is of the temporary type
V$TEMPSEG_USAGE	describes temporary segment usage

See tables for more information on temporary tables.

Undo Tablespaces

Undo tablespaces are used to store original data after it has been changed, if a user decides to rollback a change the information in the undo tablespace is used to put back the data in its original state.

Undo tablespaces are used for the following

• Rolling back transactions explicitly with a ROLLBACK command
• Rolling back transactions implicitly (automatic instance recovery)
• Reconstructing read-consistent image of data
• Recovering from logical corruptions

Creating	create undo tablespace undotbs02 datafile ' c:\oracle\undo01.dbf' size 2G;
set default	alter system set undo_tablespace='undotbs02';

See undo for more information.

Tablespace quotas

You can assign a user tablespace quota thus limiting to a certain amount of storage space within the tablespace. By default a user has none when the account is first created, see users for information on tablespace quotas.

Tablespace Alerts

The MMON daemon checks tablespace usage every 10 mins to see if any thresholds have been exceeded and raises any alerts. There are two types of alerts warning (low space warning) and critical (action should be taken immediately). Both thresholds can be changed via OEM or DBMS_SERVER_ALERT package.

Oracle Managed Files

Oracle can make file handling a lot easier by managing the oracle files itself, there are three parameters that can be set so that oracle will manage the data, temp, redo, archive and flash logs for you

• DB_CREATE_FILE_DEST - sets the default location of the data/temp files
• DB_CREATE_ONLINE_LOG_DEST_n - sets the default location of the redo, archived log files and controlfiles.
• DB_RECOVERY_FILE_DEST - sets the default location of the flashback logs.

setting db_create_file_dest	alter system set db_create_file_dest=':c\oracle\data' scope=both;
setting db_create_online_log_dest	alter system set db_create_online_log_dest_n='c:\oracle\archive' scope=both;
Creating	create tablespace user01;
Removing	drop tablespace user01;
Adding datafile	alter tablespace user01 add datafile 1G;

Tablespace Logging

Tablespace logging can be overridden by logging specification at the table-level.

Oracle Database Architecture overview

There are two terms that are used with Oracle

• Database - A collection of physical operating system files
• Instance - A set of Oracle processes and a SGA (allocation of memory)

These two are very closely related but a database can be mounted and opened by many instances. An instance may mount and open only a single database at any one point in time.
The File Structure
The are a number of different file types that make up a database

• Parameter File - These files tells Oracle were to find the control files. Also they detail how big the memory area will be, etc
• Data Files - These hold the tables, indexes and all other segments
• Temp Files - used for disk-based sorting and temporary storage
• Redo Log Files - Our transaction logs
• Undo log files - allows a user to rollback a transaction and provides read consistency.
• Archive Log Files - Redo log files which have been archived
• Control File - Details the location of data and log files and other relevant information about their state.
• Password File - Used to authenticate users logging in into the database.
• Log files - alert.log contains database changes and events including startup information.
• trace files - are debugging files.

Parameter Files
In order for Oracle to start it needs some basically information, this information is supplied by using a parameter file. The parameter file can be either a pfile or a spfile:

• pfile - a very simple plain text file which can be manually edited via vi or notepad
• spfile - a binary which cannot be manually edited (Oracle 9i or higher required)

The parameter file for Oracle is the commonly know file init.ora or init<oracle sid>.ora, the file contains key/value pairs of information that Oracle uses when starting the database. The file contains information such as database name, caches sizes, location of control files, etc.
By Default the location of the parameter file is

• windows - $ORACLE_ HOME\database
• unix - $ORACLE_ HOME/dbs

The main difference between the spfile and pfile is that instance parameters can be changed dynamically using a spfile, where as you require a instance reboot to load pfile parameters.
To convert the file from one of the other you can perform the following

create pfile using a spfile	create pfile='c:\oracle\pfile\initD10.ora' from spfile;
startup db using pfile	startup pfile='c:\oracle\pfile\initD10.ora';
create spfile using a pfile	create spfile from pfile;
Display spfile location	show parameter spfile

Data Files
By Default Oracle will create at least two data files, the system data file which holds the data dictionary and sysaux data file which non-dictionary objects are stored, however there will be many more which will hold various types of data, a data file will belong to one tablespace only (see tablespaces for further details).
Data files can be stored on a number of different filesystem types

• Cooked - these are normally filesystems that can be accessed using "ls" commands in unix
• Raw - these are raw disk partitions which cannot be viewed, normally used to avoid filesystem buffering.
• ASM - automatic storage management is Oracle new database filesystem (see asm for further details).
• Clustered FS - this is a special filesystem used in Oracle RAC environments.

Data files contain the following

• Segments - are database objects, a table, a index, rollback segments. Every object that consumes space is a segment. Segments themselves consist of one or more extents.
• Extents - are a contiguous allocation of space in a file. Extents, in turn, consist of data blocks
• Blocks - are the smallest unit of space allocation in Oracle. Blocks normally are 2KB, 4KB, 8KB, 16KB or 32KB in size but can be larger.

The relationship between segments, extents and blocks looks like this

" height="200" width="400">
The parameter DB_BLOCK_SIZE determines the default block size of the database. Determining the block size depends on what you are going to do with the database, if you are using small rows then use a small block size (oracle recommends 8KB), if you are using LOB's then the block size should be larger.

2KB or 4KB	OLTP - online transaction processing database would benefit from a small block size
8KB (default)	Most databases would be OK to use the default size
16KB or 32KB	DW - data warehouses, media database would benefit from a larger block size
Notes
You can have different block sizes within the database, each tablespace having a different block size depending on what is stored in the tablespace. For an example System tablespace could use the default 8KB and the OLTP tablespace could use a block size of 4KB. There are few parameters that cannot be changed after installing Oracle and the DB_BLOCK_SIZE is one of them, so make sure to select the correct choice when installing Oracle.

A data block will be made up of the following, the two main area's are the free space and the data area.

Header	contains information regarding the type of block (a table block, index block, etc), transaction information regarding active and past transactions on the block and the address (location) of the block on the disk
Table Directory	contains information about the tables that store rows in this block
Row Directory	contains information describing the rows that are to be found on the block. This is an array of pointers to where the rows are to be found in the data portion of the block.
Block overhead	The three above pieces are know as the Block Overhead and are used by Oracle to manage the block itself.
Free space	available space within the block
Data	data within the block

Tablespaces
A tablespace is a container which holds segments. Each and every segment belongs to exactly one tablespace. Segments never cross tablespace boundaries. A tablespace itself has one or more files associated with it. An extent will be contained entirely within one data file.
So in summary the Oracle hierarchy is as follows:

• A database is made up of one or more tablespaces
• A tablespace is made up of one or more data files, a tablespace contains segments
• A segment (table, index, etc) is made up of one or more extents. A segment exists in a tablespace but may have data in many data files within a tablespace.
• An extent is a continuous set of blocks on a disk. An extent is in a single tablespace and is always in a single file within that tablespace.
• A block is the smallest unit of allocation in the database. A block is the smallest unit of i/o used by the database.

The minimum tablespaces required are the system and sysaux tablespace, the following reasons are why tablespaces are used.

• Tablespaces make it easier to allocate space quotas to users in the database
• Tablespaces enable you to perform partial backups and recoveries based on the tablespace as a unit
• Tablespaces can be allocated to different disks and controllers to improve performance
• You can take tablespaces offline without affecting the entire database
• You can import and export specific application data by using the import and export utilities at the tablespace.

There are a number of types that a tablespace can be

• Bigfile tablespaces, will have only one file which can range from 8-128 terabytes.
• Smallfile tablespaces (default), can have multiple files but the files are smaller than a bigfile tablespace.
• Temporary tablespaces, contain data that only persists for the duration a users session, used for sorting
• Permanent tablespaces, any tablespace that is not temporary one.
• Undo tablespaces, Oracle uses this to rollback or undo changes to the db.
• Read-only, no write operations are allowed.

See tablespaces for detailed information regarding creating, resizing, etc
Temp Files
Oracle will use temporary files to store results of a large sort operations when there is insufficient memory to hold all of it in RAM. Temporary files never have redo information (see below) generated for them, although they have undo information generated which in turns creates a small amount of redo information. Temporary data files never need to be backed up ever as they cannot be restored.
Redo log files
All the Oracle changes made to the db are recorded in the redo log files, these files along with any archived redo logs enable a dba to recover the database to any point in the past. Oracle will write all committed changes to the redo logs first before applying them to the data files. The redo logs guarantee that no committed changes are ever lost. Redo log files consist of redo records which are group of change vectors each referring to specific changes made to a data block in the db. The changes are first kept in the redo buffer but are quickly written to the redo log files.
There are two types of redo log files online and archive. Oracle uses the concept of groups and a minimum of 2 groups are required, each group having at least one file, they are used in a circular fashion when one group fills up oracle will switch to the next log group.
See redo on how to configure and maintain the log files.
Archive Redo log
When a redo log file fills up and before it is used again the file is archived for safe keeping, this archive file with other redo log files can recover a database to any point in time. It is best practice to turn on ARCHIVELOG mode which performs the archiving automatically.
See redo on how to enable archiving and maintain the archive log files.
Undo File
When you change data you should be able to either rollback that change or to provide a read consistent view of the original data. Oracle uses undo data (change vectors) to store the original data, this allows a user to rollback the data to its original state if required. This undo data is stored in the undo tablespace. See undo for further information.
Control file
The control is one of the most important files within Oracle, the file contains data and redo log location information, current log sequence numbers, RMAN backup set details and the SCN (system change number - see below for more details). This file should have multiple copies due to it's importance. This file is used in recovery as the control file notes all checkpoint information which allows oracle to recover data from the redo logs. This file is the first file that Oracle consults when starting up.
The view V$CONTROLFILE can be used to list the controlfiles, you can also use the V$CONTROLFILE_RECORD_SECTION to view the controlfile's record structure.
You can also log any checkpoints while the system is running by setting the LOG_CHECKPOINTS_TO_ALERT to true.
See recovering critical files for more information.
Password file
This file optional and contains the names of the database users who have been granted the special SYSDBA and SYSOPER admin privilege.
Log files
The alert.log file contains important startup information, major database changes and system events, this will probably be the first file that will be looked at when you have database issues. The file contains log switches, db errors, warnings and other messages. If this file is removed Oracle creates another one automatically.
Trace Files
Traces files are debugging files which can trace background process information (LGWR, DBWn, etc), core dump information (ora-600 errors, etc) and user processing information (SQL).
Oracle Managed Files
The OMF feature aims to set a standard way of laying out Oracle files, there is no need to worry about file names and the physical location of the files themselves. The method is suited in small to medium environments, OMF simplifies the initial db creation as well as on going file management.
System Change (Commit) Number (SCN)
The SCN is an important quantifier that oracle uses to keep track of its state at any given point in time. The SCN is used to keep track of all changes within the database, its a logical timestamp that is used by oracle to order events that have occurred within the database. SCN's are increasing sequence numbers and are used in redo logs to confirm that transactions have been committed, all SCN's are unique. SCN's are used in crash recovery as the control maintains a SCN for each data file, if the data files are out of sync after a crash oracle can reapply the redo log information to bring the database backup to the point of the crash. You can even take the database back in time to a specific SCN number (or point in time).
Checkpoints
Checkpoints are important events that synchronize the database buffer cache and the datafiles, they are used with recovery. Checkpoints are used as a starting point for a recovery, it is a framework that enables the writing of dirty blocks to disk based on a System Change or Commit Number (for SCN see above) and a Redo Byte Address (RBA) validation algorithm and limits the number of blocks to recover.
The checkpoint collects all the dirty buffers and writes them to disk, the SCN is associated with a specific RBA in the log, which is used to determine when all the buffers have been written.

Oracle 10g

Oracle

As I have only just started my journey into the Oracle 10g DBA world, I wanted a site that covers the basics in order for me to gain my OCP and help me in remembering the vast information that comes with learning Oracle. As my journey continues the site will hopefully become more advanced and have more specialized areas such Data Guard, Oracle RAC, etc. I plan on creating cheat sheets that cuts out all the fluff for each area were appropriate but in the mean time a very good site can be located here.

The site has been comprised of reading the following books and real world experience, if you are new to Oracle I highly recommend that you should purchase these books as they contain far more information than this web site contains and of course the Oracle web site contains all the documentation you will ever need.

Data Modeling

	Relational Databases (Introduction)
	Normalization (Forms, ER Modeling)

Architecture

	Database Physical Structure (Data Blocks, Extents, Segments, Tablespace's)
	Oracle Processes (background processes)
	Database Memory Structure (SGA, PGA)
	Oracle Files (Control file, Redo log, pfile, spfile, password, alert log, trace files)
	Oracle OEM (Enterprise Manager)

Transactions Management, Locking and Concurrency

	Transaction Management
	Locking and Concurrency (Isolation, Lock Types, Multicurrent versioning)

Undo and Transaction Management

	Redo
	Undo (AUM)
	Flashback architecture
	Resumable space allocation

Installing, Creating and Upgrading databases

	Installing Oracle
	Creating a Database (Manually, DCBA)
	Upgrading a Database (Manually, DBCA)

Controlling Oracle

	Starting and Stopping Oracle (startup, shutdown)
	Operation Modes (restrict,quiesce)
	Obtaining Database information (database/instance info, version, etc)
	Oracle Default Port Numbers
	Database Control Agents

Tablespace Management

Tablespace's (types, creating, altering, dropping)

Schema and User Management

	Schema Management
	Users (creating, altering, dropping, quotas, profiles)
	Data Access (system privileges, object privleges, roles

Tables and Indexes

	Oracle Data Types (char, number, datatime, etc)
	Tables (heap-organized)
	Special Tables (temporary, IOT, clustered, partitioned)
	Indexes (bitmap, reverse-key, function-based, partitioned)
	Integrity Constraints (primary key, not null, check, unique, referential integrity)

View, Materialized Views, Sequences, Synonyms and Triggers

	Views
	Materialized Views
	Sequences
	Synonyms
	Triggers (DDL, DML, Compound, Instead-of, System Triggers)

Connectivity

	Networking and Connectivity architecture (dedicated, shared server)
	Listener

Users

	User Management
	Accessing Data (system privileges, object privileges, roles)
	Resource Management (resource plans, resource consumer groups)
	Fine-Grained Data Access (VPD)
	Auditing the Database (standard, FGA)

Data Loading

	Extraction, Transformation and Loading (introduction)
	SQL Loader
	External Tables
	Transforming Data
	Oracle Streams
	Data Pump Export and Import
	Transportable Tablespace's

Backups and Data Recovery

	RMAN backups
	Database Recovery (non-critical/critical files, incomplete recovery, user errors, flash recovery)
	Database Corruption (block corruption)

Operational Management

	Automatic Database Diagnostic Monitor (ADDM)
	Automatic Shared Memory Management (ASMM - SGA)
	Automatic Optimizer Statistics
	Automatic Storage Management (ASM)
	Automatic Segment Space Management (ASSM)

Managing and Monitoring the Database

	Performance Statistics
	Automatic Workload Repository (AWR)
	Server Alerts (ADDM)
	Active Session History (ASH)
	Management Advisory Framework (Oracle Advisors)
	Managing Database Links
	Oracle Scheduler

Performance Tuning

	SQL Optimization
	Join Methods (Natural, Inner and Outer)
	Tuning the Instance
	Performance Tuning Tools

Data Dictionary, Dynamic Views and Supplied Packages

	Oracle Data Dictionary
	Oracle PL/SQL Supplied Packages

SQL and PL/SQL

	Oracle Data Types (char, number, datetime, etc)
	Object Types (basics, getters and setters, static member methods, comparing objects, inheritance and polymorphism)
	SQL Primer (DDL, DQL, DML and DCL)
	PL/SQL Code Basics (Procedures, Functions, Exceptions, Bind Variables, Variable Types, Variable Scope, Conditional Logic)
	PL/SQL and SQL (Cursors)
	PL/SQL Packages (Variables, scope, definer v invoker rights)
	PL/SQL Advanced Programming (Dynamic SQL - NDS, DBMS_SQL Package)
	Collections (varrays, nested tables, associative arrays, bulk collect, forall)
	Large Objects (CLOB, NCLOB, BLOB and BFILES)
	Intersession Communications (dbms_pipe, dbms_alert)
	External Procedures (C and Java)
	Oracle Regular Expressions (REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR)

Books

OCA & OCP Exam Books
Sybex OCP Oracle 10g Admin I/II	Nice easy reading and good books to get you through the exams
Osbourne OCP Certification all-in-one exam guide	I always like two books on the same subject, so this book compliments the one above.
Advanced Oracle Books
Oracle Database 11g PL/SQL Programming - Michael McLaughlin	This book is more on the development side, it includes lots of the new 11g features and is one of the best books i have read, it explains things clearly without to much waffle (straight to the point).
Apress Expert Oracle Database 10G Administration - Sam.R.Alapati	This book is from the view of a Oracle DBA admin, although a heavy going book, I highly recommend it.
Apress Expert Oracle Database Architecture 9i and 10g programming techniques and solutions - Thomas Kyte	This book is more from the developer side and compliments the above book, this book is the next version of the excellent 'Expert one-to-one' series.
Osbourne Oracle 10g The Complete Reference - Kevin Loney	For a Oracle junior DBA this book is a good book to have on your desktop
Osbourne Oracle database 10g performance tuning Tips and Techniques - Richard Niemiec	This is an excellent book for tuning the database, has a excellent chapter on the mysterious x$ tables (advanced Oracle stuff).

Oracle Normalization

Normalization is simply breaking down tables to achieve efficiency in retrieving and maintaining data. The most common reason is to avoid redundancy which means less storage is required. Normalization also helps in avoiding data anomalies.

Data anomalies can add to problems with large consumption of storage space, slow execution times, etc. There are 3 types of data anomalies

update anomaly	Failing to update all the occurrences of a certain attribute because of the repeating values problem.
insertion anomaly	You are prevented from inserting certain data because you are missing other pieces of information.
deletion anomaly	you could end up losing data because you are trying to remove some duplicate attributes from a customers data

There are 5 levels of simplification (forms)

1NF	There are no duplicate rows in the table Each cell is singled-valued (no repeating groups or arrays) Entries in a column are the same kind
2NF	Is that it is already in 1NF and it has no partial dependencies
3NF	is that it is already in 2NF and every non-key attribute is fully and directly dependent on the primary key (eliminate the columns that aren't dependent on the key.
BCNF	if every determinant is a primary key.
4NF	if it is in BCNF and contains no nontrivial multi valued dependencies
5NF	is defined as a relation that has no join dependency