SQL Developer ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specified

Problem:

Connection from the SQL Developer fails with the following error:

ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specified

Solution:

Find sqldeveloper.conf file:

$ find / -name sqldeveloper.conf 2>>/dev/null

^@/System/Volumes/Data/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf
/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf

Add the following two parmeters in /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

Restart SQL Developer and try connection again.

Backup and restore LUN0 and OS disk in Azure

==================================Backup process


1. Configure backup for rac2 which includes only LUN0 (+ OS disk, by default)

Backup configuration script from CLI: 

az backup protection enable-for-vm --resource-group marirac --vault-name vault485 --vm rac2 --policy-name  mkpolicy --disk-list-setting include --diskslist 0

Check settings from Portal: 


2. Create test files on / and /u01

[root@rac2 ~]# touch /u01/mari_u01
[root@rac2 ~]# ll /u01/mari_u01
-rw-r–r– 1 root root 0 Jun  2 15:24 /u01/mari_u01

[root@rac2 ~]# touch /mari_root
[root@rac2 ~]# ll /mari_root
-rw-r–r– 1 root root 0 Jun  2 15:24 /mari_root



==================================Restore process

1. Find the latest restore point from Azure CLI:

az backup recoverypoint list --vault-name vault485 --resource-group marirac -c rac2 -i rac2 --backup-management-type AzureIaasVM


2. Restore OS and LUN0 disks:

az backup restore restore-disks --resource-group marirac --vault-name vault485 -c rac2 -i rac2  --target-resource-group marirac --storage-account mzx41qmarivol2asrcache --diskslist 0 --rp-name 5638874691908752817


The above script restores them as managed disks, that are not attached to anything. Note restore did not replace the disk, it just restores as a new disk. 


3. To replace disks do the following:

Detach old LUN0 -> Save -> Attach restored disk as Lun 0. 

To replace existing OS disk with restored one, click Switch OS disk and then start the VM. 



4. Check that files are not there anymore:

[root@rac2 ~]#  ll /mari_root
ls: cannot access /mari_root: No such file or directory

[root@rac2 ~]# ll /u01/mariu01
ls: cannot access /u01/mariu01: No such file or directory

Find 5 biggest files in Linux

I have used this command many times, but the interval between each usage is so big that I almost always forget the syntax.

So here it is:

# du -a / | sort -n -r | head -n 5

51190272	/
37705424	/root
33040524	/root/apache-tomcat-7.0.53
32802516	/root/apache-tomcat-7.0.53/logs
32802440	/root/apache-tomcat-7.0.53/logs/catalina.out

Check what is my public ip from command line

Problem:

Need to check what is server’s public ip.

Solution:

Simply run:

# dig +short myip.opendns.com @resolver1.opendns.com
40.89.251.13

ORA-15041 during rebalance OR add disk

Problem:

One of our customers had a disk offline for more than disk_repair_time, which caused Oracle to drop 1TB disk. The problem started after that, the drop command caused rebalance operation and because of less than 1TB free space on the diskgroup, the rebalance failed with ORA-15041. Mentioned rebalance caused some of the disks to become 100% full, so free MB on some disks were 0.

Adding disks did not help, because when we were checking free space on the existing disks we were getting the following output:

# su - grid
$ sqlplus / as sysasm
SQL> select disk_number "Disk #", free_mb 
     from v$asm_disk 
     where group_number = 1 
     order by 2

    Disk #    FREE_MB
---------- ----------
        13       0
         0       0
         4       0
         3       4
        11       132900
        ...

As mentioned our rebalance was failing:

Solution

It was AWS environment and in cloud we could easily increase disk size, so we increased all disks in the diskgorup by 200GB:

Resizing steps: https://dba010.com/2019/08/23/resize-asm-disks-in-aws-fg-enabled-cluster/

Triggered Rebalance:

# su - grid
$ sqlplus / as sysasm
SQL> ALTER DISKGROUP DATA REBALANCE POWER 13; 

And after several hours rebalance finished successfully.

Please note that initially we increased space on disks by 1GB and rebalance failed again, then we increased by 200GB and the operation was successful. So you may need to increase disk size several times.

Useful note from Oracle Doc ID 473271.1

Reduce high CPU usage by TFA

Problem:

Cluster nodes experienced high CPU usage, after investigation one of the top CPU consumers on the server has been found to be a TFA process (2nd place):

 # Fri Feb 19 17:44:01 2021
AllCPU  OneCPU  PID     User    PR      NI      STime   RSS     Name
--------------------------------------------------------------------------------
11.75%  94.02%  23895   root    20      0       17:43   87M     ora_m001_ORCL2
1.42%   11.39%  2468    root    20      0       Feb02   736M    /opt/oracle.ahf/jre/bin/java -server -Xms256m -Xmx512m -Djava.awt.headless=true -Ddisable.checkForUpdate=true -XX:HeapDumpPath=/u01/app/oracle.ahf/data/rac02/diag/tfa -XX:ParallelGCThreads=5 oracle.rat.tfa.TFAMain /opt/oracle.ahf/tfa

Workaround:

In newer version of TFA, you can set CPU resource limit.

tfactl setresourcelimit 
 [-tool tool_name] 
 [-resource resource_type] 
 [-value value]

To limit TFA to a maximum of 50% of a single CPU, run the following:

# tfactl setresourcelimit -value 0.5

For more information, please check TFA official documentation.

If you don’t have newer version of TFA, you need to upgrade it first.

Create shortcuts for frequently accessed servers

Life is too short, that’s why it’s mandatory to use shortcuts… Instead of typing frequently used ssh client options such as port, user, hostname, identity-file and so on, you can save that information in sshd config file and then access it with defined alias.

  • System wide config file location is /etc/ssh/ssh_config
  • User specific config file location is ~/.ssh/config same as $HOME/.ssh/config

Instead of connecting to the server everytime using the following command:

# ssh root@95.80.12.10 -i ~/.ssh/my_id_rsa

Save the following entries in ~/.ssh/config file:

# vim ~/.ssh/config
Host my_db
HostName 95.80.12.10
IdentityFile ~/.ssh/my_id_rsa
User root

And connect to the server using this simple way:

# ssh my_db

For other options check https://linuxize.com/post/using-the-ssh-config-file/

asmcmd does not return error text for root user

Problem:

While running asmcmd commands via root user, error messages are not displayed. But if we run the same command via grid – it returns messages. In the following example, ORACLE_HOME is set to RDBMS home (instead of GI) and we are trying to list the file which does not exist:

[root@rac1 ~]# echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/dbhome_1

[root@rac1 ~]# echo $PATH
/u01/app/19.3.0/grid/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

[root@rac1 ~]# asmcmd ls +demodg/orcl/tempfile/TEMP.263.10617958
<<<<<<Nothing is displayed here

When a file does not exist, ls command should display ASMCMD-8002: entry 'TEMP.263.10617958' does not exist in directory '+demodg/orcl/tempfile/' error. But in our example, it does not return anything.

In the following example, if we try to copy a file, we see a message about copying a file but actually, the file is not copied because grid user does not have permission under /u01:

[root@rac1 ~]# asmcmd cp +demodg/orcl/tempfile/TEMP.263.1061795851 /u01
copying +demodg/orcl/tempfile/TEMP.263.1061795851 -> /u01/TEMP.263.1061795851

If we run the same command via grid, we get understandable error message:

[grid@rac1 ~]$  asmcmd cp +demodg/orcl/tempfile/TEMP.263.1061795851 /u01
 ASMCMD-9463: operation failed due to lack of write permissions

Reason:

Environment variables are not set correctly. ORACLE_HOME should be pointing to GI home.

Solution:

[root@rac1 ~]# export ORACLE_HOME=/u01/app/19.3.0/grid

[root@rac1 ~]# asmcmd ls +demodg/orcl/tempfile/TEMP.263.10617958
ASMCMD-8002: entry 'TEMP.263.10617958' does not exist in directory '+demodg/orcl/tempfile/'

Upgrade the database time zone file using the DBMS_DST package in 19c

After upgrading database from 12c to 19c, you may need to upgrade database time zone file version. This step is not always mandatory, but it is recommended by pre-upgrade checker.

Useful info about its necessity from Oracle site: https://oracle-base.com/articles/misc/update-database-time-zone-file

From Oracle 11gR2 onward, new time zone files are shipped with upgrades and patches, but they are not automatically applied to the database.

Applying a change to the database time zone file not only affects the way new data is handled, but potentially alters data stored in TIMESTAMP WITH TIME ZONE columns, so you need to consider the impact of this before upgrading the time zone file.

Remember, if you only deal with dates in your country, and your country has not altered its time zone or daylight saving time policy, this upgrade may not be necessary.”

Now let’s do time zone file upgrade:

1. Check current settings:

SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 	     26 	 0

2. Startup database in upgrade mode:

SQL> shutdown immediate;
SQL> startup upgrade;

3. Start upgrade window:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/


l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

4. Check primary and secondary time zone versions:

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 

PROPERTY_NAME		 VALUE
------------------------ ----------
DST_PRIMARY_TT_VERSION	 32
DST_SECONDARY_TT_VERSION 26
DST_UPGRADE_STATE	 UPGRADE

5. Startup database in normal mode:

SQL> shut immediate;
SQL> startup;

6. Do the upgrade:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/


Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

7. Check new settings:

SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 	     32 	 0


SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A20

SQL> SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

PROPERTY_NAME		       PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION	       32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE	       NONE

ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state

Problem:

During DTS upgrade, while following the steps mentioned here, got the following error:

SQL> DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;
  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/ 

DECLARE
*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading
of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1208
ORA-06512: at line 6

Solution:

The error simply means that this step is already done and you can continue with other steps, make sure that DST_UPGRADE_STATE column in the following query shows UPGRADE value.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME		 VALUE
------------------------ ----------
DST_PRIMARY_TT_VERSION	 32
DST_SECONDARY_TT_VERSION 26
DST_UPGRADE_STATE	 UPGRADE

there is no need to do anything, continue with the next DTS upgrade steps.