(Previous) <
1
2
3
4
5
> (Next)
- Maintaining the Control Files
- Explain the uses of the control file
- List the Contents of the control file
- Multiplex the Controlfile
- Once the database is shutdown, it is necessary to copy the control files via the OS to the desired (mirrored) locations.
- Modify the init.ora paraemeter
CONTROL_FILESand indicate where the new mirrored (multiplexed) control-file can can be accessed. - Obtain Control File Information
- Maintaining the Redo Log Files
- Explain the use of the Online Redo Log Files
- Obtain log and archive information
- Control Log Switches and checkpoints
- Multiplex and maintain online redo log files
- Plan Online Redo Log Files:
- Setting LOG_CHECKPOINT_INTERVAL in init.ora to be larger than the size of your largest redo log file.
- Setting LOG_CHECKPOINT_TIMEOUTto zero.This value eliminates time-based checkpoints. In addition to setting these parameters, also consider the size of the log files, as small log files can increase checkpoint activity and reduce performance. Oracle checkpoints current read blocks. By contrast, sort blocks and consistent read blocks are not checkpointed.
- Troubleshoot common redo log file problems
- If LGRW cannot access the next group at a log switch because the group needs to be archived, database operation temporarily halts until the group becomes available,this can be remedid normally until the group is archived.
- All members of the next group are inaccessinble to LGWR at log switch because of disk failures.Oracle returns an error and the database instance shut downs. You may need to perform media recovery on the database from the loss of an online redo log file.
- All members of the next group are inaccesible and the database checkpoint has moved beyond the lost redo log. Media recovery is not necessary because Oracle has saved the data recorded in the redo log to the datafiles. Simply drop the inaccesible redo log group.
- You want to drop an unarchived redo log when in ARCHIVELOG mode. Issue ALTER DATABASE CLEAR UNARCHIVED LOG to disable archiving before the log can be dropped.
- All the members of a group become inaccessible to LGWR while it is writing to them. Oracle returns an error and the database instance immediatly shuts down.You may need to perform media recovery. If the media containing the log is not actually lost or if the drive for the log was inadevertently turned off then media recovery may not be needed. In this case, you only need to turn the drive back on and let Oracle perform instance recovery.
- Analyze online and archived redo logs.
- Determine when a logical corruption to the database may have begun,pinpointing the time or SCN to which you need to perform incomplete recovery.
- Track changes to a specific table
- Track changes made by a specific user
- Map data access patterns
- Use archived data for tuning and capacity planning
- Specify the parameter UTL_FILE_DIR in the init.ora file, if you do not reference this parameter, the procedure will fail.
UTL_FILE_DIR = /u01/oradata/logs - In SQLPLUS mount and open the database whose files you want to analyze
- Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify both filename for the dictonary and a directory pathname for the file. This porcedure creates the dictionary file, whcih you should use to analyze log files.
- With the database either mounted or unmounted, create a list of logs specifying the NEW option when executing the DBMS_LOGMNR.ADD_LOGFILE procedure.
- Managing Tablepace and Data Files
- Describe the logical structure of the Database.
- Distinguish the different types of temporary segments.
- Create Tablespaces
- Change the size of tablespaces
- Allocate space for temporary segments
- Change the status of your Tablespaces
- Change the storage settings of Tablespaces.(Done directly through the datafile)
- Relocate Tablespaces.
- Shutdown the database.
- Move the file with an OS command (mv)
-
startup mountthe database. - Issue the command
- open the database.
alter database open - Take the tablespace offline
alter tablespace DATA offline - Move the datafile via the OS (mv)
- Issue the command
- Bring the tablespace back online
alter tablespace DATA online - Storage Structure and Relationships
- Describe the logical structure of the database
- List the Segment Types and their Uses
- TABLE: Store the rows of data associated with tables or clusters Unless very large, a properly size table will have only 1 extent. Once a data segment is acquires an extent, it keeps that extent until the segment is either dropped or truncated.
- INDEX : Hold the space of the Tables that generate indexes.They
are subject to the same space problems as Table Segments. Their parameters can be changed via the
alter index .... rebuild - ROLLBACK: Rollback segments are involved in every transaction in
the database. They allow the database to maintain read consistency between multiple transactions. Ideal rollback segments will have multiple evenly sized extents that add up to the optimal size. Each extent should be large enough to handle all the data from a single transaction. Rollback segments can dynamiclaly shrink to a specfied size, or they can be manually shrunk to a size. The
optimalparameter at creation generates this. - TEMPORARY: These store temporary data during sort operations ( such as large queries, index creations and unions.)
- PARTITION: Similar to table segments
- CLUSTER: Similar to table segments
- List the key words that control block space usage
- initial : This determines the initial size of the "extent"
- next : Determines the values of the allocated "extents" after the initial extent is full.
- pctincrease : Factor by which the extents ("next") will grow in a geometrical manner.
- maxextents :Maximum number of extents for the segment
- minextents : Minimum number of extents for the segment
- pctfree: This parameter is used to reserve space for the extent which will only be used for updates.
- pctused : Determines when a used-block is readded to the list of blocks into which rows can be inserted.e.g. If an extent (table) has a pctfree of 20 and pctused of 50. When rows are inserted into Oracle, the extent will reserve 20 % of each block free. When the 80 % mark is reached and even if you now begin to delete rows, Oracle will not write to this block until the blocks used space falls below 50 %. By DEFAULT pctused is at 40
- Obtain information about storage structures from the data dictonary.
- DBA_TABLESPACES : List the tablespaces and parametrs of all tablespaces
- USER_TABLESPACE :Lists all the tablespace the user owns.
- DBA_TABLES :Self-Explanatory
- DBA_INDEXES :Self-Explanatory
- DBA_CLUSTERS :Self-Explanatory
- DBA_SEGMENTS :Self-Explanatory
- DBA_SEQUENCES :Self-Explanatory
- DBA_OBJECTS :Self-Explanatory
- List the criteria for separating segments
A control file contains entries that specify the physical structure of the database.
It contains information on what data files and redo log files belong to the database, what character set the data should be stored as in the database, the status and revision of each datafile in the database, and other critical information. The control file is in binary format and cannot be modified manually, if the control file(s) for the database is corrupted then the data within the database cannot be accessed.
In order to see plain text information on the control file it is necessary to generate a DUMP_FILE from the control file since its information is conatined in binary format. The command is :
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
This creates a user trace file at USER_DUMP_TEST.
The view V$CONTROLFILE contains information on what controlfiles Oracle is currently reading and writing to.
The view V$DATABASE indicates wheter the control file is current or a backup, when the controlfile was created, and the last time stamp. The V$LOG view contains information from the controlfile.
These redo log files store all the change information for the database and are used by Oracle during the database recovery. These files are made up of at least two groups of redo-log files, and are written to in circular nature. Each log group can consist of multiple members. Each member is an exact mirror of the other member, and the entries are written in parallel. By using mutliple members per group you safeguard against database failure resulting from lost redo logs, as long as one member of the group is accesible, the database continues to function.
The V$LOG view holds information on the online redo log groups, the number of members per group,and which logs have been archived. And V$LOGFILE view: Displays filenames and status information on the redo log group members.
In SQL*Plus the command ARCHIVE LOG LIST displays information on archiving .
Checkpionts help reduce the amount of time needed to perform instance recovery. Checkpoints cause DBWRs to write all the blocks that have been modified since the last checkpoint to the datafiles and update the datafile headers and control files to record the checkpoint.Checkpoints occur autmatically when an online redo log file fills; the LOG_CHECKPOINT_INTERVAL in the database instance may be used to set more frequent checkpoints.
The parameter LOG_CHECKPOINT_TIMEOUT specifies an upper bound on the time a buffer can be dirty in the cache before DBWn must write it to disk. If you set LOG_CHECKPOINT_TIMEOUT to 60, then no buffer remains dirty in the cache for more than 60 seconds. The default value for LOG_CHECKPOINT_TIMEOUT is 1800.
LOG_CHECKPOINT_INTERVAL, causes the checkpoint to be triggered when a number of OS block (not Oracle bloocks!) are written to REDO. Oracle limits the maximum value to 90% of the smallest log to ensure that the checkpoint advances far enough to eliminate "log wrap". Log wrap occurs when Oracle fills the last available redo log file and cannot write to any other log file because the checkpoint has not advanced far enough. By ensuring that the checkpoint never gets too far from the end of the log, Oracle never has to wait for the checkpoint to advance before it can switch logs.
The command ALTER SYSTEM SWITCH LOGFILE forces a log switch
The parameter LOG_ARCHIVE_START specified wheter archiving is enabled, if set to TRUE then archiving is enabled, if set to FALSE then no archiving takes place. As with other systems parameters this parameter can be set on the fly with the command ALTER SYSTEM ARCHIVELOG START
This multiplexing is specified while the database is in mount and
is done through the alter database command.
alter database
>add logfile group 5
> ('/u02/app/oradata/OSMOSIS/log_1c.dbf',
> /u05/app/oradata/OSMOSIS/log_2c.dbf') size 5M;
To add a new log file to an existing group:
alter database >add logfile member '/u05/app/oradata/OSMOSIS/log_3c.dbf' >to group 3;
When adding a file to a redo log group there is no need to specify the size, because the group has already been indicated that parameter, besides all the redo log files in the group must be of the same size.
Mirroring the Archived Redo log files, you can specify up to five archive log destination areas, this is done with the parameter LOG_ARCHIVE_DEST_n. To enable or disable the destinations or these archived redo log files the parameter LOG_ARCHIVE_STATE_n (values are DEFER and ENABLE (default)),example:
LOG_ARCHIVE_DEST_1 = 'LOCATION =/u02/oradata/archive' LOG_ARCHIVE_DEST_2 = 'SERVICE = OSMOSIS2'
The service specifier is used when maintaining a standby database, use the SERVICE keyword to specify a valid net service name from the tnsnames.ora file.
Maintain at least three online redo-log groups with two members each, place each member of the group in a different disk and controller. The size of the redo log file directly influences checkpoint performance. The smaller the size of the smallest log, the more Oracle writes dirty buffers to disk to ensure the position of the checkpoint has advanced to the current log buffer that log completly fills.Oracle enforces this behavior by ensuring the number of redo block between the checkpoint and the most recent redo record is less than 90 % of the size of the smallest log.
If the redo logs are small compared to the number of changes made against the database, Oracle must switch logs frequently. If the value of LOG_CHECKPOINT_INTERVAL is less than 90 % of the size of the smallest log, this parameter will have the most influence over checkpointing behavior.
Although you specify the number and sizes of online redo log files at database creation, the commands ALTER DATABASE ADD LOGFILE can be used to add redo log file and specify its size, or the DROP LOGFILE clause to drop a redo log.
The size of the redo log appears in the LOG_FILE_SIZE_REDO_BLKS column of the V$INSTANCE_RECOVERY view. This value shows how the size of the smallest online redo log is affecting checkpointing. By increasing or decreasing the size of your online redo logs, you indirectly influence the frequency of chekcpoint writes.
Checkpoints affect: Instance recovery time performance and Run-time performance.
Frequent checkpoints can reduce instance recovery time in the event of an instance failure. If checkpoints are relatively frequent, then relatively few changes to the datasbase are made between checkpoints. In the case,relatively few changes must be rolled forward for instance recovery.
Checkpoints can momentarily reduce run-time performance because checkpoints cause DBWn processes to perform I/O.However, the overhead associated with checkpoints is usually small and affects performance only while Oracle performs the checkpoint.
Choosing Checkpoint Frequency
Choose a checkpoint frequency based on your performance concerns. If you are more concerned with efficient run-time performance than recovery time, choose a lower checkpoint frequency. If you are more concerned with having fast instance recovery than with achieving optimal run-time performance,choose a higher chekcpoint frequency.
Because checkpoints are necessary for redo log maintanence, you cannot eliminate checkpoints entirely. However, you can reduce checkpoint frequency to a minimum by:
LogMiner is a utility that is used to read information contained in online or archived redo logs based on various selection criteria, you can select information from the V$LOGMNR_CONTENTS view that enables you to:
Track changes to a specific table, Track changes made by a specific user, Map data access patterns, View the SQL syntax for undoing or redoing a specific change made against the database, use archived data for tuning and capacity planning.
LogMiner has a fully relational SQL interface that provides direct access to a complete historical view of a database- without forcing the restauration of the archived redo log files.
LogMiner is especially usefull for identifying and undoing logical corruption. LogMiner processes redo log files, translating their contents into SQL statements that represent the logical operations performed on the database. The V$LOGMNR_CONTENTS view then lists the reconstructed SQL statements that represent the original operations (SQL_REDO column) and the corresponding SQL statement to undo the operations ( SQL_UNDO columnn) . Apply the SQL_UNDO statements to roll back the original changes to the database. The V$LOGMNR_CONTENTS is used to view :
LogMiner runs in an Oracle instance with the database either mounted or unmounted. LogMiner uses a dictonary file , which is a special file that indicates the database that created it as well as the time the file was created. The dictionary file is not requeired,but is recommended. Without a dictonary file, the equivalent SQL statements will use Oracle internal object IDs for the object name and present column values as hex data. e.g,Instead of the SQL statement: INSERT INTO emp(name,salary) VALUES ('John Doe', 50000);, Log Miner will display:
insert into object#2581(col#1, col#2) values ( hextoraw('4a6f686e20446f65'),hextoraw('c306'));
Create a dictonary file by mounting a database and then extracting dictonary information into an external file. You must create the dictionary file from the same database that generated the log file you want to analyze. Once created you can use the dictionary file to analyze redo logs.
To create the dictonary file:
EXECUTE dbms_logmnr_d.build dictionary_filename => 'dictonary.ora', dictionary_location => '/u01/oradata/logs');
Once you have created a dictonary file, you can begin analyzing redo logs. The first step is to specify the log files that you want to analyze using the ADD_LOGFILE procedure. Use the folloing constants : NEW to create a new list, ADDFILE to add redo logs to a list, REMOVFILE to remove redo logs from the list.
To use log miner:
execute dbms_logmnr.add_logfile( LogFileName => '/oracle/logs/log1.f', Options => dbms_logmnt.NEW);
The options to add more logs or delete them are (respectively).
execute dbms_logmnr.add_logfile( LogFileName => '/oracle/logs/log2.f', Options => dbms_logmnr.ADDFILE); execute dbms_logmnr.add_logfile( LogFileName => '/oracle/logs/log2.f Options => dbms_logmnr.REMOVEFILE);
The following options are used to narrow the range of your search at start time:
| Option | Specifies |
|---|---|
| StartScn | The beginning of an SCN range |
| EndScn | The termination of an SCN range |
| StartTime | The beginning of a time interval |
| EndTime | The ending of a time interval |
| DictFileName | The name of the dictionary file |
execute dbms_logmnr.start_logmnr( DictFileName => '/u01/oradata/dictionary.ora', StartScn => 100, EndScn => 150);
Once LogMiner is started, you can make use of the following data dictionary views:
| View | Information About |
|---|---|
| V$LOGMNR_DICTIONARY | The dictionary file in use |
| V$LOGMNR_PARAMETERS | Current parameter setting for the LogMiner |
| V$LOGMNR_FILES | Which redo log files are being analyzxed |
| V$LOGMNR_CONTENTS | The contents of the redo files being analyzed |
A TABLESPACE is a logical division of a database. Each database has at least one tablespace (SYSTEM). These logical divisions are normally used to group user applications together and ease the maintanence and better perfromance of the database. A tablespace can belong to only one database.
Each tablespace is constituted of one or more files (datafiles) on a disk. A datafile can belong to one and only one tablespace. Creating new tablespaces requires creating new datafiles.
When a temporary segment is created, it uses the default storage parameters for the tablespace. The temporary segment extends itself as necessary, and drops itself when the operation completes or enocounters an error. The temporary segment by its nature is normally fragmented. A good sizing for the initial and next extents is 1/20 to 1/50 the size of the tablespace, you should also choose a pctincrease of 0 , as this will generate segments of the same size. When these segments are dropped,the next temporary segment to be formed will be able to reuse the dropped extents.
The first sort to use the temporary tablespace allocates a temporary segment in the tablespace, when the query completes the space used by the temporary segment is available for use by other queries; this allows the sorting operation to avoid the costs of allocating and releasing space for temporary segments. To dedicate a table for temporary segments, you use de temporary argument in the create tablespace or alter tablespace alter tablespace TEMP temporary;
To return a tablespace to store permanent storage:
alter tablespace TEMP permanent;
The contents column in the DBA_Tablespaces contains information on the status of the tablespaces as either 'Temporary' or 'Permanent',the temporary segments are Table,Index and Rollback
CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE 'file_1.f' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
create tablespace DATA datafile '/u01/app/oradata/OSMOSIS/data01.dbf' size 200M autoextend ON next 10M maxsize 250M
These parameters specify the size and location of the datafile, the autoextend argument means that when the datafile is full, it will extend itself by 10M (the next argument) until 250M are reached (the max argument).
It is also possible to add a datafile to an existing tablespace, to enable the autoextend feature.
alter tablespace DATA add datafile '/u10/app/oradata/OSMOSIS/data02.dbf' size 50M autoextend ON maxsize 300M;
NOTE: The table DBA_DATA_FILES holds information on the mapping between datafiles and tablespaces.
alter database datafile '/u02/oradata/OSMOSIS/data02.dbf' resize 80M;
alter tablespace TEMP add tempfile '/u01/oradata/tmp01.dbf'
alter tablespace DATA offline alter tablespace DATA online alter tablespace TEMP permaenent alter tablespace DATA_TEMP temporary
alter database datafile '/u02/app/oradata/OSMOSIS/data01.dbf' autoextend ON maxsize 300M
This can be done either through the alter database or alter tablespace command.
With alter database
alter database rename file
'/u01/oradata/OSMOSIS/data01.dbf' to --Old location of datafile
'/u04/oradata/OSMOSIS/data01.dbf'; --New location of datafile
With alter tablespace
alter tablespace DATA rename datafile
'/u01/oradata/OSMOSIS/data01.dbf' to --Old location of datafile
'/u04/oradata/OSMOSIS/data01.dbf'; --New location of datafile
Each time an object is generated (Table or Index), this object is placed in a "Tablespace", which will be either the one specfied at object creation or the default tablespace for the user generating the object. At this point a "segment" is generated that will be the placeholder for the information on the object . The space that is assigned to this segment is not dropped until it is manuallly shrunk or truncated.
Each segment is divided into "extents" which are contiguos "oracle blocks" once these extents are no longer capable of maintaing information, the segment will acquire another "extent". This process will continue until there is no more free space in the "datafile" ("Tablespace") or when a maximum limit of extents per segment is reached. If a segment is composed of various extents there is no guarantee that these extents are continuos.
When a segment is created (create table, create index,etc) the size of the segments, and thus its block space usage is determined by various parameters:
If these paremetrs are not specified at segment creation, then the database will use the default parameters which are stored in the views DBA_TABLESPACES and USER_TABLESPACES.
The data segments are separated on the basis of I/O access, number of updates, size of the rows in each segment,and other factors. As a good rule of thumb TABLE and INDEX extents must be in different tablespace because ther I/O is concurrent. Rollback segments due to their dynamic nature are stored in a separate tablespace to avoid fragmentation,etc,etc.