export data pump errors ORA-39014 ORA-39029 ORA-31671 ORA-04030

While performing an export database export dump the following errors where generated:

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW00” prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04030: out of process memory when trying to allocate 2328 bytes (kxs-heap-c,temporary memory)
ORA-06512: at “SYS.KUPW$WORKER”, line 1887
ORA-06512: at line 2

 

Solution:

ORA-4030 indicates a limit has been reached with respect to the Oracle process private memory allocation

 

check max_map_count  and increase it in a Linux environment.

more /proc/sys/vm/max_map_count
sysctl -w vm.max_map_count=200000

 

Converting an Oracle 12c Clusterware Standard Cluster to an Oracle Flex Cluster

The following is a summarized procedure in how to convert from Oracle 12c standard Cluster to Oracle New Flex cluster.

 

  1. Execute the following command to check your current cluster mode:

crsctl get cluster mode status

    cluster is running in “standard” mode

  1. Run the command to check that GNS is configured with fixed IP:

srvctl config gns

** if GNS  is not configured then you need to do so by executing the following command as “root” after making sure that GNS IP entry is added in DNS server:

srvctl add gns -vip vipname | ip_address

                 srvctl start gns

 

  1. Set the mode of the cluster to “flex” by executing the below command as “root”:

crsctl set cluster mode flex

Cluster mode set to “flex”

 

  1. Stop/Start the cluster so the new mode takes affect:

 crsctl stop crs

                   crsctl start crs

Status of cluster mode is “FLEX”

 

Important Final Remarks:

  • The cluster conversion is one direction from standard===> Flex  ONLY.

 

  • GNS is required with fixed IP address for Flex setup.

 

  • Oracle Flex cluster works ONLY with Oracle Flex ASM, while Oracle Flex ASM can work with either standard or Flex cluster.

 

                   Oracle Flex Cluster Architecture

 

oracle-flex-architecture

 

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.