Category: Oracle

Apply Oracle Coherence Patch onto OMS

This post demonstrates how to apply an Oracle Coherence patch onto 13.4 OMS home.

First thing to do is to download Oracle Coherence 12.2.1.3.7 patch binary “p31030882_122130_Generic.zip”, and unzip it onto a staging area.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Find Bind Variable Values of Oracle SQL Query

A couple of ways to retrieve bind variables in real time or from history

Oracle database captures a SQL query bind variables and their  values periodically, and the default capture bind variables interval is 900 seconds(15 minutes), which is modifiable but facing performance concerns.

Parameter Description                 Session Value  Instance Value
------------------------------------- -------------- --------------- 
_cursor_bind_capture_interval              900           900
interval(in seconds) between two bind 
capture for a cursor
SQL>alter system set "_cursor_bind_capture_interval"=30 scope=both;

Oracle intends to minimize the impact on database performance, and makes the capturing interval a pretty big value( like 15 minutes). So it is not a very reliable method to retrieve bind variables from Oracle database views.

Please note you need have licenses of “Oracle Diagnostics & Tuning Packs” if you query dba_hist_****** and v$sql_monitor in this post.

There are a couple of ways to retrieve bind variables in real time or from history.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Upgrade Oracle Database by Using AutoUpgrade Utility

Contents

Introduction

This post demonstrates how to upgrade 18c database to 19c by using Oracle upgrade utility tool – AutoUpgrade.

Environment :

Primary Database Host / Database : oemnode1 / OEMREP

Standby Database Host / Database:   stbnode1 / STBOEMREP

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Final Checks

DGMGRL> show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show database stboemrep

Database - stboemrep

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 30.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STBOEMREP

Database Status:
SUCCESS
[oracle@oemnode1 dbs]$ $ORACLE_HOME/OPatch/opatch lspatches

30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
30869156;Database Release Update : 19.7.0.0.200414 (30869156)
30805684;OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)

OPatch succeeded.
SQL> set pagesize 200
SQL> set linesize 300
SQL>  select CON_ID,
        TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
        PATCH_ID,
        PATCH_TYPE,
        ACTION,
        DESCRIPTION,
        SOURCE_VERSION,
        TARGET_VERSION
   from CDB_REGISTRY_SQLPATCH
  order by CON_ID, action_time, patch_id;  

    CON_ID ACTION_TIM   PATCH_ID PATCH_TYPE ACTION   DESCRIPTION                                           SOURCE_VERSION  TARGET_VERSION
---------- ---------- ---------- ---------- -------- ----------------------------------------------------- --------------- ---------------
         1 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         1 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0
         3 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         3 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0

 SQL> select comp_name, status,version,VERSION_FULL 
        from dba_registry

COMP_NAME                         STATUS     VERSION   VERSION_FULL
--------------------------------- ---------  --------- ------------
Oracle Database Catalog Views     VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Packages and TypesVALID      19.0.0.0.0 19.7.0.0.0
Oracle Real Application Clusters  OPTION OFF 19.0.0.0.0 19.7.0.0.0
JServer JAVA Virtual Machine      VALID      19.0.0.0.0 19.7.0.0.0
Oracle XDK                        VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Java Packages     VALID      19.0.0.0.0 19.7.0.0.0
OLAP Analytic Workspace           VALID      19.0.0.0.0 19.7.0.0.0
Oracle XML Database               VALID      19.0.0.0.0 19.7.0.0.0
Oracle Workspace Manager          VALID      19.0.0.0.0 19.7.0.0.0
Oracle Text                       VALID      19.0.0.0.0 19.7.0.0.0
Oracle Multimedia                 VALID      19.0.0.0.0 19.7.0.0.0
Spatial                           VALID      19.0.0.0.0 19.7.0.0.0
Oracle OLAP API                   VALID      19.0.0.0.0 19.7.0.0.0
Oracle Label Security             VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Vault             VALID      19.0.0.0.0 19.7.0.0.0

15 rows selected.
SQL> show con_name;

CON_NAME
----------------
EMPDBREPOS

SQL> select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

SQL> connect / as sysdba
Connected.
SQL>  select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

Check TimeZone version has been upgraded to version 32 from version 31 in 18c.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION     CON_ID
-------------------- ------- ----------
timezlrg_32.dat           32          0


SQL> SELECT tz_version FROM registry$database;

TZ_VERSION
----------
        32

SQL> SELECT property_name, property_value
     FROM   database_properties
     WHERE  property_name LIKE 'DST_%'
  ORDER BY property_name

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT DBMS_DST.get_latest_timezone_version  from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

Drop GRP ( Guarantee Restore Point )

SQL> select name from v$restore_point;

NAME
--------------------------------------------
AUTOUPGRADE_221145114461854_OEMREP

SQL> drop restore point AUTOUPGRADE_221145114461854_OEMREP;

Restore point dropped.

ORA-46952: standby database format mismatch for password file

SYMPTOM

After Oracle database has been upgraded from 18c to 19c, the Standby database alert log shows the following ORA-errors.

ORA-46952: standby database format mismatch for password file 
           '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwSTBOEMREP'
MRP0 (PID:6256): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-01578 ORA-01110 ORA-26040: Data block was loaded using the NOLOGGING option

Alert log shows following ORA- errors:

ORA-01578:ORACLE data block corrupted (file # 13, block # 652351)
ORA-01110:data file 13:'/media/sf_software/oradata/OEMREP/EMPDBREPOS/mgmt.dbf'
ORA-26040:Data block was loaded using the NOLOGGING option

Find out the segment impacted, and confirm this is an empty table.

SQL>select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
    from dba_extents 
   where file_id =13 and 652351 between block_id AND block_id+blocks-1;

OWNER    SEGMENT_NAME           SEGMENT_TYPE   TABLESPACE_NAME
-------- ---------------------  -------------- ----------------
SYSMAN   MOS_PA_EVAL_TARGETS_E  TABLE          MGMT_TABLESPACE
SQL> select count(*) from SYSMAN.MOS_PA_EVAL_TARGETS_E;

COUNT(*)
----------
0
SQL>select FILE#, BLOCK#, BLOCKS,scn_to_timestamp(NONLOGGED_START_CHANGE#), 
           scn_to_timestamp( NONLOGGED_END_CHANGE#), OBJECT# 
    from v$nonlogged_block;

FILE# BLOCK#   BLOCKS  SCN_TO_TIMESTAMP(NONLOGGED_START_CHANGE#)  SCN_TO_TIMESTAMP(NONLOGGED_END_CHANGE#)  OBJECT#
---------------------- -----------------------------------------  ---------------------------------------  -------
13   652351   1        12-MAY-20 11.58.27.000000000 AM           12-MAY-20 10.59.29.000000000 PM           91461

Truncate or move the table. 

SQL>truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

or

SQL> alter table SYSMAN.MOS_PA_EVAL_TARGETS_E move tablespace MGMT_TABLESPACE;

luckily, the table is empty. it is easy to deal with. If table is not empty, then we can skip corrupted blocks to avoid DML ORA- errors.

Skip corrupt blocks 

SQL> select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E;

select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 652351)
ORA-26040: Data block was loaded using the NOLOGGING option
BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'SYSMAN',
    object_name => 'MOS_PA_EVAL_TARGETS_E',
    object_type => DBMS_REPAIR.table_object,
    flags       => DBMS_REPAIR.skip_flag);
END;
/
 
SQL> select owner, table_name, SKIP_CORRUPT  from dba_tables 
      where table_name='MOS_PA_EVAL_TARGETS_E'

OWNER      TABLE_NAME                     SKIP_COR
---------- ------------------------------ --------
SYSMAN     MOS_PA_EVAL_TARGETS_E          ENABLED

Backup the table data, truncate the table , and then restore the table data

SQL> create table SYSMAN.STG_MOS_PA_EVAL_TARGETS_E as 
            select * from SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL> Truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL>Insert into SYSMAN.MOS_PA_EVAL_TARGETS_E 
                select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E ;

The nologging corrupt block is returned to free space list

SQL> Select BYTES from dba_free_space 
     where file_id=13 and 652351 between block_id and block_id + blocks -1;

BYTES
----------
65536

SQL> select * from dba_extents 
     where file_id =13 and 652351 between block_id AND block_id+blocks-1;

no rows selected

After a little while, the nologging corrupt block will be reused

SQL> Select BYTES from dba_free_space
where file_id=13 and 652351 between block_id and block_id + blocks -1; 

no rows selected

SQL> select count(*) from dba_extents
where file_id =13 and 652351 between block_id AND block_id+blocks-1; 

COUNT(*)
----------
1