ORA-01110 ORA-01157

******* PROBLEM:

a data file was added on the primary database table space and the standby database was configured with

MANUAL” file management:

SQL> show parameter standby

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      MANUAL


this will lead to the following errors being thrown while applying archive logs (syncing the database with standby database):


ORA-01110: data file 2: ‘F:\ORACLE\PRODUCT\12.1.0\DB_1\DATABASE\UNNAMED00002’

ORA-01157:cannot identify/lock data file 2 – see DBWR trace file

ORA-01111: name for data file 2 is unknown – rename to correct file

******* SOLUTION:

execute the following query to find out the data file location on the primary database:

SQL> select file#,name from v$datafile where file#=2;


execute the following on the standby database:

SQL> alter database create datafile ‘F:\ORACLE\PRODUCT\12.1.0\DB_1\DATABASE\UNNAMED00002’ as ‘G:\DATAFILE\DBF2\ORAD12D01.ORA’ ;


then put the database on automatic file management mode on the standby (if you are using init.ora text file add the entry to ensure consistency in restarts):

SQL> alter system set standby_file_management=AUTO;



How to Roll Back (De-Install) Database Patch in

The following procedure is for Non-RAC environment and in the below example I am rolling back July 2016 PSU :

Double check that under $ORACLE_HOME/rdbms/admin

that there is a file with the naming convention catbundle_PSU_DBSID_ROLLBACK.sql

If this file doesn’t exist then you need to execute the following:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog



SQL> @catbundle.sql psu apply

SQL> @utlrp.sql



Shutdown the database

SQL> shutdown immediate;

Execute opatch utility to rollback the patch:

opatch rollback -id 23054359

Remark: the patch number 23054359 is supplied with HTML document that comes with the patch downloaded.

cd $ORACLE_HOME/rdbms/admin



SQL> @catbundle_PSU_ORAT772_ROLLBACK.sql

SQL> @utlrp.sql


For Java component:

Shutdown the database

SQL> shutdown immediate;


Execute opatch utility to roll back the patch:

opatch rollback -id 23177551

cd $ORACLE_HOME/sqlpatch/23177551



SQL> startup upgrade

SQL> @postdeinstall.sql

SQL> shutdown

SQL> startup

SQL> @utlrp.sql







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



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




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












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>drop procedure sys.validate_context;


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




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;


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;