Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 8 hours 45 min ago

Back With DataGuard Bad Practice

Tue, 2024-03-05 19:39

OS authetication is typically bad practice. In my case, it was HELL.

Back to retirement?

The Beginning Of The End

Sat, 2024-02-10 20:05

Started blogging June 19, 2007.

Thank you for reading!

No better way to end it than the year of Dragon [Wood] (Chinese Horoscope).

Be well; be kind; be happy.

Extract DB User Using OEM

Thu, 2024-02-08 06:43

I know what you are probably thinking.

Number of lines from Create User and GRANTS.

$ wc -l *.sql
1310 CRYSTAL.sql
1383 TMTRCK.sql

SRDC – Collect Data Guard Diagnostic Information (Doc ID 2219763.1)

Sun, 2024-01-28 09:06

Been in DataGuard HELL and Doc ID 2219763.1 is a good starting point for troubleshooting.

Many SR’s opened without any success. Oracle suppport suggest to recreate controlfile – YIKES!

Basically, RMAN backup is being performed from standby.

From primary RMAN deletes archivelog to prevent fill up.

My unnderstanding, if archivelogs are in FRA, then cleanup shoud be automatic provided there are no BUGS.

Solution for QA – RMAN delete archivelogs without connecting to catalog at primary

and RMAN backups connecting to catalog at standby. Not sure if there are any repercussions.

RMAN Archivelog Deletion Errors – 12.1.0.2.0 – Script below seems overly complicated.

Any suggestions or ideas? TIA.

16> connect target *
17> connect catalog *
18>
19> crosscheck archivelog all;
20> delete noprompt archivelog until time 'sysdate-4/24';
21> list archivelog all;
22>
23> run {
24> allocate channel c1 device type disk;
25> SQL "alter database backup controlfile to trace as
25> ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
26> SQL "create pfile=''/tmp/init@.ora'' from spfile";
27> release channel c1;
28> }
29> show all;
30> crosscheck archivelog all;
31> crosscheck backup;
32> delete noprompt obsolete;
33> delete noprompt expired backup;
34> resync catalog from db_unique_name all;
35>

RMAN-06216: WARNING: db_unique_name mismatch - 7 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name SID_DR
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece c-1072717639-20240124-03
RMAN-06214: Backup Piece c-1072717639-20240124-05
RMAN-06214: Backup Piece c-1072717639-20240124-07
RMAN-06214: Backup Piece c-1072717639-20240125-01
RMAN-06214: Backup Piece c-1072717639-20240125-03
RMAN-06214: Backup Piece c-1072717639-20240125-05
RMAN-06214: Backup Piece c-1072717639-20240126-00

RMAN-06216: WARNING: db_unique_name mismatch - 6 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name SID_DR
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece c-1072717639-20240124-02
RMAN-06214: Backup Piece c-1072717639-20240125-00
RMAN-06214: Backup Piece c-1072717639-20240125-02
RMAN-06214: Backup Piece c-1072717639-20240125-04
RMAN-06214: Backup Piece c-1072717639-20240125-07
RMAN-06214: Backup Piece c-1072717639-20240126-01

RMAN-06207: WARNING: 6 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece c-1072717639-20240124-02
RMAN-06214: Backup Piece c-1072717639-20240124-05
RMAN-06214: Backup Piece c-1072717639-20240125-00
RMAN-06214: Backup Piece c-1072717639-20240125-02
RMAN-06214: Backup Piece c-1072717639-20240125-04
RMAN-06214: Backup Piece c-1072717639-20240125-07

RMAN-06207: WARNING: 6 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece c-1072717639-20240124-03
RMAN-06214: Backup Piece c-1072717639-20240124-07
RMAN-06214: Backup Piece c-1072717639-20240125-01
RMAN-06214: Backup Piece c-1072717639-20240125-03
RMAN-06214: Backup Piece c-1072717639-20240125-05
RMAN-06214: Backup Piece c-1072717639-20240126-00


Remove All Vagrant Boxes

Sat, 2024-01-27 13:27

I have been struggling with Vagrant Boxes for Microsoft Windows 11 Pro.

Destroy them all and start over again.

pegasus@Greek MINGW64 ~
$ vagrant box list | cut -f 1 -d ' ' | xargs -L 1 vagrant box remove -f
Removing box 'bento/ubuntu-18.04' (v202303.13.0) with provider 'virtualbox'...
Removing box 'bigdeal/mysql57' (v18.3.4) with provider 'virtualbox'...
Removing box 'centos/7' (v2004.01) with provider 'virtualbox'...
Removing box 'cosm/centos7_mysql5.6' (v5.6.51.20210213) with provider 'virtualbox'...
Removing box 'dtest/mysql-ce-5.6' (v0.1.0) with provider 'virtualbox'...
Removing box 'oraclebase/oracle-7' (v2023.10.18) with provider 'virtualbox'...
Removing box 'oraclelinux/7' (v7.9.517) with provider 'virtualbox'...
Removing box 'stevobengtson/vt-mysql-5.6' (v1.1) with provider 'virtualbox'...
Removing box 'thanh_lh/lam-php-5.3-mysql-5.6-centos-6.8-ecube-2-cfg' (v0.0.2) with provider 'virtualbox'...
Removing box 'ubuntu/trusty64' (v20190514.0.0) with provider 'virtualbox'...


pegasus@Greek MINGW64 ~
$ systeminfo
Host Name: GREEK
OS Name: Microsoft Windows 11 Pro
OS Version: 10.0.22631 N/A Build 22631
OS Manufacturer: Microsoft Corporation
OS Configuration: Standalone Workstation
OS Build Type: Multiprocessor Free
Registered Owner: pegasus
Registered Organization: N/A
Product ID: 00330-54193-28148-AAOEM
Original Install Date: Fri/6/9/2023, 7:23:01 PM
System Boot Time: Tue/1/9/2024, 7:03:46 PM
System Manufacturer: Micro Computer(HK) Tech Limited
System Model: Neptune series(i9)
System Type: x64-based PC
Processor(s): 1 Processor(s) Installed.
[01]: Intel64 Family 6 Model 154 Stepping 3 GenuineIntel ~2500 Mhz
BIOS Version: American Megatrends International, LLC. 1.0B, Tue/2/21/2023
Windows Directory: C:\WINDOWS
System Directory: C:\WINDOWS\system32
Boot Device: \Device\HarddiskVolume1
System Locale: en-us;English (United States)
Input Locale: en-us;English (United States)
Time Zone: (UTC-08:00) Pacific Time (US & Canada)
Total Physical Memory: 32,545 MB
Available Physical Memory: 19,213 MB
Virtual Memory: Max Size: 34,593 MB
Virtual Memory: Available: 19,965 MB
Virtual Memory: In Use: 14,628 MB
Page File Location(s): C:\pagefile.sys
Domain: WORKGROUP
Logon Server: \\GREEK
Hotfix(s): 4 Hotfix(s) Installed.
[01]: KB5033920
[02]: KB5027397
[03]: KB5034123
[04]: KB5032393
Network Card(s): 8 NIC(s) Installed.
[01]: Windscribe VPN
Connection Name: Local Area Connection
Status: Media disconnected
[02]: Windscribe Windtun420
Connection Name: Local Area Connection 2
Status: Media disconnected
[03]: Intel(R) Ethernet Controller (3) I225-V
Connection Name: Ethernet
DHCP Enabled: Yes
DHCP Server: 192.168.1.254
IP address(es)
[01]: 192.168.1.214
[02]: fe80::1a81:c46a:bf:55e5
[04]: Intel(R) Wi-Fi 6E AX210 160MHz
Connection Name: Wi-Fi
Status: Media disconnected
[05]: VirtualBox Host-Only Ethernet Adapter
Connection Name: Ethernet 2
DHCP Enabled: No
IP address(es)
[01]: 192.168.56.1
[02]: fe80::7fb6:5404:2b52:1346
[06]: VirtualBox Host-Only Ethernet Adapter
Connection Name: Ethernet 3
DHCP Enabled: No
IP address(es)
[01]: 192.168.100.1
[02]: fe80::f854:a563:d547:99f2
[07]: VirtualBox Host-Only Ethernet Adapter
Connection Name: Ethernet 4
DHCP Enabled: No
IP address(es)
[01]: 192.168.33.1
[02]: fe80::91b0:da47:2c8f:e543
[08]: WireGuard Tunnel
Connection Name: WindscribeWireguard
DHCP Enabled: No
IP address(es)
[01]: 100.73.168.139
Hyper-V Requirements:
A hypervisor has been detected.
Features required for Hyper-V will not be displayed.
pegasus@Greek MINGW64 ~
$

Using nfsstat To Find NFS Mounts

Tue, 2024-01-16 18:28

What’s so diffificult about finding NFS mount points?

Nothing except for when there are 23+ hosts with different OS flavors and versions.

12.1.0.2|RHEL 6.10
12.1.0.2|RHEL 7.5
11.2.0.4|AIX 7.1
12.2.0.1|AIX 7.2

Lucky for me, I was able to find nfsstat which worked for all the versions above.

Just run nfsstat -m to find all NFS mounts.

nfsstat

Purpose:
Displays statistical information ab1out the Network File System ( ) and Remote Procedure Call (RPC) calls.

Syntax:
/usr/sbin/nfsstat [ -@ WparName ] [ -c ] [ -d ] [ -s ] [ -n ] [ -r ] [ -m ] [ -4 ] [ -z ] [ -t] [-b] [ -g ]

Example: 12.1.0.2.0 (RHEL 7.5)
nfsstat -m
/tmp/Admin-Out from 172.31.250.60:/vol/vol4/Admin-Out

RAC Off – Relinking the RAC Option Worked

Wed, 2023-12-20 22:01

Starting RAC database with srvctl resulted with error below.
ORA-00439: feature not enabled: Real Application Clusters

Rac On / Rac Off – Relinking the RAC Option (Doc ID 211177.1)

RAC OFF
1. Login as the Oracle software owner and shutdown all database instances on all nodes in the cluster.
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk rac_off
If this step did not fail with fatal errors, proceed to step 4.
4. make -f ins_rdbms.mk ioracle
Note: If these steps fail with a Fatal Error, then you will need to open a Service Request and submit these errors to Oracle Support for analysis.

================================================================================
### STOP Database
================================================================================
[oracle@ol7-121-rac1 ~]$ srvctl stop database -d cdbrac
[oracle@ol7-121-rac1 ~]$ srvctl status database -d cdbrac -v
Instance cdbrac1 is not running on node ol7-121-rac1
Instance cdbrac2 is not running on node ol7-121-rac2
[oracle@ol7-121-rac1 ~]$

================================================================================
### make -f ins_rdbms.mk rac_off
================================================================================
[oracle@ol7-121-rac1 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@ol7-121-rac1 lib]$ make -f ins_rdbms.mk rac_off
rm -f /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libskgxp12.so
cp /u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libskgxpg.so /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libskgxp12.so
rm -f /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libskgxn2.so
cp /u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libskgxns.so \
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libskgxn2.so
/usr/bin/ar d /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a kcsm.o
/usr/bin/ar cr /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ksnkcs.o
[oracle@ol7-121-rac1 lib]$

================================================================================
### make -f ins_rdbms.mk ioracle
================================================================================
[oracle@ol7-121-rac1 lib]$ make -f ins_rdbms.mk ioracle
chmod 755 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin
- Linking Oracle
rm -f /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orald -o /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ -L/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/stubs/ -Wl,-E /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/nautab.o /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naeet.o /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naect.o /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naedhs.o /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/config.o -lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/u01/app/oracle/product/12.1.0.2/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.1.0.2/dbhome_1/lib -lm `cat /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/12.1.0.2/dbhome_1/lib
test ! -f /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle ||\
mv -f /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracleO
mv /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle
[oracle@ol7-121-rac1 lib]$

================================================================================
### srvctl config database -d cdbrac -v
================================================================================
[oracle@ol7-121-rac1 lib]$ srvctl config database -d cdbrac -v
Database unique name: cdbrac
Database name: cdbrac
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/CDBRAC/PARAMETERFILE/spfile.277.1155927597
Password file: +DATA/CDBRAC/PASSWORD/pwdcdbrac.257.1155927083
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: cdbrac1,cdbrac2
Configured nodes: ol7-121-rac1,ol7-121-rac2
Database is administrator managed
[oracle@ol7-121-rac1 lib]$

================================================================================
### srvctl start database -d cdbrac
================================================================================
[oracle@ol7-121-rac1 lib]$ srvctl start database -d cdbrac
PRCR-1079 : Failed to start resource ora.cdbrac.db

CRS-5017: The resource action "ora.cdbrac.db start" encountered the following error:
ORA-00439: feature not enabled: Real Application Clusters.

For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/ol7-121-rac1/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.cdbrac.db' on 'ol7-121-rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.cdbrac.db' on that would satisfy its placement policy
[oracle@ol7-121-rac1 lib]$

RAC OPTION OFF after upgrade to 12.1

Sun, 2023-12-17 12:03

Oh, The Irony!

Reference Doc ID 2494637.1 has all the details.

dba_registry shows OFF while database parameters show ON.

SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF


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

SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2


Rac On / Rac Off – Oracle Does Not Make It Easy

Sat, 2023-12-09 21:18

Working on task to convert RAC database to Single Instance database.

There are so many options and which to chose and is the best?

make -f ins_rdbms.mk rac_off and make -f ins_rdbms.mk ioracle was used.

Only issue is process ora_lmon_racdb1 is running.

Here are options and how to validate.

Rac On / Rac Off – Relinking the RAC Option (Doc ID 211177.1)

RAC OFF

  1. Login as the Oracle software owner and shutdown all database instances on all nodes in the cluster.
  2. cd $ORACLE_HOME/rdbms/lib
  3. make -f ins_rdbms.mk rac_off
    If this step did not fail with fatal errors, proceed to step 4.
  4. make -f ins_rdbms.mk ioracle

Note: If these steps fail with a Fatal Error, then you will need to open a Service Request and submit these errors to Oracle Support for analysis.

How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC (Doc ID 284785.1)

On Linux/UNIX except AIX:
ar -t $ORACLE_HOME/rdbms/lib/libknlopt.a|grep kcsm.o

To check whether a running instance is a RAC instance :

Multiple options here:

  1. Check sqlplus banner (Applicable to Windows):
    To check whether a running instance is a RAC instance :
    With the Partitioning, Real Application Clusters, OLAP and Data Mining options
  2. Check whether lmon background process exists for the instance
  3. Check cluster_database parameter
    SQL> show parameter cluster_database

Output “true” means it’s RAC instance but this is not reliable as a RAC instance may have cluster_database set to false during maintenance period.

Create MySQL In Vagrant Box

Sun, 2023-11-19 09:17

First, thank you to jazzfogPavel for https://github.com/jazzfog/MySql-in-Vagrant as it was straight forward.

Too tired and too lazy to explain all the steps; however, the output should be self explanatory.

pegasus@Greek MINGW64 /c/vagrant/MySql-in-Vagrant (master)
$ ls -al
ls -al
total 25
drwxr-xr-x 1 pegasus 197121    0 Nov 19 06:37 .
drwxr-xr-x 1 pegasus 197121    0 Nov 19 06:34 ..
drwxr-xr-x 1 pegasus 197121    0 Nov 19 06:34 .git
-rw-r--r-- 1 pegasus 197121   31 Nov 19 06:34 .gitignore
drwxr-xr-x 1 pegasus 197121    0 Nov 19 06:35 .vagrant
drwxr-xr-x 1 pegasus 197121    0 Nov 19 06:37 MysqlData
-rw-r--r-- 1 pegasus 197121  635 Nov 19 06:34 Vagrantfile
drwxr-xr-x 1 pegasus 197121    0 Nov 19 06:34 provision
-rw-r--r-- 1 pegasus 197121 3010 Nov 19 06:34 readme.md

pegasus@Greek MINGW64 /c/vagrant/MySql-in-Vagrant (master)
$

======================================================================

pegasus@Greek MINGW64 /c/vagrant
$ git clone https://github.com/jazzfog/MySql-in-Vagrant
Cloning into 'MySql-in-Vagrant'...
remote: Enumerating objects: 32, done.
remote: Total 32 (delta 0), reused 0 (delta 0), pack-reused 32
Receiving objects: 100% (32/32), 5.32 KiB | 5.32 MiB/s, done.
Resolving deltas: 100% (12/12), done.

pegasus@Greek MINGW64 /c/vagrant
$ ls
MySql-in-Vagrant/  vagrant/  vagrant-projects/

pegasus@Greek MINGW64 /c/vagrant
$ cd MySql-in-Vagrant/

pegasus@Greek MINGW64 /c/vagrant/MySql-in-Vagrant (master)
$ ls
Vagrantfile  provision/  readme.md

pegasus@Greek MINGW64 /c/vagrant/MySql-in-Vagrant (master)
$ vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Importing base box 'ubuntu/trusty64'...

vagrant@vagrant-ubuntu-trusty-64:~$ mysql -V
mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

vagrant@vagrant-ubuntu-trusty-64:~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.62-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT version();
+-------------------------+
| version()               |
+-------------------------+
| 5.5.62-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> STATUS;
--------------
mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

Connection id:          37
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.62-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 5 min 13 sec

Threads: 1  Questions: 113  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.361
--------------

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.5.62                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.62-0ubuntu0.14.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
7 rows in set (0.00 sec)

mysql>

Modify Connection For Oracle Database Gateway To SQL Server

Sun, 2023-10-29 07:48

Google did not help me on this one and had to RTFM. Hope this helps you.

During installation, connection to SQL Server was defined; now, another SQL Server should be used instead.

Need to modify initdg4msql.ora where $ORACLE_HOME is the Gateway Home.

db01-oracle:/home/oracle$ cat $ORACLE_HOME/dg4msql/admin/initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
# Michael Dinh : Oct 25, 2023
# HS_FDS_CONNECT_INFO=[VMXXENTPOCD01]:1434//TEST_BACKUP
HS_FDS_CONNECT_INFO=[AVSFHEQDDB01]:1433//LATEST
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

db01-oracle:/home/oracle$

Create database link from Oracle to SQLServer database.

SQL> CREATE PUBLIC DATABASE LINK SQLSERVER1 CONNECT TO TESTADMIN IDENTIFIED BY "latest123" USING 'dg4msql';

Database link created.

SQL> select * from LA@SQLSERVER1;
select * from LA@SQLSERVER1
              *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The SELECT permission was denied on the object 'LA', database
'LaTest', schema 'dbo'. {42000,NativeErr = 229}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The metadata
could not be determined because every code path results in an error; see previous errors for some of these. {HY000,NativeErr =
11529}
ORA-02063: preceding 2 lines from SQLSERVER1
SQL>

For SQLServer, provided dbreader role to TestAdmin. I am not aware of all the privleges as another team is managing SQLServer.

SQL> col name for a15
SQL> col address  for a15
SQL> col zip for a15
SQL> select * from LA@SQLSERVER1;

Name            Address         Zip
--------------- --------------- ---------------
Michael         Address A       123-4567
Clyde           Address B       345-6789
Mahesh          Address C       456-6789

SQL>

Troubleshooting Oracle Database Gateway Installation : INS -07003 Unexpected Error Occured While Accessing The Bean Store.

Sat, 2023-10-21 09:45

Installing 11.2.0.4 Oracle Database Gateway for Database 11.2.0.4 failed with Error In Invoking Target ‘idg4msql’ Of Makefile $OH/rdbms/lib/ins_rdbms.mk’.

Create SR and oracle support advise to use 12cR2 version.

Installing 12cR2 Oracle Database Gateway for Database 11.2.0.4 failed with [INS-07003] Unexpected error occurred while accessing the bean store.

Finally was able to get pass the error and hopefully no further issues.

The final trick is export PS1=”\u@\h:\${ORACLE_SID}:\${PWD}\n$ “

oracle@db01::/home/oracle
$ source unset_vars.sh
oracle@db01::/home/oracle
$ cd /u01/app/oracle/gateways
oracle@db01::/u01/app/oracle/gateways
$ ./runInstaller
Starting Oracle Universal Installer…

Checking Temp space: must be greater than 415 MB. Actual 4967 MB Passed
Checking swap space: must be greater than 150 MB. Actual 16095 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 1 6777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2023-10-21_08 -34-22AM. Please wait …
oracle@db01::/u01/app/oracle/gateways
$

Basically, the enviroment has way too many variables which needed to be unset before excuting runInstaller

oracle@db01::/home/oracle
$ cat unset_vars.sh
unset cyan
unset smul
unset magenta
unset smso
unset reset
unset rmso
unset rmul
unset dim
unset white
unset green
unset black
unset yellow
unset red
unset bold
unset blue
unset rev
unset white
unset green
unset black
unset NLS_LANG
unset LS_COLORS
unset NLS_DATE_FORMAT
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "
oracle@db01::/home/oracle
$

Drop RAC Database Using RMAN

Sun, 2023-10-01 11:09

As part of data center consolidation, some databases are no longer required and need to be dropped.

I have previouly posted about dropping databases; however, it was single instance vs RAC.

### Check status of database:
db01-oracle:/home/oracle$ srvctl status database -d loadtest -v
Database loadtest is running. Instance status: Open.
db01-oracle:/home/oracle$

### Modify cluster database parameters:
SQL> show parameter cluster_database
NAME                       TYPE         VALUE
-------------------------  ----------- ------------
cluster_database boolean   FALSE
cluster_database_instances integer      1

SQL> show parameter db_name
NAME                       TYPE         VALUE
-------------------------  ----------- ------------
db_name                    string      loadtest

### Startup force mount restrict:
SQL> startup force mount restrict;
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size                  3795712 bytes
Variable Size            1.2885E+10 bytes
Database Buffers         8522825728 bytes
Redo Buffers               63311872 bytes
Database mounted.

!!! Verify logins=RESTRICTED and parallel=NO !!!
SQL> select logins, parallel from v$instance;
LOGINS     PAR
---------- ---
RESTRICTED NO

SQL>

### RMAN: drop database including backups ###
/home/oracle$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 25 11:41:45 2023

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

connected to target database: loadtest (DBID=983159180, not open)

RMAN> drop database including backups;

database name is "loadtest" and DBID is 983159180

Do you really want to drop all backups and the database (enter YES or NO)?

nected to target database: loadtest (DBID=983159180, not open)

RMAN> drop database including backups;

database name is "loadtest" and DBID is 983159180

Do you really want to drop all backups and the database (enter YES or NO)? yes

using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=101 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 7.1.3.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=148 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 7.1.3.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=248 device type=DISK

..............................

deleted archived log
archived log file name=+ORFLASHBK/loadtest/ARCHIVELOG/2023_09_22/thread_1_seq_150008.8873.1148227803 RECID=150008 STAMP=1148227802
Deleted 8 objects

..............................

database name is "loadtest" and DBID is 983159180
database dropped

RMAN>
How To Drop A RAC Database Using RMAN(Doc ID 732042.1)

SQLServer and Leak

Sat, 2023-09-09 14:38

What’s easier? A bad install will always kill you.

options_packs_usage_statistics.sql

Tue, 2023-09-05 22:40
I have been working on project identify DB FEATURE USAGE.

Luckily there's a script for that and happy auditing.

Database Options/Management Packs Usage Reporting for Oracle Databases 11.2 and later (Doc ID 1317265.1)	

Here is an example and search for TRUE to find usage.

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> grant dba to mdinh identified by mdinh;

Grant succeeded.

SQL> CREATE TABLE test_tbl_oltp COMPRESS FOR OLTP
  2  AS
  3  SELECT * FROM dba_objects;

Table created.

SQL> insert into test_tbl_oltp SELECT * FROM dba_objects;
SQL> commit;

SQL> start options_packs_usage_statistics.sql
OVERALL INFORMATION

HOST_NAME                               |INSTANCE_NAME   |DATABASE_NAME |OPEN_MODE       |DATABASE_ROLE   |CREATED            |      DBID|VERSION    |BANNER
----------------------------------------|----------------|--------------|----------------|----------------|-------------------|----------|-----------|--------------------------------------------------------------------------------
GREEK                                   |orcl            |ORCL          |READ WRITE      |PRIMARY         |2023.09.04_18.35.53|1674462089|12.1.0.2.0 |Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PARAMETER                     |VALUE
------------------------------|--------------------
control_management_pack_access|DIAGNOSTIC+TUNING
enable_ddl_logging            |FALSE


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MULTITENANT INFORMATION (Please ignore errors in pre 12.1 databases)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CON_ID|NAME                          |OPEN_MODE       |RESTRICTED|REMARKS
------|------------------------------|----------------|----------|-----------------------------------------------------------------------------
     0|orcl                          |READ WRITE      |NO        |

The multitenant architecture with one user-created pluggable database (single tenant) is available in all editions without the Multitenant Option.
If more than one PDB containers are created, then Multitenant Option licensing is needed
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>> Selecting from DBA_FEATURE_USAGE_STATISTICS


DBA_FEATURE_USAGE_STATISTICS (DBA_FUS) INFORMATION - MOST RECENT SAMPLE BASED ON LAST_SAMPLE_DATE

LAST_DBA_FUS_DBID|LAST_DBA_FUS_VERS|LAST_DBA_FUS_SAMPLE|SYSDATE            |REMARKS
-----------------|-----------------|-------------------|-------------------|-----------------
       1674462089|12.1.0.2.0       |2023.09.05_00.39.38|2023.09.05_19.54.30|


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PRODUCT USAGE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PRODUCT                                            |USAGE                   |LAST_SAMPLE_DATE   |FIRST_USAGE_DATE   |LAST_USAGE_DATE
---------------------------------------------------|------------------------|-------------------|-------------------|-------------------
Active Data Guard                                  |NO_USAGE                |2023.09.05_00.39.38|                   |
Advanced Analytics                                 |NO_USAGE                |2023.09.05_00.39.38|                   |
Advanced Compression                               |NO_USAGE                |2023.09.05_00.39.38|                   |
Advanced Security                                  |NO_USAGE                |2023.09.05_00.39.38|                   |
Database In-Memory                                 |NO_USAGE                |2023.09.05_00.39.38|                   |
Database Vault                                     |NO_USAGE                |2023.09.05_00.39.38|                   |
Diagnostics Pack                                   |NO_USAGE                |2023.09.05_00.39.38|                   |
Label Security                                     |NO_USAGE                |2023.09.05_00.39.38|                   |
OLAP                                               |NO_USAGE                |2023.09.05_00.39.38|                   |
Partitioning                                       |NO_USAGE                |2023.09.05_00.39.38|                   |
RAC or RAC One Node                                |NO_USAGE                |2023.09.05_00.39.38|                   |
Real Application Clusters                          |NO_USAGE                |2023.09.05_00.39.38|                   |
Real Application Clusters One Node                 |NO_USAGE                |2023.09.05_00.39.38|                   |
Real Application Testing                           |NO_USAGE                |2023.09.05_00.39.38|                   |
Spatial and Graph                                  |NO_USAGE                |2023.09.05_00.39.38|                   |
Tuning Pack                                        |NO_USAGE                |2023.09.05_00.39.38|                   |
.Database Gateway                                  |NO_USAGE                |2023.09.05_00.39.38|                   |
.Exadata                                           |NO_USAGE                |2023.09.05_00.39.38|                   |
.GoldenGate                                        |NO_USAGE                |2023.09.05_00.39.38|                   |
.HW                                                |NO_USAGE                |2023.09.05_00.39.38|                   |
.Pillar Storage                                    |NO_USAGE                |2023.09.05_00.39.38|                   |


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
FEATURE USAGE DETAILS
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PRODUCT                                            |FEATURE_BEING_USED                                      |USAGE                   |LAST_SAMPLE_DATE   |      DBID|VERSION    |DETECTED_USAGES|TOTAL_SAMPLES|CURRENTLY_USED|FIRST_USAGE_DATE   |LAST_USAGE_DATE    |EXTRA_FEATURE_INFO
---------------------------------------------------|--------------------------------------------------------|------------------------|-------------------|----------|-----------|---------------|-------------|--------------|-------------------|-------------------|--------------------------------------------------------------------------------
Active Data Guard                                  |Active Data Guard - Real-Time Query on Physical Standby |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Active Data Guard                                  |Global Data Services                                    |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Analytics                                 |Data Mining                                             |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Advanced Index Compression                              |SUPPRESSED_DUE_TO_BUG   |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              1|            1|TRUE          |2023.09.05_00.39.38|2023.09.05_00.39.38|
Advanced Compression                               |Backup HIGH Compression                                 |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Backup LOW Compression                                  |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Backup MEDIUM Compression                               |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Backup ZLIB Compression                                 |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Data Guard                                              |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |HeapCompression                                         |SUPPRESSED_DUE_TO_BUG   |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              1|            1|TRUE          |2023.09.05_00.39.38|2023.09.05_00.39.38|
Advanced Compression                               |Heat Map                                                |SUPPRESSED_DUE_TO_BUG   |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Hybrid Columnar Compression Row Level Locking           |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Information Lifecycle Management                        |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Oracle Advanced Network Compression Service             |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Oracle Utility Datapump (Export)                        |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |Oracle Utility Datapump (Import)                        |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              1|            1|TRUE          |2023.09.05_00.39.38|2023.09.05_00.39.38|
Advanced Compression                               |SecureFile Compression (user)                           |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Compression                               |SecureFile Deduplication (user)                         |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Security                                  |Data Redaction                                          |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Security                                  |Encrypted Tablespaces                                   |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Security                                  |Oracle Utility Datapump (Export)                        |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Security                                  |Oracle Utility Datapump (Import)                        |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              1|            1|TRUE          |2023.09.05_00.39.38|2023.09.05_00.39.38|
Advanced Security                                  |SecureFile Encryption (user)                            |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Advanced Security                                  |Transparent Data Encryption                             |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Database In-Memory                                 |In-Memory Aggregation                                   |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Database In-Memory                                 |In-Memory Column Store                                  |SUPPRESSED_DUE_TO_BUG   |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Database Vault                                     |Oracle Database Vault                                   |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Database Vault                                     |Privilege Capture                                       |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |ADDM                                                    |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |AWR Baseline                                            |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |AWR Baseline Template                                   |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |AWR Report                                              |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |Automatic Workload Repository                           |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |Baseline Adaptive Thresholds                            |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |Baseline Static Computations                            |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Diagnostics Pack                                   |EM Performance Page                                     |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Label Security                                     |Label Security                                          |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
OLAP                                               |OLAP - Analytic Workspaces                              |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
OLAP                                               |OLAP - Cubes                                            |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Partitioning                                       |Partitioning (user)                                     |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Partitioning                                       |Zone maps                                               |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
RAC or RAC One Node                                |Quality of Service Management                           |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Real Application Clusters                          |Real Application Clusters (RAC)                         |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Real Application Clusters One Node                 |Real Application Cluster One Node                       |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Real Application Testing                           |Database Replay: Workload Capture                       |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Real Application Testing                           |Database Replay: Workload Replay                        |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Real Application Testing                           |SQL Performance Analyzer                                |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Spatial and Graph                                  |Spatial                                                 |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Tuning Pack                                        |SQL Access Advisor                                      |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Tuning Pack                                        |SQL Monitoring and Tuning pages                         |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Tuning Pack                                        |SQL Profile                                             |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
Tuning Pack                                        |SQL Tuning Advisor                                      |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.Database Gateway                                  |Gateways                                                |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.Database Gateway                                  |Transparent Gateway                                     |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.Exadata                                           |Exadata                                                 |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.GoldenGate                                        |GoldenGate                                              |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.HW                                                |Hybrid Columnar Compression                             |SUPPRESSED_DUE_TO_BUG   |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.HW                                                |Hybrid Columnar Compression Row Level Locking           |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.HW                                                |Sun ZFS with EHCC                                       |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.HW                                                |ZFS Storage                                             |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.HW                                                |Zone maps                                               |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.Pillar Storage                                    |Pillar Storage                                          |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |
.Pillar Storage                                    |Pillar Storage with EHCC                                |NO_CURRENT_USAGE        |2023.09.05_00.39.38|1674462089|12.1.0.2.0 |              0|            1|FALSE         |
 |                   |

USER is "SYS"

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DESCRIPTION:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The two reports, PRODUCT USAGE and FEATURE USAGE DETAILS, provide usage statistics for Database Options, Management Packs
and their corresponding features.
Information is extracted from DBA_FEATURE_USAGE_STATISTICS view.

DBA_FEATURE_USAGE_STATISTICS view is updated once a week, so it may take up to 7 days for the report to reflect usage changes.
DBA_FEATURE_USAGE_STATISTICS view contains a different set of entries for each VERSION and DBID occurring in the database history.
The weekly refresh process updates only the current row set.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
NOTES:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The report lists all detectable products and features, used or not used.
The CURRENTLY_USED column in the DBA_FEATURE_USAGE_STATISTICS view indicates if the feature in question was used during the last sampling interval
or is used at the refresh moment.
CURRENT_USAGE represents usage tracked over the last sample period, which defaults to one week.
PAST_OR_CURRENT_USAGE example: Datapump Export entry indicates CURRENTLY_USED='TRUE' and FEATURE_INFO "compression used" counter
indicates a non zero value that could have been triggered by past or current (last week) usage.
For historical details check FIRST_USAGE_DATE, LAST_USAGE_DATE, LAST_SAMPLE_DATE, TOTAL_SAMPLES, DETECTED_USAGES columns
Leading dot (.) denotes a product that is not a Database Option or Database Management Pack

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DISCLAIMER:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Information provided by the reports is to be used for informational purposes only and does not represent your license entitlement or requirement.
The usage data may indicate, in some cases, false positives.
This may be due to inclusion of usage by sample schemas (such as HR, PM, SH...) or system/internal usage.

Please refer to MOS DOC ID 1317265.1 and 1309070.1 for more information.

End of script (v 21.0 Oct-2021)
SQL>

Thank You and End of Oracle Blog

Fri, 2023-08-04 10:51

I have been given an opportunity with SQL Server and Azure and will need time to learn how to ride the new bike. Hope I don’t crash.

OGG Veridata Bundle Patch 12.2.1.4.220831 (PS4 BP6)

Mon, 2023-06-05 23:26

Something as simple as patching made convoluted by unclear documentation.

Ignacio from Oracle support was a great help by offering Zoom session.

https://blogs.oracle.com/dataintegration/post/oracle-goldengate-veridata-12214220831-is-now-available

33953823 – OGG Veridata Bundle Patch 12.2.1.4.220831 (PS4 BP6) (Server+Agent)
============================================================
From README.txt

3. Verify the OUI Inventory.
============================================================

OPatch needs access to a valid OUI inventory to apply patches.

Note: This needs the ORACLE_HOME to be set(refer section “2. Pre-Installation Instructions”)
prior to run the below commands:

Validate the OUI inventory with the following commands:

$ opatch lsinventory -jre $ORACLE_HOME/oracle_common/jdk/jre

Note:
Make sure the JDK version you use is the certified version for your product.

If the command errors out, contact Oracle Support and work to validate
and verify the inventory setup before proceeding.

[oracle@localhost ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -help

-jre
This option tells OPatch to use JRE (java) from the
specified location instead of the default location
under Oracle Home

============================================================
### NOTE:
============================================================

NO PROCESSES ARE RUNNING AND ONLY WLS AND VERIDATA HAVE BEEN INSTALLED

veridata_12.2.1.4.0
wls_infra_12.2.1.4.0

============================================================
### START PATCHING
============================================================

[oracle@localhost patch]$ pwd
/vagrant/software/patch
[oracle@localhost patch]$ ls -l
total 60584
-rwxrwxrwx. 1 vagrant vagrant 54494225 Jun 6 02:36 p28186730_1394212_Generic.zip
-rwxrwxrwx. 1 vagrant vagrant 7539013 Jun 6 02:36 p33953823_122140_Generic.zip
[oracle@localhost patch]$ unzip -qo p33953823_122140_Generic.zip; echo $?
0
[oracle@localhost patch]$ ls -l
total 60860
drwxrwxrwx. 1 vagrant vagrant 0 Sep 15 2022 33953823
-rwxrwxrwx. 1 vagrant vagrant 268073 Sep 20 2022 oracle-goldengate-veridata-release-notes_12.2.1.4.220831.pdf
-rwxrwxrwx. 1 vagrant vagrant 54494225 Jun 6 02:36 p28186730_1394212_Generic.zip
-rwxrwxrwx. 1 vagrant vagrant 7539013 Jun 6 02:36 p33953823_122140_Generic.zip
-rwxrwxrwx. 1 vagrant vagrant 11408 Sep 21 2022 README.txt
[oracle@localhost patch]$ export ORACLE_HOME=/opt/oracle/wls
[oracle@localhost patch]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 13.9.4.2.1

OPatch succeeded.
[oracle@localhost patch]$ $ORACLE_HOME/OPatch/opatch lspatches
There are no Interim patches installed in this Oracle Home “/opt/oracle/wls”.

OPatch succeeded.
[oracle@localhost patch]$ cd 33953823
[oracle@localhost 33953823]$ pwd
/vagrant/software/patch/33953823
[oracle@localhost 33953823]$

[oracle@localhost 33953823]$ $ORACLE_HOME/OPatch/opatch apply /vagrant/software/patch/33953823
Oracle Interim Patch Installer version 13.9.4.2.1
Copyright (c) 2023, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/wls
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/wls/oraInst.loc
OPatch version : 13.9.4.2.1
OUI version : 13.9.4.0.0
Log file location : /opt/oracle/wls/cfgtoollogs/opatch/opatch2023-06-06_02-46-11AM_1.log


OPatch detects the Middleware Home as “/opt/oracle/wls”

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 33953823

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/opt/oracle/wls’)


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch ‘33953823’ to OH ‘/opt/oracle/wls’
ApplySession: Optional component(s) [ oracle.veridata.agent.core, 12.2.1.4.0 ] , [ oracle.veridata.agent.core, 12.2.1.4.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.fmw.upgrade.veridata, 12.2.1.4.0…

Patching component oracle.veridata.web.core, 12.2.1.4.0…

Patching component oracle.veridata.web.core, 12.2.1.4.0…

Patching component oracle.rcu.veridata, 12.2.1.4.0…
Patch 33953823 successfully applied.
Log file location: /opt/oracle/wls/cfgtoollogs/opatch/opatch2023-06-06_02-46-11AM_1.log

OPatch succeeded.
[oracle@localhost 33953823]$

[oracle@localhost 33953823]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 13.9.4.2.1
Copyright (c) 2023, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/wls
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/wls/oraInst.loc
OPatch version : 13.9.4.2.1
OUI version : 13.9.4.0.0
Log file location : /opt/oracle/wls/cfgtoollogs/opatch/opatch2023-06-06_02-48-44AM_1.log


OPatch detects the Middleware Home as “/opt/oracle/wls”

Lsinventory Output file location : /opt/oracle/wls/cfgtoollogs/opatch/lsinv/lsinventory2023-06-06_02-48-44AM.txt

——————————————————————————–
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64


Interim patches (1) :

Patch 33953823 : applied on Tue Jun 06 02:46:42 UTC 2023
Unique Patch ID: 24943512
Patch description: “One-off”
Created on 14 Sep 2022, 19:25:44 hrs PST8PDT
Bugs fixed:
34322822, 34202990, 33979317, 34275308, 30257704, 34028901, 33885974
33750454, 33719877, 33627880, 32585259, 32133466, 28279315, 33188570
20403129, 32852132, 32286962, 32821397, 32461542, 32213540, 32322787
32717596, 32313798, 30533210, 32531882, 32258415, 32486366, 32050877
32250963, 32249623, 32113971, 30351843, 32348306, 29376272, 30608181
30771003, 30811737, 30923601, 31126930, 31152591, 31290595, 31344851
31401520, 31442050, 31568607, 30778093, 21276396, 30712451, 25418342
30392409, 30425385, 30558507



——————————————————————————–

OPatch succeeded.
[oracle@localhost 33953823]$

Install Healthcheck Script For GoldenGate Integrated MicroService Architecture

Wed, 2023-05-03 19:00

The installation was a little convoluted since OGG MicroService Architecture (MA) and database reside on differrent host.

For OGG MicroService Architecture (MA), the Healthcheck would be found in $OGG_MA_HOME/lib/sql/healthcheck

Reference: Doc ID 2653026.1

First need to connect to OGG MA to retrieve the following scripts and transfer to DB host:

-rwxr-x---. 1 oracle oinstall 1495789 May  3 10:01 ogghc_install.sql
-rwxr-x---. 1 oracle oinstall    1469 May  3 10:01 ogghc_run.sql
-rwxr-x---. 1 oracle oinstall     912 May  3 10:01 ogghc_uninstall.sql

Here is the fist install as C##GGATE dabase user.

SQL> connect C##GGATE
Enter password:
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set current_schema=C##GGATE;

Session altered.

SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
C##GGATE

SQL> @ogghc_install.sql

NAH! Did not like lack of features when installed as NON-SYS database users.

Let’s remove it.

SQL> connect C##GGATE
Enter password:
Connected.
SQL> show user
USER is "C##GGATE"
SQL> @ogghc_uninstall.sql

Let’s install as SYS in C##GGATE schema.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0

SQL> alter session set current_schema=C##GGATE;

Session altered.

SQL> select sys_context ('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
C##GGATE

SQL> show user
USER is "SYS"
SQL> !ls ogg*.sql
ogghc_install.sql  ogghc_run.sql  ogghc_uninstall.sql

SQL> @ogghc_install.sql

Checking installation. This might take a while...

DBMS_GOLDENGATE_HCADM_INT.CHECK_STATS('ALL')
--------------------------------------------------------------------------------

Total Success:261
Total Failure:0

OK...STAT DB Version Range Check.
OK...STAT DB Version Check.
OK...STAT No Duplicates.
OK...STAT Count check (261).
OK...STAT Invalid Objects Count check.
OK...GGHC Objects Count:actual=24:expected=24

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0

ASMCMD or SQL (Read_Err and Write_Err)

Tue, 2023-05-02 20:38

My preference is CLI vs SQL and here is demo.

Does anyone know when Read_Err and Write_Err are populated and if the are persistent.

Opened SR did not help.

Here’s a demo.

[oracle@ol7-19-rac1 vagrant_scripts]$ ./asm.sh
#!/bin/bash -v
# Michael Dinh : April 2023
# Tested Oracle Linux Server release 7.9
# https://docs.oracle.com/en/database/oracle/oracle-database/19/ostmg/manage-asm-asmcmd.html
#
asmcmd -V; echo
asmcmd version 19.0.0.0.0

asmcmd lsdg DATA; echo
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  1048576     40959    36785                0           36785              0             N  DATA/

asmcmd lsdsk -G DATA --statistics; echo
 Reads  Write  Read_Errs  Write_Errs   Read_time  Write_Time  Bytes_Read  Bytes_Written  Voting_File  Path
460859  94563          0           0  685.786374  189.428009  7273860608     1310206976            N  /dev/oracleasm/asm-data-disk1

asmcmd lsdsk -G DATA -p -g -t; echo
Inst_ID  Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Create_Date  Mount_Date  Repair_Timer  Path
      1          2         0  4042317611  CACHED      MEMBER       ONLINE     NORMAL  29-APR-23    01-MAY-23   0             /dev/oracleasm/asm-data-disk1
      2          2         0  4042320085  CACHED      MEMBER       ONLINE     NORMAL  29-APR-23    01-MAY-23   0             /dev/oracleasm/asm-data-disk1

asmcmd lsod -G DATA; echo
Instance Process                                    OSPID Path
1        oracle@ol7-19-rac1.localdomain (DBW0)      8711  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (GMON)      8733  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (LGWR)      8715  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (PPA7)      13656 /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (RBAL)      8730  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (RBAL)      8730  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (TNS V1-V3) 8603  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (TNS V1-V3) 8817  /dev/oracleasm/asm-data-disk1
1        oracle@ol7-19-rac1.localdomain (TNS V1-V3) 9239  /dev/oracleasm/asm-data-disk1

asmcmd lsattr -lm -G DATA; echo
Group_Name  Name                        Value         RO  Sys
DATA        access_control.enabled      FALSE         N   Y
DATA        access_control.umask        066           N   Y
DATA        appliance._partnering_type  GENERIC       Y   Y
DATA        ate_conversion_done         true          Y   Y
DATA        au_size                     1048576       Y   Y
DATA        cell.smart_scan_capable     FALSE         N   N
DATA        cell.sparse_dg              allnonsparse  N   N
DATA        compatible.asm              19.0.0.0.0    N   Y
DATA        compatible.rdbms            19.0.0.0.0    N   Y
DATA        content.check               FALSE         N   Y
DATA        content.type                data          N   Y
DATA        content_hardcheck.enabled   FALSE         N   Y
DATA        disk_repair_time            12.0h         N   Y
DATA        failgroup_repair_time       24.0h         N   Y
DATA        idp.boundary                auto          N   Y
DATA        idp.type                    dynamic       N   Y
DATA        logical_sector_size         512           N   Y
DATA        phys_meta_replicated        true          Y   Y
DATA        preferred_read.enabled      FALSE         N   Y
DATA        scrub_async_limit           1             N   Y
DATA        scrub_metadata.enabled      TRUE          N   Y
DATA        sector_size                 512           N   Y
DATA        thin_provisioned            FALSE         N   Y
DATA        vam_migration_done          false         Y   Y

asmcmd lsattr -lm -G DATA %compat*; echo
Group_Name  Name              Value       RO  Sys
DATA        compatible.asm    19.0.0.0.0  N   Y
DATA        compatible.rdbms  19.0.0.0.0  N   Y

######################################################################
# Not able to stop process from Vagrant VirtualBox - required reboot!
#asmcmd iostat -G DATA --io --region -et 15
######################################################################

### Control-C to stop after 5 results ###
echo

asmcmd iostat -G DATA --io --region -et 15; echo
Group_Name  Dsk_Name   Reads   Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Err  Write_Err  Read_Time   Write_Time
DATA        DATA_0000  460917  94566   429399      94480        0          0           0         0          685.806749  189.430973

Group_Name  Dsk_Name   Reads  Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Err  Write_Err  Read_Time  Write_Time
DATA        DATA_0000  61.00  5.00    61.00       5.00         0.00       0.00        0.00      0.00       0.02       0.01

Group_Name  Dsk_Name   Reads  Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Err  Write_Err  Read_Time  Write_Time
DATA        DATA_0000  60.00  14.00   60.00       14.00        0.00       0.00        0.00      0.00       0.03       0.01

Group_Name  Dsk_Name   Reads  Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Err  Write_Err  Read_Time  Write_Time
DATA        DATA_0000  54.00  5.00    54.00       5.00         0.00       0.00        0.00      0.00       0.02       0.01

Group_Name  Dsk_Name   Reads  Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Err  Write_Err  Read_Time  Write_Time
DATA        DATA_0000  49.00  5.00    49.00       5.00         0.00       0.00        0.00      0.00       0.02       0.00

^C
echo

ls -l /dev/oracleasm; echo
total 0
lrwxrwxrwx. 1 root root 7 May  3 01:22 asm-crs-disk1 -> ../sdc1
lrwxrwxrwx. 1 root root 7 May  3 01:21 asm-crs-disk2 -> ../sdd1
lrwxrwxrwx. 1 root root 7 May  3 01:22 asm-crs-disk3 -> ../sde1
lrwxrwxrwx. 1 root root 7 May  3 01:22 asm-data-disk1 -> ../sdf1
lrwxrwxrwx. 1 root root 7 May  3 01:22 asm-reco-disk1 -> ../sdg1

iostat -cdmxt 15 4; echo
Linux 5.4.17-2136.318.7.1.el7uek.x86_64 (ol7-19-rac1.localdomain)       05/03/2023      _x86_64_        (4 CPU)

05/03/2023 01:22:36 AM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.05    0.00    5.40    0.38    0.00   88.17

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.65    0.10   25.28     0.02     0.09     8.76     0.02    0.76    3.10    0.75 0.7194   1.83
sdg               0.00     0.00    0.47    0.67     0.00     0.01    21.30     0.00    1.17    0.45    1.67 1.7904   0.20
sdc               0.00     0.00    2.46    1.00     0.00     0.00     2.18     0.00    0.88    0.81    1.07 1.3901   0.48
sde               0.00     0.00    2.42    1.00     0.00     0.00     2.13     0.00    0.89    0.82    1.08 1.4009   0.48
sda               0.00     0.05    0.07    0.62     0.00     0.00    20.43     0.00    0.80    1.02    0.77 0.6081   0.04
sdd               0.00     0.00    2.27    1.00     0.00     0.00     1.87     0.00    0.90    0.83    1.06 1.4073   0.46
sdf               0.02     0.00    4.78    0.98     0.07     0.01    30.23     0.00    0.78    0.63    1.55 1.1222   0.65

05/03/2023 01:22:51 AM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.62    0.00    4.48    0.12    0.00   92.78

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.53    0.00   10.67     0.00     0.07    14.34     0.01    0.64    0.00    0.64 0.4562   0.49
sdg               0.00     0.00    0.20    0.67     0.00     0.01    29.62     0.00    0.69    0.33    0.80 1.5385   0.13
sdc               0.00     0.00    2.13    1.00     0.00     0.00     1.00     0.00    0.40    0.44    0.33 1.1915   0.37
sde               0.00     0.00    2.13    1.00     0.00     0.00     1.00     0.00    0.43    0.44    0.40 1.1064   0.35
sda               0.00     0.13    0.00    1.33     0.00     0.01     9.75     0.00    0.55    0.00    0.55 0.3500   0.05
sdd               0.00     0.00    2.13    1.00     0.00     0.00     1.00     0.00    0.43    0.47    0.33 1.0426   0.33
sdf               0.00     0.00    4.27    0.67     0.07     0.01    31.58     0.00    0.43    0.39    0.70 0.8649   0.43

05/03/2023 01:23:06 AM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.65    0.00    4.47    0.08    0.00   90.80

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.13    0.00    6.93     0.00     0.06    16.85     0.00    0.23    0.00    0.23 0.6250   0.43
sdg               0.00     0.00    0.13    0.53     0.00     0.01    22.90     0.00    0.90    0.00    1.12 2.1000   0.14
sdc               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.40    0.47    0.27 1.0667   0.32
sde               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.38    0.43    0.27 1.0444   0.31
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00 0.0000   0.00
sdd               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.36    0.37    0.33 1.0444   0.31
sdf               0.00     0.00    3.33    1.40     0.05     0.01    27.79     0.00    0.46    0.36    0.71 0.9296   0.44

05/03/2023 01:23:21 AM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.81    0.00    4.43    0.10    0.00   92.66

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.40    0.00    6.00     0.00     0.03     9.84     0.00    0.33    0.00    0.33 0.6556   0.39
sdg               0.00     0.00    0.07    0.33     0.00     0.01    32.00     0.00    0.83    0.00    1.00 1.8333   0.07
sdc               0.00     0.00    2.13    1.00     0.00     0.00     1.00     0.00    0.38    0.44    0.27 1.0851   0.34
sde               0.00     0.00    2.13    1.00     0.00     0.00     1.00     0.00    0.43    0.44    0.40 1.1064   0.35
sda               0.00     0.00    0.00    0.47     0.00     0.00    16.57     0.00    0.43    0.00    0.43 0.4286   0.02
sdd               0.00     0.00    2.13    1.00     0.00     0.00     1.00     0.00    0.38    0.44    0.27 1.0000   0.31
sdf               0.00     0.00    3.53    0.33     0.06     0.01    32.00     0.00    0.41    0.40    0.60 0.8448   0.33


exit
[oracle@ol7-19-rac1 vagrant_scripts]$

Managing RAC Vagrant VirtualBox

Sat, 2023-04-29 20:28

I got tired of having to type multiples commands to manage the environment.

The following one liner can be used to start and stop RAC and manage snapshots.

This is based on https://github.com/oraclebase/vagrant

Note: you will to change the location for vagrant.

Vagrant is installed on C:

cd /c/vagrant/rac/ol7_19/dns;vagrant up; cd ../node2;vagrant up; cd ../node1;vagrant up
cd /c/vagrant/rac/ol7_19/node2;vagrant halt; cd ../node1;vagrant halt; cd ../dns;vagrant halt


f8@eeyore MINGW64 /c/vagrant/rac/ol7_19/node1 (master)
$ snapshot_name=TEST

f8@eeyore MINGW64 /c/vagrant/rac/ol7_19/node1 (master)
$ cd /c/vagrant/rac/ol7_19/dns;vagrant snapshot save $snapshot_name; cd ../node2;vagrant snapshot save $snapshot_name; cd ../node1;vagrant snapshot save $snapshot_name
==> default: Snapshotting the machine as 'TEST'...
==> default: Snapshot saved! You can restore the snapshot at any time by
==> default: using `vagrant snapshot restore`. You can delete it using
==> default: `vagrant snapshot delete`.
==> default: Snapshotting the machine as 'TEST'...
==> default: Snapshot saved! You can restore the snapshot at any time by
==> default: using `vagrant snapshot restore`. You can delete it using
==> default: `vagrant snapshot delete`.
==> default: Snapshotting the machine as 'TEST'...
==> default: Snapshot saved! You can restore the snapshot at any time by
==> default: using `vagrant snapshot restore`. You can delete it using
==> default: `vagrant snapshot delete`.

f8@eeyore MINGW64 /c/vagrant/rac/ol7_19/node1 (master)
$ cd /c/vagrant/rac/ol7_19/dns;vagrant snapshot list; cd ../node2;vagrant snapshot list; cd ../node1;vagrant snapshot list
==> default:
100-setup
TEST
==> default:
100-setup
TEST
==> default:
100-setup
TEST

f8@eeyore MINGW64 /c/vagrant/rac/ol7_19/node1 (master)
$ cd /c/vagrant/rac/ol7_19/dns;vagrant snapshot delete $snapshot_name; cd ../node2;vagrant snapshot delete $snapshot_name; cd ../node1;vagrant snapshot delete $snapshot_name
==> default: Deleting the snapshot 'TEST'...
==> default: Snapshot deleted!
==> default: Deleting the snapshot 'TEST'...
==> default: Snapshot deleted!
==> default: Deleting the snapshot 'TEST'...
==> default: Snapshot deleted!

f8@eeyore MINGW64 /c/vagrant/rac/ol7_19/node1 (master)
$ cd /c/vagrant/rac/ol7_19/dns;vagrant snapshot list; cd ../node2;vagrant snapshot list; cd ../node1;vagrant snapshot list
==> default:
100-setup
==> default:
100-setup
==> default:
100-setup

f8@eeyore MINGW64 /c/vagrant/rac/ol7_19/node1 (master)
$

Pages