Infrastructure at your Service
In a project I recently had to upgrade an Oracle 12.2.-DB to 19.9. and at the same time migrate from the non-container architecture to the container architecture. The interesting part here is to do this on Windows. Actually both steps (upgrade and plugin) are possible using the autoupgrade tool , which is the preferred tool to do Oracle upgrades today. Before doing the upgrade at the customer, I performed 2 tests in my personal environment:…
Switching to Oracle Unified Auditing may produce lots of data when e.g. auditing activities of the SYS-user. I.e. according the documentation you can do the following to audit similarly as in traditional auditing with audit_sys_operations=TRUE: SQL> CREATE AUDIT POLICY TOPLEVEL_ACTIONS ACTIONS ALL ONLY TOPLEVEL; SQL> AUDIT POLICY TOPLEVEL_ACTIONS BY SYS; REMARK1: You may check the Blog on traditional SYS-auditing here REMARK2: Tests done in this Blog were done with Oracle 19.9. Auditing toplevel operations were…
Column formatting was always a pain in sqlplus when writing queries on the prompt. Most people use tools like SQL Developer or Quest TOAD which can scroll horizontally when running queries against a database, but as a consultant you are often still forced to use sqlplus. Here’s the issue: When running e.g. a query on a table T1 (which is a copy of ALL_OBJECTS) it looks by default as follows and is hard to read:…
As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without code changes. Recently I was confronted with the following issue: A process takes too long. Digging deeper I could see…
In a recent project we migrated an Oracle database previously running on 12.1.0.2 on an Oracle Database Appliance to an Exadata X8 with DB version 19.7. Shortly after the migration a merge-statement (upsert) failed with an ORA-07445: exception encountered: core dump [kdu_close()+107] [SIGSEGV] [ADDR:0xE0] [PC:0x1276AE6B] [Address not mapped to object] [] The stack looked as follows: kdu_close – updThreePhaseExe – upsexe – opiexe – kpoal8 – opiodr – ttcpip – opitsk – opiino – opiodr…
There are several methods to find out where time is spent in an execution plan of a query running in an Oracle database. Classical methods like SQL Trace and running a formatter tool like tkprof on the raw trace, or newer methods like SQL Monitor (when the Tuning Pack has been licensed) or running a query with the GATHER_PLAN_STATISTICS-hint (or with statistics_level=all set in the session) and then using DBMS_XPLAN.DISPLAY_CURSOR(format=>’ALLSTATS LAST’). However, what I often…
A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and the DBUA handles everything for me (including changing the necessary Windows services and update the timezone file). Both upgrades did…
By Clemens Bleile To prepare a presentation about Multitenant Tuning I wanted to see the METHOD_OPT dbms_stats global preference of all my pluggable DBs. In this specific case I had 3 PBDs called pdb1, pdb2 and pdb3 in my CDB. For testing purposes I changed the global preference in pdb1 from its default ‘FOR ALL COLUMNS SIZE AUTO’ to ‘FOR ALL INDEXED COLUMNS SIZE AUTO’: c##[email protected]@PDB1> exec dbms_stats.set_global_prefs(‘METHOD_OPT’,’FOR ALL INDEXED COLUMNS SIZE AUTO’); c##[email protected]@PDB1> select…
By Clemens Bleile After cloning e.g. a production database into a database for development or testing purposes, the DBA has to make sure that no activities in the cloned database have an impact on data in other production databases. Because after cloning production data jobs may still try to modify data through e.g. db-links. I.e. scheduled database jobs must not start in the cloned DB and applications connecting to the cloned database must not modify…
To save resources I thought about the idea to start an Oracle database automatically when a first connection comes in. I.e. if there are many smaller databases on a server, which are not required during specific times, then we may shut them down and automatically start them when a connection comes in. The objective was that even the first connection should be successful. Is that possible? Yes, it is. Here’s what I did: First of…
