January 2022 DBA BUNDLE 7.1

 

7.1 release of DBA Bundle is now available:

https://www.dropbox.com/s/le5pxctq0w6v49u/DBA_BUNDLE7.tar?dl=0

Once you click on the link, first close this Window:

 

 

Then, click on the Download button at the most left upper side of the page:

In case the Dropbox website is blocked, or you cannot copy files directly to the server you want to operate the DBA Bundle from, then follow this small trick:

1- On the target machine, create a new empty file with name DBA_BUNDLE7.txt:
     # touch DBA_BUNDLE7.txt

 

2- Open below link and Copy its content (Ctrl+a then Ctrl+c) and paste it to DBA_BUNDLE7.txt file 

     https://gist.github.com/haballan/aa52fbb1f8e497db989f19ef046c7939

 

3- Execute this command to convert the text file to a tar file:
     # LC_ALL=C tr -cd 0-9a-fA-F < DBA_BUNDLE7.txt | xxd -r -p > DBA_BUNDLE7.tar

 

If DBA Bundle is a new thing to you, please visit the below link to understand its functions and how it works:

http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

ORA-1017 Invalid Username or Password on 19c while credentials are correct

 Problem:

After upgrading a DB to 19c version, some legacy applications cannot connect to the DB, throwing the error "ORA-1017 Invalid Username or Password" although the login details are correct and work fine from SQLPlus and other non-legacy applications.

 
Analysis:

- sqlnet.ora file already has the following parameters which allows legacy Oracle clients to connect:

- Enabling or disabling sec_case_sensitive_logon parameter doesn't solve the issue:

when checking the password version for the user the legacy application trying to connect with, I can see it's 11G 12c:

SQL> select username,password_versions from dba_users where username='LEGACY_APP';
 

 
Solution:

Resetting the user's password --even to the same password from SQLPlus will set it to 10G version and will allow legacy applications to connect suing that user:

SQL> alter user LEGACY_APP identified by Legacy_123;

Now, the password version supports 10G:

SQL> select username,password_versions from dba_users where username='LEGACY_APP';
 

Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/case-insensitive-passwords-ora-1017-invalid-username-password.html#GUID-FDA9C77A-12F4-4410-9448-9BCC13960C27

 

Automatic Block Corruption Recovery From the Standby DB | How to Make Sure it's Enabled

 Since Oracle 11g a new feature was introduced to automatically recover the physical corrupted blocks online from the Standby DB immediately once the physical corruption occurs on the Primary without any intervention from the DBA. But in order for this feature to work, The DBA has to make sure that the following two prerequisites are fulfilled in the environment:

1- Active Data Guard should be enabled, where the standby will be in Open mode while the media recovery of redo logs is running. [This requires ADG license].

On the Standby:

SQL> ALTER DATABASE OPEN;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT;

2- db_block_checksum initialization parameter should be set to at least TYPICAL in both the Primary and the Standby (which is the default value).

But what about db_block_checking parameter? Should it be set as well?

Answering this question took me hours of searching, Although Oracle Maximum Availability Architecture recommends setting db_block_checking parameter to MEDIUM or FULL; it has nothing to do with the activation of Automatic Block Corruption which only involved in recovering PHYSICAL corrupted blocks not the Logical ones, db_block_checking is for enabling the checking of LOGICAL corruption.

Please note that the default value for db_block_checking is FALSE, and this is because when it get set to MEDIUM or FULL it can cause a performance impact from 5% to 20% based on the nature of the workload on the DB.
Getting to know about Logical Corruption immediately is a nice thing, but it will come at the expense of the performance!

Another question, why Oracle enables the check for physical block corruption by default but not the Logical corruption one?

This because Physical corruption are most common to happen than the Logical ones, also as mentioned above checking logical corruption will consume significant resources as Oracle will need to dig deeper inside each block to check the data consistency.

References:

Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration (Doc ID 1302539.1)

Oracle Maximum Availability Architecture White Paper