Thursday, November 2, 2017

What does it mean to "evolve" a SQL baseline?

Simply put, "evolving" an SQL baseline means testing whether or not an unaccepted plan performs better than any current plan in the plan baseline.

You can use Cloud Control or the package dbms_spm to evolve a plan.


Read more in the Oracle Documentation

Wednesday, November 1, 2017

How to purge a cursor from the shared pool using dbms_shared_pool

Find the address and hash value for the particular SQL you're looking for:
select address, hash_value from v$sqlarea where sql_id like 'bjkd7pcyfns86';


ADDRESS HASH_VALUE
0000000085CD2838
1021993222

Execute the purge procedure:
SQL> exec dbms_shared_pool.purge('0000000085CD2838, 1021993222','C');

Note that the name of the object is the combined values of address and hash value from v$sqlarea.
The value C is what you want to use for cursors.

Note that even if an SQL Plan Baseline is dropped from the SMB, it doesn't mean it won't get used if it is still in the cache.

If you have dropped a plan from your SMB that you would like the optimizer to ignore, you must clear out the cursor that previously using the now dropped SQL Plan baseline from the shared pool using the command above. 

Wednesday, October 25, 2017

How to use the find command on Unix/Linux systems


Here is how to list the regular files (indicated by -type f) files generated the last 30 minutes in a particular directory on a Linux/Unix server:

find . -mmin -30 -type f -exec ls -l {} +

Another example, listing files in my audit_file_dest older than 10 days:
find /u01/admin/proddb01/adump/ -name '*.aud' -mtime +10 -exec ls -latrh {} \;

Count all files with extension ".trm" generated one day or less ago:
find . -name '*.trm' -mtime -1 -type f -exec ls -l {} + | wc -l

Count all files with extension ".trm" generated more than 30 days ago:
find . -name '*.trm' -mtime +30 -type f -exec ls -l {} + | wc -l

Find all files with timestamp new than April 15th, 16:14 in the afternoon:
export TIMENOW=`date +"%a %b %e %H:%M"`
echo $TIMENOW
Thu Apr 15 16:14
find . -type f -newermt "$TIMENOW" -exec ls -la {} \;

Find and delete can be done in different ways, the recommended way is to use the -delete option. In the command below, I want to find and delete all *.trc and *.trm files older than 10 days:
find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +10 -delete

Delete all *.trm files in the current directory generated earlier than 24 hrs ago:
find *.trm -mmin +1440 -type f -delete

If the -delete option is not available, you can use a different method:
find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +10 -exec rm -f {} \;

Or, using xargs, finding files older than 2 days:
find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +2 -print0 | xargs -I {} -0 rm -v "{}"

Pipe to wc to get the number of files deleted:

find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +2 -print0 | xargs -I {} -0 rm -v "{}" | wc -l


Some good external sources:

How To Format Date For Display or Use In a Shell Script
Linux / Unix: Find And Remove Files With One Command On Fly
Linux Find Command with Practical Examples

Monday, October 23, 2017

How to to use the script-command to log everything a script does

The utility "script" will make a typescript of a terminal session.
Very useful if you have a script which produces lots of output, and you need to search for errors after the script has run to completion:

script -c /tmp/install_oracle.sh

Unless you add the name of the output file, the resulting file will be called typescript.

Thursday, October 19, 2017

How to monitor rollback activity after killing long-running session

Found this brilliant script here: http://www.oracle-wiki.net/startsqlshowrollinfo



prompt This query will monitor rollbacks of transactions after killing a job or session. 
prompt If the used undo records/used undo blocks count is going down, then the transaction is rolling back. 
prompt If they are going up, then the transaction is still running.
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 99999
 
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND   rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/
In my environment, the output looked as follows:
This query will monitor rollbacks of transactions after killing a job or session.
If the used undo records/used undo blocks count is going down, then the transaction is rolling back.
If they are going up, then the transaction is still running.

USERNAME               SID SERIAL#  USED_UBLK  USED_UREC SEGMENT_NAME                       RSSIZE STATUS         
-------------------- ----- ------- ---------- ---------- ------------------------------ ---------- ---------------
MYUSERNAME_XX_XXX_  1194   10616     670875   14088690 _SYSSMU22_1415974968$          1189502976 ONLINE

The USED_UBLK was dropping, so the ongoing rollback could be confirmed to the users.

Thursday, October 12, 2017

How to delete a specific line from a file, or all characters following a specific character using sed

If you want to search a file for a particular string, and then delete that line, use sed, like this:

sed -i "/_use_column_stats_for_function/d" init.ora

In bash, using the double quotation marks was the only way to make this work when using a variable instead of a fixed string.

To delete a specific line number, in this case line number 7:
 sed -i '7d' myfile.txt
 

To delete lines 7 and 8:
 sed -i '7,8d' insert_data.sql
To delete everything after a specific character, for example #:
sed -i "s/#.*//g" /tmp/autoupgrade.ora

Tuesday, October 10, 2017

How to find the currently enabled unified auditing policies in an Oracle 12c database



Oracle 12c comes with two auditing policies enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES.
You can find the currently enabled policies in the AUDIT_UNIFIED_ENABLED_POLICIES view.

The query below will reveal what the currently enabled policies will actually audit
select policy_name,audit_option,condition_eval_opt
from audit_unified_policies
where policy_name in (  select policy_name 
                        from audit_unified_enabled_policies);
                        


The result shows that the default Unfied Auditing Setup covers many of the actions you would certainly want to audit, like DROP USER, ALTER SYSTEM etc:

POLICY_NAME AUDIT_OPTION CONDITION_EVAL_OPT
ORA_SECURECONFIG LOGMINING NONE
ORA_SECURECONFIG TRANSLATE ANY SQL NONE
ORA_SECURECONFIG EXEMPT REDACTION POLICY NONE
ORA_SECURECONFIG PURGE DBA_RECYCLEBIN NONE
ORA_SECURECONFIG ADMINISTER KEY MANAGEMENT NONE
ORA_SECURECONFIG DROP ANY SQL TRANSLATION PROFILE NONE
ORA_SECURECONFIG ALTER ANY SQL TRANSLATION PROFILE NONE
ORA_SECURECONFIG CREATE ANY SQL TRANSLATION PROFILE NONE
ORA_SECURECONFIG CREATE SQL TRANSLATION PROFILE NONE
ORA_SECURECONFIG CREATE EXTERNAL JOB NONE
ORA_SECURECONFIG CREATE ANY JOB NONE
ORA_SECURECONFIG GRANT ANY OBJECT PRIVILEGE NONE
ORA_SECURECONFIG EXEMPT ACCESS POLICY NONE
ORA_SECURECONFIG CREATE ANY LIBRARY NONE
ORA_SECURECONFIG GRANT ANY PRIVILEGE NONE
ORA_SECURECONFIG DROP ANY PROCEDURE NONE
ORA_SECURECONFIG ALTER ANY PROCEDURE NONE
ORA_SECURECONFIG CREATE ANY PROCEDURE NONE
ORA_SECURECONFIG ALTER DATABASE NONE
ORA_SECURECONFIG GRANT ANY ROLE NONE
ORA_SECURECONFIG DROP PUBLIC SYNONYM NONE
ORA_SECURECONFIG CREATE PUBLIC SYNONYM NONE
ORA_SECURECONFIG DROP ANY TABLE NONE
ORA_SECURECONFIG ALTER ANY TABLE NONE
ORA_SECURECONFIG CREATE ANY TABLE NONE
ORA_SECURECONFIG DROP USER NONE
ORA_SECURECONFIG CREATE USER NONE
ORA_SECURECONFIG AUDIT SYSTEM NONE
ORA_SECURECONFIG ALTER SYSTEM NONE
ORA_LOGON_FAILURES LOGON NONE
ORA_SECURECONFIG CREATE DATABASE LINK NONE
ORA_SECURECONFIG DROP DATABASE LINK NONE
ORA_SECURECONFIG ALTER USER NONE
ORA_SECURECONFIG CREATE ROLE NONE
ORA_SECURECONFIG DROP ROLE NONE
ORA_SECURECONFIG SET ROLE NONE
ORA_SECURECONFIG CREATE PROFILE NONE
ORA_SECURECONFIG DROP PROFILE NONE
ORA_SECURECONFIG ALTER PROFILE NONE
ORA_SECURECONFIG ALTER ROLE NONE
ORA_SECURECONFIG CREATE DIRECTORY NONE
ORA_SECURECONFIG DROP DIRECTORY NONE
ORA_SECURECONFIG ALTER DATABASE LINK NONE
ORA_SECURECONFIG CREATE PLUGGABLE DATABASE NONE
ORA_SECURECONFIG ALTER PLUGGABLE DATABASE NONE
ORA_SECURECONFIG DROP PLUGGABLE DATABASE NONE
ORA_SECURECONFIG EXECUTE NONE

If the $ORACLE_HOME/rdbms/admin/secconf.sql script was created during database creation, you will have some other policies in your database, too, but they won't be enabled by default. These can be found by executing the following statement:
select policy_name,count(audit_option) "number of audits"
from AUDIT_UNIFIED_POLICIES
where policy_name not in (select unique policy_name from audit_unified_enabled_policies )
group by policy_name
order by 2 desc;
In my database, the following collection shows up as being created, but not yet enabled:
POLICY_NAME number of audits
ORA_RAS_POLICY_MGMT
33
ORA_CIS_RECOMMENDATIONS
26
ORA_RAS_SESSION_MGMT
14
ORA_ACCOUNT_MGMT
9
ORA_DATABASE_PARAMETER
3