Database Logical & Physical Architecture

64. What is Database Buffers?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database
such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in
INIT.ORA decides the size.

65. What is dictionary cache?
Dictionary cache is information about the database objects stored in a data
dictionary table.

66. What is meant by recursive hints?
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.

67. What is redo log buffer?
Changes made to the records 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.

68. How will you swap objects into adifferent table space for an existing
database?
- Export the user
- Perform import using the command imp system/manager file=export.dmp
indexfile=newrite.sql. This will create all definitions into newfile.sql.
- Drop necessary objects.
- Run the script newfile.sql after altering the tablespaces.
- Import from the backup for the necessary objects.

69. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have
maximum performance?
SYSTEM - Data dictionary tables. DATA
- Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.

70. How will you force database to use particular rollback segment?
SET TRANSACTION USE
ROLLBACK SEGMENT rbs_name.

71. What is meant by free extent?
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.

72.Which parameter in Storage clause will reduce number of rows per block?
PCTFREE parameter Row size also reduces no of rows per block.

73. What is the significance of having storage clause?
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 updating, etc.,

74. How does Space allocation table place within a block?
Each block contains entries as follows Fixed block header Variable block header Row Header, row date (multiple rows
may exists) PCTEREE (% of free space for row
updating in future)

75. What is the role of PCTFREE parameter is storage clause?
This is used to reserve certain amount of space in a block for expansion of rows.

76. What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback segment.
77. What is the functionality of SYSTEM table space?
To manage the database level transactions such as modifications of the
data dictionary table that record information about the free space usage.

78. How will you create multiple rollback segments in a database?
- Create a database, which implicitly
creates a SYSTEM rollback segment in a SYSTEM tablespace.
- Create a second rollback segment name R0 in the SYSTEM tablespace.
- Make new rollback segment available (after shutdown, modify init.ora file and start database)
- Create other tablespaces (RBS) for rollback segments.
- Deactivate rollback segment R0 and activate the newly created rollback
segments.

79. How the space utilization takes
place within rollback segments?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to
acquire a new extent (number of extents is based on the optimal size)

80. Why query fails sometimes?
Rollback segment dynamically extent to handle larger transactions entry loads.
A single transaction may wipeout all
available free space in the rollback segment tablespace. This prevents other user using rollback segments.

81. How will you monitor the space allocation?
By querying DBA_SEGMENT table/view

82. How will you monitor rollback segment status?
Querying the DBA_ROLLBACK_SEGS
view
IN USE - Rollback
Segment is on-line.
AVAILABLE - Rollback
Segment available but not on-line.
OFF-LINE - Rollback
Segment off-line
INVALID - Rollback
Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corrupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving
distributed database.
83. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend. Transaction Begins.
An entry is made in the RES header for new transactions entry
Transaction acquires blocks in an extent of RBS The entry attempts to wrap into second extent. None is available, so that the RBS must extent.
The RBS checks to see if it is part of its OPTIMAL size.
RBS chooses its oldest inactive segment. Oldest inactive segment is eliminated.RBS extents The data dictionary tables for space management are updated. Transaction Completes.

84. How can we plan storage for very large tables?
Limit the number of extents in the table Separate table from its indexes.
Allocate sufficient temporary storage.

85. How will you estimate the space required by a non-clustered tables?
Calculate the total header size
Calculate the available data space per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.

86. It is possible to use raw devices as data files and what are the advantages over file system files?
Yes.
The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing
into disk. Disk corruption will be very less.

87. What is a Control file?
Database's overall physical architecture is maintained in a file called control file. It will be used to maintain internal
consistency and guide recovery operations. Multiple copies of control
files are advisable.

88. How to implement the multiple control files for an existing database?
Shutdown the database Copy one of the existing controlfile to
new location Edit Config ora file by adding new control filename
Restart the database.

89. What is redo log file mirroring? How can be achieved?
Process of having a copy of redo log files is called mirroring.
This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

90. What is advantage of having disk shadowing / mirroring?
Shadow set of disks save as a backup in the event of disk failure. In most
operating systems 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.

91. What is use of rollback segments in Oracle database?
They allow the database to maintain read consistency between multiple
transactions.

92. What is a rollback segment entry?
It is the set of before image data blocks that contain rows that are modified by a transaction.
Each rollback segment entry must be completed within one rollback segment.
A single rollback segment can have multiple rollback segment entries.

93. What is hit ratio?
It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.

94. When will be a segment released?
When Segment is dropped. When Shrink (RBS only) When truncated (TRUNCATE used with drop storage option)

95. What are disadvantages of having raw devices?
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.

96. List the factors that can affect the accuracy of the estimations?
- The space used transaction entries and deleted records, 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 data block, can cause fragmentation a chained row pieces.