Tuesday 27 February 2024

Oracle on Windows: create 19.21 Gold Image and use it for out-of-place patching

This guide shows a way, how a Gold Image can be created from an Oracle Home on Windows and how that can be used to patch other Oracle Homes. This worked for me, but is not meant to be the only, best or officially recommended Oracle way.

Prepare the image source home

Start with a fresh installed Oracle Home, in this case under D:\app\oracle\product\19.0.0\dbhome_1\.

There is no standard oraenv.sh under Windows, a minimal oraenv.cmd could look like that:

set ORACLE_BASE=C:\app\oracle
set ORACLE_HOME=%ORACLE_BASE%\product\19.0.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch\;%PATH%   

Run your oraenv.cmd, unpack the software (you can use tar xfz under Windows for unzip) and run setup.bat, see the Database Installation Guide 19c for Microsoft Windows

C:\app>mkdir c:\app\oracle\product\19.0.0\dbhome_1
C:\app>oraenv.cmd
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_2>tar xfz z:\Install\Windows\WINDOWS.X64_193000_db_home.zip
C:\app\oracle\product\19.0.0\dbhome_2>setup.bat 

In this example, we use an existing user oracle instead of a virtual account, which would be the default from the oracle installer. This makes directory handling easier for out-of-place-patching. Also we only need to setup the software only and this is a single instance installation. Under Linux, we would just apply the RU and one-offs from the setup, but the options '-applyRU 'and '-applyOneOffs' are not available under Windows (see the comments on https://mikedietrichde.com/2022/05/17/simple-database-installation-with-applyru-and-applyoneoffs/, search for Windows).

Update opatch by following Doc ID 274526.1 from Oracle Support, in this case using opatch 12.2.0.1.41.

For the Gold Image, we will patch the 19.03 dbhome_1 up to 19.21 plus two one-off patches for the JVM. So download and unpack the patches to a given directory, in this example c:\app\oracle\patches. The patch numbers are 35638318, 35648110 and 35681552, these have to be applied manually by running opatch apply in every patch directory, starting with 35681552 which is the Bundle Patch 19.21.

Now the dbhome_1 is on 19.21 with two additional one-off patches and will serve as source for our Gold Image.

Create the Gold Image

Next, from the patched Oracle Home, we create a Gold Image following the documentation.

setup.exe -createGoldImage -destinationLocation c:\app\oracle\images 

Wait for the installer to finish, then you should have the newly created image in the directory stated by -destinationLocation.

Create new Oracle Home and database

Next we create a new Oracle Home and a Database ORCL/ORCLPDB under dbhome_2. Create the new directory, change the ORACLE_HOME variable in oraenv.cmd to point to dbhome_2 and run it. Then unpack the 19.03 software to that directory the same way as dbhome_1 before.

C:\app>mkdir c:\app\oracle\product\19.0.0\dbhome_2
C:\app>oraenv.cmd
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_2>tar xfz z:\Install\Windows\WINDOWS.X64_193000_db_home.zip
C:\app\oracle\product\19.0.0\dbhome_2>setup.bat

Run setup.exe in dbhome_2 and this time also create a database ORCL with a PDB ORCLPDB.

So now we have a patched dbhome_1 as source for the Gold Image and an unpatched dbhome_2 running the database ORCL with the PDB ORCLPDB.

Out-of-place patching using the Gold Image

For out-of-place patching, we need to create a new Oracle Home, where we unpack the Gold Image. Change the ORACLE_HOME in oraenv.cmd to dbhome_3 and run it.

C:\app>oraenv.cmd
C:\app>mkdir c:\app\oracle\product\19.0.0\dbhome_3
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_3>tar xfz c:\app\oracle\images\db_home_2024-02-21_05-21-14PM.zip
C:\app\oracle\product\19.0.0\dbhome_3>setup

Run setup, choose software only and the oracle user instead of a virtual account. If setup.exe crashes with a Java EXCEPTION_ACCESS_VIOLATION, follow MOS note 2772729.1.


On Unix/Linux systems, we would already be done, but on Windows we have services which need to be migrated to the new Oracle Home.


In the registry, we see that the services are linked to a given Oracle Home, so these need to be migrated.

set OLDORA_HOME=%ORACLE_BASE%\product\19.0.0\dbhome_2

rem --- stop all Oracle services first ---
net stop OracleOraDB19Home2MTSRecoveryService
net stop OracleOraDB19Home2TNSListener
net stop OracleServiceORCL
net stop OracleVssWriterORCL

Stop all services from dbhome_2. For easier scripting, we point OLDORA_HOME to the Oracle home running the old version.

rem --- Copy spfile and pwdfile to new ORACLE_HOME
copy %OLDORA_HOME%\database\SPFILEORCL.ORA %ORACLE_HOME%\database
copy %OLDORA_HOME%\database\PWDorcl.ora    %ORACLE_HOME%\database

Copy the spfile and pwdfile to dbhome_3

rem -- Delete old services
%OLDORA_HOME%\bin\oramtsctl -delete
sc delete OracleOraDB19Home2TNSListener

Delete the MTS and Listener services from the old home.

rem --- Point services to new ORACLE_HOME
sc config OracleServiceORCL         binPath="c:\app\oracle\product\19.0.0\dbhome_3\bin\ORACLE.EXE ORCL"
sc config OracleJobSchedulerORCL    binPath="c:\app\oracle\product\19.0.0\dbhome_3\bin\extjob.exe ORCL"
sc config OracleVssWriterORCL       binPath="c:\app\oracle\product\19.0.0\dbhome_3\bin\OraVSSW.exe ORCL"

Migrate the OracleService, JobScheduler and VssWriter from the old to the new home.

rem --- create new services
%ORACLE_HOME%\bin\oramtsctl -new
%ORACLE_HOME%\bin\lsnrctl start
sc config "OracleOraDB19Home3TNSListener" start= auto

The formerly deleted services from the old home need to be recreated for the new home. Starting the Listener automatically creates a new Service for the new home. That one should be configured to start automatically.

rem --- start new services
oradim -EDIT -SID ORCL -STARTMODE auto -SRVCSTART system
net start OracleServiceORCL

Set the Oracle service to be started automatically and to start the database on service start. Then start the OracleService.

rem --- start the database and run datapatch
rem --- not needed with service auto start --- @echo startup | sqlplus / as sysdba
@echo alter pluggable database all open | sqlplus / as sysdba
@echo show pdbs | sqlplus / as sysdba
%ORACLE_HOME%\OPatch\datapatch

Before running the datapatch, make sure that all pds are open. When the open state has been saved by using ALTER PLUGGABLE DATABASE ... SAVE STATE, that step can be skipped. Here we open all PDB's before running the datapatch 


The database software and data dictionary should now be on the new version.

rem --- apply optimizer fixes for every PDB
@echo @?/rdbms/admin/dbmsoptim.sql | sqlplus / as sysdba
@echo @?/rdbms/admin/prvtoptim.plb | sqlplus / as sysdba
@echo execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES'); | sqlplus / as sysdba

Last step is enabling the optimizer fixes for the new version. The complete scripting can be found on GitLab.

Monday 13 November 2023

Create an APEX Interactive Grid on an ORDS AutoREST service

 If the target database cannot be accessed by a database link, this might be a super easy alternative.

Say you have an HR schema with the EMPLOYEES table. RMB and choose Enable REST Service ...

For this easy example, check Enable object and uncheck Authorization required. Click through the dialog until the end.
Open Database Actions and go to REST > AutoREST to find the EMPLOYEES Service. Click on the Open in new tab icon right of the URL.
That should work ootb, copy the URL
Move over to the APEX App Builder, Shared Components, REST Data Sources and click on Create.
Choose from scratch, give it a name, paste the service URL and change REST Data Source Type to Oracle REST Data Services. Click through the end of the dialog, accepting the defaults.
In your application, create a new page with an Interactive Grid. Point it to the new REST Data Source and check Editing Enabled, if you want to use that.
For editing, it is mandatory to select a primary key column. Click through the end of that dialog.
Run the page, change some data and click Save.
That should work without any extra manual configuration or coding.

Tuesday 2 May 2023

Installing ORDS 23.1 with Apex 23.1 on Oracle Database 23c Free Developer Release

This post is part of my Oracle Developer VM  Project series. It is not meant to replace the official documentation in any way and what I describe is no recommended configuration. It is only meant to be a guide, if you follow my DB Developer VM Project and want to add these services to your VM.

Part 1: Install Apex

Download the Apex install bundle and unzip it (eg. to /u01), which creates an apex folder. In the unpacked folder run the installer

sqlplus sys/your_password@freepdb1 as sysdba @apexins sysaux sysaux temp /i/

Leave the session open and create the Apex Admin user by running @apxchpwd.sql.
Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it:

1
2
3
4
5
6
7
BEGIN    
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
      host => '*',
      ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                         principal_name => 'APEX_230100',
                         principal_type => xs_acl.ptype_db));
END;

Part 2: Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_HOME=/u01/ords-23-1
export PATH=$ORDS_HOME/bin:$PATH

Create both directories:

mkdir /u01/ords-23-1
mkdir /u01/ords-config

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/). Optional rename the ords directory from the archive and add the ORDS version number, eg. ords-23-1 (and reflect that in the $ORDS_HOME variable in .bashrc). This makes it easier to switch versions when neccesary.
As $PATH and $ORDS_CONFIG are already set, from anywhere run
 
ords install --interactive

ORDS will ask for some configuration parameters. In most cases, you can just accept the defaults.
If you have followed my guide so far, the only parameters, that need to be set, are:
  • Enter the database service name [orcl]: freepdb1
  • Provide database username with administrator privileges: sys (and your password)
  • Enter the APEX static resources location: /u01/apex/images/
After the setup, ORDS will keep running in standalone mode. We are not done yet, but give it a try to check if everything went fine so far. Wait until the ORDS has completely started: 
Open Firefox and enter http://localhost:8080/ords, that should give you the Apex login page (as this is the first call, it will take a few seconds).
Looking at the ORDS terminal window, you will see the following warnings:
Though this is not a production environment, I prefer to configure the system to run without these warnings. At least it makes it easier to notice, if something really goes wrong. In this single user environment, we can set both parameters to lower values like below. 
Go to /u01/ords-config/global/ and edit settings.xml. Add the following lines before the closing </properties>:

<entry key="jdbc.MaxLimit">30</entry>
<entry key="jdbc.InitialLimit">10</entry> 

I also got the following error:
INFO Disabling document root because the specified folder does not exist: /u01/ords-config/global/doc_root
To get rid of this INFO either create that directory or edit /u01/ords-config/global/settings.xml and set standalone.doc.root to an existing folder.

Sometimes I ran into errors were all like WARNING The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked
Probably I messed up my passwords when running the installer. This can be fixed by setting the passwords for the following users (and unlocking them, just in case):

1
2
3
4
5
6
7
8
alter user ORDS_PUBLIC_USER account unlock;
alter user APEX_PUBLIC_USER account unlock;
alter user APEX_LISTENER account unlock;
alter user APEX_REST_PUBLIC_USER account unlock;
alter user ORDS_PUBLIC_USER identified by oracle;
alter user APEX_PUBLIC_USER identified by oracle;
alter user APEX_LISTENER identified by oracle;
alter user APEX_REST_PUBLIC_USER identified by oracle;

Now ORDS should come up without errors.

Just to double check that everything is there, call http://localhost:8080/ords/sql-developer

Of course, you need a rest enabled schema to log in. That's it, everything is running.

Tuesday 25 April 2023

Install Oracle 23c Developer Edition in Oracle Linux 8 VM

The Oracle 23c Developer Release is available as a complete, ready-to-run VM with a complete set of tools. But if you want, for whatever reason, build your own VM, you might want to follow this guide.

Start with a fresh Oracle Linux 8.7 VM like the one from The Oracle DB Developers VM project and run a sudo dnf upgrade to make sure to be on the latest patches. Avoid using Red Hat's historic kernels and boot with the latest kernel provided by Oracle.

Download the rpm and follow the installation guide from Oracle. My OL installation only found the preinstallation packages for 19c and 21c, but not for 23c. So I followed the guide for Red Hat Linux, which worked.

sudo -s
curl -L -o oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
dnf -y localinstall oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
dnf -y localinstall /home/oracle/ddrive/Install/Linux-x64/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
/etc/init.d/oracle-free-23c configure

When the installer is done, follow the installation guide and set the variables in .bashrc and source it:

export ORACLE_SID=FREE
export ORAENV_ASK=NO
. /opt/oracle/product/23c/dbhomeFree/bin/oraenv
export PATH=$ORACLE_HOME/bin:$PATH

Take a first look at the DB, it is ready to run now.

Download the latest SQLcl and unzip it to your favorite tools location like
wget https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
unzip sqlcl-latest.zip -d ~/bin

Add SQLcl to your PATH and also add a TNS_ADMIN variable to your .bashrc and source it.

export PATH=/home/oracle/sqlcl/bin:$PATH
export SQLPATH=~/sql

Make some settings for SQLcl for a nicer experience 

mkdir ~/sql
cat >> ~/sql/login.sql <<EOF
set statusbar on
set highlighting on
EOF

Now we have SQLcl:

Download SQLDeveloper from https://www.oracle.com/database/sqldeveloper/technologies/download/
I prefer the Other Platforms version. Unzip it to its destination.

unzip ~/ddrive/Install/Generic/sqldeveloper-23.1.0.097.1607-no-jre.zip -d ~/bin

Look here on how to add SQLDeveloper to the Gnome menu. Run SQLDeveloper, it will tell you if you need to adjust the JDK location at startup.

Create a connection

Follow this guide on how to install VSCodium and the Oracle Developer Tools for VSCode. Create a new Database connection in VSCodium to freepdb1.

So far, this is quite a complete environment to start with Oracle 23c Developers Edition.


Thursday 13 April 2023

Cloning a PDB with APEX pointing to ORDS REST services on the same database

This post shows cloning a database with APEX and some database REST services in the same database, which are accessed from APEX via a REST data source. Say we have a PDB testpdb and clone it to clonepdb for dev/test. The EMPLOYEES table in HR is REST enabled and the APEX application has a REST Data Source for that REST service. Without any changes, the REST Data Source from clonepdb would still point to testpdb. But it should point to clonepdb. This could be changed manually, but if this is needed for automatic provisioning of developer environments, some automation would be helpfull.

The URL is shown in ORDS Database Actions under REST/AutoREST


The APEX application has a REST Data Source TestEmployees pointing to that service:


The Remote Server attribute contains the IP address and the database connection, in this case the TESTPDB. Click on the pen next to it.
Enable Prompt on Install.

Now clone testpdb to clonepdb:

create pluggable database clonepdb from testpdb 
file_name_convert = ('/testpdb/', '/clonepdb/');

alter pluggable database clonepdb open;

For easier access add clonepdb to tnsnames.ora:

cat >> $TNS_ADMIN/tnsnames.ora <<EOF
CLONEPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clonepdb)
    )
  )

EOF
tnsping clonepdb

Stop ORDS, register clonepdb with ORDS interactively or create a password.txt file with your passwords and run:

cat > password.txt <<EOF 
<password>
<password>
EOF

ords install --db-pool clonepdb --db-hostname localhost --db-port 1521 --db-servicename clonepdb --feature-sdw true --feature-db-api true --feature-rest-enabled-sql true --admin-user sys --proxy-user --password-stdin < password.txt

Restart ORDS and navigate to REST/AutoREST. The service for the employees table also exists in clonepdb ...


... and works as expected.


The APEX application also made it into clonepdb, but of course the TestEmployees REST Data Source still points to testpdb
As the application has been cloned with the PDB, the Prompt on Install attribute does not trigger. We need to ex- and import the application. Export the application from testpdb and make sure that Export with Original IDs is checked
Import the application on clonepdb and check Reuse Application ID 100 From Export File 
In the last step, the installer prompts for the Base URL of the Remote Server, which has been tagged for Prompt on Install. Change it from testpdb to clonepdb.
Open the REST Data Source, it now shows the correct local endpoint. 
But for automatic deployment of developer environments, we need to get rid of the interactive step. Before we can import the export file by running it in SQLcl, we need to set the override with APEX_APPLICATION_INSTALL.SET_REMOTE_SERVER .

declare
    l_base_url varchar2(255);
begin
  apex_application_install.set_remote_server(
    p_static_id => '192_168_56_104_ords_testpdb',
    p_base_url  => 'http://192.168.56.104:8080/ords/clonepdb/');

    l_base_url := apex_application_install.get_remote_server_base_url('192_168_56_104_ords_testpdb');
    DBMS_OUTPUT.PUT_LINE('Base URL for ' || '192_168_56_104_ords_testpdb' || ' is now ' || l_base_url);
end;
/

That should give
And opening the REST Data Source in the APEX application on clonepdb should show the URL pointing to clonepdb.

So all that is needed after cloning the database with the APEX application and the REST service is running a script with apex_application_install.set_remote_server and re-importing the application.

Friday 3 February 2023

The Oracle DB Developer VM: Installing VSCodium and the Oracle Developer Tools

This post is part of my Oracle DB Developers VM series. Based on the previous postings on how to set up Oracle Linux 8 with the Oracle DB on VirtualBox, this post will show how to install VSCodium with the Oracle integration in this environment on a beginners level.
Gnome, in contrast to KDE, does not come with a good visual editor for developers, so we should install something better. VS Code has gained popularity and Oracle plugins are available, but VSCodium is the better choice because it does not contain telemetry/tracking. And according to this post, getting familiar with VSCodium is a good idea for Oracle developers anyways.
For installing VSCodium, just follow the official instructions:

sudo rpmkeys --import https://gitlab.com/paulcarroty/vscodium-deb-rpm-repo/-/raw/master/pub.gpg
printf "[gitlab.com_paulcarroty_vscodium_repo]\nname=download.vscodium.com\nbaseurl=https://download.vscodium.com/rpms/\nenabled=1\ngpgcheck=1\nrepo_gpgcheck=1\ngpgkey=https://gitlab.com/paulcarroty/vscodium-deb-rpm-repo/-/raw/master/pub.gpg\nmetadata_expire=1h" | sudo tee -a /etc/yum.repos.d/vscodium.repo
sudo dnf install codium
sudo dnf install dotnet-sdk-2.1

We also need to install the .NET Core Runtime for Linux to run the Oracle Developer Tools for VS Code. It is important to use the older version 2.1.
Double check the dotnet installation with

dotnet --list-sdks
dotnet --list-runtimes

That should return something like:
Searching for Code should now show you VS Code in your activities
Moving the icon to the right into the favorites bar makes it also available in the applications menu.
Start VSCodium, go to the extensions tab and search for Oracle.
Choose the official one from Oracle Corporation and click on Install.
After installation of the extension, on the left bar an Oracle DB tab will appear. Click it and in the Oracle Explorer click the plus (+) and insert your credentials.
Double click the newly created connection. Click around, for example choose a table and select Show Data.
Editing stored procedures and functions also works.
To execute some SQL press F1 and choose Oracle: Develop New SQL or PL/SQL
Select your connection
Type some meaningfull SQL and execute it
That should give you the result set.
By choosing View->Editor Layout->Two Rows and moving the results tab into the lower part of the SQL tab, both can be displayed on one page. Of course, code completion also works.

Of course, all of this works also if you install VSCodium on your host OS. On Windows, just download and run the installer. The required .dotnet libraries are already part of the OS.

So much for the first steps with VSCodium and the Oracle extension. More information can be found on the official Oracle page.