Infrastructure at your Service

Oracle Archives - Page 2 of 40 - Blog dbi services

Jérôme Dubar

Easily manage dual backup destination with RMAN

By | Database Administration & Monitoring, Database management, Oracle | No Comments

Backup on disk with RMAN is great. It’s fast, you can set as many channels as your platform can handle for faster backups. And you can restore as fast as you can read and write files on disk with these multiple channels. As far as you’re using Enterprise Edition because Standard Edition is stuck to a single channel. Disk space is very often limited and you’ll probably have to find another solution to keep backups…

Read More
Marc Wagner

ODA database been stuck in deleting status

By | Database Administration & Monitoring | No Comments

Facing an internal inconsistency in the ODA derby database is very painful (see https://blog.dbi-services.com/oda-lite-what-is-this-odacli-repository/ for more info about the derby database). I have recently faced a case where the database deletion was failing and the database remained then in “Deleting” status.  Connecting directly to the internal derby database and doing some self cleaning is very risky and should be performed at your own and known risk. So, in most of the case, a database inconsistency issue…

Read More
Pierre Sicot

Licensable targets and Management Packs with EM13c

By | Database Administration & Monitoring | No Comments

When you add a new target in Enterprise Manager 13c , the management packs are enabled by default. This could be a problem in case of a LMS control, and to avoid any problem, you have to  manually disable those management packs. If like me you recently have moved your database infrastructure to a new one and have to add one hundred targets, you will have to click some hundredth of times on the management…

Read More
Oracle Team

ORACLE_HOME with symbolic link and postupgrade_fixups

By | Database Administration & Monitoring | No Comments

By Franck Pachot . Here is a quick post you may google into if you got the following error when running postupgrade_fixups.sql after an upgrade: ERROR – Cannot open the preupgrade_messages.properties file from the directory object preupgrade_dir DECLARE * ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at “SYS.DBMS_PREUP”, line 3300 ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 41 ORA-06512: at “SYS.UTL_FILE”, line 478 ORA-06512: at “SYS.DBMS_PREUP”, line…

Read More
Oracle Team

TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT

By | Database Administration & Monitoring, Oracle | One Comment

By Franck Pachot . When you have a Data Guard configuration, you want the application to connect to the right server, where the primary is, without taking too much time. The default TCP timeout is 1 minute which is too long. When you don’t want to configure a virtual IP address (VIP) you can simply list all the addresses in the client connection string. But then you need to reduce the timeout. A short duration…

Read More
Oracle Team

ATP vs ADW – the Autonomous Database lockdown profiles

By | Cloud, Oracle | No Comments

By Franck Pachot . The Oracle database has always distinguished two types of workloads: transactional (OLTP) and datawarehouse (VLDB, DWH, DSS, BI, analytics). There is the same idea in the managed Oracle Cloud with two autonomous database services. To show how this is old, here is how they were defined in the Oracle7 Tuning Book: The definition has not changed a lot. But the technology behind DSS/DWH has improved. Now, with In-Memory Column Store, Smart…

Read More
Oracle Team

MERGE JOIN CARTESIAN: a join method or a join type?

By | Oracle | No Comments

By Franck Pachot . I’ll present about join methods at POUG and DOAG. I’ll show how the different join methods work in order to better understand them. The idea is to show Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian on the same query. I’ll run a simple join between DEPT and EMP with the USE_NL, USE_HASH, USE_MERGE and USE_MERGE_CARTESIAN hints. I’ll show the execution plan, with SQL Monitoring in text mode. And…

Read More
Oracle Team

How much free space can be reclaimed from a segment?

By | Oracle | No Comments

By Franck Pachot . You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more,…

Read More
Oracle Team

Oracle 18c preinstall RPM on RedHat RHEL

By | Oracle | No Comments

By Franck Pachot . The Linux prerequisites for Oracle Database are all documented but using the pre-install rpm makes all things easier. Before 18c, this was easy on Oracle Enterprise Linux (OEL) but not so easy on RedHat (RHEL) where the .rpm had many dependencies on OEL and UEK. Now that 18c is there to download, there’s also the 18c preinstall rpm and the good news is that it can be run also on RHEL…

Read More
Jérôme Dubar

Patching ODA lite to 12.2.1.4.0

By | Database Administration & Monitoring, Database management, Oracle | No Comments

Here is how to apply the latest patch for your ODA. As usual the Oracle documentation is not 100% accurate. I applied this patch on 2 ODAs X7-2M previously deployed in 12.2.1.2.0, no intermediate patch was needed. 1) Download the patch The patch number is 28216794. This patch will update the following components:  dcs (odacli), operating system, bios/firmwares, ilom, GI, dbhomes and databases. Download and copy the patch to a temporary folder on the server,…

Read More