Wednesday, March 16, 2016

ORA-24247: network access denied by access control list (ACL) IN ORACLE 11G

You might face this issue when you will work with SMTP-SERVER for sending EMAIL from ORACLE DATABASE through Oracle packages.


As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB

So, whenever you will face this error, you have to give permission to that user for this host and port.  Just execute following statement with SYS user. (before that please change the below highlighted user name and SMTP server name as yours)

      acl           => 'smtp-gate-permissions.xml',
      description   => 'Permissions for smtp gate',
      principal     => 'HALIMDBA', ----DB USER NAME WHO WILL SEND THE EMAIL
      is_grant      => TRUE,
      privilege     => 'connect');

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl          => 'smtp-gate-permissions.xml',
                                HOST         => 'SMTP-SERVER-NAME', ----SMTP SERVER NAME OR IP
                               lower_port   => 25, ---THIS DEFAULT PORT
                               upper_port   => NULL);


A user name and password are being requested by http://:. The site says: “XDB” in Oracle APEX

After Installation Oracle Apex, whenever I tried to connect in Oracle APEX environment with following URL


It was saying -
A user name and password are being requested by http://:. The site says: “XDB”    (In apex version 4.2.4 and Oracle Database version 10g Enterprise Edition Release - 64bits.)

Solution was: 
1) Log in with sys user then
2) unlock and change the XDB user's password and provide these when it will ask

SQL> Alter user xdb identified by xdb account unlock ;

3) Then invoke this URL  http://localhost:8080/apex    
It will ask for user id and password. please provide this here. 

user id : xdb
password : xdb


ORA-00904 string: invalid identifier

Recently we faced this issue when we renamed a column and changed columns datatype of a table which have a function based index on it.

   After this change, whenever we were trying to query this table it says above error.

   Our problem solved by Rebuilding that function based indexes of this target table.

I hope it will help you to identify the gray area quickly. cheers.

Failed to connect to remote database Error is ORA-01017

You may encounter above error in oracle dataguard environment- if you
    1) change the SYS user's password
or 2) change the SYSDG user's password - in 11g and above
or 3) remove any of password files 
or 4) remote_login_passwordfile parameter is not set to SHARED or EXCLUSIVE

Recently, I was asked to solve this problem for an environment- I saw they changed the SYS user's password in Primary database but didn't update this password to Standby databases.

You can't update the password in standby database directly. so solution is- after changing password in primary database, transfer the password file from primary to standby databases.

 Following are the steps to do this...

1) Shutdown the standby database
2) Rename the ORACLE_HOME\dbs\orapwHALIMDB this file on standby database.
3) Copy ORACLE_HOME\dbs\orapwHALIMDB from primary to standby database same location (assume HALIMDB is the instance name).
4) Then change this new file name to actual standby database's instance name.
5) Start standby database.

for transfer (in linux) you can use like below-

$scp oracle@primary_server_name:/u01/app/oracle/product/12.1/db_1/dbs/orapwHALIMDB   oracle@standby_server_name:/u01/app/oracle/product/12.1/db_1/dbs

Just FYI- actual error from database alert log file and data guard broker log file- (intentionally changed the instance name to halimdb )

Connection to database halimdb returns ORA-1017.
Please check if database halimdb is using a remote password file,
its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and the SYS or SYSDG password is the same as this database.
Failed to connect to remote database halimdb. Error is ORA-01017
Failed to send message to site halimdb. Error code is ORA-01017.


Thursday, September 10, 2015

emctl is not working in oracle 12c and How to manually configure EM Express

From oracle 12c, there is no command like “emctl start dbconsole” because, in 12c there is no DBCONSOLE ( no middle-tier for EM). In replace Oracle has introduced EM Express.

What is EM Express and its Architecture?

Oracle Enterprise Manager Database Express, also referred to as EM Express, is a web-based tool for managing Oracle Database 12c. Built inside the database server, it offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.. 
EM Express is designed to be lightweight and to incur minimal overhead on the database server. In order to achieve this goal, EM Express is built inside the Oracle Database and only uses internal infrastructure components. . such as XDB and SQL*Net. It does not require any separate middle-tier components.

Since EM Express is built inside the database, the database has to be open in order to use EM Express, and EM Express cannot perform actions outside the database.

EM Express does not have background tasks or processes that periodically collect information. Instead, it utilizes data that is already collected by the database. Data is requested only when the user interacts with the UI and all UI processing is done in the browser, thus minimizing load on the database server.

By default EM Express will install by DBCA while you will create a database in oracle 12C, you just need invoke it via


for this you need to know the port number. 

How can I find the HTTP/HTTPS port for EM Express

from SQL*Plus:
SQL> select dbms_xdb.getHttpPort() from dual; ---http port


SQL> select dbms_xdb_config.getHttpsPort() from dual;--https


Privilige for EM express


SQL> grant EM_EXPRESS_BASIC to scott ;

By default EM Express is installed by DBCA when you will create a database but still you can configure your OWN. Please see in below.

Manually configure the EM Express in Oracle 12c

  1. Configure and start the Oracle Listener.
  2) Assign local_listener parameter value ( If listener is         running     on a nonstandard port. for example, not 1521)
The local_listener entry references a TNSNAMES entry that points to the correct listener.
So you can set it like : 

SQL> alter system set local_listener = orcl ; --(orcl is the tnsnames)

SQL> alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=1526)) (CONNECT_DATA=(SERVICE_NAME=service_name)(SERVER=DEDICATED)))' ;

  1. Enable the TCP dispatcher by adding the following entry to the init.ora file for the database you want to manage using EM Express:
dispatchers="(PROTOCOL=TCP)(SERVICE=ORCLXDB)" ---here ORCL is the SID name .
SQL> alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=ORCLXDB)" ;

  1. Then Restart the database so that the changes made can take effect.

  1. Set the HTTPS/HTTP port for EM Express
Connect with SYS then,
      For a Non-Container Database : 


      For Container Database: 
      SQL> alter session set container=CDB$ROOT;
        For PDB (plug-gable Database)
        SQL> alter session set container=PDB1;
6) Use the following command to confirm that the port has registered with the listener:
cmd> lsnrctl status | grep -i 5507 ---(port number)
7) For accessing to EM express


For example: