I have a testbed that I use for research purposes. This testbed consists of a 2-node RAC primary database (RESP) and a 2-node RAC physical standby database (RESS). Both Oracle 19c on Oracle Linux. I also use the DG Broker in this configuration. I am planning using this testbed to work on a new Oracle feature. Normally when I let this testbed sit unattended for a long period of time, the physical standby has stopped receiving its redo stream from the primary. Today I checked on it and sure enough, the standby has not been keeping up. DGMGRL> show database ress Database – ress Role: PHYSICA ..read more

I have a new SQL Server 2019 system that I set up. After migrating applications from the old server to the new server, I started getting reports where the AD users could not connect to the SQL instance. They received a generic message that the “Login failed”. Looking at the Event Viewer log info, I received a more detailed message as can be seen below: There were many hits for this error on the Internet, but none of them really helped me. “Login lacks connect endpoint permission” is the root cause of the problem. How do I get past this error? After reading information I found, and educating m ..read more

I am patching an Oracle Multitenant 19.13 database with the April 2022 RU which will bring my database version up to 19.15 (yes I skipped 19.14). When running datapatch against the PDBs, I received the following error stack for every PDB: Patch 33806152 apply (pdb TST): WITH ERRORS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_ORCL_TST_2022May14_00_47_11.log (errors) -> Error at line 468: script rdbms/admin/backport_files/bug_32583355_apply.sql - ORA-65033: a common privilege may not be granted or revoked on a local object - ORA-06512: at li ..read more

I have a new project I’m working on where I want to have an Oracle job revoke privileges I granted to IT staff that are older than 30 days. Our IT staff needs occasional access to a few production tables to troubleshoot problems. We grant SELECT privs on the tables that person needs, but no one ever tells me when they are done with their task and those privileges sit out there forever. I wanted to have a system automatically revoke privileges older than 30 days so that I wouldn’t have to remember to do it. Before I could revoke privileges, I needed a way of tracking those privileges. So I crea ..read more

Back in 2015, I upgraded our Oracle 11.2.0.4 databases to 12.1.0.2 and experienced some performance issues related to our use of GTTs. I blogged about those issues here. The crux of the issue I was trying to solve was that a behavior change in 12c lead to Oracle saving stats that the GTT has zero rows when it doesn’t. Stats showing the number of rows equals zero lead to full table scans and cartesian products on queries that involve the GTT. As I stated in that blog post, we used DBMS_STATS.SET_TABLE_STATS after we populated the table with data so that each session would have proper stats to a ..read more

I have an Oracle 19.3 Multitenant database that I am attempting to apply the 19.7 Release Update. The RU was installed by opatch just fine. But datapatch will incur the ORA-1114 error. I get errors like the following in one of the logs: SQL> alter pluggable database GOLD2020_06_18_123653 open read write instances=all; alter pluggable database GOLD2020_06_18_123653 open read write instances=all * ERROR at line 1: ORA-65107: Error encountered when processing the current task on instance:1 ORA-17500: ODM err:Invalid argument ORA-01114: IO error writing block to file 12346 (block # 1) ORA-17500 ..read more

By now, everyone knows that data is very valuable. Major corporations use data to make decisions that hopefully drive the business forward and achieve a higher level of profitability. As database administrators, we safeguard the data, especially Personally Identifiable Information (PII). Systems are hacked to obtain data. There is a lot of value in data and you would have to be living under the proverbial rock to be learning this today. What I rarely read about, and the subject of this blog post, is how the value of data changes over time. The value of the data should be used to drive you ..read more

I have been working on upgrading Oracle 12.1.0.2 databases to Oracle 19.3 when I ran into a problem that took me quite awhile to figure out. I did a search on My Oracle Support and a quick Google search and didn’t see anyone else have this problem. So hopefully this will save someone time in the future. Side note: I had the same problem upgrading from 12.1.0.2 to 18.3 but did not check 12.1.0.2 to 12.2.0.1. During the upgrade, the DBUA gives me an error that the type SYS.ODCIPARTINFOLIST is invalid. After a few more minutes, I get a lot of error messages and XDB will not upgrade correctly ..read more

I have an Oracle 12.1.0.2 Multitenant database that I’m trying to remove a PDB from. However, I mistakenly removed the storage from the database server and the PDB cannot access its files. When unplugging the PDB, I get the following error: SQL> alter pluggable database GOLD2019_08_22_125953 unplug into '/tmp/GOLD2019_08_22_125953.xml'; alter pluggable database GOLD2019_08_22_125953 unplug into '/tmp/GOLD2019_08_22_125953.xml' * ERROR at line 1: ORA-01157: cannot identify/lock data file 50277 - see DBWR trace file ORA-01110: data file 50277: '/u01/app/oracle/oradata/mt_golden_2019_ ..read more

I am trying to remove a PDB in Oracle 19.3 that is no longer needed. I get the following error: SQL> alter pluggable database DEV_PDB close immediate instances=all; Pluggable database altered. SQL> alter pluggable database DEV_PDB unplug into '/tmp/DEV_PDB.xml'; alter pluggable database DEV_PDB unplug into '/tmp/DEV_PDB.xml' * ERROR at line 1: ORA-17500: ODM err:Invalid argument ORA-01114: IO error writing block to file 5590 (block # 1) ORA-17500: ODM err:Invalid argument ORA-01114: IO error writing block to file 5589 (block # 1) ORA-17500: ODM err:Invalid argument ORA ..read more