|by Michael Ritacco, Oraclenotes.com||Version 1.3|
Oracle Server Security
CONNECT INTERNAL has been discontinued and is no longer available as a DBA access method.
Server Manager is no longer available. SQLPlus has all the features of Server Manager and should be used for all database management activities.
Equivalent Internal Connections
SQL> connect sys/change_on_install as sysdba
SQL> connect / as sysdba
Database Creation Assistant (DBCA) which was called DBASSIST in Oracle8i now creates databases with accounts locked and without default passwords. This increases security by closing access to most of the standard accounts, such as CTXSYS, MDSYS, OUTLN.
The exception are SYS, SYSTEM, and SCOTT which are not locked by the DBCA. However, the tool requires you to enter a password for these account since no default are provided. If you create a database manually, the passwords for SYS and SYSTEM have the Oracle8i and earlier defaults.
The 07_DICTIONARY_ACCESSIBILITY is an initialization parameter previously set to TRUE, which enabled users with the SELECT ANY TABLE privilege to read the data dictionary tables. This parameter is now set to FALSE which requires login as SYSDBA to read the data dictionary, or have explicit object grants from SYS.
Another consideration to improve security is to revoke EXECUTION granted to PUBLIC on some packages. Granting only the privileges to the few users that require execution will limit access to some features which could be used by a hacker to gain access to other parts of the database.
Secure Application Roles
Enabling a role is checked through a package not a password. Using the SYS_CONTEXT mechanism found in the Virtual Private Database.
Global Application Contexts
A context can now be global and shared and is:
To help manage application contexts the following interfaces have been added to DBMS_SESSION package.
Fine-Grained Access Control Enhancements
Oracle9i introduces partitioned Fine-Grained Access Control (FGAC) which enables application driven security policies. Previously, when FGAC was defined on a table a row must satisfy all requirements before being visible. When the table was used by users from separate application groups trying to share the same data, it required one very complicated FGAC to include all user groups' requirements.
Policy Groups are used to distinguish policies between different applications. The Driving Context indicated the policy group in effect. When tables and views are accessed, the FGAC engine looks up the driving context to identify the policy group in effect, enforcing all policies that belong to that policy group.
SYS_DEFAULT is predefined and are always executed in addition to policy group specified by the driving context.
Provides extensible intrusion detection, capturing SQL statements, not the retrieved data, with the ability to invoke a procedure as part of the audit.
Audit policies are created with the DBMS_FGA on the tables needing audit. A list of the policies in effect can be found in the DBA_AUDIT_POLICIES view. Audit records are placed in the DBA_FGA_AUDIT_TRAIL. Administrators can define audit event handlers to process events, which could send a e-mail alert to administrators.
GETKEY has been added to the DBMS_OBFUSCATION_TOOLKIT package. The GETKEY procedure is a Federal Information Processing Standard -140 certified random number generator used for secure key generation.
Oracle9i introduces a two-pass instance/crash recovery to reduce recovery time.
Factors affecting instance recovery time:
Since the redo logs may contain entries of changes made to data blocks that were not dirty in the buffer cache at the time of failure, Oracle now reads the logs twice. The first read establishes the minimum number of block needed for recovery with the second pass applying only those required changes. Since the first sequential read is very fast, the reduction in workload easily offsets the cost of the additional read and results in an improved recovery time.
This feature is enabled by default and requires no configuration by the DBA.
Fast-Start Time-Based Recovery Limit
The new dynamic initialization parameter FAST_START_MTTR_TARGET allows for the DBA to specify the estimated number of seconds a crash recovery should take. Replaces the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters.
The FAST_START_MTTR_TARGET parameter internally maps to the correct values for FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL, however since this is set by the Oracle Server it greatly simplifies and increases the accuracy of setting these parameters individually.
V$INSTANCE_RECOVERY has the following new columns added:
V$INSTANCE_RECOVERY is used by the DBA to monitor check pointing Every 30 seconds, Oracle calculates an estimate of the current MTTR based on current I/O rates and places this value in V$INSTANCE_RECOVERY. Allowing the DBA to monitor the current estimated MTTR to the setting specified by FAST_START_MTTR_TARGET.
Note: A small value for FAST_START_MTTR_TARGET can have a negative impact on performance because of the additional checkpoints that will be generated. Additional writes due to check pointing are displayed in the column CKPT_BLOCK_WRITES.
Enables users to see a (read-only) consistent view of the database at a point in the past. Only committed transactions up until the time specified are visible.
DBMS_FLASHBACK is the interface with the Oracle Flashback functionality. Users must have the execute privilege on DBMS_FLASHBACK to use the feature. Note: The DBMS_FLASHBACK package cannot be executed as SYS.
A long enough undo retention interval must be set to be able to construct the data.
SQL> alter system set undo_retention=
Resumable Space Allocation
The resumable space allocation feature provides the ability to resume execution of a database operation in the event of a repairable failure. A statement executes in Resumable Space Allocation mode only when explicitly set for the session using the ALTER SESSION ENABLE RESUMABLE command.
The following types of operations are candidates for Resumable Space Allocation:
The DBMS_RESUMABLE package:
Note: You cannot use this package to enable Resumable Space Allocation mode for a session. However, the DBA can create a logon trigger that can enable this feature.
The After Suspend System event is new in Oracle9i. Used with a After Suspend trigger the DBA has the ability to handle the user error. Oracle recommends that the After Suspend trigger be created in the SYS schema.
The new RESUMABLE system privilege allows execution of statements in Resumable Space Allocation mode.
The DBA_RESUMABLE and USER_RESUMABLE views display the set of Resumable Space Allocation statements in the system. This information is not persistent and cannot be accessed across database shutdown or startup.
New import parameter for statistics (STATISTICS)
New export tablespace parameter (TABLESPACES)
New Import/Export Resumable Space Allocation parameters:
New Import/Export Flashback parameters:
Oracle9i adds the following new features:
Note: LogMiner can be run in a shared server configuration. However, this is not recommended due to the performance impact. It is suggested to user a dedicated server for a LogMiner connection.
Backup and Recovery
Oracle9i RMAN Enhancements
Customizable configuration parameters enable the DBA to simplify most RMAN operations. The default settings required for the environment can now be set once and used for all backup jobs. The configuration values are stored in the control file and synchronized to the recovery catalog if applicable.
BACKUP DATABASE : a single command to backup the database using RMAN.
CONFIGURE : allows the DBA to override default settings persistently.
RMAN allows for two types of retention policies to manage your backups. These policies are mutually exclusive and set using the CONFIGURE command. Note: The default retention policy is REDUNDANCY 1.
Remember to set the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME greater then the space requirements of your retention policy, if not using a recovery catalog. Otherwise, RMAN will not be able to reconstruct a list of backup files required to do a valid restore.
Note: the difference between CONFIGURE RETENTION POLICY TO NONE and CONFIGURE RETENTION POLICY TO CLEAR. Setting the retention policy to NONE means there is no retention policy in effect, backups will not expire and the DELETE OBSOLETE command will error. Using the CLEAR command resets RMAN back to the default retention policy of REDUNDANCY 1.
The DELETE OBSOLETE command is used to delete backups that are no longer needed to satisfy the retention policy.
You can use the following data dictionary views to see RMAN configuration settings:
Automatic Channel Allocation
This feature improves and simplifies the interface to RMAN. Now a channel is automatically allocated if one is not explicitly specified in a RMAN script. Since RMAN knows which commands are being run it can appropriately allocated channels based on the type of work being done. For backups, only a single type of channel is allocated. For restores, RMAN know which devices types (DISK/TAPE) are required for the restore, and allocates channels as required.
This feature can be used with the following commands:
RMAN Configuration Commands
Long Term Backups
Backups can be archived for time-periods longer then specified by the retention policy, by using the KEEP option.
RMAN> BACKUP .... KEEP [UNTIL TIME 'date'|FOREVER] [NOLOGS|LOGS]
KEEP can be used with both the BACKUP, COPY, and CHANGE commands.
Unsuccessful RMAN backups can now be restarted. RMAN finds only the missing or incomplete files (based on backup time) and continues the backup; files that were successfully backed up before the failure are skipped.
Use the new option NOT BACKED UP option to enable this feature.
RMAN> BACKUP DATABASE NOT BACKED UP SINCE TIME '03-FEB-02 15:00:00';
Note: This feature only works with Oracle9i databases, previous releases of Oracle will not restart.
Archive Log Backups
Archive logs which have not been backed up can now be included with data file backups using the PLUS ARCHIVELOG option of the BACKUP command. The PLUS ARCHIVELOG option is also the default when backing to tape. RMAN will not signal an error if no archive logs are found to backup.
The revised non-disk process follows the following steps:
This feature enables the DBA to perform disk management using RMAN. The source for this command must be disk, and when the command completes, the backup exists on both disk and tape.
RMAN> BACKUP DEVICE TYPE SBT BACKUPSET ALL;
Using the DELETE INPUT option enables the DBA to have the most current backups on disk while older backups residing just on tape.
RMAN> BACKUP DEVICE TYPE SBT BACKUPSET CREATED BEFORE 'sysdate-2' DELETE INPUT;
Restartable Restores/Restore File Optimization
Using RMAN's restore file optimization, RMAN checks the consistency of each file that is to be restored and if the file header contains the expected information, it will not restore the file to disk again from the backup. Previously, if a restore operation failed RMAN would restore all data files from backup. This was especially painful if the failure occurred on the last data file and your restore had been running for 8 hours. With restore file optimization, RMAN examines all target file headers before beginning the restore and restores just the data files with the incorrect state.
This feature allows restore operations to be restarted after any type of failure without doing unnecessary work. The RESTORE FORCE command still can be used to restore a file regardless of the header state.
Note: Restore optimization only checks the data file headers without scanning the data file body for corruption.
Block Media Recovery (BMR)
A block is now the smallest unit of media restore and recovery. BMR lowers the mean time to recover and increases data availability during media recovery. BMR uses the existing recovery mechanisms to apply changes from the redo stream to block versions restored from backup. This feature is only available through RMAN and is not available via existing SQL interfaces.
Note: Only complete recovery is possible with block media recovery. A incomplete recovery would result in the data files involved to be physically inconsistent.
BMR is not suitable when the extent of data loss or corruption is unknown, and is targeted towards recovering when specific blocks are reported in Oracle errors.
RMAN performs BMR with the BLOCKRECOVER command. RMAN identifies the required backups to obtain the blocks to recover and reads the backups placing the requested blocks into in-memory buffers. RMAN manages the complete block media recovery, reading any archive logs from backup if necessary. BMR always performs a complete recovery.
RMAN> BLOCKRECOVER DATAFILE 1 BLOCK 2;
Two types of corruption can be detected by RMAN when using the BACKUP and COPY commands:
RMAN lists blocks that failed logical validation during backup in:
Using the CORRUPTION LIST clause specifies all blocks found in these views to be recovered.
Note: The RESTORE UNTIL clause specifies backups and copies created before the specified date. In this example RMAN would use a backup from 2 days ago.
RMAN> BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE - 2';
RMAN Reporting Enhancements
If recovery encounters a problem the database is always left in a physically consistent state. The errors encountered and information about them are found in the alert log. Since the alert log only contains information about errors found up until that point, trial recovery can be used to determine the entire extent of the damage.
Trial recovery is a new feature that allows the DBA to find out how many more problematic blocks are going to occur during recovery. Trial recovery only changes blocks in memory and can allow an unlimited number of corrupt data blocks. Errors encountered during a trial recovery are written to the alert log and are marked as test run errors.
The DBA may invoke a test recovery by adding the TEST option to any restore command:
With the ability to further investigate, the DBA can either choose to open resetlogs at that point or continue recovery if only a few blocks are bad using the ALLOW N CORRUPTION option.
SQL> RECOVER DATABASE ALLOW N CORRUPTION;
A trial recovery ends when:
When a Trial Recovery ends, even if the instance dies, all effects of the test run are automatically removed from the system because trial recovery NEVER writes changes to disk.
Oracle9i Data Guard helps your database survive destructive events by:
Data Guard Architecture:
The DMON process is started using the parameter DRS_START=TRUE.
Data Availability Modes in Data Guard
During an unplanned outage the primary role is moved to one of the standby sites.
New to Oracle9i, primary and standby databases can alternate roles without rebuilding the standby site, under the following conditions.
Standby Switchover Steps
This command performs the following:
Automatic Recovery of Log Gaps
Oracle9i eliminates any need for operator intervention when any number of situation may arise causing the standby database to be unable to apply the next archive redo log. Managed Recovery must be enabled.
Standby File Management
Adding or dropping files in the standby database has been automated in Oracle9i. However, certain operations are not allowed in the standby database. The following commands are not allowed: ALTER DATABASE RENAME, ADD/DROP LOGFILE, ADD/DROP LOGFILE MEMBER, CREATE DATAFILE AS.
Set the following parameter to enable this feature:
Note: Non-Oracle Managed Files are not dropped on the standby database. If you drop the tablespace on the primary, the tablespace will drop on the standby but the data files must be removed manually.
Background Managed Recovery Mode
There is now a background process called MPR used to perform managed recovery. Freeing the terminal session to execute the recovery command.
Standby Lag Time
The DBA can delay the availability of archive log files at the primary site to the standby database. The value of this attribute is in minutes and specifies the interval that must expire, after archive log transmittal has completed, before the archive log can be processed. The standby database can override this delay by using the [NO DELAY] or [FINISH] option of the RECOVER MANAGED STANDBY DATABASE command.
Online redo log enhancements:
Database Resource Manager Enhancements
Active Session Pool
The active session pool allows the DBA to control concurrent system workload. With this functionality the DBA can indirectly control the amount of resources that any resource consumer group uses. By controlling the number of active sessions in the database, and queuing active sessions once the active session pool is filled, a minimum set of available system resources can be guaranteed.
Setting the Active session pool size for a resource consumer group:
Maximum Estimated Execution Time
The resource manager can now estimate the execution time of an operation proactively. The DBA can specify a maximum estimated execution time for an operation at the resource consumer group level. The benefit is the ability to eliminate large jobs that would consume too many system resources.
Automatic Consumer Group Switching
The database resource manager automatically switches a session's consumer group based on the following resource plan directives:
New plan directive has been added UNDO_POOL. UNDO_POOL is specified in kilobytes and the default is 1000000.
The DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE package can be used to change the undo quota anytime during database operation.
Oracle Supplied Plans
Oracle9i can now perform ANALYZE VALIDATE STRUCTURE online. In addition to having the capability to "quiesce" the database. A database that is quiesced only allows transaction to be made by DBA, however all currently connected users are blocked from performing queries, DML and PL/SQL. This enables the DBA to perform maintenance operations without a shutdown and enabling RESTRICTED SESSION.
Quiesce benefits over RESTRICTED SESSION:
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
This command puts the database in a quiesced state.
SQL> ALTER SYSTEM UNQUIESCE;
This command returns the database to a normal state.
The ACTIVE_STATE column of V$INSTANCE can be checked to see what state (3) the database is in:
Online Index Rebuild adds support for the following indexes:
Index Organized Table Enhancements
Online Table Redefinition
DBMS_REDEFINITION is the package used for online table redefinition but has several limitations.
The following cannot currently be redefined:
Server Parameter File (SPFILE) is a binary file that can persistently store instance parameters across startup and shutdown.
You can create a spfile in any database state (IDLE, NOMOUNT, MOUNT, or OPEN) but your user must be granted either the SYSDBA or SYSOPER role.
Use the following command to create a spfile from a existing init.ora:
SQL> CREATE SPFILE 'spfile-name' FROM PFILE 'pfile-name';
Use this command to export the contents of a spfile to a standard init.ora file.
SQL> CREATE PFILE = 'mypfile.ora' FROM SPFILE;
The DBA can view the contents of the spfile with the new V$SPPARAMETER view. Parameters can be changed using the ALTER SYSTEM SET command with the additional cause SCOPE. Scope can be set to MEMORY, SPFILE, and BOTH. If a spfile was used to startup the database BOTH is the default, if a init.ora file was used MEMORY is the default.
Oracle9i database startup behavior has changed now that the STARTUP command by uses the spfile by default and only if not found or is unusable the instance uses the default init.ora file. Note: The init.ora can contain a pointer to call a spfile.
FULL_BLOCKS - blocks no longer available for INSERTs
FS1_BLOCKS - blocks below the HWM with 0-25% free space
FS2_BLOCKS - blocks below the HWM with 25-50% free space
FS3_BLOCKS - blocks below the HWM with 50-75% free space
FS4_BLOCKS - blocks below the HWM with 75-100% free space
Steps for using OCI Connection Pooling
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51733/，如需转载，请注明出处，否则将追究法律责任。