Patch 28729234 requires component(s) that are not installed in OracleHome. These not-installed components are oracle.crs:11.2.0.4.0

Problem:

Applying OCW patch 28729234 on RDBMS home failed with the following error:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME

Oracle Interim Patch Installer version 11.2.0.3.20
...
UtilSession failed: Patch 28729234 requires component(s) that are not installed in OracleHome. These not-installed components are oracle.crs:11.2.0.4.0

Reason:

The error message is confusing, because the real reason is in the patch itself. There is a duplication of folders and files. Custom/server directory under 28729234, also contains 28729234 directory.

Solution:

As a workaround run opatch from inner 28729234 directory:

[oracle@rac1 ~]$ cd /home/oracle/28813878/28729234/custom/server/28729234

Generate emocmrsp file:

[oracle@rac1 28729234]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/emocmrsp

OCM Installation Response Generator 10.3.7.0.0 - Production
Copyright (c) 2005, 2012, Oracle and/or its affiliates.  All rights reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (ocm.rsp) was successfully created

Apply patch using the following command:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -ocmrf ./ocm.rsp 
...
Start OOP by Prereq process.
Launch OOP…
Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation.  All rights reserved.
...
Applying interim patch '28729234' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching component oracle.rdbms, 11.2.0.4.0…
Patch 28729234 successfully applied.

Checking that OCW is applied:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch lspatches
28729234;OCW Patch Set Update : 11.2.0.4.190115 (28729234)
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262)

JDBC 11g: SQLException(“Io exception: Connection reset”)

Problem:

Connection using 11g ojdbc was very slow and most of the time was failing with Connection reset error after 60s (default inbound connection timeout). Database alert log contained WARNING: inbound connection timed out (ORA-3136) errors.

Reason:

Oracle 11g JDBC drivers use random numbers during authentication. Those random numbers are generated by OS using /dev/random and if there is faulty/slow hardware or not too much activity on the system this generation can be slow, which causes slowness during jdbc connection.

Solution:

Instead of /dev/random indicate non-blocking /dev/urandom as java command line argument:

# java -Djava.security.egd=file:/dev/urandom -cp ojdbc8.jar:. JDBCTest "stbyrac-scan.example.com"

ORA-01103: when creating a Standby Database on the same Host as the Primary Database

Typically the standby and the primary databases are located on the different hosts to ensure the full DR capabilities. However, there are some situations when you want to have the primary and standby database on the same Host.

Problem #1: You are not able to start two databases with the same SID on the same server.

Problem #2: You cannot change db_name, because it is used in the controlfile and if you try to duplicate the standby database from the primary using different db_name, you will get the following error:

ORA-01103: database name 'orcldgst' in control file is not 'orcldg'

Assume db_name=orcldg and ORACLE_SID for the primary is orcldg1. To solve problem #1 and problem #2, you need to the following steps:

db_name must be the same for both databases. But during startup nomount of the standby database, you need to set ORACLE_SID to the different value:

$ export ORACLE_SID=orcldgst1
$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/mypfile.ora'

After that you will be able to run RMAN duplicate command to create the standby database on the same host as the primary.

Daylight saving time support in Oracle CRS

Dear readers,

I am glad to announce that my blog has been entered in Top 50 Oracle Blogs. For more information about Top 100 Oracle Blogs And Websites for Oracle DBAs To Follow in 2018 please visit https://blog.feedspot.com/oracle_blogs. You will improve your knowledge and experience by following them. 

In this post, I want to share my experience of how I solved the daylight saving time problem with Oracle CRS. With the default setup, in case timezone changes on your system, the client/application who connects to the database remotely(local/BEQ connections have correct timezone) will still have old timezone information and will enter wrong data.

Some countries,  that are not affected by daylight saving time are lucky and does not have to worry about it. But if your servers are not located in lucky countries then you must make CRS DTS aware.

During the GI installation, Oracle saves Timezone information in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt file, that makes TZ not to change for CRS even it is changed on OS level.

Please note that timezone can be changed for the database using srvctl:

srvctl setenv database -env 'TZ=time zone'

But I do not recommend to do that, because you must do the same everytime you create a new database.
Better to change TZ globally at CRS level.

In simple words just commenting out the TZ variable in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt and restarting the CRS on each node just one time is enough to do that, but let’s check it.

1.  List the current timezone settings:

[root@rac1 ~]# timedatectl status|grep zone
Time zone: UTC (UTC, +0000)
[root@rac2 ~]#  timedatectl status|grep zone
Time zone: UTC (UTC, +0000)

2. Change timezone at OS level:

[root@rac1 ~]# timedatectl set-timezone Europe/Bratislava
[root@rac2 ~]# timedatectl set-timezone Europe/Bratislava

3. Check local and scan connections:

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> select to_char(sysdate,'HH24:MI:SS AM')  dbtime from dual;

DBTIME
-----------
18:50:05 PM     <<<<<<<<<<<<Correct , same as OS

[oracle@rac1 ~]$ sqlplus marik/123@ORCL

SQL> select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
16:50:10 PM     <<<<<<<<<<<<Incorrect

4. Comment TZ in the config file:

[root@rac1 ~]# cat /u01/app/18.3.0/grid/crs/install/s_crsconfig_rac1_env.txt|grep TZ=
#   the appropriate time zone name. For example, TZ=America/New_York
#TZ=UTC

[root@rac2 ~]# cat /u01/app/18.3.0/grid/crs/install/s_crsconfig_rac2_env.txt|grep TZ=
#   the appropriate time zone name. For example, TZ=America/New_York
#TZ=UTC

5. Restart CRS on both nodes:

[root@rac1 ~]#  crsctl stop crs
[root@rac1 ~]#  crsctl start crs -wait
[root@rac2 ~]#  crsctl stop crs
[root@rac2 ~]#  crsctl start crs -wait

6. Change timezone on OS level several times and check local & scan connections:

[root@rac1 ~]# timedatectl set-timezone Africa/Conakry
[root@rac2 ~]# timedatectl set-timezone Africa/Conakry

Important: You need to reconnect to the database(so consider that sessions must be disconnected and reconnected again, old connections have old settings)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
17:15:56 PM <<<<<<<<<<<<Correct


[oracle@rac1 ~]$ sqlplus marik/123@ORCL

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
17:15:27 PM <<<<<<<<<<<<Correct

Change one more time:

[root@rac1 ~]# timedatectl set-timezone America/Aruba
[root@rac2 ~]# timedatectl set-timezone America/Aruba

Exit connections and reconnect:

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
13:17:47 PM <<<<<<<<<<<<Correct

[oracle@rac1 ~]$ sqlplus marik/123@ORCL

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
13:17:31 PM <<<<<<<<<<<<Correct

Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases

Document is self-explanatory: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2118136.2

 

ORACLE-BASE: Network ACL ddl generator script

After enabling Database Vault in our database there was a chance that this option may have changed ACL entries , so decided to save old entries and generated as a script.

If you have many ACLs than this job becomes time consuming.

I found the simple script that does it for us.

ORACLE-BASE site : https://oracle-base.com/dba/script?category=script_creation&file=network_acls_ddl.sql

SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 300
DECLARE
 l_last_acl dba_network_acls.acl%TYPE := '~';
 l_last_principal dba_network_acl_privileges.principal%TYPE := '~';
 l_last_privilege dba_network_acl_privileges.privilege%TYPE := '~';
 l_last_host dba_network_acls.host%TYPE := '~';

FUNCTION get_timestamp (p_timestamp IN TIMESTAMP WITH TIME ZONE)
 RETURN VARCHAR2
 AS
 l_return VARCHAR2(32767);
 BEGIN
 IF p_timestamp IS NULL THEN
 RETURN 'NULL';
 END IF;
 RETURN 'TO_TIMESTAMP_TZ(''' || TO_CHAR(p_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') || ''',''DD-MON-YYYY HH24:MI:SS.FF TZH:TZM'')';
 END;
BEGIN
 FOR i IN (SELECT a.acl,
 a.host,
 a.lower_port,
 a.upper_port,
 b.principal,
 b.privilege,
 b.is_grant,
 b.start_date,
 b.end_date
 FROM dba_network_acls a
 JOIN dba_network_acl_privileges b ON a.acl = b.acl
 ORDER BY a.acl, a.host, a.lower_port, a.upper_port)
 LOOP
 IF l_last_acl <> i.acl THEN
 -- First time we've seen this ACL, so create a new one.
 l_last_host := '~';

DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('-- ' || i.acl);
 DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.drop_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.create_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' description => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_acl := i.acl;
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_principal <> i.principal 
 OR (l_last_principal = i.principal AND l_last_privilege <> i.privilege) THEN
 -- Add another principal to an existing ACL.
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.add_privilege (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_host <> i.host||':'||i.lower_port||':'||i.upper_port THEN
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.assign_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' host => ''' || i.host || ''',');
 DBMS_OUTPUT.put_line(' lower_port => ' || NVL(TO_CHAR(i.lower_port),'NULL') || ',');
 DBMS_OUTPUT.put_line(' upper_port => ' || NVL(TO_CHAR(i.upper_port),'NULL') || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_host := i.host||':'||i.lower_port||':'||i.upper_port;
 END IF;
 END LOOP;
END;
/

Drop multiple columns faster in Oracle

From documentation: http://docs.oracle.com/database/122/ADMIN/managing-tables.htm#GUID-74A86E52-E2D2-405E-B888-94164E3973B9

“If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. In most cases, constraints, indexes, and statistics defined on the column are also removed. The exception is that any internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

Disabling/enabling general job execution before/after maintenance

During the maintenance you may need to disable jobs.

To disable jobs created by dbms_jobs set job_queue_processes to zero.

–Save old value

show parameter job_queue_processes
1000

–disable

alter system set job_queue_processes=0;

If you are using dbms_scheduler, this parameter does not work for you.

You will have to run the following:

dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

After finishing maintenance, enable them:

alter system set job_queue_processes=1000;
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

 

SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

How to find remote session executing over a database link

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;

GTXID is the same on both databases.

################################### Sample output ###################################

##Destination

3   LBREPDB01-51715  LBREP.aa2c0b4f.94.11.4694801  5447.62951   I   SQL*Net me

##Source

2   LB\MARIAMI-41196:4058  LBREP.aa2c0b4f.94.11.4694801 87.36231  I  SQL*Net me

More Details:

SID – 87
SERIAL – 36231