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.

Oracle Fails to Drop Unused Columns

When trying to drop an Oracle table unused columns, the sql command executes and shows successful, but actually the unused columns are not dropped.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

Further investigation shows the table is created with compression :

SQL>select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TRANSACTIONS';

OWNER    TABLE_NAME   COMPRESS  COMPRESS_FOR
-------- ------------ --------- -------------
TESTER    TRANSACTIONS ENABLED    ADVANCED

Change table to uncompressing status without moving data:

SQL>  alter table TRANSACTIONS NOCOMPRESS;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;
ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Have to move table with nocompress option, then drop unused columns successfully.

SQL> alter table TRANSACTIONS move nocompress;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

no rows selected

Oracle Enterprise Manager Corrective Action (CA) Execution Stats

How many times Corrective Action (CA) has been executed one year so far ?

SQL> select CA_NAME,  count(*) 
       from sysman.MGMT$CA_EXECUTIONS 
      where START_TIME>=sysdate-365  
   group by CA_NAME order by 1

CA_NAME                                  COUNT(*)
---------------------------------------- ----------
...
..
.
EXTEND_TABLESPACE_NORAC                         3
EXTEND_TABLESPACE_PDB                          16
EXTEND_TABLESPACE_RAC                          70
NON-RAC_RMAN_AUTO_BACKUP_ARCHIVELOGS           56
RMAN_AUTO_BACKUP_ARCHIVELOGS                  533
...
..
.

10 rows selected.

About view MGMT$CA_EXECUTIONS , Oracle official documentation is here or just click the following link.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emvws/job-views.html#GUID-CBDDC556-498B-4458-BCB4-CF28CAB3A834

How to Upgrade AWS RDS Microsoft SQL Server DB Engine

This blog demonstrates how to do minor version upgrades and major version upgrades against SQL server RDS on AWS.

This post also addresses some errors like ‘InvalidParameterCombination’, etc.

Subscribe to get access

Read more of this content when you subscribe today.

How to Get All Oracle Databases Size From OEM Repository

Database Manager asks you as a DBA about all the database size in the supported environment .

SQL> select host_name,target_name, sum( FILE_SIZE/1024/1024/1024)
     from mgmt$db_datafiles 
     group by host_name,target_name 
     order by host_name, target_name;

HOST_NAME                                TARGET_NAME                              SUM(FILE_SIZE/1024/1024/1024)
---------------------------------------- ---------------------------------------- -----------------------------
HOST1                                    DB1                                                    38.65
HOST2                                    DB2                                                    385.88
HOST3                                    DB3                                                    27.52
...
..
.

OR we can use another another view mgmt$db_tablespaces :

SQL> select host_name,target_name,sum( TABLESPACE_SIZE/1024/1024/1024) 
     from mgmt$db_tablespaces 
     group by host_name,target_name 
     order by 1,2;

How to Write Errors into SQL Server Log

The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:

IF EXISTS (
    SELECT bs.database_name,  MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
    FROM  msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
    INNER JOIN master.sys.databases d ON d.name = bs.database_name
    WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
    GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log

Check the log as below:

ALso we can check spid 55 details:

select * from sys.sysprocesses where spid =55;