Interview Q and A for Oracle Architecture Part - 2

101. What is Log Switch ?

The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

102. What are the steps involved in Instance Recovery ?

R_olling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments.

Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

103. What is a Database instance ? Explain

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

104. What are the basic element of Base configuration of an oracle Database ?

It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas, one or more rollback segments, one or more tablespaces, Data dictionary Tables, User objects (table,indexes,views etc.,), The server that access the database consists of SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool), SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write) DBWR (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher, User Process with associated PGS,

105. What is a deadlock ? Explain .

Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically. These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

106. What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient Shared SQL pool size. Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is

greater than 1 then increase the SHARED_POOL_SIZE.

107. 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.

108. What is dictionary cache ?

Dictionary cache is information about the databse objects stored in a data dictionary table.

109. 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.

110. What is meant by redo log buffer ?

Change 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

111. List the Optional Flexible Architecture (OFA) of Oracle database ? or How can we organise 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.

112. How will you force database to use particular rollback segment ?

SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.

113. 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.

114. How free extents are managed in Ver 6.0 and Ver 7.0 ?

Free extents cannot be merged together in Ver 6.0. Free extents are periodically coalesces with the neighboring free extent in Ver 7.0

115.Which parameter in Storage clause will reduce no. of rows per block?

PCTFREE parameter Row size also reduces no of rows per block.

116. 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 updations etc.,

117. How does Space allocation table place within a block ?

Each block contains entries as follows, Fixied block header, Variable block header, Row Header,row date (multiple rows may exists), PCTEREE (% of free space for row updation in future)

118. 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.

119. What is the OPTIMAL parameter ?

It is used to set the optimal length of a rollback segment.

120. 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.

121. 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 (No. of extents is based on the optimal size)

122. Why query fails sometimes ?

Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all avaliable free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments.

123. How will you monitor the space allocation ?

By quering DBA_SEGMENT table/view.

124. 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 corupted. PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database.

125. 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 table for space management are updated. Transaction Completes.

126. 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.

127. How will you estimate the space required by a non-clustered tables?

Calculate the total header size, Calculate the available dataspace 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.

128. It is possible to use raw devices as data files and what is the advantages over file. system files ?

Yes. The advantages over file system files. I/O will be improved because Oracle is byepassing the kernnel which writing into disk. Disk Corruption will be very less.

129. 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.

130. How to implement the multiple control files for an existing database ?

Shutdown the databse. Copy one of the existing control file to new location. Edit lnlt ora file by adding new control file.name. Restart the database.

131. What is meant by Redo Log file mirrorring ? How it 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.

132. 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 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.

133. What is use of Rollback Segments In Database ?

They allow the database to maintain read consistency between multiple transactions.

134. 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.

135. 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.

136. When will be a segment released ?

When Segment is dropped. When Shrink (RBS only). When truncated (TRUNCATE used with drop storage option).

137. What are disadvanteges 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.

138. 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. Trailling 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 an chained row pieces.

139. What is user Account in Oracle database ?

An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.

140. How will you enforce security using stored procedures ?

Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

141. What are the dictionary tables used to monitor a database spaces ?

DBA_FREE_SPACE, DBA_SEGMENTS, DBA_DATA_FILES.

142. What are the responsibilities of a Database Administrator ?

Installing and upgrading the Oracle Server and application tools. Allocating system storage and planning future storage requirements for the database system. Managing primary database structures (tablespaces). Managing primary objects (table,views,indexes). Enrolling users and maintaining system security. Ensuring compliance with Oralce license agreement. Controlling and monitoring user access to the database. Monitoring and optimising the performance of the database. Planning for backup and recovery of database information. Maintain archived data on tape. Backing up and restoring the database. Contacting Oracle Corporation for technical support.

143. What are the roles and user accounts created automatically with the database ?

DBA - role Contains all database system privileges. SYS user account - The DBA role will be assigned to this account. All of the basetables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE.

SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.

144. What are the database administrators utilities avaliable ?

SQL * DBA - This allows DBA to monitor and control an ORACLE database.

SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables.

Export (EXP) and Import (imp) utilities allow you to move existing data in ORACLE format to and from ORACLE database.

145. What are the minimum parameters should exist in the parameter file (init.ora) ?

DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation.

DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters (DB_NAME & DB_DOMAIN)

CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used. DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA.

PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.

 ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup.

Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.

146. What is a trace file and how is it created ?

Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.

147. What are roles ? How can we implement roles ?

Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provies to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.

148. How can you enable automatic archiving ?

Shut the database. Backup the database. Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file. Start up the database.

149. How can we specify the Archived log file name format and destination ?

By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left paded, %s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.

150. What is the use of ANALYZE command ?

To perform one of these function on an index,table, or cluster: - to collect statisties about object used by the optimizer and store them in the data dictionary. - to delete statistics about the object used by object from the data dictionary. - to validate the structure of the object. - to identify migrated and chained rows of the table or cluster.

151. What are the benefits of distributed options in databases ?

Database on other servers can be updated and those transactions can be grouped together with others in a logical unit. Database uses a two phase commit.

152. How do you switch from an init.ora file to a spfile?

Issue the create spfile from pfile command.

153. Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

154. Give two examples of how you might determine the structure of the table DEPT.

Use the describe command or use the dbms_metadata.get_ddl package.

156. Where would you look for errors from the database engine?

In the alert log.

157. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode

and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

158. Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup

STARTUP MOUNT - The database is mounted

STARTUP OPEN - The database is opened

159. What column differentiates the V$ views to the GV$ views and how?

The INST_ID column which indicates the instance in a RAC environment the information came from.

160. How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.

Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement

Look at the explain plan with utlxplp.sql or utlxpls.sql

161. How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

162. Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

163. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle.

ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

164. How would you determine the time zone under which a database was operating?

select DBTIMEZONE from dual;

165. Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

166. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

Database Cache Advisory

Shared Pool Advisory

PGA Advisory

SHARED_POOL_ADVICE

PGA_TARGET_ADVICE

DB_CACHE_ADVICE

167. Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

168. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

169. When a user process fails, what background process cleans up after it?

PMON

170. What background process refreshes materialized views?

The Job Queue Processes.

171. How would you determine what sessions are connected and what resources they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

172. How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

173. Give two methods you could use to determine what DDL changes have been made.

You could use Logminer or Streams

174. What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

175. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

176. Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

177. When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.

178. How do you add a data file to a tablespace?

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

179. How do you resize a data file?

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

180. What view would you use to look at the size of a data file?

DBA_DATA_FILES

181. What view would you use to determine free space in a tablespace?

DBA_FREE_SPACE

182. How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

183. How can you rebuild an index?

ALTER INDEX <index_name> REBUILD;

184. Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

185. How can you gather statistics on a table?

The ANALYZE command.

186. How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or  Use ALTER SESSION SET SQL_TRACE = TRUE;

187. What is the difference between the SQL*Loader and IMPORT utilities?

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

188. Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

189. What are the four Oracle system processes that must always be up and running for the database to be useable

The four Oracle system processes that must always be up and running for the database to be useable include DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), and PMON (Process Monitor).

190. What are database files, control files and log files. How many of these files should a database have at least? Why?

Database Files. The database files hold the actual data and are typically the largest in size. Depending on their sizes, the tables (and other objects) for all the user accounts can go in one database file—but that's not an ideal situation because it does not make

the database structure very flexible for controlling access to storage for different users, putting the database on different disk drives, or backing up and restoring just part of the database.

You must have at least one database file but usually, more than one files are used. In terms of accessing and using the data in the tables and other objects, the number (or location) of the files is immaterial. The database files are fixed in size and never grow bigger than the size at which they were created.

Control Files :The control files and redo logs support the rest of the architecture. Any database must have at least one control file, although you typically have more than one to guard against loss. The control file records the name of the database, the date and time it was created, the location of the database and redo logs, and the synchronization information to ensure that all three sets of files are always in step. Every time you add a new database or redo log file to the database, the information is recorded in the control files.

Redo Logs: Any database must have at least two redo logs. These are the journals for the database; the redo logs record all changes to the user objects or system objects. If any type of failure occurs, the changes recorded in the redo logs can be used to bring the database to a consistent state without losing any committed transactions. In the case of non-data loss failure, Oracle can apply the information in the redo logs automatically without intervention from the DBA. The redo log files are fixed in size and never grow dynamically from the size at which they were created.