IF

Oracle Is Guilty Until Proven Innocent

Received email from Technical Lead | Senior Manager for the following errors.

Error Description: 0: Invalid pool name ‘oraclePool’ while getting a database connection.
Please check for consistency of the properties files or BPML
Time of Event: 20240419141429
Workflow Id: 88867

First inclination is to check Oracle database parameters (sessions and processes) which wasted time on a wild goose chase.

I am by no mean an expert but Google is your friend.

SI fails to startup with error: “Invalid pool name ‘NewPool_oraclePool’ while getting a database connection. Please check for consistency of the properties files or BPML” (SCI91968)

It puzzle me how a Technical Lead | Senior Manager does not know how to Google.

LMGTFY – Let Me Google That For You for all those people who find it more convenient to bother you with their question rather than to Google it for themselves.

Oracle OEM Read Only Access

With great power comes great responsibility.

Reference: https://en.wikipedia.org/wiki/With_great_power_comes_great_responsibility

On boarding 4 Database Consultants and they have request access to OEM. Sharing SYSMAN password with every DBA is not a good idea and also difficult to determined who messed up.

Here are 2 articles and I favor Doc ID 2180307.1 based on last update and contains screenshots.

OEM 13c How To Create an Administrator with Read Only Access (Doc ID 2925232.1)
Enterprise Manager Base Platform – Version 13.4.0.0.0 and later
Last Update: Feb 1, 2023

EM 13c : How to Create an EM Administrator with Read Only Access to the Performance Pages of a Database Target? (Doc ID 2180307.1)
Enterprise Manager for Oracle Database – Version 13.1.1.0.0 and later
Last Update: May 9, 2023

Would have been nice to have emcli script to do this but beggars cannot be choosers.

Zero-Sum Game

Zero-sum is a situation, often cited in game theory, in which one person’s gain is equivalent to another’s loss, so the net change in wealth or benefit is zero.

A zero-sum game may have as few as two players or as many as millions of participants.

Reference: Zero-Sum Game Definition in Finance, With Example

This is what the database team is facing because system team is upgrading operating system to Red Hat Enterprise Linux 7 which will reach its end of life on June 30, 2024 (2 months later).

Here are some Oracle Docs with may be helpful.

IMPORTANT: Need to relink GI and DB

There is one step missing and do you know what it is? Keep in mind, not all environments are the same.

How To Relink The Oracle Grid Infrastructure Standalone (Restart) Installation Or
Oracle Grid InfrastructureRAC/ClusterInstallation (11.2 to 21c).
Doc ID 1536057.1

Relinking Oracle Home FAQ ( Frequently Asked Questions)
Doc ID 1467060.1

Executing “relink all” resets permission of extjob, jssu, oradism, externaljob.ora
Oracle Database – Enterprise Edition – Version 10.2.0.3 and later
Doc ID 1555453.1

The Best vs. The Rest

The Best vs. The Rest

I was looking how to start and title blog post and found great read above.

CAUTION: whine being served next.

Currently, Oracle database version 11.2.0.4 is running on RHEL 6.10 and database version 12.1.0.2 is running on RHEL 6.10, 7.1, 7.5.

System administrators decides to upgrade OS to RHEL 7.9 which will be EOL.

Basically, ZERO sum gain.

In addition, there are plans to migrate away from Oracle RDBMS and to Non-Oracle cloud.

I am not the sharpest tool in the shed, but isn’t this like putting lipstick on the pig before the pig get slaughter?

Common Sense

I am often reminded of of my experience at 24 Hour Fitness24 Hour Fitness from 2005.

Just purchased Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning and a consultant (we call him Dibs) tells me to throw the book away.

Book is not going to help you if you don’t have common sense.

SQL Server Gateway (/tmp 100% FULL)

Trouble Shooting :=)

hostname:/home/oracle$ df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg01-tmplv
                       12G   12G  4.2M 100% /tmp
hostname:/home/oracle$

hostname:/home/oracle$ lsof +D /tmp
lsof: WARNING: can't opendir(/tmp/lost+found): Permission denied
COMMAND   PID   USER   FD   TYPE DEVICE    SIZE/OFF    NODE NAME
dg4msql  7319 oracle   10u   REG 253,26 11290202112      39 /tmp/qe9vfnzS
java    18849 oracle  mem    REG 253,26       32768 1441794 /tmp/hsperfdata_oracle/18849
hostname:/home/oracle$

hostname:/home/oracle$ rm -fv /tmp/qeOGiDuK
removed `/tmp/qeOGiDuK'
hostname:/home/oracle$

The process dg4msql (Oracle Database Gateway for SQL Server) used up 100% tmp space.

Temp space for dg4msql is analogous to temporary tablespace for Oracle database.

There can be Multiple Temporary Tablespaces in Oracle and why not multiple tmp directory for OS!

Solution provided by Oracle support.

Those temp files starting with “qe*” are a necessity by the driver and they will be created nevertheless.

One option to avoid to generate in /tmp files if you are running out of space is to set the variable QE_TMPDIR.

This variabe would only change the location where the tmp files are stored. In case you have a partition with more space than the default location of /tmp

You can set this parameter choosing one of the below options –

A) – Set the env variable in the gateway init file specifying the new location of the files, adding the below parameter

HS_FDS_CONNECT_INFO=…
:
#
SET PRIVATE QE_TMPDIR=/var/tmp

B) – Or place it into the listener.ora entry for the gateway using the ENV= parameter, for example:

(SID_DESC=
(SID_NAME=sid_name)
(ORACLE_HOME=)
(ENVS=QE_TMPDIR=/var/tmp,LD_LIBRARY_PATH=/dg4msql/driver/lib:/lib)
(PROGRAM=dg4msql)
)

Either specifying in the gateway init file or in the listener.ora file, stop/start the listener and monitor again.

One suggestion is to check the contents of the QE_TMPDIR or /tmp once a week and see if there are files there, how big they are and how old they are.

If the contents of the QE_TMPDIR or /tmp seem empty and suddenly it fills up; then it is one of your queries that is passing thru a lot of data.

You would need to have that query or session identified and check if there is one way you can make it spread the work a little bit, using a PL/SQL stored procedure that does a few commits along the way, releasing some space.

Closing thought, FS and DB TEMP space should be the same size to prevent issues.

The Beginning Of The End

Started blogging June 19, 2007.

Thank you for reading!

No better way to end it than the year of Dragon [Wood] (Chinese Horoscope).

Be well; be kind; be happy.

Extract DB User Using OEM

I know what you are probably thinking.

Number of lines from Create User and GRANTS.

$ wc -l *.sql
1310 CRYSTAL.sql
1383 TMTRCK.sql

SRDC – Collect Data Guard Diagnostic Information (Doc ID 2219763.1)

Been in DataGuard HELL and Doc ID 2219763.1 is a good starting point for troubleshooting.

Many SR’s opened without any success. Oracle suppport suggest to recreate controlfile – YIKES!

Basically, RMAN backup is being performed from standby.

From primary RMAN deletes archivelog to prevent fill up.

My unnderstanding, if archivelogs are in FRA, then cleanup shoud be automatic provided there are no BUGS.

Solution for QA – RMAN delete archivelogs without connecting to catalog at primary

and RMAN backups connecting to catalog at standby. Not sure if there are any repercussions.

RMAN Archivelog Deletion Errors – 12.1.0.2.0 – Script below seems overly complicated.

Any suggestions or ideas? TIA.

16> connect target *
17> connect catalog *
18>
19> crosscheck archivelog all;
20> delete noprompt archivelog until time 'sysdate-4/24';
21> list archivelog all;
22>
23> run {
24> allocate channel c1 device type disk;
25> SQL "alter database backup controlfile to trace as
25> ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
26> SQL "create pfile=''/tmp/init@.ora'' from spfile";
27> release channel c1;
28> }
29> show all;
30> crosscheck archivelog all;
31> crosscheck backup;
32> delete noprompt obsolete;
33> delete noprompt expired backup;
34> resync catalog from db_unique_name all;
35>

RMAN-06216: WARNING: db_unique_name mismatch - 7 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name SID_DR
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    c-1072717639-20240124-03
RMAN-06214: Backup Piece    c-1072717639-20240124-05
RMAN-06214: Backup Piece    c-1072717639-20240124-07
RMAN-06214: Backup Piece    c-1072717639-20240125-01
RMAN-06214: Backup Piece    c-1072717639-20240125-03
RMAN-06214: Backup Piece    c-1072717639-20240125-05
RMAN-06214: Backup Piece    c-1072717639-20240126-00

RMAN-06216: WARNING: db_unique_name mismatch - 6 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name SID_DR
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    c-1072717639-20240124-02
RMAN-06214: Backup Piece    c-1072717639-20240125-00
RMAN-06214: Backup Piece    c-1072717639-20240125-02
RMAN-06214: Backup Piece    c-1072717639-20240125-04
RMAN-06214: Backup Piece    c-1072717639-20240125-07
RMAN-06214: Backup Piece    c-1072717639-20240126-01

RMAN-06207: WARNING: 6 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    c-1072717639-20240124-02
RMAN-06214: Backup Piece    c-1072717639-20240124-05
RMAN-06214: Backup Piece    c-1072717639-20240125-00
RMAN-06214: Backup Piece    c-1072717639-20240125-02
RMAN-06214: Backup Piece    c-1072717639-20240125-04
RMAN-06214: Backup Piece    c-1072717639-20240125-07

RMAN-06207: WARNING: 6 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    c-1072717639-20240124-03
RMAN-06214: Backup Piece    c-1072717639-20240124-07
RMAN-06214: Backup Piece    c-1072717639-20240125-01
RMAN-06214: Backup Piece    c-1072717639-20240125-03
RMAN-06214: Backup Piece    c-1072717639-20240125-05
RMAN-06214: Backup Piece    c-1072717639-20240126-00