DB Management tools Flashcards
Q: What is an “abort” shutdown in Oracle?
An “abort” shutdown, also known as a “fast” or “immediate” shutdown, is a method of shutting down an Oracle database that disconnects all connected users and terminates all background processes immediately, regardless of any active transactions.
When should I use an “abort” shutdown?
An “abort” shutdown should be used in emergency situations when the database needs to be shut down immediately and there is no time for a “normal” or “transactional” shutdown. This can be used when there is a database corruption or when the database is stuck in a hung state and normal shutdown is not possible.
what is SHUTDOWN NORMAL
A “normal” shutdown is a way to turn off an Oracle Database. It makes sure all the work is done before turning off, just like you finish your work before going to bed. It is a safe way to stop the database.
What does Shutdown TRANSACTIONAL do
A “transactional” shutdown is a way to turn off an Oracle Database in a way that allows to finish any ongoing transaction before shutdown, it means that it will wait for all ongoing transactions to complete or rollback before shutting down the database. It’s a safe way to stop the database, but it may take longer than other methods
What is a immediate shutdown
A “immediate” shutdown is a way to turn off an Oracle Database quickly, without waiting for any ongoing transactions to complete. It’s like turning off the lights and leaving the house quickly, without waiting for everyone to be done with what they’re doing. It’s a fast way to stop the database, but it may cause some unsaved changes to be lost.
What is the difference between Shutdown IMMEDIATE and Shutdown ABORT
The difference between “immediate” and “abort” shutdown is that “immediate” shutdown will wait for all active transactions to rollback before shutting down the database, while “abort” shutdown will terminate all background processes and disconnect all connected users immediately, regardless of any active transactions. Both options will cause any unsaved changes to be lost, but “abort” shutdown may cause more data corruption if the shutdown process is not handled properly.
What is the difference between Shutdown IMMEDIATE and Shutdown TRANSACTIONAL
-“Immediate” shutdown will terminate all background processes and disconnect all connected users without waiting for any active transactions to complete or rollback.
-“Transactional” shutdown will wait for all active transactions to complete or rollback before shutting down the database.
-Both options will cause any unsaved changes to be lost.
-“Transactional” shutdown may take longer than an “immediate” shutdown.
Which command performs a clean shutdown without waiting for clients to disconnect?
SHUTDOWN IMMEDIATE
Q: What is the Log Writer (LGWR) process in Oracle Database?
A: The Log Writer (LGWR) process is a background process in Oracle Database that is responsible for writing redo log entries to the online redo log files.
Q: How does LGWR work?
A: LGWR constantly monitors the redo log buffer in the SGA (System Global Area) for new redo log entries. Once the buffer is full or there is a specified time interval, LGWR writes the contents of the buffer to the online redo log files. This process is repeated continuously to ensure that all changes made to the database are captured in the redo logs.
Q: What is the importance of LGWR in the context of database recovery?
A: LGWR plays a critical role in the context of database recovery. The redo log files written by LGWR contain all the information necessary to recover the database in case of a failure. In the event of a failure, the database can be recovered by applying the redo log entries to the last known good state of the database
Q: Can LGWR be configured?
A: Yes, LGWR can be configured. For example, you can configure the size of the redo log buffer, the frequency at which LGWR writes to the redo log files, and the number of redo log files that should be used. This can be done via the initialization parameters in the database configuration file or via the ALTER SYSTEM statement.
Q: Is there any effect on performance if LGWR is slow?
A: If LGWR is slow, it can cause a bottleneck in the system and affect overall performance. This can happen if the redo log buffer is too small, the redo log files are not properly configured or if the storage system is slow. When this happens, LGWR will spend more time writing to the redo log files and less time processing new transactions, resulting in slow performance of the database.
Which package should an administrator use to configure local extent management for tablespaces?
DBMS_SPACE_ADMIN
Q: What is the Oracle Database Process Monitor?
A: The Oracle Database Process Monitor (PMON) is a background process in Oracle Database that is responsible for cleaning up after failed or terminated user processes, and for recovery of failed processes.
Q: What are the tasks of PMON?
Cleaning up after failed or terminated user processes by releasing resources such as locks and memory.
Monitoring and recovering failed database connections.
Monitoring and restarting failed Dispatcher and Server processes.
Coordinating instance recovery in a Real Application Clusters (RAC) environment.
Q: How does PMON help in the recovery process?
A: PMON plays an important role in the recovery process by detecting failed or terminated user processes, and releasing any resources they were using so that they can be reused by other processes. It also monitors and recovers failed database connections, and restarts failed Dispatcher and Server processes to ensure that the database is always available.
Q: Is PMON configurable?
A: PMON is not configurable as it is an integral part of the Oracle Database system and its behavior is controlled by the Oracle Database software. However, some of its related functionalities can be managed via database initialization parameters, for example PMON_DUMP_TIMER which controls how frequently PMON writes diagnostic information to the alert log.
Q: What is the impact on performance if PMON is not working properly?
A: If PMON is not working properly, it can cause various issues such as memory leaks, un-released locks and stuck processes, which can lead to poor performance and possibly even bring down the database. Additionally, it can cause the database to be less available and can lead to recovery failures.
Q: What is Oracle DBMS_SPACE_ADMIN package?
A: The DBMS_SPACE_ADMIN package is a set of procedures and functions provided by Oracle Database for managing space allocation and usage within the database.
Q: What are the functionalities provided by DBMS_SPACE_ADMIN package?
A: Some of the functionalities provided by DBMS_SPACE_ADMIN package include:
Managing space allocation for tablespaces, segments and extents.
Reclaiming unused space within a tablespace.
Compacting and defragmenting tablespaces and segments.
Managing temporary tablespaces and temporary segments.
Gathering statistics on space usage and fragmentation.
Q: How can DBMS_SPACE_ADMIN be used to manage space allocation?
A: DBMS_SPACE_ADMIN provides procedures such as ALLOCATE_EXTENT and DEALLOCATE_EXTENT that can be used to manage space allocation for tablespaces, segments and extents. These procedures can be used to allocate and deallocate space as needed, allowing for more precise control over space usage within the database.
Q: How can DBMS_SPACE_ADMIN be used to reclaim unused space?
DBMS_SPACE_ADMIN provides a procedure called SHRINK_Tablespace that can reclaim unused space within a tablespace. This procedure can be used to shrink a tablespace to a specified size or a percentage of its current size, effectively reclaiming any unused space.
Q: How can DBMS_SPACE_ADMIN be used to reclaim unused space?
A: DBMS_SPACE_ADMIN provides a procedure called SHRINK_Tablespace that can be used to reclaim unused space within a tablespace. This procedure can be used to shrink a tablespace to a specified size or to a percentage of its current size, effectively reclaiming any unused space.
Q: How can DBMS_SPACE_ADMIN be used to manage temporary tablespaces?
A: DBMS_SPACE_ADMIN provides procedures such as CREATE_TEMP_TABLESPACE and DROP_TEMP_TABLESPACE that can be used to create and drop temporary tablespaces. It also provides procedures such as ADD_TEMP_FILE and DROP_TEMP_FILE for managing temp files for temporary tablespaces.
Q: How can DBMS_SPACE_ADMIN be used to manage temporary tablespaces?
A: DBMS_SPACE_ADMIN provides procedures such as CREATE_TEMP_TABLESPACE and DROP_TEMP_TABLESPACE that can be used to create and drop temporary tablespaces. It also provides procedures such as ADD_TEMP_FILE and DROP_TEMP_FILE for managing temp files for temporary tablespaces.
Is there any permission need to access DBMS_SPACE_ADMIN package?
To use the procedures and functions in the DBMS_SPACE_ADMIN package, you need the EXECUTE privilege on the package. This privilege is typically granted to database administrators and other users with similar responsibilities.
Q: What is Oracle DBMS_AUDIT_MGMT package?
A: The DBMS_AUDIT_MGMT package is a set of procedures and functions provided by Oracle Database for managing and purging audit data in the audit trai
Q: What are the functionalities provided by DBMS_AUDIT_MGMT package?
: Some of the functionalities provided by DBMS_AUDIT_MGMT package include:
Purging old audit data from the audit trail.
Archiving audit data to another location.
Managing the audit trail’s storage and retention.
Compressing and encrypting the audit trail data.
Auditing and reporting on the use of the DBMS_AUDIT_MGMT packag
How can DBMS_AUDIT_MGMT be used to purge old audit data?
DBMS_AUDIT_MGMT provides procedures such as PURGE_AUDIT_TRAIL and PURGE_UNIFIED_AUDIT_TRAIL that can be used to purge old audit data from the audit trail. These procedures can be used to remove audit data that is no longer needed, such as data that is older than a specified date or data that is associated with a specific user or action.
How can DBMS_AUDIT_MGMT be used to archive audit data?
DBMS_AUDIT_MGMT provides procedures such as EXPORT_AUDIT_TRAIL and EXPORT_UNIFIED_AUDIT_TRAIL that can be used to export audit data to another location, such as a file or a remote database. These procedures can be used to create an archive of audit data for compliance or reporting purposes.
How can DBMS_AUDIT_MGMT be used to manage the storage and retention of the audit trail?
DBMS_AUDIT_MGMT provides procedures such as SET_AUDIT_TRAIL_LOCATION and SET_UNIFIED_AUDIT_TRAIL_LOCATION that can be used to manage the storage and retention of the audit trail. These procedures can be used to change the location where the audit trail is stored, and to set the retention period for the audit trail
Is there any permission need to access DBMS_AUDIT_MGMT package?
To use the procedures and functions in the DBMS_AUDIT_MGMT package, you need the EXECUTE privilege on the package. This privilege is typically granted to database administrators and other users with similar responsibilities, or to those who have been granted the AUDIT_ADMIN role.
What is the Oracle DBMS_TRANSFORM package?
The DBMS_TRANSFORM package is a set of procedures and functions provided by Oracle Database for performing data type conversion on table columns.
What are the functionalities provided by DBMS_TRANSFORM package?
Converting one data type to another.
Converting a column of one data type to another data type.
Changing the length and precision of a column
Transforming a column of one data type to another data type while preserving the data.
Generating a SQL statement that can be used to transform a table column.
How can DBMS_TRANSFORM be used to convert a column data type?
DBMS_TRANSFORM provides procedures such as COLUMN_TRANSFORM, COLUMN_TRANSFORM_CV, COLUMN_TRANSFORM_CT, COLUMN_TRANSFORM_CC which can be used to convert a column data type. These procedures can be used to convert a column of one data type to another data type and can also be used to change the length, precision and scale of the column
How can DBMS_TRANSFORM be used to generate a SQL statement for column transformation?
DBMS_TRANSFORM package provides procedures such as COLUMN_TRANSFORM_SQL which can be used to generate a SQL statement for column transformation. This procedure can be used to generate a SQL statement that can be used to transform a table column, without actually performing the transformation, in order to test or review the statement before applying it.
Can DBMS_TRANSFORM be used for LOB columns?
Yes, DBMS_TRANSFORM can be used to transform LOB columns. The package provides procedures such as COLUMN_TRANSFORM_BLOB, COLUMN_TRANSFORM_CLOB and COLUMN_TRANSFORM_NCLOB which can be used to transform LOB columns
Is there any permission need to access DBMS_TRANSFORM package?
To use the procedures and functions in the DBMS_TRANSFORM package, you need the EXECUTE privilege on the package. This privilege is typically granted to database administrators and other users with similar responsibilities
What are LOB columns?
LOB (Large Object) columns are a data type in Oracle Database used to store large amounts of binary or character data. LOB columns can store data up to 4 GB in size and can be used to store data such as images, audio, video and other types of unstructured data. There are three types of LOB columns in Oracle Database: BLOB (Binary Large Object), CLOB (Character Large Object) and NCLOB (National Character Large Object).
LOB columns are typically used to store large amounts of unstructured data that do not fit well in traditional relational columns. They are also used to store data that is not frequently accessed, as LOB data is stored out-of-line, separate from the rest of the row, which can affect performance when compared to storing the data in-line.
BLOB
columns store binary data, such as images or audio files.
CLOB
columns store character data, such as large text documents.
NCLOB
columns store national character data, which is similar to CLOB but with support for multiple character sets.
COLUMN_TRANSFORM
This procedure is used to convert a column data type to another data type. It can also change the length, precision and scale of the column. The data in the column is preserved during the transformation.
COLUMN_TRANSFORM_CV
This procedure is similar to COLUMN_TRANSFORM but it verifies the data after the transformation to ensure that the data is still valid and can be inserted into the new column. If any of the data is invalid, an exception is raised and the transformation is not completed.
COLUMN_TRANSFORM_CT
This procedure is similar to COLUMN_TRANSFORM but it creates a new table that contains the transformed column and copies the data from the original table to the new table. The original table is not modified.
COLUMN_TRANSFORM_CC
This procedure is similar to COLUMN_TRANSFORM_CV but it creates a new table and copies the data from the original table to the new table. The original table is not modified and the data is verified after the transformation to ensure that the data is still valid and can be inserted into the new column. If any of the data is invalid, an exception is raised and the transformation is not completed.
What is Oracle DBMS_METADATA package?
The DBMS_METADATA package is a set of procedures and functions provided by Oracle Database for extracting metadata information from the database.
What are the functionalities provided by DBMS_METADATA package?
Extracting the DDL (Data Definition Language) for database objects such as tables, views, indexes, and procedures.
Extracting the DDL for an entire schema or a specific object.
Extracting the DDL in a specific format such as SQL, XML or CREATE scripts.
Extracting the comments and other attributes associated with a database object.
Extracting the statistics and other information about an object.
How can DBMS_METADATA be used to extract the DDL for a specific object?
DBMS_METADATA provides procedures such as GET_DDL and GET_XML for extracting the DDL for a specific object. These procedures can be used to extract the DDL for a table, view, index, procedure, etc.
How can DBMS_METADATA be used to extract the DDL for a specific schema?
DBMS_METADATA provides procedures such as GET_SCHEMA and GET_XML_SCHEMA for extracting the DDL for a specific schema. These procedures can be used to extract the DDL for all objects in a schema such as tables, views, indexes, procedures, etc.
How can DBMS_METADATA be used to extract the comments and attributes of a database object?
DBMS_METADATA provides procedures such as GET_COMMENTS
What should an administrator use to identify tables that are fragmented?
Segment Advisor
What is the Oracle Segment Advisor?
The Segment Advisor is a feature in Oracle Database that provides recommendations for managing segments (such as tables and indexes) in the database. It analyzes the segments in the database and provides recommendations for improving performance, reducing space usage, and reclaiming unused space.
What are the functionalities provided by Segment Advisor
Identifying segments that are consuming too much space
Identifying segments that have excessive fragmentation
Recommending actions such as reorganizing, shrinking, or dropping segments
Identifying opportunities to create bitmap indexes
Analyzing segments in Real Application Clusters (RAC) environment
How can Segment Advisor be used to improve performance?
The Segment Advisor can be used to identify segments that are consuming too much space, or have excessive fragmentation, which can negatively impact performance. It can recommend actions such as reorganizing, shrinking, or dropping segments, which can improve performance by reducing the amount of I/O required to access the data.
How can Segment Advisor be used to reclaim unused space?
The Segment Advisor can be used to identify segments that are consuming too much space and recommend actions such as shrinking or dropping segments. This can help to reclaim unused space in the database and free up resources for other purposes.
Is there any permission need to access Segment Advisor?
To use the Segment Advisor, you need the ADVISOR privilege. This privilege is typically granted to database administrators and other users with similar responsibilities.
How is Segment Advisor related to other Automatic Workload Repository (AWR)?
The Segment Advisor uses statistics and other information from the AWR to make its recommendations
How is Segment Advisor related to other Automatic Database Diagnostics Monitor (ADDM)?
ADDM can detect performance issues related to segments and trigger the Segment Advisor to run and analyze the segments.
How is Segment Advisor related to other Enterprise Manager Database Express
The Segment Advisor can be accessed and run through the Enterprise Manager Database Express web-based interface.
How is Segment Advisor related to other DBMS_SPACE_ADMIN?
The Segment Advisor can recommend actions such as shrinking or dropping segments, which can be implemented using the procedures of the DBMS_SPACE_ADMIN package.
What is the Oracle Automatic Database Diagnostic Monitor (ADDM)?
The Automatic Database Diagnostic Monitor (ADDM) is a feature in Oracle Database that automatically diagnoses and resolves performance issues in the database. It analyzes performance data and provides recommendations for improving performance, identifying the root cause of performance problems, and identifying opportunities for performance optimization.
What are the functionalities provided by ADDM?
Identifying and diagnosing performance issues in the database
Identifying the root cause of performance problems
Providing recommendations for performance optimization
Analyzing performance data in Real Application Clusters (RAC) environment
Generating detailed performance reports
Automatically triggering other performance-related features such as the Segment Advisor to run
How can ADDM be used to improve performance?
ADDM can be used to automatically identify and diagnose performance issues in the database, and provide recommendations for performance optimization. It can identify the root cause of performance problems, such as poorly performing SQL statements or contention for resources, and provide recommendations for resolving these issues.
How can ADDM be used to generate performance reports?
ADDM can be used to generate detailed performance reports, which can be used to analyze performance over time and identify trends or patterns. These reports can be used to identify performance bottlenecks and opportunities for optimization.
Is there any permission need to access ADDM?
To use the ADDM, you need the ADMINISTER DIAGNOSTIC PACKAGE privilege. This privilege is typically granted to database administrators and other users with similar responsibilities.
How does ADDM work?
ADDM runs automatically at regular intervals and analyzes performance data collected by the Automatic Workload Repository (AWR) and other sources. It uses this data to identify and diagnose performance issues, identify the root cause of problems, and provide recommendations for resolving issues and optimizing performance.
