Trouble Shooting

Patching 19.3 to 19.6

by

I’m not going to go through the entire patching process, there are lots of other sites that can walk you through this. What I am going to talk about is some issues that I had after patching. The patch to the binaries went fine, however when I tried to run Datapatch against my database, I got the following error


[oracle@oradb1 OPatch]$ ./datapatch 
SQL Patching tool version 19.6.0.0.0 Production on Tue Mar 17 13:02:31 2020 
Copyright (c) 2012, 2019, Oracle. All rights reserved. 

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_1929_2020_03_17_13_02_31/sqlpatch_invocation.log 

Connecting to database... 
Error: prereq checks failed! 
Database connect failed with: ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach) 

Please refer to MOS Note 1609718.1 and/or the invocation log 
/opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_1929_2020_03_17_13_02_31/sqlpatch_invocation.log 
for information on how to resolve the above errors. 

SQL Patching tool complete on Tue Mar 17 13:02:32 2020

I have see this error before when the parameter remote_listener is not set on a database, but this is a single instance database.

I opened an SR with oracle support. After some trouble shooting, they recommended I remove the trailing slash from my ORACLE_HOME variable and restart the database.

So change
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1/
to
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1

after restarting the database, I got a new error

[oracle@oradb1 OPatch]$ ./datapatch -verbose 
SQL Patching tool version 19.6.0.0.0 Production on Wed Mar 18 16:52:03 2020 
Copyright (c) 2012, 2019, Oracle. All rights reserved. 

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_31007_2020_03_18_16_52_03/sqlpatch_invocation.log 

Connecting to database...OK 
Gathering database info...done 
Bootstrapping registry and package to current versions...done 
Determining current state...done 

Current state of interim SQL patches: 
No interim patches found 

Current state of release update SQL patches: 
Binary registry: 
19.6.0.0.0 Release_Update 191217155004: Installed 
SQL registry: 
Applied 19.6.0.0.0 Release_Update 191217155004 with errors on 18-MAR-20 04.51.13.824844 PM 

Adding patches to installation queue and performing prereq checks...done 
Installation queue: 
No interim patches need to be rolled back 
Patch 30557433 (Database Release Update : 19.6.0.0.200114 (30557433)): 
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.6.0.0.0 Release_Update 191217155004 
No interim patches need to be applied 

Installing patches... 
Patch installation complete. Total patches installed: 1 

Validating logfiles...done 
Patch 30557433 apply: WITH ERRORS 
logfile: /opt/oracle/cfgtoollogs/sqlpatch/30557433/23305305/30557433_apply_DB1_2020Mar18_16_52_52.log (errors) 
-> Error at line 7738: script rdbms/admin/prvtgwm.sql 
- ORA-03113: end-of-file on communication channel 
- Process ID: 32448 
- Session ID: 270 Serial number: 1817 
-> Error at line 7745: script rdbms/admin/prvtgwm.sql 
- ORA-03114: not connected to ORACLE 
-> Error at line 7749: script rdbms/admin/prvtgwm.sql 
- ORA-03114: not connected to ORACLE 
-> Error at line 7753: script rdbms/admin/prvtgwm.sql 
- ORA-03114: not connected to ORACLE 
-> Error at line 7757: script rdbms/admin/prvtgwm.sql

So some progress. Something was causing a core dump and the connection was terminated.

Looking at the script it was failing on, prvtgwm.sql, that script is all related to the schema gsmadmin_internal

Looking at the patching log, I see the following

GRANT datapump_exp_full_database TO package gsmadmin_internal.exchange
                                                              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 32448
Session ID: 270 Serial number: 1817

So looking back at the database, there are several package inside the GSMADMIN_INTERNAL schema that are invalid

======================================================
Count of Invalids by Schema
======================================================


OWNER        OBJECT_TYPE                                COUNT(*)
------------ ---------------------------------------- ----------
GSMADMIN_INT PACKAGE                                           1
ERNAL

GSMADMIN_INT PACKAGE BODY                                      7
ERNAL

GSMADMIN_INT PROCEDURE                                         2
ERNAL

GSMADMIN_INT TRIGGER                                           4
ERNAL

GSMADMIN_INT VIEW                                              4
ERNAL

MDSYS        FUNCTION                                          1
MDSYS        PACKAGE BODY                                      2
MDSYS        TRIGGER                                           1
PUBLIC       SYNONYM                                           5
SYS          PACKAGE BODY                                      3
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================

I tried running utlrp several times, however I was able to compile most of the objects manually issuing compile commands.

I was then able to run Datapatch successfully

[oracle@oradb1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.6.0.0.0 Production on Thu Mar 19 09:33:50 2020
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_3442_2020_03_19_09_33_50/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.6.0.0.0 Release_Update 191217155004: Installed
  SQL registry:
    Applied 19.6.0.0.0 Release_Update 191217155004 with errors on 18-MAR-20 04.53.44.788558 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 30557433 (Database Release Update : 19.6.0.0.200114 (30557433)):
    Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.6.0.0.0 Release_Update 191217155004
  No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 30557433 apply: SUCCESS
  logfile: /opt/oracle/cfgtoollogs/sqlpatch/30557433/23305305/30557433_apply_DB1_2020Mar19_09_34_28.log (no errors)
SQL Patching tool complete on Thu Mar 19 09:39:03 2020

myMYSQL Nightmare Continues

by

My fight this week with mySQL continues. I started getting really strange errors in the error log, and users couldn’t connect, not even root@localhost.

The error log showed

2016-09-30T15:04:08.790598Z 0 [Warning] System table 'time_zone_leap_second' is expected to be transactional.
2016-09-30T15:04:08.790625Z 0 [Warning] System table 'time_zone_name' is expected to be transactional.
2016-09-30T15:04:08.790628Z 0 [Warning] System table 'time_zone' is expected to be transactional.
2016-09-30T15:04:08.790643Z 0 [Warning] System table 'time_zone_transition_type' is expected to be transactional.
2016-09-30T15:04:08.790646Z 0 [Warning] System table 'time_zone_transition' is expected to be transactional.
2016-09-30T15:04:08.793672Z 0 [Warning] System table 'servers' is expected to be transactional.
2016-09-30T15:04:08.800142Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2016-09-30T15:04:08.803287Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.

Followed by logins failing

2016-09-30T15:14:27.945521Z 4 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-30T15:14:35.794051Z 5 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-30T15:14:50.242206Z 6 [Note] Access denied for user 'root'@'localhost' (using password: YES)

What I think had happened, mySQL was upgraded on the OS by the linux admins. It doesn’t look like anything inside the database was upgrade.

The new version of mySQL uses the innodb engine (v5.6.15)

mysql> show create table time_zone\G
*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones'
1 row in set (0.00 sec)

Whereas the older version of mySQL uses MyISAM (from another server v5.5.50)

[mysql]> show create table time_zone\G
*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'

mysql was expecting INNODB and found MyISAM

This should be a simple fix, just run the mysql_upgrade script. However the database is either down, or I can’t log in when the database is up.

So I had to shutdown the database, start it up skipping permissions, and change my root password

mysqld --skip-grant-tables --skip-networking

in another session I was now able to connect to the database

mysql -u root

and update the root password

FLUSH PRIVILEGES;
SET PASSWORD FOR root@'localhost' = PASSWORD('password');

then killed off my instance, and started it back up

service mysqld start

run the mysql_upgrade script

msyql_upgrade -uroot -pPassword

and restart once more

service mysqld restart

I don’t know what wrong with the passwords at this point, but none of the users could log in, so I changed all of their passwords to their current passwords. Just rewriting the password seemed to do the trick.

CREATE USER 'dba_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

Everything seems to be back to normal…

 

 

Mind your spaces

by

I am trying to export a database, everything except for a single table. I need to send a copy of the database to the vendor, but they don’t need a table that takes up %90 of the space in the database due to it storing a massive number of BLOBs and CLOBs

This should be really simple

$ expdp "'/ as sysdba'" directory=A dumpfile=fullexport.dmp exclude=TABLE:"IN ('TABLEOFCLOBS')"

Export: Release 11.2.0.4.0 - Production on Fri Jun 17 10:37:01 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

That syntax looks correct to me! Maybe I copy/pasted in a special character or something…. I type out the full command by hand and get the same error. I am stumped!

idontgetit

I worked the googles on the internet machine, and see a bunch of examples that look pretty much identical to mine. Then I notice it. I have a space after the “IN” key word.

$ expdp "'/ as sysdba'" directory=A dumpfile=fullexport.dmp exclude=table:"IN('TABLEOFCLOBS')" FULL=Y

Export: Release 11.2.0.4.0 - Production on Fri Jun 17 10:38:14 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=A dumpfile=exportfile.dmp exclude=table:IN('TABLEOFCLOBS') FULL=Y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 53.28 GB

Thwarted by spaces again! It seems trival, and kind of incorrect since the “IN” in a sql statement needs that space there.

Rushing = Stupid Mistakes

by

I am currently being asked to refresh a database. The end users are pissed because they asked for the database to be refreshed at noon yesterday, this was the first they had asked for it, and they were doing “really important things” and by 3pm were pissed that I hadn’t responded to them, let alone completed the work. The thing was, I took a vacation. I took a whole two days off to spend some time with my girlfriend and wasn’t checking my email. Never mind that we have a whole team of DBAs that can refresh a database, they had emailed me. What they should have done was open a proper ticket, and someone would have helped out even though they hadn’t planned and were now in a panic.

So After escalating it to their boss because I wasn’t providing them with “good service”, who in turn talked to my boss, who informed him how they had dropped the ball, and I would deal with it today, that they were being completely unprofessional. (My boss is a good guy, and sticks up for me).

So here is the technical part of this post. Ever DBA has cloned a database, thousands and thousands of databases, but when you are rushing you can make stupid errors. I just fought this error for 20 minutes

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/08/2016 14:23:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-27191: sbtinfo2 returned error
Additional information: 3480

This should have been a no brainier, but in my rush I wasn’t fully paying attention to the error.

I was just issuing the command

duplicate target database to testdb from active database

My defaults for RMAN are using sbt ie tape, so for some reason the tape channels were being loaded. This causes an error. The solution is simple and should have been fairly immediate, just allocate disk channels.

run
{
ALLOCATE CHANNEL d1 TYPE DISK ;
ALLOCATE CHANNEL d2 TYPE DISK ;
ALLOCATE CHANNEL d3 TYPE DISK ;
ALLOCATE AUXILIARY CHANNEL a1 TYPE DISK ;
ALLOCATE AUXILIARY CHANNEL a2 TYPE DISK ;
ALLOCATE AUXILIARY CHANNEL a3 TYPE DISK ;
duplicate target database to testdb from active database;
}

Even simple things become very hard when you are rushing.

ORA-00904: “UTL_RAW”.”CAST_FROM_NUMBER”: invalid identifier

by

I have been migrating single instance databases to RAC databases and had several databases that filled up the diag directory spewing out alerts. There is a bug that can cause some of these, but in my case it was much simpler. Simple configuration mistakes

The first error was due to the existance of a spfile on the file system that was being read in.

the spfile was create in ASM, and $ORACLE_HOME/dbs/initdp0db#.ora was created on both nodes

However on node1, we still had $ORACLE_HOME/dbs/spfiledp0db1.ora

checking the spfile parameter in the database confirmed that this was the file being read in. The alert log wasn’t very helpful just spewing out the following.

Emon ping encountered error 12801
Errors in file /u01/app/oracle/diag/rdbms/dp0db/dp0db1/trace/dp0db1_q00p_617988.trc:
ORA-12801: error signaled in parallel query server PZ99, instance node2:dp0db2 (2)
ORA-00904: "UTL_RAW"."CAST_FROM_NUMBER": invalid identifier
Thu Jun 11 11:54:19 2015

The second issue I ran into that also filled up the diag directory was due to bad permissions on one of the nodes on the audit directory. This generated the exact same error as above in the log file.

If you see this error check the following

  • on each node check the value of the spfile in the databse, they should both be pointing to the ASM spfile
  • check the location of the audit directory and make sure it exists and oracle can write to it

These were simple mistakes that caused a huge headache.