Tag Archives: Oracle

Services are useful, especially for tracing


(Picture by Shane Hauser, via Unsplash)

A fellow developer recently asked me if I could help him check if a bug had been fixed in production or not, because he could not trace (pun intented) recent changes. The problem consisted in 2 specific queries launched a gazillion times on an Oracle 12.1 database. Among other things, I wanted to get some traces, so I asked questions to better understand and narrow down what should be traced.
Continue reading

Oracle Trace File Analyzer (TFA) – 18.2.1 REST services installation and examples

Nesting dolls

(Picture by allysonmiller1969 [CC BY-SA 3.0], via Pixabay)

After having upgraded TFA in version 18.2.1, I really wanted to configure and start using its new REST support feature (since 18.2.0) : “Oracle Trace File Analyzer now includes REST support, allowing invocation & query over HTTPS. […] REST supports printing details, starting a diagcollect and downloading collections”

First, I simply tried to start TFA REST services :

# sudo /grid/infrastructure/home/bin/tfactl rest -start

Configuring TFA REST Services using ORDS :

This might take couple of minutes. Please be patient.

Adding Dependency Jars to ORDS

Adding users to ORDS :

Enter a password for user tfaadmin:
Confirm password for user tfaadmin:

Enter a password for user tfarest:
Confirm password for user tfarest:

Starting TFA REST Services

Failed to start TFA REST Services

URL : https://node01:9090/ords/tfactl/print/status

NOTE : The Standalone Oracle Rest Data Services (ORDS) setup feature utilizes file based user
authentication and is provided solely for use in test and development environments.

But I got an error : Failed to start TFA REST Services. What is the problem ?

Continue reading

Oracle Trace File Analyzer (TFA) – Installation/upgrade as root without SSH, and synchronization between nodes

Animal footprints

(Picture by Clker-Free-Vector-Images [CC BY-SA 3.0], via Pixabay)

Working on a 3-node Oracle Grid Infrastructure cluster on Red Hat Enterprise Linux Server release 7.4, I need to upgrade TFA to the latest version which is 18.2.1, as of today. I don’t want to configure passwordless SSH user equivalency for root and need to use sudo. Official documentation indicates :

“If you do not want to use ssh, you can install on each host using a local install. Then use tfactl syncnodes to generate and deploy the relevant SSL certificates.”

Here are the steps I follow to upgrade TFA :

Continue reading

SQLcl, login.sql, format and alias


(Picture from Oracle.com)

There are (more than) two things that I love with SQLcl : formatting and aliases.

I work with several Grid Infrastructure clusters, hosting hundreds of databases accross multiple nodes. I am very often typing the same queries, this is where SQLcl comes in handy. You can use a login.sql file with SQLcl, just like you would use it with SQL*Plus.
After some tests, I found out that SQLcl looks for a login.sql file in directory and subdirectories pointed by the environment variable SQLPATH.

Continue reading

Prerequisites to debug PL/SQL in Oracle Database 12c with SQL Developer


(Picture by Nata-Ap [CC BY-SA 3.0], via Pixabay)

Why I am so obsessed with ACL ? I have no idea ! Actually it is the other way round, ACL are hunting me.
This morning, I helped a fellow developer who wanted to debug his new PL/SQL procedure with SQL Developer, on a non-CDB 12.1 Oracle Database.

His first attempt generated errors :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
Process exited.
Disconnecting from the database test_debugger.

I issued this command, thinking it would fix the problem :


But he encountered errors again :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database test_debugger.

Continue reading

Duplicate from a previous incarnation without connecting to target database

Cute ghosts

(Picture by OpenClipart-Vectors [CC BY-SA 3.0], via Pixabay)

Like most DBA, sometimes I need to generate AWR reports, nothing very complex so far. But occasionally, I need to cover a period of time which is BEFORE the database I am working on has been overwritten with a duplicate operation … Which means it does not contain relevant AWR snapshots anymore.

Continue reading

Fun with Access Control Entries in Oracle 12cR1 – Part 2 : ACE and Data Pump

Porte d'Aix, Marseille

(Picture by Tiia Monto [CC BY-SA 3.0], via Wikimedia Commons)

As explained in my previous blog post, in an attempt to develop a solid script to handle ACE (who says I am a quibbler ?), I also played with Data Pump to export and import ACE/ACL in Oracle 12cR1.
I found out that OBJECT_PATH=NETWORK_ACL exists in database_export_objects. I could not get many information about that, so I got confirmation from My Oracle Support that NETWORK_ACL was the right OBJECT_PATH to use.

This blog post consists in a quick reminder on how to export and import ACE/ACL with Data Pump.

Continue reading

Fun with Access Control Entries in Oracle 12cR1 – Part 1 : A way to extract ACE without Data Pump

Château de Versailles, Grille royale.

(Picture by Miguel Hermoso Cuesta (Own work) [CC BY-SA 3.0], via Wikimedia Commons)

My work environment has approximately 100 production databases and 400 non-production databases. We developed a self-service web application (with APEX 😉 ) that offers developers the right to clone databases. We have some development databases with ACL, concerning development hosts, which are relevant only in development environment. And of course, some production databases with ACL, concerning production hosts, which are relevant only in production environment.

What happens when we duplicate a production database on a development database ? The ACL from the production database are now on the development database. And the development ACL are gone, obviously.

What can we do to keep those development ACL on the development database after a duplication ?

Continue reading

Bulk relocation of OEM targets related to former agents

I currently work on a 4-node cluster with Grid Infrastructure installed, hosting several 11g and 12c databases. As it is a cold failover cluster, databases are not RAC databases and are active on only one node at a time.

Thoses databases share the same action script. The “start” section of this action script is quite complex and also manages the relocation of the database target on Enterprise Manager But this specific task is time-consuming (approximately 30 seconds for 1 target) and is not always successful. Which means I often have to relocate some targets in Enterprise Manager by hand.

Recently, 2 nodes of our cluster did not survive a major storage outage. After their reboot, the databases restarted (quite slowly) according to the action script. But target relocation did not work as expected for all targets and I had to manually fix the mess.

Continue reading

Checklist to determine if an Oracle schema is self-contained

For a few months, I have been working on massive migrations of Oracle 10g/11g databases on Solaris to 12c on Linux. Most of these databases are highly consolidated with several schemas (no PDBs of course), and these schemas are used by different applications. These applications have supposedly nothing do to with each other. Of course, those applications being different, they follow different roadmaps. Knowing this, it was quite impossible to migrate the whole database at once, and schema-by-schema migration was relevant and comfortable for everybody.

I realized that the main challenge to address was not technical complexity, it was more about understanding the muddle between schemas that should be distinct.
Everytime I had to migrate another schema, I wondered : Is this particular schema “self-contained” ? Can I move it with Datapump from one database to another “as is”, with minimum work and error handling ?
I knew DBMS_TTS.TRANSPORT_SET_CHECK procedure could be helpful at tablespace level, but I needed information at schema level.

I still have no magic wand to separate dependant schemas, but at least I have a checklist to identify wether a schema is self-contained or not.

Continue reading