Thursday, December 24, 2020

SANGAM20 : Rapid Fire Session: Database Performance Tuning

SANGAM20 : Rapid Fire Session: Database Performance Tuning 

As you all know AIOUG's flagship event SANGAM20 was happened early this December. It was three week event 03Dec to 06-Dec, 10-Dec to 13-Dec and 17-Dec to 20-Dec 2020.

It was really awesome event and many great speakers has presented in this event.

This year, AIOUG brings new concept of Rapid Fire where two or more speakers speaks about technology and participants can shoot questions to the experts.

I am very much grateful to be a part of this event. Vivek Sharma, Karan Dodwal and Chandan Tanwani (me) were speaker for Rapid Fire session of Database Performance Tuning. Our Agenda for this session was as below,

  • High-Level Overview of Database/Application Performance and Oracle Optimizer - Vivek Sharma
  • SQL Plan Optimization - Karan Dodwal
  • SQL Execution Plan Case Studies - Chandan Tanwani

If you were not able to attend this session must watch recording on AIOUG website. Watch here,

https://www.aioug.org/sangam20-sessions?session_id=191


SANGAM20 Interview by Jim Grisanzio

I am very grateful that I was interviewed by Jim Grisanzio. It was nice talking with Jim and I shareD my thought about Rapid Fire session i.e. what attendees can learn and how this will help to achieve maximum performance and it's insight. And we had discussed on AIOUG's Flagship event SANGAM20, about challenges to organize such big event, the limitations and the barriers and volunteer's passion about AIOUG events.


Thank you all for joining us in Sangam20. Looking forward for your great participation again in upcoming events.


Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Wednesday, December 2, 2020

SANGAM20 : A Flagship Event by AIOUG : It's Oracle Learning Festival

SANGAM20 : A Flagship Event by AIOUG : It's Oracle Learning Festival  

AIOUG: Asia’s Largest Oracle User Group, is back with SANGAM20, a Flagship event of the year 2020.

SANGAM20, This is Oracle Learning Festival. This is 12th year and this time it is for twelve days. Truly inspirational event which is scheduled on 3-6, 10-13, 17-20 December 2020. Must for in-depth technical sessions, Hands-on-labs, Rapid Fire Sessions, Fireside chats, Keynotes, Community sessions and of course, open your mind and expand your network at Sangam20. Only this year it is being brought to you in a new format to adapt to the 'new' normal.

What's New
AIOUG is bringing unique Oracle festival to you in a new and exciting way, using a digital format to bring you an interactive, productive and fun festival experience.
The new format lends itself very nicely to our global theme! So no matter where you are in the world, you can come and be part of Sangam20.
This year AIOUG leveraging the best digital tools to make the Oracle festival fun and immersive experience. And importantly deliver innovative new solutions that will help your business.


Sangam20 Highlights:
  • New Content! New Speakers!
  • 9 Keynote Sessions
  • 12 days, 90+ hours of learning and networking
  • 3 Fire chat, 9 Rapid Fire Sessions
  • 90+ Sessions, 120+ Speakers World-class speakers
  • 6+ Hands-on Lab Sessions
  • 7 Tracks ( Database, Applications, Analytics & ML, Future Technology Cloud, Oracle ACE Community Sessions, Interviews, and many more )
  • Global Webinar Series covering APAC, EMEA, AMS

How to Register: 
Register for FREE and Secure Your Seat! Registration: 3 simple steps to register

  1. Go to https://www.aioug.org/join page and join the AIOUG Starter (FREE) membership.
  2. Log in and go to https://www.aioug.org/sangam20#sang_597 page.
  3. Check the schedule and register your favourite session(s).
How to Join the Webinars: Login to the AIOUG website and Go to https://www.aioug.org/sangam20#sang_597



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Wednesday, August 26, 2020

Password Less; Schema Only Accounts

Password Less; Schema Only Accounts 

Schema Only Accounts Overview

Oracle Database 18c introduced new feature called "Schema Only Accounts". It means that there is no password authentication on schemas.
This new functionality allows administrators to secure their databases and protect their applications.
Since schemas have no password, one cannot connect directly to the database (this is the whole purpose of the schema only account).

The only way is, connect through a proxy account.
As one cannot connect directly to the schema only accounts, the schema itself is more Secure. In other words, you don’t want application users to connect directly with the schema account.


Why and When to use?

Generally, schema is created with password and credentials might shared with different stack holders or application users.

To avoid direct usage of schema and password, Proxy user can be created but still one can login to schema if someone knows the password.

So, how to avoid miss use of schema password? 
Solution is, Schema Only Accounts (in other words password less).

Since there is no password for schemas, no direct connection is allowed. And also no password management is required like password expiry or password rotation.

This feature reduces the security risk of attackers using default/regular passwords to hack into these accounts.

Configure Password less Schema Only Accounts

Following is example show that mobileapp schema not required and authentication to login. One cannot directly login to this schema as there is no password but User cbtadmin has given grants to access objects from mobileapp schema using proxy authentication. 

Create Schema without password.

Syntax
CREATE USER <schema_name> NO AUTHENTICATION QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO <schema_name>;

Example
CREATE USER mobileapp NO AUTHENTICATION QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO mobileapp;


Allow application user to connect to Schema

Syntax
ALTER USER schema_owner GRANT CONNECT THROUGH <application user>;

Example
ALTER USER mobileapp GRANT CONNECT THROUGH cbtadmin;


How application user can connect to schema

Syntax
sql> conn <application user>[schema name]/password@pdb1

Example
sql> conn cbtadmin[mobileapp]/cbtadmin@pdb1




Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Thursday, July 2, 2020

Oracle Groundbreakers Yatra 2020 - Online Webinar Series

Oracle Groundbreakers Yatra 2020 - Online Webinar Series



AIOUG is largest Oracle users group. In Oracle Groundbreakers Yatra 2020 (OGYatra) webinar series, they are covering 125+ hours of learning sessions in 14 days, 100+ speakers, including Oracle Gurus, Oracle ACE and Java Champions.

It's my pleasure that I am also part of this #OGYatra webinar series and get a chance to speak and share my knowledge to large audience across the world. Many Thanks to Sai Penumuru and AIOUG team for great support.

Join me on 16th July at 11:30am and let's discuss on Oracle Multi-Tenant Architecture and it's Feature with Live Demo.

Specially, I will try to cover following features in Live Demo,
'- Monitoring of CDB/PDBs
'- Hot Clone PDB Databases
'- Plug-Unplug PDBs from one CDB to another and non-CDB to CDB
'- Refreshable PDBs 
'- PDB Snapshot carousel




Looking forward for your participation.
And Thank you in advance for joining me. See you all soon.



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Wednesday, June 24, 2020

ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error

ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error


I was getting this TNS error while creating Listner with "netca", here is the screenshot for the same.


D:\app\db193\bin>netca

Oracle Net Services Configuration:
Configuring Listener:LISTENERCBT
ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error
  caused by: oracle.net.config.ConfigException: TNS-04414: File error
  caused by: TNS-04605: Invalid syntax error: Unexpected char or LITERAL "IEPDB1" before or at  IEPDB1 = (
  Error in file D:\TNS_ADMIN\tnsnames.ora


This is my windows machine and creating new listner for my newly created CDB. DB version is 19.3

As above error shows that I have some issue with my tnsnames.ora with some of my TNS Entry with IEPDB1.

In my environment TNS_ADMIN environment variable is set and it is pointing to my D:\TNS_ADMIN\tnsnames.ora

Here I found some junk characters near IEPDB1 TNS entry, I removed it and save the file and ran netca again...
Now, all went good...It solved my problem .




Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
 

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory


Recently I have installed Oracle 19c Database binary to windows machine. And after that I started DBCA to create database.

In "Configuration options" steps I got memory error. i.e. [DBT-50000] Unable to check for available memory
Earlier I selected 6GB for my SGA but after this error I had selected 1GB only. But still was facing this issue.

Here is the screenshot for the same.



After searching oracle docs for known issue, I found, it's a bug. One can find details about this bug on following oracle notes.

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory in "Specify configuration option" (Doc ID 2631718.1)

Workaround for this issue is,

Run dbca with "-J-Doracle.assistants.dbca.validate.ConfigurationParams=false" like a bellow command,

$> dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

This will solve your issue and DBCA will run smooth, no issues thn.





Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
 
 

Friday, March 27, 2020

ORA-65259: partial synchronization of application is disallowed

ORA-65259: partial synchronization of application is disallowed

You have started application Sync but you find it is taking more time to complete this action. By mistake or purposely you did ctrl+c or stopped the application sync operation. Later point of time if you again start application syncing command, now you might end up hitting bug.

Check below listing,

alter pluggable database application MYAPP sync
*
ERROR at line 1:
ORA-65259: partial synchronization of application is disallowed

This issue occurs in two reasons,
1) If any other operation also going on in parallel with sync.
2) If previously Sync operation was killed or Stopped.

In both the cases, it makes PDBs in inconsistent state. Here suggestion is, always try to avoid cancellation on SYNC operation on application PDBs, no matter if it is taking long time.

Here are two workaround for this issue.

Workaround 1.
Work around for this issue is to apply for bug 24430427.
Patch can be found on https://support.oracle.com

Workaround 2.
Step 1. Create new PDB under application root.
Step 2. Synchronize PDBs with the application root
Step 3:- Upgrade application



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Saturday, February 22, 2020

Oracle Database 20c New Features

Oracle Database 20c New Features


Oracle Database 20c is about to release this year but Oracle has already announce some wonderful features of 20c release in OOW and 20c Webcast.

Most Important things of this year is,

1) Oracle Cloud - Free Tier -
Always Free Services- 1 ADW, 1 ATP , 2 Compute services always free

2) De-support of non-cdb architecture, facilitating 3 PDB's with no cost


Few new enhancements which catches my eyes on Oracle Database 20c Release.

1) In-Memory Enhancements
- Spatial & Text
                - Vector Joins
                - Hybrid Scans
                - Auto populate and evict in-memory

Read more here about In-Memory Enhancements
https://blogs.oracle.com/in-memory/oracle-database-20c-aim-enhancements

2) New Binary JSON data type

Read more here,
https://blogs.oracle.com/sql/how-to-store-query-and-create-json-documents-in-oracle-database

3) Auto Machin Learning in OML4py
    Automatically build and compare Machine Learning models

Read more here,
https://blogs.oracle.com/machinelearning/automated-machine-learning-for-data-scientists

4) Native Blockchain Table
New blockchain type table. This allows only Inserts and select.

Read more here,
https://blogs.oracle.com/blockchain/blockchain-tables-in-oracle-database:-technology-convergence



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert


Tuesday, February 18, 2020

Oracle Database New Features (12c to 19c)

Oracle Database New Features (12c to 19c)


From Oracle database 12c on-wards, whole architecture was changed. And it comes with many new features. Oracle also changed the policy of releasing database patches and version. Now we are seeing new version every year from 2018 on-wards, i.e. 18c in the year of 2018, 19, in the year of 2019 and now very soon 20c is coming this year.

Here I am highlighting Oracle database new features which was introduce in different Oracle database versions. My focus is to highlight most important features from version 12c onwards up to 19c.

This will help you to understand which feature we can use in which version.


Category
DB Versions
12.1
12.2
18c
19c





High Availability
Comprehensive HA and disaster recovery functionality
Sharding
Sharded RAC
Data Guard DML Redirect
Oracle Golden Gate for heterogeneous replication
Autonomous Health Framework
User-defined Sharding Methods
Sharding: Supports multiple PDB shards in a CDB
Unique sequence number generation per shard



Zero Downtime Grid Infrastructure Patching



PDB Recovery Catalog



Improvements to Application Continuity




Category
DB Versions
12.1
12.2
18c
19c





Performance and Salability
In-Memory Column Store
In Memory JSON Queries
Memory Optimized Row Store
Fast ingest support for IoT type workloads
Engineered Systems
In Memory Active Standby
New Approximate Functions
Quarantine for SQL Statements
Optimizer Adaptive Feature
Optimizer Adaptive Feature

Stats Only Queries
Approximate Query Processing
New Approximate Functions

Improvements for COUNT DISTINCT and GROUP BY queries

Optimizer Statistics Advisor

Automatic Indexing



Realtime Statistics



Automatic Resolution of SQL Plan Regressions



Category
DB Versions
12.1
12.2
18c
19c





Multitenant
Container managed database virtualization
Online cloning & relocation
Transportable backups
DBCA Silent mode operations for PDB
Manage Many as one
Incremental refresh of test/dev master
Snapshot carousel
RAT and ADDM at PDB level
Patching, Backup, Security, Online Cloning, Online Relocation
Application containers
Refreshable PDB switchover
Automated PDB Relocation



Category
DB Versions
12.1
12.2
18c
19c





Security
Security In-Depth
Access Controls, Encryption, Redaction, Masking, Auditing, SQL Firewalls Key Vault
Online encryption
Integration with Active Directory
Data Dictionary Encryption
Audit Vault Database Firewall

Password-less schema creation
Password-less Schema-only Oracle accounts
Security Assessment Tool


Database Vault Operations Control



Privilege Analysis available in EE



Category
DB Versions
12.1
12.2
18c
19c





Application 
Development
Application Express 5.1
Long Identifiers
Improved JSON Support
SQL JSON Enhancements
Comprehensive Language support
PL/SQL, SQL , Python, Node.js, PHP, Java, C, .NET, REST
JSON
Case Insensitive Databases
C, Python, JavaScript JSON SODA API
Materialized View for JSON_TABLE


Private Temporary Tables
Partial JSON Update support



Realtime SQL Monitoring for Developers



Category
DB Versions
12.1
12.2
18c
19c





Data Warehousing and Big Data
Partitioning, Compression, SQL, Analytical Views, Analytical SQL, Data Mining
Partitioned External Tables
In-Memory for external tables
Hybrid Partitioned Tables
Easily analyze data held in Hadoop with Big Data SQL
Analytical Views
Automatic propagation of nologged data to standby
Stats only Queries
Big Data Appliance

Machine Learning :
Random Forrest, Neural Network




Category
DB Versions
12.1
12.2
18c
19c





Other 
Improvements



EZConnect Improvements



Oracle Database XE
Free to use
Includes most EE features
no RAC, Data Guard, GoldenGate



Oracle Database 19c RPM Install, Yum install of Oracle Instant Clients



Oracle Read Only Homes



No click through client software install



Oracle Docker images



SQLDeveloper Web



Oracle Data REST Services (ORDS)










Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert