Infrastructure at your Service
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…
On this Blog I described on how to install Oracle XE on a current Linux Mint version (19.3. Tricia when writing the Blog). After the conversion of the Oracle provided rpm to a deb installation file with the tool alien, you can install the Oracle XE software with a simple command [email protected]:/opt/distr# dpkg -i oracle-database-xe-18c_1.0-2_amd64.deb If the installation fails or the XE database cannot be created with the configuration script later on, then you have…
On my old Laptop (Acer Travelmate with an Intel Celeron N3160 CPU) I wanted to install Oracle XE. Currently the available XE version is 18.4. My Laptop runs on Linux Mint 19.3 (Tricia). The Blog will describe the steps I had to follow (steps for Ubuntu would be similar). REMARK: The following steps were done just for fun and are not supported and not licensable from Oracle. If you follow them then you do it…
During tests in Oracle 19c I recently experienced this: [email protected]@orcl> select * from demo4 where m=103; [email protected]@orcl> select * from table(dbms_xplan.display_cursor); … ————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————— | 0 | SELECT STATEMENT | | | | 26439 (100)| | |* 1 | TABLE ACCESS FULL| DEMO4 | 1 | 10 | 26439 (14)| 00:00:02 | ————————————————————————— –> The costs of the full table…
ARRAYSIZE or ROWPREFETCH in sqlplus? What is the difference between the well known sqlplus-setting arraysize and the new sqlplus-12.2.-feature rowprefetch? In Blog https://blog.dbi-services.com/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch/ I showed a case, which helps to reduce the logical IOs when using rowprefetch. Here the definition of arraysize and rowprefetch according the documentation: arraysize: SET System Variable Summary: Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time. Valid values are 1 to…
During my time as a Consultant working on Tuning Tasks I had the feeling that many people think that there is an Oracle-parameter “FAST=TRUE” to speed up the performance and throughput of the database calls. Unfortunately such a parameter is not available, but since version 12cR2 Oracle provided the option “-F” or “-FAST” for sqlplus, which looks like a “FAST=TRUE”-setting. Here an excerpt from the documentation: The FAST option improves general performance. This command line…
As the elapsed time (time it takes for a task from start to end, often called wall-clock time) per execution of parallel queries are not shown correctly in AWR-reports, I thought I setup a testcase to find a way to get an elapsed time closer to reality. REMARK: To use AWR (Automatic Workload Repository) and ASH (Active Session History) as described in this Blog you need to have the Oracle Diagnostics Pack licensed. I created…
Since Oracle 11g files created by the database belong by default to the Linux group oinstall. Changing the default group after creating the central inventory is difficult. In this Blog I want to show how locally created archived redo can be in group dba instead of oinstall. One of my customers had the requirement to provide read-access on archived redo to an application for logmining. To ensure the application can access the archived redo, we…
Oracle 19c has been released quite a while ago already and some customers already run it in Production. However, as it is the long term supported release, I thought I blog about some interesting information and features around 19c to encourage people to migrate to it. Download Oracle 19c: https://www.oracle.com/technetwork/database/enterprise-edition/downloads or https://edelivery.oracle.com (search e.g. for “Database Enterprise Edition”) Docker-Images: https://github.com/oracle/docker-images/tree/master/OracleDatabase Oracle provides different offerings for 19c: On-premises: – Oracle Database Standard Edition 2 (SE2) –…
