How to manually install Oracle Data Mining in 11g

There are some substantial changes with the Data Mining option in 11g than the previous versions. In 11g, no DMSYS schema exists. Also it has a tight integration with Oracle Database. Data Mining metadata and PL/SQL packages have been migrated from DMSYS to SYS schema. Moreover there is no longer an 'odm' directory in the ORACLE_HOME.

If you look into further details, you will notice that there is no entry for Data Mining in the DBA_REGISTRY but there is a value in V$OPTION:

SQL> col value format a30
SQL> col parameter format a30
SQL> select * from v$option where PARAMETER = 'Data Mining';

PARAMETER                      VALUE
------------------------------ ------------------------------
Data Mining                    TRUE

In general, the option is installed with the use of the Database Configuration Assistant (dbca).

However if there is a need to install Data Mining manually, then you need to perform following steps.

1) Go to the $ORACLE_HOME/admin directory of the RDBMS instance where you want to install Data Mining.

2) Ensure that your ORACLE_HOME and ORACLE_SID environmental variable setup is correct.
On unix/linux issue,
$ echo $ORACLE_HOME
$ echo $ORACLE_SID

3) Login as SYS and run catodm.sql, dbmsodm.sql, prvtodm.plb scripts from SQL*Plus:

SQL> conn / as sysdba
SQL> @catodm.sql
SQL> @dbmsodm.sql
SQL> @prvtodm.plb

New features in Oracle 11g Data Guard

Oracle 11g already have two releases. Oracle 11g Release 1 (11.1g) and Oracle 11g Release 2(11.2g). In this post I will specify new features available to Oracle data guard 11g separately.

New Features in Oracle Data Guard 11.1 (Applicable to both Redo Apply and SQL Apply)
- The COMPRESSION attribute is used to specify whether redo data is compressed before transmission to a redo transport destination.

- With NET_TIMEOUT attribute it is specified the number of seconds that the LGWR background process will block waiting for a redo transport destination to acknowledge redo data sent to it. If an acknowledgement is not received within NET_TIMEOUT seconds, an error is logged and the redo transport session to that destination is terminated.

- Role transitions happened faster than previous versions.

- Strong authentication for redo transport network sessions.

- Simplified Data Guard management interface by deprecation of redundant SQL clauses and initialization parameters.

- A physical standby database can now take advantage of the rolling upgrade feature provided by a logical standby. Through the use of the new KEEP IDENTITY clause option to the SQL ALTER DATABASE RECOVER TO LOGICAL STANDBY statement, a physical standby database can be temporarily converted into a logical standby database for the rolling upgrade, and then reverted back to the original configuration of a primary database and a physical standby database when the upgrade is done.

- In the same Data Guard configuration now it allows a mix of Linux and Windows primary and standby databases.

New Features in Oracle Data Guard 11.1 (Applicable to only Redo Apply)
- You can open ans query physical standby database while continuing to receive and apply redo data from a primary database.

- There comes snapshot standby database which is new type of updatable standby database.

- You can use the RMAN DUPLICATE command to create a physical standby database over the network without a need for pre-existing database backups.

- Lost-write detection feature using a physical standby.

New Features in Oracle Data Guard 11.1 (Applicable to SQL Apply only)
- DBMS_RLS, DBMS_FGA PL/SQL Packages are supported and also XML stored as CLOB.

- Transparent Data Encryption (TDE) is supported in SQL apply. This allows a logical standby database to provide data protection for applications with advanced security requirements.

- Data Guard SQL Apply parameters can be set dynamically.

- When switching over to a logical standby database where either the primary database or the standby database is using Oracle RAC, the SWITCHOVER command can be used without having to shut down any instance, either at the primary or at the logical standby database.

- Scheduler Jobs can be created on a standby database using the PL/SQL DBMS_SCHEDULER package and can be associated with an appropriate database role so that they run when intended (for example, when the database is the primary, standby, or both).


New Features in Oracle Data Guard 11.2 (Applicable to both Redo Apply and SQL Apply)

- Data Guard configuration now support up to 30 standby databases.

- In Oracle 10g there was introduced flash recovery area. Starting from Oracle 11g the place is known as fast recovery area. In 11gR2 the fast recovery area location changed from LOG_ARCHIVE_DEST_10 to LOG_ARCHIVE_DEST_1.

- The initialization parameter FAL_CLIENT is deprecated.

- Redo transport compression is no longer limited to compressing redo data only when a redo gap is being resolved. When compression is enabled, all redo data sent is compressed.

- The ALTER SYSTEM FLUSH REDO SQL statement can be used at failover time to flush unsent redo from a mounted primary database to a standby database, thereby allowing a zero data loss failover even if the primary database is not running in a zero data loss data protection mode.


New Features in Oracle Data Guard 11.2 (Applicable to both Redo Apply only)

- Apply lag tolerance can be configured by using the new STANDBY_MAX_DATA_DELAY parameter.

- The "ALTER SESSION SYNC WITH PRIMARY" SQL statement can be used to ensure that a physical standby database is synchronized with the primary database as of the time the statement is issued.

- The V$DATAGUARD_STATS view now has apply lag and transport lag columns. Data inside the view columns also contain more accurate information.

- The new V$STANDBY_EVENT_HISTOGRAM view has been introduced where we can see the histogram of apply lag values on the physical standby database.

- A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database and vice versa.

New Features in Oracle Data Guard 11.2 (Applicable to only SQL Apply)
- Logical standby databases and the LogMiner utility support tables with basic table compression and OLTP table compression.

- Logical standby and the LogMiner utility support tables with SecureFile LOB columns. Compression and encryption operations on SecureFile LOB columns are also supported.

- Online redefinition performed at the primary database using the DBMS_REDEFINITION PL/SQL package is transparently replicated on a logical standby database.

- Logical Standby supports the use of editions at the primary database, including the use of edition-based redefinition to upgrade applications with minimal downtime.

- Logical standby databases support Streams Capture. This allows you to offload processing from the primary database in one-way information propagation configurations and make the logical standby the hub that propagates information to multiple databases. Streams Capture can also propagate changes that are local to the logical standby database.

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

New features in Oracle database 11g of data pump utility

New features of oracle perfomance in 10.2g

How to determine the software version of the database and the Data Pump client

List of Oracle Database Version Release.

Timeline of Oracle RDBMS Major Release Version.