Category: Java | DevOps | APEX | Scripts

Database, SQL | PLSQL

WHEN OTHERS -> NULL: Hidding your PLSQL Errors?

 , , ,

Are you using WHEN OTHERS -> NULL to hide your PLSQL errors?
Don’t be so sure…

WHEN OTHERS exception handlers that do nothing and don’t raise errors using RAISE or RAISE_APPLICATION_ERROR can often hide code failures that result in hard to identify bugs.

To avoid this, a new PL/SQL compiler warning was added in 11g to identify those kind of situations. Check example below:

SQL> ALTER SESSION SET plsql_warnings = 'enable:all';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE warning_test AS
  2  BEGIN
  3    RAISE_APPLICATION_ERROR(-20000, 'This is an Exception!');
  4  EXCEPTION
  5    WHEN OTHERS THEN
  6      NULL;
  7  END;
  8  /

SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE OTHERS_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8      PLW-06009: procedure "WARNING_TEST" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR

Nice, right?!

There are also some other warnings improvements like:

  • New NO_DATA_NEEDED Predefined Exception: ORA-06548: For parallel access and pipelined table functions the caller of a pipelined function does not need more rows to be produced by the pipelined function.

Warnings:

  • Severe
    – 5018 – omitted optional AUTHID clause
    – 5018 – omitted optional AUTHID clause
    – 5019 – deprecated language element
    – 5020 – parameter name must be identified
  • Informative
    – 6016 – native code generation turned off (size/time)
    – 6017 – operation will raise an exception
    – 6018 – an infinity or NaN value computed or used

Cheers!

Database, Performance, SQL | PLSQL

Cross-Session PL/SQL Function Result Cache

 , , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Cross-Session PL/SQL Function Result Cache

Since 11gR1 we have simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA:The cross-session PL/SQL function result cache.

The results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in a SQL query, or within a loop in PL/SQL.

Ok, but how to do this? It’s as simple as adding the RESULT_CACHE clause:

CREATE OR REPLACE FUNCTION procedure_example (p_in IN NUMBER)
  RETURN NUMBER
  RESULT_CACHE

The RELIES_ON may be set in 11gR1 but is unnecessary in 11.2 as it automatically tracks dependencies and invalidates the cached results when necessary.

Nice, right?
Cheers!

Database, SQL | PLSQL

Moving Audit Trails (AUD$ and FGA_LOG$) to a Different Tablespace

 , , , ,

Hi all!
There is one thing that is always true for any version  of the Oracle Database: the audit trail simply keeps growing and growing.

Some important facts in this regard:
– It’s based on activity, not the amount of data changed.
– The default location of the database audit trail is the AUD$ table, in the SYSTEM tablespace.
– As you know, If this tablespace gets filled up there could be serious consequences.
– The same concern is true for FGA_LOG$, which is located in the SYSAUX tablespace.

Ok, so if you could move these two tables  to specific tablespaces, the root problem would be solved, right?

From 11g R2 on you can move the some or all audit trails to other tablespaces you choose. How?

To move the AUD$:

begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDIT_TBS');
end;
/

To move the FGA_LOG$:

begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
audit_trail_location_value => 'AUDIT_TBS');
end;
/

 

There is only one important thing to keep in mind:  This tablespace needs to be online when the database is open. Otherwise, if any auditable operation try to be executed, you’ll get an ORA-02002.

This is something recommendable if you are setting the Auditing for your database. This is also something included Oracle in scripts like the Exacheck.

Hope it helps!
Cheers!

Database, Errors and Bugs, SQL | PLSQL

ORA-02158: invalid CREATE INDEX option while using redef_table on Oracle 12cR1

Hey all,
I’m working on a table redefinition project to migrate the existing tables and indexes to new compressed tablespaces. As the customer asked to have near 0 downtime to its data we decided to go with DBMS_REDEFINITION.

Simple right? Well… I sure hoped so.
I’m preparing a serie of posts about it, but before that I would like to share some hands on issues and that the magic of the new redef_table is not that great yet, at least on 12cR1/12.1.0.2.
Prior 12c, when would need to redefine a table you would use the DBMS_REDEFINITION and its 6 steps:

0 – Manually create a interim table to receive the data with the same structure as the original table
1 – DBMS_REDEFINITION.can_redef_table
2 – DBMS_REDEFINITION.start_redef_table
3 – DBMS_REDEFINITION.sync_interim_table
4 – DBMS_REDEFINITION.copy_table_dependents
5 – DBMS_REDEFINITION.finish_redef_table

And sometimes you would need to user the DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT to help on some issues but if everything was good you would only need to do the steps above.

There are a few issues with the approach by that I mean BUGS 🙂 so you need to watch your back do an a good prep work.

In 12c you have a new procedure called DBMS_REDEFINITION.redef_table that would bundle all the 6 steps into one single procedure call. With its up and down side.
For me, the down side is that we can’t monitor the procedure, once this is no loger anything being recorded on dba_redefinition_errors.
By working as a transaction, everything works or its rolled back (Or it should but I will leave it for another post).

So the only way to know what is being done is to trace the session that is doing the redefinition. And that what I needed to do to see what was going on with a strange situation.

This is what was happening: I 1st tried the DBMS_REDEFINITION.redef_table and it raised ORA-02158: invalid CREATE INDEX option but when I used the 6 steps mentioned above (can_redef_table,start_redef_table,etc) it worked without issues:

That bugged me so I traced the session.

SQL> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

SQL> BEGIN DBMS_REDEFINITION.REDEF_TABLE(uname => 'USER1',
tname => 'TEST1',
table_compression_type => 'COMPRESS FOR OLTP', 
table_part_tablespace => 'DATA_COMP', 
index_tablespace => 'DATA_COMP', 
index_key_compression_type   => 'COMPRESS ADVANCED LOW', 
lob_compression_type => 'COMPRESS HIGH', 
lob_tablespace => 'DATA_COMP', 
lob_store_as => 'SECUREFILE');
END;
/
BEGIN*
ERROR at line 1:ORA-02158: invalid CREATE INDEX option
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 2

And this was the create index statement that was in the trace:

250438 PARSE ERROR #140737488293344:len=421 dep=1 uid=0 oct=9 lid=0 tim=3977494319094 err=2158250439 CREATE INDEX "USER1"."TMP$$_INDEX0" ON "USER1"."REDEF$_16752430_0" ("VISIT_NO")250440   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOWNOLOGGING250441   STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645250442   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1250443   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)250444   TABLESPACE "DATA_COMP"

Can you see the issue?
Well neither could I, a colleague read the trace again and found a silly bug.

Here it is The create index had this:

COMPRESS ADVANCED LOWNOLOGGING

Instead of this, it had this:

COMPRESS ADVANCED LOW NOLOGGING

A silly space was missing and was causing the entire redefinition to fail!

I could not find any reference in MOS but that was it a space prevented to use the redef_table and caused me to lose some hours on it.

Hope this save you some time on your troubleshooting and I will be posting other strange situations that I found using the redef_table on Oracle 12cR1.

See you next post!

Elisson Almeida

Database, Shell Scripts

Tips to Improve your Oracle DB Shellscripting!

 , ,

There are some small tricks to speed up your shellscripts for Oracle DBs. I’m pretty sure you can do almost magic only with those:

# To get all OS Users with an Oracle Instance in server:

ps -ef | grep [o]ra_pmon | awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u

# To get all DBSids in server:

ps -ef | grep [o]ra_pmon  | awk -F_ '{ print $3}'

# To get all DBNames in server:

ps -ef | grep [o]ra_pmon  | awk -F_ '{ print substr($3, 1, length($3)-1)}'

# To get Oracle Home from /etc/oratab for a database:

grep "dbname:" /etc/oratab|cut -d: -f2 -s

# Looping for any of the lists from commands above:

for DBUSER|DBNAME|DBINST in `$Comand_From_ListAbove` do [...] done

# Example:
– Take DBInstance and Oracle Home for Each DBName on Each OSUser:

for DBUSER in `ps -ef | grep [o]ra_pmon | awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u`
do
for DBNAME in `ps -ef | grep [o]ra_pmon | grep ${DBUSER} | awk -F_ '{ print substr($3, 1, length($3)-1)}'`
do
DBSID=`ps -ef | grep [o]ra_pmon | grep -i ${DBNAME^^}| awk -F_ '{ print $3}'`
DBOH=`grep "^${DBNAME,,}:" /etc/oratab|cut -d: -f2 -s`
done
done

# Variable Upper Case or Lower Case:

For uppercase: ${DBNAME^^}
For lowercase: ${DBNAME,,}

# Putting Colors on your messages:

RED='\033[0;31m'
GREEN='\033[0;32m'
PURPLE='\033[0;35m'
NC='\033[0m' # No Color
echo -e "Status: ${GREEN}All Good!${NC}";
echo -e "Status: ${RED}ERROR!${NC}"
echo -e "Status: ${PURPLE}Do you like messages in purple?${NC}"

# Conditional: Is there any line with a specific content?

if [`cat myfile.log | grep "text_I_want"| wc -l |awk '{print $1}'` -gt 0 ]; then
echo -e "Count Greater Then Zero: ${GREEN}Line Exists!${NC}";
else echo -e "Count equals to Zero: ${RED}No line with this text...${NC}"
fi;

# Some other related posts:
Shellscript & Oracle: Run Script for a List of Databases
Shellscript: Using eval and SQLPlus
Recursive string change
Grepping Entries from Alert.log
Grepping Alert by Day
Shellscript: Master Blaster KB!

PS: Thanks for Jared Still for suggesting improvements and saving some command greps (grep ora_pmon |grep -v grep -> grep [o]ra_pmon). That’s a nice tip. Thanks! 😀

Anything I forgot to put here? Tell me with a comment!
Cheers!

Fusion Middleware, Shell Scripts, Weblogic

Disk Space is not Released After Deleting Files

 , , , , ,

When deleting a large files, the file is deleted successfully but the size of the filesystem does not reflect the change.
The files was be deleted, but only restarting the jvm or java process released the disk space.
Usually occurs with log files.

The lsof command show the files opened in system.
For example: lsof |grep deleted

java 15138 oracle 2959r REG 253,3 5875027 131163 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1.out03422 (deleted)
java 15138 oracle 3054r REG 253,3 10480928 131166 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1-diagnostic-81.log (deleted)
java 15138 oracle 3062r REG 253,3 10479417 131200 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1-diagnostic-82.log (deleted)

The command output shows pid, owner, file descriptor (fd), size and file.

Workaround:
If can’t restart the process, it is possible to force the system to de-allocate the space consumed by an in-use file by forcing the system to truncate the file.
$ echo > /proc/pid/fd/fd_number

Be careful not to truncate unwanted files.

In my case:
$ echo > /proc/15138/fd/2959
$ echo > /proc/15138/fd/3054
$ echo > /proc/15138/fd/3062

That’s all for today.

Database, Shell Scripts

Shellscript & Oracle: Run Script for a List of Databases

 , , ,

Hey all!
Quick tip/script for today: How to run same script for a list of databases quickly?

In my case I have same password/user in all databases, but in case you haven’t you can make a similar awk command to retrieve users and passwords from a file.

The list:

$: cat /tmp/dbs.cfg
db01
db02
db03
testdb

The script:

for DBSID in ${*-$(awk -F: '!/^#/ {print $1}' /tmp/dbs.cfg}
do
    print "
        connect user/password@${DBSID}
        @script_to_run.sql
        exit " |
    sqlplus /nolog >> /tmp/output_test.log
done

Hope it helps!
Cheers!

Java | DevOps | APEX | Scripts, PostgreSQL

Managing database changes with Sqitch

 , , , , , , , ,

Nowadays everybody talks about continuous integration, automated test tools, and stuff like that. But almost every time it’s about testing applications. Let’s talk a little about Sqitch, “a database change manager” as they use to call themselves.

Let’s say you’re starting a new project and want to ensure every database change are managed and secured with tests? Just like with an app, you can write tests to ensure everything works and you can have a “revert” method for the changes too. Suppose you are changing a field type and start creating a backup column (fallback ;)), but something goes wrong on the meantime and for some reason, you can’t do a simple rollback… revert it and be happy.

What if your CI says that the application it’s fine and can be released to the production server but “a wild bug appears” and you NEED to revert to a previous version of your service, easy huh? With Docker and one command line, the service is up and running again. But what about the database changes your team did? Just call the revert and be happy :).

Have I mentioned that you can have multiples connection URI at the project and have different environments to apply the changes with a single command?!. Let’s see a few commands to create a simple MySQL database…

Create a folder, initialize GIT in it and initialize the Sqitch project.

mkdir awesomeapp
cd awesomeapp
git init .
sqitch init flipr --uri https://github.com/theory/sqitch-mysql-intro/ --engine mysql

Now you have a ready to go sqitch structure. Now let’s do some basic creates.

sqitch add createcustomeruser -n 'Creation of Customer table'

Add the create table script to the file inside the “deploy” dir.

CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name VARCHAR(50)
);

Add the verify script, can be a simple select. Add the revert script, guess what’s the command, and after all, you can run the commands below and see the result.

sqitch deploy db:mysql://root@/awesomeapp
sqitch verify db:mysql://root@/awesomeapp
sqitch revert db:mysql://root@/awesomeapp

This was a quick explanation about Sqitch so go ahead and read more at their docs and tutorials, they are pretty good.

Database, Security, SQL | PLSQL

Oracle Security: Users with Default Passwords

 , ,

Hi all!
Did you know since Oracle Database 11g we have a way to quickly identify users with default passwords?
It’s implemented in a really simple way, with a single data dictionary view: DBA_USERS_WITH_DEFPWD

You can identify these users with the query:

select * from dba_users_with_defpwd

Example of output:

USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

You can see SCOTT listed above, because his password is TIGER, the default one. Change it with:

SQL> alter user scott identified by tiger1;

User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

You don’t see SCOTT on the list anymore. It’s that simple!

Hope it helps to make you database more secure.
Cheers!

Java | DevOps | APEX | Scripts

Automating your Github Project’s build and deploy with Heroku and Travis

 , , , , , , , ,

Hey fellas!
My name is Jéferson Sartor Decker, I’m a Software Developer in Thoughtworks Brasil and live in Porto Alegre! Matheus invited me to make a post here, so I hope you enjoy it!

What we’re going to use?

Github: A web-based service for version control, that uses git and is free to open source projects <3 !

Heroku: As they said in their site:

Heroku is a cloud platform that lets companies build, deliver, monitor and scale apps — we’re the fastest way to go from idea to URL, bypassing all those infrastructure headaches.

And is also free with some restrictions. Remember that Heroku support for default the languages below:

  • Javascript
  • Ruby
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Heroku CLI: Heroku Command Line Interface.

Travis-CI: A continuous integration service, that builds and tests code in Github and also is free to open source projects <3 !

Travis-CI CLI: Travis CI Command Line Interface.

Let’s get started!

So, first of all we need to have a project on Github, I’ll create a sample Java + Spring project, just to have something to build and deploy. You can do this to any project that you already have in your Github! My project is the ‘sample-java-project’, as you can see in the next image:
More“Automating your Github Project’s build and deploy with Heroku and Travis”