First thoughts about Oracle 18XE db

On the 20th october the new free Oracle database version 18XE (Express Edition) was anounced (https://blogs.oracle.com/oracle-database/oracle-database-18c-xe-now-available).

I had a first quick look at the database but mostly at the documentation. And here are my thoughts about it.

Restrictions

• Up to 12 GB of user data (previously 11 GB)
• Up to 2 GB of database RAM (previously 1 GB) – that is SGA + PGA combined.
• Up to 2 CPU threads (previously 1 CPU – but multiple threads?!)
• Up to 3 Pluggable Databases (this is new)

Not much of an improvement.

Previously there was a restriction of only 1 XE per computer (not per VM). The license guide does not mention this restriction anymore. I think it means that we can have multiple XEs on the same machine, but they need to be in different VMs. In the area of cloud based installations, we might not even know if another XE is on the same server. So this is very good news.

missing and included features

The following list does not claim to be complete. But those are features that I find interesting to consider.

✓ multi tenant (up to 3 PDBs)
− no plug / unplug into all other databases
✓ partitioning
− no sharding
✓ in-memory column store
✓ compression
− no active session history (ASH) and other performance related diagnostics
✓ spatial & graph support
✓ encryption and redaction
✓ Database Vault, FGA
− RAC

 

In general everything that works is allowed! This is different from 11gXE!

 

Thoughts

Server/VM limitations

11g XE was limited to one installation per machine. This was specified in the 11gXE license agreement.

Any use of the Oracle Database Express Edition is subject to the following limitations;
1. Express Edition is limited to a single instance on any server;
…

18cXE is limited to one installation per VM (my interpretation).

The restriction that was previously in the license guide is gone, however the install guide for linux, still has this passage

4.2 Oracle Database XE Installation and Execution Restrictions

Only one installation of Oracle Database XE can be performed on a single environment. …
To run more than one Oracle Database instance or install more than one copy of the database software, upgrade to Oracle Database 18c Personal Edition, Oracle Database 18c Standard Edition 2, or Oracle Database 18c Enterprise Edition.

The word server was replaced by environment. I interpret that so, that on a single server with multiple environments (e.g. VM images) we can have multiple XE installations.

The other restricting factor is that the oracle SID is fixed to XE: I first thought it is hardcoded, but I seen one trustworthy report, where the claim was made that this can be changed. But even if that is possible, it is not clear whether that is allowed.

This might be problematic for upgrade scenarios (from 18XE to 19XE). We can not have two active DBs one old 18XE and the new 19XE on the same environment. Maybe not even if one is shut down (because of the only one copy of the db software issue).

There is a section in the installation guide, that seems to support this impression: Exporting and Importing Data for non-APEX Users. It describes an upgrade scenario via datapump. It specifically mentions to deinstall 11cXE.

2. Deinstall Oracle Database XE 11.2 if installation of Oracle Database XE 18c is planned on the same system. See Deinstalling Oracle Database XE for more information

There certainly is some clarification with regards to possible upgrade scenarios needed in the future. For now, only one XE installation on a single virtual box/machine.

Ressource restrictions

The 2 CPU threads might be one of the more limiting factors. It feels like a downgrade from the previous version. With hyperthreading a CPU could support easily up to 4 or even 8 threads. This is now limited to two threads. Oracle tries to make sure that we can not trick the limitations by useing advanced hardware.

This reminds my of the restriction introduced when switching to SE2 (standard edition 2). SE2 is limited to 16 (user) threads. Which essentially means, we should disable hyperthreading and just use normal CPU power.

Especially for web based applications the thread limit seems severe. In APEX we typcially have connection pools that serve up to 10 concurrent connections. Sometimes multiple connections pools (apex, apex_al, apex_pu, apex_rt). We need to rethink the default apex and ords settings. A thread on a CPU usually does not resemble a user process 1:1 but it can serve as a crude first estimation.

The 12 GB user data means we can not store many document files in the database. For most well structured data this should be reasonably enough. As long as no video, audio or very large photo collections are used. This still leaves the option to store documents as BFILE directly in the file system. This would avoid the data being used for the limited user table space. I’m not sure if temp and undo space is included in that limit. Main question seems to be, what exactly is measured.

Also note that system tablespace is not included in that 12 GB limit.  Unfortunatly I can’t find the source anymore where I read about this.

This query might help measureing how much space is used.

select nvl(tablespace_name,' - Total - ') tablespace_name,
round(sum(bytes)/1024/1024/1024,1) as size_in_GB
from dba_segments
where tablespace_name not in ('SYSAUX','SYSTEM')
group by rollup(tablespace_name)
order by size_in_GB desc;

I think that restriction is quite ok. Although the improvement from 11gXE is only marginal.

In-memory

Since we are only able to use 2 GB RAM in-memory column storage is not really useful. It can be nice to test out this feature or maybe use it on very tiny databases.
It might also be useful in combination with partitioning. I’m not sure if that is possible, but if we load only the relevant (e.g. current) partitions into memory we might be able to stay within the 2GB limit even when data sizes grow.

More of a marketing stunt, that in-memory option is included.

Compression

There is basic and advanced compression. Could be highly useful, especially to save up on disk space. However if you ever plan to upgrade to a Standard Edition, don’t depend on compression. This also is an EE feature.

On the other hand compression might be used to lower the pressure on the 12 GB user data limit. Using advanced compression it should be easily possible to double the size of the user data without reaching the limit.

APEX

APEX is not preinstalled. This is in line with the current 18c db situation. In 11g/12cR1 APEX was preinstalled in the CDB, which was generally considered bad practice. It was changed with 18c (i believe) and XE behaves the same way. Still it would be nice to have an option during install that gives us ORDS and APEX in a PDB.

In general APEX (and ORDS) update their versions more frequently than the database,
This might change with the new release cycles of the database however. 19XE is supposed to come out with the 19.3 db version.

upgrade thoughts

We can not directly upgrade from 11gXE to 18cXE. Nor will we be able to upgrade from 18cXE to 19XE. The way to upgrade is to export the data and import it into the new DB.

multitenant

We got 3 PDBs to use. This is very nice. Remember SE2 (standard edition 2) only allows one plugged PDB (+the seed PDB). I still would only put one PDB into major use. But it might become much easier to clone a PDB to try out a few things there, without the need to shut the original PDB down.

Unfortunatly it is not possible to plug the XE PDB into a SE2 database. It is possible to plug it into an EE database or even into a Oracle Cloud Db. I believe the SE restriction has to do with the problem that several EE features are available in the XE, which are not available in SE/SE2.

database links

So far I do not know if it is is possible to create a DB link on an XE. I totally expect that it will work. The whole CDB/PDB concept uses DB links.

backup

It is now possible to use RMAN to backup a XE. Recommended!

Tuning and diagnostic tools

At first I heared that ASH and AWR reports will not be possible in XE. But in this thread from July 2018 Gerald Venzl explained otherwise

Will it be legal to use all these options like DIAGNOSTIC+TUNING, ADDM, AWR, ASH in production systems?

–> Yes

Meanwhile Gerald clarified that the feature did not make it into 18XE. It is now planned for 19XE.

The license guide has a detailed chapter showing which options are available and which not.

It would be great to have it. Performance troubleshooting is a topic on any production system. I don’t expect XE to be an exception.

 

production ready and other usages

Can we use it for production environments?

From a pure license standpoint it is ok. See the quote from the faq

Can I use Oracle 18c XE in production?

Oracle Database Express Edition does not restrict in which environment it can be deployed. However, Oracle Database Express Edition is not supported and does not receive any patches, including security patches. Oracle recommends to run production deployments on fully supported Oracle Database editions or Cloud Services.

So – yes we can! But should we use it in production? Yes and no. For non-critical applications (like if you want to manage your local club data via a XE + apex) that is certainly fine. Although the cloud based alternatives are interesting.  However we will not receive any security fixes and don’t have oracle support (we are not paying for it, so that is fair). This might be a risk. And that’s why I wouldn’t recommend XE for mission critical stuff.

The other thing people already speculated, is to use an XE for testing purposes. If it is feature testing, then this we can already do with an EE (enterprise edition) using the OTN Developer  license. Just recently the oracle prebuild developer-vm was upgraded to 18c. If you just want the database then you can use a vagrant-box to to provision the installation: Oracle DB 18.3 . Since XE will not receive any patches, we will not be able to test the newest version of a feature on it. So for pure testing purposes it would be a bad choice.

It might be an exellent tool to demonstrate the capability of an oracle database to critical customers however. If you are an ISV you could even use it to give your potential customer a trial version of your software running on an XE.

Conclusion

I’m very excited that 18cXE finally made it. I’m also happy that a lot of EE features are available. For demos, talks and presentations I will probably stay with the preconfigured Developer-VM and an EE version of the database. For long-running small side projects, I consider to setup one or two XEs. Additionally it is a great counter argument for all those “Oracle DB is (too) expensive” opinions.

useful links

XE Quick Start: https://www.oracle.com/database/technologies/appdev/xe/quickstart.html

XE licensing information: https://docs.oracle.com/en/database/oracle/oracle-database/18/xelic/licensing-information.html#GUID-0F2574A6-360F-4237-8098-17B02FFC3BB3

XE Forum: https://community.oracle.com/community/groundbreakers/database/developer-tools/oracle_database_express_edition_xe

Oracle Vagrant-Boxes: https://blogs.oracle.com/developers/announcing-the-oracle-vagrant-boxes-github-repository

Jonathan Lewis about compression: https://www.red-gate.com/simple-talk/sql/oracle/compression-oracle-basic-table-compression/

dbms_scheduler 12c/18c – run EXTERNAL_SCRIPT

Introduction

With 12c we have several new job types for our scheduler jobs. One of them is EXTERNAL_SCRIPT. The other new job types are SQL_SCRIPT and BACKUP_SCRIPT.

From Oracle 12.2 plsql packages and type reference

‘EXTERNAL_SCRIPT’

This specifies that the job is an external script that uses the command shell of the computer running the job. For Windows this is cmd.exe and for UNIX based systems the sh shell, unless a different interpreter is specified by prefixing the first line of the script with #!.

In the past we could run an external script using the EXECUTABLE job type. This type is still available.

‘EXECUTABLE’

This specifies that the job is going to be run outside the database using an external executable. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

In general both options could do the same thing. Execute something on the host OS.
So why should we change anything? Is there a difference?

Let’s find out.

schedule an EXTERNAL_SCRIPT

setup credentials first

We can create credentials using a dbms package or via SQL Developer.

dbms_credential.create_credential(credential_name   => 'ORACLE_OS_CREDS',
                                  username          =>  'oracle',
                                  password          =>  'oracle',
                                  comments          => 'run scripts using oracle OS account');

In the developer VM box, the password is always oracle. That’s why I included it here. You need to use your own correct password.

For real world environments I suggest to create a specific OS account that is only allowed to execute the script and to do anything that needs to be done for this specific task but not more. This account might need the “Log On As Batch Job” Right under windows (support note #2065024.1).

For demonstration purposes I stay here with the oracle credentials.

setup a scheduled job to run a linux script

Of cause this works under windows too, but I did test it only using Oracle Linux.

Here I setup 4 slightly different examples how to run an EXTERNAL_SCRIPT job. After that we check and compare the output.

These are our for slightly different test scenarios.

1. run a simple bash script.
2. run a script, that has an error
3. run a script with an error, but an exitcode=0
4. same as 3. and use undocumented FAIL_ON_SCRIPT_ERROR argument

To test what happens if the script itself has an error, I added a change directory command pointing to a non existent directory.
This command will result in an error.

cd /abcd/efgh/ijk

Here is the command to schedule the 4 jobs. Each job has a slightly different name. The differences between one and the previous job are marked. (sorry new WP editor messed up syntax highlighting for code)

-- First test a script that does not produce an error

declare
  v_jobname     varchar2(200);
  v_good_script clob;
begin
   v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_NO_ERROR');

  -- the following line breaks are important.
  -- Do NOT remove them, they are part of the linux script.
   v_good_script := 
$IF DBMS_DB_VERSION.VER_LE_12_2 $THEN
   '#!/bin/bash
'||
$END
'echo "Job ok!"';

   dbms_scheduler.create_job(job_name   => v_jobname,
                              job_type   => 'EXTERNAL_SCRIPT',
                              job_action => v_good_script,
                              credential_name => 'ORACLE_OS_CREDS',
                              enabled    => false,
                              auto_drop  => false
                              );
   -- run the job
   dbms_scheduler.enable(v_jobname);         

end;
/


PL/SQL procedure successfully completed.

-- Now test a script that does produce an error


declare
  v_jobname    varchar2(200);
  v_bad_script clob;
begin
   v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR');

   v_bad_script :=
$IF DBMS_DB_VERSION.VER_LE_12_2 $THEN
'#!/bin/bash
'||
$END
'cd /abcd/efgh/ijk';

   dbms_scheduler.create_job(job_name   => v_jobname,
                              job_type   => 'EXTERNAL_SCRIPT',
                              job_action => v_bad_script,
                              credential_name => 'ORACLE_OS_CREDS',
                              enabled    => false,
                              auto_drop  => false
                              );
   dbms_scheduler.enable(v_jobname);
end;
/

PL/SQL procedure successfully completed.

-- Now test a script that does produce an error but uses exit 0

declare
  v_jobname    varchar2(200);
  v_bad_script clob;
begin
   v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_EXIT0');

 v_bad_script := $IF DBMS_DB_VERSION.VER_LE_12_2 $THEN '#!/bin/bash '|| $END
'cd /abcd/efgh/ijk
exit 0';

   dbms_scheduler.create_job(job_name   => v_jobname,
                              job_type   => 'EXTERNAL_SCRIPT',
                              job_action => v_bad_script,
                              credential_name => 'ORACLE_OS_CREDS',
                              enabled    => false,
                              auto_drop  => false
                              );

   dbms_scheduler.enable(v_jobname);
end;
/

PL/SQL procedure successfully completed.

-- run script using attribute FAIL_ON_ERROR
declare
  v_jobname    varchar2(200);
  v_bad_script clob;
begin
   v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_FAILONERROR');

 v_bad_script := $IF DBMS_DB_VERSION.VER_LE_12_2 $THEN '#!/bin/bash '|| $END
'cd /abcd/efgh/ijk
exit 0';

   dbms_scheduler.create_job(job_name   => v_jobname,
                              job_type   => 'EXTERNAL_SCRIPT',
                              job_action => v_bad_script,
                              credential_name => 'ORACLE_OS_CREDS',
                              enabled    => false,
                              auto_drop  => false
                              );
  -- Make sure script errors result in a job error and are noticed.
   dbms_scheduler.set_attribute(
            name => v_jobname,
            attribute => 'FAIL_ON_SCRIPT_ERROR',
            value => true);                              

   -- run the job
   dbms_scheduler.enable(v_jobname);
end;
/
PL/SQL procedure successfully completed.

Note that all anonymous blocks executed successfully. This only means that we could create and start a scheduled job. It does not tell us, what the result of the job was.

To find the job result, we need to check the data dictionary view all_scheduler_job_run_details. The information is in there but only AFTER the job finished.

Results

The data dictionary gives us information about the result of the scheduler runs in the view ALL_SCHEDULER_JOB_RUN_DETAILS.

select replace(job_name,'DEMO_SCHEDULED_EXTERNAL_SCRIPT') as job_name,
       log_date, status, output, error#, errors, additional_info
from all_Scheduler_job_run_details
where job_name like 'DEMO_SCHEDULED_EXTERNAL_SCRIPT%'
--and log_date >= sysdate - interval '5' minute
order by log_date desc
;

For presentation purposes I flipped rows and columns. So the next picture shows the columns from the DD view as rows.

Let’s go through the results step by step.

The first testcase did not have an error. status of the run = SUCCEEDED. The output column also shows the stdev output which is nice. So there is no need to spool the output into an extra file, just to be able to see later what happend. The same column is also used for jobs of type SQL_SCRIPT to return dbms_output.

The second testcase had an error. The job status correctly reported that the script errored with exit code = 1 (column error#). And we see what kind of error happen in the errors column.

"/tmp/job_2078996_3568888_script: line 2: cd: /abcd/efgh/ijk: No such file or directory
"

We also see that the script itself was created as a file in the /tmp folder using job and log id for the file name “job_2078996_3568888_script”.
Also note that the additional_info column says

"EXTERNAL_LOG_ID="job_2078995_3568886",
ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted
"

This is slightly misleading, since the job_type was EXTERNAL_SCRIPT and not EXECUTABLE. And “Operation not permitted” could lead us suspecting some issue with privs (might be the case when “cd” doesn’t work) or with the credentials (definitly not the case here).

The third testcase had an error in the script, however it finished with exit 0 (=success). It makes sense that in this case the job run status is also marked as SUCCEEDED. However the error “no such file or directory” still can be found in the errors column.

The forth testcase uses a new feature. I’m not sure if that is already there in the 12.1 db version, all my tests were done under 12.2. The all_scheduler_jobs view has a new column FAIL_ON_SCRIPT_ERROR. It defaults to FALSE. We can set this as an attribute for the scheduled job.

   dbms_scheduler.set_attribute(
            name => v_jobname,
            attribute => 'FAIL_ON_SCRIPT_ERROR',
            value => true);

This is currently undocumented, but it works, as above demo shows. I believe it is a documentation bug. The new column in the view is documented, but FAIL_ON_SCRIPT_ERROR is not in the list of allowed attributes.

The result is, that even with EXIT=0, the job status goes to FAILED. We also see a different error number 27382 instead of 1. 27382 seems to be the ORA-Error number that we also see in the additional_details column. This column now says “ORA-27382: job type EXTERNAL_SCRIPT has errors in the job action”. Not a major difference, but slightly better additional_details when we use this attribute.

Quite some interesting behaviour. So if we want to run a script that always exits with 0, we can still make our job go to FAILED if there are any errors inside that script.

I would guess that we find the same behaviour for the other new job_types SQL_SCRIPT and BACKUP_SCRIPT. I didn’t test it yet.

Comparison between EXTERNAL_SCRIPT and EXECUTABLE

Executable is the old way.

It requires to configure and start an external agent. Often this is a fairly complex task. It involves configuration of the listener, tnsnames.ora, extproc.ora, externaljob.ora and some other parts. Those are usually not under control of a developer. Additionally all external jobs that are executed using this agent run with the same OS privileges. Using credentials gives us a little more control.

Starting from 12.1 such external jobs can alternativly run with credentials. Same as I already showed for running external_scripts. So the credentials argument is only a half-baked one.

It is also difficult to track down an error in case something goes wrong. This is where EXTERNAL_SCRIPT seem much better then EXECUTABLE. I have to admit I did only some very short tests about this, but I never found a disadvantage for running a scheduled job as EXTERNAL_SCRIPT instead of EXECUTABLE.

So if you have a choice go with the newer option.

Integrate it into plsql including status check

How to setup a plsql procedure that is able to run an external_script, but also returns an exception, in case the script runs into errors?

Lets assume the external script is something like this:

# set environment
export PATH=/usr/local/bin:$PATH
export ORACLE_SID=XE
export ORAENV_ASK=NO
. /usr/local/bin/oraenv

CLASSPATH=$CLASSPATH:$APEX_HOME/utilities
CLASSPATH=$CLASSPATH:.
export CLASSPATH

cd /opt/jasper/report1

java -jar runJasperReport.jar
exit

So this executes some jar file. I don’t want to wait until the java logic is finished, but I want to be informed if something basic goes wrong – like if the jar file couldn’t be found.

Here is an example that I used in some APEX application. After the job is started, it hangs around for a couple of seconds and checks if anything surprising did happen. The code to start the job itself is not included. But you can assume that it is done in the same packaged procedure. The script is in the v_script variable.

Do not copy it 1:1, but understand it and adapt it to your needs.

...
    v_jobname             varchar2(128);
    r_job_details         all_scheduler_job_run_details%rowtype;
    r_job                 all_scheduler_jobs%rowtype;
    c_max_check_job_tries constant binary_integer :=3;
    v_message             varchar2(1000);
    v_script              clob;
begin
...
   -- check if the scheduled job did sucessfully start.
   -- In case some error happend after starting the job, we might not notice that, since it was started in the background.
   -- e.G. ORA-27369: Job of Typ EXECUTABLE not successfull with Exit-Code: No such file or directory
   -- In such a case check scheduler data dictionary almost immediatly after job creation.
   apex_util.pause(p_seconds => 0.5);
   
   for i in 1..c_max_check_job_tries loop
      -- constant is set to 3, so max. wait time = 3.5 seconds.

        -- read info about scheduled job
        -- started/running jobs are in scheduler_jobs, finished jobs including results are in scheduler_job_run_details
        begin
          select * into r_job from all_scheduler_jobs where job_name = upper(v_jobname);
          logger.trace(p_message=>'Job "'||v_jobname||'" created with state='||r_job.state );
       exception
         when no_data_found then
           -- Job might have stopped already, check run details!
           r_job.state := 'NOT FOUND';
       end;    

       if r_job.state in ('RUNNING','SCHEDULED') then
         -- RUNNING+SCHEDULED => Looks ok, Job runs,just to make sure wait for another few seconds
         apex_util.pause(p_seconds =>  1);

       else
         begin
           -- for any other state check details
           select * into r_job_details from all_scheduler_job_run_details where job_name = upper(v_jobname);
           logger.trace(p_message=>'Job Details "'||v_jobname||'" with status='||r_job_details.status );
           if r_job_details.status= 'FAILED' or r_job.state= 'FAILED'  then
                v_message := 'Job "'|| v_jobname||'" with Error!';
                if r_job_details.additional_info like '%ORA-27369%' or r_job_details.additional_info like '%ORA-27382%' then
                  v_message := v_message ||' Jobaction='||v_script;
                end if;
                pk_logging.pr_logError(p_message=>v_message);
                apex_error.add_error(   p_message => v_message,
                                        p_additional_info => r_job_details.additional_info,
                                        p_display_location => apex_error.c_inline_in_notification --apex_error.c_on_error_page
                                        );
                -- step out of loop and raise an error using the OS error message
                Raise_application_error(-20001, r_job_details.errors);
           end if;
         exception
           when no_data_found then
             -- Job not started yet or just about to finish...
             -- consider to wait a few sec first. Then raise an error if job still not there.
             -- last try?
             if i=c_max_check_job_tries then
                logger.error(p_message=>'Job "'||v_jobname||'" was not started!');
                Raise_application_error(-20001, 'Warning! Job "'|| v_jobname||'" wasn't started (yet)! Check application log!');
             else
                -- wait 1 second until job is hopefully created
                apex_util.pause(p_seconds =>  1);
             end if;
         end;
       end if;
   end loop get_job_info;
   if r_job.state = 'SCHEDULED' then
      -- still scheduled? inform user
      Raise_application_error(-20001, 'Warning! Job "'|| v_jobname||'" needs longer than expected to start. Please monitor closely and informa administrator!');
   end if;
...

18c issue: ORA-27369: job of type EXECUTABLE failed with exit code: No child processes

In 18c my external scripts encountered an error

10 Command not found

EXTERNAL_LOG_ID=”job_xxx”,
ORA-27369: job of type EXECUTABLE failed with exit code: No child processes

Something did change.

I found out that beginning with 18c we must not set the shell at the start of the script. Simply remove the line ‘#!/bin/bash’ . I changed the test code above and added conditional compiling, so that the tests scripts should be running in all db versions.

Security considerations

Running external jobs is always something where we need to take extra care – so that we do not put holes into our security defense system.

I carefully watch out for two major security risks:
1) If we add something dynamically to the script that we are executing, like an extra parameter, make sure to sanitize all the inputs. Otherwise we could get some kind of injection problem.

2) The agent/account that runs our script should only get the least needed privileges. So it shoud NOT be running under user oracle, like I did in the demo.
Create a separate account for that. Name the account to something that logically points to the task that it is supposed to do.

conclusion

The new job_type EXECUTE_SCRIPT is useful.

Some of the hurdles that developers face when trying to run a host command are lowered. The script itself does not need to be deployed on the database server.

Reacting to errors in the script is possible, but we need to check the correct columns and use the proper settings.

cleanup demo jobs

-- cleanup
-- remove all the jobs
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_NO_ERROR');
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR');
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_EXIT0');
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_FAILONERROR');

working with editions – part 2

This is the second post about working with the edition based redefinition feature of the oracle database. The first post was about useful commands that help to set up editions inside an oracle database and enable schemas to use editioned objects.

This post will concentrate on different methods for connecting and setting a session to a specific edition.
It assumes that we already have three editions in our database :

ORA$BASE->DEV$BETA->DEV$ALPHA

connect using SQL*PLUS directly

1. connect to the default edition
   Without any additional settings, the connect goes to the specified default edition of the database. E.g. ORA$BASE

   SQL> connect user/pwd@database 
   Connect done.

2. Use environment Variable ORA_EDITION
   https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm
   windows:

   set ORA_EDITION=DEV$BETA

   linux:

   export ORA_EDITION=DEV\$BETA

3. CONNECT with specific edition
   https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve015.htm#i2697450

   SQL> connect user/pwd@database edition=DEV$ALPHA
   Connect done.

so setting the edition works with SQL*plus.
Unfortunatly only a minority of tools support editions during the connect.

connect using TNSNAMES + DB Services

This will work with all tools including connections from applications servers, e.g. Apex/ORDS.

We can create different tnsnames entries that connect to a different db service name.

The db service can be configured to connect to a defined edition.

extra DB Service for different editions

1. using servercontrol (important when Oracle Restart is used)

   srvctl add service -d db_unique_name -s service_name -t edition_name

2. using plsql package to install DB services (starting from 11.2.0.2)

   begin
   DBMS_SERVICE.CREATE_SERVICE(
   service_name => 'DEV_BETA',
   network_name => 'DEV_BETA',
   edition => 'DEV$BETA');
   end;
   /
   
   begin
   DBMS_SERVICE.CREATE_SERVICE(
   service_name => 'DEV_ALPHA',
   network_name => 'DEV_ALPHA',
   edition => 'DEV$ALPHA');
   end;
   /
   
   begin
   DBMS_SERVICE.CREATE_SERVICE(
   service_name => 'DEV_BASE',
   network_name => 'DEV_BASE',
   edition => 'ORA$BASE');
   end;
   /
   
   commit;
   
   select * from dba_services;
   

The name of the DB service is then used inside TNSNAMES.ORA for the service_name parameter (do not use SID!).

connect using JDBC

It is possible to set the edition for connections made via JDBC:OCI. To use that the OracleConnection class extention needs to be used.

Properties prop = new Properties();
prop.setProperty(OracleConnection.CONNECTION_PROPERTY_EDITION_NAME, "DEV$BETA");  

check the configuration (services and other relevant parameters)

select * from v$parameter
where regexp_like(name, 'edition|service|global|processes|sessions|dispatch|shared|listen')
;