Oracle Rdb after-image journal (.aij) files contain a wealth
of useful information about the history of transactions in a
database. After-image journal files contain all of the data
needed to perform database recovery. These files record every
change made to data and metadata in the database. The LogMiner
for Rdb feature provides an interface to the data record contents
of Oracle Rdb after-image journal files. Data records that are
added, updated, or deleted by committed transactions may be
extracted (unloaded) from the .aij files in a format suitable
for subsequent loading into another database or for use by user-
written application programs.
Oracle Rdb after-image journaling protects the integrity of your
data by recording all changes made by committed transactions to a
database in a sequential log or journal file. Oracle Corporation
recommends that you enable after-image journaling to record your
database transaction activity between full backup operations
as part of your database restore and recovery strategy. The
after-image journal file is also used to enable several database
performance enhancements (such as the fast commit, row cache, and
hot standby features).
See the Oracle Rdb7 Guide to Database Maintenance for more
information about setting up after-image journaling.
To use LogMiner for Rdb, follow these steps:
1. Enable the database for LogMiner operation using the RMU Set
Logminer command. See Set Logminer for additional information.
2. Back up the after-image journal file using the Quiet_Point
qualifier to the RMU Backup command.
3. Extract changed records using the RMU Unload After_Journal
command. See the Unload After_Journal help topic for
additional information.
1 – Restrictions and Limitations
The following restrictions exist for the LogMiner for Rdb
feature:
o Temporary tables cannot be extracted. Modifications to
temporary tables are not written to the after-image journal
file and, therefore, are not available to LogMiner for Rdb.
o Optimized after-image journal files cannot be used as input
to the LogMiner for Rdb. Information needed by the RMU Unload
After_Journal command is removed by the optimization process.
o Records removed from tables using the SQL TRUNCATE TABLE
statement are not extracted. The SQL TRUNCATE TABLE statement
does not journal each individual data record being removed
from the database.
o Records removed by dropping tables using the SQL DROP TABLE
statement are not extracted. The SQL DROP TABLE statement does
not journal each individual data record being removed from the
database.
o Tables that use the vertical record partitioning (VRP) feature
cannot be extracted using LogMiner for Rdb. LogMiner software
currently does not detect these tables. A future release
of Oracle Rdb will detect and reject access to vertically
partitioned tables.
o Segmented string data (BLOB) cannot be extracted using
LogMiner for Rdb. Because the segmented string data is
related to the base table row by means of a database key,
there is no convenient way to determine what data to extract.
Additionally, the data type of an extracted column is changed
from LIST OF BYTE VARYING to BIGINT. This column contains the
DBKEY of the original BLOB data. Therefore, the contents of
this column should be considered unreliable.
o COMPUTED BY columns in a table are not extracted. These
columns are not stored in the after-image journal file.
o VARCHAR fields are not space padded in the output file. The
VARCHAR data type is extracted as a 2-byte count field and a
fixed-length data field. The 2-byte count field indicates the
number of valid characters in the fixed-length data field. Any
additional contents in the data field are unpredictable.
o You cannot extract changes to a table when the table
definition is changed within an after-image journal file.
Data definition language (DDL) changes to a table are not
allowed within an .aij file being extracted. All records in an
.aij file must be the current record version. If you are going
to perform DDL operations on tables that you wish to extract
using the LogMiner for Rdb, you should:
1. Back up your after-image journal files.
2. Extract the .aij files using the RMU Unload After_Journal
command.
3. Make the DDL changes.
o Do not use the OpenVMS Alpha High Performance Sort/Merge
utility (selected by defining the logical name SORTSHR to
SYS$SHARE:HYPERSORT) when using LogMiner for Rdb. HYPERSORT
supports only a subset of the library sort routines that
LogMiner requires. Make sure that the SORTSHR logical name
is not defined to HYPERSORT.
2 – Information Returned
LogMiner for Rdb appends several output fields to the data
fields, creating an output record. The output record contains
fixed-length fields in a binary data format (that is, integer
fields are not converted to text strings). The data fields
correspond to the extracted table columns. This information
may or may not be required by all applications and readers of
the data. There is currently no available method to restrict or
reorder the output fields.
Extracted data field contents are the fields that are actually
stored in the Oracle Rdb database. COMPUTED BY fields are not
extracted because they are not stored in the database or in the
after-image journal file. Segmented string (BLOB) contents are
not extracted.
Output Fields describes the output fields and data types of an
output record.
Table 23 Output Fields
Byte
Field Name Data Type LengthDescription
ACTION CHAR (1) 1 Indicates record state.
"M" indicates an insert or
modify action. "D" indicates a
delete action. "E" indicates
stream end-of-file (EOF)
when a callback routine is
being used. "P" indicates
a value from the command
line Parameter qualifier
when a callback routine is
being used (see Parameter
qualifier). "C" indicates
transaction commit information
when the Include=Action=Commit
qualifier is specified.
RELATION_ CHAR (31) 31 Table name. Space padded to 31
NAME characters.
RECORD_TYPE INTEGER 4 The Oracle Rdb internal
(Longword) relation identifier.
DATA_LEN SMALLINT 2 Length, in bytes, of the data
(Word) record content.
NBV_LEN SMALLINT 2 Length, in bits, of the null
(Word) bit vector content.
DBK BIGINT 8 Records logical database key.
(Quadword) The database key is a 3-field
structure containing a 16-
bit line number, a 32-bit
page number and a 16-bit area
number.
START_TAD DATE VMS 8 Date-time of the start of the
(Quadword) transaction.
COMMIT_TAD DATE VMS 8 Date-time of the commitment of
(Quadword) the transaction.
TSN BIGINT 8 Transaction sequence number of
(Quadword) the transaction that performed
the record operation.
RECORD_ SMALLINT 2 Record version.
VERSION (Word)
Record Data Varies Actual data record field
contents.
Record NBV BIT VECTOR Null bit vector. There is
(array of one bit for each field in the
bits) data record. If a bit value
is 1, the corresponding field
is NULL; if a bit value is
0, the corresponding field
is not NULL and contains an
actual data value. The null
bit vector begins on a byte
boundary. Any extra bits in
the final byte of the vector
after the final null bit are
unused.
3 – Record Definition Prefix
An RMS file containing the record structure definition for the
output file can be used as an input file to the RMU Load command
if extracted data is to be loaded into an Oracle Rdb database.
The record description uses the CDO record and field definition
format (this is the format used by the RMU Load and RMU Unload
commands when the Record_Definition qualifier is used). The
default file extension is .rrd.
The record definition for the fields that LogMiner for Rdb
writes to the output is shown in the following example. These
fields can be manually appended to a record definition file
for the actual user data fields being unloaded. Alternately,
the Record_Definition qualifier can be used with the Table
qualifier or within an Options file to automatically create the
record definition file. This can be used to load a transaction
table within a database. A transaction table is the output that
LogMiner for Rdb writes to a table consisting of sequential
transactions performed in a database.
DEFINE FIELD RDB$LM_ACTION DATATYPE IS TEXT SIZE IS 1.
DEFINE FIELD RDB$LM_RELATION_NAME DATATYPE IS TEXT SIZE IS 31.
DEFINE FIELD RDB$LM_RECORD_TYPE DATATYPE IS SIGNED LONGWORD.
DEFINE FIELD RDB$LM_DATA_LEN DATATYPE IS SIGNED WORD.
DEFINE FIELD RDB$LM_NBV_LEN DATATYPE IS SIGNED WORD.
DEFINE FIELD RDB$LM_DBK DATATYPE IS SIGNED QUADWORD.
DEFINE FIELD RDB$LM_START_TAD DATETYPE IS DATE
DEFINE FIELD RDB$LM_COMMIT_TAD DATATYPE IS DATE
DEFINE FIELD RDB$LM_TSN DATATYPE IS SIGNED QUADWORD.
DEFINE FIELD RDB$LM_RECORD_VERSION DATATYPE IS SIGNED WORD.
4 – SQL Table Definition Prefix
The SQL record definition for the fields that LogMiner for Rdb
writes to the output is shown in the following example. These
fields can be manually appended to the table creation command
for the actual user data fields being unloaded. Alternately, the
Table_Definition qualifier can be used with the Table qualifier
or within an Options file to automatically create the SQL
definition file. This can be used to create a transaction table
of changed data.
SQL> create table MYLOGTABLE (
cont> RDB$LM_ACTION CHAR,
cont> RDB$LM_RELATION_NAME CHAR (31),
cont> RDB$LM_RECORD_TYPE INTEGER,
cont> RDB$LM_DATA_LEN SMALLINT,
cont> RDB$LM_NBV_LEN SMALLINT,
cont> RDB$LM_DBK BIGINT,
cont> RDB$LM_START_TAD DATE VMS,
cont> RDB$LM_COMMIT_TAD DATE VMS,
cont> RDB$LM_TSN BIGINT,
cont> RDB$LM_RECORD_VERSION SMALLINT ...);
5 – Segmented String Columns
Segmented string (also called BLOB or LIST OF BYTE VARYING)
column data is not extracted. However, the field definition
itself is extracted as a quadword integer representing the
database key of the original segmented string data. In generated
table definition or record definition files, a comment is added
indicating that the segmented string data type is not supported
by LogMiner for Rdb.
6 – Maintenance
Lengthy offline application or database maintenance operations
can pose a significant problem in high-availability production
environments. The LogMiner for Rdb feature can help reduce the
length of downtime to a matter of minutes.
If a back up of the database is used for maintenance operations,
the application can continue to be modified during lengthy
maintenance operations. Once the maintenance is complete,
the application can be shut down, the production system .aij
file or files can be backed up, and LogMiner for Rdb can be
used to extract changes made to production tables since the
database was backed up. These changes can then be applied (using
an application program or the trigger technique previously
described) to the new database. Once the new database has been
updated, the application can be restarted using the new database.
The sequence of events required would be similar to the
following:
1. Perform a full online, quiet-point database backup of the
production database.
2. Restore the backup to create a new database that will
eventually become the production database.
3. Perform maintenance operations on the new database. (Note that
the production system continues to run.)
4. Perform an online, quiet-point after-image journal backup of
the production database.
5. Use the RMU Unload After_Journal command to unload all
database tables into individual output files from the .aij
backup file.
6. Using either the trigger technique or an application program,
update the tables in the new database with the changed data.
7. Shut down the production application and close the database.
8. Perform an offline, quiet-point after-image journal backup of
the production database.
9. Use the RMU Unload After_Journal command to unload all
database tables into individual output files from the .aij
backup file.
10. Using either the trigger technique or an application program,
update the tables in the new database with the changed data.
11. Start an online, quiet-point backup of the new database.
12. Change logical names or the environment to specify the new
database root file as the production database.
13. Restart the application on the new database.
Depending on the amount of application database activity, steps
4, 5, and 6 can be repeated to limit the amount of data that
needs to be applied (and the amount of downtime required) during
the final after-image journal backup and apply stage in steps 8,
9, and 10.
7 – OpenVMS Pipe
You can use an OpenVMS pipe to pass data from the RMU Unload
After_Journal command to another application (for example,
RMU Load). Do not use any options (such as the Log or Verify
qualifiers) that could cause LogMiner to send extra output to the
SYS$OUTPUT device, as that information would be part of the input
data source stream to the next pipeline segment.
You may find that the OpenVMS default size of the pipe is too
small if the records being extracted (including LogMiner fields)
are larger than 256 bytes. If the pipe is too small, increase
the SYSGEN parameters MAXBUF and DEFMBXMXMSG, and then reboot the
system.
The following example uses LogMiner for Rdb to direct output
to an OpenVMS pipe device and uses RMU Load to read the pipe
device as the input data record stream. Using the pipeline allows
parallel processing and also avoids the need for an intermediate
disk file. Note that you must have created the record definition
(.rrd) file prior to executing the command.
$ PIPE (RMU /UNLOAD /AFTER_JOURNAL OLTP.RDB AIJ1.AIJ -
/TABLE = (NAME = MYTBL, OUTPUT = SYS$OUTPUT:)) -
| (RMU /LOAD REPORTS.RDB MYLOGTBL SYS$PIPE: -
/RECORD_DEFINITION = FILE = MYLOGTBL.RRD)