I’ll start this blog-post by posing a question. Is it possible to have multiple records in v$sql for a given sql_id and child_number combination? While the title of this blog post may give you some clues, I’ll admit I’d always assumed that those values uniquely identified a child cursor. As a bit of background we …
Recently we hit an issue with a complex SQL statement (formatted was 44,000 lines, maybe subject of a separate blog post), causing the CBO to struggle consuming large amounts of PGA memory, and the host to start swapping memory and impacting other database users. The pga_aggregate_limit parameter did not appear to be kicking in (maybe …
Resource manager session_pga_limit has it’s own limits Read More »
Invalid SQL being sent to the database is something to watch out for, it’s a real performance killer because once SQL is rejected by parser, it is not cached in shared pool, resulting in hard parse every time the SQL is encountered. One cool new feature of Oracle 12.2 is the fact that such SQL …
Fun with SQL Translation Framework – By-Passing Parse Errors Read More »
With version 12.2 there seems to have a some change in behavior of Materialised View Logs which caught us by surprise; I haven’t seen it documented elsewhere, so let me demonstrate. My test instance is un-patched 12.2.0.1 running on Oracle Linux. For the setup, I create a table, a mview log on that table, a …
The behavior of the LAST_LOGIN field on DBA_USERS has changed with respect to proxy authentication (for the better I think). Proxy authentication is an feature of the Oracle Database that effective allows you to be connected as one user (the client user to use Oracle’s terminology), but using the credentials of another user (the proxy …
Just a quick note in case anyone else hits this issue. We use make extensive use of Kerberos to give us Windows single-sign on. For database connections via Enterprise Manger we use “Database Kerberos Credentials” credential type and have no problems connecting to 11.2 and 12.1 databases (apart from slight pain points that it’s not …
Problems with Kerberos Credentials to 12.2 Databases in Enterprise Manger Read More »
I’ve blogged about this bug in passing, but I thought would be worthwhile to document my testcase. Basically backups of controlfiles never get marked as obsolete. The issue reproduces for both image copies and backupsets, my test-case uses the latter for simplicity. RMAN> show all; RMAN configuration parameters for database with db_unique_name PVJTEST are: CONFIGURE …
12.2 controlfile backup never gets marked as obsolete Read More »
This is the second time we have hit this error after upgrading the recovery catalog to 12.2. The first is now documented by Oracle as Document 2291791.1, and affects 12.1 multi-tenant instances with DBBP 12.1.0.2.161018 or later applied. If you have applied that patchset, you can see the problem by checking the contents of file …
“ORA-20110: set stamp set count conflict” after upgrading to 12.2 Recovery Catalog Read More »
I thought it would be useful to have a consolidated list of issues we have run into, some of which I have already blogged about, some of which I haven’t. I will try to keep this page up-to-date moving forwards. Note we are not really using 12cR2 extensively, we have just upgraded OEM Repository and …
We have just noticed a difference in behavior in 12cR2 with regards to image copies being marked as obsolete after the backup is updated past the drop of a datafile. I won’t describe the feature itself, if necessary you can read up at oracle-base or the Oracle documentation. First review output from the testcase on …
12cR2 Incrementally Updated Backups and dropped datafiles Read More »