datablogs: Oracle 19C
Showing posts with label Oracle 19C. Show all posts
Showing posts with label Oracle 19C. Show all posts

Thursday, December 15, 2022

Friday, March 18, 2022

Oracle REST Data Services (ORDS) : Upgrade ORDS from 5.1.4 to 19.4

 |   |  No comments  |  , , , , ,

 We have upgraded Oracle Apex in the previous blog and next have to plan for below things 

  • Apex static images update for upgraded version
  • ORDS Upgrade to stable version 


Prerequisites 

  • Downloads the upgraded apex version in oracle RDS ( Apex 21.1.2 )
  • Download latest stable version of ORDS 19.4
  • Make sure you have already configured Oracle Client 19C and Java 1.8 is installed 

Apex Static Image Upgrade : 

Before upgrade apex images , we will receive below error while login apex workspace page 


Once downloaded the Apex bundle , 
  • Rename the existing apex folder from D:\apex to D:\apex_5.1.4
  • Unzip the apex_21.1_en.zip folder into apex folder
  • Go to apex utilities folder 
  • Stop the ORDS running service 
Connect Oracle 19C in SQL Plus

C:\Windows\system>D:

D:\>cd apex

D:\apex>cd utilities

D:\apex\utilities>

D:\apex\utilities>sqlplus dataadmin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Enter Password:

SQL> @reset_image_prefix.sql

Enter the Application Express image prefix [/i/] https://static.oracle.com/cdn/apex/21.1.2/ 

...Changing Application Express image prefix

NEW_IMAGE_PREFIX
------------------------------------------
https://static.oracle.com/cdn/apex/21.1.2/

PL/SQL procedure successfully completed.

...Recreate APEX global

PL/SQL procedure successfully completed.

Commit complete.

Image Prefix update complete

Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

D:\apex\utilities>

Once its completed , we will able to browse the apex pages without any issues 

ORDS Upgrade Paths 
Step 1 : 
Rename existing ords folder as ords_3.0.13 . As mentioned in prerequisites , unzip the downloaded ords-19.4.6.142.1859 into ords folder 

Step 2 :
Copy the D:/apex/images folder into D:/ords/

Step 3:
Uninstall the existing ORDS version from Oracle RDS ,

D:\ords>java -jar ords.war uninstall 

Specify the database connection type to use. 

Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]: 

Enter the name of the database server [localhost]:dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com

Enter the database listen port [1521]: 

Enter 1 to specify the database service name, or 2 to specify the database SID [1]: 

Enter the database service name:ORCL 

Requires to login with administrator privileges to verify Oracle REST Data Services schema. 

Enter the administrator username:dataadmin 

Enter the database password for dataadmin: 

Confirm password: 

Connecting to database user: dataadmin url: jdbc:oracle:thin:@//dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com:1521/ORCL

Retrieving information. 

Uninstalling Oracle REST Data Services 

... Log file written to C:\Users\datablogsadmin\ords_uninstall_core_2022-03-16_133300_00152.log 

Completed uninstall for Oracle REST Data Services. Elapsed time: 00:00:07.840
Step 4 :
Set Config Directory 
java -jar ords.war configdir D:\config

Step 5 :
Update passwords and unlock below accounts in Oracle RDS 
  • APEX_PUBLIC_USER
  • APEX_LISTENER
  • APEX_REST_PUBLIC_USER
Connect Oracle 19C in SQL Plus

D:\apex\utilities>sqlplus dataadmin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Enter Password:

SQL>alter user APEX_PUBLIC_USER account unlock identified by Admin123;
SQL>exec rdsadmin.rdsadmin_run_apex_rest_config('Admin123','Admin123');
SQL>ALTER USER APEX_REST_PUBLIC_USER account unlock IDENTIFIED BY Admin123;
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'DATAADMIN', 'SELECT', true); 
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'DATAADMIN', 'EXECUTE', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'DATAADMIN', 'EXECUTE', true);
SQL>EXEC rdsadmin.rdsadmin_util.grant_apex_admin_role;
SQL>grant APEX_ADMINISTRATOR_ROLE to DATAADMIN;

Once its executed , proceed the ORDS Installation

Step 6 :
Install the required database naming configuration setup for ORDS , By default it will install as apex 

D:\ords>java -jar ords.war setup --database dataadmin
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:
Enter the name of the database server [localhost]:dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:ORCL
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:dataadmin
Enter the database password for dataadmin:
Confirm password:
Connecting to database user: dataadmin url: jdbc:oracle:thin:@//dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com:1521/ORCL

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:1
2022-03-16T05:35:15.021Z INFO   reloaded pools: [|dataadmin||, |dataadmin|al|, |dataadmin|rt|, |dataadmin|pu|]
Installing Oracle REST Data Services version 19.4.6.r1421859
... Log file written to C:\Users\datablogsadmin\ords_install_core_2022-03-16_133515_00223.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to C:\Users\datablogsadmin\ords_install_datamodel_2022-03-16_133527_00932.log
... Log file written to C:\Users\datablogsadmin\ords_install_apex_2022-03-16_133530_00191.log
Completed installation for Oracle REST Data Services version 19.4.6.r1421859. Elapsed time: 00:00:17.247

Step 7 :
Once installed ORDS , if you get any errors on ORDS users . Run below command in SQL PLUS

ALTER USER ORDS_PUBLIC_USER account unlock IDENTIFIED BY Admin123; 

ALTER USER ORDS_METADATA account unlock IDENTIFIED BY Admin123; 

Step 8 : 
Start the ORDS Service 

java -jar ords.war



Step 9 :
If you need to map your ords link with multiple schemas , its must have URL mapping . To avoid confusion to access multiple workspaces 

D:\ords>java -jar ords.war map-url --workspace-id ss --type base-path /datablogs01 dataadmin 


Its all completed , if you have any issues and doubts please contact us