Database Administration: Optimization and Recovery Techniques

This report delves into key aspects of database administration, focusing on optimization, recovery, and performance tuning. It addresses issues such as query optimization, the use of the shared pool size, and the library cache. The report covers techniques to avoid hard parses, optimize SQL queries, and manage data partitioning. It also examines the use of RMAN for backup and recovery, including the VALIDATE command and the RMAN> advise failure command for providing repair options. Furthermore, the report highlights the significance of transaction management and the use of techniques like UNION ALL and EXISTS for improved performance. References to relevant research papers support the discussed concepts and techniques.

Database creation, configuration, optimization and recovery
Database Administration
Student Name
Date

Database creation, configuration, optimization and recovery
1. Queries are aged out from the SGA to facilitate the execution of a new query. The
queries are aged out quickly, resulting in a hard parse. This could be avoided by
allocating extra memory to the library cache (Bryla and Loney, 2013). The volume of
the library cache can be raised by updating the value of the SHARED_POOL_SIZE
initialization parameter.
2. The page not found error can be rectified by checking the registry entries for
$ORACLE_HOME and ORACLE_SID and ensuring the connectivity. Checking if
the listener is running properly and restarting the listener could also rectify the error.
The EM environment could also be reinitialized with the emca command.
3. If the data to process is more than the available SQL work area, the data is partitioned
into smaller chunks of data. A portion of the data is processed in the available
memory, while the rest is stored in the temporary tablespace.
4. Using Joins instead of subqueries minimize the processing required. The manual use
of transactions reduces the number of writes to the transaction log. Using UNION
ALL to combine SELECT statements instead of UNION reduces the processing as
duplicates are not avoided (Herodotou et.al, 2011). Using EXISTS instead of
COUNT(*) returns a value as soon as the condition is met instead of checking all
records.
5. The VALIDATE command is used by RMAN to detect corruption and enables the
database to keep information in backup files while doing it. It also reviews a
command block each time it is read or written, to report a timely detection of
corruption (Zhu et.al, 2012). The RMAN> advise failure command is used to give
suggestions about the best repair options available while back up.

References
Herodotou, H., Borisov, N., & Babu, S. (2011, June). Query optimization techniques for
partitioned tables. In Proceedings of the 2011 ACM SIGMOD International Conference on
Management of data (pp. 49-60). ACM.
Zhu, Y. C., Luo, D., Yang, X. R., & Guo, P. C. (2012). RMAN Backup, Recovery and
optimization Based on oracle 10g. Chinese Medical Equipment Journal, 10, 023.
Bryla, B., & Loney, K. (2013). Oracle Database 12c the Complete Reference. McGraw-Hill
Osborne Media.