Blog

Featured

SQL Server Migration Assistant (SSMA) Tables Partially Migrated and High Memory Usage

PROBLEM

While we migrate a relatively big size database from Oracle to Microsoft SQL Server, all the small tables are migrated successfully without any issues. But the following issues occur from big tables migration.

  • “SSMAforOracle.exe” process consumes high memory, so the server got 99% memory utilization consistently.
  • For big tables with more than 10 million records, they are always partially migrated.
  • After big tables migration failure, we can see sometime the follow message in the migration log.

“Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

DataMigration Error. 
From : "SCHEMANAME"."TABLENAME"
To : [DBNAME].[SCHEMANAME].[TABLENAME] Execution Timeout [Error: Datamigrator] [9812/41] [2020-11-01 21:21:34]: Exception: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

It is not really a timeout issue. Even after increasing the “Data Migration Timeout” from default 5 mins to maximum 1440 mins( 24 hrs), the migration of big tables still fails.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Featured

How to Restrict Parallel Server Processes Running on Specific Oracle RAC Nodes

For this SQL, I want all the parallel processes running on this node or those nodes only.

All Oracle DBA knows, by default, the parallel server processes spawned to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster.

For big indexes rebuilding , a client wants all the parallel server processes running on specific node(s).

There are two easy ways to achieve this without bouncing database required.

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Install Oracle RAC Software and Create Oracle RAC Database In Silent Mode

In this post, I am going to install Oracle 18c RAC software, and create a 18c RAC database in silent mode.

This post will also apply to how to install Oracle 19c RAC software, and create a 19c RAC database in silent mode.

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Step by Step Setting Up SQL Server Always On Availability Group

This post demonstrates how to build SQL-Server High Availability feature Always On. All the step by step building procedures make use of Virtual machine, Windows OS, Networks, Active Directory Domain, DNS, Firewall, SQL Server, Clustering, etc.

Contents

Subscribe to get access

Read more of this content when you subscribe today.

Featured

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.

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.
Featured

Step by Step Installing Oracle Enterprise Manager 13cR3( 13.3.0.0 ) Cloud Control

CONTENTS

Introduction

This post demonstrates the installation of Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0) on Oracle Linux  7 (x86_64).

Subscribe to get access

Read more of this content when you subscribe today.

Access Enterprise Manager Cloud Control 13c

URL :            https://oemnode1:7803/em
username:  sysman
Password:   you specified during your installation

Capture
Capture

Check OMS and Local Agent Status

Local Agent Status

[oracle@oemnode1 scripts]$ . oraenv
ORACLE_SID = [OEMREP] ? AGENT

[oracle@oemnode1 scripts]$ emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.3.0.0.0
OMS Version : 13.3.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/Product/Agent/agent_inst
Agent Log Directory : /u01/app/oracle/Product/Agent/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/Product/Agent/agent_13.3.0.0.0
Core JAR Location : /u01/app/oracle/Product/Agent/agent_13.3.0.0.0/jlib
Agent Process ID : 6463
Parent Process ID : 6403
Agent URL : https://oemnode1:3872/emd/main/
Local Agent URL in NAT : https://oemnode1:3872/emd/main/
Repository URL : https://oemnode1:4903/empbs/upload
Started at : 2020-03-27 23:43:12
Started by user : oracle
Operating System : Linux version 4.14.35-1902.10.7.el7uek.x86_64 (amd64)
Number of Targets : 33
Last Reload : (none)
Last successful upload : 2020-03-28 00:14:07
Last attempted upload : 2020-03-28 00:14:07
Total Megabytes of XML files uploaded so far : 1.72
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 13.15%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-03-28 00:13:52
Last successful heartbeat to OMS : 2020-03-28 00:13:52
Next scheduled heartbeat to OMS : 2020-03-28 00:14:52

Agent is Running and Ready

OMS & BI Publisher status

[oracle@oemnode1 ~]$ . oraenv
ORACLE_SID = [OMS] ? OMS

[oracle@oemnode1 ~]$ emctl status oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
BI Publisher Server is Up

[oracle@oemnode1 ~]$ emctl status oms -details
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : oemnode1
HTTP Console Port : 7788
HTTPS Console Port : 7803
HTTP Upload Port : 4889
HTTPS Upload Port : 4903
EM Instance Home : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemnode1:7803/em
Upload URL: https://oemnode1:4903/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : oemnode1
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oemnode1
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up

BI Publisher Server Information
BI Publisher Managed Server Name: BIP
BI Publisher Server is Up

BI Publisher HTTP Managed Server Port : 9701
BI Publisher HTTPS Managed Server Port : 9803
BI Publisher HTTP OHS Port : 9788
BI Publisher HTTPS OHS Port : 9851
BI Publisher is locked.
BI Publisher Server named 'BIP' running at URL: https://oemnode1:9851/xmlpserver/servlet/home
BI Publisher Server Logs: /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Log : /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher/bipublisher.log
[oracle@oemnode1 ~]$

Most Used Commands for OMS and Agent

Stop only OMS & JVMD Engine

[oracle@oemnode1 ~]$ emctl stop oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down

[oracle@oemnode1 ~]$ emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Down
JVMD Engine is Down
BI Publisher Server is Up
[oracle@oemnode1 ~]$

Stop OMS including Administration Server, BI Publisher,HTTP Server and Node Manager

[oracle@oemnode1 ~]$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down
[oracle@oemnode1 ~]$

Check the status of OMS

[oracle@oemnode1 ~]$ emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Up
[oracle@oemnode1 ~]$

Get detailed OMS status,  SYSMAN Password is required

[oracle@oemnode1 ~]$ emctl status oms -details
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : oemnode1
HTTP Console Port : 7788
HTTPS Console Port : 7803
HTTP Upload Port : 4889
HTTPS Upload Port : 4903
EM Instance Home : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemnode1:7803/em
Upload URL: https://oemnode1:4903/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : oemnode1
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oemnode1
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up

BI Publisher Server Information
BI Publisher Managed Server Name: BIP
BI Publisher Server is Up

BI Publisher HTTP Managed Server Port : 9701
BI Publisher HTTPS Managed Server Port : 9803
BI Publisher HTTP OHS Port : 9788
BI Publisher HTTPS OHS Port : 9851
BI Publisher is locked.
BI Publisher Server named 'BIP' running at URL: https://oemnode1:9851/xmlpserver/servlet/home
BI Publisher Server Logs: /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Log : /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher/bipublisher.log

Stop OEM agent

[oracle@oemnode1 ~]$ emctl stop agent

Start OEM agent

[oracle@oemnode1 ~]$ emctl start agent

Check status of OEM agent

[oracle@oemnode1 ~]$ emctl status agent
Featured

How to Upgrade Linux OS In RAC Environment

This post introduces how to upgrade Linux OS from RHEL 5 to RHEL 7 with zero downtime for two nodes RAC. 

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Step by Step Installing Oracle 12c GI and RAC on Linux 7 Using VirtualBox

Great start to build your own RAC with detailed instructions and explanations. For building 18c/19c/20c GI/RAC, this post is still helpful. After completing this lab, you should be in better position as an Oracle RAC DBA.

Contents

Introduction

This article describes the detailed step by step installation of Oracle Database 12c  (12.1.0.2 64-bit) GI and RAC on Linux (Oracle Linux 7.1 64-bit) using VirtualBox (5.0.10). It applies to Oracle 18c and 19c GI and RAC as well.

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Client Side Transparent Application Failover for Oracle RAC

TAF – Transparent Application Failover is a very useful feature in mission critical environment.

TAF – – Transparent Application Failover is a feature of the OCI driver, and TAF cannot be used with thin driver. The client side TAF can be configured through client connection definitions in TNS connect descriptors.

The tests are held under the following environment,  both 11gR2 database and client are used to test the behaviors of client side TAF by using SCAN.

                    GI: 12.1.0.2.0
4 nodes RAC: 11.2.0.4
Oracle Client: 11.2.0.4

The service name is RAC_TEST running on node1 and node4, while available nodes are node2 and node3.

$ srvctl status service -s RAC_TEST -d TESTDB
Service RAC_TEST is running on instance(s) TESTDB1,TESTDB4

There are two TYPES of TAF available, SESSION and SELECT.

SESSION: Session Failover re-creates the connections and sessions to the surviving instance.

SELECT: In addition to recreating the session, Select Failover also replays the queries that were in progress.

There are two METHODS in which TAF establishes the failover connection, BASIC and PRECONNECT.

BASIC: The second connection is re-established only after the first connection to the instance or service failed.

PRECONNECT: Two connections are established when the client logs in to the database. A login to database will create two connection at the same time. For this to work, clusterware actually starts two services. One main service and another shadow service.

Client Side TAF Configuration — BASIC

For non-TAF database connection, by query gv$session to get client side TAF connection details, which demonstrates the server side TAF is not configured. That is what we expected in this case.

SQL>select INST_ID,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER
from gv$session
where username='TESTUSER';

INST_ID USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER ------- --------- -------------- ----------- -------------
4 TESTUSER NONE NONE NO

Use the following TNS entry to test client side TAF with BASIC method.

CLIENT_TAF =
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =CLU-SCAN)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = RAC_TEST)
(FAILOVER_MODE= (TYPE=select)(METHOD=basic)
(RETRIES=20)(DELAY=15))
)
)

1) Make a database connection by using TNS entry CLIENT_TAF, and run a long query :

SQL>select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
           FAILOVER_METHOD,FAILED_OVER 
      from gv$session 
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI -------- ----- -------- --------- ------------- ----------- --- 4 70 11171 TESTUSER SELECT BASIC NO

2) Manually kill the session ( only works when session is ACTIVE, otherwise it is not working for killing from testing ).

SQL> alter system kill session '70,11171,@4';

System altered.

 Query the session status, we can see a new failed over session with different ‘SID, serial#’ created, and the query is still going until completed.

 SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
             FAILOVER_METHOD,FAILED_OVER 
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER -------- ---- ------- -------- ------------- --------- ------------ 4 366 14271 TESTUSER SELECT BASIC YES

3) Instead of killing session in step 2, we manually stop the service on node 4 to simulate instance crash. (  it works for both ACTIVE and INACTIVE sessions ).

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE  FAILOVER_M FAI
------- --- ------- -------- -------------- ---------- ---
4 70 11185 TETSUSER SELECT BASIC NO

Now stop the service on node4, expect the session will failover onto survival node1 .

$ srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f
SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI
------- --- -------- --------- -------------- ---------- ---
1 367 8663 TETSUSER SELECT BASIC YES

restart the service on node4 for more tests.

$ srvctl start service -s RAC_TEST -i TESTDB4 -d TESTDB

Client Side TAF Configuration — PRECONNECT

Two tnsname entries are prepared with following contents:

PRIMARY=
( DESCRIPTION=(ADDRESS=
(PROTOCOL=tcp)(HOST=clu-scan)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=RAC_TEST)
(FAILOVER_MODE=(BACKUP=SECONDARY)(TYPE=select)
(METHOD=preconnect))
)
)

SECONDARY=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=clu-scan)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=RAC_TEST)
(FAILOVER_MODE=
(BACKUP=PRIMARY)
(TYPE=select)
(METHOD=preconnect))
)
)

1) Make a database connection by using TNS entry above, and run a long query :

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
------- ---- ------ ---------- ------------ ----------- --- -------- 1 42 8485 TESTUSER NONE NONE NO ACTIVE 4 366 14375 TESTUSER SELECT PRECONNECT NO INACTIVE

2) Manually stop service RAC_TEST on node 4.

$ srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f

3) The preconnected session takes over the session and continue the query until completion.

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS ------- --- ------- ---------- ------------ ------------- --- -------- 1 42 8485 TESTUSER SELECT PRECONNECT YES ACTIVE

Restart service on node4 for next test.

$ srvctl start service -s RAC_TEST -i -i TESTDB4 -d TESTDB

4) if you kill this PRECONNECT session again , TAF still works again as preconnect.

SQL> alter system kill session '42,8485,@1';

System altered.
SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
------- --- ------- --------- ------------ ----------- --- ------
4 265 1563 TESTUSER SELECT PRECONNECT YES ACTIVE

TAF – Transparent Application Failover is a very useful feature in mission critical environment, and it should be used whenever it is available and possible.

RMAN-06429: RCVCAT database is not compatible with this version of RMAN

While trying to register 19c database into RMAN catalog, the following errors occur:

$ rman target / catalog rman/xxxxxxxx@rman

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 28 09:43:02 2022
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=9998108000)
recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 12.02.00.01. in RCVCAT database is too old

RMAN> register database;

PL/SQL package RMAN.DBMS_RCVCAT version 12.02.00.01. in RCVCAT database is too old
PL/SQL package RMAN.DBMS_RCVCAT version 12.02.00.01. in RCVCAT database is too old
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 09/28/2022 09:43:21
RMAN-06429: RCVCAT database is not compatible with this version of RMAN

SOLUTION

In this situation, The RMAN Oracle database version is 12.1.0.2, the catalog version is 12.02.00.01, and target database to be registered version is 19.10.0.0.0. Since the catalog version must be equal to or greater than the target version, so upgrade catalog is required.

SQL> select * from rman.rcver;

VERSION
---------------
VERSION
------------
12.02.00.01
RMAN> upgrade  catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>  UPGRADE CATALOG;

recovery catalog upgraded to version 19.10.00.00.00
DBMS_RCVMAN package upgraded to version 19.10.00.00
DBMS_RCVCAT package upgraded to version 19.10.00.00.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
SQL> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 28 11:22:41 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select distinct status from dba_objects where owner='RMAN';

STATUS
-------
VALID

SQL> select * from rman.rcver;

VERSION
---------------
19.10.00.00.00

ORA-39181: Only partial table data may be exported due to fine grain access control

Oracle data pump tries to export some tables with the following errors:

...
..
.
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."BRL"
. . exported "RMAN"."BRL"                                    0 KB       0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."RLH"
. . exported "RMAN"."RLH"                                    0 KB       0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."ROUT"
. . exported "RMAN"."ROUT"                                   0 KB       0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMAN"."BP"
. . exported "RMAN"."BP"                                     0 KB       0 rows
...
..
.

Workaround

Grant “EXEMPT ACCESS POLICY” privilege to data pump user :

SQL> grant EXEMPT ACCESS POLICY to expuser;

Grant succeeded.

How to upgrade DBAAS Cloud Tooling using dbaascli

Logon as root user

$ sudo -s
#

 Check your current version of cloud tooling

#  rpm -qa|grep -i dbaastools
dbaastools_exa-1.0-1+22.2.1.1.0_220713.1149.x86_64

Check whether any cloud tooling updates are available

# sudo dbaascli admin showLatestStackVersion
DBAAS CLI version 22.2.1.1.0
Executing command admin showLatestStackVersion
Job id: 7cf1c287-3617-4d9b-990b-7b2147d1e164
{
  "description" : "DBAAS Tools for Oracle Public Cloud",
  "version" : "22.3.1.0.1_220831.2106",
  "baseVersion" : "22.3.1.0.1"
}
dbaascli execution completed

# dbaascli patch tools list
DBAAS CLI version 22.2.1.1.0
Executing command patch tools list
Checking Current tools on all nodes

exaccnode1: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

exaccnode2: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

exaccnode3: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

exaccnode4: Patchid : 22.2.1.1.0_220713.1149

Available Patches
Patchid : 22.3.1.0.1_220831.2106(LATEST)

Install the latest version of dbaastools by using the command: dbaascli admin updateStack

All Nodes have the same tools version

Upgrading Prechecks

# dbaascli admin updateStack --prechecksOnly --version LATEST
DBAAS CLI version 22.2.1.1.0
Executing command admin updateStack --prechecksOnly --version LATEST
INFO : Review log file => /var/opt/oracle/log/tooling/Update/Update_yyyy-mm-dd_hh24:mi:ss.nnnnnnnnnnnn.log 
============ Starting RPM update operation =========== 
Loading PILOT...
Session ID of the current execution is: 274
Log file location: /var/opt/oracle/log/tooling/Update/pilot_mm-dd_hh24:mi:ss-PM
-----------------
Running Plugin_initialization job
Completed Plugin_initialization job
-----------------
Running Default_value_initialization job
Completed Default_value_initialization job
-----------------
Running Rpm_version_validation job
Completed Rpm_version_validation job
-----------------
Running Rpm_source_validation job
Completed Rpm_source_validation job
-----------------
Running Disk_space_download_validation job
Completed Disk_space_download_validation job
-----------------
Running Rpm_download job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_validation job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_disk_space_validation job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_local_installation job
Skipping. Job is detected as not applicable.
-----------------
Running Rpm_remote_installation job
Skipping. Job is detected as not applicable.
-----------------
Running Installed_rpm_backup job
Skipping. Job is detected as not applicable.
-----------------
Running Cleanup_downloaded_rpm job
Skipping. Job is detected as not applicable.
-----------------
Running Cleanup_legacy_rpms job
Skipping. Job is detected as not applicable

Upgrade Cloud Tooling

# dbaascli admin updateStack --version LATEST
...
..
.

Check your current version of cloud tooling

# rpm -qa --last|egrep -i "dbaastools_exa|dbcs"
dbaastools_exa-1.0-1+22.3.1.0.1_220831.2106.x86_64 Fri 16 Sep 2022 02:14:38 PM AEST
dbcs-agent-update-exacc-22.2.1.1.0-220713.1149.x86_64 Thu 28 Jul 2022 01:25:22 PM AEST
dbcs-agent-exacc-2.8OL7-21.1.1.0.2_210408.1900.x86_64 Wed 07 Jul 2021 08:07:29 PM AEST

Check Available Updates

# dbaascli admin showLatestStackVersion
DBAAS CLI version 22.3.1.0.1
Executing command admin showLatestStackVersion
Job id: 886e630e-f911-425d-9219-936d62e5ab5e
{
  "description" : "DBAAS Tools for Oracle Public Cloud",
  "version" : "22.3.1.0.1_220831.2106",
  "baseVersion" : "22.3.1.0.1"
}
dbaascli execution completed


# dbaascli patch tools list
DBAAS CLI version 22.3.1.0.1
Executing command patch tools list
Checking Current tools on all nodes

exaccnode1: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

exaccnode2: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

exaccnode3: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

exaccnode4: Patchid : 22.3.1.0.1_220831.2106
No applicable tools patches are available

All Nodes have the same tools version

Site StandbyDb returned ORA-16664

The ORA-16664 occur4es in Data Guard log files as following:

...
..
.
Site StandbyDb returned ORA-16664.
...
..
.

Solution

a) Check both alert and data guard logs of primary database

b) Check both alert and data guard logs of standby database with following errors:

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925

...
..

.
OS Audit file could not be created; failing after 6 retries

...

After cleaned the old audit files, then everything is fine.

DGMGRL> show database verbose "STANDBYDB";

...
..
.
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Average Apply Rate:      4.38 MByte/s
  Active Apply Rate:       1.11 MByte/s
  Maximum Apply Rate:      46.39 MByte/s
..
..
.
Database Status:
SUCCESS

DGMGRL>

How to Delete OS Audit Files in Oracle

When manually deleting OS audit files in Oracle, the following error occurs:

$ cd $ORCALE_HOME/rdbms

$ du -sh ./audit
38G     ./audit

$ cd audit
$ rm *.aud
-bash: /bin/rm: Argument list too long

SOLUTION

$ find ./ -name "*.aud" -mtime +30 -exec rm {} \;

OR

$ find ./ -name "*.aud"  -exec rm {} \;