Oracle GoldenGate 21c -- How Triggers Fired during Replication

Problem

You need a better understanding on how Oracle database triggers work with Oracle GoldenGate replication

Solution

By default in an Oracle database, triggers do fire only when a statement executed from a user process, and do not fire in case of Oracle-maintained processes, like Logical standby SQL Apply or Oracle Streams replication processes 

The property can be changed by using the procedure:

DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY

When set to FALSE, triggers will fire during replication, that is, the statements issued by Replicat processes

OCI Autonomous Database -- How to Create a DB Link

Problem

You want to create a database link on an Autonomous Database in OCI (Oracle Cloud Infrastructure)

Solution

Use commands below to create a db link:

If db link being created in a schema other than ADMIN, grant required privileges:

SQL> grant execute on DBMS_CLOUD_ADMIN to <schema>;
SQL> grant CREATE DATABASE LINK to <schema>;

Create credential;

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => '<credential_name>',
    username        => '<username>',
    password        => '<password>'
  );
END;

Create database link:

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name       => '<db_link_name>',
          hostname           => 'target_db_host_name',
          port               => '<port>',
          service_name       => '<service_name>',
          ssl_server_cert_dn => NULL,
          credential_name    => '<credential_name>',
          directory_name     => NULL,
          private_target     => true);
END;

Cygwin -- File permissions for ssh

Problem

While configuring openssh for cygwin, ssh key authentication does not work, the key does not get accepted 

Solution

Make sure the .ssh directory and the authorized_keys files have required permissions and ownership:

chown <your_username>:none ~/
chmod g-w ~/
chmod 700 ~/.ssh/
chmod 600 ~/.ssh/authorized_keys

AIX 7.2 -- setreuid fails with PermissionError

Problem

You want to change euid or ruid for a process, for example, the snippet below tries to start bash as user oracle executed by user grid:

[grid] $ python3 -c 'import os; import pwd; import sys; import subprocess; uid=pwd.getpwnam("oracle").pw_uid; os.setreuid(uid,uid); retval = subprocess.run(["bash", "--norc"]).returncode; sys.exit(retval);'
Traceback (most recent call last):
  File "<string>", line 1, in <module>
PermissionError: [Errno 1] Not owner 

Though executing the same snippet as user root succeeds

Solution

AIX 7.2 provides Role Based Access Control (RBAC) mechanisms which allows to control certain security aspects of a system. 

In particular, RBAC allows to grant privileges to users, roles and process to perform certain actions

To allow a process to call setreuid() run as root:

# setsecattr -c accessauths=ALLOW_ALL innateprivs=PV_DAC_O,PV_DAC_UID secflags=FSF_EPS euid=0 egid=0 /opt/bin/python3.7
# setkst

 Note. Aliases not accepted by setsecatt, only direct executables

[grid] $ python3 -c 'import os; import pwd; import sys; import subprocess; uid=pwd.getpwnam("oracle").pw_uid; os.setreuid(uid,uid); retval = subprocess.run(["bash", "--norc"]).returncode; sys.exit(retval);'
[oracle] $ 

Reference:

• Role-based access control

Oracle Database 11.2 -- Manually Install Java Component in an Oracle Database

Problem

You want to manually install the Java Component in a Oracle Database version 11.2 and up

Solution

Note. The solution is application only for the first-time installation. If the Java component was previously installed, then this procedure is not applicable for repair/re-install. 

1) Optionally, cleanly stop the database, create a guaranteed restore point, enable restricted session:

shutdown immediate;
startup mount;
alter database archivelog;
alter system enable restricted session;
alter database open;
alter system switch logfile;
create restore point before_java guarantee flashback database;

2) Install Java component:

alter system set "_system_trig_enabled" = false scope=memory;
alter system set "java_jit_enabled" = false scope=memory;
spool /tmp/full_jvminst.log;
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
11g only @?/rdbms/admin/catexf.sql
@?/rdbms/admin/utlrp.sql
alter system set java_jit_enabled = true scope=memory;
alter system set "_system_trig_enabled"=true scope=memory;
spool off

3) In case of any of the actions of Step 1) was performed, perform the reverse actions:

drop restore point before_java;
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;

Reference:

• Safe repair/reinstall of the JVM Component in 11.2 and up (Doc ID 2314363.1)
• Script to Check the Status or State of the JVM within the Database (Doc ID 456949.1) 

 

Oracle Database 12.2 -- How to Create a Dump of a Controlfile

Problem

You need to create a dump of a controlfile

Solution

Use the below command to dump a controlfile to a trace file:

alter session set events 'immediate trace name controlf level 9';

Identify the location of a trace file by either:

v$diag_info

or

oradebug setmypid
oradebug tracefile_name

 

Oracle Database 19c -- Disable VIEW PUSHED PREDICATE

Problem

You want to disable VIEW PUSHED PREDICATE transformation in a session or for the whole system

Solution

Apply the hidden parameter at session or system level:

alter session set "_optimizer_push_pred_cost_based"=false; 

Oracle Database 19c -- Display Actual Rows along with Estimated Rows on Execution Plans

Problem

You want to have Actual rows and Estimated rows statistics displayed on execution plan of a query

Solution

First enable the optimizer statistics gathering for a statement using hint:

/*+ GATHER_PLAN_STATISTICS */

After, display the execution plan with:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

Note that the above statement will display the execution plan of the last executed statement within that session

Oracle Linux 7 -- How To Get A Topmost Root CA Of A Web Resource

Problem

You need to extract the topmost root CA certificate from the certificate chain of a web resource

Solution

First identify the certificate chain:

$ openssl s_client -connect google.com:443 -showcerts < /dev/null
CONNECTED(00000003)
depth=3 C = BE, O = GlobalSign nv-sa, OU = Root CA, CN = GlobalSign Root CA
verify return:1
depth=2 C = US, O = Google Trust Services LLC, CN = GTS Root R1
verify return:1
depth=1 C = US, O = Google Trust Services LLC, CN = GTS CA 1C3
verify return:1
depth=0 CN = *.google.com

The topmost root CA will be the one with highest depth (depth=3 in the case above), but it will probably be missing on the displayed chain of certificates, the last displayed certificate is for depth=2 in the case above:

 2 s:/C=US/O=Google Trust Services LLC/CN=GTS Root R1
   i:/C=BE/O=GlobalSign nv-sa/OU=Root CA/CN=GlobalSign Root CA
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----

Save content of the last certificate in a temp file and issue the command against it:

$ openssl x509 -in /tmp/cert2.cert -text

Authority Information Access:
OCSP - URI:http://ocsp.pki.goog/gsr1
CA Issuers - URI:http://pki.goog/gsr1/gsr1.crt

Download the root CA certificate file (in this case gsr1.crt) and verify that certificate matches the root CA reported in the first output above (with depth=3):

$ wget http://pki.goog/gsr1/gsr1.crt

$ openssl x509 -in gsr1.crt -text -inform DER

Issuer: C=BE, O=GlobalSign nv-sa, OU=Root CA, CN=GlobalSign Root CA

The certificates matches

Optionally save the certificate in a PEM format file (the data block enclosed with BEGIN CERTIFICATE and END CERTIFICATE lines)

Oracle Database 19c -- Parse Listener Log to Identify Connected Clients

Problem

You need to identify all connected clients to a database for a specific interval of time.

Audit was not previously enabled for successful logons

Solution

You may parse the listener log file to extract all previously connected clients

1) Identify the listener log location using: 

$ LISTENER_LOG=$(lsnrctl status | grep "Listener Log File" | sed 's|alert/log.xml|trace/listener.log|' | awk '{print $4}')

$ echo $LISTENER_LOG

2) Visually check the log file to determine the timestamp format, different releases had different timestamp formats

3) Extract the range of records of interest for faster processing:

$ awk '/14-DEC-2023/,/non_matching_string/ {print $0}' $LISTENER_LOG > /tmp/listener_part.log

The above command will extract all records starting from 14-DEC-2023 till the end of the file. Replace non_matching_string with a non-inclusive timestamp to limit the range from the end

4) Extract IP addresses of the previously connected clients:

$ grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)\(PORT=\d+\)" /tmp/listener_part.log | grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)"| grep -P -o "\d+\.\d+\.\d+\.\d+" | sort -u > /tmp/ips.txt

/tmp/ips.txt will contain a list of IP addresses

5) Optionally generate hostnames for the IP addresses extracted in previous steps:

$ grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)\(PORT=\d+\)" /tmp/listener_part.log | grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)"| grep -P -o "\d+\.\d+\.\d+\.\d+" | sort -u | xargs -I {} host {} | awk '{print $5}' | grep . > /tmp/hosts.txt

6) If hostnames generated, present the both lists as single view:

$ paste /tmp/ips.txt /tmp/hosts.txt