Wednesday, June 17, 2020

Uninstalling ORDS from Database

Hello Guys!!

Oracle REST Data Service (ords) is used as alternative to Oracle HTTP server (OHS) proxy web server that uses mod_plsql. In this post I would like ot share my experience on how to uninstall the ORDS from the Database.

Before uninstalling you should aware of the following:

Where is the ords.war exists
JDK installed path (JAVA_HOME
From which Database it need to uninstall

Syntax:

Navigate to the ORDS extracted directory which was previously used:

  java -jar ords.war uninstall

ORDS Uninstallation Issue

The challenge we faced during the ORDS uninstall

  $JAVA_HOME/bin/java -jar ords.war uninstall
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter the database service name [ORCL]:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema. Installation may be required.
Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:
Jun 15, 2020 5:50:53 AM oracle.dbtools.installer.Installer uninstallORDS
INFO:
Uninstall Oracle REST Data Services
... Log file written to /opt/apex_listener/logs/ordsuninstall_2020-06-15_055053_00961.log
Jun 15, 2020 6:36:05 AM oracle.dbtools.rt.config.setup.Uninstall execute
WARNING: Error executing script: ords_uninstall.sql Error: ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at line 6
ORA-06512: at line 6
 Refer to log file

Cause :

In the log file, we found that there is something blocking session that is not allowing to complete uninstall.

   force_print
Rollback
Error starting at line : 85 in command -
begin
   for c1 in (
      select username from sys.dba_users where username in ('ORDS_METADATA','ORDS_PUBLIC_USER')
   ) loop
      dbms_output.put_line('drop user ' || c1.username);
      execute immediate 'drop user ' || c1.username || ' cascade';
   end loop;
end;
Error report -
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at line 6

Solution :

After taking DDL Backup and performing the drop schemas which are failed ORDS_METADATA and ORDS_PUBLIC_USER then the ORDS uninstall command executed successfully

Official Reference:

ORDS Uninstallation

Here the moral of this story, Understand the log files properly and what the system unable to do that activity do it manually. Some time digging into the automated scripts become more complicated but the real detective in you will come out to solve such problems!

Sunday, June 14, 2020

Oracle APEX & Oracle REST Data Services installation

Namaste!! Welcome to 'MyOFMWExperiments' blog!

In this post, I would like to share about the Oracle APEX 18 Installation process. Oracle Database 19.3 includes this APEX installer inside the release installer bundle.

When I started exploring the APEX thought that I've to download the Oracle APEX separately and do the installation on the Oracle Database. When I've gone thru many YouTube videos about Oracle APEX. But all those previous versions.

After the Creation of the dedicated PDB connect to it with sys as SYSDBA role. Login to that PDB which has named as pdb1. The APEX installation will be simple sql script 'apexins.sql', usually, it is present in the 'apex' folder inside Oracle database or if you download Oracle APEX specific version and extracted.

In your Oracle database 19c installed box, Change directory to the directory holding the unzipped APEX software. In that directory you need to connect to the PDB which was created:

  cd /opt/oracle/product/19c/dbhome_1/apex 
  sqlplus / nologon
  SQL> conn sys@pdb1 as sysdba
	Enter password:
	Connected.
     @apexins sysaux sysaux temp /i/

There are three phases of APEX installation:

Phase 1: Setting the session environment
Phase 2: Upgrade Metadata, APEX_180200, restrictions, installation
Phase 3:Validating Installation

When we run this APEX installation on a database that will almost take 30 mins of elapsed time.

Once APEXINS sql script completed you cans ee the above screen.

How to Verify APEX version on the database?

You can rn the following sql :

SELECT version FROM dba_registry WHERE comp_id='APEX';

If there is older version exist then use the same version apex folder to uninstall. If it is not exist download that same older version and rnn the following command from that apex folder.

@apxremov.sql

Exit from the sqlplus session. reconnect to sys user withe sysdba to install.
Now set the passwd for the APEX

@apxchpwd.sql

This script can be used to change the password of an Application Express instance administrator. If the user does not yet exist, a user record will be created.

Remember the following password rules, I've used password as : Wlatechtrainer_123 [Sample to understand]

Password must contain at least one numeric character (0123456789).
Password must contain at least one punctuation character (!"#$%&()``*+,-/:;?_).
Password must not contain username.
Password must contain at least one upper-case alphabetic character.
Password must not contain username.

Configure Embedded PL/SQL Gateway (EPG)

Here is a trick setting the one directory back where apex folder exist as parameter to the sql script.

  @apex_epg_config.sql /opt/oracle/product/19c/dbhome_1

Next we need to UNLOCK the ANONYMOUS account:

  ALTER USER ANONYMOUS ACCOUNT UNLOCK;

Set the HTTP Port to Non-zero, which was set to 0 earlier to APEX Install. Resetting to 8080 port and verify the same.

  EXEC DBMS_XDB.sethttpport(8080);
  SELECT DBMS_XDB.gethttpport FROM dual;

Enable Network services on Oracle database

  BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'localhost',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name=>'APEX_180200',
principal_type=> xs_acl.ptype_db)
);
END;
/