The 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;
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.
During 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.
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 18.104.22.168.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 22.214.171.124.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.
Starting 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.
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.
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 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:
- MS SQL
- DB2, etc.
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 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.
Oracle Database 12.1 shows information on applied patches in the alert log during instance startup.
A question come up: The msg info in alert.log (below) is about patch applied on OH only or this information is related to patch applied on Database?
alert.log ... 2016-09-01 18:07:52.476000 -03:00 =========================================================== Dumping current patch information =========================================================== Patch Id: 23144544 Patch Description: DATABASE BUNDLE PATCH: 126.96.36.199.160719 (23144544) Patch Apply Time: 2016-08-31 17:44:09 GMT-03:00 ...
To get the answer I installed a OH 12.1 without create a database and apply the DATABASE BUNDLE PATCH: 188.8.131.52.160719.
I created a CDB database with DBCA using template General purpose or transaction processing.
During a database creation was looking at alert log and I noticed two things.
First the alert log show:
=========================================================== Dumping current patch information =========================================================== No patches have been applied ===========================================================
Then at end of database creation alert log show:
... 2016-09-01 18:07:52.476000 -03:00 =========================================================== Dumping current patch information =========================================================== Patch Id: 23144544 Patch Description: DATABASE BUNDLE PATCH: 184.108.40.206.160719 (23144544) Patch Apply Time: 2016-08-31 17:44:09 GMT-03:00 ...
The patch has been applied on my database during database creation?
The anwser is NO.
To my surprise, the “Dumping current patch information” is about Patches applied on OH only.
Why “No Patches” the after some time “Show Patches”?
The patch information is populated by calling DBMS_QOPATCH. It is populated if the database was started in a open mode, as it’s not possible to execute any package with database not opened in read-write.
At first moment database was started in a non-open mode , then NO PATCHES, when database was started in OPEN mode then alertlog was pouplated with the info.
Why this can lead a misinterpretation?
DBMS_QOPATCH was introduced in 12c to query about Patch information applied on database, but the info on alert.log is not about patch applied on database, the info is about patch applied on OH only, it can lead us to think wich database have listed patch on alertlog applied.
(I think this is some bug or something else, because it makes no sense to me)
What is the safe mode to check if database have a patch applied?
conn as sysdba SQL> set serverout on SQL> exec dbms_qopatch.get_sqlpatch_status; PL/SQL procedure successfully completed.
No outuput then No Patch applied.
After create database I checked if patch was applied and none patch was applied, but the “Dumping current patch information” show all applied patch in the alertlog at startup of instance.
How to apply a BP or PSU on Database 12c?
By executing datapatch utility.
./datapatch -h SQL Patching tool version 220.127.116.11.0 on Thu Sep 1 19:35:49 2016 Copyright (c) 2016, Oracle. All rights reserved. sqlpatch usage: All arguments are optional, if there are no arguments sqlpatch will automatically determine which SQL scripts need to be run in order to complete the installation of any SQL patches. Optional arguments: -db <db name> Use the specified database rather than $ORACLE_SID -bundle_series <bundle_series> Specify if the patch is a bundle patch Should also be accompanied by -force option if -bundle_series option is specified,only 1 patch will be considered by the -force command -apply <patch1,patch2,...,patchn> Only consider the specified patch list for apply operations -rollback <patch1,patch2,...,patchn> Only consider the specified patch list for rollback operations -upgrade_mode_only Only consider patches that require upgrade mode -force Run the apply and/or rollback scripts even if not necessary per the SQL registry -pdbs <pdb1,pdb2,...,pdbn> Only consider the specified list of PDBs for patching. All other PDBs will not be patched -prereq Run prerequisite checks only, do not actually run any scripts -oh <oracle_home value> Use the specified directory to check for installed patches -verbose Output additional information used for debugging -help Output usage information and exit -version Output build information and exit
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Let’s apply the patch.
cd $ORACLE_HOME/OPatch ./datapatch -verbose
Suprise: When execute ./datapatch -verbose it has no warning or confirmation, the datapatch automatically will apply patch in all CDBs and in all opened PDBs of current OH environment.
The same concern that we have about “dd” command, we must have about datapatch command. Hope Oracle fix it.
./datapatch -verbose SQL Patching tool version 18.104.22.168.0 on Thu Sep 1 18:28:47 2016 Copyright (c) 2016, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/ sqlpatch_23003606_2016_09_01_18_28_47/sqlpath_invocation.log Connecting to database...OK Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) Bootstrapping registry and package to current versions...done Determining current state...done Current state of SQL patches: Bundle series DBBP: ID 160719 in the binary registry and not installed in any PDB Adding patches to installation queue and performing prereq checks... Installation queue: For the following PDBs: CDB$ROOT PDB$SEED Nothing to roll back The following patches will be applied: 23144544 (DATABASE BUNDLE PATCH: 22.214.171.124.160719 (23144544)) Installing patches... Patch installation complete. Total patches installed: 2 Validating logfiles... Patch 23144544 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_ PRDCDB_CDBROOT_2016Sep01_18_29_26.log (no errors) Patch 23144544 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_ PRDCDB_PDBSEED_2016Sep01_18_32_36.log (no errors) SQL Patching tool complete on Thu Sep 1 18:34:54 2016
datapatch always validate if a BP or PSU already was applied as show below:
./datapatch -verbose SQL Patching tool version 126.96.36.199.0 on Thu Sep 1 18:35:21 2016 Copyright (c) 2016, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/ sqlpatch_23593462_2016_09_01_18_35_21/sqlpatch_invocation.log Connecting to database...OK Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) Bootstrapping registry and package to current versions...done Determining current state...done Current state of SQL patches: Bundle series DBBP: ID 160719 in the binary registry and ID 160719 in PDB CDB$ROOT, ID 160719 in PDB PDB$SEED Adding patches to installation queue and performing prereq checks... Installation queue: For the following PDBs: CDB$ROOT PDB$SEED Nothing to roll back Nothing to apply SQL Patching tool complete on Thu Sep 1 18:35:53 2016
Checking what patches have been applied (or rolled back) on database
SQL> set serverout on SQL> exec dbms_qopatch.get_sqlpatch_status; Patch Id : 23144544 Action : APPLY Action Time : 01-SEP-2016 18:34:46 Description : DATABASE BUNDLE PATCH: 188.8.131.52.160719 (23144544) Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_PRDCDB_CDB ROOT_2016Sep01_18_29_26.log Status : SUCCESS PL/SQL procedure successfully completed.
- Patch Information in Alert Log is about OH only
- Creating a Database a CDB or Non-CDB database using a default Template it does not apply any Patch, you must apply patch manually after database creation.
- datapatch utility is dangerous because has no warning or confirmation when executed.
The Oracle Grid Infrastructure software owner (typically, grid) must be a member of the OSDBA group. Membership in the OSDBA group enables access to the files managed by Oracle ASM. If you have a separate OSDBA group for Oracle ASM, then the Oracle Restart software owner must be a member of the OSDBA group for each database and the OSDBA group for Oracle ASM.
If grid user is not a member of dba os group, then DBCA will fails at end of database creation and DBCA will perform rollback of database creation (database will not be created).
The error below will be raised:
srvctl start database -d prdcdb PRCR-1079 : Failed to start resource ora.prdcdb.db ORA-01017: invalid username/password; logon denied CRS-5017: The resource action "ora.prdcdb.db start" encountered the following error: ORA-01017: invalid username/password; logon denied . For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/db-oracle/crs/ trace/ohasd_oraagent_grid.trc".
To fix the issue you must add user “grid” to OSDBA group “dba” and relink RDBMS Binaries.
# id grid uid=205(grid) gid=202(oinstall) groups=208(asmdba),209(asmadmin),210(asmoper) # usermod -a -G dba grid (linux) # id grid uid=205(grid) gid=202(oinstall) groups=203(dba),208(asmdba),209(asmadmin), 210(asmoper) # su - oracle oracle@db-oracle:/home/oracle> export AIXTHREAD_SCOPE=S oracle@db-oracle:/home/oracle> export ORACLE_BASE=/u01/app/oracle oracle@db-oracle:/home/oracle> export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/ dbhome_12102 oracle@db-oracle:/home/oracle> export PATH=$ORACLE_HOME/bin:$PATH oracle@db-oracle:/home/oracle> relink all writing relink log to: /u01/app/oracle/product/12.1.0/dbhome_12102/ install/relink.log
If there are no errors reported in $ORACLE_HOME/install/relink.log, then retry to create the database with dbca.
Oracle Grid Infrastructure is supported on Operation System, but ACFS isn’t. Why?
The Grid Infrastructure is a Software that use OS Library, so Grid Infrastructure isn’t Kernel dependent but OS Package dependent.
ACFS is a module of Grid Infrastructure that have drivers installed/configured into OS Kernel, then ACFS is Kernel Dependent.
An Oracle ACFS file system is installed as a dynamically loadable vendor operating system (OS) file system driver and tool set that is developed for each supported operating system platform. The driver is implemented as a Virtual File System (VFS) and processes all file and directory operations directed to a specific file system.
The ACFS is composed by three components:
The Oracle ACFS, Oracle Kernel Services (OKS) and Oracle ADVM drivers, they are dynamically loaded when the Oracle ASM instance is started.
- Oracle ACFS
This driver processes all Oracle ACFS file and directory operations.
- Oracle ADVM
This driver provides block device services for Oracle ASM volume files that are used by file systems for creating file systems.
- Oracle Kernel Services Driver (OKS)
This driver provides portable driver services for memory allocation, synchronization primitives, and distributed locking services to Oracle ACFS and Oracle ADVM.
Before upgrade your OS Kernel you must place into your check list the ACFS Drivers, as you do with others OS Drives.
How to Oracle support the ACFS into future Kernels?
By releasing Patch Set Updates (PSUs), they are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule.
How it’s works?
Check this EXAMPLE below:
Note: This table is only a example where these kernel and dates are fictitious.
On image above we can see.
In jan/2016 the GI 184.108.40.206 and 220.127.116.11 don’t need apply PSU because already have supported drives into base release, but 18.104.22.168 need PSU 22.214.171.124.2 to get supported drivers to kernel 2.6.32-100.
In Feb/2016 was released the kernel 2.6.65-30, so ACFS Deployment take some time until developers build new ACFS Drives. So, in FeB/2016 none release is supported for the kernel 2.6.65-30.
In Mar/2016 Oracle release the PSU where all ACFS versions was supported under that PSU.
With example above we can conclude: Does not matter what Grid Infrastructure base version (such as 126.96.36.199 or 188.8.131.52 ) you are using, what matter is, the Grid Infrastructure must have supported ACFS Drivers for that Kernel.
Where to find Certification Matrix for ACFS ?
Oracle Support have a detailed MOS Note : ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)
You need ALWAYS check above mos note before any kernel updates for environments that have ACFS Configured.