When are the system change numbers assigned?
1. When a transaction begins.
2. When a transaction ends abnormally
3. When a checkpoint occurs.
4. When a commit is issued.
Answer 4 - A system change number or SCN is assigned when the transaction is committed. The SCN is a unique number acting as an internal timestamp and is used for recovery and read-consistent queries.
Tuesday, 30 June 2009
Monday, 29 June 2009
Architecture and Administration Q018
Which memory structure records all database changes made to the instance?
1. Database buffer.
2. Dictionary Cache
3. Redo Log Buffer
4. Library Cache
Answer 3 - The redo log buffer keeps track of all the changes made to the database before writing them to the redo log files. The database buffer contains the data blocks that are read from data files, and are most recently used. The dictionary cache holds the most recently used data dictionary information. The library cache holds the parsed SQL statements and PL/SQL code.
1. Database buffer.
2. Dictionary Cache
3. Redo Log Buffer
4. Library Cache
Answer 3 - The redo log buffer keeps track of all the changes made to the database before writing them to the redo log files. The database buffer contains the data blocks that are read from data files, and are most recently used. The dictionary cache holds the most recently used data dictionary information. The library cache holds the parsed SQL statements and PL/SQL code.
Sunday, 28 June 2009
Architecture and Administration Q017
What is the minimum number of online redo log files regired in a database.
1. One
2. Two
3. Three
4. Four
5. Zero
Answer 2 - There should be at east two redo log files in a dadtabase. The LGWR process writes to the redo log files in a circular manner, therby implying a minimum of two files, one being written, the other already written to.
1. One
2. Two
3. Three
4. Four
5. Zero
Answer 2 - There should be at east two redo log files in a dadtabase. The LGWR process writes to the redo log files in a circular manner, therby implying a minimum of two files, one being written, the other already written to.
Saturday, 27 June 2009
Architecture and Administration Q016
When a SELECT statement is issued, which stage checks the user's privileges?
1. Parse
2. Fetch
3. Execute
Answer 1 - The parse compiles the SQL statement if there is not an already parsed statement available in the library cache and then checks the privileges. The next stage is the execution stage, when the parsed code is executed. In the fetch stage rows are returned to the user.
1. Parse
2. Fetch
3. Execute
Answer 1 - The parse compiles the SQL statement if there is not an already parsed statement available in the library cache and then checks the privileges. The next stage is the execution stage, when the parsed code is executed. In the fetch stage rows are returned to the user.
Friday, 26 June 2009
Backup and Recovery Q010
What is the main goal of backup and recovery?
a. Performing backups only when necessary.
b. Backing up all files.
c. Keeping backed up files off-site.
d. Minimising data loss and downtime.
Answer D: An effective backup and recovery strategy will minimise data loss and downtime. Answers A,B and C are wrong because they are supplementary goals.
a. Performing backups only when necessary.
b. Backing up all files.
c. Keeping backed up files off-site.
d. Minimising data loss and downtime.
Answer D: An effective backup and recovery strategy will minimise data loss and downtime. Answers A,B and C are wrong because they are supplementary goals.
Architecture and Administration Q015
Which memory structures are shared? Choose two.
1. Sort Area.
2. Program Global Area
3. Library Cache
4. Large Pool
Answer 3 and 4 - The sort area is allocated to the server process as part of the PGA. The PGA is allocated when the server process starts and is deallocated when the server process completes. The library cache and the large pool are part of the SGA and are shared. The SGA is created when the instance starts.
1. Sort Area.
2. Program Global Area
3. Library Cache
4. Large Pool
Answer 3 and 4 - The sort area is allocated to the server process as part of the PGA. The PGA is allocated when the server process starts and is deallocated when the server process completes. The library cache and the large pool are part of the SGA and are shared. The SGA is created when the instance starts.
Thursday, 25 June 2009
Backup and Recovery Q009
You are the newly hired DBA for a pizza delivery company. What should you do to ensure that the previous DBA's abckup and recovery plan is valid?
a. Place 100% reliance on the documentation for the plan if it looks like it should work.
b. Throw away the previous DBA's plan and write your own from scratch.
c. Test the backup and recovery plan to make sure that a complete recovery is possible.
d. Make sure the documentation is correct.
Answer C: All backup and recovery plans should be tested to ensure their validity and effectiveness. Answer a is incorrect because a DBA should not rely solely on documentation to determine validity. Answer B is incorrect because only testing can validate the backup and recovery plan. Answer D is incorrect because testing is required before a DBA can be sure that the documentation is correct.
a. Place 100% reliance on the documentation for the plan if it looks like it should work.
b. Throw away the previous DBA's plan and write your own from scratch.
c. Test the backup and recovery plan to make sure that a complete recovery is possible.
d. Make sure the documentation is correct.
Answer C: All backup and recovery plans should be tested to ensure their validity and effectiveness. Answer a is incorrect because a DBA should not rely solely on documentation to determine validity. Answer B is incorrect because only testing can validate the backup and recovery plan. Answer D is incorrect because testing is required before a DBA can be sure that the documentation is correct.
Architecture and Administration Q014
What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
1. Signals the CKPT process to clean up the dirty buffers.
2. Signals the SMON process to clean up the dirty buffers.
3. Signals the CKPT process to initiate a checkpoint.
4. Signals the DBWn process to clean up the dirty buffers.
Answer 4 - To reduce disk I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold or when there are not enough free buffers available or when a checkpoint occurs.
1. Signals the CKPT process to clean up the dirty buffers.
2. Signals the SMON process to clean up the dirty buffers.
3. Signals the CKPT process to initiate a checkpoint.
4. Signals the DBWn process to clean up the dirty buffers.
Answer 4 - To reduce disk I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold or when there are not enough free buffers available or when a checkpoint occurs.
Wednesday, 24 June 2009
Backup and Recovery Q008
What are the components to develop in a disaster recovery plan (Choose all correct answers)?
a. Data
b. Equipment.
c. Facilities.
d. Personnel.
e. None of the above.
Answer A,B,C, and D: The four components of disaster recovery are data, equipment, facilities and personnel.
a. Data
b. Equipment.
c. Facilities.
d. Personnel.
e. None of the above.
Answer A,B,C, and D: The four components of disaster recovery are data, equipment, facilities and personnel.
Architecture and Administration Q013
If you are updating one row in a table using the ROWID in the WHERE clause, assuming that the row is not already in the buffer cache, what will be the minimum amount of information read to the buffer cache?
1. The entire table is copied to the buffer cache.
2. The extent is copied to the buffer cache.
3. The block is copied to the buffer cache.
4. The row is copied to the buffer cache.
Answer 3 - The block is the smallest unit that can be copied to the buffer cache.
Tuesday, 23 June 2009
Backup and Recovery Q007
How will minimising recovery time affect the business?
a. It reduces the need to regularly update the backup and recovery strategy.
b. It reduces the cost of downtime.
c. It reduces the amount of downtime.
d. It helps to prevent failures from occurring.
Answer B: A reduction in recovery time will shorten total downtime and thus reduce the costs associated with downtime. Answer a is incorrect because minimising recovery time does not affect how often the backup and recovery strategy is updated. Answer c is incorrect because the amount of data loss depends on the availability of valid backups. Answer d is incorrect because appropriate database configuration and not minimisation of recovery time prevents failures from occuring.
a. It reduces the need to regularly update the backup and recovery strategy.
b. It reduces the cost of downtime.
c. It reduces the amount of downtime.
d. It helps to prevent failures from occurring.
Answer B: A reduction in recovery time will shorten total downtime and thus reduce the costs associated with downtime. Answer a is incorrect because minimising recovery time does not affect how often the backup and recovery strategy is updated. Answer c is incorrect because the amount of data loss depends on the availability of valid backups. Answer d is incorrect because appropriate database configuration and not minimisation of recovery time prevents failures from occuring.
Architecture and Administration Q012
What is a dirty buffer?
1. A data buffer that is being accessed.
2. A data buffer that is changed but is not yet written to the disk.
3. A data buffer that is now free.
4. A data buffer that has changed and has been written to the disk.
Answer 2 - Dirty buffers are the buffer blocks that need to be written to the data files. The dat in these files is changed and is not yet written to the disk. A block waiting to be written to the disk is on the dirty list and cannot be overwritten.
1. A data buffer that is being accessed.
2. A data buffer that is changed but is not yet written to the disk.
3. A data buffer that is now free.
4. A data buffer that has changed and has been written to the disk.
Answer 2 - Dirty buffers are the buffer blocks that need to be written to the data files. The dat in these files is changed and is not yet written to the disk. A block waiting to be written to the disk is on the dirty list and cannot be overwritten.
Monday, 22 June 2009
Architecture and Administration Q011
When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
1. DBWn
2. LGWR
3. SMON
4. PMON
Answer 4 - PMON, the Process MONitor is responsible for clearing up the failed user processes. It reclaims all the resources held by the user and releases all locks on tables and rows held by the user.
1. DBWn
2. LGWR
3. SMON
4. PMON
Answer 4 - PMON, the Process MONitor is responsible for clearing up the failed user processes. It reclaims all the resources held by the user and releases all locks on tables and rows held by the user.
Backup and Recovery Q006
What is the main objective for conducting a thorough analysis of the business, operational, and technical needs for backup and recovery?
a. To facilitate effective management decision making by providing information that fosters understanding of all backup and recovery ramifications.
b. To maintain optimal database configuration.
c. To ensure the backup and recovery strategy is sound.
d. To prepare a static backup and recovery strategy.
Answer A: A thorough analysis of the business, operational and technical needs provides management with information so that an effective decision can be made and appropriate resources can be dedicated for the execution of the backup and recovery strategy. Answers B and C are secondary and D is incorrect inthat a backup strategy should never be static, it should evolve with the business.
a. To facilitate effective management decision making by providing information that fosters understanding of all backup and recovery ramifications.
b. To maintain optimal database configuration.
c. To ensure the backup and recovery strategy is sound.
d. To prepare a static backup and recovery strategy.
Answer A: A thorough analysis of the business, operational and technical needs provides management with information so that an effective decision can be made and appropriate resources can be dedicated for the execution of the backup and recovery strategy. Answers B and C are secondary and D is incorrect inthat a backup strategy should never be static, it should evolve with the business.
Sunday, 21 June 2009
Architecture and Administration Q010
Which component in the SGA has the dictionary cache?
1. Buffer Cache.
2. Library Cache
3. Shared Pool
4. Program Global Area
5. Large Pool
Answer 3 - The shared pool has three components: the library cache, the dictionary cache and the control structures.
Backup and Recovery Q005
Your company has a relatively static database that is refreshed monthly. Recovery time is not an issue with the users. Which backup approach should you follow?
a. Perform backups less frequently than a company with high data update activity.
b. Perform backups more frequently than a company with high data update activity.
c. Perform backups each time the database is changed.
d. Backups are not necessary because the data doen not change enough.
Answer A: Depending on the required recovery time, a fairly static database will need less frequent backups than a highly volatile database. Answer b is therefore invalid. Answer c is invalid because a static database that is refreshed monthly does not require taking a backup each time the database is changed. Answer d is incorrect because backups are still necessary, just not as frequent for a static database that doesn't change frequently.
a. Perform backups less frequently than a company with high data update activity.
b. Perform backups more frequently than a company with high data update activity.
c. Perform backups each time the database is changed.
d. Backups are not necessary because the data doen not change enough.
Answer A: Depending on the required recovery time, a fairly static database will need less frequent backups than a highly volatile database. Answer b is therefore invalid. Answer c is invalid because a static database that is refreshed monthly does not require taking a backup each time the database is changed. Answer d is incorrect because backups are still necessary, just not as frequent for a static database that doesn't change frequently.
Saturday, 20 June 2009
Architecture and Administration Q009
Which background process is responsible for writing the dirty buffers to the database files?
1. DBWn
2. SMON
3. LGWR
4. CKPT
5. PMON
Answer 1 - The DBWn process writes the dirty buffers to the data files under two circumstances - when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.
Backup and Recovery Q004
How will offline physical backups affect your database?
a. It will increase disk space requirements.
b. The backups will not be valid.
c. It will increase recovery time from a media failure.
Answer A: A physical backup copies each database file to the target backup location. The
number of files involved in the copy operation can affect disk space. Aswer b is not correct
because offline physical backups are valid backups unless testing indicates that they are not
valid. Answer c is incorrect because offline backups do not increase recovery time from a
media failure. Answer d is incorrect becase offline physical backups are easier to manage
than online backups.
a. It will increase disk space requirements.
b. The backups will not be valid.
c. It will increase recovery time from a media failure.
Answer A: A physical backup copies each database file to the target backup location. The
number of files involved in the copy operation can affect disk space. Aswer b is not correct
because offline physical backups are valid backups unless testing indicates that they are not
valid. Answer c is incorrect because offline backups do not increase recovery time from a
media failure. Answer d is incorrect becase offline physical backups are easier to manage
than online backups.
Friday, 19 June 2009
Architecture and Administration Q008
Which stage is not part of processing a DML statement, but is part of processing a query?
1. Parse.
2. Fetch.
3. Execute.
4. Feedback.
Answer 2 - For rocessing queries, the stages are parse, execute and fetch. For DML statements like SELECT, INSERT, UPDATE and DELETE (Yes SELECT is DML) the stages involved are parse and execute. There is no feedback stage in processing SQL.
1. Parse.
2. Fetch.
3. Execute.
4. Feedback.
Answer 2 - For rocessing queries, the stages are parse, execute and fetch. For DML statements like SELECT, INSERT, UPDATE and DELETE (Yes SELECT is DML) the stages involved are parse and execute. There is no feedback stage in processing SQL.
Backup and Recovery Q003
What is needed to successfully recover an inoperative database?
1. A valid control file.
2. A database parameter file.
3. A valid backup.
4. Online log files.
Answer:
3 - For any type of recovery you need a valid backup. All the other options are components of a backup.
1. A valid control file.
2. A database parameter file.
3. A valid backup.
4. Online log files.
Answer:
3 - For any type of recovery you need a valid backup. All the other options are components of a backup.
Thursday, 18 June 2009
Architecture and Administration Q007
Which component of the SGA contains the parsed SQL code?
1. Buffer Cache.
2. Dictionary Cache.
3. Library Cache.
4. Parse Cache.
Answer 3 - The library cache contains the parsed SQL code. If a query is executed again before it is aged out of the library cache, Oracle will re-use the parsed code and execution plan from the library cache. The buffer cache has data blocks that are cached. The dictionary cache caches data dictionary information. There is no structre called Parse cache.
Backup and Recovery Q002
What is the DBAs most important responsibility?
1. Keeping the database organised.
2. Keeping up to date backups.
3. Maximising database availability for users.
4. Preventing users from corrupting the database.
Answer:
3 - The database is of no use if it is not available for use. 1,2 and 4 are important responsibilities but not the most important.
1. Keeping the database organised.
2. Keeping up to date backups.
3. Maximising database availability for users.
4. Preventing users from corrupting the database.
Answer:
3 - The database is of no use if it is not available for use. 1,2 and 4 are important responsibilities but not the most important.
Wednesday, 17 June 2009
Architecture and Administration Q006
Choose the right heirarchy, from largest to smallest, from the following list of logical database structures.
1. Database, tablespace, extent, segment, block.
2. Database, tablespace,segment, extent, block.
3. Database, segment, tablespace, extent, block.
4. Database, extent, talespace, segment, block.
Answer 2 - The first logical level of the database is the tablespace. A tablespace may have segments. Segments have one or more extents, and extents have one or more contiguous blocks of data.
Backup and Recovery Q001
Which of the following statements is true?
1. Your backup strategy will not affect recoverability.
2. Your backup strategy will determine if rollback segments are needed.
3. Your backup strategy will help you get management support.
4. Your backup strategy will determine if you can do a complete or incomplete recovery.
Answer:
4 - Your backup strategy will determine the types of recovery that are possible in the event of a failure.
1. Your backup strategy will not affect recoverability.
2. Your backup strategy will determine if rollback segments are needed.
3. Your backup strategy will help you get management support.
4. Your backup strategy will determine if you can do a complete or incomplete recovery.
Answer:
4 - Your backup strategy will determine the types of recovery that are possible in the event of a failure.
Tuesday, 16 June 2009
Architecture and Adminisration Q005
Which of the following best describes a parallel server configuration?
1. One database, multiple instances.
2. One instance, multiple databases.
3. Multiple databases on multiple servers.
4. Shared server processes handle many users.
Answer 1 : In a parallel server configuration there are multiple instances mounted on one database. An instance is only ever associated with one database, but a database can have multiple instances or nodes.
This is now called RAC in later versions of Oracle 9i, 10g and 11g.
Monday, 15 June 2009
Architecture and Administration Q004
Which background process is not started up by default when you start up the Oracle instance?
1. DBWn
2. LGWR
3. CKPT
4. ARCn
Answer 4 : ARCn is the archiver process, which only started when LOG_ARCHIVE_START = TRUE in the initialization parameters. The default processes are DBWn, LGWR, CKPT, SMON and PMON.
1. DBWn
2. LGWR
3. CKPT
4. ARCn
Answer 4 : ARCn is the archiver process, which only started when LOG_ARCHIVE_START = TRUE in the initialization parameters. The default processes are DBWn, LGWR, CKPT, SMON and PMON.
Sunday, 14 June 2009
Architecture and Administration Q003
What is the maximum number of database writer processes allowed in an Oracle Instance?
1. 1
2. 10
3. 256
4. Set by underlying OS.
Answer 2: By default, every Oracle instance has one database writer process started DBW0. You can start additional processes by changing the value of the initialization parameter DB_WRITER_PROCESSES to a value of 10. This will give you DBW1 - DBW9 additionally.
1. 1
2. 10
3. 256
4. Set by underlying OS.
Answer 2: By default, every Oracle instance has one database writer process started DBW0. You can start additional processes by changing the value of the initialization parameter DB_WRITER_PROCESSES to a value of 10. This will give you DBW1 - DBW9 additionally.
Saturday, 13 June 2009
Architecture and Administration Q002
Todays DBA Question is on Oracle Architecture and Administration.
Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the database files?
1. DBWn and the database buffer cache
2. LGWR ond online redo log file
3. CKPT and control file
4. DBWn and archived redo log file
The answer - 2 LGWR ond online redo log file. The LGWR process writes the redo log buffer entries when a COMMIT occurs. The redo log buffer holds the information on the changes made to the database.
The DBWn process writes dirty buffers to the datafile but is independant of COMMIT. The dirty buffers can be written to the disk before or after a COMMIT. Writing the commited changes to the online redo log file ensures that the changes are not lost in case of a failure.
For more on Oracle certification visit
Oracle Certification for details and Sybex
Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the database files?
1. DBWn and the database buffer cache
2. LGWR ond online redo log file
3. CKPT and control file
4. DBWn and archived redo log file
The answer - 2 LGWR ond online redo log file. The LGWR process writes the redo log buffer entries when a COMMIT occurs. The redo log buffer holds the information on the changes made to the database.
The DBWn process writes dirty buffers to the datafile but is independant of COMMIT. The dirty buffers can be written to the disk before or after a COMMIT. Writing the commited changes to the online redo log file ensures that the changes are not lost in case of a failure.
For more on Oracle certification visit
Oracle Certification for details and Sybex
Friday, 12 June 2009
Architecture and Administration Q001
Todays DBA Question is on Oracle Architecture and Administration.
Which component is not part of the Oracle Instance
1. System Global Area
2. Process Monitor
3. Control File
4. Shared Pool
The answer - 3 The Oracle Instance is made up of memory structures and background processes. The database is made up of physical files, including the control file. The Shared Pool and SGA are memory structures and PMON is a background process.
For more on Oracle certification visit
Oracle Certification for details and Sybex
Which component is not part of the Oracle Instance
1. System Global Area
2. Process Monitor
3. Control File
4. Shared Pool
The answer - 3 The Oracle Instance is made up of memory structures and background processes. The database is made up of physical files, including the control file. The Shared Pool and SGA are memory structures and PMON is a background process.
For more on Oracle certification visit
Oracle Certification for details and Sybex
Subscribe to:
Comments (Atom)