set serveroutput on VARIABLE cnt NUMBER -- Specifying both the SQL ID and the plan hash value: EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( - sql_id => '5abzqhtfcvr73' - ,plan_hash_value =>7104589 - ,fixed => 'YES' - ,enabled=>'YES'); -- without a specific plan hash value: EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( - sql_id => '5abzqhtfcvr73' - ,fixed => 'YES' - ,enabled=>'YES'); print :cnt; exit
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Thursday, March 15, 2018
How to load a plan from the cursor cache into the SMB
Wednesday, March 14, 2018
How to recursively zip a folder and its subfolders and add password protetion+encryption
Below I am compressing all files and subfolders in the folder /home/oracle/outputfiles:
You will be prompted for a password, which has to be verified.
If you are located in the parent directory, use
The .zip extension will be added to myzipfile automatically.
cd /home/oracle/outputfiles zip -r --encrypt myzipfile.zip *
You will be prompted for a password, which has to be verified.
If you are located in the parent directory, use
zip -r -q myzipfile mydirwhere myzipfile is the name of the resulting zip file, and mydir is the name of the directory.
The .zip extension will be added to myzipfile automatically.
Tuesday, February 27, 2018
How to install APEX in an existing database
I have based my receipe on Tim Hall's instructions found at www.oracle-base.com, and I do not want to take credit for this. But the installation steps was executed by myself, and my notes may come in handy for others.
In my case, a developer needed access to the APEX_JSON package, and as confirmed with Oracle Development, the package is not available outside the APEX software stack. However, when APEX is installed in an existing database, you can start using the functionality in this package even if you don't need anything else that comes with APEX.
Here is how I performed the minimal installation:
1. Create a dedicated tablespace:
create bigfile tablespace apex datafile '/u02/oradata/testdb01/apex.dbf' size 128M autoextend on next 32M maxsize unlimited; |
2. Download the latest APEX installation bundle from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html#close, and upload it to your server
3. Unzip the file
4. change directory to the apex folder
5. Install the software.
The header of the apexins.sql file states its usage:
Arguments: Position 1: Name of tablespace for Application Express application user Position 2: Name of tablespace for Application Express files user Position 3: Name of temporary tablespace or tablespace group Position 4: Virtual directory for APEX images
sqlplus / as sysdba @apexins.sql APEX APEX TEMP /i/ |
6. Set the admin password:
sqlplus / as sysdba @apxchpwd.sql |
7. Create the APEX_LISTENER and schema APEX_REST_PUBLIC_USER:
sqlplus / as sysdba @apex_rest_config.sql |
There are additional, optional steps to be configured, depending on your needs. See this article at Oracle-base.com
Monday, February 26, 2018
How to set up End-to-End tracing
Based on Performing Application Tracing in Oracle Database 12.1 SQL Tuning Guide
Add the following to your application code:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'PublishInfoModule'; exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>'BroadcastPublish'); exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'Broadcast', action_name=>NULL);
Right before a SELECT statement:
exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'GetRows');
Right before an UPDATE statement:
exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'MarkAsPublished');
Enable statistics gathering for End-to-End Tracing. Note that the names of service, module and actions are case sensitive, and need to be referred to as they are found in v$sessions and v$services:
exec DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name=>'BroadcastPublish'); exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name =>'MarkAsPublished', WAITS=>TRUE,binds=>TRUE,plan_stat=>'FIRST_EXECUTION');
Go to the trace file location, found as a subdirectory in the location specified by diagnostic_dest:
show parameter diagnostic_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /u01/oracle
In my case:
cd /u01/oracle/diag/rdbms/proddb01/proddb01/traceYou should now find files named according to your SET TRACEFILE_IDENTIFIER command previously:
-rw-r----- 1 oracle dba 732258822 Feb 26 07:49 alert_proddb01.log -rw-r----- 1 oracle dba 24398 Feb 26 08:21 proddb01_ora_54248.trc -rw-r----- 1 oracle dba 120418 Feb 26 08:21 proddb01_ora_54248.trm -rw-r----- 1 oracle dba 3077570 Feb 26 08:26 proddb01_ora_35967_PublishInfoModule.trm -rw-r----- 1 oracle dba 133643579 Feb 26 08:26 proddb01_ora_35967_PublishInfoModule.trc
Disable statistics gathering, and disable the trace:
exec DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name=>'BroadcastPublish'); exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name =>'MarkAsPublished');
You can now format these using tkprof:
tkprof /u01/oracle/diag/rdbms/proddb01/proddb01/trace/proddb01_ora_35967_PublishInfoModule.trc /u01/oracle/admin/proddb01/sql/tuning/tkprof2/tkprof_2.out waits=yes sort=EXEDSK insert=insert.sql sys=no explain=broadcaster/**** record=tkprof_sqlstmts.sql table=sys.PLAN_TABLE$
The output will be the file /u01/oracle/admin/proddb01/sql/tuning/tkprof2/tkprof_2.out
tkprof has many options and you should take some time to get familiarized with the tool before you start. A key point is to minimize the information so that you find what's relevant to you.
For a shortlist of commands, see this post.
If you have many trace files generated by multiple sessions, you need to use another tool called trcsess to merge all these into one single trace file that you then format with tkprof, which I will demonstrate next.
In the following example, I am creating tracefiles for a specific action set in the application code.
The application code uses this code:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Reporter';
BEGIN DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'getNextOutlook/actions/report'); END; /
So I will execute
cd trace trcsess output=getNextOutlook.out action='getNextOutlook/actions/report' pks_*report*.trc
Thursday, February 22, 2018
DST_UPGRADE_STATE set to DATAPUMP(1) when running preupgrade_fixups.sql
During the preparation for a 12.2 upgrade, I found that the preupgrade-tool reported about an incomplete DST upgrade, where DST_UPGRADE_STATE was set to "DATAPUMP(1)".
The preupgrade tool reported:
Then, running the preupgrade_fixups.sql:
Result:
According to https://blog.oracle-ninja.com/2013/09/17/stuck-timezone-upgrades-and-smart-scans the solution would be, as sysdba:
1. ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';
2. exec dbms_dst.unload_secondary;
I tried this, with the following result:
I also executed
just to be on the safe side.
Finally, run preupgrade_fixups.sql again:
And the test is passed, and you are ready to upgrade
The preupgrade tool reported:
$ORACLE_HOME/jdk/bin/java -jar /tmp/preupgradetool/preupgrade.jar TERMINAL TEXT Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Complete any pending DST update operation before starting the database upgrade. There is an unfinished DST update operation in the database. It's current state is: DATAPUMP(1) There must not be any Daylight Savings Time (DST) update operations pending in the database before starting the upgrade process. Refer to My Oracle Support Note 1509653.1 for more information.
Then, running the preupgrade_fixups.sql:
Fixup Check Name Status Further DBA Action ---------- ------ ------------------ purge_recyclebin Passed None pending_dst_session Failed Manual fixup required. invalid_objects_exist Failed None dictionary_stats Passed None
Result:
SYS@proddb01 SQL> r 1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 2 FROM DATABASE_PROPERTIES 3 WHERE PROPERTY_NAME LIKE 'DST_%' 4* ORDER BY PROPERTY_NAME PROPERTY_NAME VALUE ---------------------------------------- -------------------- DST_PRIMARY_TT_VERSION 18 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE DATAPUMP(1)
According to https://blog.oracle-ninja.com/2013/09/17/stuck-timezone-upgrades-and-smart-scans the solution would be, as sysdba:
1. ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';
2. exec dbms_dst.unload_secondary;
I tried this, with the following result:
SYSproddb01 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE ---------------------------------------- -------------------- DST_PRIMARY_TT_VERSION 18 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
I also executed
ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, OFF';
just to be on the safe side.
Finally, run preupgrade_fixups.sql again:
Check Name Status Further DBA Action ---------- ------ ------------------ purge_recyclebin Passed None pending_dst_session Passed None invalid_objects_exist Failed None dictionary_stats Passed None PL/SQL-prosedyren ble fullført.
And the test is passed, and you are ready to upgrade
Roles necessary to administer and view the Unified Audit Trail
Oracle 12c provides two new roles for auditing purposes:
AUDIT_ADMIN for audit configuration/administration
AUDIT_VIEWER for viewing and analyzing audit data
The following can role was granted by myself to allow a senior developer to view the audit information generated in a test database:
https://docs.oracle.com/database/121/DBSEG/release_changes.htm#DBSEG000
https://docs.oracle.com/database/121/DBSEG/auditing.htm#DBSEG617
AUDIT_ADMIN for audit configuration/administration
AUDIT_VIEWER for viewing and analyzing audit data
The following can role was granted by myself to allow a senior developer to view the audit information generated in a test database:
grant audit_viewer to scott;
https://docs.oracle.com/database/121/DBSEG/release_changes.htm#DBSEG000
https://docs.oracle.com/database/121/DBSEG/auditing.htm#DBSEG617
Thursday, February 15, 2018
How to use dbms_application_info to create session details
The package dbms_application_info lets you add supplemental information in your session, which can be picked up in the V$SESSION view.
A very useful package, which I have used many times myself, but for some reason it is rarely used by Developers. It should typically be called before beginning a transaction in order to register and name a transaction/client_info/module for later use when measuring performance across an application.
exec dbms_application_info.set_client_info('Step one.'); exec dbms_application_info.set_module('module1','gettrades');
Here is an example where I was troubleshooting a "hanging" delete statement for a customer.
Turned out the problem was concurrency locks:
select SID,SERIAL#,SQL_ID,STATUS,osuser,machine,schemaname,MODULE,ACTION,CLIENT_INFO,LOGON_TIME,EVENT,STATE,FINAL_BLOCKING_SESSION "blocking" from v$session WHERE schemaname='SALES' AND STATUS='ACTIVE' UNION select SID,SERIAL#,SQL_ID,STATUS,osuser,machine,schemaname,MODULE,ACTION,CLIENT_INFO,LOGON_TIME,EVENT,STATE,FINAL_BLOCKING_SESSION "blocking" from v$session where SID = (SELECT FINAL_BLOCKING_SESSION FROM V$SESSION WHERE schemaname='SALES' AND STATUS='ACTIVE');
In the resulting two rows, the first one is mine, the second is the blocking session:
SID | SERIAL# | SQL_ID | STATUS | OSUSER | MACHINE | SCHEMANAME | MODULE | ACTION | CLIENT_INFO | LOGON_TIME | EVENT | STATE | blocking |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
32 | 4759 | 5jg1839cyxzgh | ACTIVE | oracle | mytestserver.com | SALES | DELETE MODULE | DELETE_SCHEMA | Delete XSD. | 15.02.2018 12:04:43 | library cache lock | WAITING | 2285 |
2285 | 48625 | g3bc37vx8fy3u | INACTIVE | JIM | COMPANY\PC-157 | SALES | SQL*Plus | 14.02.2018 14:08:57 | SQL*Net message from client | WAITING | NULL |
For more information, check the documentation
Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1) at My Oracle Support.
Subscribe to:
Posts (Atom)