Wednesday, December 26, 2018

My Journey of SANGAM18, Meeting of Minds

My Journey of SANGAM18, 

Meeting of Minds


SANGAM is the yearly event conduct by AIOUG every year. As name implies SANGAM event is the SANGAM of various Oracle Technology Gurus/Experts. Here, one can learn and explore Oracle Technologies under one roof. Moreover here participants can learn, meet, share and grow their network. 
I am very much happy to be part of this event. This is my fourth SANGAM event. Every year AIOUG team is bringing something new in this event. It was the 10th year of SANGAM even and very special to everyone. I am glad to be a part of SANGAM18 and AIOUG’s volunteer team.

In SANGAM18, we were having so many technical topics i.e. Database In-Memory , SQL optimizer , Upgrades , Machine Learning , Oracle Applications , Cloud migrations , Exadata , Apex , RAC , API’s, Blockchain, Docker, kubernetes and many more from the Masters of those topics, our speakers were,
Vipin Samar, Penny Avril, Tirthankar Lahiri, Sandesh Rao, Connor McDonald, Douglas Hood, Markus Michalewicz, Arup Nanda, Patrick Jolliffe, Chris Saxon, Luigi Villa, Debra Lilley, Gurmeet Goindi, Basheer Khan, Sai Janakiram Penumuru, Sai Pradeep Vattem, Vinay Mistry, Aman Sharma, Nitin Vengurlekar, Veeratteshwaran Sridhar, Abhinav Shroff, Chaitanya Koratamaddi, Deepak Sharma, Suresh Gandhi, Sasidhar Bala, Harjul Jobanputra, Abhijeet Rajurkar and many more...

Here I am sharing my SANGAM18 memories,




As this was 10th year anniversary of SANGAM event. We all volunteers decided to appreciate Sai Penumuru’s contribution towards AIOUG. Sai Penumuru is co-founder of AIOUG group and President of AIOUG. Sai’s dedication to run this group and seven local chapters is really very much appreciated. Sai is our Mentor, Supporter and his like our elder brother. Here we made trophy momento for Sai Penumuru.


We presented Trophy to Sai Penumuru

All Speakers and All Volunteers


I am very glad to share that I was also part of Speaker in SANGAM18 and I shared my knowledge on Optimizer Statistics, in which I covered all insight and out of optimizer statistics.




This year SANGAM also special because I received “AIOUG Outstanding Volunteer Award” by Connor Mcdonald. This is the most memorable moment of this year.
Many Thanks to Sai Penumuru and AIOUG team for giving me such opportunity to Speak in SANGAM18 and also to be part of volunteer team.





Also I am part of Volunteer team in Pune and Gujarat Chapter. Here we have received Outstanding Chapter Award. 



Expert Panel in SANGAM18


Closing Ceremony
Finally it was a time for SANGAM18 closing ceremony and we were having wonderful cake. I am very happy to be a part of this cake cutting. Vinay Me and Veera had cut the cake. I was very much excited and also little nervous at the same time.








Thank you for reading my blog.


Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Wednesday, December 19, 2018

SYSTEM Tablespace is Growing Abnormally in 12c

SYSTEM Tablespace is Growing Abnormally in 12c


My SYSTEM tablespace was growing rapidly. This was happening in my test environment. We observe that we were not doing much on this database. Only few users were working to test the application and doing approx 40-50 transaction per day. That's it.
There was no much load as well, still it's went up to 100gb. This seems to be some wired behavior .

Database version is 12cR1


Possibly there are few reasons for the same.

1)    Some has assigned default tablespace as SYSTEM
2)    Auditing is ON and consuming more space.
3)    SYS_LOB Objects uses lot of disk space
4)    Data dictionary objects uses lot of disk space

So now, how to find the root cause and what could be the solution?

Let’s query to dba_segments table to find the root cause. I came across few scenarios in my different databases. Here I am discussing those scenarios, what could be the problem and solution of it.

Use below query to find out what are those segments which are consuming lot of space in SYSTEM tablespace.
select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_mb
 from dba_segments
 where tablespace_name = 'SYSTEM'
 order by size_mb desc



Scenario 1


Here in above database my AUD$ table is growing rapidly and it is almost 30gb in size.

Problem
Auditing is enabled hence AUD$ table is growing very fast. Organization wants to do auditing and there is no option to disable it.

Solution
Few solutions which Oracle always recommended,
 ‘-  Move AUD$ to different tablespace
‘- Purge audit data
‘- Delete/truncate older data

Visit https://support.oracle.com  there are many notes available for this issue..

Scenario 2
Check below screenshot,



Here in 2nd scenario, ARGUMENT$, I_ARGUMENT1, I_ARGUMENT2, IDL_UB1$, SOURCE$ ; tables and indexes showing very huge in size.
These tables are core dictionary tables. Consider like these tables stores your database/schema/table procedure’s meta data information.

You cannot move those tables to another table space or truncate or reorg of these table. Oracle strictly says that don’t touch those tables as these are metadata tables. If you move or truncate tables, you might lose database information and this will corrupts your database as well.

So, is this the ideal scenario?
Can’t we control the growth of those tables like AUD$ tables?

Answer is, Yes, this is the ideal scenario and we don’t have control over it.

Scenario 3
SYSTEM tablespace growing unexpectedly after database upgrade OR after IMPDP

If you are upgrading your database from 11g to 12c, this might happen that you observer some unexpected growth of SYSTEM tablespace.

Here I would like to highlight that when you do the data import; import do lots of compilation of your function, procedure and packages. Metadata is associated with PL/SQL library. During compilation it generates duplicate rows in ARGUMENT$ table, which cause to huge size of ARGUMENT$ table.

This behavior found in 12c database due to new features introduced and reported as BUG. Here is the bug information.
Bug 5910872 : ARGUMENT$ DATA UNNECESSARILY DUPLICATED.

Solution
Here the workaround is to recompile the objects. This will decrease the logical space, not the physical space.
Run this command after impdp,
Example
1) alter session set events ='10946 trace name context forever, level 8454144';

2) exec utl_recomp.recomp_parallel('4','SCOTT');  

Scenario 4

Many a times it has been observed that data dictionary tables are occupying more space like BOOTSTRAP$, FILE$, UNDO$, IDL_UB1$, SOURCE$ etc.
Such issue found in Oracle Database 9i, 10g, 11g. Still I haven’t faced this type of issue in 12c or 18c.






Solution
To resolve this issue run catalog.sql and catproc.sql and re-query to dba_segments. This will work only in certain scenarios only, this is not for all.


Hope this note will help to understand the causes and solutions available for the same.



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Friday, November 9, 2018

SANGAM, India's Largest Oracle Users Group Conference

SANGAM18, India's Largest Oracle Users Group Conference

About Sangam (www.sangam18.info)


"Sangam, India's the largest independent Oracle user's group conference organized by All India Oracle Users Group (AIOUG). Every year Sangam is hosting 100+ in-depth technical sessions on new cutting-edge Oracle Technologies. SANGAM is a prestigious event that provides in-depth Technical Session, Live Demos, Hands on Labs, Technology Booths, Expert Talks, Fire Chats with Technology Gurus and many more.
In Sangam, Technology Experts and Gurus are sharing their knowledge and experience in person with participants. Over 1000+ participation every year which includes Database admin, Developers, Architects, Solution Designers, Technology Directors, Business Analysts, Delivery Heads etc."

I am very much thankful to @AIOUG @SaiPenumuru has given me a chance to speak in SANGAM18.




                                                           My Session Details 
 

Sangam18 session highlights from Key Speakers which gives tremendous knowledge, I would like to attend almost all sessions if possible.

Thanks to AIOUG for great efforts and bringing Oracle Experts and Gurus under one roof. 

Sangam18 Session Highlights from Key Speakers,
•    Threats to Your Data are Bigger Than They Appear by Vipin Samar
•    What to Expect from Oracle Autonomous Database by Penny Avril
•    Oracle Database: What's New and Coming Next by Penny Avril
•    DBAs versus Autonomous Databases by Julian Dontcheff
•    Hyperscale Oracle Database: Extreme Scaling without the Compromises by Tirthankar Lahiri
•    Oracle Database In-Memory [Bring Your Own Laptop] by Tirthankar Lahiri
•    Oracle Database In-Memory: When to Use and Getting Started by Tirthankar Lahiri
•    Database In-Memory: Under the Hood and What's Coming by Tirthankar Lahiri
•    The Database Strikes Back: Return of the SQL Jedi by Connor McDonald
•    Taming the beast. The 12c Optimizer by Connor McDonald
•    Building great apps with great SQL by Connor McDonald
•    Oracle Autonomous Health Framework to Preserve Performance and Rapidly Recover by Sandesh Rao
•    Applied Machine Learning for Database Autonomous Health by Sandesh Rao
•    Introducing new AIOps Innovations in Oracle 19c Autonomous Health Framework by Sandesh Rao
•    Oracle Exadata: Strategy and Roadmap for New Technologies, Cloud, and On-Premises by Gurmeet Goindi
•    Oracle Database In-Memory on Exadata: A Potent Combination by Gurmeet Goindi
•    Should I Drop Indexes in Exadata Cloud by Arup Nanda
•    Database Security in a Single Day in the Cloud by Arup Nanda
•    How Oracle Buffer Cache Works in Cloud by Arup Nanda
•    Cloud Applications – Back to Basics by Debra Lilley
•    Should I move to Cloud SaaS Applications? by Debra Lilley
•    Extensions in Oracle Applications Cloud (ERP & HCM) _ what can you do? by Debra Lilley
•    18(ish) Things You'll Love about Oracle Database 18c by Chris Saxon
•    An Open Chat with AskTom Team by Connor McDonald • Chris Saxon
•    Why Isn't My Query Using an Index? An Introduction to SQL Performance by Chris Saxon
•    How to Hack Your App Using SQL Injection by Chris Saxon
•    Oracle Real Application Clusters Roadmap for New Features by Markus Michalewicz
•    Oracle MAA (Maximum Availability Architecture) by Markus Michalewicz
•    (Oracle) DBA Skills to Have, to Obtain and to Nurture by Markus Michalewicz
•    Developing Applications on the World's Fastest Database by Douglas Hood
•    TimesTen Scaleout [Bring Your Own Laptop] by Douglas Hood
•    Using the World's Fastest Database in the Cloud by Douglas Hood
•    Database Driven Machine Learning by Douglas Hood
•    Turbocharge Analytical and OLTP workloads with Oracle Database In-Memory 18c [Bring Your Own Laptop] by Gavin Soorma
•    Upgrade and Migrate to Oracle Database 18c best practices for minimizing downtime by Gavin Soorma
•    SQL Tuning - Tools, Tips & Techniques by Vivek Sharma
•    Oracle Database Backups On Clouds- An Overview by Aman Sharma
•    Using Machine Learning to secure your company assets against modern cyber-attacks by Chetankumar Vithlani
 

Do you need more sessions, I think, you should visit Agenda, 
See here, https://sangam18.sched.com/

Let's joins us,


Date:- 7th & 8th December 2018
Venue:-
The Oterra,
Bengaluru



SANGAM18 at Glance

About SANGAM18

Registration Link
www.sangam18.info   => Registration





Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Wednesday, October 17, 2018

Set Autotrace Traceonly giving error SP2-0618 and SP2-0611 in Oracle 18c

Set Autotrace Traceonly giving error SP2-0618 and SP2-0611 in Oracle 18c


I was trying few things in Oracle 18c database using command window and needed autotrace enable for the same.

When i had given command "set autotrace traceonly" it has thrown error as below,


SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report



It seems something wired here.

Few steps need to follow to work autotrace in sqlplus.


1.       cd $oracle_home/rdbms/admin
2.       log into sqlplus as sys or system
3.       run SQL> @utlxplan
4.       run SQL> create public synonym plan_table for plan_table
5.       run SQL> grant all on plan_table to public
6.       exit sqlplus and cd $oracle_home/sqlplus/admin
7.       log into sqlplus as SYS
8.       run SQL> @plustrce
9.       run SQL> grant plustrace to public



Let's follow another steps.

$ cd $ORACLE_HOME/sqlplus/admin/
$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 17 12:40:27 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> alter session set container=testpdb1;
Session altered.

SQL> @plustrce
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;
Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.

SQL> grant select on v_$statname to plustrace;
Grant succeeded.

SQL> grant select on v_$mystat to plustrace;
Grant succeeded.

SQL> grant plustrace to dba with admin option;
Grant succeeded.

SQL>
SQL> set echo off
SQL>
SQL> grant plustrace to public;
Grant succeeded.



After following all above steps, now we are good with autotrace traceonly.

SQL> conn hr/hr@testpdb1
Connected.
SQL>
SQL> set autotrace traceonly
SQL>



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert