Multiple Block Sizes

Multiple Block Sizes

9i supports multiple blocksizes within one database. The original block size specified is called the default blocksize or the standard blocksize. An Oracle database can have up to four additional blocksizes. Oracle’s allowable blocksizes are 2, 4, 8, 16, and 32 Kilobytes.

The SYSTEM and temporary tablespaces must be of the standard blocksize, and all partitions of a partitioned table must be of the same blocksize.

For each nonstandard blocksize you intend to use, you must set one initialization parameter to provide its cache. Here’s an example for an 8K blocksize:

DB_8K_CACHE_SIZE = 8M

Now you can create tablespace(s) with the new blocksize simply by appending the keywords BLOCKSIZE 8K to the CREATE TABLESPACE statement. The dictionary view DBA_TABLESPACES has the new column BLOCK_SIZE to track this parameter.

9i New for RMAN

Backup and Recovery Enhancements

Recovery Manager (RMAN) Enhancements

Block Media Recovery (BMR)

By default, RMAN’s backup/recovery method operates on the datafile level. 9i now allows you to recover individual data blocks, called block media recovery (BMR). BMR is quicker than full file recovery where you have a small number of corrupt blocks that need to be recovered. Plus you do not have to take either the datafile or the database offline to perform BMR. BMR minimizes Mean Time to Recover (MTTR).

Determine what blocks are bad from views V$BACKUP_CORRUPTION and V$COPY_CORRUPTION. Oracle writes information about block corruption in both the Alert Log and the user trace files.

Here’s the command to recover one or more data blocks:

BLOCKRECOVER DATAFILE filename_1 BLOCK b1 [, b2, b3 ...]

[ DATAFILE filename_2 BLOCK b4, b5 ... ]

Restrictions:

• You can only do BMR from RMAN (not from SQL*Plus)
• You can only do BMR off full backups (not incremental backups)
• You can only recover complete blocks

The CONFIGURE Command

The new 9i RMAN CONFIGURE command lets you permanently configure many aspects of RMAN. Configure the window for the retention policy:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Configure the redundancy policy:

CONFIGURE RENTENTION POLICY TO REDUNDANCY 5;

Configure the default backup device type:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

Clear the previous command:

CONFIGURE DEFAULT DEVICE TYPE CLEAR;

You can also use the CONFIGURE command to assign attributes to channels. As well, you can configure things so that control files are automatically backed up without having to specify the INCLUDING CONTROL FILE phrase on the BACKUP command.

You can configure to exclude certain tablespaces from backups:

CONFIGURE EXCLUDE FOR TABLESPACE my_ts ;

Append keyword CLEAR to that command to remove the exclusion.

Turn on backup optimization with:

CONFIGURE BACKUP OPTIMIZATION ON;

This skips files that already have a backup on the backup device with the same header status (they don’t need to be backed up, because the same header means the original files have not been changed).

Other RMAN Enhancements

Archivelog Backup – Include any archivelogs not yet backed up with a datafile backup by this command: BACKUP DATAFILE n PLUS ARCHIVELOG ;

Restartable Backup - Back up files that have not been backed up since a particular date and time with the new clause NOT BACKED UP [SINCE TIME...]. Example:

BACKUP DATABASE NOT BACKED UP SINCE TIME ‘sysdate – 5 ;

Smarter Restoration – Oracle only restores files whose headers do not match those of the target. This saves needlessly restoring certain files that are unchanged.

Other Improvements -

• 9i backs up tablespaces of different blocksizes in one command
• Report obsolete backups not needed in the recovery period specified: REPORT OBSOLETE RECOVERY WINDOW 7 DAYS;
• The RECOVERY WINDOW clause was added here too:

REPORT NEED BACKUP RECOVERY WINDOW 7 DAYS:

• Use the CROSSCHECK command to check if backup sets or file copies exist
• Display RMAN session configuration by the SHOW or SHOW ALL commands
• The LIST command has new parameters BY BACKUP and BY FILE
• You can now execute the BACKUP and RESTORE commands directly from the RMAN prompt, instead of only from within a RUN command

Replication Enhancements in 9i

Replication Enhancements

9i allows you to add a new Master site to a Master Group without quiescing the Master Group. The new Master site must be configured for multi-master replication, you can only add one new Master site at a time, the Master Group must be replicating in asynchronous mode, and all links must be configured for parallel propagation.

To ensure transactions are propagated in the proper order, 9i now tracks dependency SCNs at the row level (8i could not track dependencies because it only tracked SCNs at the block level). To use this feature, encode keyword ROWDEPENDENCIES on the CREATE TABLE statement.

9i can now replicate user-defined types, user-defined operators, and these database objects:

• Tables having column objects, nested tables, VARRAYs and REF columns
• Object tables
• Object views (including INSTEAD OF triggers)
• Index types

9i eliminates the SNP background processes used to submit jobs. Instead, it uses a job coordinator background process (called CJQ0), and that job coordinator spawns job queue processes (J00n) to perform the work. The initialization parameter JOB_QUEUE_PROCESSES establishes the upper limit for the number of job queue processes, and the parameter JOB_QUEUE_INTERVAL is now obsolete.