Month: August 2018

Database, Linux, Shell Scripts, Tools

/bin/rm: cannot execute [Argument list too long]

 , , ,

Hey all!

Just a quickie and useful thing today. How many times you found this?

/bin/rm: cannot execute [Argument list too long]

Ok, so, first thing: Is it related to Oracle logs? If so, you may want to ADCRI. Check this post for more info: ADRCI Retention Policy and Ad-Hoc Purge Script for all Bases.

If not, you may solve this using find with rm. Ok, but want to keep the most recent files?

Some examples for you, removing audit files:

# Remove older then 1 day:

find /oracle/greporadb/admin/greporadb/adump -name "*.aud" -mtime +1 -exec rm {} \;

# Remove older then 1 hour:

find /oracle/greporadb/admin/greporadb/adump -name "*.aud" -cmin +60 -exec rm {} \;

More“/bin/rm: cannot execute [Argument list too long]”

Database, RAC | Data Guard

Services [not] Starting Automatically with CRS after Reboot

 , , , ,

Hello all!
So, these days a client asked me to check why his database was not starting with CRS after reboot. So I started investigating and noticed this…

On Oracle 11.2, the database auto start policy in the clusterware is restore, which means that clusterware will remember the last state of the database. As well as database, Oracle 11.2 comes by default with several important resources with attribute AUTO_START=restore in the profile.

With that, if the database was stopped normally then on the next restart of clusterware it won’t be started. Otherwise if the server crashes or by some reason the OS is rebooted then clusterware will start the database because last state was ONLINE (running).

In my case, a second reboot was done with database down, and this is why database didn’t started. And same applied to some other target types.

The conclusion I get?
For best availability, it is a good practice to change this default behaviour after each installation on 11.2. Some checking and adjusting scripts below:

To check all Resources:

crsctl stat res -p

Checking on database resource:

...
NAME=ora.grepora.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
...

But besides DB, several resources were in restore or even in never, like ASM and Voting Disk DG… Check for all resourced AUTO_START:

[root@greporasrv1 ~]# crsctl stat res -p |grep AUTO_START
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore
AUTO_START=never
AUTO_START=never
AUTO_START=restore
AUTO_START=always
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore
AUTO_START=always
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore
AUTO_START=restore

Well, so to fix all the resources:

crsctl modify resource ora.APS1.dg -attr AUTO_START=always
crsctl modify resource ora.DG1.dg -attr AUTO_START=always
crsctl modify resource ora.FRA.dg -attr AUTO_START=always
crsctl modify resource ora.LISTENER.lsnr -attr AUTO_START=always
crsctl modify resource ora.VDISK_OCR.dg -attr AUTO_START=always
crsctl modify resource ora.asm -attr AUTO_START=always
crsctl modify resource ora.gsd -attr AUTO_START=always
crsctl modify resource ora.net1.network -attr AUTO_START=always
crsctl modify resource ora.ons -attr AUTO_START=always
crsctl modify resource ora.registry.acfs -attr AUTO_START=always
crsctl modify resource ora.LISTENER_SCAN1.lsnr -attr AUTO_START=always
crsctl modify resource ora.LISTENER_SCAN2.lsnr -attr AUTO_START=always
crsctl modify resource ora.LISTENER_SCAN3.lsnr -attr AUTO_START=always
crsctl modify resource ora.cvu -attr AUTO_START=always
crsctl modify resource ora.grepora.db -attr AUTO_START=always
crsctl modify resource ora.greporasrv1.vip -attr AUTO_START=always
crsctl modify resource ora.greporasrv2.vip -attr AUTO_START=always
crsctl modify resource ora.oc4j -attr AUTO_START=always
crsctl modify resource ora.scan1.vip -attr AUTO_START=always
crsctl modify resource ora.scan2.vip -attr AUTO_START=always
crsctl modify resource ora.scan3.vip -attr AUTO_START=always

Below the output and the checking:

[root@greporasrv1 ~]# crsctl modify resource ora.LISTENER.lsnr -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.VDISK_OCR.dg -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.asm -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.gsd -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.net1.network -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.ons -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.registry.acfs -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.LISTENER_SCAN1.lsnr -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.LISTENER_SCAN2.lsnr -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.LISTENER_SCAN3.lsnr -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.cvu -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.grepora.db -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.greporasrv1.vip -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.greporasrv2.vip -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.oc4j -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.scan1.vip -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.scan2.vip -attr AUTO_START=always
[root@greporasrv1 ~]# crsctl modify resource ora.scan3.vip -attr AUTO_START=always
[root@greporasrv1 ~]#
[root@greporasrv1 ~]# crsctl stat res -p |grep AUTO_START
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
AUTO_START=always
[root@greporasrv1 ~]#

 

Community, Events

GUOB Tech Day 2018: Feedback!

 , , ,

Hey all!

Once again, here only to say great words on the GUOB Tech Day this year. Besides the awesome names we could here here, which is amazing for Brazil, I’d like to bring up the attention to schedule and keeping it from the organization side. Great!

Btw, for the updates version of my slides: Matheus’ Slides.

Plus, I had the opportunity to meet again great friends on the community, things that are only possible by those events, once we are thousands of kilometers apart.

Here are some of, including me trying to absorve some of Connors expertise:


Cheers GUOB, see you next year!

Database, SQL | PLSQL

Identifying the top segments

 , ,

Hello readers! My name is Bruno Kremer, this is my first post from a series, and I will be talking about how we can identify the top segments of the database.

Introduction

It’s well known that we can create automated tasks to collect and save the space used/allocated by the database objects, such as saving snapshots of the DBA_SEGMENTS view. But what if this is your first contact with a specific database and you need to identify the top segments, estimate their growth ratio, check the history of space allocated, or even to perform some kind of capacity planning? There are some alternatives to answer these questions, but on this post I will share the starting point. Please feel free to customize the scripts to your own need.

Checking the top sized segments


select
s.owner,
s.segment_name,
s.segment_type,
round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
fetch first &TOP rows only;

Input values: &TOP – limit the number of rows returned.

Filters you might want to use: and s.segment_type in ('&OBJECT_TYPE') – ‘TABLE’, ‘TABLE PARTITION’, ‘INDEX’…

Return example:

Note: the scripts used in this series were tested on 12.1.0.2 databases. Some of these use the “FETCH FIRST” clause to limit the number of rows returned, but if you are using older versions of Oracle Database, you can still use the old fashion like “ROWNUM”.

Example:


select * from (
select s.owner, s.segment_name, s.segment_type, round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
) where rownum <= &TOP;

Now that you already have an idea regarding the size of the largest database segments, you might want to check the top growing segments… On next publications we will talk about how we can use AWR data dictionary views and some DBMS_SPACE procedures to estimate space usage history and top growing segments.

Database, Security

Oracle Security: Immediate Protection for JVM Exploits (CVE-2018-3110)

 , , ,

Hello all!

Now that CVE-2018-3110 is a hot topic, I think this is a pretty interesting topic to go on.

So, we all know this is consistently one of the components with more CVEs for Oracle Databases. Basically because you can create Java objects in the database (which I think is an abomination :D) and run this code there, usually doing some tricks to escalate privileges to DBA, to PDB, to CDB, to host and other CDBs…

The problem is that (before 18c) OJVM PSU Patches are not RAC Rolling installable. Which means will need a maintenance window to apply fixes for this component. Quite bad, hãn… And if you discover a vulnerability and the PSU window is only in a month or so?

Well, we have a solution 🙂
It is well described in MOS Oracle Recommended Patches — “Oracle JavaVM Component Database PSU and Update” (OJVM PSU and OJVM Update) Patches (Doc ID 1929745.1), under the name “Mitigation Patch”.

It basically consists in install a patch Patch 19721304: SCRIPT TO LOCK DOWN JAVA DEVELOPMENT, which is a Rolling Patch, which allows you to disable any new Java object to be created. This is, so, assuming exploits can be done by creating new java objects on DB (as most of Java CVEs). Also, this patch don’t have any version requirement (after 9i).

Having the patch, however, doesn’t mean you are automatically protected against any vulnerability, but means you can protect yourself temporary by disabling new java objects creation with “exec dbms_java_dev.disable;” anytime.

The Mitigation Patch does not remove Java objects or block any java execution, it only disable the creation of new Java objects, so if the exploit is already planted, it’s not a viable solution. The mitigation patch can be used in any scenario where the PSU or proper JVM fixes cannot be applied at the moment but it’s wanted to prevent against JVM vulnerabilities.

Now, before downloading the patch, first check if it’s not already installed to your home, as it’s part of some Boundle Patches, like “Database Bundle Patch : 12.1.0.2.180417 (27338029)“.

Important Note: The intent is to be like a “workaround” while the PSU is planned. This was not built to be definitive solution. The idea is to just disable new Java objects to be created until the fix is applied on the proper planned maintenance window.

Hope it helps!

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!

Community, Events

LAD TOUR 2018 CLOUG Feedback

 , , ,

Hey all!

On last Aug 2nd I had the opportunity to be at CLOUG Oracle event, delivering 3 sessions for an amazing group of people. Plus, I could meet again some very good friends, great experience on this regard!

Find all my slides on its most recent version here.

Thank you all for the opportunity and attendance.
See you next time!

More details on my sessions:

> Oracle Database Backup Service: Backup of On-Premise Databases to Cloud
In this session, I’ll be speaking about a study case one of the most fundamental Disaster Recovery tool for any business or technology: The Backup.  But, what happens if your backup don’t work, or you lose your backup with your Data Center?

The session covers the Oracle Backup Service available in Oracle Public Cloud, specially focusing on offsite backups for On-Premise Databases. It is the less disruptive and easy implementable alternative to have an offsite backup, a common need in several business due the Sarbanes-Oxley Act (SOX-2002). This is also the first step to put Database environments into Oracle Cloud.

The session has a hands-on lab covering installing, configuring, backing up and restoring a corrupted file from Public Cloud.

> Amazing Free Tools for Oracle DBAs
Your scripts utility belt don’t need to cover it all! Know some outstanding free tools to manage and monitor your Oracle Database environment.

In this session we’ll get in touch with some amazing free tools to check on the database performance, ongoing issues, tracing, dashboarding and graphing things from your database and server perspectives.

> Oracle Database Migration and Consolidation Techniques
Migrating and Consolidating databases or servers is a regular task for DBAs all over the world, caused mostly by hardware upgrades, RAC revision, application decomissioning and other strategic decisions.
But which tools Oracle provide us to help on this process?

This session covers the main tools and techniques to be used for database and hosts consolidation, including migration tools, workload simulating and go to live strategies.

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!

Community, Events

LAD OTN TOUR Feedback: OUGPY

 , , , ,

Hi all!

Last week I had the amazing experience to talk on OUGPY event, as part of OTN Tour Latin America, and for that I’d like to say my THANK YOU for the organization, specially to Lilian for arranging everything so wonderfully.

By the way, find my presentation slides here.

Here are some record I have from this moment and the awesome people I could meet there.

Thanks Paraguay, see you next year!

HA’EVEte, Paraguay! Ahata aju!

 

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!