Infrastructure at your Service

All posts by Michael Hein

Michael Hein



Michael Hein

SCAN listener does not know about service

By | Database Administration & Monitoring | One Comment

When trying to connect to a database via SCAN listener in a RAC environment with sqlplus, an ORA-12514 error is thrown. Tnsping can resolve the connect string. Whereas connecting to the same database over node listener with sqlplus succeeds. One possible reason could be, that the parameter remote_listener of the database to be connected is not set to SCAN listener of RAC cluster. So try to set remote_listener to SCAN_LISTENER_HOST:SCAN_LISTENER_PORT like (e.g. host is scan_host,…

Read More
Michael Hein

Logical standby: No view for data guard status on session level

By | Database management, Oracle | No Comments

On logical standby you have to switch off data guard at least on session level to make modifications on objects which are maintained by data guard. This is done by command alter session disable guard; Dataguard can be reenabled for this session by issuing alter session enable guard; For executing these commands “alter database” privilege is at least needed, which is often not liked by IT security staff. Command “alter session disable guard” is also…

Read More
Michael Hein

How to shrink tables with on commit materialized views

By | Database Administration & Monitoring | No Comments

Usually it is not possible to shrink tables which are used by on commit materialized views. The result is an ORA-10652 “Object has on-commit materialized views” error, for which in action section nothing is suggested. There is a workaround for this error: Convert all materialized views which rely on your table to be shrinked from on-commit to on-demand views. Application must tolerate that the affected materialized views are not updated during shrinking space of the…

Read More
Michael Hein

utl_dbws causes ORA-29532 and bad_record_mac

By | Database Administration & Monitoring | No Comments

After installing OJVM patch set update APR-2017 on a 11.2.0.4 database with PSU APR-2017 installed, first call of utl_dbws package was successful, but after a while utl_dbws calls failed always with ORA-29532 and bad_record_mac. All Java objects remained valid. Also after trying procedures described in MOS document 2314363.1 utl_dbws worked first time, after that it always failed. We could observe that after a while after restarting database m000 process ran and tried to recompile Java…

Read More
Michael Hein

Disabling database trap with dataguard broker

By | Database Administration & Monitoring | No Comments

When connecting to dataguard broker and disabling database with ORACLE_SID set before connecting to broker, broker becomes inaccessible, which is shown below: Login to broker via database ila: oracle [ILA@p03] /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs : dgmgrl / DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type “help” for information. Connected. DGMGRL> show configuration; Configuration – ila Protection Mode: MaxAvailability Databases: ILAP – Primary database ila – Physical…

Read More
Michael Hein

How to flashback databases in dataguard with broker

By | Database Administration & Monitoring | No Comments

Last week I had to do some tests with dataguard. To make restores easier restore points were required. Given is following configuration: show configuration; Configuration – ila Protection Mode: MaxAvailability Databases: ila – Primary database ilal – Logical standby database ILAP – Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS How to set restore points here ? Two things to consider: Redo apply to a physical standby is incompatible with setting a restore point…

Read More
Michael Hein

Duplex RMAN backups between disk and tape

By | Database Administration & Monitoring | No Comments

Below a workaround is shown how to “duplex” archivelog backups between disk and tape: Backup on disk (normal way): backup device type disk archivelog all;   Immediately  backup on tape: backup device type sbt archivelog until time ‘sysdate’ not backed up 2 times;   This backup command backs up all archivelogs, that are not backed up twice, so all which are backed up with the first command. As in the first backup command a logfile…

Read More
Michael Hein

Naming of archivelog files with non existing top level archivelog directory

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

In Oracle 12.2 an archive log directory is accepted, if top level directory does not exist: oracle@localhost:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/ [DMK] ls -l /u02/oradata/DMK/ total 2267920 drwxr-xr-x. 2 oracle dba        96 Dec  6 05:36 arch … Now database accepts this non existing archivelog destination: SQL> alter system set log_archive_dest_3=’LOCATION=/u02/oradata/DMK/arch/arch2′; System altered. But not this: SQL> alter system set log_archive_dest_4=’LOCATION=/u02/oradata/DMK/arch/arch2/arch4′; alter system set log_archive_dest_4=’LOCATION=/u02/oradata/DMK/arch/arch2/arch4′ * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid…

Read More