Run Oracle Database Express Edition using Podman on an Oracle Cloud compute instance

In this article, I will demonstrate how to run Oracle Database Express Edition using Podman on an Oracle Cloud compute instance.

On Oracle Cloud Console

1. create instance, name instance-21c-xe
2. change image to Oracle Linux 8.0
3. New VCN, New Public Subnet, Assign Public IPv4 address

It is not necessary to use a paid instance. The free instance will work as well.

Wait for the instance to be ready. Go to Security List, add Ingress Rule for port 1521

ssh into the new instance

sudo bash
ln -sf /usr/share/zoneinfo/Canada/Eastern /etc/localtime
dnf -y install podman podman-docker buildah skopeo dnf-utils zip unzip tar gzip git sqlcl
dnf -y update
podman pull docker.io/gvenzl/oracle-xe:21-full
podman run -d -p 1521:1521 -e ORACLE_PASSWORD=SysPassword1 --name 21cFull -v oracle-volume:/opt/oracle/XE21CFULL/oradata gvenzl/oracle-xe:21-full
podman ps

Give it 2 minutes to startup

podman logs 21cFull

You should find this in the output
#########################
DATABASE IS READY TO USE!
#########################

Testing via SQLcl

sql system/SysPassword1@//localhost/XEPDB1

Setup archivelog mode via opening a session into the container

podman exec -it --user=oracle 21cFull bash
sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Setup autostart on reboot

podman generate systemd --name 21cFull > /lib/systemd/system/container-21cFull.service
systemctl enable container-21cFull

All set. Reboot, wait for a few minutes and check again

Oracle SQL Explain Plan

Display the execution plan stored in the plan table. We are not executing the actual SQL.

EXPLAIN PLAN
    FOR SELECT /*+ gather_plan_statistics */
               COUNT (*) FROM dba_objects;

SELECT * FROM TABLE (DBMS_XPLAN.display (format => 'TYPICAL'));

Displays the actual execution plan used to run a query stored in the cursor cache. We have to execute the SQL first.

SELECT /*+ gather_plan_statistics */
       COUNT (*) FROM dba_objects;

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format => 'ALLSTATS LAST +cost +bytes +peeked_binds'));

Apply Oracle Database Oct 2020 CPU Patch

Microsoft Windows BP 19.9.0.0.20 (& associated OJVM)

Download these files from Oracle Support

1. p6880880_190000_MSWIN-x86-64.zip - OPatch

2. p32172777_199000DBRU_Generic.zip - Patch 32172777: DATAPATCH SHOULD SKIP RUNNING STANDARD.SQL
3. p31719903_190000_MSWIN-x86-64.zip - Patch 31719903: WINDOWS DATABASE BUNDLE PATCH 19.9.0.0.201020
4. p31668882_190000_MSWIN-x86-64.zip - Patch 31668882: OJVM RELEASE UPDATE 19.9.0.0.0

Update OPatch

Open command window as Administrator

rename %ORACLE_HOME%\OPatch to %ORACLE_HOME%\OPatch_20210205
unzip p6880880_190000_MSWIN-x86-64.zip to %ORACLE_HOME%\OPatch

Apply Patch 31719903: WINDOWS DATABASE BUNDLE PATCH 19.9.0.0.201020

Take Windows Services screenshot, shutdown all Oracle services, change them to MANUAL startup and reboot

unzip p31719903_190000_MSWIN-x86-64.zip
cd 31719903
%ORACLE_HOME%\OPatch\opatch apply

Apply Patch 32172777: DATAPATCH SHOULD SKIP RUNNING STANDARD.SQL

unzip p32172777_199000DBRU_Generic.zip
cd 32172777
rename %ORACLE_HOME%\sqlpatch\sqlpatch.pm to sqlpatch_20210205.pm
copy files\sqlpatch\sqlpatch.pm to %ORACLE_HOME%\sqlpatch\

Start listener, start Oracle database service

sqlplus / as sysdba
startup
alter pluggable database all open;
exit

cd /D %ORACLE_HOME%\OPatch
datapatch -verbose

Enabling new optimizer fixes

SELECT * FROM V$SYSTEM_FIX_CONTROL;

EXECUTE dbms_optim_bundle.getBugsforBundle;

EXECUTE dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES');

CREATE PFILE FROM SPFILE;

Apply Patch 31668882: OJVM RELEASE UPDATE 19.9.0.0.0

Reboot

unzip p31668882_190000_MSWIN-x86-64.zip
cd 31668882 
%ORACLE_HOME%\OPatch\opatch prereq CheckConflictAgainstOHWithDetail -ph .
%ORACLE_HOME%\OPatch\opatch apply

Start listener, start Oracle database service

sqlplus / as sysdba
shutdown
startup upgrade
alter pluggable database all open upgrade
exit

cd /D %ORACLE_HOME%\OPatch
datapatch -verbose

Base on the screenshot taken earlier, put back the original startup setting

sqlplus / as sysdba
shutdown
exit

reboot

Re-compile all database objects

sqlplus / as sysdba
exec utl_recomp.recomp_parallel;

Oracle RAC status check

crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.net1.network
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.ons
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.proxy_advm
               OFFLINE OFFLINE      rac1                     STABLE
               OFFLINE OFFLINE      rac2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       rac1                     192.168.50.10,STABLE
ora.asm
      1        ONLINE  ONLINE       rac1                     Started,STABLE
      2        ONLINE  ONLINE       rac2                     Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       rac1                     STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       rac1                     Open,STABLE
ora.orcl.db
      1        ONLINE  ONLINE       rac1                     Open,HOME=C:\oracle\
                                                             product\12.2.0\dbhom
                                                             e_1,STABLE
      2        ONLINE  ONLINE       rac2                     Open,HOME=C:\oracle\
                                                             product\12.2.0\dbhom
                                                             e_1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------

srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: C:\oracle\product\12.2.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.298.976826851
Password file: +DATA/ORCL/PASSWORD/pwdorcl.282.976826257
Domain: leavemealone.local
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database instances: orcl1,orcl2
Configured nodes: rac1,rac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

srvctl config scan
SCAN name: RAC-scan, Network: 1
Subnet IPv4: 10.10.2.0/255.255.255.0/NIC1, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 10.10.2.111
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.10.2.110
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 3 IPv4 VIP: 10.10.2.109
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:

srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac1

cluvfy comp ocr -n all -verbose

Verifying OCR Integrity ...PASSED

Verification of OCR integrity was successful.

CVU operation performed:      OCR integrity
Date:                         May 30, 2018 11:54:26 PM
CVU home:                     C:\oracle_grid_home\bin\..\
User:                         leavemealone\scammisuli

ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       2124
         Available space (kbytes) :     407444
         ID                       : 2115914722
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

Oracle Scheduler Job Notification

Initial system setup

BEGIN
DBMS_SCHEDULER.create_credential (credential_name   => 'MAILSERVER_CREDENTIAL',
username          => 'uuuuuuuu',
password          => 'xxxxxxxx');
END;

BEGIN
DBMS_SCHEDULER.set_scheduler_attribute ('email_server', 'vtordc01.leavemealone.local:25');
DBMS_SCHEDULER.set_scheduler_attribute ('email_sender', 'service@leavemealone.ca');
DBMS_SCHEDULER.set_scheduler_attribute ('email_server_credential', 'MAILSERVER_CREDENTIAL');
END;

Job notification setup

BEGIN
DBMS_SCHEDULER.add_job_email_notification (job_name     => 'ADHOC_04',
recipients   => 'prtg.alert@gmail.com',
events       => 'JOB_STARTED,JOB_SUCCEEDED,JOB_FAILED,JOB_STOPPED');
END;

Check the job notification setup

SELECT job_name,
recipient,
event,
filter_condition
FROM user_scheduler_notifications;

Oracle Transparent Application Failover (TAF)

TAF is based on OCI (Oracle Call Interface). So every client using OCI can/may profit from TAF.
JDBC Thick requires an Oracle Client to be installed, hence you have OCI and therefore TAF.
JDBC Thin does not require an Oracle Client to be installed (just the appropriate JAR files) and is not based on OCI, hence NO TAF.

You need to install the Oracle instant client first.

Below is a client-side TAF setup

tnsnames.ora

RACPROD =
  (DESCRIPTION=
    (FAILOVER = ON)
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=rac-scan.leavemealone.local)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=prod.leavemealone.local)
      (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))
    )
  )

SQL*Plus with TAF

sqlplus accdb@racprod

SQLcl with TAF

sqlcl accdb@jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prod.leavemealone.local)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))))

SQL Developer with TAF
Change connection type to Advanced and then put this in Custom JDBC URL

jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prod.leavemealone.local)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))))

Below is a server-side TAF setup

Add and review TAF service

srvctl add service -d orcl -s prodtaf.leavemealone.local -m BASIC -e SELECT -z 180 -w 5 -j LONG -r "ORCL1,ORCL2"
-pdb prod
srvctl start service -d orcl -s prodtaf.leavemealone.local
srvctl config service -d orcl -s prodtaf.leavemealone.local
Service name: prodtaf.leavemealone.local
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: prod
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:
CSS critical: no

tnsnames.ora

RACPROD =
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=rac-scan.leavemealone.local)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=prodtaf.leavemealone.local)
    )
  )

SQL*Plus with TAF

sqlplus accdb@racprod

SQLcl with TAF

sqlcl accdb@jdbc:oracle:oci:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prodtaf.leavemealone.local)))

SQL Developer with TAF
Change connection type to Advanced and then put this in Custom JDBC URL

jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prodtaf.leavemealone.local)))

To verify the connection is actually using TAF

SELECT inst_id,
         machine,
         program,
         username,
         logon_time,
         failover_type,
         failover_method,
         failed_over
    FROM gv$session
ORDER BY logon_time DESC;

APEX Office Print plug-in upgrade script

Run the script connected to SQLcl as the owner (parsing schema) of the application.
We need to repeat this script for each application.

DECLARE
    l_workspace_id   NUMBER;
BEGIN
    SELECT workspace_id
      INTO l_workspace_id
      FROM apex_workspaces
     WHERE workspace = 'OPTRUST';

    --
    apex_application_install.set_workspace_id (l_workspace_id);
    apex_application_install.set_application_id (&1);
    apex_application_install.generate_offset;
    apex_application_install.set_schema ('OPTRUST');
END;
/

@dynamic_action_plugin_be_apexrnd_aop_convert_da.sql
@dynamic_action_plugin_be_apexrnd_aop_da.sql
@process_type_plugin_be_apexrnd_aop.sql
COMMIT;

Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE
acl_path     VARCHAR2 (4000);
v_hostname   VARCHAR2 (200)  := 'xxx.leavemealone.local';
v_user       VARCHAR2 (30)   := 'APEX_050100';
BEGIN
SELECT acl
INTO acl_path
FROM dba_network_acls
WHERE HOST = v_hostname AND lower_port IS NULL AND upper_port IS NULL;

IF dbms_network_acl_admin.check_privilege (acl_path, v_user, 'connect') IS NULL
THEN
dbms_network_acl_admin.add_privilege (acl_path, v_user, TRUE, 'connect');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dbms_network_acl_admin.create_acl ('send_mail.xml', 'send_mail ACL', v_user, TRUE, 'connect');
dbms_network_acl_admin.assign_acl ('send_mail.xml', v_hostname, 25, 25);
END;
/
COMMIT;

New way of adding ACL in Oracle 12c

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'xxx.leavemealone.local', 
    lower_port => 25,
    upper_port => 25,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'APEX_050100',
                              principal_type => xs_acl.ptype_db)); 
END;
/
COMMIT;

Check ACL and privileges under SYS

SELECT * FROM dba_network_acls;

SELECT * FROM dba_network_acl_privileges order by principal;

Add missing ACL after upgrading APEX

BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/power_users.xml',
'APEX_050100',
TRUE,
'connect');
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/send_mail.xml',
'APEX_050100',
TRUE,
'connect');
END;

Duplicate ACL for one schema. In this example, we use APEX_200200. Copy the creation script output and run it on the destination database.

SELECT    'BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace (
            HOST         => '''
       || dna.HOST
       || ''',
            lower_port   => '
       || dna.lower_port
       || ',
            upper_port   => '
       || dna.upper_port
       || ',
            ace          =>
                xs$ace_type (privilege_list   => xs$name_list (''connect''),
                             principal_name   => '''
       || dnap.principal
       || ''',
                             principal_type   => xs_acl.ptype_db)); END;
                             /' v_sql
  FROM dba_network_acls dna, dba_network_acl_privileges dnap
 WHERE dna.aclid = dnap.aclid AND dnap.principal = 'APEX_200200';

UNDO tablespace growing non-stop Part 2

This the original post and afterward we find out the culprit is the Flashback Data Archive (FDA).

It is a simple fix. We just need to bounce the FDA and within a few hours, oracle should release all the spaces.

BEGIN
  dbms_flashback_archive.disable_application(application_name => 'PARIS_AUDIT');
  dbms_flashback_archive.enable_application(application_name => 'PARIS_AUDIT');
END;
/

Oracle sql statement audit for an user

Enable Audit for an user

CREATE AUDIT POLICY penweb_api_audit_policy
ACTIONS DELETE, INSERT, UPDATE, SELECT
WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''PENWEB_API'''
EVALUATE PER SESSION;

AUDIT POLICY penweb_api_audit_policy;

SELECT object_schema,
object_name,
object_type,
audit_option,
condition_eval_opt,
audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'PENWEB_API_AUDIT_POLICY';

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Check the Audit trail

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

SELECT *
FROM   unified_audit_trail
WHERE  dbusername = 'PENWEB_API'
ORDER BY event_timestamp desc;

Remove Audit

NOAUDIT POLICY penweb_api_audit_policy;
DROP AUDIT POLICY penweb_api_audit_policy;

Oracle Statistics History

Check the current retention value.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

For alter this retention period to 90 days

execute dbms_stats.alter_stats_history_retention(90);

For check the old record present in database (upto you restore the stats)

select dbms_stats.get_stats_history_availability from dual;

Check out the statistics history

SELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner='OWNER_APP'
AND table_name='ORDERS'
ORDER BY stats_update_time;

Check out the differences

select * from table(dbms_stats.diff_table_stats_in_history(
ownname => 'OWNER_APP',
tabname => 'ORDERS',
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

Restore the previous statistics for the whole database

execute dbms_stats.restore_database_stats(sysdate-1);

Restore the previous statistics for a schema

execute dbms_stats.restore_schema_stats (ownname=>'OWNER_APP',AS_OF_TIMESTAMP=>sysdate-1);

Restore the previous statistics for a table

execute dbms_stats.restore_table_stats (ownname=>'OWNER_APP', tabname=>'ORDERS', AS_OF_TIMESTAMP=>sysdate-1);

Flush the shared pool to see the new execute plan after restore

alter system flush shared_pool

UNDO tablespace growing non-stop

Recently I found that the UNDO tablespace keep on growing, even after a database restart, it is still holding up the space. Creating a new UNDOTBS2 tablespace doesn't help either. 120GB "active" undo space never released.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
alter system set undo_tablespace=UNDOTBS2 scope=both;

Check the UNDO tablespace usage

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
291                              23.8125 UNEXPIRED UNDOTBS1                      
93                               378.375 UNEXPIRED UNDOTBS2                      
545                             750.9375 EXPIRED   UNDOTBS1                      
1349                           10969.625 EXPIRED   UNDOTBS2                      
1                                     64 ACTIVE    UNDOTBS2                      
18130                         121075.375 ACTIVE    UNDOTBS1                      

6 rows selected.

Checking which active transaction is holding up those spaces tells me there is none.

SELECT s.username,s.sid, t.xidusn, t.ubafil,
    t.ubablk, t.used_ublk
    FROM v$session s, v$transaction t
   WHERE s.saddr = t.ses_addr;

USERNAME           SID     XIDUSN     UBAFIL     UBABLK  USED_UBLK
----------- ---------- ---------- ---------- ---------- ----------
                    14        308          0          0          1

Rollback Segment indicated there are problem with some segments

SELECT segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS               TABLESPACE_NAME
------------------------------ -------------------- ------------------
SYSTEM                         ONLINE               SYSTEM
_SYSSMU1_3780397527$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU2_2232571081$           OFFLINE              UNDOTBS1
_SYSSMU3_2097677531$           OFFLINE              UNDOTBS1
_SYSSMU4_1152005954$           OFFLINE              UNDOTBS1
_SYSSMU5_1527469038$           OFFLINE              UNDOTBS1
_SYSSMU6_2443381498$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU7_3286610060$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU8_2012382730$           OFFLINE              UNDOTBS1
_SYSSMU9_1424341975$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU10_3550978943$          OFFLINE              UNDOTBS1
_SYSSMU11_3715213117$          ONLINE               UNDOTBS2
_SYSSMU12_3563522984$          ONLINE               UNDOTBS2
_SYSSMU13_1284218525$          ONLINE               UNDOTBS2
_SYSSMU14_1624181735$          ONLINE               UNDOTBS2
_SYSSMU15_1102482685$          ONLINE               UNDOTBS2
_SYSSMU16_1909532494$          ONLINE               UNDOTBS2
_SYSSMU17_3453924897$          ONLINE               UNDOTBS2
_SYSSMU18_4116382225$          ONLINE               UNDOTBS2
_SYSSMU19_606215510$           ONLINE               UNDOTBS2
_SYSSMU20_1963701883$          ONLINE               UNDOTBS2

21 rows selected.

SELECT
    LISTAGG(''''||segment_name||'''', ',') WITHIN GROUP(
        ORDER BY
            segment_name
    )
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

SELECT '"drop rollback segment "'||segment_name||'";'
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

Let's kill the problem segments

create pfile from spfile

Add this to the end of the pfile
paris._offline_rollback_segments=('_SYSSMU1_3780397527$','_SYSSMU6_2443381498$','_SYSSMU7_3286610060$','_SYSSMU9_1424341975$')

shutdown immediate;
startup pfile='E:\oracle\db_home\product\12.2.0\dbhome_1\database\INITPARIS.ORA'

drop rollback segment "_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU6_2443381498$";
drop rollback segment "_SYSSMU7_3286610060$";
drop rollback segment "_SYSSMU9_1424341975$";
drop tablespace UNDOTBS1 including contents and datafiles;

shutdown immediate;
startup;

Check the UNDO tablespace usage again

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
                162                  678 UNEXPIRED UNDOTBS2                      
                319               2840.5 EXPIRED   UNDOTBS2

Oracle Database 18c Express Edition (XE) RPM Installation On Amazon Oracle Linux 7 (OL7)

Create 3GB Linux swap

# dd if=/dev/zero of=/swapfile bs=1024 count=1048576
# mkswap /swapfile
# swapon /swapfile
# chmod 600 /swapfile
# swapon -s

Add Linux swap to /etc/fstab

/swapfile  none  swap  sw 0  0

Oracle database will be installed under /opt. So, make sure you have enough room under /opt

# yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm
# /etc/init.d/oracle-xe-18c configure

Test Database under non-root account

$ . oraenv
$ sqlplus sys/xxxxxx@//localhost:1521/XE as sysdba
$ sqlplus sys/xxxxxx@//localhost:1521/XEPDB1 as sysdba

Fix iptables
Allow port 80, 443 and 1521

# iptables -I INPUT -p tcp --dport 1521 -j ACCEPT
# iptables -t eth0 -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080
# iptables -t eth0 -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080
# iptables -t eth0 -I PREROUTING -p tcp --dport 443 -j REDIRECT --to-ports 8443
# iptables -t eth0 -I OUTPUT -p tcp --dport 443 -j REDIRECT --to-ports 8443
# service iptables save

Configure Oracle to startup on boot

# systemctl daemon-reload
# systemctl enable oracle-xe-18c
# reboot

Oracle user environment setup .bash_profile

export ORAENV_ASK=NO
export ORACLE_SID=XE
. oraenv

sqlplus / as sysdba

Apply Oracle Database Patch

Take screenshot, shutdown all oracle services and change them to MANUAL startup
reboot
open window command, run as administrator

List all the patches applied to database

E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch lspatches

Apply the patch

cd C:\Users\CyberArkDBA\Downloads\p28574555_122010_MSWIN-x86-64\28574555
E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch apply

Startup Oracle
cd %ORACLE_HOME%/OPatch
datapatch -verbose

Base on the screenshot, put back the original startup setting
reboot

Recompile all objects

exec utl_recomp.recomp_parallel;

select * from dba_objects where status = 'INVALID' order by 1,2;

Check patch history

select * from dba_registry_sqlpatch;

select dbms_java.get_jdk_version() from dual;

select dbms_qopatch.get_opatch_lsinventory() from dual;

Remove patch (if there is conflict)

E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch rollback -id 28135129
cd %ORACLE_HOME%/OPatch
datapatch -verbose -rollback 28135129

Oracle APEX Upgrade

Upgrade
I am using Oracle APEX Static Resources on Content Delivery Network. There is no need to copy the images directory to tomcat as /i/

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@apexins.sql APEX_DATA APEX_DATA TEMP https://static.oracle.com/cdn/apex/20.2.0.00.20/
@apex_rest_config.sql

Install French Language
goto this directory apex/builder/fr

set NLS_LANG=American_America.AL32UTF8
sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
ALTER SESSION SET CURRENT_SCHEMA = APEX_200200;
@load_fr.sql

Apply Patch
unzip p32006852_2020_GENERIC.zip, goto patch directory

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@catpatch.sql

Re-compile all objects

EXEC utl_recomp.recomp_parallel;
SELECT COUNT (*)
  FROM dba_objects
 WHERE status = 'INVALID'; 

O/S authenticated local login without password

Create User

CREATE USER "OPS$OPT_DOMAIN\CYBERARKDBA" IDENTIFIED EXTERNALLY AS 'null'    
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT CREATE SESSION TO "OPS$OPT_DOMAIN\CYBERARKDBA";

Now, you can login to oracle without password

sqlplus /

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 10 22:55:51 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
OPS$OPT_DOMAIN\CYBERARKDBA

Move audit information away from SYSAUX

Check current tablespace location

SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 

Create tablespace for audit

create tablespace AUDIT_DATA datafile size 1G autoextend on; 

Move to the new AUDIT_DATA tablespace

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
  audit_trail_location_value => 'AUDIT_DATA');
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
  audit_trail_location_value => 'AUDIT_DATA');
END;
/

Extract entire database DDL script for SVN

The best program I found is scheme2dll. It can generate ddl script per schema/object type/object name in a tree structure. This is perfect for SVN.

You can download the binary here.

This is the command I use to extract the ddl, schema_export.cmd

@echo off
REM SVN Revision Information: DO NOT REMOVE
REM $Revision: 37 $
REM $Author: chiup $
REM $Date: 2018-06-08 11:56:08 -0400 (Fri, 08 Jun 2018) $
REM
setlocal
java -jar scheme2ddl.jar -url dba_account/xxxxxxxxxx@localhost:1521:paris -p 8 -s ^
ACTUARY_BBS,^
APS,^
B2B,^
BAM,^
BBS,^
BFS,^
CHATS,^
DATAMANAGER,^
DCS,^
DEATH_RECALC,^
DISCOVERER5,^
DSGATEWAY,^
ENROL,^
GENERIC_TRD,^
IP,^
LTIP,^
MAINT,^
MBK,^
OCA,^
ODS,^
OPTFIN,^
OPTIT,^
ORABPEL,^
ORAOCA_PUBLIC,^
PAY,^
PBC,^
PCONV,^
PENWEB,^
PIN,^
PPI,^
PPS_OPSEU,^
PRISM_DBA,^
RMS_OPSEU,^
RULES_TRD,^
SES,^
TRD,^
UDDISYS,^
UNCL,^
WCRSYS,^
XFS,^
XPSPA ^
-o F:\oracle_ddl
endlocal

Installing Oracle XE 11g r2 on Amazon EC2

Add a disk via EC2 console and setup it up in linux

mkfs -t ext4 /dev/xvdb
mkdir /u01

Find out the uuid of the disk

ls -al /dev/disk/by-uuid/

Add the following line to /etc/fstab to mount the disk at boot time

UUID=82497d4a-9507-47db-b19c-c4473827ead6 /u01   ext4    defaults,nofail        0       2

Mount the disk and verify fstab syntax

mount -a

Add Swap space

dd if=/dev/zero of=/swapfile bs=1024 count=3072000
mkswap /swapfile
swapon /swapfile

Add the following line to /etc/fstab to enable swap at boot time

/swapfile swap swap defaults 0 0

Mount the swap and verify fstab syntax

mount -a

Add oracle group and user

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba,oinstall oracle
chown -R oracle:oinstall /u01
passwd oracle

Add the following lines to /etc/security/limits.conf

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   10240

Install Oracle

yum install glibc make binutils gcc libaio
rpm -i oracle-xe-11.2.0-0.5.x86_64.rpm
/etc/init.d/oracle-xe configure

Add the following lines to ~oracle/.bash_profile

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE

PATH=$PATH:$ORACLE_HOME/bin:$HOME/.local/bin:$HOME/bin
export PATH

RAC database commands

Database level

srvctl stop database -d orcl
srvctl start database -d orcl

PDB level

alter pluggable database PDB1 open  instances=all;
alter pluggable database PDB1 close  instances=all;

SELECT * FROM gv$pdbs;
SELECT * FROM dba_pdb_saved_states;