Blog
DBlink Oracle for three databases in SQL Server
Category: Database Author: César Carvalho Date: 1 year ago Comments: 0

DBlink Oracle for three databases in SQL Server

Building access via dblink to three SQL Server databases.
We will use a native technology of the Oracle database, the Heterogeneous Services (HS).
 
Databases:
10.16.0.11 - Source Database Oracle database

10.16.0.15 - SQL Server target database

 

Create a user with the necessary read permissions in the SQL Server environment:

 

User: USR_CONSULTA

Snhea: P!!2012300

 

Use some Oracle/EPEL repository:

 

[root@instance-20220613-1754 yum.repos.d]# cat oracle-linux-ol7.repo

[ol7_latest]

name=Oracle Linux $releasever Latest ($basearch)

baseurl=https://yum.oracle.com/repo/OracleLinux/OL7/latest/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=1

enabled=1

 

We can use the Microsoft repository too:

 

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

 

Installing the necessary packages:

 

yum clean all

yum repolist

yum install unixODBC unixODBC-devel freetds -y

 

ODBC installation validation:

 

[root@olaria ~]# odbcinst -j

unixODBC 2.3.7

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

[root@olaria ~]#

 

Creating the configuration init of Heterogeneous Services in Oracle referring to the three SQL Server databases that we will access via dblink:
Name of the databases that we will access in SQL Server: AEQUUS,FINANCIAL,CONTABILIS

 

[oracle@olaria ~]$ cd $ORACLE_HOME/hs/admin/




[oracle@olaria admin]$ cat initAEQUUS.ora

HS_FDS_CONNECT_INFO = AEQUUS

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB

set ODBCINI=/etc/odbc.ini




[oracle@olaria admin]$ cat initFINANCIAL.ora

HS_FDS_CONNECT_INFO = FINANCIAL

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB

set ODBCINI=/etc/odbc.ini




[oracle@olaria admin]$ cat initCONTABILIS.ora

HS_FDS_CONNECT_INFO = CONTABILIS

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB

set ODBCINI=/etc/odbc.ini

 

Create if it doesn’t exist or edit the ODBC configuration file that we installed earlier, each bank must have a connection entry:

 

[root@olaria yum.repos.d]# cat /etc/odbc.ini




[DBC Data Sources]

AEQUUS = MSSQL Server




[AEQUUS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = AEQUUS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

DisguiseGuid = Yes

text size = 64512

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

CONTABILIS = MSSQL Server




[CONTABILIS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = CONTABILIS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

FINANCIAL = MSSQL Server




[FINANCIAL]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = FINANCIAL

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8

 

Create if it doesn’t exist or edit the ODBC configuration file that we installed earlier, each bank must have a connection entry:

 

[root@olaria yum.repos.d]# cat /etc/odbc.ini




[DBC Data Sources]

AEQUUS = MSSQL Server




[AEQUUS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = AEQUUS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

DisguiseGuid = Yes

text size = 64512

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

CONTABILIS = MSSQL Server




[CONTABILIS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = CONTABILIS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

FINANCIAL = MSSQL Server




[FINANCIAL]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = FINANCIAL

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8

 

Adjust tnsnames.ora with the access entries to the three SQL Server databases that we will use in dblink:

 

[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/tnsnames.ora




# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.




AEQUUS =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))

      (CONNECT_DATA=(SID=AEQUUS))

      (HS=OK)

    )




CONTABILIS =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))

      (CONNECT_DATA=(SID=CONTABILIS))

      (HS=OK)

    )




FINANCIAL =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))

      (CONNECT_DATA=(SID=FINANCIAL))

      (HS=OK)

    )

 

Adjust the listener.ora with the access entries to the three SQL Server databases that we will use in the dblink:

 

[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.




LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = olaria)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

DEFAULT_SERVICE_LISTENER=orcl

ADR_BASE_LISTENER = /u01/app/oracle







SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ORCL)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)

      (SID_NAME = ORCL)

    )

        (SID_DESC=

          (SID_NAME=AEQUUS)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

          (PROGRAM=dg4odbc)

        )




        (SID_DESC=

          (SID_NAME=CONTABILIS)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

          (PROGRAM=dg4odbc)

        )




        (SID_DESC=

          (SID_NAME=FINANCIAL)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

          (PROGRAM=dg4odbc)

        )




  )







- Reload listener.ora settings

[oracle@olaria db_1]$ lsnrctl reload




LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUN-2022 10:16:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=olaria)(PORT=1521)))

The command completed successfully

 

Test the access via odbc by the osql utility:

 

osql -S AEQUUS -U USR_CONSULTA -P 12398546




Attempting connection as dblinkdev ...

+ isql AEQUUS USR_CONSULTA 12398546 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>













osql -S CONTABILIS -U USR_CONSULTA -P 12398546




Attempting connection as dblinkdev ...

+ isql CONTABILIS USR_CONSULTA 12398546 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>







osql -S FINANCIAL -U USR_CONSULTA -P 12398546




Attempting connection as dblinkdev ...

+ isql FINANCIAL USR_CONSULTA 12398546 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

 

Create dblinks and perform access tests:

 

create public database link AEQUUS connect to "USR_CONSULTA" identified by "12398546" using 'AEQUUS';

create public database link CONTABILIS connect to "USR_CONSULTA" identified by "12398546" using 'CONTABILIS';

create public database link FINANCIALARACAJU connect to "USR_CONSULTA" identified by "12398546" using 'FINANCIALARACAJU';

 

Test select dblinks:

 

select "codigo","nome" from IMPACOES@AEQUUS;

select * from ALVARA@CONTABILIS;

select "nu_cnpj","sq_produto","nm_produto" from PRODUTO@FINANCIAL;

 

Note: If any SQL Server database has tables with the varchar(max) data type, we must create views to consume this data, because the varchar(max) data type is problematic with dblink, I could not get around this problem,
this way I created a view, see the example below:

 

CREATE VIEW VEW_IMPACOES

AS

SELECT codigo,CONVERT(VARCHAR(2000),nome) nome FROM IMPACOES;

 

 

 
César Carvalho – DBA
Contact: https://twitter.com/Cesar_DBA
https://sgbdbrasil.wordpress.com/

Applying ExaCC DomU patch
Category: Engineer System Author: César Carvalho Date: 2 years ago Comments: 0

Applying ExaCC DomU patch

Always read Oracle technical notes before applying any patches.

 

Technical References
DomU upgrades and cloud tooling

 

#### We will apply the patch in a RAC environment. ####
#### Performing Oracle Home backup of RAC nodes. ####
[oracle@srv01 ~]$ echo $ORACLE_HOME
/u02/app/oracle/product/11.2.0/dbhome_2

#### Node 1 ####
[root@srv01 ~]$ tar -pcvf /backup/cesar_update/oracle_home_srv01.tar dbhome_2
[root@srv01 ~]$ cd /u01/app/
[root@srv01 ~]$ tar -pcvf /backup/cesar_update/srv01_oraInventory.tar oraInventory

#### Node 2 ####
[root@srv02 ~]$ tar -pcvf /backup/cesar_update/oracle_home_srv02.tar dbhome_2
[root@srv02 ~]$ cd /u01/app/
[root@srv02 ~]$ tar -pcvf /backup/cesar_update/srv02_oraInventory.tar oraInventory

#### Checking RAC nodes Status ####
[oracle@srv01 ~]$ srvctl status database -d DBPROD
Instance DBPROD1 is running on node srv01
Instance DBPROD2 is running on node srv02

#### Checking version of dbaastools installed ####
[root@srv01 11.2.0]# rpm -qa|grep -i dbaastools
dbaastools_exa-1.0-1+19.1.1.1.0_211221.1316.x86_64

[root@srv01 11.2.0]# dbaascli patch tools list
DBAAS CLI version 19.1.1.1.0
Executing command patch tools list

[root@srv02 11.2.0]# rpm -qa|grep -i dbaastools
dbaastools_exa-1.0-1+19.1.1.1.0_211221.1316.x86_64

[root@srv02 11.2.0]# dbaascli patch tools list
DBAAS CLI version 19.1.1.1.0
Executing command patch tools list

#### Check if the patch download url is the same on all nodes ####
[root@srv01 exapatch]# cat /var/opt/oracle/exapatch/exadbcpatch.cfg |grep oss_container_url
[root@srv02 exapatch]# cat /var/opt/oracle/exapatch/exadbcpatch.cfg |grep oss_container_url

#### Check if the url is accessible ####
[root@srv01 exapatch]# curl -v -O URL GERADA ACIMA
[root@srv02 exapatch]# curl -v -O URL GERADA ACIMA

#### Export environment variables and check available patches to be applied ####
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_2
export PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID=DBPROD1
echo $ORACLE_SID

export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_2
export PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID=DBPROD2
echo $ORACLE_SID

[root@srv02 ~]# dbaascli patch db list --oh srv02:/u02/app/oracle/product/11.2.0/dbhome_2
DBAAS CLI version 19.1.1.1.0
Executing command patch db list --oh srv02:/u02/app/oracle/product/11.2.0/dbhome_2
INFO : EXACS patching

Available Patches
patchid :26610265 (DB 11.2.0.4.170814 QUARTERLY DATABASE PATCH FOR EXADATA - Aug 2017)
patchid :26635694 (DB 11.2.0.4.171017 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2017)
patchid :27011043 (DB 11.2.0.4.180116 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2018)
patchid :27475722 (DB 11.2.0.4.180417 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2018)
patchid :27980213 (DB 11.2.0.4.180717 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2018)
patchid :28462975 (DB 11.2.0.4.181016 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2018)
patchid :28833571 (DB 11.2.0.4.190115 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2019)
patchid :29257245 (DB 11.2.0.4.190416 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2019)
patchid :29698813 (DB 11.2.0.4.190716 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2019)
patchid :30070157 (DB 11.2.0.4.191015 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2019)
patchid :30501894 (DB 11.2.0.4.200114 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2020)
patchid :30805507 (DB 11.2.0.4.200414 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2020)
patchid :31220011 (DB 11.2.0.4.200714 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2020)
patchid :31718644 (DB 11.2.0.4.201020 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2020)
patchid :32131241 (DB 11.2.0.4.210119 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2021)
patchid :32537095 (DB 11.2.0.4.210420 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2021)
patchid :32917411 (DB 11.2.0.4.210720 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2021)
patchid :33248386 (DB 11.2.0.4.211019 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2021)
patchid :33575241 (DB 11.2.0.4.220118 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2022)
Install database patch using
dbaascli patch db apply --patchid 33575241 --dbnames <>

[root@srv01 ~]# dbaascli patch db list --oh srv01:/u02/app/oracle/product/11.2.0/dbhome_2
DBAAS CLI version 19.1.1.1.0
Executing command patch db list --oh srv01:/u02/app/oracle/product/11.2.0/dbhome_2
INFO : EXACS patching

Available Patches
patchid :26610265 (DB 11.2.0.4.170814 QUARTERLY DATABASE PATCH FOR EXADATA - Aug 2017)
patchid :26635694 (DB 11.2.0.4.171017 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2017)
patchid :27011043 (DB 11.2.0.4.180116 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2018)
patchid :27475722 (DB 11.2.0.4.180417 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2018)
patchid :27980213 (DB 11.2.0.4.180717 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2018)
patchid :28462975 (DB 11.2.0.4.181016 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2018)
patchid :28833571 (DB 11.2.0.4.190115 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2019)
patchid :29257245 (DB 11.2.0.4.190416 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2019)
patchid :29698813 (DB 11.2.0.4.190716 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2019)
patchid :30070157 (DB 11.2.0.4.191015 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2019)
patchid :30501894 (DB 11.2.0.4.200114 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2020)
patchid :30805507 (DB 11.2.0.4.200414 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2020)
patchid :31220011 (DB 11.2.0.4.200714 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2020)
patchid :31718644 (DB 11.2.0.4.201020 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2020)
patchid :32131241 (DB 11.2.0.4.210119 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2021)
patchid :32537095 (DB 11.2.0.4.210420 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2021)
patchid :32917411 (DB 11.2.0.4.210720 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2021)
patchid :33248386 (DB 11.2.0.4.211019 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2021)
patchid :33575241 (DB 11.2.0.4.220118 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2022)
Install database patch using
dbaascli patch db apply --patchid 33575241 --dbnames <>

#### Precheck all nodes before patchid 33575241 ####
[root@srv01 ~]$ dbaascli patch db prereq --patchid 33575241 --instance1 srv01:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD -alldbs
[root@srv02 ~]$ dbaascli patch db prereq --patchid 33575241 --instance1 srv02:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD -alldbs

#### Last lines of the log informing that it was executed successfully ####
INFO: status of slave txn###: Precheck completed on srv01
INFO: -precheck_async completed on srv01:/u02/app/oracle/product/11.2.0/dbhome_2
INFO: Successfully released ohome lock. Proceeding to release local provisioning lock
INFO: Successfully released local provisioning lock
INFO: -precheck_async completed on all nodes

INFO: status of slave txn###: Precheck completed on srv02
INFO: -precheck_async completed on srv02:/u02/app/oracle/product/11.2.0/dbhome_2
INFO: Successfully released ohome lock. Proceeding to release local provisioning lock
INFO: Successfully released local provisioning lock
INFO: -precheck_async completed on all nodes

#### Apply patchid 33575241 on node srv02 ####
[root@srv02 ~]$ nohup dbaascli patch db apply --patchid 33575241 --instance1 srv02:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD --run_datasql 0 &

#### Tracking patch application logs ####
[root@srv02 ~]$ tail -f /var/opt/oracle/log/exadbcpatch/exadbcpatch.log
2022-05-19 18:07:00.950618 - Instance check cleared for node srv02 w.r.t nodelist
2022-05-19 18:07:00.950760 - INFO: deleting patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-19 18:07:00.958084 - INFO: deleted patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-19 18:07:00.958209 -
INFO: initpatch being run for post ecs patching
2022-05-19 18:07:00.958467 - Output from cmd /var/opt/oracle/misc/initpatch.pl ecsbppost run on localhost is:
INFO : No patch needed
2022-05-19 18:07:01.510654 - cmd took 0.551481008529663 seconds
2022-05-19 18:07:01.510964 - ##### INFO: Exadbcpatch completed successfully #####

#### Apply patchid 33575241 on node srv01 ####
[root@srv02 ~]$ nohup dbaascli patch db apply --patchid 33575241 --instance1 srv01:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD --run_datasql 1 &

#### Tracking patch application logs ####
[root@srv01 ~]$ tail -f /var/opt/oracle/log/exadbcpatch/exadbcpatch.log

2022-05-20 09:47:27.879789 - Instance check cleared for node srv01 w.r.t nodelist
2022-05-20 09:47:27.880011 - INFO: deleting patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-20 09:47:27.891809 - INFO: deleted patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-20 09:47:27.892030 -
INFO: initpatch being run for post ecs patching
2022-05-20 09:47:27.893285 - Output from cmd /var/opt/oracle/misc/initpatch.pl ecsbppost run on localhost is:
INFO : No patch needed
2022-05-20 09:47:28.447038 - cmd took 0.553269147872925 seconds
2022-05-20 09:47:28.447205 - ##### INFO: Exadbcpatch completed successfully #####
César Carvalho – DBA
Contact: https://twitter.com/Cesar_DBA
https://sgbdbrasil.wordpress.com/

Applying ExaCC DomU patch dataguard
Category: Engineer System Author: César Carvalho Date: 2 years ago Comments: 0

Applying ExaCC DomU patch dataguard

 
 
Always read Oracle technical notes before applying any patches.
 
Technical References
DomU upgrades and cloud tooling
#### We will apply the patch in a RAC environment. ####
#### Performing Oracle Home backup of RAC nodes. ####
[oracle@srv01 ~]$ echo $ORACLE_HOME
/u02/app/oracle/product/11.2.0/dbhome_2

#### Node 1 ####
[root@srv01 ~]$ tar -pcvf /backup/cesar_update/oracle_home_srv01.tar dbhome_2
[root@srv01 ~]$ cd /u01/app/
[root@srv01 ~]$ tar -pcvf /backup/cesar_update/srv01_oraInventory.tar oraInventory

#### Node 2 ####
[root@srv02 ~]$ tar -pcvf /backup/cesar_update/oracle_home_srv02.tar dbhome_2
[root@srv02 ~]$ cd /u01/app/
[root@srv02 ~]$ tar -pcvf /backup/cesar_update/srv02_oraInventory.tar oraInventory

#### Checking RAC nodes Status ####
[oracle@srv01 ~]$ srvctl status database -d DBPROD
Instance DBPROD1 is running on node srv01
Instance DBPROD2 is running on node srv02

#### Checking version of dbaastools installed ####
[root@srv01 11.2.0]# rpm -qa|grep -i dbaastools
dbaastools_exa-1.0-1+19.1.1.1.0_211221.1316.x86_64

[root@srv01 11.2.0]# dbaascli patch tools list
DBAAS CLI version 19.1.1.1.0
Executing command patch tools list

[root@srv02 11.2.0]# rpm -qa|grep -i dbaastools
dbaastools_exa-1.0-1+19.1.1.1.0_211221.1316.x86_64

[root@srv02 11.2.0]# dbaascli patch tools list
DBAAS CLI version 19.1.1.1.0
Executing command patch tools list

#### Check if the patch download url is the same on all nodes ####
[root@srv01 exapatch]# cat /var/opt/oracle/exapatch/exadbcpatch.cfg |grep oss_container_url
[root@srv02 exapatch]# cat /var/opt/oracle/exapatch/exadbcpatch.cfg |grep oss_container_url

#### Check if the url is accessible ####
[root@srv01 exapatch]# curl -v -O URL GERADA ACIMA
[root@srv02 exapatch]# curl -v -O URL GERADA ACIMA

#### Export environment variables and check available patches to be applied ####
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_2
export PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID=DBPROD1
echo $ORACLE_SID

export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_2
export PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID=DBPROD2
echo $ORACLE_SID

[root@srv02 ~]# dbaascli patch db list --oh srv02:/u02/app/oracle/product/11.2.0/dbhome_2
DBAAS CLI version 19.1.1.1.0
Executing command patch db list --oh srv02:/u02/app/oracle/product/11.2.0/dbhome_2
INFO : EXACS patching

Available Patches
patchid :26610265 (DB 11.2.0.4.170814 QUARTERLY DATABASE PATCH FOR EXADATA - Aug 2017)
patchid :26635694 (DB 11.2.0.4.171017 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2017)
patchid :27011043 (DB 11.2.0.4.180116 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2018)
patchid :27475722 (DB 11.2.0.4.180417 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2018)
patchid :27980213 (DB 11.2.0.4.180717 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2018)
patchid :28462975 (DB 11.2.0.4.181016 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2018)
patchid :28833571 (DB 11.2.0.4.190115 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2019)
patchid :29257245 (DB 11.2.0.4.190416 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2019)
patchid :29698813 (DB 11.2.0.4.190716 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2019)
patchid :30070157 (DB 11.2.0.4.191015 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2019)
patchid :30501894 (DB 11.2.0.4.200114 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2020)
patchid :30805507 (DB 11.2.0.4.200414 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2020)
patchid :31220011 (DB 11.2.0.4.200714 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2020)
patchid :31718644 (DB 11.2.0.4.201020 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2020)
patchid :32131241 (DB 11.2.0.4.210119 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2021)
patchid :32537095 (DB 11.2.0.4.210420 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2021)
patchid :32917411 (DB 11.2.0.4.210720 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2021)
patchid :33248386 (DB 11.2.0.4.211019 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2021)
patchid :33575241 (DB 11.2.0.4.220118 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2022)
Install database patch using
dbaascli patch db apply --patchid 33575241 --dbnames <>

[root@srv01 ~]# dbaascli patch db list --oh srv01:/u02/app/oracle/product/11.2.0/dbhome_2
DBAAS CLI version 19.1.1.1.0
Executing command patch db list --oh srv01:/u02/app/oracle/product/11.2.0/dbhome_2
INFO : EXACS patching

Available Patches
patchid :26610265 (DB 11.2.0.4.170814 QUARTERLY DATABASE PATCH FOR EXADATA - Aug 2017)
patchid :26635694 (DB 11.2.0.4.171017 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2017)
patchid :27011043 (DB 11.2.0.4.180116 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2018)
patchid :27475722 (DB 11.2.0.4.180417 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2018)
patchid :27980213 (DB 11.2.0.4.180717 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2018)
patchid :28462975 (DB 11.2.0.4.181016 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2018)
patchid :28833571 (DB 11.2.0.4.190115 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2019)
patchid :29257245 (DB 11.2.0.4.190416 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2019)
patchid :29698813 (DB 11.2.0.4.190716 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2019)
patchid :30070157 (DB 11.2.0.4.191015 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2019)
patchid :30501894 (DB 11.2.0.4.200114 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2020)
patchid :30805507 (DB 11.2.0.4.200414 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2020)
patchid :31220011 (DB 11.2.0.4.200714 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2020)
patchid :31718644 (DB 11.2.0.4.201020 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2020)
patchid :32131241 (DB 11.2.0.4.210119 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2021)
patchid :32537095 (DB 11.2.0.4.210420 QUARTERLY DATABASE PATCH FOR EXADATA - Apr 2021)
patchid :32917411 (DB 11.2.0.4.210720 QUARTERLY DATABASE PATCH FOR EXADATA - Jul 2021)
patchid :33248386 (DB 11.2.0.4.211019 QUARTERLY DATABASE PATCH FOR EXADATA - Oct 2021)
patchid :33575241 (DB 11.2.0.4.220118 QUARTERLY DATABASE PATCH FOR EXADATA - Jan 2022)
Install database patch using
dbaascli patch db apply --patchid 33575241 --dbnames <>

#### Precheck all nodes before patchid 33575241 ####
[root@srv01 ~]$ dbaascli patch db prereq --patchid 33575241 --instance1 srv01:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD -alldbs
[root@srv02 ~]$ dbaascli patch db prereq --patchid 33575241 --instance1 srv02:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD -alldbs

#### Last lines of the log informing that it was executed successfully ####
INFO: status of slave txn###: Precheck completed on srv01
INFO: -precheck_async completed on srv01:/u02/app/oracle/product/11.2.0/dbhome_2
INFO: Successfully released ohome lock. Proceeding to release local provisioning lock
INFO: Successfully released local provisioning lock
INFO: -precheck_async completed on all nodes

INFO: status of slave txn###: Precheck completed on srv02
INFO: -precheck_async completed on srv02:/u02/app/oracle/product/11.2.0/dbhome_2
INFO: Successfully released ohome lock. Proceeding to release local provisioning lock
INFO: Successfully released local provisioning lock
INFO: -precheck_async completed on all nodes

################### DATAGUARD ###################
#### Apply patchid 33575241 on node srv02 Dataguard ####
[root@srv02 ~]$ nohup dbaascli patch db apply --patchid 33575241 --instance1 srv02:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD --run_datasql 0 &

#### Tracking patch application logs ####
[root@srv02 ~]$ tail -f /var/opt/oracle/log/exadbcpatch/exadbcpatch.log
2022-05-19 18:07:00.950618 - Instance check cleared for node srv02 w.r.t nodelist
2022-05-19 18:07:00.950760 - INFO: deleting patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-19 18:07:00.958084 - INFO: deleted patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-19 18:07:00.958209 -
INFO: initpatch being run for post ecs patching
2022-05-19 18:07:00.958467 - Output from cmd /var/opt/oracle/misc/initpatch.pl ecsbppost run on localhost is:
INFO : No patch needed
2022-05-19 18:07:01.510654 - cmd took 0.551481008529663 seconds
2022-05-19 18:07:01.510964 - ##### INFO: Exadbcpatch completed successfully #####

#### Apply patchid 33575241 on node srv01 Dataguard ####
[root@srv02 ~]$ nohup dbaascli patch db apply --patchid 33575241 --instance1 srv01:/u02/app/oracle/product/11.2.0/dbhome_2 --dbnames DBPROD --run_datasql 0 &

#### Tracking patch application logs ####
[root@srv01 ~]$ tail -f /var/opt/oracle/log/exadbcpatch/exadbcpatch.log

2022-05-20 09:47:27.879789 - Instance check cleared for node srv01 w.r.t nodelist
2022-05-20 09:47:27.880011 - INFO: deleting patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-20 09:47:27.891809 - INFO: deleted patching_progress, patched_ohome, patched_ohome_name from creg
2022-05-20 09:47:27.892030 -
INFO: initpatch being run for post ecs patching
2022-05-20 09:47:27.893285 - Output from cmd /var/opt/oracle/misc/initpatch.pl ecsbppost run on localhost is:
INFO : No patch needed
2022-05-20 09:47:28.447038 - cmd took 0.553269147872925 seconds
2022-05-20 09:47:28.447205 - ##### INFO: Exadbcpatch completed successfully #####

César Carvalho – DBA
Contact: https://twitter.com/Cesar_DBA
https://sgbdbrasil.wordpress.com/

LuxOUG – 2022
Category: Cloud,Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 2 years ago Comments: 0

LuxOUG - EMEA Community Tour 2022

 
 
As part of EMEA Community Tour 2022 , we invite everyone to attend our first face-to-face event in Luxembourg.

 

 

 

We will hold our first event in Luxembourg, covering various technologies such as DevOps, Engineered System, Middleware, Cloud and others.
Location: Luxembourg
Date: 05/10/2022
Event Schedule: 9:00 AM – 18:00 PM (CEST)
Atrium Business Park
Address:  25 Rue du puits Romain, 8070 Bertrange.
 
How to reach the event and some additional information about using public transportation here in Luxembourg.  Click here
 

 Register for the event :  Click here

Event Schedule



Presentation slides



Event Photos




Sponsor


1 2 3 32