How to Enable and Disable Database Options in oracle 11g

Installation of Oracle Database 11g Release 2 Software installs all the licensable database options. Though installed, not all the licensable database options are enabled by default.
During installation, installer gives an option for users to enable the licensable database options that are not enabled in a default installation.

SQL> SET LINESIZE 200
SQL> select * from v$option where value ='FALSE';
PARAMETER                                                         VALUE
---------------------------------------                       --------------------------------------
Real Application Clusters                                        FALSE
Automatic Storage Management                            FALSE
Oracle Label Security                                               FALSE
Real Application Testing                                          FALSE

So we see the disabled options are listed above.

To enable an option lets say "Real Application Testing" we would follow the following process.

Step 1:-Stop the database, Database Control console process, and listener.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@configsrv2 ~]$ emctl stop dbconsole
[oracle@configsrv2 ~]$ lsnrctl stop


Step 2:-
[oracle@configsrv2 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@configsrv2 ~]$ make -f ins_rdbms.mk rat_on ioracle
After hitting the above command it would take some time as this would enable the Real Application Testing option  for the database.

Step 3:-
Once the execution of the above command is done ; we need to start the database, listener and Database Control console process.
[oracle@configsrv2 ~]$ lsnrctl start
SQL> startup
SQL> select * from v$option where parameter='Real Application Testing';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Real Application Testing                                         TRUE



To disable a certain option:-
SQL> select * from v$option where value ='TRUE';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning                                                     TRUE
Objects                                                          TRUE
Advanced replication                                             TRUE
Bit-mapped indexes                                               TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE

Here lets disable Partitioning in our database

Step 1:-
So, Stop the database, Database Control console process, and listener.

[oracle@configsrv2 ~]$ lsnrctl stop
[oracle@configsrv2 ~]$ emctl stop dbconsole
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2:-
[oracle@configsrv2 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@configsrv2 ~]$ make -f ins_rdbms.mk part_off ioracle
After hitting the above command it would take some time as this would enable the Partitioning option for the database.

Step 3:-
Once the execution of the above command is done ; we need to start the database, listener and Database Control console process.
[oracle@configsrv2 ~]$ lsnrctl start
SQL> startup
SQL> select * from v$option where parameter='Partitioning';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning                                                     FALSE

So we can see the option Partitioning has been disabled for the database.


Here is the list of database options and switches:
Database Option                    ON            OFF
Data Mining                        dm_on        dm_off
Data Mining Scoring Engine        dmse_on        dmse_off
Database Vault                    dv_on        dv_off
Label Security                    bac_on        lbac_off
Partitioning                    part_on        part_off
Real Application Clusters        rac_on        rac_off
Spatial    s                        do_on        sdo_off
Real Application Testing        rat_on        rat_off
OLAP                            olap_on        olap_off
Automatic Storage Management    asm_on        asm_off
Context Management Text            ctx_on        ctx_off




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.