Why Oracle named 18c instead 13c ??!!! What is missing here?
Posted: 12/07/2018 Filed under: Oracle | Tags: 18c, oracle Leave a commentWhy Oracle decided jump Oracle Database Version from 12c to 18c, what is missing here?
Things we need to know.
Oracle RDBMS has two names Oracle Database Version (marketing name) and Oracle Release Version (technical name).
The “marketing name” is announced on Oracle Openword and when you buy a Oracle Database.
e.g Oracle 9i (i =Internet), 11g (g=Grid Computing), 12c (c = Cloud Computing)
The “technical name” is used when you need perform admins task such as (deploy, technical support, etc).
e.g RDMBS 11.2.0.4, 9.2.0.4, 12.2.0.2, etc
Changes on Oracle Database Version
Until Oracle 11g Release 1 things worked as follows.
The release of Oracle Database Version (e.g Oracle 9i Release 2) have a Initial Release Version (e.g 9.2.0.1) and to upgrade to another release you had to apply the patchset (e.g 9.2.0.2) on this initial release.
Starting with Oracle 11g Release 2 patchset are full release (what’s mean no patchset). You don’t need to install initial release (e.g 11.2.0.1) to use later releases (e.g 11.2.0.4). You can directly to install the desired release.
Starting with Oracle Release 12c Release 2 the new releases will be annual and the version will be the last two digits of the release year.
So, means that 18c stand for release of year 2018 ?
Yes. The Oracle 18c is Oracle full “patchset” release 12.2.0.2, the Oracle Release 12.2.0.1 still named as Oracle 12c Release 2.
The Oracle release 12.2.0.3 planned to be released on 2019 will be Oracle 19c and so on until Oracle decided change it.
What is missing here?
The real reason behind scenes.
The Oracle 12.2.0.1 until 12.2.0.3 will be treated under the umbrella of 12.2 for Lifetime Support purposes.
The expectation is for Oracle Database 19c to be the last release (“long term support” release) for 12.2.
Oracle really care about $$$$.
Release Schedule of Current Database Releases (Doc ID 742060.1)
ORA-20200: The instance was shutdown between snapshots – Solved!!!
Posted: 11/01/2018 Filed under: 11g R1, 11g R2, 12c R1, Database, Oracle, Performance Tuning Leave a commentThe AWR Report is only generated using snapshots from period that instance was Started. If any shutdown occurrs it break stats and AWR can’t generate a report comparing a period where stats belong a old Instance Startup.
This occurrs because Instance stats is not persistent accross reboots, (as the name says is a Instance), so all stats get reseted in every reboot.
When generating reports between hours is easy identify when instance was started, but when generating awr reports between many days this become a painfull task if instance was restarted multiples times during a desired period.
How to find the best Interval to Generate your AWR Reports?
To Single Instance this can be done using bellow query:
SET LINESIZE 200
SET PAGESIZE 200
UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999
SELECT
s.startup_time,
di.instance_name,
MIN(snap_id) snap_start,
MAX(snap_id) snap_end,
MIN(end_interval_time) start_interval,
MAX(end_interval_time) end_interval,
EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
|| ' Days(s) '
|| EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
|| ' Hour(s) '
|| EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
|| ' Minute(s) ' range_interval,
MAX(snap_id) - MIN(snap_id) qtd_snaps
FROM
dba_hist_snapshot s,
dba_hist_database_instance di
WHERE
di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy'
) - (&num_days - 1) )
GROUP BY
s.startup_time,
di.instance_name
ORDER BY
startup_time ASC;
Output:
Sqlplus:
https://levipereira.files.wordpress.com/2018/01/sql_awr.jpg
Sqldeveloper
https://levipereira.files.wordpress.com/2018/01/sql_awr1.jpg
In above output is easy identify what SNAP_ID to use without keep trying and getting ORA-20200 or by reading a huge list of snaps.
The above query is NOT valid to get SNAP_ID to generate AWR Global RAC Report.
Soon as possible I will post a Query to generate same output to easy our life when generating AWR Global RAC Report.
Hope its helps.
Upgrade Oracle Database from 12.1 to 12.2 “ORA-20001: MISSING GRANT”
Posted: 17/04/2017 Filed under: 12c R2, Database, Database Upgrade, Oracle 1 CommentDuring upgrade of Database from 12.1 to 12.2 I received a error during upgrade, I will share the solution since I didn’t not find nothing about it on MOS and Internet (googling).
I don’t know if this is a relevant info, but when I installed the Oracle 12.1 I performed a uninstall of APEX from the CBD and it was installed on PDB only.
Using procedure from below link.
https://oracle-base.com/articles/12c/multitenant-uninstall-apex-from-the-cdb-12cr1
SYMPTOMS
Severe errors encountered during exection of “PDBS Recompile Invalid Objects”
The errors was found on log file “/u01/app/oracle/cfgtoollogs/dbua/upgrade2017-04-17_07-39-02-PM/prdcdb/PDBSUtlprp2R0.log”.
SQL> Rem ===================================================================== SQL> Rem Run component validation procedure SQL> Rem ===================================================================== SQL> SQL> SET serveroutput on SQL> EXECUTE dbms_registry_sys.validate_components; ...(22:53:11) Starting validate_apex for APEX_050100 ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_050100 ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_050100 ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_050100 ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_050100 ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_050100 ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_050100 ...(22:53:11) Checking missing sys privileges ...(22:53:11) Recompiling ...(22:53:11) Checking for objects that are still invalid ...(22:53:12) Key object existence check ...(22:53:12) Setting DBMS registry for APEX to INVALID ...(22:53:12) Exiting validate_apex PL/SQL procedure successfully completed.
How do I Fix it:
$ sqlplus / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter session set container=COMMON_APPS; Session altered. SQL> grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_050100; Grant succeeded. SQL> grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_050100; Grant succeeded. SQL> grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_050100; Grant succeeded. SQL> grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_050100; Grant succeeded. SQL> grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_050100; Grant succeeded. SQL> grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_050100; Grant succeeded. SQL> grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_050100; Grant succeeded. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Click and Retry and errors is gone.
SQL> Rem ===================================================================== SQL> Rem Run component validation procedure SQL> Rem ===================================================================== SQL> SQL> SET serveroutput on SQL> EXECUTE dbms_registry_sys.validate_components; ...(22:59:11) Starting validate_apex for APEX_050100 ...(22:59:11) Checking missing sys privileges ...(22:59:11) Recompiling ...(22:59:12) Checking for objects that are still invalid ...(22:59:12) Key object existence check ...(22:59:13) Setting DBMS Registry for APEX to valid ...(22:59:13) Exiting validate_apex PL/SQL procedure successfully completed.
The upgrade finished successful.
Changes Installing Oracle Grid Infrastructure 12.2
Posted: 17/04/2017 Filed under: 12c R2, Clusterware / Grid Infrastructure, Oracle Leave a commentStarting with Oracle Grid Infrastructure 12c Release 2 (12.2), Oracle Grid Infrastructure software is available as an image file for download and installation.
You must extract the image software into the directory where you want your Grid home to be located, and then run the gridSetup.sh (no more runInstaller) script to start Oracle Grid Infrastructure installation.
Before Install (Important):
Since it’s only a image and you just unpack it, if download is corrupted then you will get a corrupted Oracle Home.
Then I recommend you perform a check before.
Example:
grid@lpi-oracle:/u01/mount> unzip -t aixppc64_12201_grid_home.zip |grep -v OK Archive: aixppc64_12201_grid_home.zip No errors detected in compressed data of aixppc64_12201_grid_home.zip.
How to Install
Just unpack the zip image on new OH.
If you unpack on /tmp/grid and run gridSetup.sh the new OH will be /tmp/grid.
grid@lpi-oracle:/u01/mount> unzip aixppc64_12201_grid_home.zip -d /u01/app/grid/product/12.2.0/grid/ creating: /u01/app/grid/product/12.2.0/grid/OPatch/ inflating: /u01/app/grid/product/12.2.0/grid/OPatch/README.txt creating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/ creating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/ creating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/bin/ inflating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/bin/opatchauto.sh inflating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/bin/opatchautoCopy.sh . . . finishing deferred symbolic links: /u01/app/grid/product/12.2.0/grid/bin/lbuilder -> ../nls/lbuilder/lbuilder /u01/app/grid/product/12.2.0/grid/javavm/admin/cbp.jar -> ../../javavm/jdk/jdk8/admin/cbp.jar /u01/app/grid/product/12.2.0/grid/javavm/admin/classes.bin -> ../../javavm/jdk/jdk8/admin/classes.bin /u01/app/grid/product/12.2.0/grid/javavm/admin/lfclasses.bin -> ../../javavm/jdk/jdk8/admin/lfclasses.bin /u01/app/grid/product/12.2.0/grid/javavm/admin/libjtcjt.so -> ../../javavm/jdk/jdk8/admin/libjtcjt.so /u01/app/grid/product/12.2.0/grid/javavm/lib/jce.jar -> ../../javavm/jdk/jdk8/lib/jce.jar /u01/app/grid/product/12.2.0/grid/javavm/lib/security/US_export_policy.jar -> ../../../javavm/jdk/jdk8/lib/security/US_export_policy.jar /u01/app/grid/product/12.2.0/grid/javavm/lib/security/cacerts -> ../../../javavm/jdk/jdk8/lib/security/cacerts /u01/app/grid/product/12.2.0/grid/javavm/lib/security/java.security -> ../../../javavm/jdk/jdk8/lib/security/java.security /u01/app/grid/product/12.2.0/grid/javavm/lib/security/local_policy.jar -> ../../../javavm/jdk/jdk8/lib/security/local_policy.jar /u01/app/grid/product/12.2.0/grid/javavm/lib/sunjce_provider.jar -> ../../javavm/jdk/jdk8/lib/sunjce_provider.jar /u01/app/grid/product/12.2.0/grid/jdk/jre/lib/ppc64/classic/libjvm.a -> libjvm.so /u01/app/grid/product/12.2.0/grid/jdk/jre/lib/ppc64/j9vm/libjvm.a -> libjvm.so /u01/app/grid/product/12.2.0/grid/jdk/jre/lib/ppc64/libjsig.a -> libjsig.so /u01/app/grid/product/12.2.0/grid/lib/libjavavm12.a -> ../javavm/jdk/jdk8/lib/libjavavm12.a /u01/app/grid/product/12.2.0/grid/lib/libodm12.so -> libodmd12.so
You can’t change ORACLE HOME during installation, you can set ORACLE_BASE only.
Note: Download and copy the Oracle Grid Infrastructure image files to the local node only. During installation, the software is copied and installed on all other nodes in the cluster.
Another new important Feature.
Starting with Oracle Grid Infrastructure 12c Release 2 (12.2), Oracle Grid Infrastructure installer supports the option of deploying Oracle Domain Services Clusters and Oracle Member Clusters.
Two ways to Deploying a Grid Installation for a Cluster:
Oracle Standalone Cluster – Default Installation of Oracle Clusterware
Oracle Cluster Domain – If you have multiples Oracle Clusterware Installation in your infraestructure and want manage it as single Cluster, then you must start think deploy a Cluster Domain.
Multiple cluster configurations are grouped under an Oracle Cluster Domain for management purposes and make use of shared services available within that Oracle Cluster Domain. The cluster configurations within that Oracle Cluster Domain include Oracle Domain Services Cluster and Oracle Member Clusters.
Later I will create a new post explaining the new installation feature Oracle Domain Services Clusters and Oracle Member Clusters.
Plugins to Monitor Oracle with Zabbix
Posted: 20/10/2016 Filed under: Oracle 1 CommentZBXORA
Zabbix Oracle monitoring plugin Downloadable from https://github.com/ikzelf/zbxora
Written in python, tested with python 2.6 and 2.7. Using cx_Oracle purpose is monitoring an Oracle database in an efficient way. Optionally calling zabbix_sender to upload data
Supports Oracle 9,10,11,12 RAC,asm and plugin databases Tested with Oracle 11,12 RAC,standby,asm and plugin databases
ZabbixDBA
ZabbixDBA is fast, flexible, and continuously developing plugin to monitor your RDBMS.
ZabbixDBA uses threading of DBI connections which is good for monitoring of multiple database instances simultaneously. Just configure it, run daemon and it will do all the job. Currently there are template and query set only for Oracle database, but Perl DBI supports any type of RDBMS:
- Oracle
- MySQL
- MS SQL
- PostgreSQL
- DB2, etc.
DABABIX
DaBaBix is a Zabbix database monitoring agent. It combines the features of postbix and orabbix, but has been rewritten and improved to monitor different database types (currently postgre/mysql/mssql/oracle) and instances with a single daemon instance based on apache daemons (unix/windows service).
ORABBIX
Orabbix was made to monitor oracle instances with zabbix, with this you are going to acquire data from every oracle instances that you want to monitor and then zabbix server is going to produce graphs and collect data. For most of data collected are present some trigger that send mail for each trouble funded or performance problem. It’s incredibly useful to collect data and produce SLA or to have a workload history of your DB.
Levi Pereira | Oracle Blog 
