Enable/disable oracle components using CHOPT utility in oracle 11gR2

Enable/disable oracle components usinig chopt command like utility:

From 11g relase 2, we can enable/disable oracle components using the command line utility called CHOPT.
This utility is located in $ORACLE_HOME/bin location.

In older versions, In order to enable/disable oracle components we have to use  OUI(till 11.1) or we need to enable/disable component at binary level using relink (make) cmd(11.1.0.7)

But from 11g release 2,
Using chopt utility we can enable/disable following components:

dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing

syntax:  chopt [ enable | disable] db_option

bash-3.00$ chopt
usage:
chopt

options:
dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing

steps to enable/disable oracle components

1) shutdown the database for which do you want to enable/disable the oracle component.
2) enable/disable the component using chopt command.
3) startup the database and verify the component installed using v$option view.

Eg: Enable partitioning for database

select * from v$option where parameter=’ Partitioning’;

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

shut immediate

cd $ORACLE_HOME/bin

bash-3.00$ chopt enable Partitioning

startup

select * from v$option where parameter=’ Partitioning’;

PARAMETER        VALUE
----------------------- -----------
Partitioning      TRUE

Partitioning new features 12c

* Can move table partitions from one TS to anothes TS online:

The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while maintaining associated indexes.

If in one session we have an active transaction (i.e. not yet committed):

while we move a table partition in another session as we did previously:




SQL> alter table muse move partition p3 tablespace users update indexes;

  

alter table muse move partition p3 tablespace users update indexes

  

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired





we now use the new 12c UPDATE INDEXES ONLINE clause:



SQL> alter table muse move partition p3 tablespace users update indexes online;




The session now simply hangs until the transaction in session one completes, in a similar manner to that of an index rebuild online operation.





Upgrade DST timezone version from 4 to 14 after upgrading the db from 10.2.0.4 to 11.2.0.3

After upgrading the db from 10.2.0.4 to 11.2.0.3 find the timezone DST TZ version using the command,

SQL> SELECT version FROM v$timezone_file;


   VERSION
----------
         4

this value is less than 14, So we have to upgrade to 14.

SQL>  SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         4

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

check for the timezone .dat files 

/opt/oracle/product/11.2.0.3/dbs > ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*

-rw-r--r--   1 oracle   dba         336K Aug 21  2011 /opt/oracle/product/11.2.0.3/oracore/zoneinfo/timezone_14.dat

-rw-r--r--   1 oracle   dba         773K Aug 21  2011 /opt/oracle/product/11.2.0.3/oracore/zoneinfo/timezlrg_14.dat

/opt/oracle/product/11.2.0.3/dbs >

Connect as SYS user and prepare for the upgrade.

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
PL/SQL procedure successfully completed


SQL> SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              PREPARE

Now you will see 14 as secondary timezone.

Truncate all the DST affected tables:

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.



Run the below PL/SQL code to log errors for DST affected tables.

SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

Following run should return no rows which confirms that no DST tables affected during the operation.

SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected

End the prepare.

SQL>  EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.

Shut the database and startup in UPGRADE mode.

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3424985088 bytes
Fixed Size                  2163120 bytes
Variable Size            2315263568 bytes
Database Buffers         1090519040 bytes
Redo Buffers               17039360 bytes
Database mounted.
Database opened.
SQL>

Execute the DST upgrade.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

PL/SQL procedure successfully completed.

SQL>  SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE

SQL>

Shut the database and Startup the database in NORMAL mode.

SQL> SELECT version FROM v$timezone_file;
VERSION
———-
14


============

upgrade DB from 10.2.0.4 to 11.2.0.3 + ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist

After upgrading the DB from 10.2.0.4 to 11.2.0.3, when I select data from data dictionary view or when I run utlrp.sql, getting following error.

SQL> select count(*) from dba_objects where status<>'VALID';
select count(*) from dba_objects where status<>'VALID'
                     *
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.DBA_OBJECTS
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.NAME_SECURITY"
ORA-06512: at line 2


SQL> Alter trigger SYS.NAMECHECK_BEFORE_DDL_DB_TRG disable;

Trigger altered.

SQL> select count(*) from dba_objects where status<>'VALID';

  COUNT(*)
----------
       987

• 
  

  Post Upgrade Scripts - utlu112s.sql & catuppst.sql report many Errors after upgrading Database from 10.2.0.X to 11.2.0.X (Doc ID 1543850.1)

ORA-25153: Temporary Tablespace is Empty & ORA-06512: at "SYS.NAME_SECURITY", line 166

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/uo1/database/myprod/ myprod _temp_01.dbf' Size 2G;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-25153: Temporary Tablespace is Empty

ORA-06512: at "SYS.NAME_SECURITY", line 166

ORA-06512: at line 2

SQL> Alter trigger SYS.NAMECHECK_BEFORE_DDL_DB_TRG disable;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/uo1/database/myprod/ myprod _temp_01.dbf' Size 2G;

Tablespace altered.

SQL> Alter trigger SYS.NAMECHECK_BEFORE_DDL_DB_TRG enable;