While startup standby database it fails with ORA-01154

Problem Description
Whenever you try to open or shutdown a standby database it fails with error ORA-01154
SQL> connect / as sysdba
Connected.

SQL> alter database open
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> shutdown
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

Cause of the Problem
The database is a standby database and it is in managed recovery mode. To verify the database is in managed recovery mode, enter this command

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

If it returns MANAGED then the standby database is in managed recovery mode.

If database is in managed recover mode then it is not allowed to do open or shutdown the database.

Solution of the Problem
To take the database out of managed recovery mode, enter this command

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

You can now open the database as,
SQL> ALTER DATABASE OPEN READ ONLY;

Or, you may now shutdown the database.
SQL> shutdown

Database startup fails with ORA-00064

Problem Description
Case 01: After it is set SGA_TARGET to 100G with a total of about 300G physical memory on the server "startup" fails with
ORA-00064: object is too large to allocate on this O/S (1,15429280) errors

Case 02: The parameter OPEN_LINKS_PER_INSTNACE is set to 1000 and then startup of the instance causes ORA-00064.

Case 03: After it is set high value of PROCESSES parameter, whenever you try to startup the instance it fails with ORA-00064.

Case 04: "db_files" initialization parameter on 64bit versions of Oracle is set to a higher value and now startup does not work.

Cause of the Problem
As it is already discussed in the post startup migrate fails with ORA-00064 while upgrading to 10.2.0.2 with DBUA the problem happened due to lower default value of oracle hidden parameter _ksmg_granule_size.

The calculation is,
- sga_max_size <= 1024M then _ksmg_granule_size = 4M - sga_max_size > 1024M and <128g then _ksmg_granule_size = 16M - sga_max_size > 128G and <256g then _ksmg_granule_size = 32M - sga_max_size > 256G and <512g then _ksmg_granule_size = 64M

Solution of the Problem
Solution 01: The issue is fixed in Oracle 10.2.0.4.3 (Patch Set Update) and 10.2.0.5 (Server Patch Set). So applying patch will solve the problem.

Solution 02: Disable NUMA optimization on the system
To do this set two hidden parameters like below.
_enable_NUMA_optimization=FALSE
and
_db_block_numa=1

Solution 03: Increase granule size on the system (_ksmg_granule_size)
_ksmg_granule_size=32M

Solution 04: If your PROCESSES initialization parameter has higher value then reduce the value. For example make it less than 1500. Also set the DB_FILES parameter to lower value.

startup migrate fails with ORA-00064 while upgrading to 10.2.0.2 with DBUA

Problem Description
It was tried to upgrade an oracle database from version 9.2.0.7 to 10.2.0.2 using oracle database upgrade assistant. After invoking DBUA ORA-00064: returns. If you also issue startup upgrade from sql*plus it also fails like below.

SQL> startup upgrade
ORA-00064: object is too large to allocate on this O/S (1,7614720)

Cause of the Problem
1. The oracle PROCESSES initialization parameter is set to high value. For example, if you set 'Processes' parameter to a high value (for example > 14000), the instance startup fails with ORA-00064.

2. Low value of granule size.

The oracle hidden parameter "_ksmg_granule_size" is set based on the oracle sga size.
The calculation is,
- sga_max_size <= 1024M then _ksmg_granule_size = 4M
- sga_max_size > 1024M and <128g then _ksmg_granule_size = 16M
- sga_max_size > 128G and <256g then _ksmg_granule_size = 32M
- sga_max_size > 256G and <512g then _ksmg_granule_size = 64M

Now if you set high value of a parameter(for example DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE) that exceeds the value of the granule size that was calculated based on the size of the sga.

If your SGA size is over 1G then you will manually have to set the parameter _ksmg_granule_size. Note that the default setting of _ksmg_granule_size is 4M and low value of _ksmg_granule_size can prohibit the database startup if your memory value is over 1G.

3. "db_files" initialization parameter on 64bit versions of Oracle is set to a higher value.

4. The ORA-00064 error could be occurred even though the big "_ksmg_granule_size" was configured in init.ora file as because during DBUA in 10.2.x it strips out the oracle hidden parameter while opening oracle database.

Solution of the Problem
Solution 01:
Reduce the value of the oracle "PROCESSES" initialization parameter.

For example, open oracle parameter file with editor and put following line
PROCESSES = 1500

Solution 02:
Increase Oracle hidden parameter value of "_ksmg_granule_size" directly to 16M (16777216) or 32M (33554432)

- open oracle parameter file with editor and put following line
_ksmg_granule_size=16777216
or
_ksmg_granule_size=33554432

- Start up gradation manually.

Solution 03:
- Increase SGA size bigger than 1024M to affect granule size. Open oracle parameter file with and editor and put following line
sga_max_size = 1028M

Solution 04:
- Run DBUA with -initParam flag.

$ dbua -initParam "_ksmg_granule_size"=16777216
or
$ dbua -initParam "_ksmg_granule_size"=33554432

Non-Oracle user hits SP2-0642 or SP2-1503/SP2-152 after DST patch

Problem Description
The oracle user (owner of the oracle installation) is able to run sql*plus.

However users different than the owner of the installation (Oracle user is usually the owner) are not able to run SQL*Plus after the DST patches were applied at operating system and database software level. Non oracle users who do not belong under dba unix group it fails with

"SP2-0642: SQL*Plus internal error state 2165, context 4294967295:0:0
Unable to proceed"


Or, after applying DST patch (i.e. Patch 5632264) to Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 it fails with following errors.

$ sqlplus /nolog 
SP2-1503: Unable to initialize Oracle call interface 
SP2-0152: ORACLE may not be functioning properly

Cause of the Problem
SQL*Plus is unable to access $ORACLE_HOME/oracore/zoneinfo/timezone.dat file. The error message is indicating timezone.dat file is missing from install or inadequate permissions to access $ORACLE_HOME/oracore/zoneinfo directory.

If you do ls -l you will see output like,

$ls -l oracore/zoneinfo/timezone.dat
-rw-r----- 1 oracle dba 161096 Mar 10 20:12 oracore/zoneinfo/timezone.dat

From the permission, it is clear that the users others than oracle and not under dba group do not have privileges on the timezone.dat file, so they are not able to read/write the file above. It needs at least read privileges on other unix group.

Solution of the Problem
To implement the solution, execute the following steps:

1. Manually change the privileges for the $ORACLE_HOME/oracore/zoneinfo directory and files.

$ chmod o+r oracore
$ chmod o+r oracore/zoneinfo/timezone.dat

Alternatively, you can do.
$ cd $ORACLE_HOME
$ chmod -R 755 oracore


2. After you change the permission setting SQL*Plus should be working.
$ sqlplus

SP2-1503 SP2-0152 returned after invoking sqlplus as a non-admin user

Problem Symptoms
When logged on to the Windows server as a non-Administrator OS account using Microsoft Terminal Services client (mstsc.exe)/ Remote desktop option, starting SQL*Plus fails with

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

But, starting SQL*Plus works when logged on to the Windows server using an Administrator OS account.

Also it works when logged on locally to the Windows server console using the non-Administrator OS account.

Cause of the Problem
The issue is related to a Windows Security configuration. The problem is caused by a security policy called "Create Global Objects". The user account that is used to run the program does not have the "Create global objects" user right. This security policy was introduced with Windows 2000 SP4, and determines if applications started during a Terminal Services session can create or access globally accessible memory.

By default, members of the Administrators group, the System account, and Services that are started by the Service Control Manager are assigned the "Create global objects" user right. That's why administrators group members do not receive any error while non-administrator members get errors.

Solution of the Problem
Assign the "Create global objects" user right to the non-Administrator account.

1. Click Start, point to Programs, point to Administrative Tools, and then click Local Security Policy.

2. Expand Local Policies, and then click User Rights Assignment.

3. In the right pane, double-click Create global objects.

4. In the Local Security Policy Setting dialog box, click Add.

5. In the Select Users or Group dialog box, click the user account that you want to add, click Add, and then click OK.

6. Click OK.

After 11.2g new installation invoking sqlplus fails with SP2-1503 SP2-0152

Problem Description
After new Oracle 11.2g installation(64 bit) invoking sqlplus returns SP2-1503 SP2-0152 errors like below.

$ sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

If you browse to $ORACLE_HOME/oracore/zoneinfo directory and check privilege by using command ls -l you will see a similar output like below,

$cd $ORACLE_HOME/oracore/zoneinfo
$ls -l 
total 10092
drwxr-xr-x 2 oracle dba 4096 May 12 17:02 big
drwxr-xr-x 2 oracle dba 4096 May 12 17:02 little
-rw-r--r-- 1 oracle dba 5725 Jun 12 12:02 readme.txt
-rw-r--r-- 1 oracle dba 25681 Jun 16 12:02 timezdif.csv
-rw-r--r-- 1 oracle dba 792894 Jul 10 10:11 timezlrg_10.dat
-rw-r--r-- 1 oracle dba 787272 Jul 10 10:11 timezlrg_11.dat
-rw-r--r-- 1 oracle dba 493675 Jul 10 10:11 timezlrg_1.dat
-rw-r--r-- 1 oracle dba 507957 Jul 10 10:11 timezlrg_2.dat
-rw-r--r-- 1 oracle dba 527717 Jul 10 10:11 timezlrg_3.dat
-rw-r--r-- 1 oracle dba 531137 Jul 10 10:11 timezlrg_4.dat
-rw-r--r-- 1 oracle dba 587487 Jul 10 10:11 timezlrg_5.dat
-rw-r--r-- 1 oracle dba 586750 Jul 10 10:11 timezlrg_6.dat
-rw-r--r-- 1 oracle dba 601242 Jul 10 10:11 timezlrg_7.dat
-rw-r--r-- 1 oracle dba 616723 Jul 10 10:11 timezlrg_8.dat
-rw-r--r-- 1 oracle dba 801410 Jul 10 10:11 timezlrg_9.dat
-rw-r--r-- 1 oracle dba 345637 Jul 10 10:11 timezone_10.dat
-rw-r--r-- 1 oracle dba 345356 Jul 10 10:11 timezone_11.dat
-rw-r--r-- 1 oracle dba 274427 Jul 10 10:11 timezone_1.dat
-rw-r--r-- 1 oracle dba 274900 Jul 10 10:11 timezone_2.dat
-rw-r--r-- 1 oracle dba 286651 Jul 10 10:11 timezone_3.dat
-rw-r--r-- 1 oracle dba 286264 Jul 10 10:11 timezone_4.dat
-rw-r--r-- 1 oracle dba 286310 Jul 10 10:11 timezone_5.dat
-rw-r--r-- 1 oracle dba 286217 Jul 10 10:11 timezone_6.dat
-rw-r--r-- 1 oracle dba 286815 Jul 10 10:11 timezone_7.dat
-rw-r--r-- 1 oracle dba 302100 Jul 10 10:11 timezone_8.dat
-rw-r--r-- 1 oracle dba 351525 Jul 10 10:11 timezone_9.dat

Cause of the Problem
If you look for the ls -l output you will see timezone.dat and timezlrg.dat are not present but multiple versions timezlrg.* and timezone.* are present. The problem happened because there is a choice of timezone versions are available.

Solution of the Problem
To resolve the problem, create links to one of the timezone_nn.dat files. While creating symbolic links you should choose the latest timezone version unless you have a requirement for a specific version.

$ cd $ORACLE_HOME/oracore/zoneinfo
$ ln -s timezone_11.dat timezone.dat
$ ln -s timezlrg_11.dat timezlrg.dat

How to login to RHEL4 after you have forgotten root password

This post will guide to you what to do after you have forgotten you linux root password and help you how to login as root under in Red Hat Enterprise Linux 4.

The idea is you can log in using single-user mode and create a new root password.

Step 01: Using your machine restart button manually reboot your computer.

Step 02: If you use the default boot loader, GRUB, you can enter single user mode. To do so, at the boot loader menu, use the arrow keys to highlight the installation you want to edit and type [A] to enter into append mode.

Step 03: You are presented with a prompt that looks similar to the following:

grub append> ro root=LABEL=/

Step 04: Press the Spacebar once to add a blank space, then add the word single to tell GRUB to boot into single-user Linux mode. The result should look like the following:

ro root=LABEL=/ single

Step 05: Press [Enter] and GRUB will boot single-user Linux mode. After it finishes loading, you will be presented with a shell prompt similar to the following:

sh-2.05b#

Step 06: You can now change the root password by typing

passwd root
You will be asked to re-type the password for verification. Once you are finished, the password will be changed. You can then reboot by typing reboot at the prompt; then you can log in to root as you normally would.

Difference between connecting to database as normal and sysdba/sysoper

As it is discussed in Database Administrator Authentication, it is said whenever you connect to database as sysdba privilege, you are connecting to SYS default schema. And whenever you are connecting to database as sysoper privilege, you are connecting to PUBLIC default schema.

Note that, sysdba and sysoper are special privilege and therefore certain types of operations can be performed whenever you assign sysdba/sysoper privilege to a user. A complete lists of operations that can be performed by the user who has sysdba/sysoper privilege are listed in the post SYSDBA and SYSOPER authorized operations. A very important thing to remember that, whenever you only assign these two privileges to a user and you don't assign any more privilege then user will not be able to do any schema/table level modification unless you specifically login as sysdba privilege.

With examples I will try to make you more clear between the differences.

sysdba privilege is not enough for a user to login to database unless he login as sysdba privilege
1. Login as sysdba.
E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 17 15:12:05 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2. Create a user named test_sysdba with password test_sysdba.
SQL> create user test_sysdba identified by test_sysdba;

User created.

3. Change default tablespace to users of test_sysdba user.
SQL> alter user test_sysdba default tablespace users;

User altered.

4. Grant sysdba privilege to user test_sysdba.
SQL> grant sysdba to test_sysdba;

Grant succeeded.

As soon as we assign sysbda privilege under password file there will be an entry. By querying v$pwfile_users view we can see an entry.

SQL> select * from v$pwfile_users ;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
TEST_SYSDBA                    TRUE  FALSE FALSE

5. Try to connect to database as test_sysdba
SQL> conn test_sysdba/test_sysdba
ERROR:
ORA-01045: user TEST_SYSDBA lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

As we have not specify "as sysdba" while login so it does not permit test_sysdba to login to database even he has sysdba privilege.

If you don't login as SYSDBA privilege it will act as a normal user
1. Log in as sysdba
SQL> conn / as sysdba
Connected.

2. Grant create session privilege to test_sysdba.
SQL> grant create session to test_sysdba;

Grant succeeded.

3. Now try to login as test_sysdba privilege and try to create table.
SQL> conn test_sysdba/test_sysdba
Connected.

SQL> create table test(col1 number);
create table test(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

The "create table" statement fails as while login we did not specify "sysdba" privilege, and so user has connected to database as normal user.

4. Now connect as sysdba privilege and grant dba to test_sysdba user.
SQL> conn / as sysdba
Connected.

SQL> grant dba to test_sysdba;

Grant succeeded.

5. Connection will be successful as it has dba role but still it is normal test_sysdba user.
SQL> conn test_sysdba/test_sysdba
Connected.

SQL> create table test_sysdba_table1(col1 number);

Table created.

SQL> show user
USER is "TEST_SYSDBA"

Note that the user is TEST_SYSDBA.

Whenever we specify "sysdba privilege" while connecting the schema became SYS
1. Connect to database with test_sysdba user and using sysdba privilege.
SQL> conn test_sysdba/test_sysdba as sysdba
Connected.

SQL> show user
USER is "SYS"

Note that, now user became SYS as we specified "as sysdba" while login.

2. As it is SYS user and any table we create will go under SYS default "SYSTEM" tablespace whereas whenever we connect as normal test_sysdba user it would go under that user default schema.

SQL> create table test_sysdba_table2(col1 number);

Table created.

SQL> set lines 200
SQL> select owner, table_name, tablespace_name from dba_tables where table_name like 'TEST_SYSDBA_TABLE%';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST_SYSDBA                    TEST_SYSDBA_TABLE1             USERS
SYS                            TEST_SYSDBA_TABLE2             SYSTEM

Whenever we connect through sysoper privilege the schema is PUBLIC
SQL> grant sysoper to test_sysdba;

Grant succeeded.

SQL> conn test_sysdba/test_sysdba as sysoper;
Connected.

SQL> show user
USER is "PUBLIC"

SQL> create table test_sysdba_table3(col1 number);
create table test_sysdba_table3(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

So user connecting as sysoper privilege will not be able to create table as PUBLIC user is not permitted so.

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

How to manually install Data Mining in Oracle 10g

Manually Install Oracle Data Mining for RDBMS 10.1.x
Step 01: Ensure that your ORACLE_HOME and ORACLE_SID environmental variable setup is correct.
On unix/linux issue,
$ echo $ORACLE_HOME
$ echo $ORACLE_SID


Step 02: Start Sql*plus and connect with sys user as sysdba privilege.
$ sqlplus /nolog
SQL> conn / as sysdba

Step 03: Execute dminst.sql, odmpatch.sql and utlrp.sql scripts like below.

On Unix - Linux,

SQL> run $ORACLE_HOME/dm/admin/dminst.sql SYSAUX TEMP
SQL> run $ORACLE_HOME/dm/admin/odmpatch.sql
SQL> run $ORACLE_HOME/dm/admin/utlrp.sql

On Windows,

SQL> start %ORACLE_HOME%\dm\admin\dminst.sql SYSAUX TEMP 
SQL> start %ORACLE_HOME%\dm\admin\odmpatch.sql 
SQL> start %ORACLE_HOME%\dm\admin\utlrp.sql

Step 04: Ensure that 'Oracle Data Mining' is at Valid status in dba_registry by executing the following query,

SQL> select COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Oracle Data Mining';

Manually Install Oracle Data Mining for RDBMS 10.2.x
Step 01: Ensure that your ORACLE_HOME and ORACLE_SID environmental variable setup is correct.
On unix/linux issue,
$ echo $ORACLE_HOME
$ echo $ORACLE_SID


Step 02: Start Sql*plus and connect with sys user as sysdba privilege.
$ sqlplus /nolog
SQL> conn / as sysdba

Step 03: Execute dminst.sql, odmpatch.sql and utlrp.sql scripts like below.

On Unix/Linux issue,

SQL> run $ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP
SQL> run $ORACLE_HOME/rdbms/admin/odmpatch.sql
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql

On Windows,

SQL> start %ORACLE_HOME%\rdbms\admin\dminst.sql SYSAUX TEMP 
SQL> start %ORACLE_HOME%\rdbms\admin\odmpatch.sql 
SQL> start %ORACLE_HOME%\rdbms\admin\utlrp.sql

Step 04: Ensure that 'Oracle Data Mining' is at Valid status in dba_registry by executing the following query,

SQL> select COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Oracle Data Mining';

How to remove data mining option from database

Oracle data mining is a licensed database option that is generally installed by default. However if you want to uninstall it the following procedures will help you.

Uninstall for database versions 9iR2 (9.2.x) and 10gR1 (10.1.x)
From the Oracle Universal Installer (OUI) page, you should be able to deinstall Data Mining from the 'Deinstall' icon. This is a supported way.

If OUI does not work for some reasons, you may do the following to deinstall ODM.

1. Start SQLPLUS and connect with user sys as sysdba
$ sqlplus / as sysdba

2. Remove ODM repositories from the database.

2.1 For Database release 9iR2 (9.2.x).

- Drop ODM, ODM_MTR schemas.

SQL> DROP USER ODM_MTR CASCADE;
SQL> DROP USER ODM CASCADE;

- Drop ODM public synonyms.

SQL> set heading off
SQL> spool /home/oracle/drop_odm_synonyms.sql
SQL>  select 'Drop public synonym ' || SYNONYM_NAME || ' ;' from DBA_SYNONYMS where TABLE_OWNER = 'ODM';
SQL> spool off
SQL> @/home/oracle/drop_odm_synonyms.sql

Note that 264 synonyms should be selected and dropped.

2.2 For Database release 10gR1 (10.1.x)

- Drop DMSYS schema.

SQL> DROP USER DMSYS CASCADE;

- Drop DMSYS public synonyms.

SQL> set heading off 
SQL> spool /home/oracle/drop_dmsys_synonyms.sql 
SQL> select 'Drop public synonym '" || SYNONYM_NAME || '" ;' from DBA_SYNONYMS where TABLE_OWNER = 'DMSYS'; 
SQL> spool off 
SQL> @/home/oracle/drop_dmsys_synonyms.sql

Note that 568 synonyms should be selected and dropped.

Issue the following DELETE statement.

SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS';

3. Update the DBA registry :

Before updating the DBA registry, first make sure Data Mining is registered in your database by running the following:

SQL> connect / as sysdba; 
SQL> select comp_id, version, status from dba_registry;

If ODM (Data Mining) is returned by the above query than execute the following to remove Data Mining from the DBA registry:

SQL> exec dbms_registry.removed('ODM');

For UNIX Platform the following steps allow to remake Oracle executable without DM libraries.

1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. startup the database

The sqlplus banner should no longer display the Data Mining option and v$option should show 'Oracle Data Mining' as false on a Unix system based install.

Please note that it is possible synonyms may exist for Data Mining objects. If any exist, remove them to complete the removal.

Uninstall for Database version 10gR2 (10.2.x)

With Database Release 10gR2, the only supported way to remove the Data Mining option is to use the Oracle Universal Installer.

The steps would be,

- Start The Oracle Universal Installer.

-In Installer , click on the "Deinstall" Button.

- Find you RDBMS home and expand it.

- Under it you will find 'Oracle Database 10g 10.2.0.x' . Expand it.

- Under it you will find another folder also called Oracle Database 10g 10.2.0.1'. Expand it.

- In the bottom of this tree you will find 'Oracle Data Mining RDBMS Files 10.2.0.1'. This is what you need to remove.

- This Data Mining uninstall process should relink oracle executable with dm_off target, which should be reflected in sys.v$option view and SQLPlus banner.

Note that on some platform (e.g: HP UX) you may notice that after selecting the 'Oracle Data Mining RDBMS Files 10.2.0.1' and pressing OK you will be asked if you're sure you want to remove basically everything, including "Oracle Database 10g" itself. If this is the case stop deinstall process and do the following:

On unix and linux platform,

1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. startup database.

On all platforms,
Update the database registry to remove the Data Mining component from it by performing the following :

Start SQL*Plus and log in as SYSDBA and execute dbms_registry.removed('ODM')

SQL> conn / as sysdba
SQL> exec dbms_registry.removed('ODM');

The sqlplus banner should no longer display the Data Mining option and v$option should show 'Oracle Data Mining' as false on a Unix system based install.

Uninstall for Database version 11gR1 (11.1.x)

With 11g, Oracle Data Mining is installed as part of the database installation. Data Mining is now part of Oracle binary and SYS metadata.

The Data Mining option cannot be removed but it can be disabled:

On Linux/Unix platform as below:

1. Shutdown database 
2. cd $ORACLE_HOME/rdbms/lib 
3. make -f ins_rdbms.mk dm_off 
4. make -f ins_rdbms.mk ioracle 
5. startup database

On Windows platform as below:

Use the remove option from the Universal Installer and select the Data Mining under Enterprise Edition.

To verify the option have been disabled:

- Start sqlplus
$ sqlplus /nolog

- Connect as DBA user
SQL> conn / as sysdba

- Query the sys.v$option using :
SQL> Select parameter, value from v$option where parameter ='Data Mining';

If the returned value for column "value" is FALSE, then the option have been disabled.

Expdp fails with PLS-00201: identifier DMSYS.DBMS_DM_MODEL_EXP must be declared

Problem Description
Oracle data pump export fails with error "PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared ORA-06550:" like below.
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.
GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN
DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT('CLUS1PROD1',0,1,'10.01.00.03.00');
END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 872
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Cause of the Problem
The problem arises because DMSYS schema objects have been accidentally removed or DMSYS schema objects have Invalid status or DMSYS schema has been dropped.

Solution of the Problem
Case 01: DMSYS has been dropped
1. Start SQLPlus.
$ sqlplus /nolog

2. Connect with user SYS as SYSDBA and issue the following commands:

SQL> conn / as sysdba
SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS'; 
SQL> exit; 

3. Run export jobs.

Case 02: DMSYS schema objects have been accidentally removed / have invalid objects
1. Start SQLPlus and connect with user SYS as SYSDBA
$ sqlplus / as sysdba

2.
i) If Database is version 10.1.0.x do the following steps:

 SQL> run $ORACLE_HOME/dm/admin/dminst.sql SYSAUX TEMP $ORACLE_HOME/dm/admin/ 
 SQL> run $ORACLE_HOME/dm/admin/odmpatch.sql (if the database is at a patch level) 
 SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql

ii) If Database is version 10.2.0.x do the following steps:

 SQL> run $ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP $ORACLE_HOME/rdbms/admin/ 
 SQL> run $ORACLE_HOME/rdbms/admin/odmpatch.sql 
 SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql

3. Ensure that 'Oracle Data Mining' is at valid status in dba_registry using,
SQL> select COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Oracle Data Mining';

4. Run the export jobs.

Export DataPump fails with ORA-39125 while Calling DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT

Problem Description
While invoking oracle datapump schema level export it fails with error
"ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT" like below.

#> expdp userid=system/password DIRECTORY=mydir DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott

Export: Release 10.2.0.1.0 - 64bit Production on Sunday, 27 July, 2008 10:00:38

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."JOB1": userid=system/***** DIRECTORY=mydir DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS 
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT('SCOTT',0,1,'10.01.00.02.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 872
ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called

Cause of the Problem
As the line "ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors" appears the problem happened due to invalid state of the package DMSYS.DBMS_DM_UTIL. Package DMSYS.DBMS_DM_UTIL is used by the Oracle Data Mining option. You can verify the invalid objects in oracle by following query,

SQL> set lines 200
SQL> select status, 
            object_id, 
            object_type, 
            owner||'.'||object_name "OWNER.OBJECT"
     from   dba_objects 
     where  status != 'VALID' 
     order  by 4,2;

Solution of the Problem
Step 01: Log in to database as dmsys user and run the script dmutil.plb to re-create the invalid package.

SQL> CONNECT dmsys/dmsys
Connected.

SQL> @$ORACLE_HOME/dm/admin/dmutil.plb
Package created.
Package created.

Step 02: Run the script $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile the invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Step 03: Delete the Export DataPump log file and dump file of the previous failed attempt. Then re-run the export DataPump operation.

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.