Overview of Compatible parameter
Oracle compatible parameter is a string data type parameter and value of this parameter is oracle database version. It can take value as 11.1.0.7, 11.1.0.6, 10.2.0.4, 10.2.0.3, 10.1.0.5, 10.1.0.4, 9.2.0.8, 9.0.1.4, 8.1.7.4 etc. This parameter controls the database behavior for example whether a feature will work for a database. For example if database version is 10.2.0.4 but compatible parameter is set to 9.2 then certain feature like RMAN compression will not work.
After the database upgrade, if the compatible parameter is set to database version then new feature stores any data on disk (including data dictionary changes) that cannot be processed with your previous release. However after upgrade if compatible parameter is not changed then new feature of the upgraded version will not be available.
Default, minimum and maximum value of compatible parameter
Here goes the compatible parameter default, minimum and maximum values based on oracle database version.
In order to check your compatible parameter issue,
SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';
or if you use sql*plus issue,
SQL> show parameter compatible;
Steps to change compatible parameter
1) Perform full backup of your database (optional).
Before changing compatible parameter, you should take a full backup of your database. Because raising the COMPATIBLE initialization parameter may cause your database to become incompatible with earlier releases of the Oracle Database, and a backup ensures that you can return to the earlier release whenever you want.
2) If you are using spfile parameter to start up your database then complete the following things.
a. Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter.
For example, to set the COMPATIBLE initialization parameter to 10.2.0, issue the following statement:
SQL> ALTER SYSTEM SET COMPATIBLE = '10.2.0' SCOPE=SPFILE;
b. Shut down and restart the instance.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
3) If you are using pfile parameter, then complete the following steps.
a. Shut down the instance if it is running:
SQL> SHUTDOWN IMMEDIATE
b. Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter.
For example, to set the COMPATIBLE initialization parameter to 10.2.0, enter the following in the initialization parameter file:
COMPATIBLE = 10.2.0
c. Start the instance using STARTUP.
SQL> STARTUP
Related Documents
http://arjudba.blogspot.com/2008/04/list-of-oracle-database-version-release.html
http://arjudba.blogspot.com/2008/04/oracle-database-editions.html
http://arjudba.blogspot.com/2008/04/oracle-products.html
http://arjudba.blogspot.com/2008/04/timeline-of-oracle-rdbms-major-release.html
http://arjudba.blogspot.com/2008/04/history-of-oracle-corporation.html
http://arjudba.blogspot.com/2008/10/list-of-patchset-number-in-metalink.html
http://arjudba.blogspot.com/2010/01/how-to-know-whether-patches-applied-to.html
Showing posts with label Version. Show all posts
Showing posts with label Version. Show all posts
Friday, June 4, 2010
Wednesday, January 6, 2010
How to know whether patches applied to database
One of my brother asked me how he will know whether he applied a certain patch into database. Because somehow he forgot whether he applied a patch to one database as he had to monitor many databases as a time.
There are several ways to identify whether a patch applied to database. I am describing some of them.
1)By invoking $ opatch lsinventory.
Note that before invoking opatch you have to set or export ORACLE_HOME and then change the directory to opatch.
$ showrev -p
$ owhat bin/oracle
From all the ways described above we can get idea whether patches are applied to database.
Related Documents
http://arjudba.blogspot.com/2008/04/list-of-oracle-database-version-release.html
http://arjudba.blogspot.com/2008/04/oracle-database-editions.html
http://arjudba.blogspot.com/2008/04/oracle-products.html
http://arjudba.blogspot.com/2008/04/timeline-of-oracle-rdbms-major-release.html
http://arjudba.blogspot.com/2008/04/history-of-oracle-corporation.html
http://arjudba.blogspot.com/2008/10/list-of-patchset-number-in-metalink.html
There are several ways to identify whether a patch applied to database. I am describing some of them.
1)By invoking $ opatch lsinventory.
Note that before invoking opatch you have to set or export ORACLE_HOME and then change the directory to opatch.
oracle:/home/oracle APPS> $ opatch lsinventory2)By invoking $ opatch lsinventory -details
Invoking OPatch 10.2.0.4.3
Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /APPS/app/oracle/product/10.2.0/db
Central Inventory : /APPS/app/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.3
OUI version : 10.2.0.4.0
OUI location : /APPS/app/oracle/product/10.2.0/db/oui
Log file location : /APPS/app/oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2010-01-07_00-19-28AM.log
Lsinventory Output file location : /APPS/app/oracle/product/10.2.0/db/cfgtoollogs/opatch/lsinv/lsinventory2010-01- 07_00-19-28AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
oracle:/APPS/app/oracle/product/10.2.0/db/OPatch AHDP> $ opatch lsinventory -details3)Login to oracle as sys user and invoke select * from sys.registry$history;
Invoking OPatch 10.2.0.4.3
Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /APPS/app/oracle/product/10.2.0/db
Central Inventory : /APPS/app/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.3
OUI version : 10.2.0.4.0
OUI location : /APPS/app/oracle/product/10.2.0/db/oui
Log file location : /APPS/app/oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2010-01-07_00-26-17AM.log
Lsinventory Output file location : /APPS/app/oracle/product/10.2.0/db/cfgtoollogs/opatch/lsinv/lsinventory2010-01-07_00-26-17AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
There are 2 products installed in this Oracle Home.
Installed Products (160):
Agent Required Support Files 10.2.0.1.0
Agent Required Support Files Patch 10.2.0.4.0
Assistant Common Files 10.2.0.1.0
Assistant Common Files Patch 10.2.0.4.0
Bali Share 1.1.18.0.0
Buildtools Common Files 10.2.0.1.0
Character Set Migration Utility 10.2.0.1.0
Character Set Migration Utility Patch 10.2.0.4.0
Database Configuration and Upgrade Assistants 10.2.0.1.0
Database Configuration and Upgrade Assistants Patch 10.2.0.4.0
Database SQL Scripts 10.2.0.1.0
Database SQL Scripts Patch 10.2.0.4.0
Database Workspace Manager 10.2.0.1.0
Database Workspace Manager 10.2.0.4.0
DBJAVA Required Support Files 10.2.0.1.0
DBJAVA Required Support Files Patch 10.2.0.4.0
Enterprise Manager Baseline 10.2.0.1.0
Enterprise Manager Baseline 10.2.0.4.0
Enterprise Manager Minimal Integration 10.2.0.1.0
Enterprise Manager plugin Common Files 10.2.0.1.0
Generic Connectivity Common Files 10.2.0.1.0
Generic Connectivity Common Files Patch 10.2.0.4.0
HAS Common Files 10.2.0.1.0
HAS Common Files Patch 10.2.0.4.0
HAS Files for DB 10.2.0.1.0
HAS Files for DB Patch 10.2.0.4.0
Installation Common Files 10.2.0.1.0
Installation Common Files Patch 10.2.0.4.0
Installer SDK Component 10.2.0.4.0
Java Runtime Environment 1.4.2.14.0
JDBC Common Files 10.2.0.1.0
JDBC Common Files 10.2.0.4.0
LDAP Required Support Files 10.2.0.1.0
LDAP Required Support Files Patch 10.2.0.4.0
OLAP SQL Scripts 10.2.0.1.0
OLAP SQL Scripts Patch 10.2.0.4.0
Oracle Call Interface (OCI) 10.2.0.1.0
Oracle Call Interface (OCI) Patch 10.2.0.4.0
Oracle Clusterware RDBMS Files 10.2.0.1.0
Oracle Clusterware RDBMS Files Patch 10.2.0.4.0
Oracle Code Editor 1.2.1.0.0I
Oracle Configuration Manager 10.2.7.1.0
Oracle Containers for Java 10.2.0.1.0
Oracle Containers for Java 10.2.0.4.0
Oracle Core Required Support Files 10.2.0.1.0
Oracle Core Required Support Files Patch 10.2.0.4.0
Oracle Data Mining RDBMS Files 10.2.0.1.0
Oracle Data Mining RDBMS Files Patch 10.2.0.4.0
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g 10.2.0.1.0
Oracle Database 10G 32 bit 10.1.0.2.0
Oracle Database 10g interMedia Files 10.2.0.1.0
Oracle Database 10g interMedia Files Patch 10.2.0.4.0
Oracle Database 10g Patch 10.2.0.4.0
Oracle Database 10g Patch 10.2.0.4.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
Oracle Database User Interface 2.2.13.0.0
Oracle Database Utilities 10.2.0.1.0
Oracle Database Utilities Patch 10.2.0.4.0
Oracle Display Fonts 9.0.2.0.0
Oracle Extended Windowing Toolkit 3.4.38.0.0
Oracle Globalization Support 10.2.0.1.0
Oracle Globalization Support Patch 10.2.0.4.0
Oracle Help For Java 4.2.6.1.0
Oracle Ice Browser 5.2.3.6.0
Oracle interMedia 10.2.0.1.0
Oracle interMedia Annotator 10.2.0.1.0
Oracle interMedia Client Option 10.2.0.1.0
Oracle interMedia Client Option Patch 10.2.0.4.0
Oracle interMedia Java Advanced Imaging 10.2.0.1.0
Oracle interMedia Java Advanced Imaging Patch 10.2.0.4.0
Oracle interMedia Locator 10.2.0.1.0
Oracle interMedia Locator Patch 10.2.0.4.0
Oracle interMedia Locator RDBMS Files 10.2.0.1.0
Oracle interMedia Locator RDBMS Files Patch 10.2.0.4.0
Oracle interMedia Patch 10.2.0.4.0
Oracle Internet Directory Client 10.2.0.1.0
Oracle Internet Directory Client Patch 10.2.0.4.0
Oracle Java Client 10.2.0.1.0
Oracle Java Client Patch 10.2.0.4.0
Oracle JDBC Thin Driver for JDK 1.2 10.2.0.1.0
Oracle JDBC Thin Driver for JDK 1.2 Patch 10.2.0.4.0
Oracle JDBC Thin Driver for JDK 1.4 10.2.0.1.0
Oracle JDBC Thin Driver for JDK 1.4 Patch 10.2.0.4.0
Oracle JDBC/OCI Instant Client 10.2.0.1.0
Oracle JDBC/OCI Instant Client Patch 10.2.0.4.0
Oracle JFC Extended Windowing Toolkit 4.2.33.0.0
Oracle JVM 10.2.0.1.0
Oracle JVM Patch 10.2.0.4.0
Oracle LDAP administration 10.2.0.1.0
Oracle LDAP administration patch 10.2.0.4.0
Oracle Locale Builder 10.2.0.1.0
Oracle Message Gateway Common Files 10.2.0.1.0
Oracle Message Gateway Common Files Patch 10.2.0.4.0
Oracle Net 10.2.0.1.0
Oracle Net Listener 10.2.0.1.0
Oracle Net Listener Patch 10.2.0.4.0
Oracle Net Patch 10.2.0.4.0
Oracle Net Required Support Files 10.2.0.1.0
Oracle Net Required Support Files Patch 10.2.0.4.0
Oracle Net Services 10.2.0.1.0
Oracle Notification Service 10.1.0.3.0
Oracle Notification Service Patch 10.2.0.4.0
Oracle One-Off Patch Installer 10.2.0.4.0
Oracle Programmer 10.2.0.1.0
Oracle RAC Required Support Files-HAS 10.2.0.1.0
Oracle RAC Required Support Files-HAS Patch 10.2.0.4.0
Oracle Real Application Testing 10.2.0.4.0
Oracle Recovery Manager 10.2.0.1.0
Oracle Recovery Manager Patch 10.2.0.4.0
Oracle Required Support Files 32 bit 10.2.0.0.0
Oracle Required Support Files 32 bit Patch 10.2.0.4.0
Oracle Starter Database 10.2.0.1.0
Oracle Starter Database Patch 10.2.0.4.0
Oracle Text 10.2.0.1.0
Oracle Text Patch 10.2.0.4.0
Oracle UIX 2.1.22.0.0
Oracle Universal Installer 10.2.0.4.0
Oracle Wallet Manager 10.2.0.1.0
Oracle Wallet Manager Patch 10.2.0.4.0
Oracle XML Development Kit 10.2.0.1.0
Oracle XML Development Kit Patch 10.2.0.4.0
Parser Generator Required Support Files 10.2.0.1.0
Perl Interpreter 5.8.3.0.1
PL/SQL 10.2.0.1.0
PL/SQL 10.2.0.4.0
PL/SQL Embedded Gateway 10.2.0.1.0
PL/SQL Embedded Gateway Patch 10.2.0.4.0
Platform Required Support Files 10.2.0.1.0
Platform Required Support Files 10.2.0.4.0
Precompiler Common Files 10.2.0.1.0
Precompiler Common Files Patch 10.2.0.4.0
Precompiler Required Support Files 10.2.0.1.0
Precompiler Required Support Files Patch 10.2.0.4.0
RDBMS Required Support Files 10.2.0.1.0
RDBMS Required Support Files for Instant Client 10.2.0.1.0
RDBMS Required Support Files for Instant Client Patch 10.2.0.4.0
RDBMS Required Support Files Patch 10.2.0.4.0
regexp 2.1.9.0.0
Required Support Files 10.2.0.1.0
Sample Schema Data 10.2.0.1.0
Sample Schema Data Patch 10.2.0.4.0
Secure Socket Layer 10.2.0.1.0
Secure Socket Layer Patch 10.2.0.4.0
SQL*Plus 10.2.0.1.0
SQL*Plus 10.2.0.4.0
SQL*Plus Required Support Files 10.2.0.1.0
SQL*Plus Required Support Files Patch 10.2.0.4.0
SQLJ Runtime 10.2.0.1.0
SQLJ Runtime Patch 10.2.0.4.0
SSL Required Support Files for InstantClient 10.2.0.1.0
SSL Required Support Files for InstantClient Patch 10.2.0.4.0
Sun JDK 1.4.2.13.0
Sun JDK extensions 9.0.4.0.0
XDK Required Support Files 10.2.0.1.0
XDK Required Support Files Patch 10.2.0.4.0
XML Parser for Java 10.2.0.1.0
XML Parser for Java Patch 10.2.0.4.0
XML Parser for Oracle JVM 10.2.0.1.0
XML Parser for Oracle JVM Patch 10.2.0.4.0
There are 160 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
SQL> select * from sys.registry$history;4)Various OS Commands.
ACTION_TIME ACTION NAMESPACE
------------------------------- ------------------------------ ------------------------------
VERSION ID COMMENTS BUNDLE_SERIES
------------------------------ ---------- ------------------------------------ ------------------------------
30-JUL-08 05.43.41.290171 PM CPU
6452863 view recompilation
08-SEP-08 08.22.59.110066 PM CPU SERVER
10.2.0.3.0 6864068 CPUApr2008
05-AUG-09 07.08.18.158451 AM UPGRADE SERVER
10.2.0.4.0 Upgraded from 10.2.0.3.0
05-AUG-09 07.42.03.040347 AM CPU
6452863 view recompilation
$ showrev -p
$ owhat bin/oracle
From all the ways described above we can get idea whether patches are applied to database.
Related Documents
http://arjudba.blogspot.com/2008/04/list-of-oracle-database-version-release.html
http://arjudba.blogspot.com/2008/04/oracle-database-editions.html
http://arjudba.blogspot.com/2008/04/oracle-products.html
http://arjudba.blogspot.com/2008/04/timeline-of-oracle-rdbms-major-release.html
http://arjudba.blogspot.com/2008/04/history-of-oracle-corporation.html
http://arjudba.blogspot.com/2008/10/list-of-patchset-number-in-metalink.html
Wednesday, June 17, 2009
New features in Oracle database administration in 11g
This post will discuss about new features of oracle database 11g administration. Every Oracle database major release comes with new features and step by step some new features are discussed here briefly that comes with Oracle database 11g.
1)Improved Automatic Memory Management:
In 10g in case of automatic memory management we knew that if SGA_TARGET is set then all dynamic SGA components will be automatically managed and if PGA_AGGREGATE_TARGET is set then pga components will be automatically managed. But starting with oracle 11g both pga and sga memory can be managed automatically by setting only MEMORY_TARGET parameter. However you can still assign minimum sizes individually for the SGA and instance PGA.
2)New fault diagnosability infrastructure:
The fault diagnosability infrastructure are for easy diagnosis for any critical problems. After a problem has been occurred it gather necessary information against the problem and then an Incident Packaging Service packages all diagnostic data for a problem into a zip file so that you can transmit that to Oracle Support and thus reduce time to gather information about the problems.
3)Invisible Indexes:
Now in 11g you can make an index as invisible state in stead of dropping it or mark them unusable. It is very effective in order to test performance of a query to see whether removing index will improve the performance. By default an invisible index is ignored by an optimizer but is maintained during DML statements. You can change the initialization parameter to cause the optimizer to use invisible indexes.
4)Virtual Columns:
In tables you can now include virtual columns. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.
5)Enhanced Security:
In 11g now by default your password is case sensitive. So you can use mixed case password. Though by editing initialization parameter you can make password case-insensitive. More about it is discussed in the topic http://arjudba.blogspot.com/2008/04/password-is-case-sensitive-in-oracle.html
6)Database resident connection pooling:
Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling significantly reduces database resources needed to support a large number of client connections and thereby boosting the scalability of both middle-tier and database tiers.
7)Tablespace level Encryption:
You can now encrypt a tablespace that contains sensitive data. Tablespace encryption is completely transparent to your application and when you encrypt a tablespace, all tablespace blocks are encrypted also.
8)Invalidation of dependent schema objects are greatly reduced:
Before 11g if we make any changes in the main object then dependent object become invalid. In 11g invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced and thus increasing application availability during maintenance, upgrades, and online table redefinition.
9)Enhanced automated maintenance task infrastructure:
Though in Oracle 10g you had automatic optimizer statistics gathering, Automatic Segment Advisor, and Automatic SQL Tuning Advisor. In 11g you can now exercise finer control over automated maintenance task scheduling.
10)Transparent data encryption enhanced:
Now in 11g using the transparent data encryption feature you can now encrypt SecureFile LOBs.
11)Table compression now supported in OLTP environments:
On compressed tables you can now run DML operations and adding or dropping columns of a compressed tables.
12)Query result cache in sga:
Now query results can be cached in sga memory in the result cache. The database can then use cached results to answer future executions of that query. It is also true for query fragments. As because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.
13)Default automatic undo management mode:
In 10g if you create database manually then the parameter UNDO_MANAGEMENT became MANUAL as by default UNDO_MANAGEMENT is manual in oracle 10g. However if you created database by dbca it makes the parameter AUTO. In 11g by default UNDO_MANAGEMENT is set to AUTO. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.
14)Enhancements to Oracle Database Resource Manager:
15)Enhancements to Oracle Scheduler:
16)Enhanced online index creation and rebuild:
Before oracle 11g we can do online index creation and rebuild operation but it required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. The DML blocking lock can lock other DMLs and thus performance problem can arise while doing online index creation and rebuild operation. In 11g this lock is no longer required, making these online index operations fully transparent.
17)Tables with materialized view logs can now be redefine online:
Prior to 11g online redefinition of a table that have materialized view logs were not possible. But in 11g tables with materialized view logs can now be redefined online. Materialized view logs are now considered one of the dependent objects. So they can be copied to interim table using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure. To know more about online redefinition have a look at Make a non-partitioned table into partition table using online redefinition .
18)Facility to make a table read only mode:
Now to avoid any update or insert or delete on a table you don't need to make whole tablespace in read only mode. In 11g with ALTER TABLE statement you can place a single table into read only mode.
19)DDL commands can wait for locks:
Prior to 11g if you have any DML operation running against the table then you can't do DDL commands on the table and instantly ORA-54 message would return as demonstrated in ORA-54 Resource Busy. But in 11g you can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails. So we might not need wait for prompting further command to test whether we can run DDL commands. Oracle will check and if no DML operations DDL commands will be performed if it get resource free within DDL_LOCK_TIMEOUT time.
20)Enhancements to initialization parameter management:
There are several enhancement in the initialization parameters.
Upon startup, values of initialization parameters are written to the alert log in
such a way as to make it easy to copy and paste them to create a new PFILE.
The name and path of the PFILE or SPFILE used to start the instance is written
to the alert log so we need not to search which one is used to startup database.
Related Documents
1)Improved Automatic Memory Management:
In 10g in case of automatic memory management we knew that if SGA_TARGET is set then all dynamic SGA components will be automatically managed and if PGA_AGGREGATE_TARGET is set then pga components will be automatically managed. But starting with oracle 11g both pga and sga memory can be managed automatically by setting only MEMORY_TARGET parameter. However you can still assign minimum sizes individually for the SGA and instance PGA.
2)New fault diagnosability infrastructure:
The fault diagnosability infrastructure are for easy diagnosis for any critical problems. After a problem has been occurred it gather necessary information against the problem and then an Incident Packaging Service packages all diagnostic data for a problem into a zip file so that you can transmit that to Oracle Support and thus reduce time to gather information about the problems.
3)Invisible Indexes:
Now in 11g you can make an index as invisible state in stead of dropping it or mark them unusable. It is very effective in order to test performance of a query to see whether removing index will improve the performance. By default an invisible index is ignored by an optimizer but is maintained during DML statements. You can change the initialization parameter to cause the optimizer to use invisible indexes.
4)Virtual Columns:
In tables you can now include virtual columns. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.
5)Enhanced Security:
In 11g now by default your password is case sensitive. So you can use mixed case password. Though by editing initialization parameter you can make password case-insensitive. More about it is discussed in the topic http://arjudba.blogspot.com/2008/04/password-is-case-sensitive-in-oracle.html
6)Database resident connection pooling:
Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling significantly reduces database resources needed to support a large number of client connections and thereby boosting the scalability of both middle-tier and database tiers.
7)Tablespace level Encryption:
You can now encrypt a tablespace that contains sensitive data. Tablespace encryption is completely transparent to your application and when you encrypt a tablespace, all tablespace blocks are encrypted also.
8)Invalidation of dependent schema objects are greatly reduced:
Before 11g if we make any changes in the main object then dependent object become invalid. In 11g invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced and thus increasing application availability during maintenance, upgrades, and online table redefinition.
9)Enhanced automated maintenance task infrastructure:
Though in Oracle 10g you had automatic optimizer statistics gathering, Automatic Segment Advisor, and Automatic SQL Tuning Advisor. In 11g you can now exercise finer control over automated maintenance task scheduling.
10)Transparent data encryption enhanced:
Now in 11g using the transparent data encryption feature you can now encrypt SecureFile LOBs.
11)Table compression now supported in OLTP environments:
On compressed tables you can now run DML operations and adding or dropping columns of a compressed tables.
12)Query result cache in sga:
Now query results can be cached in sga memory in the result cache. The database can then use cached results to answer future executions of that query. It is also true for query fragments. As because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.
13)Default automatic undo management mode:
In 10g if you create database manually then the parameter UNDO_MANAGEMENT became MANUAL as by default UNDO_MANAGEMENT is manual in oracle 10g. However if you created database by dbca it makes the parameter AUTO. In 11g by default UNDO_MANAGEMENT is set to AUTO. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.
14)Enhancements to Oracle Database Resource Manager:
15)Enhancements to Oracle Scheduler:
16)Enhanced online index creation and rebuild:
Before oracle 11g we can do online index creation and rebuild operation but it required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. The DML blocking lock can lock other DMLs and thus performance problem can arise while doing online index creation and rebuild operation. In 11g this lock is no longer required, making these online index operations fully transparent.
17)Tables with materialized view logs can now be redefine online:
Prior to 11g online redefinition of a table that have materialized view logs were not possible. But in 11g tables with materialized view logs can now be redefined online. Materialized view logs are now considered one of the dependent objects. So they can be copied to interim table using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure. To know more about online redefinition have a look at Make a non-partitioned table into partition table using online redefinition .
18)Facility to make a table read only mode:
Now to avoid any update or insert or delete on a table you don't need to make whole tablespace in read only mode. In 11g with ALTER TABLE statement you can place a single table into read only mode.
19)DDL commands can wait for locks:
Prior to 11g if you have any DML operation running against the table then you can't do DDL commands on the table and instantly ORA-54 message would return as demonstrated in ORA-54 Resource Busy. But in 11g you can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails. So we might not need wait for prompting further command to test whether we can run DDL commands. Oracle will check and if no DML operations DDL commands will be performed if it get resource free within DDL_LOCK_TIMEOUT time.
20)Enhancements to initialization parameter management:
There are several enhancement in the initialization parameters.
Upon startup, values of initialization parameters are written to the alert log in
such a way as to make it easy to copy and paste them to create a new PFILE.
The name and path of the PFILE or SPFILE used to start the instance is written
to the alert log so we need not to search which one is used to startup database.
Related Documents
Sunday, August 17, 2008
How to Find out or Check Linux Version Information
Before installing oracle clusterware it is necessary in which version of linux you are trying to install to. Because based on the version procedure may vary to install clusterware.
Here I mention several ways to check the version of linux.
Find out linux release information
1)On Red Hat Linux,
-bash-3.1$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
Alternatively,
# rpm -q redhat-release
redhat-release-4ES-2
2)On SUSE Linux,
$cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
3)On Debian Release,
debian:/home/Arju/Spark# cat /etc/debian_version
4.0
Find out linux Kernel Version
debian:/home/Arju/Spark# uname -s -r
Linux 2.6.18-4-686
debian:/home/Arju/Spark# uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
-bash-3.1$ uname -a
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
-bash-3.1$ uname -s -r
Linux 2.6.18-8.el5
Description of the output of uname -a
At the end but the before of OS name the output of "uname -a " shows the OS word-size.
- i686 i386 indicates 32-bit Linux on standard x86 microprocessor series hardware
- x86_64 indicates 64-bit Linux on standard AMD64 or Intel EM64T hardware
- ia64 indicates 64-bit Linux on the Itanium-2 processor developed jointly by Hewlett-Packard and Intel
- s390x indicates 64-bit Linux on IBM S/390 (31-bit) and zSeries (64-bit) hardware.
- ppc64 indicates 64-bit Linux on IBM power based systems that support Linux includes machines branded as pSeries, iSeries, System p5 and System i5.
The first word of the output shows the kernel name. We see the kernel name is linux.
Only itself can be seen by,
-bash-3.1$ uname -s
Linux
The second word of the output of " uname -a " shows the hostname. Here we see the hostname is debian for debian machine and racnode-1 for redhat linux machine.
Only itself can be seen by,
-bash-3.1$ uname -n
racnode-1
The third word of the output of "uname -a" prints the kernel release. Here for racnode-1 machine we see kernel release is 2.6.18-8.el5. Only itselef can be seen by uname -r
-bash-3.1$ uname -r
2.6.18-8.el5
The fourth word print the kernel version. For racnode-1 we see kernel version is #1 SMP Fri Jan 26 14:15:21 EST 2007. Only itself can be seen by, uname -v.
-bash-3.1$ uname -v
#1 SMP Fri Jan 26 14:15:21 EST 2007
The fifth one prints the machine hardware name. Only itself can be seen from ,
-bash-3.1$ uname -m
i686
The sixth one prints the processor type. Here it is seen by,
-bash-3.1$ uname -p
i686
The seventh one prints the hardware platform. Here it is
-bash-3.1$ uname -i
i386
The eighth one print the operating system. Here it is
-bash-3.1$ uname -o
GNU/Linux
In fact "uname -a" is the combination of all these 8. That is "uname -a" is equivalent to,
-bash-3.1$ uname -snrvmpio
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
Another Way by Cheking issue file
From the /etc/issue file you can also check the version information. In fact it is a text file which contains a message or system identification to be printed before the login prompt.
From here we see for debian machine it is 4.0 and for red hat racnode-1 it is 5.
debian:/home/Arju/Spark# cat /etc/issue
Debian GNU/Linux 4.0 \n \l
-bash-3.1$ cat /etc/issue
Red Hat Enterprise Linux Server release 5 (Tikanga)
Kernel \r on an \m
Related Documents
How to Identify OS or Oracle 64 bit or 32 bit on Unix
Here I mention several ways to check the version of linux.
Find out linux release information
1)On Red Hat Linux,
-bash-3.1$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
Alternatively,
# rpm -q redhat-release
redhat-release-4ES-2
2)On SUSE Linux,
$cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
3)On Debian Release,
debian:/home/Arju/Spark# cat /etc/debian_version
4.0
Find out linux Kernel Version
debian:/home/Arju/Spark# uname -s -r
Linux 2.6.18-4-686
debian:/home/Arju/Spark# uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
-bash-3.1$ uname -a
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
-bash-3.1$ uname -s -r
Linux 2.6.18-8.el5
Description of the output of uname -a
At the end but the before of OS name the output of "uname -a " shows the OS word-size.
- i686 i386 indicates 32-bit Linux on standard x86 microprocessor series hardware
- x86_64 indicates 64-bit Linux on standard AMD64 or Intel EM64T hardware
- ia64 indicates 64-bit Linux on the Itanium-2 processor developed jointly by Hewlett-Packard and Intel
- s390x indicates 64-bit Linux on IBM S/390 (31-bit) and zSeries (64-bit) hardware.
- ppc64 indicates 64-bit Linux on IBM power based systems that support Linux includes machines branded as pSeries, iSeries, System p5 and System i5.
The first word of the output shows the kernel name. We see the kernel name is linux.
Only itself can be seen by,
-bash-3.1$ uname -s
Linux
The second word of the output of " uname -a " shows the hostname. Here we see the hostname is debian for debian machine and racnode-1 for redhat linux machine.
Only itself can be seen by,
-bash-3.1$ uname -n
racnode-1
The third word of the output of "uname -a" prints the kernel release. Here for racnode-1 machine we see kernel release is 2.6.18-8.el5. Only itselef can be seen by uname -r
-bash-3.1$ uname -r
2.6.18-8.el5
The fourth word print the kernel version. For racnode-1 we see kernel version is #1 SMP Fri Jan 26 14:15:21 EST 2007. Only itself can be seen by, uname -v.
-bash-3.1$ uname -v
#1 SMP Fri Jan 26 14:15:21 EST 2007
The fifth one prints the machine hardware name. Only itself can be seen from ,
-bash-3.1$ uname -m
i686
The sixth one prints the processor type. Here it is seen by,
-bash-3.1$ uname -p
i686
The seventh one prints the hardware platform. Here it is
-bash-3.1$ uname -i
i386
The eighth one print the operating system. Here it is
-bash-3.1$ uname -o
GNU/Linux
In fact "uname -a" is the combination of all these 8. That is "uname -a" is equivalent to,
-bash-3.1$ uname -snrvmpio
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
Another Way by Cheking issue file
From the /etc/issue file you can also check the version information. In fact it is a text file which contains a message or system identification to be printed before the login prompt.
From here we see for debian machine it is 4.0 and for red hat racnode-1 it is 5.
debian:/home/Arju/Spark# cat /etc/issue
Debian GNU/Linux 4.0 \n \l
-bash-3.1$ cat /etc/issue
Red Hat Enterprise Linux Server release 5 (Tikanga)
Kernel \r on an \m
Related Documents
How to Identify OS or Oracle 64 bit or 32 bit on Unix
RunInstaller fails with java.lang.UnsatisfiedLinkError
To install clusterware whenever I run runIstaller it fails with following errors along with unknown source.
-bash-3.1$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2008-08-17_03-53-41PM. Please wait ...-bash-3.1$ Oracle Universal Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.
Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2008-08-17_03-53-41PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..
java.lang.UnsatisfiedLinkError: /tmp/OraInstall2008-08-17_03-53-41PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.(Unknown Source)
at java.awt.Component.(Unknown Source)
at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.(OiifmGraphicInterfaceManager.java:222)
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:193)
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:202)
at oracle.sysman.oii.oiic.OiicInstaller.getInterfaceManager(OiicInstaller.java:436)
at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:926)
at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:866)
Exception in thread "main" java.lang.NoClassDefFoundError
at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.(OiifmGraphicInterfaceManager.java:222)
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:193)
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:202)
at oracle.sysman.oii.oiif.oiifm.OiifmAlert.(OiifmAlert.java:151)
at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:984)
at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:866)
Solution of The problem
Create the dynamic link with the library as following,
$cd /usr/lib
$ln -s libXpm.so.4.11.0 libXp.so.6
-bash-3.1$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2008-08-17_03-53-41PM. Please wait ...-bash-3.1$ Oracle Universal Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.
Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2008-08-17_03-53-41PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..
java.lang.UnsatisfiedLinkError: /tmp/OraInstall2008-08-17_03-53-41PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.
at java.awt.Component.
at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:193)
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:202)
at oracle.sysman.oii.oiic.OiicInstaller.getInterfaceManager(OiicInstaller.java:436)
at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:926)
at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:866)
Exception in thread "main" java.lang.NoClassDefFoundError
at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:193)
at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:202)
at oracle.sysman.oii.oiif.oiifm.OiifmAlert.
at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:984)
at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:866)
Solution of The problem
Create the dynamic link with the library as following,
$cd /usr/lib
$ln -s libXpm.so.4.11.0 libXp.so.6
Tuesday, July 29, 2008
ORA-38760: This database instance failed to turn on flashback database ORA-38701 ORA-27037
Problem Description
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Subscribe to:
Posts (Atom)