Author: dbalifeeasy

Install Oracle 19c RAC Software and Create CDB Database

Define ORACLE_HOME and create ORACLE_HOME directory

[oracle@racnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@racnode1 ~]$ mkdir -p $ORACLE_HOME

Unzip oracle database binary into ORACLE_HOME

[oracle@racnode1 19c]$ unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

Run runInstaller as oracle owner

Please note the “runInstaller” script is under $ORACLE_HOME, not the one under $ORACLE_HOME/oui/bin. 

[oracle@racnode1 ~]$ cd $ORACLE_HOME
[oracle@racnode1 dbhome_1]$ ./runInstaller

Oracle 19c RAC software Installation Steps

Select Configuration Option -> Set Up Software Only
Select Database Installation Option -> Oracle Real Application Clusters database installation
Select List of Nodes
SSH connectivity..
OS Username: oracle
OS Password:
Reuse Private and Public Keys existing in the user home
Select Database Edition -> Enterprise Edition
Specify Installation Location
Oracle base: /u01/app/oracle
Software location:/u01/app/oracle/product/19.0.0/dbhome_1
Privilleged Operating System groups
Database Administator (OSDBA) group: dba
Database Operator (OSOPER) group (Optional): oper
Database Backup and Recovery (OSBACKUPDBA) group: backupdba
Data Guard administrative (OSDGDBA) group: dgdba
Encryption Key Management administrative (OSKMDBA) group: kmdba
Real Application Cluster administrative (OSRACDBA) group: racdba
Root script execution configuration -> No ticking Automatically run configuration scripts
Perform Prerequisite Checks
wait a few minute -> Ignore All -> Next
Summary
Install
Install Product -> Details
wait a fewer minutes -> use root account run shell script on every node
Finish Install database software

Run DBCA to create CDB database

[oracle@racnode1 ~]$ cd $ORACLE_HOME/bin
[oracle@racnode1 bin]$ ./dbca

Database Configuration Assistant Step by Step

Select Database Operation
Create a database
Select Database Creation Mode
Advanced configuration
Select Database Deployment Type
Database type: Oracle Real Application Cluster (RAC) database
Configuration type: Admin Managed
Select a template for your database: General Purpose or Transaction Processing
Select List of Nodes
Select all
Specify Database Identification Details
Global database name: RACDBD
SID Prefix: RACDB
ticking Create as Container database
ticking Use Local Undo tablespace for PDBs
choose Create a Container database with one or more PDBs
Number of PDBs: 1
PDB name:racdbpdb1
Select Database Storage Option
choose Use following for the database storage attributes
Database file storage type: Automatic Storage Mangement(ASM)
Database file location: +DATA/{DB_UNIQUE_NAME}
ticking Use Oracle-Managed Files(OMF)
Select Fast Recovery Option
ticking specify Fast Recovery Area
Recovery file storage type: Automatic Storage Management(ASM)
Fast Recovery Area: +FRA
Fast Recovery Area size: 13332 MB
ticking Enable archiving
Select Oracle Data Vault Config Option
NO ticking “Configure Oracle Database Vault”
NO ticking “Configure Oracle Label Security”
Next>
Specity Configuration Options
Memory: choose Use Automatic Shared Memory Management
SGA size: 3072 MB
PGA size: 512 MB
Sizing
Processes:300
Character set
Choose Use Unicode( AL32UTF8 )
National character set: AL16UTF16 – Unicode UTF-16 Universal character set
Default language: American
Default territory: United States
Connection mode
Choose Dedicated Server mode
Sample schemas
No ticking schema to the database
Next>
Specify Management Options
Tick Run Cluster Verfication Utility(CVU check periodically
Specify Database User Credentials
Choose Use the same administrative password for all accounts
Select Database Creation Option
Tick Create database
Tick Generate database creation scripts
Perform Prerequisite Check
Summary
Finish
Close

Verify CDB and PDB

[oracle@racnode1 ~]$ srvctl status database -d RACDBD
Instance RACDBD1 is running on node racnode1
Instance RACDBD2 is running on node racnode2
SQL>select CON_ID, NAME, OPEN_MODE, RESTRICTED, PDB_COUNT 
    from  v$containers;

CON_ID  NAME       OPEN_MODE    RES PDB_COUNT
----- -------- --------- --- -------
1       CDB$ROOT   READ WRITE   NO  1 
2       PDB$SEED   READ ONLY    NO  0 
3       RACDBDPDB1 READ WRITE   NO  0

SQL> show pdbs
CON_ID   CON_NAME   OPEN MODE   RESTRICTED
-----  -------  -------- --------
2        PDB$SEED   READ ONLY   NO 
3        RACDBDPDB1 READ WRITE  NO

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