Infrastructure at your Service

All Posts By

Michael Hein

Michael Hein

Unable to add physical standby database

By | Database Administration & Monitoring | No Comments

Recently I tried to setup some new physical standby databases and got the following strange message: DGMGRL> ADD DATABASE “XXXX” as connect identifier is “XXXX” maintained as physical; Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added Often you can read, that you have to check parameter log_archive_dest_2 on primary side. But this error message can also occur if log_archive_dest_2 on standby site points to another standby database. Solution is following command on standby…

Read More
Michael Hein

Second Meetup SOUG / DOAG /AOUG Region Lake of Constance

By | Database Administration & Monitoring | No Comments

Tuesday last week I attended the second meeting of SOUG region lake of constance which took place at Robotron Switzerland in Wil SG. Eleven people attended this event, Georg Russ hold a quite interesting presentation about “Hacked Biometric Data”. He told about how to fake finger prints, iris images, face ids and patterns of hand venes. After this presentation a general discussion about security and other Oracle related themes took place. It was a good…

Read More
Michael Hein

Cause for looping sssd

By | Operation systems | No Comments

In RedHat Enterprise Linux 7, the sssd daemons can connect to active directory servers. Default behaviour is to update DNS entries dynamically. If a statical DNS entry already exists this can lead to a CPU consuming sssd_nss daemon. To prevent this behaviour, the dynamic DNS updates should be switched off with this setting in every doman section of config file /etc/sssd/sssd.conf: dyndns_update = False After that sssd should be restarted to take this change effect.

Read More
Michael Hein

How to extract sql statements with awk

By | Database Administration & Monitoring | No Comments

A common problem is to extract sql statements out of a sql file e.g. produced by full metadata impdp. Often these files are too big to be edited with vi. Following statement extracts all sql statements with “CREATE MATERIALIZED VIEW” at the beginning with awk: cat impdp_full_metadata.sql | awk ‘BEGIN{ FLAG = 0 } { if( $0 ~ /CREATE MATERIALIZED VIEW/ ) FLAG=1; if(FLAG != 0) print $0; if ($0 ~ /;/ ) FLAG=0; }’…

Read More
Michael Hein

How to disable ACLs in Oracle 12c

By | Database Administration & Monitoring | No Comments

A common problem in Oracle 12c is, that ACLs are often not wanted to limit connections from database to other hosts. To disable ACLs is not that easy, so the best way is to enable connections and resolutions to all hosts for all users like following example: BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘*’, ace => xs$ace_type(privilege_list => xs$name_list(‘resolve’), principal_name => ‘PUBLIC’, principal_type => xs_acl.ptype_db)); DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => ‘*’, ace => xs$ace_type(privilege_list => xs$name_list(‘connect’), principal_name => ‘PUBLIC’,…

Read More
Michael Hein

DOAG day “database migration” in Mannheim at 19.02.2019

By | Database Administration & Monitoring | No Comments

Yesterday I attended DOAG conference in Mannheim about migrating Oracle databases. First presentation was about challenges about migrating to multitenant databases. With Oracle 20 it is probably not possible anymore to create a non CDB database or to upgrade from a non CDB database. So in the next years all databases have to be migrated to multitenant architecture. Problems with licensing, different charactersets were covered and some migration methods to PDB were shown. In the…

Read More
Michael Hein

Looping sssd_nss

By | Database Administration & Monitoring | No Comments

Often Linux systems are connected to LDAP via sssd. A high CPU consuming sssd can be seen in top, like below ( e.g. 89.4 % CPU usage): 9020 root 20 0 1296344 466780 333364 R 89.4 0.8 41:20.17 sssd_nss 27227 oracle 20 0 2371676 48320 29732 S 4.3 0.1 27:00.70 oracle One reason could be default configuration of /etc/nsswitch.conf: passwd: db sss files shadow: db sss files group: db sss files This configuration causes sssd_nss…

Read More
Michael Hein

How to build a dummy resource plan

By | Database Administration & Monitoring | No Comments

Often switching off resource plan feature of Oracle database is desired. Unfortunately setting parameter RESOURCE_MANAGER_PLAN to empty string does not help, because it does not disable switching to another resource plan, e.g. with dbms_scheduler. Following procedure prevents this switching. 1. Creation of a dummy resource plan, e.g. TEST_PLAN: BEGIN DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN( plan =>’TEST_PLAN’, comment => ‘plan for TEST’, mgmt_mth => ‘EMPHASIS’, active_sess_pool_mth => ‘ACTIVE_SESS_POOL_ABSOLUTE’, parallel_degree_limit_mth => ‘PARALLEL_DEGREE_LIMIT_ABSOLUTE’); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan =>’TEST_PLAN’, group_or_subplan => ‘OTHER_GROUPS’, comment…

Read More
Michael Hein

ODA: The Patch version must be 12.2.1.4.0

By | Database Administration & Monitoring | No Comments

When trying to patch your virtualized ODA with patch 12.1.2.12.0 and immediately after that with 12.2.1.4.0, you might consider to load the patches like this: [[email protected] Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p26433712_1212120_Linux-x86-64_1of2.zip Unpacking will take some time, Please wait… Successfully unpacked the files to repository. [[email protected] Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p26433712_1212120_Linux-x86-64_2of2.zip Unpacking will take some time, Please wait… Successfully unpacked the files to repository. [[email protected] Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p28216780_122140_Linux-x86-64_1of3.zip Unpacking will take some time, Please…

Read More
Michael Hein

Patching a virtualized ODA to patch 12.2.1.4.0

By | Database Administration & Monitoring | 3 Comments

This article describes patching a virtualized Oracle Database Appliance (ODA) containing only an ODA_BASE virtual machine. Do this patching first on test machines because it can not be guaranteed that all causes of failures of single VM ODAs are covered in this article. I got the experience that precheck for ODA patches does not detect some failure conditions which may lead to an unusuable ODA. Overview: Patch first to 12.1.2.12.0 After that patch to 12.2.1.4.0…

Read More