Oracle Developer Community LAD TOUR 2018

Hello all!
I’m pretty sure you know what I’m talking about. Don’t know?
Perhaps the term OTN Tour LAD rings a bell?

Not yet?
Well, this is an Oracle Users Groups Tour around the Latin America where local communities engage their Oracle Professionals for a meeting discussion about experiences and technology. This is all organized by the LAOUC – Latin American Oracle Users Group Community (logo below) with the local users groups.

In general, we have people from other countries and continents doing a crossover to increase the value of any discussions. This year I’ll be speaking in Paraguay, Chile and Brazil. Check below my agenda:

# Paraguay (Assunción) – OUGPY – July 31th:
Oracle Database Backup Service: Backup of On-Premise Databases to Cloud

# Chile (Santiago) – CLOUG – August 2nd:
Oracle Database Backup Service: Backup of On-Premise Databases to Cloud
Amazing Free Tools for Oracle DBAs
Oracle Database Migration and Consolidation Techniques

# Brazil (São Paulo) – GUOB – August 4th:
Amazing Free Tools for Oracle DBAs

I’ll be doing a post with more info about each event (date/time, location, subscription info, etc) soon.

LOGO-LAOUC

The complete agenda for the LAD tour is:

Asuncion, Paraguay — Tuesday 31-Jul-2018
Santiago, Chile — Thursday 2-Aug-2018
Sao Paulo, Brasil — Saturday 4-Aug-2018
Montevideo, Uruguay — Monday 6-Aug-2018 & Tuesday 7-Aug-2018
Buenos Aires, Argentina — Thursday 9-Aug-2018
Lima, Peru — Saturday 11-Aug-2018
Quito, Ecuador — Tuesday 14-Aug-2018
Barranquilla, Colombia — Thursday 16-Aug-2018 & Friday 17-Aug-2018
San Jose, Costa Rica — Monday 20-Aug-2018
Panamá, Panamá — Wednesday 22-Aug-2018
Mexico DF, Mexico — Friday 24-Aug-2018
Guatemala, Guatemala — Tuesday 28-Aug-2018

For more info, please access:

LAOUC
OUGPY
CLOUG
GUOB

Cheers!

 

“TNS-12531: TNS:cannot allocate memory error” – Are you sure, Oracle?

Hey guys!
So, I was working on a server build and everything was running fine until I tried to start the listerner. The process hang on “Starting /u01/app/grid/product/12.1.0/grid/bin/tnslsnr: please wait…” and then raised TNS-12531: TNS:cannot allocate memory error.

Well 1st thing, looked the error up using orerr:

TNS-12531: TNS: cannot allocate memory
Cause: Sufficient memory could not be allocated to perform the desired activity.
Action: Either free some resource for TNS, or add more memory to the machine. For further details, turn on tracing and re-execute the operation.

Should be simple right? Well, not in this case. The server had plenty of resources and not even the database was up yet so over 90% of the server memory was free.

Checked all sort of things when I started to check the server network configuration.
Looking up found that the server will through this error also when the hostname definition is different from what is resolved by the /etc/hosts file.

Once those matched, volià, listener started successfully.

Not the memory right? Oracle and its tricks…

That kept me bugging so I found this article, which shows a trace of the listener with a bit more information.

Hope this can save you some minutes on troubleshooting.

Until next time!

Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS

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:

Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS

In version 11g Oracle introduced SecureFiles, a new LOB storage architecture as replacement for BASICFILES LOBs’storage, being faster than Unix files to read/write. Lots of potential benefits for OLAP analytic workspaces are expected, as the LOBs used to hold AWs have historically been very slow to write. In addition, this object type is compliant to other mechanisms like deduplication, compression and encryption. Besides that, lock and concurrency model has been improved to manage those kind of objects. Other improvements like space management, reduced fragmentation, intelligent pre-fetching, new network layer, no LOB index contention, no high water mark contention and being easier to manage are important to mention.

Continue reading

11g+ Features: Extended Statistics

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:

Extended Statistics

One of the most expected features of Oracle 11g is improvements to the dbms_stats package, specifically the ability to aid complex queries by providing extended statistics to the cost-based optimizer (CBO).

The 11g extended statistics are intended to improve the optimizers guesses for the cardinality of combined columns and columns that are modified by a built-in or user-defined function. In Oracle 10g dynamic sampling can be used to provide inter-table cardinality estimates, but dynamic sampling has important limitations. However, the 11g create_extended_stats in dbms_stats relieves much of the problem of sub-optimal table join orders allowing for extended statistics on correlated columns.

One of the expectation is to avoid using the ORDERED hint, one of the most popular SQL tuning hints, used to to specify that the tables be joined together in the same order that they appear in the FROM clause.

This feature can be controlled by hidden parameter:

_optimizer_enable_extended_stats in case of SQL Performance regression.

 

You can also see more detailed material here:
About improvements on 18c (yeah, lots of things on 12c and 18c).
Oracle 12c: Automatic Column Group Detection in Oracle Database 12c Release 1 (12.1)
Oracle Base: Extended Statistics on 11gR2.

Cheers!

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 ora_pmon | grep -v grep| awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u

# To get all DBSids in server:

ps -ef | grep ora_pmon | grep -v grep | awk -F_ '{ print $3}'

# To get all DBNames in server:

ps -ef | grep ora_pmon | grep -v grep | 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 ora_pmon | grep -v grep| awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u`
do
for DBNAME in `ps -ef | grep ora_pmon | grep -v grep |grep ${DBUSER} | awk -F_ '{ print substr($3, 1, length($3)-1)}'`
do
DBSID=`ps -ef | grep ora_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!

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

GGate: STOP request pending. There are open, long-running transactions.

Hey all!
So, these days I was about to stop the GGate extract to add a new table to replication and saw this:

GGSCI (greporasrvr) 2> stop extract GREPORA_EXT

Sending STOP request to EXTRACT GREPORA_EXT ...

STOP request pending. There are open, long-running transactions.
Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts.
To force Extract to stop, use the SEND EXTRACT GREPORA_EXT, FORCESTOP command.
Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 616, RBA 135673360.

2018-04-12 15:55:41 WARNING OGG-01742 Command sent to EXTRACT GREPORA_EXT returned with an invalid response.

Interesting, right?
Let’s discover what is causing the situation:

GGSCI (greporasrvr) 2> send extract GREPORA_EXT, showtrans duration 20 MIN

Sending showtrans request to EXTRACT GREPORA_EXT ...

Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 616, RBA 135673360

------------------------------------------------------------
XID: 92.1.1460254
Items: 1
Extract: GREPORA_EXT
Redo Thread: 1
Start Time: 2018-04-12:08:27:03
SCN: 22.120909138 (94610189650)
Redo Seq: 616
Redo RBA: 135673360
Status: Running

------------------------------------------------------------
XID: 69.7.1771671
Items: 1
Extract: GREPORA_EXT
Redo Thread: 1
Start Time: 2018-04-12:13:27:29
SCN: 22.121387178 (94610667690)
Redo Seq: 629
Redo RBA: 1457168
Status: Running

15:59:12 SYS@GREPDB>select * from gv$transaction where xidusn=92;
1 000000018FEA2170 92 1 1460254 121 548870 24255 48 ACTIVE 04/12/18 08:27:02 120909138 22 168 2
131578 24097 43 0000000198A49D68 7683 NO NO NO NO

0 0 0 0 0 0 0 0 81875 4581610 23108750 1415217 -1.675E+09 197 12-APR-2018 08:27:02 0 0
9.4610E+10 0 5C0001001E481600 0000000000000000 0000000000000000

1 row selected.

16:00:08 SYS@GREPDB>select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='000000018FEA2170';
885 55577 db file sequential read feedprocs12 39za7ttrfjbr1 55 6x9pg77q982g3
JDBC Thin Client JDBC Thin Client

1 row selected.

16:00:37 SYS@GREPDB>set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a140
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99
col execs for 99999,999
select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text
from v$sql s, dba_users u
where
sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id
/
Enter value for sql_id: 39za7ttrfjbr1

SQL_ID CHILD PLAN_HASH EXECS ETIME AVG_ETIME USERNAME
------------- ------ ---------- ---------- ------------- ------------- -------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
39za7ttrfjbr1 0 72089250 0 25,434.97 25,434.97 GREPORA
--SQL Name:summary/envision/insertMyTable INSERT INTO mytable ( .......) 

1 row selected.

Nice!
Now we know it’s being caused by this long insert in mytable, but how can I proceed?
There are basically two options:
1. Kill the session on DB: Long rollback predicted, be aware of the impact and rollback activity, plus application effects if any.
2. Force stop of Extract with SEND EXTRACT GREPORA_EXT, FORCESTOP: In this case there is no effect for the database and no rollback, HOWEVER, when extract start again it will need to start from this cancelled sequence. First question, do I have this sequence still available? As you could see before, sequence 616 on would be needed, lets check on our availability:

16:01:41 SYS@GREPDB>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA_GREPDB
Oldest online log sequence 619
Next log sequence to archive 634
Current log sequence 634

Ouch!
So, in case it be needed, be aware you’ll need your archivelogs and, if already removed, restore may be needed.

There is also third option: 3. Wait until transaction be completed. 🙂

If 1 or 2 need to be chosen, choose wisely. 🙂

Hope this helps.
Cheers!