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.

Thanks for reading this post. If you have any questions about how to build SQL 2019 Always On High Availability Group, feel free to contact us.

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.

How to Upgrade AWS RDS PostgreSQL With Extensions(2)

In another post “how to upgrade aws rds Postgresql with extensions(1)” we have demonstrated how to upgrade AWS PostgreSQL RDS 9.5.23-R1 to AWS PostgreSQL RDS 9.6.20-R1 with PostGIS extension. According to AWS doc about PostGIS extension:

If a database uses the PostGIS extension, you can’t skip major versions for some source to target combinations. For these circumstances, upgrade in steps to the next major version one step at a time until you reach the desired target version.

So in this post, we will show how to upgrade AWS PostgreSQL RDS 9.6.20-R1 with PostGIS extension to AWS PostgreSQL RDS 10.15-R1.

Subscribe to get access

Read more of this content when you subscribe today.

Tasks of After Major Version Upgrade

  • Run the ANALYZE operation to refresh the pg_statistic table.
  • Optionally, use Amazon RDS to view two logs that the pg_upgrade utility produces. These are pg_upgrade_internal.log and pg_upgrade_server.log. Amazon RDS appends a timestamp to the file name for these logs. You can view these logs as you can any other log. For more information, see Amazon RDS database log files.You can also upload the upgrade logs to Amazon CloudWatch Logs. For more information, see Publishing PostgreSQL logs to CloudWatch Logs.
  • To verify that everything works as expected, test your application on the upgraded database with a similar workload. After the upgrade is verified, you can delete this test instance.

How to Upgrade AWS RDS PostgreSQL With Extensions(1)

One of the client just received notifications about end of support for AWS PostgreSQL RDS 9.5. They do have a couple of PostgreSQL RDS 9.5 in AWS. Some of them are using PostGIS extensions.

This post demonstrates step by step how to upgrade AWS PostgreSQL RDS 9.5.23-R1 to AWS PostgreSQL RDS 9.6.20-R1. Certainly the upgrading method in this post can be applied to other versions as well like AWS PostgreSQL RDS 10, 11 and 12, etc.

Subscribe to get access

Read more of this content when you subscribe today.

When Do You Need Startup Upgrade For OJVM PSU/RU Patches

While applying Combo of OJVM and Database Release Update 19.9.0.0.201020, something comes to my mind that when and why “startup upgrade” is needed ? Is it possible to startup database in normal mode before running datapatch command for post-installation step?

Subscribe to get access

Read more of this content when you subscribe today.

Failed to connect to ASM instance. The connection is closed: The connection is closed

One client OEM reports the following incidents intermittently from OEM 13c.

Host=racnode1.virtuallab
Target type=Automatic Storage Management
Target name=+ASM1_racnode1.virtuallab
Categories=Availability
Message=Failed to connect to ASM instance. The connection is closed: The connection is closed
Severity=Fatal
Event reported time=20/12/2020 09:19:01AM EST
Target Lifecycle Status=Test
Operating System=LINUX
Platform=X86_64
Associated Incident Id=12345
Associated Incident Status=New
Associated Incident Owner=oracle
Associated Incident Acknowledged By Owner=No
Associated Incident Priority=High
Associated Incident Escalation Level=0
Event Type=Target Availability
Event name=Status
Availability status=Down

Let’s do some investigations, and provide the easy workaround if patches are unavailable to some clients.

Subscribe to get access

Read more of this content when you subscribe today.

How to Check Process Memory Usage in Linux

This post demonstrates how to check a process memory usage in Linux. While there are system resource issues, it is always helpful to identify the root cause by checking suspected process memory usage, which can be memory leaking from time to time.

Subscribe to get access

Read more of this content when you subscribe today.