installation/deinstallation of Oracle Text in Oracle 12c

Best Title

In Oracle 12c Database you can’t manually install or de-install Oracle Text component (anymore), if your database was upgraded from 11g to 12c with Oracle Text component already installed then you are OK.

To install in 12c , you will be using DBCA (Database Configuration Assistance):

*** Command Line silent approach (syntax):

dbca -silent -configureDatabase -sourceDB <db unique name for RAC or SID for Single Instance db> -sysDBAUserName <user name with SYSDBA privileges> -sysDBAPassword <password for sysDBAUserName user name>] -addDBOption ORACLE_TEXT

ora47> dbca -silent -configureDatabase  -SourceDB ora47 -sysDBAUsername sys -addDBOption ORACLE_TEXT

Preparing to Configure Database

4% complete

8% complete

40% complete

Adding Oracle Text

44% complete

45% complete

46% complete

47% complete

48% complete

49% complete

50% complete

51% complete

52% complete

53% complete

54% complete

55% complete

56% complete

57% complete

58% complete

59% complete

60% complete

61% complete

62% complete

63% complete

64% complete

65% complete

66% complete

67% complete

68% complete

69% complete

70% complete

71% complete

72% complete

73% complete

74% complete

75% complete

76% complete

77% complete

78% complete

79% complete

80% complete

Completing Database Configuration

100% complete

Look at the log file “/ora47/ora47/cfgtoollogs/dbca/ora47/ora47.log” for further details.

*** GUI interface:

cd /etc

vi oratab

//add your database SID:ORACLE_HOME

ora47:/oraclex/ ora47/product/12.1.0:N

export DISPLAY=PC_SICO:0.0

dbca

IMG1

IMG22

IMG33

 

IMG44

 

IMG55

IMG66

 

IMG77

If you face the above the problem, then you need grant  execute the following system packages to “PUBLIC” :

 

SQL> grant execute on sys.dbms_sql to public;

 

SQL> grant execute on sys.dbms_job to public;

 

SQL> grant execute on sys.dbms_lob to public;

and that is it….you have successfully installed Oracle Text.

select * from dba_registry where COMP_ID=’CONTEXT’ ;

 

if you want to de-install the Oracle TEXT component you can use DBCA again and “uncheck” the component.

 

if whaever reason you couldn’t do that then use the traditional 11g way:

SQL>@?/ctx/admin/catnoctx.sql
SQL>drop procedure sys.validate_context;

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

select comp_id, comp_name, version, status from dba_registry where COMP_ID=’CONTEXT’;
SELECT object_name FROM dba_objects WHERE status = ‘INVALID’;

 

 

100,000 Views !!!

my blog just reached 100,000 views…..which is an encouraging milestone. I started my blog in 2013 where i wanted to share with the community any new technologies in the database management system domain  or technical problems faced and i was able to resolve.

 

I hope that my blog helped many people around the world , and will try my best to continue blogging.

 

Thank You.

USE GRANT READ instead of GRANT SELECT in Oracle 12c

Normally when we want to grant an oracle database account access to read records form certain tables, we use the SQL command (GRANT SELECT), however this is found to be not the best security practice. And, new security feature has been introduced in Oracle 12c which is GRANT READ.

To illustrate more,

I have created a dummy account named “dummy_test” with the following basic privileges:

dummy account

And created a dummy table with random values called “DUMMY_RECORDS”, and executed the below SQL statement to grant the user access to read records from the table:

SQL> grant select on DUMMY_RECORDS to dummy_test ;

Now….the interesting part is the following…..i will be able to exclusively LOCK the table !!!

either by executing the following:

SQL> lock table DUMMY_RECORDS in exclusive mode;

OR

SQL> select * from DUMMY_RECORDS for update;

exclusive lock.jpg

Now, let us revoke (GRANT SELECT) and use (GRANT READ) on the table

grant read 1

grant read 2

as shown above, after logging with the account we were not able to exclusively  lock the table and ORA-01031 was thrown.

Important Remarks:

  • this security feature is only available in 12c release.

 

  • some applications could frequently use (select* from table for update) frequently so you need to test the consequences of using the GRANT READ permission.

 

  • the purpose of this security feature is that it will prevent the hacker who stole the credentials of the account to lock the table which will block transactions and impact the running the application ! (denial of service)

 

 

 

 

useful SQL queries to find Segments that can’t extend

The following SQL query, will provide the list of segments that can’t be extended:

 

Select s.owner segowner, s.segment_name segname,s.segment_type segtype,
s.tablespace_name segtablespace, s.next_extent segnextext, s.bytes
from dba_segments s
where (s.next_extent * 5) > (select nvl((select max(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name),0)  from dual)
order by segowner

 

Also, this query will list segments that are approaching max extents:

 

Select segment_name, segment_type
from dba_segments
where extents >= max_extents -5;

Real Time ADDM (Automatic Database Diagnostic Monitoring) in Oracle 12c

Automatic Database Diagnostic Monitoring (ADDM) is one of the ways to use if you are facing performance problems with your database to gather information. Moreover, in previous releases the ADDM is generated based on 2 AWR (Automatic Workload Repository) Report snapshots which is taken on hourly basis and retained for 8 days by default. In oracle 12c, a new enhanced feature is introduced which is Real Time-ADDM. The real time ADDM is based on information fetched from memory triggered by manageability monitor process (MMON) especially when the database is in “hanging” state.

Database statistics gathering requires that STATISTICS_LEVEL parameter is set to TYPICAL or ALL. Also, check CONTROL_MANAGEMENT_PACK_ACCESS parameter.

ADDM DB Parameters

Let us assume a scenario that your database is in “hanging” state:

sqlplus ‘/ as sysdba’  (if you can’t access then use: sqlplus -prelim “/as sysdba”)
SQL>@$ORACLE_HOME/rdbms/admin/rtaddmrpt.sql

SQL>exit

Now you have successfully generated a Real-Time ADDM, check it and see what is causing the problem (the file will be in HTML format).

Another important report you can generated is “Performance Hub Active Report” which is a comprehensive HTML report that will show different tab information such as:

Activity

Workload

ADDM findings

Resources (CPU and Memory)

I/O

This can be generated using the following query:

SQL>@$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

perf hub

oracle 11g (11.2.0.4) CATPROC – oracle database packages and types invalid

when checking your database components after an upgrade or a patching, using the below query:

SQL> select * from dba_registry;

IF you found out that CATRPOC (oracle database packages and types) invalid

To fix this:

$sqlplus “/as sysdba”

SQL> startup upgrade

SQL > @?/rdbms/admin/catalog.sql

SQL > @?/rdbms/admin/catproc.sql

SQL > @?/rdbms/admin/utlrp.sql

 

Then you need to shutdown/startup the database in normal mode

SQL> shutdown immediate;

SQL> startup;

Then check the CATPROC component in your database.

Exploring Oracle 12c Unified Auditing

After installing and starting up your oracle 12c database, check that the “Unified Auditing” status by default it should be “FALSE”.

When you create a new database, by default the database uses mixed mode auditing, which enables both traditional (that is, the audit facility from releases earlier than Release 12c) and the new audit facilities (unified auditing). This chapter describes how to use pure unified auditing only.

You can enable the database in either of these two modes: the mixed mode auditing or pure unified auditing mode. Even though the features of unified auditing are enabled in both these modes, there are differences between them. In mixed mode, you can use the new unified audit facility alongside the traditional auditing facility. In pure unified auditing, you only use the unified audit facility.

SQL> select parameter , value from v$option where PARAMETER = ‘Unified Auditing’;

unif_status_sql

***Oracle 12c Unified Auditing Architecture Diagram***

Oracle 12c unified auditing architecture diagram

Howe to enable unified auditing?

1.Shutdown listener and database:

Lsnrctl stop listener_dev5

SQL> shutdown immediate

2.Relink Oracle Home binaries:

For (unix/linux)

cd $ORACLE_HOME/rdbms/lib


make -f ins_rdbms.mk uniaud_on ioracle

For windows:

must rename the %ORACLE_HOME%\bin\orauniaud12.dll.dbl file to %ORACLE_HOME%\bin\orauniaud12.dll

 

3.startup the database

SQL> startup

4.Check the status if the unified is enabled successfully by executing the query:

SQL> select * from v$option where PARAMETER = ‘Unified Auditing’;

uni_true

 

Also to avoid having both auditing mechanisms working check the following parameter:

SQL> show parameter AUDIT_TRAIL

 

NAME                                 TYPE

———————————— ——————————–

VALUE

——————————

audit_trail                          string

DB, EXTENDED

It should be set to NONE

audit_trail_none

To turn unified auditing OFF in (Unix/Linux) environment:

cd $ORACLE_HOME/rdbms/lib


make -f ins_rdbms.mk uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME

 

I will now explore data pump and RMAN operations with the new feature:

 

 

A.  Auditing Oracle Data pump operations:

Create the a new export data pump policy called AUDIT_EXPORT_DATAPUMP

SQL> create audit policy AUDIT_EXPORT_DATAPUMP actions component=datapump export;

Audit policy created.

Enable the audit policy:

SQL> audit policy AUDIT_EXPORT_DATAPUMP;

Audit succeeded.

To verify that audit policy is enabled execute the sql query:

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES

 where POLICY_NAME like ‘%AUD%’;

audit_policy_datapump.png

Remark: you will notice in the highlighted yellow bellow that auditing option is being generated while performing the export

uni_export.png

After the export is finished, you can check that export process in the unified audit view:

SQL> select DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1

 from   UNIFIED_AUDIT_TRAIL where  DP_TEXT_PARAMETERS1 is not null;

 

B.  Auditing Oracle RMAN operations:

Perform a backup

RMAN> backup tablespace ts_user_data_01;

After finishing the backup check if this was update:

SQL> select DBUSERNAME, RMAN_OPERATION

from UNIFIED_AUDIT_TRAIL where RMAN_OPERATION is not null;

 

rman_uni_query

If not updated yet, flush it:

SQL> exec SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle 11.2.0.4 error PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’

After upgrading the database to 11.2.0.4 and applying the latest Patch Set Update(October 2015 PSU) (11.2.0.4.8) , a database account could not perform any update or delete transaction on a table (that has context index) and received the following errors:

 

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

Solution:

There is a patch provided by Oracle to fix this OR perform the following “workaround” by recreating ctxsys.syncrn procedure:

SQL> sqlplus ‘/as sysdba’

SQL>alter session set current_schema=CTXSYS;

SQL>create or replace procedure syncrn (
  ownid IN binary_integer,
  oname IN varchar2,
  idxid IN binary_integer,
  ixpid IN binary_integer,
  rtabnm IN varchar2,
  srcflg IN binary_integer,
  smallr IN binary_integer
)
  authid definer
  as external
  name “comt_cb”
  library dr$lib
  with context
  parameters(
    context,
    ownid  ub4,
    oname  OCISTRING,
    idxid  ub4,
    ixpid  ub4,
    rtabnm OCISTRING,
    srcflg ub1,
    smallr ub1
);
/

// execute the following to recompile all database objects:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

//Then check that your database components are “valid”:

SQL> select * from dba_registry;

//check if there is any invalid objects under CTXSYS:

SQL>select owner, count(*) from dba_objects where status <>’VALID’ group by owner order by 1;

 

Remark: to find the patch set update applied on your database use the following query:

select * from sys.registry$history;

Upgrading Oracle database from 11.2.0.3 to 11.2.0.4

The following is a generic procedure to upgrade from 11.2.0.3 to 11.2.0.4 . As many of you know that 11.2.0.3 release has been desupported and last PSU released was in JULY 2015. so you need to either upgrade to 12cR1 OR to 11.2.0.4 . The right move is of course to move forward to 12c however, some vendor applications are not certified  Yet😦 !!!

So you will need to upgrade 11.2.0.4.

I hope this will help. Please note that the below procedure assumes that you have already installed 11.2.0.4 binaries successfully.

 

Procedure upgrade in Unix environment:

//create a directory called “upgrade_11gR4”

mkdir upgrade_11gR4

cd upgrade_11gR4

//now copy 2 scripts (utlu112i.sql) and (dbupgdiag.sql )

cp /oraclex/oradbp05/product/11.2.0.4/rdbms/admin/utlu112i.sql .

cp /oracle_software/database/dbupgdiag.sql .

//gather general database information

sql

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> set pages 300

SQL> set lines 250

SQL> col owner for a23;

SQL> col object_name for a35;

SQL> col object_type for a20;

SQL> col action for a15;

SQL> col comments for a40;

SQL> col comp_name for a40;

SQL> col version for a15;

 

SQL> spool before_upgrading_11gR4.log

SQL> SELECT NAME FROM V$DATABASE;

SQL> SELECT * from V$VERSION;

SQL> SELECT ACTION,VERSION,COMMENTS FROM SYS.REGISTRY$HISTORY;

SQL> SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

SQL> select count(*) from dba_objects where  status = ‘INVALID’;

SQL> spool off

SQL>upgrade_information_gather.log

SQL>@utlu112i.sql

SQL>spool off

exit

//check the the log output and read it carefully

cat upgrade_information_gather.log

sql

SQL>alter session set nls_language=’American’;
SQL>@dbupgdiag.sql

//then check the log output for diagnostic information

//Purging the Database Recycle Bin

SQL> PURGE dba_recyclebin;

//Copy the LISTENER.ORA &  TNSNAMES.ORA & SQLNET.ORA from 11.2.0.3 directory to 11.2.0.4 directory

//shutdown the database

//change  your .profile to point to the new Oracle HOME binaries for 11.2.0.4

cd $ORACLE_HOME/rdbms/admin

sqlplus “/ as sysdba”

SQL>startup UPGRADE

SQL>@catupgrd.sql

//enter a directory where you want to log to be generated to

SQL> shutdown immediate;

sqlplus “/ as sysdba”

SQL>STARTUP

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL>set pages 300

SQL>set lines 250

SQL>col owner for a17;

SQL>col object_name for a35;

SQL>col object_type for a20;

SQL>Select owner,object_type,object_name from dba_objects where status=’INVALID’ ORDER BY 1,2,3;

SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql

 

//Run the following script to identify any new invalid objects due to the upgrade:

SQL>@$ORACLE_HOME/rdbms/admin/utluiobj.sql

 

//check your time zone

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

//if the the time zone is 14 then you are OK…and your upgrade is DONE…congratulations🙂

//if the time zone value is less than 14 then proceed with the following:

SQL>TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

SQL>TRUNCATE TABLE sys.dst$affected_tables;

SQL>TRUNCATE TABLE sys.dst$error_table;      

 

//Start upgrade window

SQL>EXEC DBMS_DST.BEGIN_UPGRADE(14);

//The message “An upgrade window has been successfully started.” will be seen

//check

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

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE ‘DST_%’

ORDER BY PROPERTY_NAME;

//restart the database

SQL>shutdown immediate;

SQL> startup;

SQL>set serveroutput on

VAR numfail number

BEGIN

DBMS_DST.UPGRADE_DATABASE(:numfail,

parallel => TRUE,

log_errors => TRUE,

log_errors_table => ‘SYS.DST$ERROR_TABLE’,

log_triggers_table => ‘SYS.DST$TRIGGER_TABLE’,

error_on_overlap_time => FALSE,

error_on_nonexisting_time => FALSE);

DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :numfail);

END;

/

SQL> VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :fail);

END;

/

//Output that will be seen: “An upgrade window has been successfully ended”  “Failures:0”