The System Global Area in a Oracle database is the area in memory to facilitates the trfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is Database buffers, Dictionary cache, Redo Log Buffer and Shared SQL pool (ver 7.0 only) area.
By using dictionary views called v$rollstat,dba_rollback_segs.
Due to syntax errors.
Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently.
LOG_BUFFER parameter will decide the size.
A flag to indicate whether file content should be displayed or not.
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
Dictionary cache is information about the database objects stored in a data dictionary table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.
A flag to indicate whether indexes on tables will be exported.
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.
A flag to indicate whether grants on database objects will be imported.
The name of the file from which import should be performed.
Record length in bytes.
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL
A flag to indicate whether full databse export should be performed.
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.
A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.
Yes. It is possible to use raw devices as data files.
The advantages over file system files:
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
To manage the database level tractions such as modifications of the data dictionary table that record information about the free space usage.
The Control File is a small binary file necessary for the database to start and operate successfully. Each Control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to USE.
The Control file is not accessible, the database does not function properly.
Hit Ratio is the ratio of shared SQL and PL/SQL items found in the Library Cache versus physical storage.It can also be defined in a mathematical expression as 1 - ((physical reads) / (db block gets + consistent reads)).
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
LGWR process writes all change vectors from the redo log buffer to online redo log file sequentially.
The name of the file which log of the export will be written.
Multiplexing Redo log file called Mirroring. ( Keeping multiple copies in different disks)
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
List of tables should be exported.
The space used traction entries and a deleted record does not become free immediately after completion due to delayed cleanout.
Trailing nulls and length bytes are not stored. Inserts of, updates to and deletes of rows as well as columns larger than a single datablock, can cause fragmentation and chained row pieces.
It is area in memory that is used by a Single Oracle User Process.
Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
By correctly fitting optimal size.
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.
When a user updated a particular table (for example 100 rows) the old value will be retained in the roll back segments(Oracle 8) and now it is Undo segment (oracle 9i). If the user issue a rollback command the old value will be taken from the rollback segment(that too if undo_retention parameter set properly in the parameter file).
Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk.
Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
To avoid the space wastage we use OPTIMAL parameter.
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
Name of the parameter file to be passed for export.
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
Each block contains entries as follows:
Store data in tablespaces to avoid disk contension.system tablespace-recursive callsuserdata-users objectsindex tablespace-for indexesrollback segmnets-undo tablespace or manual rollback segmentsplace application specific data in respective tablespaces.Place all these tablespaces in saperate disks.Try to implement raid-0 (striping) for better performance.
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
When ever changes happened to the database previous change will be there in the rollback segment.