ORA-00904: “DBMS_XSTREAM_GG_ADM”.”SYNCHRONIZATION”, PLS-00201: identifier ‘DBMS_XSTREAM_GG_ADM’ must be declared

Problem:

While running command in GGSCI, I received the following messages:

GGSCI (rac1.mycompany.mydomain as ggcw@orcl2) 4>  ADD TRANDATA HR.*
...
2024-04-06 00:13:51  WARNING OGG-00706  Failed to add supplemental log group on table HR.TEST due to ORA-06550: line 1, column 46:
PL/SQL: ORA-00904: "DBMS_XSTREAM_GG_ADM"."SYNCHRONIZATION": invalid identifier
ORA-06550: line 1, column 39:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 109:
PLS-00201: identifier 'DBMS_XSTREAM_GG_ADM' must be declared
ORA-06550: line 1, column 109:
PL/SQL: Statement ignored
ORA-06550: line 1, column 156:
PLS-00201: identifier 'DBMS_CAPTURE_ADM' must be declared
ORA-06550: line 1, column 156:
PL/SQL: Statement ignored
ORA-06550: line 1, column 263:
PLS-00201: identifier 'DBMS_XSTREAM_GG_ADM' must be declared
ORA-06550: line 1, column 263:
PL/SQL: Statement ignored
 SQL DECLARE saved_sync varchar2(4); BEGIN select dbms_xstream_gg_adm.synchronization into saved_sync from dual; dbms_xstream_gg_adm.synchronization := 'NONE'; DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => '"HR"."TEST"', supplemental_logging => 'none'); dbms_xstream_gg_adm.synchronization := saved_sync; END;.

Solution:

Connect to the database via sqlplus and grant the below privileges to GG user (ggcw in my case):

exec dbms_goldengate_auth.grant_admin_privilege('ggcw');
grant select any dictionary to ggcw;
grant insert any table to ggcw;
grant update any table to ggcw;
grant delete any table to ggcw;

Rerun the statement:

GGSCI (rac1.mycompany.mydomain as ggcw@orcl2) 4>  ADD TRANDATA HR.*

WARNING OGG-00706 Failed to add supplemental log group on table HR.TEST due to ORA-01031: insufficient privileges

Problem:

I received a warning while running the following command in GGSCI:

GGSCI (rac1.mycompany.mydomain as ggcw@orcl2) 2> ADD TRANDATA HR.*
...
2024-04-06 00:11:56  WARNING OGG-00706  Failed to add supplemental log group on table HR.TEST due to ORA-01031: insufficient privileges
 SQL ALTER TABLE "HR"."TEST" ADD SUPPLEMENTAL LOG GROUP "GGS_76882" ("A") ALWAYS  ENABLE LOGICAL REPLICATION ALL KEYS   /* GOLDENGATE_DDL_REPLICATION */.

Solution:

Connect to the database via sqlplus and grant necessary permission to GG user (ggcw in my case):

$ export ORACLE_SID=orcl1
$ sqlplus / as sysdba
SQL> grant alter any table to ggcw;

Rerun the statement:

GGSCI (rac1.mycompany.mydomain as ggcw@orcl2) 4>  ADD TRANDATA HR.*

OPatchauto fails: CLSRSC-180: An error occurred while executing the command ‘/bin/rpm -qf /sbin/init’

Problem:

During applying ACFS patch on top of GI home, I received the following error:

Command failure output:
...
2024/03/08 19:31:03 CLSRSC-180: An error occurred while executing the command '/bin/rpm -qf /sbin/init'

After fixing the cause of failure Run opatchauto resume

]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.

OPatchauto session completed at Fri Mar  8 19:31:04 2024
Time taken to complete the session 3 minutes, 38 seconds

 opatchauto failed with error code 42

Troubleshooting:

I attempted to manually execute the command that failed, and it returned a helpful error message:

[root@rac1 tmp]# /bin/rpm -qf /sbin/init
error: rpmdb: BDB0113 Thread/process 5003/139974823143296 failed: BDB1507 Thread died in Berkeley DB library
error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db5 -  (-30973)
error: cannot open Packages database in /var/lib/rpm
...

Solution:

I have a solution for this type of error in another post. Let’s solve it again:

[root@rac1 tmp]# rpm  --rebuilddb

Rerun the failing command to make sure it was resolved:

[root@rac1 tmp]# /bin/rpm -qf /sbin/init
systemd-239-78.0.3.el8.x86_64

If you encountered an error during patching, you can resume opatchauto at this point:

[root@rac1 tmp]# /u01/app/19.3.0/grid/OPatch/opatchauto resume

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:rac1
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /tmp/36114443/36114443
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-03-08_19-29-48PM_1.log

Good luck, as always!

How to find “IOPs per instance type” info in AWS?

Problem:

An instance’s EBS performance is limited by the performance of its attached volumes or the instance, whichever is lower.

When attaching volumes, we need to consider the instance type’s limitations on maximum IOPs.

Let’s determine the maximum IOPs that the target instance type can provide.

Solution:

There are multiple sources of information available, however, some of them are dry and lack detail.

I will provide two possible ways to check that info in detail:

AWS webpage: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-optimized.html

AWS CLI:

 aws ec2 describe-instance-types \
 --instance-types r5.2xlarge \
 --query InstanceTypes[].EbsInfo

In the above example, we used the r5.2xlarge instance type. Please specify your desired instance type to determine its corresponding EBS information.

Rename directories, subdirectories, files recursively that contain matching string

Problem:

I have copied /u01 directory (containing Oracle software) from another node. The Oracle software home includes directories and files with hostnames.

My task was to rename all directories, subdirectories, and files containing the specific hostname (in my case rac2) into rac1.

Let me show you the folder hierarchy that is challenging when you want to rename by script. For simplicity, this hierarchy is made up, but this type of dependency exists in /u01:

/u01/first_level_rac2/second_level_rac2/third_level_rac2.txt

We want to have:

/u01/first_level_rac1/second_level_rac1/third_level_rac1.txt

So finally, all folders or files containing the string rac2 should be replaced with rac1.

The challenge here is that you need to start renaming from the third_level, then rename second_level and later first_level. Otherwise, you will have accessibility issues with other subdirectories or files.

Solution:

If you want a shortcut, here is the code:

[root@rac1 ~]# find /u01 -depth -name "*rac2*" | while read i ; do
newname="$(echo ${i} |sed 's/\(.*\)rac2/\1rac1/')" ;
echo "mv" "${i}" "${newname}" >> rename_rac2_rac1.sh;
done

Later you need to run rename_rac2_rac1.sh file, which will contain mv statements for each matching file or folder.

Let me explain,

find /u01 -depth -name "*rac2*" – This will find all files and folders that contain rac2 keyword and will display the output with depth-first order.

Without depth, the output is the following:

/u01/first_level_rac2
/u01/first_level_rac2/second_level_rac2
/u01/first_level_rac2/second_level_rac2/third_level_rac2.txt

With -depth, you will see the next order:

/u01/first_level_rac2/second_level_rac2/third_level_rac2.txt
/u01/first_level_rac2/second_level_rac2
/u01/first_level_rac2

"$(echo ${i} |sed 's/\(.*\)rac2/\1rac1/')" – In this line, the value of i iterator (each line from find command) will be redirected to sed command that will replace the first occurrence of rac2 keyword searching from backward.

Later old name and a new name will be concatenated with mv statement and saved into rename_rac2_rac1.sh

This will be mv statements generated by the script:

mv /u01/first_level_rac2/second_level_rac2/third_level_rac2.txt /u01/first_level_rac2/second_level_rac2/third_level_rac1.txt

mv /u01/first_level_rac2/second_level_rac2 /u01/first_level_rac2/second_level_rac1

mv /u01/first_level_rac2 /u01/first_level_rac1

[WARNING] [INS-08102] Unexpected error occurred while transitioning from state ‘DBIdentification’

Problem:

While using dbca to create a database I get the following error:

[oracle@rac1 ~]$ dbca -silent -createDatabase \
>   -responseFile NO_VALUE \
>   -templateName New_Database.dbt \
>   -dbOptions "OMS:false,JSERVER:false,SPATIAL:false,IMEDIA:false,ORACLE_TEXT:false,SAMPLE_SCHEMA:false,CWMLITE:false,APEX:false,DV:false" \
>   -gdbname racdb \
>   -characterSet AL32UTF8 \
>   -sysPassword Oracle123 \
>   -systemPassword Oracle123 \
>   -databaseType MULTIPURPOSE \
>   -automaticMemoryManagement false \
>   -totalMemory 6144 \
>   -redoLogFileSize 512 \
>   -nodelist rac1,rac2 \
>   -storageType ASM \
>   -diskGroupName DATA01 \
>   -recoveryGroupName DATA01 \
>   -recoveryAreaSize 20000 \
>   -initParams "log_buffer=128M,processes=3000" \
>   -asmsnmpPassword Oracle123
[WARNING] [INS-08102] Unexpected error occurred while transitioning from state 'DBIdentification'
   CAUSE: No additional information available.
   ACTION: Contact Oracle Support Services or refer to the software manual.

On DBCA GUI it looks like this:

Solution:

On the Oracle site or on the Internet, there is no useful information that can help so far. The only place you should search for the reason is in dbca logs generated under /u01/app/oracle/cfgtoollogs/dbca

[root@rac1 dbca]# pwd
/u01/app/oracle/cfgtoollogs/dbca

[root@rac1 dbca]# ll
total 1344
drwxr-xr-x 2 root   root        4096 Jul 14 18:48 old
drwxr-x--- 2 oracle oinstall       6 Apr 28 16:25 orcl
-rw-r----- 1 oracle oinstall 1370046 Jul 14 18:51 trace.log_2023-07-14_06-51-03PM
-rw-r----- 1 oracle oinstall       0 Jul 14 18:51 trace.log_2023-07-14_06-51-03PM.lck

The latest trace file contains the following error messages:

[Thread-355] [ 2023-07-14 18:51:35.375 UTC ] [StreamReader.run:66]  OUTPUT> DIA-49802: missing read, write, or execute permission on specified ADR home directory [/u01/app/grid/diag/crs/rac1/crs/log]
[Thread-355] [ 2023-07-14 18:51:35.375 UTC ] [StreamReader.run:66]  OUTPUT>DIA-49801: actual permissions [rwxr-xr-x], expected minimum permissions [rwxrwx---] for effective user [oracle]
[Thread-355] [ 2023-07-14 18:51:35.375 UTC ] [StreamReader.run:66]  OUTPUT>DIA-48188: user missing read, write, or exec permission on specified directory
....
[Thread-363] [ 2023-07-14 19:01:53.417 UTC ] [StreamReader.run:66]  OUTPUT> DIA-48141: error creating directory during ADR initialization [/u01/app/grid/diag/crs/rac1/crs/trace]
[Thread-363] [ 2023-07-14 19:01:53.417 UTC ] [StreamReader.run:66]  OUTPUT>DIA-48189: OS command to create directory failed
[Thread-363] [ 2023-07-14 19:01:53.417 UTC ] [StreamReader.run:66]  OUTPUT>Linux-x86_64 Error: 1: Operation not permitted

Let’s check permissions on folders under /u01/app/grid/diag/crs/rac1/crs

[root@rac1 dbca]# ll /u01/app/grid/diag/crs/rac1/crs/

drwxr-xr-x 2 grid oinstall   21 Jul 14 16:09 alert
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 cdump
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 incident
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 incpkg
drwxr-xr-x 2 grid oinstall 4096 Jul 12 21:33 lck
drwxrwxr-x 5 grid oinstall   48 Jul 12 21:33 log
drwxr-xr-x 2 grid oinstall 4096 Jul 12 21:33 metadata
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 metadata_dgif
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 metadata_pv
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 stage
drwxr-xr-x 2 grid oinstall    6 Jul 12 21:33 sweep
drwxr-xr-x 2 grid oinstall 8192 Jul 14 18:43 trace

When I have the same case, I always check the same folder on a healthy server, let’s verify permissions on a healthy node:

[root@rac2 oraInventory]# ll /u01/app/grid/diag/crs/rac2/crs

drwxrwxr-x 2 grid oinstall    21 Apr 26 17:53 alert
drwxrwxr-x 5 grid oinstall    57 Jul 13 21:43 cdump
drwxrwxr-x 7 grid oinstall    89 Jul 13 21:43 incident
drwxrwxr-x 2 grid oinstall     6 Apr 26 17:53 incpkg
drwxrwxr-x 2 grid oinstall  4096 Jul 13 21:43 lck
drwxrwxr-x 5 grid oinstall    65 Jun  1 00:36 log
drwxrwxr-x 2 grid oinstall  4096 Apr 26 17:53 metadata
drwxrwxr-x 2 grid oinstall     6 Apr 26 17:53 metadata_dgif
drwxrwxr-x 2 grid oinstall     6 Apr 26 17:53 metadata_pv
drwxrwxr-x 2 grid oinstall   119 Jul 13 21:43 stage
drwxrwxr-x 2 grid oinstall   124 Jul 13 21:43 sweep
drwxrwxr-x 2 grid oinstall 32768 Jul 14 18:43 trace

Now it’s time to correct permissions on the problematic node:

[root@rac1 dbca]# chmod -R 775 /u01/app/grid/diag/crs/rac1/crs/*

[root@rac1 dbca]# ll /u01/app/grid/diag/crs/rac1/crs/

drwxrwxr-x 2 grid oinstall   21 Jul 14 16:09 alert
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 cdump
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 incident
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 incpkg
drwxrwxr-x 2 grid oinstall 4096 Jul 12 21:33 lck
drwxrwxr-x 5 grid oinstall   48 Jul 12 21:33 log
drwxrwxr-x 2 grid oinstall 4096 Jul 12 21:33 metadata
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 metadata_dgif
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 metadata_pv
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 stage
drwxrwxr-x 2 grid oinstall    6 Jul 12 21:33 sweep
drwxrwxr-x 2 grid oinstall 8192 Jul 14 18:43 trace

It is now possible for DBCA to proceed without any issues.
Good Luck!

Copy a file over SSH without SCP

Problem:

/usr/bin/scp binary was removed from the system. Which has caused the Oracle Patching process to fail.

scp binary is provided by openssh-clients rpm, which was present on the system, but scp binary was missing.

Troubleshooting/Testing:

The workaround is to copy scp binary from a similar healthy server (keep the same version). However, transferring a file to a location where it doesn’t exist can be a bit challenging. Let’s try:

[fg@rac1 ~]$ scp /usr/bin/scp racq:/tmp/scp
bash: scp: command not found
lost connection

We got lost connection, because scp is not on racq node.

Solution:

Need to use ssh and cat commands. For most systems root user login is not enabled, so you need to place the file under /tmp and then relocate to the correct location.

In my example, I have already set up fg user equivalency, so in my case, the format will be the following:

[fg@rac1 ~]$ ssh racq cat < /usr/bin/scp ">" /tmp/scp

Connect to the remote server and copy /tmp/scp to the correct location. Reset permissions.

[root@racq tmp]# cp /tmp/scp /usr/bin/scp
[root@racq tmp]# chmod 755 /usr/bin/scp
[root@racq tmp]# chown root:root /usr/bin/scp

The transfer should be working now:

[fg@rac1 ~]$ scp /usr/bin/scp racq:/tmp/scp
scp      100%   89KB  44.0MB/s   00:00

The process worked for a binary file, so it will work for a text file too.

aad_certhandler: The public key is of type ssh-rsa, not a certificate.

Problem:

I am currently experiencing difficulty connecting to the Azure VM using AD authentication. I am receiving an error message that states:

~ az ssh  vm -n rac1 -g marirac
OpenSSH_9.0p1, LibreSSL 3.3.6
...: Permission denied (publickey).
The OpenSSH server version in the target VM 7.4 is too old. Version incompatible with OpenSSH client version 9.0. Refer to https://bugzilla.mindrot.org/show_bug.cgi?id=3351 for more information.

When I check /var/log/secure log on the server side, it shows:

Jun  6 16:49:54 rac1 sshd[27249]: Connection closed by ... port 51572 [preauth]
Jun  6 16:54:44 rac1 sshd[31386]: nss_aad: This is an Azure machine
Jun  6 16:54:44 rac1 aad_certhandler[31393]: Version: 1.0.022600002; user: ...
Jun  6 16:54:44 rac1 aad_certhandler[31393]: The public key is of type ssh-rsa, not a certificate.
Jun  6 16:54:44 rac1 sshd[31386]: error: AuthorizedKeysCommand /usr/sbin/aad_certhandler ...
Jun  6 16:54:44 rac1 sshd[31386]: Connection closed by ... port 52092 [preauth]

I have intentionally redacted certain portions of the information for security reasons, although errors are still apparent.

Troubleshooting:

Client and server versions are different:

Server:

[root@rac1 ~]# ssh -V
OpenSSH_7.4p1, OpenSSL 1.0.2k-fips  26 Jan 2017

Client:

 ~ ssh -V
OpenSSH_9.0p1, LibreSSL 3.3.6

Workaround:

Pass the following option to ssh when using az ssh Command:

~ az ssh  vm -n rac1 -g marirac -- -o PubkeyAcceptedKeyTypes=+ssh-rsa-cert-v01@openssh.com

Azure CLI install ssh extension

Problem:

To enable AD authentication on a Linux OS Azure VM, you must install Azure CLI and have the SSH extension. However, the SSH extension is not installed automatically after installing Azure CLI.

I can guide you through the simple steps to add this extension.

Solution:

Ensure that the extension is not present:

~ az version
{
  "azure-cli": "2.49.0",
  "azure-cli-core": "2.49.0",
  "azure-cli-telemetry": "1.0.8",
  "extensions": {}
}

List available extensions:

~ az extension list-available --output table

Name    Version    Summary  Preview  Experimental  Installed
------- --------   -------  -------  ------------  --------------------------
...
ssh     1.1.6      SSH...   False    False         False
...

Add extension:

~ az extension add --name ssh

Ensure that the extension has been added:

~ az version
{
  "azure-cli": "2.49.0",
  "azure-cli-core": "2.49.0",
  "azure-cli-telemetry": "1.0.8",
  "extensions": {
    "ssh": "1.1.6"
  }
}

Export all images easily from Google Drive Word document

Let’s assume you have created a Word document on Google Drive and inserted tons of screenshots inside.

You have been asked to export all images from this document for verification. While there are multiple ways to accomplish this, I have found the fastest solution.

1. Go to the section File -> Download -> Web Page

2. Go to the Download location and unzip the file

3. Under the extracted folder, you will find a sub-folder called images, which will contain all images that the Word document contains.