How to authenticate with LDAP in Oracle APEX

First, create an Access Control List (ACL). Let's assume the owner of the application is 'userapp'. The ACL will pe granted to APEX04000 user (first, check the name of the apex user with 'select username from dba_users'), and then a privilege will be added for USERAPP. The ip of the LDAP server is 192.168,132,12, but, of course, better way is to use the name instead of the ip. Your call. The LDAP port is 389.

 BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'ldap_acl_file.xml',
    description  => 'ACL to grant access to LDAP server',
    principal    => 'APEX_040000',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'ldap_acl_file.xml',
    host        => '192.168.132.12',
    lower_port  => 389,
    upper_port  => NULL);

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl         => 'ldap_acl_file.xml',
    principal   => 'USERAPP',
    is_grant    => true,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);
                                     
  COMMIT;
END;
/                                     
Now, under the APPUSER scheme let's create the authentication function:

CREATE OR REPLACE FUNCTION appuser.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := '192.168.132.12';
  l_ldap_port     VARCHAR2(256) := '389';
  l_ldap_base     VARCHAR2(256) := 'dc=domain,dc=ro';
  l_dn_prefix     VARCHAR2(100) := ''; -- here you can mention
                                       -- some peculiar group, 
                                       -- under your LDAP directory.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
BEGIN
  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
 
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
 
  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);
                                     
  -- No exceptions mean you are authenticated.
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed.
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status(p_status => 'Incorrect username and/or password');
    RETURN FALSE;   
END;
/

Now, it's time to use the function. Make an authorization scheme, under the Application Builder -> Shared Components -> Authentication Schemes (don't use the LDAP template scheme, just make an ordinary authentication scheme) and the most important thing to do is to put in the  Authentication Function box the following text: "return ldap_auth;"
And that's all, you will authenticate with the LDAP credentials.

Recover a standby database from archived logs gap (ORA-16783: cannot resolve gap for database ....)

First, identify de SCN where the standby has lost the pace


 select current_scn from gv$database;

 CURRENT_SCN
-----------
 2320214727


Of course, the SCN of the primary database is higher and the gap could be pretty big.


dgmgrl

 

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> show configuration;

Configuration - DGC

  Protection Mode: MaxAvailability

  Databases:

    primary - Primary database

      Error: ORA-16810: multiple errors or warnings detected for the database

     - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL> edit database 'standby' set state='APPLY-OFF';

on the primary:

rman target /              

RMAN> run

2> {

3> allocate channel ch1 type disk;

4> allocate channel ch2 type disk;

5> backup incremental from scn 2320214726 database format '/home/oracle/standby_%d_%t_%c_%p';

6> }

copy the backup pieces on the standby file system

on the standby:

RMAN> catalog start with '/home/oracle/bkp';

(of course, be careful with the locations, use your own instead of ....)

RMAN> run

2> {

3> allocate channel ch1 type disk;

4> recover database noredo;

5> }

 on the primary:

SQL> alter database create standby controlfile as '/home/oracle/bkp/standby.ctl';

Database altered.

copy the controlfile on the standby

stop the standby database, doesn't matter how

start it nomount

on the standby:

rman target /
RMAN> restore controlfile from '/home/oracle/bkp/standby.ctl';


alter database mount

RMAN> CATALOG START WITH '+standby_DATA1/standby/DATAFILE/';

RMAN> SWITCH DATABASE TO COPY;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
....

$ dgmgrl    

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> edit database 'standby' set state='APPLY-ON';

Succeeded.

sqlplus / as sysdba

> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

Now everything should be ok