Oracle Database In-Memory Base Level Feature in Oracle 19c

In-Memory database technology is already introduced in Oracle database system, however in Oracle 19c things changed ! The feature is now free with limited size of  16 GB. You can now explore “Base-Level” feature starting with Oracle 19.8 version where JULY 2020 RU (Release Update) is applied.

The new Base Level feature supports all Database In-Memory features except for the following:

  • Automatic In-Memory  (AIM)
  • Compression levels other than MEMCOMPRESS FOR QUERY LOW
  • Excluded columns (all columns of a table are populated)
  • The CELLMEMORY feature on Exadata

To enable In-Memory “Base-level” feature:

go to cd $ORACLE_HOME/dbs and edit your init.ora file by adding the following two parameters and “restart” your database

INMEMORY_FORCE=BASE_LEVEL

INMEMORY_SIZE=7G

SQL> create table dba.in_mem as select * from dba_objects;

Table created.

SQL> explain plan SET STATEMENT_ID=’EMO1′ for select count(*) from dba.in_mem;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’EMO1′,’ALL’));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3285478656

———————————————————————

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

———————————————————————

| 0 | SELECT STATEMENT | | 1 | 115 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| IN_MEM | 25010 | 115 (0)| 00:00:01 |

———————————————————————

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT

——————————————————————————–

————————————————————-

1 – SEL$1

2 – SEL$1 / IN_MEM@SEL$1

Column Projection Information (identified by operation id):

———————————————————–

1 – (#keys=0) COUNT(*)[22]

2 – (rowset=1019)

21 rows selected.

*** — Now i will load the table to Memory:

SQL> ALTER TABLE dba.in_mem INMEMORY PRIORITY HIGH;

Table altered.

— to check objects uploaded in memory you can query dictionary view V$IM_SEGMENTS:

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from v$IM_SEGMENTS ;

OWNER SEGMENT_NAME SEGMENT_TYPE

——————————————————————————————————————————– ——————————————————————————————————————————– ——————

dba IN_MEM TABLE

SQL> explain plan SET STATEMENT_ID=’EMO2′ for select count(*) from dba.in_mem;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’EMO2′,’ALL’));

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

Plan hash value: 3285478656

——————————————————————————

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

——————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS INMEMORY FULL| IN_MEM | 25010 | 5 (0)| 00:00:01 |

——————————————————————————

Query Block Name / Object Alias (identified by operation id):

————————————————————-

1 – SEL$1

2 – SEL$1 / IN_MEM@SEL$1

Column Projection Information (identified by operation id):

———————————————————–

1 – (#keys=0) COUNT(*)[22]

2 – (rowset=1019)

21 rows selected.

 

as shown above the explain plan shows full scan against database table stored in-memory.

** resources/refrences:

https://blogs.oracle.com/in-memory/base-level-198-ru

https://docs.oracle.com/en/database/oracle/oracle-database/20/ftnew/database-memory-base-level.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/INMEMORY_FORCE.html#GUID-1CAEDEBC-AE38-428D-B07E-6718A7225548

https://www.oracle.com/a/tech/docs/twp-oracle-database-in-memory-19c.pdf

Oracle 19c Easy Connect Plus

In Oracle Database 19c a new feature is introduced called “Easy Connect Plus” that will enhance connection string syntax.

Easy Connect Plus will make it easier to use features such as TLS connections, wallets, load balancing, connection timeouts, and to tune network buffer sizes without external configuration.

The following is an example of the traditional easy connect syntax against a pluggable database:

SQL> connect emad/password@dbhost.example.com:1521/pdb1

Where port number is “optional” in the command syntax, in my example its 1521

The following is Easy Connect syntax structure that is available in Oracle Database drivers (JDBC,

ODP.Net, cx_Oracle, node-oracledb etc) that use Oracle Client 19c and connect to Oracle Database 11.2 or later.

 

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name][:

server_type][/instance_name]][?parameter_name=value{&parameter_name=

value}]

 

Protocol: transport protocol to be used while connecting to the database

host. In 19c, the supported values of protocol are TCP and TCPS. The default is TCP.

 

Hosts: The host list is a comma-separated list of host names or IP addresses which are used to connect to a database.

Ports: Port numbers indicate the ports that the database services are listening on.

When using multiple hosts, if the same port is used on each host, then you can use:

host1,host2:port Otherwise you can indicate different ports should be used:

host1:port1,host2:port2

Service Name:The service name is the service the database is known by. It is a name comprised of the database name and domain name, entered during installation or database creation.Service names can be found by running “lsnrctl services” on the database host.Note that database “system identifiers“ (SIDs) cannot be used in Easy Connect or Easy Connect Plus syntax.

 

Server Type: The server type specifies what kind of server is used on the database host to handle the connection. It can be one of DEDICATED, SHARED, or POOLED.

Instance Name: to connect to a specific instance that the service represents, you can use an instance name.

Parameters: Parameters are name-value pairs that control the behavior of connections. The syntax uses ‘?’ to indicate start of parameters and a ‘&’ delimiter between each parameter. Leading and trailing white spaces are ignored within parameter values. If whitespace is required as part of the value, it should be placed within double quotes. Parameters are described in the next section.

Examples of 19c Easy Connect Plus Syntax:

sqlplus c##emad/emad_123@tcp://oracle-19c-vagrant:1521/ORCLCDB

sqlplus c##emad/emad_123@[fe80::a00:27ff:fed0:4f43]:1521/ORCLCDB

sqlplus c##emad/emad_123@tcps://oracle-19c-vagrant:1521/ORCLCDB

 

Reference Link:

https://download.oracle.com/ocomdocs/global/Oracle-Net-19c-Easy-Connect-Plus.pdf

Oracle Database New Auto-Upgrade Tool Simulation

Oracle has released a new tool “Auto-Upgrade” to automate the process of upgrading Oracle Database Management System. This tool is much needed now, as the upgrade process is “lengthy” procedure ,and having such automation capability will shorten the period of upgrading any organization’s Large Oracle database infrastructure.

The tool will let you upgrade from 11g (11.2.0.4) as the “source” minimum version to either 12cR2, 18c , 19c releases as “target” version.

 

target versions

In this simulation of Oracle Auto-Upgrade tool, I am simulating the upgrade from 18c (18.6) to 19c (19.3) release.

Pre-requisite checks:

Check Java version under Oracle Home, it should be version 8. Java 8 is available in Oracle database homes for Oracle 12cR2 release and beyond. If your Oracle release is 11g (11.2.0.4) either install java 8 to your Oracle Home or use the “target” release java Oracle Home directory.

To check which java release you have , execute the following command:

$ORACLE_HOME/jdk/bin/java -version

java8

Since my “source” database is 18c , so its expected to have java 8 version available.

Preparations before upgrading the database:

I will install and setup Oracle 19c binaries within the same database server:

Will prepare the profile:

cd

cp .bashrc .bashrc_original

cp .bashrc .bashrc_19c

// edit the 19c profile with new Oracle Home

vi .bashrc_19c

profile

Then overwrite it the main profile:

cp .bashrc_19c .bashrc

source .bashrc

I will unzip the 19c oracle binaries under the new Oracle Home Path:

unzip LINUX.X64_193000_db_home.zip -d /opt/oracle/product/19c/dbhome_2

And then I will perform silent-installation:

./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile /opt/oracle/product/19c/dbhome_2/install/response/db_install.rsp

*** run root.sh after successful installation

/opt/oracle/product/19c/dbhome_2/root.sh

Reset your profile to point back to 18c database binaries, and startup the 18c database.

Auto-Upgrade Setup:

*** go to the location where you downloaded autoupgrade.jar file , the file can be downloaded from Oracle Support (Doc ID 2485457.1) , and create the configuration file

cd /vagrant/autoupgrade

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -create_sample_file config

config file

*** create your sample configuration file – the following is a TEMPLATE I have used:

cp sample_config.cfg my_config.txt

vi my_config.txt

#

# sample config file

#

#

# Global configurations

#

# This directory will include the following

#   (1) AutoUpgrade’s global directory

#   (2) Any logs, not directly tied to a job

#   (3) Config files

#   (4) progress.json and status.json

global.autoupg_log_dir=/vagrant/autoupgrade/autoupgrade_log

 #

# Database number 1

#

upg1.dbname=ORCLCDB

upg1.start_time=NOW

upg1.source_home=/opt/oracle/product/18c/dbhome_1

upg1.target_home=/opt/oracle/product/19c/dbhome_2

upg1.sid=ORCLCDB

upg1.log_dir=/vagrant/autoupgrade/autoupgrade_log

upg1.upgrade_node=localhost

upg1.target_version=19

#upg1.run_utlrp=yes

#upg1.timezone_upg=yes

*** you can execute the following help command to check the options available:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -help

help-command

The following image illustrate different auto-upgrade modes:

auto-upgrade-modes

I will start with “analyze” mode:

The analyze mode will basically perform the pre-checks required in for the upgrade to succeed.

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode analyze -noconsole

*** please note that -noconsole option specified in the command, will make AutoUpgrade runs based ONLY on the settings in the configuration file, without requiring console input.

analyze-mode

The command executes successfully and the Job number is 100 !

I will go to the location where the logs are generated to grab the produced HTML file:

cd /vagrant/autoupgrade/autoupgrade_log/ORCLCDB/100/prechecks

Inspecting orclcdb_preupgrade.html file….the report will be as shown below:

htmil_report

In the report you will see different severity levels: INFO , WARNING , ERROR , RECOMMEND

Of course “ERROR” indicates that you need Fix the issue before attempting to upgrade the database.

I will explore now “FIXUPS” mode:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode fixups -noconsole

fixups_mode

I will configure flashback, and guaranteed restore point:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST =’/opt/oracle/product/18c/dbhome_1/fra’ SCOPE=BOTH;

SQL> create restore point before_19c_upgrade_emad guarantee flashback database;

I will explore now “UPGRADE” mode:

Shutdown your current database, and switch your environment variables to point to the target binaries (in my case 19c).

Don’t forget to copy your init.ora file from old binaries to new binaries…. also anything under $ORACLE_HOME/network/admin from old binaries to new binaries for illustration:

cp -r /opt/oracle/product/18c/dbhome_1/dbs/*  $ORACLE_HOME/dbs

cp -r /opt/oracle/product/18c/dbhome_1/network/admin/*  $ORACLE_HOME/network/admin/

sqlplus / as sysdba

SQL> startup upgrade

exit;

Then, execute the following command to upgrade:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode upgrade -noconsole

upgrade_mode

In my simulation I had a problem with FRA size…so I fixed it and the process completed successfully.

Important Remark: in “upgrade” mode No post-upgrade actions will be performed. The post-upgrade actions are expected to be handled by the DBA. For example timezone upgrade will be implemented manually by the DBA. [check the image of different modes at the beginning of the blog and what stages each mode will cover, “deploy” mode will run all stages]

In case you want to roll-back to previous release (in my case 18c):

shutdown your current database(19c) and listener.

set your environment variables to point to 18c binaries.

sqlplus / as sysdba;

SQL> select current_scn from v$database;

SQL> shutdown immediate;

SQL> startup mount;

SQL> select * from v$restore_point;

SQL> flashback database to restore point before_19c_upgrade_emad;

SQL> alter database open resetlogs;

SQL> select current_scn from v$database;

 

I hope this blog post about Oracle Auto-Upgrade tool will be a good starting point for your to try it out !

Resources/References:

The new AutoUpgrade utility in Oracle 19c

AutoUpgrade Tool (Doc ID 2485457.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109