Miscellaneous Availability and Recoverability Features
Duplexed & Multiple Archive Logs
Multiplexing allows you to place archive redo-logs in more than one location.
Can specify up to 5 locations for local destination and 1 location for remote destination. Use the following init parm to specify locations:
LOG_ARCHIVE_DEST_n = ‘LOCATION= /dir_name/’
where dir-name is a valid directory location. Note the use of the LOCATION key word.
LOG_ARCHIVE_DEST_n = ‘SERVICE= tnsnames-service’
where tnsnames-service is a valid service (alias) entry in the tnsname.ora file that represents a standby database.
Both forms of the commands above can include the MANDATORY or OPTIONAL parameter values. If marked as MANDATORY, archiving must be successful before the redo-log can be over-written. If marked as OPTIONAL, successful archiving to the specified location is not required for the redo-log to be reused.
Use the optional LOG_ARCHIVE_MIN_SUCCEED_DEST=n parameter to help control the minimum number of successful archive locations before LGWR can reuse a redo-log file. The default value of n is 1. Valid values are 1 to 5.
Use the LOG_ARVHIVE_DEST_STATE_n parameter to specify the availability state of each destination. Valid values are ENABLE and DEFER. If enabled, the location can be used for automatic and manual archiving operations. If disabled (DEFER), the location is excluded from archiving operations.
Can change the value of LOG_ARCHIVE_DEST_STATE_n using:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n= value
or
ALTER SESSION SET LOG_ARCHIVE_DEST_STATE_N = value
The V$ARCHIVE_DEST view shows the values in use for the current session.
Note the following:
If you do not specify MANDATORY for a destination, it is treated as OPTIONAL.
You do need to set at least one local destination that can be specified as OPTIONAL or MANDATORY.
When using LOG_ARCHIVE_MIN_SUCCEED_DEST, a minimum of one destination will be operationally treated as MANDATORY because the default is 1.
If any MANDATORY locations fail to be archived, the LOG_ARCHIVE_MIN_SUCCEED_DEST value has no impact on processing.
LOG_ARCHIVE_MIN_SUCCEED_DEST value can not be larger than the total number of destinations.
If a mandatory destination is deferred and the online redo-log is overwritten without sending the archived log to a standby database, the logs will have to be transferred manually.
Use these steps to set archive redo log destinations using LOG_ARCHIVE_DEST_n
1. SHUTDOWN NORMAL or IMMEDIATE
2. Edit the init
3. Edit the init
%s - seq number (%S to left pad zeros)
%t – thread number (%T to left pad zeros)
The REOPEN option of the LOG_ARCHIVE_DEST_n parameter specifies in seconds how long the ARCn process should wait before trying again to archive the log. Default value is 300 seconds. If set to 0 (zero), the REOPEN option is disabled.
If REOPEN is specified for an OPTIONAL destination, the online redo-log can still be over-written (reused) if there is an error. However, if REOPEN is specified for a MANDATORY destination and the redo-logwrite fails, the database will come to a standstill.
You can start multiple archiver processes with the LOG_ARCHIVE_MAX_PROCESSES parameter. Set to the number of archive processes you want Oracle to start when LOG_ARCHIVE_START=TRUE. Having multiple archive processes can help to prevent bottlenecks.
As an alternative to using LOG_ARCHIVE_DEST_n method to specify multiple locations for archive logs, you can use the combination of the following 2 parameters.
LOG_ARCHIVE_DEST - primary archive location.
LOG_ARCHIVE_DUPLEX_DEST - optional secondary location
Using this method you can not have the archive file automatically copied to a different machine. In other words, you can’t archive to a remote location.
You can not use the first method described in this section and this method at the same time.
Standby dB in Sustained Recovery Mode
Can create a standby database and keep it in sustained (managed) recovery mode. In this mode, archived redo logs are automatically sent and applied to the standby database SERVICE (alias).
Standby transmission mode is used for maintaining a local or remote standby database.
ARCn processes create corresponding RFS connections for each standby database. If there are 4 ARCn processes archiving to 2 standby databases there will be a total of 8 RFS connections (4 for each database).
The ARCn process “pushes” information to the RFS process.
The RFS process is active when the standby instance is started and is responsible for:
- Receiving I/O from the ARCn process.
- Create and populate the log files from the primary database on the standby database. The location is specified by the STANDBY_ARCHIVE_DEST parameter.
- Updates the log history in the standby control file.
The TIMEOUT option of the RECOVER command can be used to specify an optional timeout interval (specified in minutes). If Oracle “times out” before an update to the standby database control file, managed recovery is exited. By default, no TIMEOUT value specified, managed recovery will wait indefinitely and will shutdown only through manual intervention, a shutdown or a system crash.
RECOVER MANAGED STANDBY DATABASE CANCEL command will terminate managed recovery, but wait until the current managed recovery operation is complete.
RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE command will terminate managed recovery before reading another block from the redo log or before opening the next redo log, but it will wait until the current managed recovery operation is complete. If terminated while processing a log, the standby database will be in an inconsistent state.
Use the following commands to place a standby database in managed recovery mode:
1. STARTUP NOMOUNT
2. ALTER DATABASE MOUNT STANDBY DATABASE
3. RECOVER MANAGED STANDBY DATABASE TIMEOUT 45
(TIMEOUT parameter is optional)
Start a Database for Read-Only Operations
You can startup a standby database in read-only mode and make it available for query (DSS) users.
Note that while a standby database is opened as read-only, it becomes unavailable for managed recovery.
If you need to use your standby database for fail-over while it is open for read-only, you will need to run a recovery operation against it to resynchronize it with your production dB (applying logs). This can be time consuming. It is recommended to keep one standby database for query read-only users and a second for actual fail-over requirements. The one for fail-over should always by in managed recovery mode.
When placed in read-only mode, the only writes that occur on the standby database are for creation of temporary sort segments.
There are a few scenarios for getting a standby database into read-only mode:
-Put in read-only mode from SHUTDOWN STATE:
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE OPEN READ ONLY ;
-Put in read-only mode from MANUAL recovery mode:
RECOVER CANCEL
ALTER DATABASE OPEN READ ONLY;
-Put in read-only mode from MANAGED recovery mode:
RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
Note: Application of redo-logs will stop after the end of applying the current redo-log.
There are a couple of scenarios for getting a read-only standby database into recovery mode:
-Put into MANAGED recovery mode from read-only mode:
Terminate or kill all of the sessions that are active against the standby dB.
RECOVER MANAGED STANDBY DATABASE; (TIMEOUT parameter optional)
-Put into MANUAL recovery mode from read-only mode:
Terminate or kill all of the sessions that are active against the standby dB.
RECOVER STANDBY DATABASE; (TIMEOUT parameter optional)
LogMiner
LogMiner can read info that is stored in online and archived redo logs, based upon selection criteria, without having to restore archived redo log files. Good for tracking changes to the database. Don’t use for audits.
Can be very useful in determining and reversing logical corruption by constructing SQL that can redo and/or undo operations. V$LOGMNR_CONTENTS view contains these SQL statements in the SQL_REDO and SQL_UNDO columns.
LogMiner creates 1 row in the V$LOGMNR_CONTENTS view for each redo record analyzed.
You can also use the data in V$LOGMNR_CONTENTS to:
- Identify the SCN before and/or after logical recovery occurred.
- Follow the flow of transactions applied to a specific table.
- Follow the flow of transactions make by a specific user.
- Identify patterns of data access
- Perform volume/capacity testing and performance tuning.
LogMiner has the following functionality and/or restrictions:
- Only works in 8.1 or higher
- Analyze redo for any 8.0 and higher dB that is on the same platform and character page as the instance running LogMiner from.
- Performs all of its analysis using a dictionary created by a PL/SQL package.
- Collects DML operation info on conventional tables and NOT ON
IOT’s - Clustered tables and indexes
- Non-Scalar data types
- Chained rows
The session that analyzed redo-logs and created the data in the V$LOGMINER_CONTENTS view is the only session that can access that data. If it needs to be made available to other sessions, move the data in the view into a table.
Fast-Start Parallel Rollback(FSPR)
FAST_START_IO_TARGET parameter – specified as an integer. Helps to control the number of I/O’s required during instance or crash recovery by making the DBWn’s work harder and write more dirty buffers to disk. Smaller values should result in faster recovery times at the expense of more I/O writes during normal processing.
Oracle ensures that the checkpoint position does not lag behind the redo log by more than 90% of the size of the smallest redo log.
V$INSTANCE_RECOVERY – gets info that will help you to determine the impact of your fast-start checkpointing parameters, such as info on redo blocks and recovery I/O’s
V$FILESTAT – Use to get average I/O statistics that will help to determine the FAST_START_IO_TARGET value. AVGIOTIM column has the I/O average time statistics.
SMON process coordinates the rollback of transaction sets in parallel via multiple server processes. SMON evaluates a threshold to begin the parallel rollback.
Can be very useful when there are long running parallel DML operations that need to get rolled back during instance recovery upon startup.
FSPR sometimes uses a technique call intra-transaction recovery. In this situation, there may be multiple transactions that need to be rolled back and all of them are of equal size except one that may be significantly larger than the rest. Oracle will cause the large transaction rollback to be divided and shared among the individual parallel processes that are allocated to the other smaller processes. In this way, the overall time to perform the instance recovery is reduced.
Use the FAST_START_PARALLEL_ROLLBACK parameter to control the number of processes that get involved in transaction recovery. Valid values are:
FALSE – disables fast-start parallel rollback.
LOW - total number of recovery servers; can not be more that 2 times the CPU_COUNT parameter
HIGH - total number of recovery servers; can not be more that 4 times the CPU_COUNT parameter
V$FAST_START_SERVERS – data for all FSPR processes.
V$FAST_START_TRANSACTIONS – progress of the transactions.
Manage Corrupt Block Detection and Repair
The DBMS_REPAIR package can be used to detect and repair corrupt table and index blocks, while objects remain available to normal processing.
Procedures available in the DBMS_REPAIR package
check_object – detect and report table or index corruption
fix_corrupt_blocks – mark blocks identified by the check-object procedure as corrupt.
dump_orphan_keys – identify index entries that reference rows in corrupt data blocks.
rebuild_freelists – rebuild the object freelist.
skip_corrupt_blocks – will allow the DBMS_REPAIR package to skip blocks that were marked as corrupt during full table and index scans.
admin_tables – admin functions for creating, dropping, and purging DBMS_REPAIR repair and orphan key tables. These tables live in the SYS schema.
The DBMS_REPAIR package has the following restriction:
-LOB’s, VARRAYS and nested are supported, but out-of-line columns are ignored.
- skip_corrupt_blocks and rebuild_freelist procedures support clusters, but the check object procedure does not.
- IOT’s and LOB indexes are not supported.
- dump_orphan_keys procedure does not support bitmap indexes or function-based indexes.
- The largest key that the dump_orphan_keys procedure can process is 3,950 bytes.
Additional methods for determining corruption:
DB_VERIFY – performs block checking on datafile blocks, from a command prompt, for an online and offline datafiles. Minimal performance impact.
ANALYZE – the VALIDATE STRUCTURE option will verify the structural integrity of an index, table or cluster. Can check or verify that tables and indexes are in sync. Use this method for checking and reporting corruption on partitioned tables. Can identify invalid rows in a partitioned table.
DB_BLOCK_CHECKING – Will identify a corrupt block before it is actually marked as corrupt, by performing checks at the time changes are made to a block. Is a init parameter. When block detection is detected and DB_BLOCK_CHECKING=TRUE, an Ora-1578 error is written to the alter file.
No comments:
Post a Comment