Subscriber Count

    464

Subscribe to Posts by Email

Pages

Docker : Oracle MySQL Container

 

Assuming you have completed docker installation on windows and registered with oracle container registry, Read first part here if not yet done

This post explain how to create a container for mysql and also show some example of managing container etc. In less than 10 mins mysql database is ready and you can practice what ever you like.

Pull Docker Image and Run the docker with default  options

docker pull container-registry.oracle.com/mysql/community-server

Run the docker with mysql image, I have kept the root password and name of docker is mysql5.7

docker run --name mysql5.7 -e MYSQL_ROOT_PASSWORD=sayit -d container-registry.oracle.com/mysql/community-server:lates

Login to Shell

docker exec -it mysql5.7 bash

mysql1 mysql2 mysql3 mysql4

Getting connected outside from the docker use -p or -P option while run the docker command

 

Once logged in , show databases and create databases etc and rest of things will be as usual.

mysql7

 

Stop docker container

docker stop mysql5.7

mysql5

Start docker container

docker start mysql5.7

mysql6

 

Remove docker container, you can use -f option to forcefully delete the running docker

docker rm mysql5.6 -f 

 

Further, if you want to keep any additional parameter you can use a cnf file and run the docker run command, this file will replace the cnf file in the docker container 🙂

The MySQL startup configuration in these Docker images is specified in the file /etc/my.cnf. If you want to customize this configuration for your own purposes, you can create your alternative configuration file in a directory on the host machine and then mount this file in the appropriate location inside the MySQL container, effectively replacing the standard configuration file.

If you want to base your changes on the standard configuration file, start your MySQL container in the standard way described above, then do:
docker exec -it my-container-name cat /etc/my.cnf > /my/custom/config-file
… where ´/my/custom/config-file´ is the path and name of the new configuration file. Then start a new MySQL container like this:
docker run --name my-new-container-name -v /my/custom/config-file:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=my-secret-pw -d container-registry.oracle.com/mysql/community-server:tag

 

Docker : Oracle Weblogic Container

Hello

Assuming you have done the installation of docker and registered to container registry. This post elaborates the weblogic docker usage and how to see logs and manage weblogic. At last of this post I will also show you to run some examples to create additional domain /servers etc with docker samples.

In less than 20 mins (download time), the weblogic server with Admin server is ready for use, Cool isnt it 🙂

 Pull the docker image from the container registry

 $docker pull container-registry.oracle.com/middleweare/weblogic

wl2

 

$docker run -d container-registry.oracle.com/middleware/weblogic:latest

wl1

 

Get the logs, so you can get password

C:\Users\gandhi>docker logs 21054365ed941fc0f2cc1e4742adf50fa767ab63ea813d26f4634e1f4027774a

Password does not Match the criteria, re-generating...
Password does not Match the criteria, re-generating...

Oracle WebLogic Server Auto Generated Empty Domain:

----> 'weblogic' admin password: qw1mAUIu
Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

domain_name : [base_domain]
admin_port : [7001]
domain_path : [/u01/oracle/user_projects/domains/base_domain]
production_mode : [prod]
admin password : [qw1mAUIu]
admin name : [AdminServer]
admin username : [weblogic]
Exiting WebLogic Scripting Tool.

.
.
JAVA Memory arguments: -Djava.security.egd=file:/dev/./urandom
.
CLASSPATH=/usr/java/jdk1.8.0_151/lib/tools.jar:/u01/oracle/wlserver/server/lib/weblogic.jar:/u01/oracle/wlserver/../oracle_common/modules/thirdparty/ant-contrib-1.0b3.jar:/u01/oracle/wlserver/modules/features/oracle.wls.common.nodemanager.jar::/u01/oracle/wlserver/common/derby/lib/derbynet.jar:/u01/oracle/wlserver/common/derby/lib/derbyclient.jar:/u01/oracle/wlserver/common/derby/lib/derby.jar
.
PATH=/u01/oracle/user_projects/domains/base_domain/bin:/u01/oracle/wlserver/server/bin:/u01/oracle/wlserver/../oracle_common/modules/thirdparty/org.apache.ant/1.9.8.0.0/apache-ant-1.9.8/bin:/usr/java/jdk1.8.0_151/jre/bin:/usr/java/jdk1.8.0_151/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/java/default/bin:/u01/oracle/oracle_common/common/bin:/u01/oracle/wlserver/common/bin
.
***************************************************
* To start WebLogic Server, use a username and *
* password assigned to an admin-level user. For *
* server administration, use the WebLogic Server *
* console at http://hostname:port/console *
***************************************************
Starting WLS with line:
/usr/java/jdk1.8.0_151/bin/java -server -Djava.security.egd=file:/dev/./urandom -cp /u01/oracle/wlserver/server/lib/weblogic-launcher.jar -Dlaunch.use.env.classpath=true -Dweblogic.Name=AdminServer -Djava.security.policy=/u01/oracle/wlserver/server/lib/weblogic.policy -Dweblogic.ProductionModeEnabled=true -Djava.system.class.loader=com.oracle.classloader.weblogic.LaunchClassLoader -javaagent:/u01/oracle/wlserver/server/lib/debugpatch-agent.jar -da -Dwls.home=/u01/oracle/wlserver/server -Dweblogic.home=/u01/oracle/wlserver/server weblogic.Server
<Dec 9, 2017 8:22:17 AM GMT> <Info> <Security> <BEA-090905> <Disabling the CryptoJ JCE Provider self-integrity check for better startup performance. To enable this check, specify -Dweblogic.security.allowCryptoJDefaultJCEVerification=true.>
<Dec 9, 2017 8:22:17 AM GMT> <Info> <Security> <BEA-090906> <Changing the default Random Number Generator in RSA CryptoJ from ECDRBG128 to HMACDRBG. To disable this change, specify -Dweblogic.security.allowCryptoJDefaultPRNG=true.>
<Dec 9, 2017 8:22:18 AM GMT> <Info> <WebLogicServer> <BEA-000377> <Starting WebLogic Server with Java HotSpot(TM) 64-Bit Server VM Version 25.151-b12 from Oracle Corporation.>
<Dec 9, 2017 8:22:18 AM GMT> <Info> <RCM> <BEA-2165021> <"ResourceManagement" is not enabled in this JVM. Enable "ResourceManagement" to use the WebLogic Server "Resource Consumption Management" feature. To enable "ResourceManagement", you must specify the following JVM options in the WebLogic Server instance in which the JVM runs: -XX:+UnlockCommercialFeatures -XX:+ResourceManagement.>
<Dec 9, 2017 8:22:18 AM GMT> <Info> <Management> <BEA-141107> <Version: WebLogic Server 12.2.1.3.0 Thu Aug 17 13:39:49 PDT 2017 1882952>
<Dec 9, 2017 8:22:20 AM GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING.>
<Dec 9, 2017 8:22:20 AM GMT> <Info> <WorkManager> <BEA-002900> <Initializing self-tuning thread pool.>
<Dec 9, 2017 8:22:20 AM GMT> <Info> <WorkManager> <BEA-002942> <CMM memory level becomes 0. Setting standby thread pool size to 256.>
<Dec 9, 2017 8:22:20,982 AM GMT> <Notice> <Log Management> <BEA-170019> <The server log file weblogic.logging.FileStreamHandler instance=1527301193
Current log file=/u01/oracle/user_projects/domains/base_domain/servers/AdminServer/logs/AdminServer.log
Rotation dir=/u01/oracle/user_projects/domains/base_domain/servers/AdminServer/logs
is opened. All server side log events will be written to this file.>
<Dec 9, 2017 8:22:21,190 AM GMT> <Notice> <Security> <BEA-090946> <Security pre-initializing using security realm: myrealm>
<Dec 9, 2017 8:22:21,874 AM GMT> <Notice> <Security> <BEA-090947> <Security post-initializing using security realm: myrealm>
<Dec 9, 2017 8:22:22,861 AM GMT> <Notice> <Security> <BEA-090082> <Security initialized using administrative security realm: myrealm>
<Dec 9, 2017 8:22:23,127 AM GMT> <Notice> <Security> <BEA-090083> <Storing boot identity in the file: /u01/oracle/user_projects/domains/base_domain/servers/AdminServer/security/boot.properties.>
<Dec 9, 2017 8:22:23,455 AM GMT> <Notice> <JMX> <BEA-149512> <JMX Connector Server started at service:jmx:iiop://172.17.0.5:7001/jndi/weblogic.management.mbeanservers.runtime.>
<Dec 9, 2017 8:22:23,844 AM GMT> <Notice> <JMX> <BEA-149512> <JMX Connector Server started at service:jmx:iiop://172.17.0.5:7001/jndi/weblogic.management.mbeanservers.domainruntime.>
<Dec 9, 2017 8:22:23,860 AM GMT> <Notice> <JMX> <BEA-149512> <JMX Connector Server started at service:jmx:iiop://172.17.0.5:7001/jndi/weblogic.management.mbeanservers.edit.>
<Dec 9, 2017 8:22:25,210 AM GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STANDBY.>
<Dec 9, 2017 8:22:25,212 AM GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING.>
<Dec 9, 2017 8:22:25,275 AM GMT> <Notice> <Log Management> <BEA-170036> <The Logging monitoring service timer has started to check for logged message counts every 30 seconds.>
<Dec 9, 2017 8:22:29,116 AM GMT> <Notice> <Log Management> <BEA-170027> <The server has successfully established a connection with the Domain level Diagnostic Service.>
<Dec 9, 2017 8:22:30,192 AM GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to ADMIN.>
<Dec 9, 2017 8:22:30,286 AM GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RESUMING.>
<Dec 9, 2017 8:22:30,382 AM GMT> <Notice> <Server> <BEA-002613> <Channel "Default[1]" is now listening on 127.0.0.1:7001 for protocols iiop, t3, ldap, snmp, http.>
<Dec 9, 2017 8:22:30,386 AM GMT> <Notice> <Server> <BEA-002613> <Channel "Default" is now listening on 172.17.0.5:7001 for protocols iiop, t3, ldap, snmp, http.>
<Dec 9, 2017 8:22:30,390 AM GMT> <Notice> <Server> <BEA-002613> <Channel "Default[1]" is now listening on 127.0.0.1:7001 for protocols iiop, t3, ldap, snmp, http.>
<Dec 9, 2017 8:22:30,390 AM GMT> <Notice> <WebLogicServer> <BEA-000329> <Started the WebLogic Server Administration Server "AdminServer" for domain "base_domain" running in production mode.>
<Dec 9, 2017 8:22:30,390 AM GMT> <Notice> <Server> <BEA-002613> <Channel "Default" is now listening on 172.17.0.5:7001 for protocols iiop, t3, ldap, snmp, http.>
<Dec 9, 2017 8:22:30,395 AM GMT> <Notice> <WebLogicServer> <BEA-000360> <The server started in RUNNING mode.>
<Dec 9, 2017 8:22:30,410 AM GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING.>

 

Then go to your browser and type http://127.0.0.1:7001/console , then weblogic admin console will be appeared.

wl3

 

 

 

Oracle Container Registry – Docker for Oracle Databases

Hello All,

This post is about how to use Docker and utilize oracle container registry i.e oracle official docker hub.

Until now,I am using open source/public docker images from dockerhub, Today I had a final look on Oracle Container Registry which having Official Oracle Docker Images with different products like FMW,Database,Goldengate,Java, Coherence,Weblogic.

These two locations we can find Oracle Docker Images

  1. https://container-registry.oracle.com
  2. https://store.docker.com/search?certification_status=certified&q=oracle&source=verified&type=image

And there is github official repository as well.

  1. https://github.com/oracle/docker-images

Those who are not familiar with docker, its just simple. All of us are using Oracle Virtual Box/VM etc for Virtualization, how ever  if you want to use a virtual box first you need to install the OS on virtual machine, in Docker, your host OS is shared among all other containers (aka virtual images) or the docker image will have its own OS too. All you need Install docker, download image, run the image. Thats it. :). I will write a post on managing docker separately .

Let's have a quick install and look at container-registry.

Install docker for windows using following link (You need to have virtualisation enabled in your machine)

https://store.docker.com/editions/community/docker-ce-desktop-windows

Then log into https://container-registry.oracle.com with your single sign on account.

containerregistry1

 

containerregistry2

As you see above Coherance, Databases, Java,Middleware,MySQL, OS and Openstack are the current categogies and docker images available. Click on the Databases.

 

There you find the enterprise,standard, instant client for oracle database. click on any enterprise or standard, you will be asked to accept terms and conditions, You click on continuebutton, on the right of the following screen. I already accepted hence I do not have it to show you

 

containerregistrydb1

 

Currently with Oracle database the following limitations are there

  1. This Docker image release supports only single database instance.
  2. Dataguard feature is not supported.

When you click on the enterprise again you will be open with documentation how you can use this image.

 

containerregistrydb2

 

The highlighted are the two commands really you need to do to get a database up and running. (You must have a decent internet connection to download the 2.5gb of database image).

At this point, you have installed docker and you have container-registry account.

Open a command prompt and pull the docker image first. It will prompt you to login your sso account. While pulling the image if you receive access denied error that means you have not accepted the terms and conditions in browser, accept it and then pull again.

 

Once you pulled the images, run using,

docker pull container-registry.oracle.com/database/enterprise

donwload-enterprise-docker

For standard edition use ,

docker pull container-registry.oracle.com/database/standard

donwload-standard-docker

This will take time to download, once downloaded, run the image with, Just run the docker image without options,

Just run without options

docker run -d -it --name orcl12c container-registry.oracle.com/database/enterprise:12.2.0.1

Login to docker for sqlplus

docker exec -it orcl12c bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

Run the image to ensure the database is accessible outside container -p is used

docker run -d -it --name orcl12c -P container-registry.oracle.com/database/enterprise:12.2.0.1

If you want to set options and few other params for databases run the following block, This will use this options, SID, PDB Name, Password and Characterset , lets run this one

docker run --name orcl12cr2 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCL12C -e ORACLE_PDB=PDB1  -e ORACLE_PWD=admin123 -e ORACLE_CHARACTERSET=AL32UTF8 -  container-registry.oracle.com/database/enterprise:12.2.0.1

Screen output

Setup Oracle Database
Oracle Database 12.2.0.1 Setup
Sat Dec 9 11:07:23 UTC 2017

Check parameters ......
log file is : /home/oracle/setup/log/paramChk.log
paramChk.sh is done at 0 sec

untar DB bits ......
log file is : /home/oracle/setup/log/untarDB.log
untarDB.sh is done at 59 sec

config DB ......
log file is : /home/oracle/setup/log/configDB.log
Sat Dec 9 11:08:22 UTC 2017
Start Docker DB configuration
Call configDBora.sh to configure database
Sat Dec 9 11:08:22 UTC 2017
Configure DB as oracle user
Setup Database directories ...

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 9 11:08:22 2017

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

Connected to an idle instance.

SQL>
File created.

SQL> ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size 8792536 bytes
Variable Size 352323112 bytes
Database Buffers 973078528 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL>
Database altered.

SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.2.0
/dbhome_1/dbs/spfileORCLCDB.or
a
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string CLOUD_ONLY
SQL>
User altered.

SQL>
User altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
update password

Enter password for SYS:
create pdb : ORCLPDB1

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 9 11:08:42 2017

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

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

SQL> 2 3 4 5
Pluggable database created.

SQL>
Pluggable database altered.

SQL>
Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Reset Database parameters

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 9 11:08:52 2017

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

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

SQL>
System altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 09-DEC-2017 11:08:52

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

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/a3d65060e96e/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 09-DEC-2017 11:08:53
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/a3d65060e96e/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

DONE!
Remove password info
Docker DB configuration is complete !
configDB.sh is done at 90 sec

Done ! The database is ready for use .
# ===========================================================================
# == Add below entries to your tnsnames.ora to access this database server ==
# ====================== from external host =================================
ORCLCDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip-address>)(PORT=<port>))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLCDB.localdomain)))
ORCLPDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip-address>)(PORT=<port>))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)))
#
#ip-address : IP address of the host where the container is running.
#port : Host Port that is mapped to the port 1521 of the container.
#
# The mapped port can be obtained from running "docker port <container-id>"
# ===========================================================================
ORCLPDB1(3):Database Characterset for ORCLPDB1 is AL32UTF8
ORCLPDB1(3):Opatch validation is skipped for PDB ORCLPDB1 (con_id=0)
2017-12-09T11:08:52.480340+00:00
ORCLPDB1(3):Opening pdb with no Resource Manager plan active
Pluggable database ORCLPDB1 opened read write
Completed: alter pluggable database ORCLPDB1 open
alter pluggable database all save state
Completed: alter pluggable database all save state
2017-12-09T11:08:52.666801+00:00
ALTER SYSTEM SET encrypt_new_tablespaces='DDL' SCOPE=BOTH;
2017-12-09T11:09:40.915047+00:00
TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P287 (2900) VALUES LESS THAN (TO_DATE(' 2017-12-10 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P288 (2900) VALUES LESS THAN (TO_DATE(' 2017-12-10 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2017-12-09T11:09:41.026256+00:00
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: /u04/app/oracle/redo/redo002.log
TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P291 (2899) VALUES LESS THAN (TO_DATE(' 2017-12-09 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

Find the tns details using

docker port oracleDB

C:\Users\gandhi>docker port orcl12cr2
1521/tcp -> 0.0.0.0:1521
5500/tcp -> 0.0.0.0:5500

Then connect using sqlplus, but in order to work connection outside of container works you must run the container with -P or -p option and the default sys password is Oracdoc_db1

sqlplus sys/Oradoc_db1@ORCLCDB as sysdba

dockersqlplus

Let's look at environment, the filesystem and enviornment variables inside the container

docker exec -it orcl12cr2 bash

env

 

Let's check the alert log, docker has option logs, which shows alert log, and also you can tail it using -f and since how many mins you want ,

docker logs orcl12cr2 -f --since 10m

alertlogcheck

For your knowledge purpose , /home/oracle/setup/ contains all the files that docker use for setup etc.

Hope you liked it, Dockers provides much quicker provisioning and provides flexibility and feasibility for a devops model in database domain.

Next post we will be seeing Oracle Container for MySQL and Weblogic

-Thanks

Suresh

 

AIOUG – Sangam 17 – I am Speaking too

Hello All,

I am speaking this year at AIOUG Sangam 17 and my presenation topic is Oracle Sharding.

I_m_Speaking

Agenda yet to be published and registrations are open.

http://www.aioug.org/sangam17/index.php/registration/registration-details

-Thanks

Suresh

 

Australia OTN 2017 – IAOUG – OTN (ODC) Conference 2017

We are back again, this year with more fantastic sessions with another set of Speakers who are well known in Oracle Community and ACE (D).

iaoug

More Details at http://www.iaoug.com/otn-days.html

Registrations are Open and Tickets are Limited

Venue: Rydge Hotel World Square, Sydney, Australia

Date: Nov 24,

From: 8:30 am until 5:30 pm

Schedule Details: https://sydneyotnday2017.sched.com/

And I am speaking too about : DevOps for Databases,

Register here: https://sydneyotnday2017.sched.com/event/CVLo/devops-for-databases

-Thanks

Suresh

 

catctl.pl -E Emulation Feature

Hi

Many of us know about catctl.pl script to upgrade the databases using parallel slave processes since 12c. Adding to that there is executable in $ORACLE_HOME/rdbms/bin called dbupgrade utility which in turn calls out catctl.pl.

There is little known feature which is emulation feature you can run with this utility or script. it displays on screen the same output that you see during an actual upgrade.

$ORACLE_HOME/rdbms/catctl.pl -E

You can read more from here https://docs.oracle.com/database/122/UPGRD/testing-upgraded-production-oracle-database.htm#UPGRD52882

-Thanks

Suresh

 

18c Database

Hello All

As you all aware or if not aware of, the next database release i.e 12.2.0.2 is named as 18c and 19c so on. Here is the roadmap from document 742060.1.

18c roadmapWhat does it contain or we expect from this release?

  1. Tagged as fully autonomous database if your database in Oracle Public Cloud
  2. If you are not in Oracle Public cloud, you will not be getting fully autonomous features
  3. Self patching, sounds to me like Oracle Configuration Management feature enhancements

-Thanks

Suresh

Oracle 12c Upgrade : Virtual Column with Function Based Index (bug)

Issue: Post 12C upgrade: Oracle Bug - Virtual Column issue

Fix: Set it at session level or system level - fix_control=’16237969:OFF’

Be aware of the virtual column getting created with function based index, which may lead to sub-optimal plans.

In 11g , index is getting picked by the oracle optimizer and going with optimal Plan. But in 12c , index is not getting picked by the oracle optimizer and hence it’s going with sub-optimal plan in the query that  result in high logical I/O’s and elapse time.

Thanks Vasu N, by sharing his findings on this and working with Oracle on the same and he thought of helping to all by sharing this.

#########################################
Reproduce the issue with below steps:
#########################################

CREATE TABLE TEST_VIRTUAL
AS
SELECT ROWNUM id, 'TEST' || ROWNUM name
FROM DUAL
CONNECT BY ROWNUM < 10000;

CREATE TABLE TEST_VIRTUAL_PARENT
AS
SELECT ROWNUM id, MOD (ROWNUM, 3) GROUP_NAME
FROM DUAL
CONNECT BY ROWNUM < 10000;

CREATE INDEX TEST_VIRTUAL_FBI_IDX ON TEST_VIRTUAL (UPPER ("NAME"));
CREATE INDEX TEST_VIRTUAL_PARENT_IDX ON TEST_VIRTUAL_PARENT (ID);

##############
#Execute statement with a simple function
#############
SELECT LRD.ID FROM TEST_VIRTUAL_PARENT LRD, TEST_VIRTUAL MLR
WHERE     UPPER (MLR.NAME) = 'TEST1'
AND LRD.ID = MLR.ID(+)
AND LRD.GROUP_NAME = 1;

#####################
The resulted Plan is as below by not picking fbi  index
#####################
SELECT STATEMENT ALL_ROWS Cost: 17 Bytes: 66,660 Cardinality: 3,333

4 FILTER Filter Predicates: UPPER("NAME")='TEST1'
3 HASH JOIN OUTER Access Predicates: "LRD"."ID"="MLR"."ID"(+) Cost: 17 Bytes: 66,660 Cardinality: 3,333
1 TABLE ACCESS FULL TABLE PERF11I.TEST_VIRTUAL_PARENT Filter Predicates: "LRD"."GROUP_NAME"=1 Cost: 7 Bytes: 23,331 Cardinality: 3,333
2 TABLE ACCESS FULL TABLE PERF11I.TEST_VIRTUAL Cost: 10 Bytes: 129,987 Cardinality: 9,999

##############
#Execute statement with a simple function, along with hint to not use the virtual columns
#############

SELECT /*+ OPT_PARAM('_replace_virtual_columns','false') */
LRD.ID
FROM TEST_VIRTUAL_PARENT LRD, TEST_VIRTUAL MLR
WHERE UPPER (MLR.NAME) = 'TEST1' AND LRD.ID = MLR.ID(+)

###############
Plan
###############
SELECT STATEMENT ALL_ROWS Cost: 16 Bytes: 1,700 Cardinality: 100
4 HASH JOIN Access Predicates: "LRD"."ID"="MLR"."ID" Cost: 16 Bytes: 1,700 Cardinality: 100
2 TABLE ACCESS BY INDEX ROWID BATCHED TABLE PERF11I.TEST_VIRTUAL Cost: 9 Bytes: 1,300 Cardinality: 100
1 INDEX RANGE SCAN INDEX PERF11I.TEST_VIRTUAL_FBI_IDX Access Predicates: UPPER("NAME")='TEST1' Cost: 1 Cardinality: 40
3 INDEX FAST FULL SCAN INDEX PERF11I.TEST_VIRTUAL_PARENT_IDX Cost: 7 Bytes: 39,996 Cardinality: 9,999

Oracle 12.2 New Features : Approximate Query Processing

While sometime ago, I was looking for a database project requirement to process analytical workload. The organisation choice was Oracle and Developers choice was postgres or vertica. But to handle Vertica we do not have that much of data processing actually needed so the final contenders are Oracle and Postgres.

As per licensing guidelines I cannot publish the details of outcome , but I would say the results are not good for me as a Oracle DBA , atleast in processing aggregate functions Oracle for a 2 million record table with more than 200 columns doing a count (*). But indeed we want an enterprise database and stable one not the open source database. Well our data in terms of distinctivity does not change much so some sort of approximation is also fine for us.

That is what now in 12.2, with Approximate Query Processing which is as per documentation

Approximate Query Processing

The 12.2 release extends the area of approximate query processing by adding approximate percentile aggregation. With this feature, the processing of large volumes of data is significantly faster than the exact aggregation. This is especially true for data sets that have a large number of distinct values with a negligible deviation from the exact result.

Approximate query aggregation is a common requirement in today's data analysis. It optimizes the processing time and resource consumption by orders of magnitude while providing almost exact results. Approximate query aggregation can be used to speed up existing processing.

Oracle provides a set of SQL functions that enable you to obtain approximate results with negligible deviation from the exact result. There are additional approximate functions that support materialized view based summary aggregation strategies. The functions that provide approximate results are as follows:

APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT_DETAIL
APPROX_COUNT_DISTINCT_AGG
TO_APPROX_COUNT_DISTINCT
APPROX_MEDIAN
APPROX_PERCENTILE
APPROX_PERCENTILE_DETAIL
APPROX_PERCENTILE_AGG
TO_APPROX_PERCENTILE

Approximate query processing can be used without any changes to your existing code. When you set the appropriate initialization parameters, Oracle Database replaces exact functions in queries with the corresponding SQL functions that return approximate results.

Parameter that effects the Approximate Query Processing

SQL&gt; show parameter approx

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
approx_for_aggregation               boolean     TRUE
approx_for_count_distinct            boolean     TRUE
approx_for_percentile                string      ALL
SQL&gt;

When we use the functions , the value returns in hexadecimal to retrieve exact values we need to use associate to_* functions along with materialized views

SQL&gt; select unit_code,APPROX_COUNT_DISTINCT_AGG(id) from (select unit_code,APPROX_COUNT_DISTINCT_DETAIL(ID) id from INSIGHTS.TABLE group by unit_code) group by unit_code;

UNIT_CODE       APPROX_COUNT_DISTINCT_AGG(ID)
--------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
MHIX115         0D0C0925009E00000000000000A1000000000000000000000040000000000000000000000000000000001000000000000000000000000000000000000000000000040000000000000000000000000000
POIX108         0D0C0DD500740000000000000075000000000000000000000000000080000000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000
PHIX365         0D0C0DD500270000000000000027000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000

So, let me create a MV first with my query

CREATE MATERIALIZED VIEW test_approx_agg_mv AS
SELECT unit_code,
APPROX_COUNT_DISTINCT_DETAIL(id) id
FROM INSIGHTS.TABLE
GROUP BY unit_code;
Then let's access the query as below, as you see the values provided are approximate , when you compare the values with original count (second query)

SQL&gt; SELECT unit_code, TO_APPROX_COUNT_DISTINCT(id) "ids" FROM test_approx_agg_mv ORDER BY unit_code;

UNIT_CODE              ids
--------------- ----------
ABR13                    2
ABT10                   42
ABT13                   63

SQL&gt; SELECT unit_code, count(id) from INSIGHTS.TABLE where unit_code in ('ABR13','ABT10','ABT13') group by unit_code;

UNIT_CODE       COUNT(id)
--------------- -------------
ABR13                      11
ABT13                     264
ABT10                     202

If i directly try to access the column with regular count we get an error;

SQL&gt;SELECT unit_code, count(id) from test_approx_agg_mv ORDER BY unit_code
                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got BLOB
SQL&gt;

So use the approximation function with associate function to retrieve the data, this works well with MV's rather plain queries

-Thanks

Suresh