Statistics not Being Auto Purged – Splitting Purge

Hi all!
The post Purge SYSAUX Tablespace,  made on Fabruary 8this, is yet being high accessed. So, if  you’re interested, here it goes another post about:

Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) time of recent analyze in the system – 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.

The problem was very clear in alert.log, through the entry:

Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation

But it’s happening since 2012! How to address that?
Continue reading

Manipulating JMS queues using WLST Script

Hi.

Today, let’s talk about Java Message Systems (JMS), the reason led me talk about this, is that my environment, a complex architecture of messages where we have more of two hundred queues in the same domain.
The administration of queues in the weblogic console is very simple, but, if you need to remove a million messages, in a hundred queues, you have a problem!
To turn more agile the visualization of messages, state and other queue properties, nothing better than to use WLST.

This post shows a script, which can grow up where you imagine, for while the script have just three options (the most useful to me) and nothing prevents to have more.

Continue reading

Flashback- Part 3 (Flashback Versions Query)

Hi Everyone,

Here we are to continue our Flashback Saga! If you lost our first 2 posts about that and are in the mood for a good reading, please go through the links below:

Flashback Drop

Flashback Query

Today we are going to discuss Flashback Versions Query, which has a strong link with the previous post, the Flashback Query (AS OF). With this feature, we are able to verify all changes made  between 2 points in time in the past, using SCN or a Timestamp. Of course, the Flashback Versions Query will retrieve only the committed data. Just like Flashback Query, the Flashback Versions Query is undo-based, so make sure your undo Tablespace and undo retention period is good enough for you.

Continue reading

APEX: Let’s Talk About Charts Attributes (Inverted Scale)

Hello! If you had play with Apex before, you know how easy is to build a simple report to present your data. But sometimes, your boss will ask you to build something more “graphical” or with a better design. But I never thought in color themes or pictures when I developed my simple reports in Sqlplus. Those colorful themes and design things are, most of the times, not familiar for DBA’s.

Continue reading

Package Body APEX_030200.WWV_FLOW_HELP Invalid after Oracle Text Installing

Hi all!
The package body APEX_030200.WWV_FLOW_HELP become invalid after Oracle Text installation with the follow errors:

Compilation errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP
#13#10Error: PL/SQL: ORA-00942: table or view does not exist
Line: 189
#13#10Error: PL/SQL: SQL Statement ignored
Line: 188
#13#10Error: PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Line: 191
#13#10Error: PL/SQL: Statement ignored
Line: 191
#13#10Error: PL/SQL: ORA-00942: table or view does not exist
Line: 197
#13#10Error: PL/SQL: SQL Statement ignored
Line: 196
#13#10Error: PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Line: 199
#13#10Error: PL/SQL: Statement ignored
Line: 199
#13#10Error: PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Line: 261
#13#10Error: PL/SQL: Statement ignored
Line: 261
#13#10Error: PLS-00201: identifier 'CTX_DDL.SET_ATTRIBUTE' must be declared
Line: 262
#13#10Error: PL/SQL: Statement ignored
Line: 262
#13#10Error: PLS-00201: identifier 'CTX_DDL.SET_ATTRIBUTE' must be declared
Line: 265
#13#10Error: PL/SQL: Statement ignored
Line: 265
#13#10Error: PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Line: 280
#13#10Error: PL/SQL: Statement ignored
Line: 280
#13#10Error: PLS-00201: identifier 'CTX_DOC.FILTER' must be declared
Line: 292
#13#10Error: PL/SQL: Statement ignored
Line: 292
#13#10Error: PLS-00201: identifier 'CTX_DOC.FILTER' must be declared
Line: 312
#13#10Error: PL/SQL: Statement ignored
Line: 312

It happens bassically because APEX schema has not been granted with execute privileges for CTX_DDL and CTX_DOC. The note below it’s exactly about it:
The WWV_FLOW_HELP PACKAGE Status is Invalid After Installing Oracle Text (Doc ID 1335521.1)

The solution is simple:

mydb> grant execute on ctx_ddl to APEX_030200;
Grant succeeded.
mydb> grant execute on ctx_doc to APEX_030200;
Grant succeeded.
mydb> alter package APEX_030200.WWV_FLOW_HELP compile;
Package altered.
mydb> alter package APEX_030200.WWV_FLOW_HELP compile body;
Package body altered.

Have a nice day!
Matheus.

Decrypting WebLogic Datasource Password

Hi Guys,

Today I bring you a script that I use to decrypt datasource passwords and also the password of AdminServer, which is very useful on a daily basis.

The script uses the encrypted password that is found within the datasource configuration files ($DOMAIN_HOME/config/jdbc/*.xml).
To decrypt the AdminServer password is used the encrypted password contained within the boot.properties ($DOMAIN_HOME/servers/AdminServer/security).

Below the script (decryptPassword.py):

#=======================================================================================
# This Script decrypt WebLogic passwords
#
# Usage:
# wlst decryptPassword.py
#
#
#=======================================================================================
import os
import weblogic.security.internal.SerializedSystemIni
import weblogic.security.internal.encryption.ClearOrEncryptedService

def decrypt(domainHomeName, encryptedPwd):
domainHomeAbsolutePath = os.path.abspath(domainHomeName)
encryptionService = weblogic.security.internal.SerializedSystemIni.getEncryptionService(domainHomeAbsolutePath)
ces = weblogic.security.internal.encryption.ClearOrEncryptedService(encryptionService)
clear = ces.decrypt(encryptedPwd)
print "RESULT:" + clear

try:
if len(sys.argv) == 3:
decrypt(sys.argv[1], sys.argv[2])
else:
print "INVALID ARGUMENTS"
print " Usage: java weblogic.WLST decryptPassword.py "
print " Example:"
print " java weblogic.WLST decryptPassword.py D:/Oracle/Middleware/user_projects/domains/base_domain {AES}819R5h3JUS9fAcPmF58p9Wb3swTJxFl0t8NInD/ykkE="
except:
print "Unexpected error: ", sys.exc_info()[0]
dumpStack()
raise

Syntax using: java weblogic.WLST decryptPassword.py $DOMAIN_HOME encrypted_password

Download script here.

For example:
[oracle@app1osbgrepora1l scripts]$ source /oracle/domains/osb_domain/bin/setDomainEnv.sh
[oracle@app1osbgrepora1l osb_domain]$ java weblogic.WLST decryptPassword.py /oracle/domains/osb_domain/ {AES}WdbfYhD1EbVXmIe62hLftef4WtNPvyRDGc1/lsyQ014=
Initializing WebLogic Scripting Tool (WLST) …
Welcome to WebLogic Server Administration Scripting Shell
Type help() for help on available commands
RESULT:OSBPASS123

That’s all for today
Jackson.

Flashback – Part 2 (Flashback Query)

Hey team,

This is the second part of our Flashback Tutorial and today we’re gonna talk about FLASHBACK QUERY. Please check here for the first post about Flashback Drop.

Let’s go:

FLASHBACK QUERY

In the last Flashback post, we learnt about restoring tables that were dropped from the database with the RecycleBin facility. But if you think about it, it’s way more likely that a table suffer an undesirable change, than actually be dropped. Example, when you UPDATE a table with values that are not correct, or delete values (and commit, of course), and so on, wouldn’t it be great if we could come back in the past and see how it was before the change? Thanks to the almighty Oracle Database we can! We can use the Flashback Query to see how a table was at a specific time in the past. And the best part of it, is if you are the owner of your table, you can do it yourself, no need to bother the DBA with that (definetely the best part), and you can correct your own mistakes. Also, please keep in mind that for FLASHBACK QUERY to work, we need to have our undo properly configured. To illustrate that, let’s see an example:

Let’s create our same old table:

CREATE TABLE grepora
( column1 VARCHAR2(30),
 column2 VARCHAR2(40),
 column3 VARCHAR2(20) )
  5   TABLESPACE users;

Table created.

Then, let’s insert some values on it:

SQL> insert into grepora values ('value1', 'value2', 'value3');

1 row created.

SQL> insert into grepora values ('line2', 'line2', 'line2');

1 row created.

SQL> insert into grepora values ('line3', 'line3', 'line3');

1 row created.

SQL> insert into grepora values ('line4', 'line4', 'line4');

1 row created.

SQL> insert into grepora values ('line5', 'line5', 'line5');

1 row created.

SQL> commit;

See how the table is at the moment:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

Get the SYSDATE, to know the exact date where you have this amount of data:

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> 
SQL> select sysdate from dual;

SYSDATE
-------------------
20/06/2016 16:36:07

Now, let’s make some “mistakes” here, try to change the content of the table, deleting and updating values:

SQL> delete from grepora where column1='line5';

1 row deleted.

SQL> update grepora set column1='line1', column2='line1', column3='line1' where column1='value1';

1 row updated.

SQL> commit;

Commit complete.

And see how the table is right now:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

Check that the content data of the table is different from the original version after our changes. How can we revert that if we didn’t know how it was before that?

We use the famous AS OF TIMESTAMP statement, which allow us to see the table in a different time in the past.

With the example below, check that after using the clause AS OF TIMESTAMP and using the date we caught before to DML our table, we can find the same previous data:

SQL> select * from grepora as of timestamp (to_timestamp('20/06/2016 16:36:07', 'dd/mm/yyyy hh24:mi:ss'));

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

And the current version:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

With this feature, we can see how a table was “before the mistake” and do the proper actions to fix it.

I hope it was clear to everyone, if you have any doubt, please get in touch with GrepOra and we’ll be glad to help.

For the next post, we’ll be doing a test case for FLASHBACK VERSIONS QUERY! Stay Tuned!

Rafael.

 

1º GrepOra Meeting!

Hi all!
Last Monday we had the first GrepOra formal meeting (ok, it was a barbecue) to talk about the blog. As you know, we are friends from other situations, so it was pretty hard to focus, specially with some beer and wine…

Anyway, things were discussed include:
– Implement a ‘Posting Agenda‘ to organize and distribute the posts. It’s already done, take a look.
– Implement a ‘Event Schedule‘ about participations in meeting of community, presentations or other events we judge interessant. It’s a good site to place the link about this king osf stuffs. Its already done too, here.
– Review blog layout. Any suggestion? leave a comment.
– Review (or create) an oficial logo. It’s quite ugly by about 1 year…
– It was spoken some stuffs about events participation, but I’m not sure what…

Had we discussed anything more?

Oh!
And we registered this momment! Here we go:

IMG-20160615-WA0001

Thanks guys!