Using ASM’s Cluster File System (ACFS) & Dynamic Volume Manager (ADVM) on a Single Node

Oracle 11gR2 ASM supports a new type of file called a “volume file” which may be created in its own DISK GROUP or which may share space in another disk group. Volume files are externalised to the Unix operating system by the oracleadvm device driver and appear dynamically as special files in the /dev/asm directory.

These volumes may be used as block devices, may contain a file system such as an ext3 system or the ASM cluster file system, or ACFS may be used in which case the oracleacfs driver is also used for IO to the file system.

In Single node configurations these Oracle supplied device drivers are not loaded automatically, but the system administrator may use the acfsload utility in the $ORACLE_HOME/bin directory of the Grid Infrastructure installation from where the ASM instance executes. This may be done as follows:

# /u01/app/oracle/product/11.2.0/grid/bin/acfsload start

Alternatively creating a script, which is invoked automatically by init at system startup, and setting the script as a service may automate this. The script which would be placed in the /etc/init.d directory and which could be called for example “acfsdrivers” would look something like this to perform the load silently:

#!/bin/sh

# chkconfig: 2345 30 21

# description: Load Oracle ACFS drivers at system boot

/u01/app/oracle/product/11.2.0/grid/bin/acfsload start -s

To automate this at boot time one would do the following as root:

# chmod u+x /etc/init.d/acfsdrivers

# chkconfig –add acfsdrivers

Accompanying this post is a demonstration script designed to run from SQLPLUS logged in the ASM instance on a single node running 11gR2 under Linux. In order to use it the Grid Infrastructure home owner must be added to /etc/sudoers, permitting execution of the utilities used in the demo with the sudo command, without requiring a password. In addition the name of the disk group should be changed to one that is present in your system and the name of the volume file changed to match the one created.

The demo is commented and does the following:

1.    Demonstrates the command line use of the following utilities

• “acfsload” –  to start and stop the drivers
• “advmutil” – to display ASM dynamic volume details
• “acfsutil” –   to register and unregister acfs mount points, display the “registry” contents, display acfs file system metadata and create and remove acfs snapshots.
• “asmcmd” –   to create and delete asm dynamic volumes, display volume metadata, list disk groups anddisplay volume statistics

2.    Creates an asm dynamic volume and file system

3.    Uses standard Linux utilities on these ASM components:

• “mkfs” – to create the asm file system
• “fsck”  – to check the file system
• “lsmod” – to display the loaded kernel modules
• “mount” – to mount the file system
• “umount” – to unmount the file system
• “mount.acfs” – to mount acfs file systems based on the registry

4.    Uses several new v$ views relevant to ADVM and ACFS.

You may download the script from here and the spooled output from advm_spool

Are you “Chained” to your Tables?

David Kurtz gave a very interesting and useful talk at yesterdays  UKOUG UNIX SIG concerning performance problems that he diagnosed at one of his customers. Whenever the number of logins exceeded 90 there was a correlation between that and the number of waits on “cache buffers chains latch” which he demonstrated visually by creating excel graphs from extracted AWR report data accessed via ODBC.

The diagnosis pointed to tables that had lob columns with the storage enabled in row. Most of the lob cells for most of the rows were short enough at the start to be included “in line”.  Subsequent updates to the lob cells however, sometimes caused the total length of the column to exceed 4096 bytes including the metadata thereby causing the lob data to be moved to the lob segment. This also had the effect of increasing the free space in the data block thereby allowing more rows to be inserted with short lob cells which later on could also have their data moved to the lob segment.

All of these updates caused rows to be partly contained in the data block and partly elsewhere and a discussion ensued about “migrated” rows.

But Oracle is a bit ambiguous about “Chained” rows and “Migrated” rows and so we had a discussion both during the Q&A and afterward, and I thought this topic needed to be revisited due to the ambiguity.

First lets define the terminology:

Chained Rows – Rows where the row headers indicate that the row is contained in two or more “pieces” due to:

1. A row having been inserted or updated such that the row  is larger than the maximum free space in an oracle block after accounting for the block headers. In this case the tablespace blocksize and the row length determine whether the row is chained and if so the “row pieces” are held in different blocks. Such a row uses the first byte of the row header called the flag byte to indicate which piece of the chain is in the current block and if not the last piece, then a rowid of the next piece follows the three byte row header pointing to the next piece in another block. It also uses the third byte of the row header called the column count to indicate how many columns are in each row piece.
2. A row having been inserted or updated such that the number of columns exceeds 255.  In this case the block size and row length relationship only determine whether or not different blocks are used for the pieces but there will be always be two or more “row pieces” depending on the number of columns in the table. This is known as “intra-block chaining” and has been a feature of Oracle since version 8.0 when support for more that 255 columns was added. Such a row uses the flag byte in the row header to indicate each piece of the chain and uses the column count to indicate how many columns are in this row piece.  If the row is chained only due to column count then all the row pieces will be in the same block with a maximum of four pieces and this will not be considered a “chained row” for performance reasons as only one block access is needed to fetch the row. If however the row also is too large then it will be chained as in case 1 and may have as many pieces as required.
3. A row having been updated making the row larger such that the updated version of the existing row is unable to fit into the current block due to insufficient free space. This type of chaining is known as “row migration” but is repairable as will be described later. In this case the original block will contain the original row header and the rowid pointing to where the row has moved but there will be no column data in the original block. If the row is migrated and fits into 1 block then the row header in the new block will indicate in the flag byte that there is only 1 row piece that is entirely in this block. If the row is migrated but is now also too large or has more than 255 columns or both, then the row will be both migrated and chained as described above.
4. A row in an Indexed Organised table (IOT) having an overflow segment and a row has overflowed some or all of the non-key columns. This may occur either because the row is longer than the “PCTTHRESHOLD” value which defines a limit as a percentage of the block space for storing data in IOT btree blocks. It may also occur due to the “INCLUDING” clause which specifies a cutoff column in the table after which all columns are overflowed for each row. If both clauses are used then whichever method results in a smaller row piece in the btree block will be used for that specific row.

Multi-Block rows – Rows where the row headers do NOT indicate that the row is contained in two or more “pieces” due to:

1. A row containing a lob column and either the “disable storage in row” clause was specified for the lob, or the “enable storage in row” clause was specified for the lob but the lob cell length for this row including lob metadata exceeds 4096  bytes.  In this case the lob metadata in the form of a “lob locator” points to the location of the lob data within a block of the lob segment or indirectly via the lob index segment. Note that these rows are NOT considered to be chained in the usual sense so that the “Analyze table xxx list chained rows” command will not report such a row as being chained even though multiple blocks may be required for row access. This is because the clob data is in a different segment not in another block of the same segment and because the row header will indicate in the flag byte that the entire row is in the one block. Furthermore all the columns are represented in the block but the out of line lob has only metadata in line in the block.

How can we tell that chained or migrated rows are affecting performance?

In the AWR or statspack reports or in the v$sysstat view look for the line containing statistic “table fetch continued row”. Here is an example:

select name, value from v$sysstat where name like ‘%table fetch%’;

NAME                                               VALUE

——-                                                 ——-

table fetch continued row                   9293

If the values in this statistic are rising over time then more and more rows that are “chained” in some way are being fetched causing extra latching and block pinning as well as extra I/O from time to time.

To determine if certain heap tables have chained rows due to any of the first three cases listed above one may look at the DBA_TABLES.CHAIN_CNT column but only if you use the ANALYZE command for statistics. Since best practice is to use DBMS_STATS, the solution is as follows:

1. Run the $ORACLE_HOME/rdbms/admin/utlchain.sql script
2. From the same user do “Analyze table xxx list chained rows” where xxx is the name of the table in question.
3. Query the chained_rows table to see details of any row in the table that has row pieces in multiple blocks.
4. Determine if repair or migrated rows is a useful activity

The HEAD_ROWID column contains the rowid for the start of the chain IE the rowid points to the block where the first 3 byte row header resides. If the row is migrated as in case three above then the entire row may be in one or more other blocks as mentioned.

If the row is chained due to either case 1 or 2 above, then the HEAD_ROWID points to the location of the first row piece. Subsequent row pieces may or may not be in the same block depending on the circumstances outlined above.

Only migrated rows as in case 3 may be fixed.  Since a migrated row remains migrated in order to preserve the rowid for index use, the only way to repair this is to delete and then reinsert all the “chained” rows. This would be useful if the average row length of the table is much smaller than the block size or if by describing the table one can determine that the maximum row length is smaller than the block size. In these cases many or most if not all the “chained” rows reported by ANALYZE .. LIST CHAINED ROWS command will be the migrated type which can be repaired.

To effect the repair of a table called TABA do the following:

1. Disable any triggers on the table.
2. Disable any constraints on the table.
3. Do  “CREATE TABLE TABA_TEMP AS SELECT * FROM TABA WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS);” to copy the chained rows to a holding table.
4. Do “DELETE FROM TABA WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS);” to delete the chained rows.
5. Then do “INSERT INTO TABA SELECT * FROM TABA_TEMP” to reinsert the rows as new rows in the table. As they are new they will be handled as any other rows are upon insert and the chained rows caused by the migration process listed above will now no longer be chained.
6. Delete the holding table
7. Re-enable the constrains if any exist
8. Re-enable the triggers if any exist.

To determine if IOTs have chained rows due to row pieces that are in an IOT overflow segment one may look at the DBA_TABLES.CHAIN_CNT column but only if you use the ANALYZE command for statistics. Since best practice is to use DBMS_STATS, the solution is as follows:

1. Run the $ORACLE_HOME/rdbms/admin/utlchn1.sql script. This script differs from the ultchain.sql script by creating a CHAINED_ROWS table with a UROWID data type instead of a ROWID data type in the HEAD_ROWID column to support the universal rowid format used for IOT chaining and IOT secondary indexes.
2. Then do an “ANALYZE TABLE xxx LIST CHAINED ROWS” as described earlier.
3. Then do a “SELECT * FROM CHAINED_ROWS“.

An alternative is to:

1. Use the “DBMS_IOT.BUILD_CHAIN_ROWS_TABLE“  procedure
2. Do the “ANALYZE TABLE xxx LIST CHAINED ROWS INTO IOT_CHAINED_ROWS;“
3. Then do a “SELECT * FROM IOT_CHAINED_ROWS“.

Regarding tables with LOB columns, one may examine the LOB properties from DBA_LOBS or DBA_PART_LOBS using the IN_ROW column. Out of line LOBS always require visits to extra blocks which may cause more I/O and more latching. For LOBS that enable storage in row more I/O and latching may still occur. For example if the lob cells are large but stay in the block, then fewer rows will fit in the block and a random set of row accesses may require more block visits than would occur if the lobs were out of line. This is especially so if the application does not always access the lob column(s).  This is why one may disable the storage in row even if the lobs are not that big. But rows in such tables are not flagged as “chained” even though they may have data in more than one block and in fact in more than one segment.

To sumarise what DBAs may do regarding performance,  “Migrated” rows may be repaired, and one may control LOB overheads to some degree by enabling or disabling storage in row to suit access patterns, but to do all this requires that one know the data and be familiar with the applications to some extent.

Oracle Certification Exam Design

Are you preparing for your Oracle Certification tests? Or perhaps considering whether or not to sit the exams? If so then you might enjoy viewing my 3 part video series produced by my colleague Harold Green who is one of the senior Programme Managers for the Oracle Certification programme. This presentation describes the design philosophy and value of Oracle Certification tests and discusses how the development process has changed over time.
Understanding the process and the distinction between “Learner” and “Practitioner” tasks should assist anyone in preparing for OCA, OCP or OCE “Theory” style exams. If you are a DBA OCM candidate then I cover the development of “Performance” style exams separately in the presentation.

Here is Part 1 covering design philosophy:

Here is Part 2 describing the different style of questions known as “Items”:

Here is Part 3 dealing with OCM Exams.

Do You Think About Your Database Links

I have written and presented a Master Class on Database links this year to two UKOUG DBA SIGs and added it to my “Articles and Presentations” page.  This forms part one of a two part series on DB Links which covers: the various DB Link Options, Remote Queries, Performance implications for DB Links, Security using DB Links and Implications for Administration. Part two will be presented at the UKOUG annual conference and will  cover:  remote and distributed queries, remote and distributed transactions; Oracle two-phase commit processing and recovery implications of distributed systems.

If you are interested in DB Links and Distributed Systems then have a look at Database Links Master Class Part 1.

Interested in the Oracle Scheduler?

If you are working with or plan to use the Oracle Seheduler then a new book by Ronald Rood may help. I have just reviewed his book “Mastering the Oracle Scheduler in Oracle 11g Databases published by Packt Publishing. It is a useful guide to all the scheduler features upto and including Oracle 11gR1.

Here is a link to the review on my new Book Review Page.

Measuring Exadata Offload Efficiency

More customers have begun to look at Exadata since the announcement last autumn and I have been teaching the Oracle Exadata Seminar this year to both an EMEA Audience and audiences in North America.

Exadata permits intelligent offloading of certain types of IO to the Cells where it is processed locally reducing the amount of traffic on the storage network and thereby reducing contention, latency and improving performance. Exadata can offload the following:

1. Filters or where clauses on SQL statements – in this case the SQL is offloaded to the cell which reads the blocks and applies the filters to the rows before returning the results over the Exadata Infiniband Storage Interconnect to the requesting host node.

2. Column Projections on SQL statements – in this case the column list is a subset of the columns of a table and the SQL is offloaded to the cell which reads the blocks and returns only the selected columns to the requesting host node. Of course case 1 and 2 may be combined.

3. Certain Data Warehouse Joins – in this case a cell may join data from multiple tables that are joined in the downloaded SQL using the Bloom filtering technique to reduce the number of rows that are returned over the storage network to be post-processed back in the Oracle Database instance.

4. Data File Creation or Extension formatting – When Create Tablespace DDL statements are issued a  datafile is created. Likewise if a data file is added to a tablespace or a file is resized, or autoextends, then this is offloaded to the cells for formatting the blocks of the file reducing both IO and workload on the server.

5. RMAN Fast Incremental Backups- When Block Change Tracking is used with RMAN and Exadata the Cell does the incremental backups at a granularity of the individual block, rather than at the granularity of a group of blocks as is done without Exadata.

To measure these activities there are several V$ Views and here are some of the ways one may view the benefit of these offloads:

1. Look at the total offload efficiency using system stats. V$SYSSTAT lists 16 statistics involving cells and we can use some of them straight from the view and some require a calculation. Here are the stats:

cell blocks helped by commit cache
cell blocks helped by minscn optimization
cell blocks processed by cache layer
cell blocks processed by data layer
cell blocks processed by index layer
cell blocks processed by txn layer
cell blocks skipped due to chained rows
cell commit cache queries
cell physical IO bytes eligible for predicate offload
cell physical IO bytes saved during optimized RMAN file restore
cell physical IO bytes saved during optimized file creation
cell physical IO interconnect bytes
cell scans
cell simulated physical IO bytes eligible for predicate offload
cell simulated physical IO bytes returned by predicate offload
cell transactions found in commit cache

The Statistic “cell physical IO bytes saved during optimized file creation” tells us how we have benefited using Exadata for Datafile create and extend.

The Statistic “cell physical IO bytes saved during optimized RMAN file restore” is an indication of RMAN benefits using Exadata.

To determine offload benefit for SQL where clauses (filtering), Column Projections and Joins we use the “cell physical IO interconnect bytes” and “cell physical IO bytes eligible for predicate offload” statistics as follows:

select 100 – 100*s1.value/s2.value io_filtering_percentage
from v$mystat s1
, v$mystat s2
, v$statname n1
, v$statname n2
where s1.statistic# = n1.statistic#
and s2.statistic# = n2.statistic#
and n1.name = ‘cell physical IO interconnect bytes’
and n2.name = ‘cell physical IO bytes eligible for predicate offload’;

IO_FILTERING_PERCENTAGE
————————————————–
81.9404485

This measure the number of bytes that were offloaded over the Exadata iterconnect as a percentage of the nuber of bytes theoretcially eligible for predicate offload.

Another way to examine the efficiency is to look at Cell Specific Wait events to see the type, frequency and average duration of the events. There are currently 8 cell oriented wait events for which an Oracle process may wait as follows:

cell multiblock physical read
cell single block physical read
cell smart file creation
cell smart incremental backup
cell smart index scan
cell smart restore from backup
cell smart table scan
cell statistics gather

Just looking at the list is an indication of the types of offloads that may occur and for which a process may wait and this includes table scans, index fast full scans, file creation and RMAN smart Backup and restore.

To see if an instance has waited on these events, how often and for how long one may use queries which exploit the following Exadata Cell v$views:

V$CELL
V$CELL_REQUEST_TOTALS
V$CELL_STATE
V$CELL_THREAD_HISTORY

To see if any sessions are currently waiting, and for which cell:

SELECT w.event, c.cell_path, d.name, w.p3
FROM V$SESSION_WAIT w, V$EVENT_NAME e,
V$ASM_DISK d, V$CELL c
WHERE e.name LIKE ‘cell%’ AND
e.wait_class_id = w.wait_class_id AND
w.p1 = c.cell_hashval AND w.p2 = d.hash_value
order by 1;

To see the waits for the instance since startup use the following:

SELECT d.name, c.cell_path, w.event,
w.total_waits, w.time_waited, w.average_wait
FROM V$SYSTEM_EVENT w, V$ASM_DISK d, V$CELL c
WHERE w.event LIKE ‘%cell%’ AND
d.path like ‘o/%’
order by 1,3,4 desc;

To see the IO occurring from the cell log in to the “cellcli” utility on a cell and use the “list activerequest” command to see active IOs on the cell as follows:

CellCLI> list activerequest detail
name:                   0
asmDiskGroupNumber:     0
asmFileIncarnation:     0
asmFileNumber:          0
consumerGroupID:        11202
consumerGroupName:
dbID:                   3931557617
dbName:
dbRequestID:            0
fileType:
id:                     0
instanceNumber:         1
ioBytes:                1048576
ioBytesSofar:           0
ioGridDisk:             /box/predicate3
ioOffset:               0
ioReason:
ioType:                 “Predicate Pushing”
objectNumber:           -1
parentID:               0
requestState:           “Queued for Predicate Disk”
sessionID:              128
sessionSerNumber:       17
sqlID:                  7wnj4uwqw1yb2
tableSpaceNumber:       0

Another way to examine performance is to list the Cell metrics that are gathered automatically byt the cell using “cellcli“. Each performance metric has a definition which may be listed using  the “list METRICDEFINITION detail” command. An example of some metrics are:

CellCLI> list METRICDEFINITION detail
name:                   CD_IO_BY_R_LG
description:            “Number of megabytes read in large blocks from a cell disk”
metricType:             Cumulative
objectType:             CELLDISK
unit:                   MB

name:                   CD_IO_BY_R_LG_SEC
description:            “Number of megabytes read in large blocks per second from a cell disk”
metricType:             Rate
objectType:             CELLDISK
unit:                   MB/sec

To see the metrics for example for a specific grid disk do:

CellCLI> list metriccurrent where metricObjectName = ‘data_CD_disk01_cell1′
GD_IO_BY_R_LG   data_CD_disk01_cell1    55.4 MB
GD_IO_BY_R_LG_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_R_SM   data_CD_disk01_cell1    0.7 MB
GD_IO_BY_R_SM_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_W_LG   data_CD_disk01_cell1    11.9 MB
GD_IO_BY_W_LG_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_W_SM   data_CD_disk01_cell1    0.1 MB
GD_IO_BY_W_SM_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_RQ_R_LG   data_CD_disk01_cell1    56 IO requests
GD_IO_RQ_R_LG_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_R_SM   data_CD_disk01_cell1    81 IO requests
GD_IO_RQ_R_SM_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_W_LG   data_CD_disk01_cell1    24 IO requests
GD_IO_RQ_W_LG_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_W_SM   data_CD_disk01_cell1    9 IO requests
GD_IO_RQ_W_SM_SEC       data_CD_disk01_cell1    0.0 IO/sec

There are many other tools in “cellcli” which are beyond the scope of this discussion but if and when you work with exadata this is a good way to get started in performance measurement.

Oracle Certification Benefits

I presented a free lunchtime Web Seminar for Oracle EMEA today to discuss the benefits of certification to employers and employees as well as to describe the exam requirements and design process. There were some requests to access the slides so hewre they are:

>> The Benefits of Oracle Certification

I am the global technical leader of the DBA Certification team and also a lead Subject Matter Expert (SME) reviewer for the DBA, SQL, PL/SQL and Oracle on Linux exams and therefore spend a great deal of time helping and mentoring the exam developers.  In the presentation you can see the project development steps taken from the inception of the “blueprint” for an exam until the exam is ready for production.

If you have any queries please contact me here.

Skills and Training Plans for New Oracle DBAs

After co-authoring an article on Performing a DBA 1.0 to DBA 2.0 Upgrade with Harald van Breederode last year,  we had many requests to share our views on entry level Oracle DBAs who may be totally new, or who may have moved into the DBA role from Operations, Development or from other technology areas.  We have now written another article called Installing Oracle  DBA 1.0 to share our own experiences in learning Oracle and to provide advice based on our experience in the Oracle DBA Classroom.

Is Your Audit Trailing Behind?

Here is a recent twist to an old problem. The Oracle Audit Trail is written to a data dictionary table “sys.aud$” which is in the system tablespace. Over the years and releases many DBAs have wished to move “sys.aud$” (or “system.aud$” if OLS is installed) to another tablespace when auditing is heavily used to reduce system tablespace I/O. Since the advent of Fine Grain Auditing a similar desire has existed for “sys.fga_log$” which is used to contain fine grain auditing rows. Technically these are sys owned tables but do not contain metadata of the same sort as contained in other base tables and some do not consider them to be proper data dictionary tables.

Moving one or both of these tables to another tablespace is technically possible, but the situation is somewhat risky. Published technote 72460.1 explains how this is done :

1.  Restart the instance with AUDIT_TRAIL = none.

2.  Log in to SQL/PLUS as sys.

connect sys/<password>

3. Copy aud$ to system schema (or another schema) in another tablespace.

create table system.aud$ tablespace <tablespace name>as select * from aud$;

4.Create an index on the correct columns.

create index system.i_aud1 on system.aud$(sessionid, ses$tid);

5. Temporarily rename sys.aud$.

rename sys.aud$ to aud$_temp;

6. Create a view belonging to sys called aud$ based on the new aud$ table.

create view sys.aud$ as select * from system.aud$;

7. Login as system.

connect system/<password>

8. Grant appropriate privileges on the new view and table.

grant all on system.aud$ to sys with grant option;
grant delete on system.aud$ to delete_catalog_role;

But the same technote also contains other important statements:

1. That relocation of aud$ is not supported, although changing the default storage parameters except for “INITIAL” is supported.

2. That the technote was published externally because of “customer demand”.

3. Errors may occur if the tablespace containing the audit is offline.

In effect this is saying ” DBAs, you are on their own if you move aud$ but here is how to do it and it works in most cases”.

But there is good news. One of the requirements of the 10g and 11g Audit Vault feature was to provide the facility to manipulate the Audit Trail and this resulted in the creation of package DBMS_AUDIT_MGMT which provides the needed functionality for all audit trail managmenet including purging, table relocation and a few others. But use of the package outside of Audit Vault was not originally supported.  But from 11.1.0.7 some of the restrictions have been relaxed so that support now exists for moving aud$ and fga_log$ to another tablespace. Furthermore there are patches for older versions from 10.2.0.3 onwards to install this package as well.

Here is the procedure to move the audit trails:

PROCEDURE SET_AUDIT_TRAIL_LOCATION
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
AUDIT_TRAIL_TYPE               BINARY_INTEGER          IN
AUDIT_TRAIL_LOCATION_VALUE     VARCHAR2                IN

The possible values for audit_trail_type are listed below but not all these types are suitable for tablespace relocation as some are used in other procedures within the package such as when purging which is beyond the scope of this post:

1. AUDIT_TRAIL_AUD_STD  - in sys.aud$

2. AUDIT_TRAIL_FGA_STD  - in sys.fga_log$

3. AUDIT_TRAIL_DB_STD   - both 1 and 2 above

4. AUDIT_TRAIL_OS       - OS audit trail

5.  AUDIT_TRAIL_XML      - Audit in XML OS files

6. AUDIT_TRAIL_FILES    - both 4 and 5 above

7. AUDIT_TRAIL_ALL      - 1,2,4,5 above


In addion to the DBMS_AUDIT_MGMT package there are also some dictionary views and the one we are interested in is:

Current Audit location Settings:

SQL> select audit_trail,parameter_name,parameter_value
from  DBA_AUDIT_MGMT_CONFIG_PARAMS;

AUDIT_TRAIL                  PARAMETER_NAME       PARAMETER_VALUE
—————————- ——————– ——————–
FGA AUDIT TRAIL              DB AUDIT TABLESPACE  SYSAUX
STANDARD AUDIT TRAIL         DB AUDIT TABLESPACE  SYSAUX

Have a look at technote 731908.1 for full details and don’t leave your audit behind.

Recovered Yet??

In my post on Shared Sessions and Shared Servers I mentioned the new “Bits and Pieces” format at the UKOUG Technical SIGs which invited SIG delegates to speak spontaneously for as little as five minutes to share useful tips and experiences with their colleagues.

This was a nice addition to the SIG and consistant with my DBA classroom where delegates are invited to conribute if they can on my courses to everyone’s benefit.

I spoke about a few topics on that day each for a short time and the one that generated the most lively debate and discussion centred on “Backup and Recovery” strategy for distributed systems.

It began when I asked “who used DB links” which most delegates did. The next point was to distinguish between “Remote Transactions” and “Distributed Transactions” in order to see who might need to revisit their strategy.

In a “Remote Transaction” the local session updates one or more objects in a remote database over a DB link. When a “Commit” is issued by the local Database Instance, it causes a “Single Phase Commit” request to be passed over the DB link to be processed in the remote database which either commits or rolls back depending on its own internal processing requirements and which returns control to the calling instance.

In “Distributed Transactions”, two or more databases have objects updated necessitating a “Two Phased Commit” which is controlled from the Database instance where the client or middle tier is connected, also known as the “Global Coordinator”. “Two Phased” commit protocol guarantees that all participating instances will either commit or rollback and not have a mixed outcome. It is not my intention to cover the details of Oracle Database Two Phased Commit here as this is a large topic in itself which I plan to deliver later this year in a MasterClass format.

The “Backup and Recovery” strategy issue relates to situations where one of the participating databases has undergone a Database Point in Time Recovery (DBPITR) for any reason. This may have been caused by corruption, by incorrect input to a batch run, by loss of redo logs discovered during normal recovery; it does not matter really. If DBPITR is done to one of the databases, then the other databases that participate in the distributed transactions must be recovered back to the same global SCN in order for the entire distributed environment to once again be consistant.

I also told the group of a delegate on a course I delivered about four or five years ago whose jaw dropped when I mentioned this Distributed Systems Backup and recovery situation. She was a DBA for a large bank which four months prior, had performed  DBPITR  to one of three databases involved in an international distributed system but not to the other two databases. She spent much of the breaks and lunch for the next two or three days on her mobile in meetings to decide what should be done.

I asked the User Group how many of the 70 or so people using DB Links and distributed transactions had performed DBPITR in the past year. Only a couple of DBAs claimed to have done so. I then asked how many know how to perform this recovery and most were not sure or did not reply (it is an SCN based DBPITR for the other participating databases).

Furthermore one of the DBAs had a related issue where the database did not use links but stored some data on another system using AQ but where the AQ Data was not backed off when the Database had a DBPITR performed.

So the discussion then turned to listing those configurations and scenarios that we as a group could think of where coordinated Recovery is needed after a Database has DBPITR performed on it.

Here is the list we created of situations involving database transactions which include at least some updates done in some other environment outside the database instance to which the client connects:

1. Distributed Transactions Using DB Links

2. Systems updating data using UTL_FILE

3. Systems using AQ or Streams Queuing

4. Systems creating Data Pump External Tables

5. Systems making “extproc” callouts to programmes that update files

6. Systems using Bfile Lobs

7. Systems using XA for open Distributed Transactions

To me the best part of this 10 minutes was not my original idea of warning about recovery when using DB links but rather that the group as a whole then built upon this idea making an interesting discussion and expanding the original idea. But it certainly convinced many DBAs that they ought to revisit their “Backup and Recovery”  strategy. If you have any of these configurations, then  you might wish to do so as well.

Please comment if you have other scenarios to add to the list or if you have has a problem caused by the lack of a comprehensive recovery strategy.