Oracle 11g Database New Features Interview Questions and Answers
1. Database Replay
2. The SQL Performance Analyzer
3. Online Patching in Oracle Database Control
4. Automatic Diagnostic Repository (ADR)
5. Data Recovery Advisor
6. Automatic Memory Management
7. Invisible Indexes
8. Read-Only Tables
9. Shrinking Temporary Tablespaces and Tempfiles
10. Server Result Cache
11. SQL Tuning Automation
12. SQL Plan Management
13. Database ADDM
14. New SYSASM Privilege for ASM Administration
15. Enhanced Block Media Recovery
16. VALIDATE Command
17. Configuring an Archived Redo Log Deletion Policy
18. Active Database Duplication
19. Virtual Private Catalogs
20. ASM Restricted Mode
21. Checking Diskgroup
22. The FORCE option with Drop Diskgroup Command
23. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
ASM Rolling Upgrades
Database Replay
Database Replay (sometimes named as Workload Replay) feature in Oracle11g allows you to reproduce the
production database conditions in a testing environment.In other words, with this feature you can capture the
actual workload on a production system and replay it in a test system. This way, you can analyze the condition of the production database without working on the actual production database.
This feature enables you to test the impact of applying changes on a production database. These changes could be database upgrades, switching to RAC, application upgrades, operating system upgrades or storage system changes.
The SQL Performance Analyzer
The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL statements, it provides you recommendations on how to improve their performance.
This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it to study the effect of upgrading an Oracle database 10g release 2 to 11g.
Note:
If you plan to use SPA on a test database, it is highly recommended to make the test database resemble the production database as closely as possible. You can use the RMAN duplicate command for this purpose.
Online Patching in Oracle Database Control
Patching through Database Control is enhanced in Oracle 11g. With Oracle 11g online patching (or called hot patching),you can apply or roll back a database patch while the instance is running. Also it can detect conflicts between two online patches.On the other hand, online patching consumes more memory than the conventional method.
In UNIX systems, you use the script $ORACLE_HOME/OPatch/opatch to invoke the online patching.
Automatic Diagnostic Repository (ADR)
The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.
In Oracle 11g, there is a new framework (named as fault diagnosability infrastructure) consisting of many tools for diagnosing and repairing the errors in the database. All those tools refer to the ADR in their operation.
ADR is developed to provide the following advantages:
1.Diagnosis data, because it is stored in file system, is available even when the database is down.
2.It is easier to provide Oracle support with diagnosis data when a problem occurs in the database.
3.ADR has diagnosis data not only for the database instance. It has troubleshooting data for other Oracle components such as ASM and CRS.
Note:
For each database instance two alert log files are generated: one as text file and one with xml
format. Contents of the xml-formatted file can be examined using adrci tool.
Also the xml-formatted alert log is saved in the ADR and specifically in the directory
$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/alert
Data Recovery Advisor
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options,and executes repairs at the user's request. Data Recovery Advisor can diagnose failures such as the following:
1. Inaccessible components like datafiles and control files.
2. Physical corruptions such as block checksum failures and invalid block header
3. Field values
4. Inconsistent datafiles (online and offline)
5. I/O failures
The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager.
Automatic Memory Management
In Oracle 11g, a new parameter named as MEMORY_TARGET is added to automate memory allocation for both the SGA and PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload.
This parameter is dynamic and can be alter using the ALTER SYSTEM command as shown below:
ALTER SYSTEM SET MEMORY_TARGET = 1024M ;
However, if the database is not configured to use this parameter and you want to use it, you must restart the
database after setting the parameter.
Invisible Indexes
Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries.
When using invisible indexes, consider the following:
- If you rebuild an invisible index, the resulting operation will make the index visible.
- If you want the optimizer to consider the invisible indexes in its operation, you can set the new initialization
parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE (the default is FALSE). You can set the parameter in the system and session levels.
Read-Only Tables
In Oracle 11g, you can set a table to be read only,i.e. users can only query from the table but no DML
statement is allowed on the table.
Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles.
Server Result Cache
In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results. The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course,the target is to obtain high response time. The cached results stored become invalid when data in the dependent database objects is modified.
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.
SQL Tuning Automation
The SQL Tuning Advisor is run by default every night during the automated maintenance window. Basically, the advisor catches the SQL statements from AWR that are candidate for tuning (they are called buckets) during four different time periods.It then automatically creates SQL profile for any poor SQL statement, if that helps. Tuned plans are automatically added to the SQL plan baselines by the automatic SQL tuning task.
The advisor also may recommend actions like creating new indexes, refreshing statistics or re-writing the statement. These actions, however, are not automatically implemented by the advisor.
On Oracle 11g Release 2 (11.2.0.2), a new package named as DBMS_AUTO_SQLTUNE should be used instead of the DBMS_SQLTUNE package. The new package provides more restrictive access to the Automatic SQL Tuning feature.
To use the DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS.
SQL Plan Management
SQL plan management (SPM), is a new feature in Oracle 11g that prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.Changes to the execution plan may be resulted from database upgrades,system and data changes, application upgrade or bug fixes.
When SPM is enabled, the system maintains a plan history that contains all plans generated by the optimizer and store them in a component called plan baseline. Among the plan history in the plan baseline, plans that are verified not to cause performance regression are marked as acceptable. The plan baseline is used by the optimizer to decide on the best plan to use when compiling a SQL statement.
Repository stored in data dictionary of plan baselines and statement log maintained by the optimizer is called
SQL management base(SMB).
SQL Plan management is implemented by undertaking the following phases:
1.Capturing SQL Plan Baselines: this can be done automatically or manually.
2.Selecting SQL Plan Baselines by the optimizer
3.Evolving SQL Plan Baselines
Database ADDM
Oracle Database 11g has added a new layer of analysis to ADDM called Database ADDM. The mode ADDM was working in Oracle 10g is now called instance ADDM. The main target of database ADDM is to analyze and report on RAC environment. To enable Database ADDM, you set the parameter INSTANCES in DBMS_ADVISOR.
New SYSASM Privilege for ASM Administration
SYSASM is a new privilege introduced in Oracle 11g. Users who are granted this privilege can perform ASM administration tasks. The idea behind this privilege is to separate database management and the storage management responsibilities.
Backup and Recovery New Features:-
Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ...
blockcommand replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
While the block media recovery is going on, any attempt by users to access data in the corrupt blocks will result in an error message, telling the user that the data block is corrupt.
VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles,backup sets, and even individual data blocks. The comma nd by default checks for physical corruption. You can optionally specify CHECK LOGICAL . Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Configuring an Archived Redo Log Deletion Policy
You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for
deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When the policy is configured, it applies on the automatic deletion of the logs in the flash recovery area and the manual deletion by the BACKUP ... DELETE and DELETE ... ARCHIVELOG commands.
To enable an archived redo log deletion policy, run the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP n TIMES command with the desired options.
Active Database Duplication
In Oracle Database 11g, you can directly duplicate a data base over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance.
Virtual Private Catalogs
In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog. The subset that a user has read/write access to is termed as virtual private catalog, or just virtual catalog. The central or source recovery catalog is now called the base recovery catalog.
ASM Restricted Mode
In Oracle 11g, you can start the ASM instance in restricted mode. When in restricted mode, databases will not be permitted to access the ASM instance. Also, individual diskgroup can be set in restricted mode.
Checking Diskgroup
Starting from Oracle Database 11g, you can validate the internal consistency of ASM diskgroup metadata using the ALTER DISKGROUP ... CHECK command. Summary of errors is logged in the ASM alert log file.
The FORCE option with Drop Diskgroup Command
If a disk is destroyed beyond repair, you want to drop it. But because the disk is practically damaged, you cannot mount it and thus you cannot issue the DROP DISKGROUP command against it. In such a condition, Oracle 11g provides the FORCE INCLUDING CONTENTS option to drop the diskgroup even if it is not mounted.
SQL>DROP DISKGROUP <DISKGROUP_NAME> FORCE INCLUDING CONTENTS;
ASM Rolling Upgrades
ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting
database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions.
Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database.
With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.
Active Dataguard Features:
1. Physical Standby with Real-time Query
2. Fast Incremental Backup on Physical Standby.
3. Automatic Block Repair.
http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf
http://pocketoracle.blogspot.com/search/label/10g%20New%20Features
http://pocketoracle.blogspot.com/search/label/11g%20New%20Features
1. Database Replay
2. The SQL Performance Analyzer
3. Online Patching in Oracle Database Control
4. Automatic Diagnostic Repository (ADR)
5. Data Recovery Advisor
6. Automatic Memory Management
7. Invisible Indexes
8. Read-Only Tables
9. Shrinking Temporary Tablespaces and Tempfiles
10. Server Result Cache
11. SQL Tuning Automation
12. SQL Plan Management
13. Database ADDM
14. New SYSASM Privilege for ASM Administration
15. Enhanced Block Media Recovery
16. VALIDATE Command
17. Configuring an Archived Redo Log Deletion Policy
18. Active Database Duplication
19. Virtual Private Catalogs
20. ASM Restricted Mode
21. Checking Diskgroup
22. The FORCE option with Drop Diskgroup Command
23. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
ASM Rolling Upgrades
Database Replay
Database Replay (sometimes named as Workload Replay) feature in Oracle11g allows you to reproduce the
production database conditions in a testing environment.In other words, with this feature you can capture the
actual workload on a production system and replay it in a test system. This way, you can analyze the condition of the production database without working on the actual production database.
This feature enables you to test the impact of applying changes on a production database. These changes could be database upgrades, switching to RAC, application upgrades, operating system upgrades or storage system changes.
The SQL Performance Analyzer
The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL statements, it provides you recommendations on how to improve their performance.
This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it to study the effect of upgrading an Oracle database 10g release 2 to 11g.
Note:
If you plan to use SPA on a test database, it is highly recommended to make the test database resemble the production database as closely as possible. You can use the RMAN duplicate command for this purpose.
Online Patching in Oracle Database Control
Patching through Database Control is enhanced in Oracle 11g. With Oracle 11g online patching (or called hot patching),you can apply or roll back a database patch while the instance is running. Also it can detect conflicts between two online patches.On the other hand, online patching consumes more memory than the conventional method.
In UNIX systems, you use the script $ORACLE_HOME/OPatch/opatch to invoke the online patching.
Automatic Diagnostic Repository (ADR)
The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.
In Oracle 11g, there is a new framework (named as fault diagnosability infrastructure) consisting of many tools for diagnosing and repairing the errors in the database. All those tools refer to the ADR in their operation.
ADR is developed to provide the following advantages:
1.Diagnosis data, because it is stored in file system, is available even when the database is down.
2.It is easier to provide Oracle support with diagnosis data when a problem occurs in the database.
3.ADR has diagnosis data not only for the database instance. It has troubleshooting data for other Oracle components such as ASM and CRS.
Note:
For each database instance two alert log files are generated: one as text file and one with xml
format. Contents of the xml-formatted file can be examined using adrci tool.
Also the xml-formatted alert log is saved in the ADR and specifically in the directory
$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/alert
Data Recovery Advisor
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options,and executes repairs at the user's request. Data Recovery Advisor can diagnose failures such as the following:
1. Inaccessible components like datafiles and control files.
2. Physical corruptions such as block checksum failures and invalid block header
3. Field values
4. Inconsistent datafiles (online and offline)
5. I/O failures
The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager.
Automatic Memory Management
In Oracle 11g, a new parameter named as MEMORY_TARGET is added to automate memory allocation for both the SGA and PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload.
This parameter is dynamic and can be alter using the ALTER SYSTEM command as shown below:
ALTER SYSTEM SET MEMORY_TARGET = 1024M ;
However, if the database is not configured to use this parameter and you want to use it, you must restart the
database after setting the parameter.
Invisible Indexes
Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries.
When using invisible indexes, consider the following:
- If you rebuild an invisible index, the resulting operation will make the index visible.
- If you want the optimizer to consider the invisible indexes in its operation, you can set the new initialization
parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE (the default is FALSE). You can set the parameter in the system and session levels.
Read-Only Tables
In Oracle 11g, you can set a table to be read only,i.e. users can only query from the table but no DML
statement is allowed on the table.
Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles.
Server Result Cache
In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results. The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course,the target is to obtain high response time. The cached results stored become invalid when data in the dependent database objects is modified.
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.
SQL Tuning Automation
The SQL Tuning Advisor is run by default every night during the automated maintenance window. Basically, the advisor catches the SQL statements from AWR that are candidate for tuning (they are called buckets) during four different time periods.It then automatically creates SQL profile for any poor SQL statement, if that helps. Tuned plans are automatically added to the SQL plan baselines by the automatic SQL tuning task.
The advisor also may recommend actions like creating new indexes, refreshing statistics or re-writing the statement. These actions, however, are not automatically implemented by the advisor.
On Oracle 11g Release 2 (11.2.0.2), a new package named as DBMS_AUTO_SQLTUNE should be used instead of the DBMS_SQLTUNE package. The new package provides more restrictive access to the Automatic SQL Tuning feature.
To use the DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS.
SQL Plan Management
SQL plan management (SPM), is a new feature in Oracle 11g that prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.Changes to the execution plan may be resulted from database upgrades,system and data changes, application upgrade or bug fixes.
When SPM is enabled, the system maintains a plan history that contains all plans generated by the optimizer and store them in a component called plan baseline. Among the plan history in the plan baseline, plans that are verified not to cause performance regression are marked as acceptable. The plan baseline is used by the optimizer to decide on the best plan to use when compiling a SQL statement.
Repository stored in data dictionary of plan baselines and statement log maintained by the optimizer is called
SQL management base(SMB).
SQL Plan management is implemented by undertaking the following phases:
1.Capturing SQL Plan Baselines: this can be done automatically or manually.
2.Selecting SQL Plan Baselines by the optimizer
3.Evolving SQL Plan Baselines
Database ADDM
Oracle Database 11g has added a new layer of analysis to ADDM called Database ADDM. The mode ADDM was working in Oracle 10g is now called instance ADDM. The main target of database ADDM is to analyze and report on RAC environment. To enable Database ADDM, you set the parameter INSTANCES in DBMS_ADVISOR.
New SYSASM Privilege for ASM Administration
SYSASM is a new privilege introduced in Oracle 11g. Users who are granted this privilege can perform ASM administration tasks. The idea behind this privilege is to separate database management and the storage management responsibilities.
Backup and Recovery New Features:-
Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ...
blockcommand replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
While the block media recovery is going on, any attempt by users to access data in the corrupt blocks will result in an error message, telling the user that the data block is corrupt.
VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles,backup sets, and even individual data blocks. The comma nd by default checks for physical corruption. You can optionally specify CHECK LOGICAL . Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Configuring an Archived Redo Log Deletion Policy
You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for
deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When the policy is configured, it applies on the automatic deletion of the logs in the flash recovery area and the manual deletion by the BACKUP ... DELETE and DELETE ... ARCHIVELOG commands.
To enable an archived redo log deletion policy, run the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP n TIMES command with the desired options.
Active Database Duplication
In Oracle Database 11g, you can directly duplicate a data base over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance.
Virtual Private Catalogs
In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog. The subset that a user has read/write access to is termed as virtual private catalog, or just virtual catalog. The central or source recovery catalog is now called the base recovery catalog.
ASM Restricted Mode
In Oracle 11g, you can start the ASM instance in restricted mode. When in restricted mode, databases will not be permitted to access the ASM instance. Also, individual diskgroup can be set in restricted mode.
Checking Diskgroup
Starting from Oracle Database 11g, you can validate the internal consistency of ASM diskgroup metadata using the ALTER DISKGROUP ... CHECK command. Summary of errors is logged in the ASM alert log file.
The FORCE option with Drop Diskgroup Command
If a disk is destroyed beyond repair, you want to drop it. But because the disk is practically damaged, you cannot mount it and thus you cannot issue the DROP DISKGROUP command against it. In such a condition, Oracle 11g provides the FORCE INCLUDING CONTENTS option to drop the diskgroup even if it is not mounted.
SQL>DROP DISKGROUP <DISKGROUP_NAME> FORCE INCLUDING CONTENTS;
ASM Rolling Upgrades
ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting
database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions.
Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database.
With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.
Active Dataguard Features:
1. Physical Standby with Real-time Query
2. Fast Incremental Backup on Physical Standby.
3. Automatic Block Repair.
http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf
http://pocketoracle.blogspot.com/search/label/10g%20New%20Features
http://pocketoracle.blogspot.com/search/label/11g%20New%20Features
No comments:
Post a Comment