(Picture by Jannes Glas, via Unsplash)
I have to admit that I always volunteer at work when a patching has to be done. I like patching. Not the patching process itself, but the learning process. Sometimes it even helps me get better at troubleshooting.
Today I just want to gather in one post a few things that came up after applying Database Proactive Bundle Patch + OJVM PSU 12.1.0.2.180717, hoping this can help someone somewhere 🙂 I will update this post later if I encouter new issues.
(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
(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 ?
(Picture by Clker-Free-Vector-Images [CC BY-SA 3.0], via Pixabay)
Working on a 3-node Oracle 12.2.0.1 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 :
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.
(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 :
GRANT DEBUG CONNECT SESSION TO HIS_SCHEMA ;
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.
(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.
(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.
(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.
I currently work on a 4-node cluster with 12.1.0.2 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 12.1.0.5. 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.
Floo Bar 
