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.