Michael Dinh
Create MySQL In Vagrant Box
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
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.
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
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>
SQLServer and Leak
What’s easier? A bad install will always kill you.
options_packs_usage_statistics.sql
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
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)
Something as simple as patching made convoluted by unclear documentation.
Ignacio from Oracle support was a great help by offering Zoom session.
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
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.sqlHere 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.sqlNAH! 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.sqlLet’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.0ASMCMD or SQL (Read_Err and Write_Err)
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
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)
$
The Career 5 C’s
Learned this long ago.
Compensation – happy being underpaid?
Challenge – want be be bored to death or have too high hurdles?
Colleagues – want to be on good team?
Commute – is sitting 2 hours in traffice okay?
Culture – corporate and team – do they help you grow?
OGG 21c Microservices Vagrant VirtualBox Google Doc
https://docs.google.com/document/d/1nJ-0Oo0V4X0u8r3uC3inESgQUh4gCTn6liLhyROZ7ks/edit?usp=sharing
You need to build your own and hope this helps.
Configuring nginx Oracle Linux Server 7.9 for Oracle GoldenGate 21c
Just wanted to document the process as I was struggling with nginx.
Don’t forget to enable autostart for when host is rebooted.
I used: baseurl=http://nginx.org/packages/rhel/7/$basearch versus baseurl=http://nginx.org/packages/mainline/centos/7/$basearch How to install and use Nginx on CentOS 7 / RHEL 7
[root@localhost ~]# vi /etc/yum.repos.d/nginx.repo
[root@localhost ~]# cat /etc/yum.repos.d/nginx.repo
[nginx]
name=nginx repo
baseurl=http://nginx.org/packages/rhel/7/$basearch/
gpgcheck=0
enabled=1
[root@localhost ~]# yum install nginx -y
Installed:
nginx.x86_64 1:1.24.0-1.el7.ngx
Dependency Installed:
pcre2.x86_64 0:10.23-2.el7
Complete!
[root@localhost ~]#
==================================================
oracle@localhost::/home/oracle
$ export OGG_HOME=/opt/oracle/goldengate/ogg21c_ma
oracle@localhost::/home/oracle
$ cd $OGG_HOME/lib/utl/reverseproxy
oracle@localhost::/opt/oracle/goldengate/ogg21c_ma/lib/utl/reverseproxy
$ ./ReverseProxySettings -u oggadmin -P X#tpdZdm2wMt -o ogg.conf http://localhost:10000
oracle@localhost::/opt/oracle/goldengate/ogg21c_ma/lib/utl/reverseproxy
$ ls -l ogg.conf
-rw-r--r--. 1 oracle oinstall 73480 Apr 20 02:57 ogg.conf
oracle@localhost::/opt/oracle/goldengate/ogg21c_ma/lib/utl/reverseproxy
[root@localhost ~]# export OGG_HOME=/opt/oracle/goldengate/ogg21c_ma
[root@localhost ~]# cd $OGG_HOME/lib/utl/reverseproxy
[root@localhost reverseproxy]# ls -l ogg.conf
-rw-r--r--. 1 oracle oinstall 73480 Apr 20 02:57 ogg.conf
[root@localhost reverseproxy]# mv -v ogg.conf /etc/nginx/conf.d/
‘ogg.conf’ -> ‘/etc/nginx/conf.d/ogg.conf’
[root@localhost reverseproxy]#
[root@localhost reverseproxy]# /etc/ssl/certs/make-dummy-cert /etc/nginx/ogg.pem
[root@localhost reverseproxy]# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
[root@localhost reverseproxy]#
==================================================
[root@localhost reverseproxy]# systemctl status nginx
● nginx.service - nginx - high performance web server
Loaded: loaded (/usr/lib/systemd/system/nginx.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: http://nginx.org/en/docs/
[root@localhost reverseproxy]#
[root@localhost reverseproxy]# systemctl start nginx.service
[root@localhost reverseproxy]# systemctl status nginx
● nginx.service - nginx - high performance web server
Loaded: loaded (/usr/lib/systemd/system/nginx.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2023-04-20 03:06:25 UTC; 2s ago
Docs: http://nginx.org/en/docs/
Process: 6250 ExecStart=/usr/sbin/nginx -c /etc/nginx/nginx.conf (code=exited, status=0/SUCCESS)
Main PID: 6251 (nginx)
CGroup: /system.slice/nginx.service
├─6251 nginx: master process /usr/sbin/nginx -c /etc/nginx/nginx.conf
├─6252 nginx: worker process
└─6253 nginx: worker process
Apr 20 03:06:25 localhost.localdomain systemd[1]: Starting nginx - high performance web server...
Apr 20 03:06:25 localhost.localdomain systemd[1]: Started nginx - high performance web server.
[root@localhost reverseproxy]#
[root@localhost reverseproxy]# systemctl list-unit-files|grep nginx
nginx-debug.service disabled
nginx.service disabled
[root@localhost reverseproxy]#
[root@localhost reverseproxy]# systemctl enable nginx.service
Created symlink from /etc/systemd/system/multi-user.target.wants/nginx.service to /usr/lib/systemd/system/nginx.service.
[root@localhost reverseproxy]# systemctl list-unit-files|grep nginx
nginx-debug.service disabled
nginx.service enabled
[root@localhost reverseproxy]#
[root@localhost reverseproxy]# cat /etc/os-release | grep PRE
PRETTY_NAME="Oracle Linux Server 7.9"
[root@localhost reverseproxy]#
==================================================
$ firefox localhost:443
Crash Annotation GraphicsCriticalError: |[0][GFX1-]: glxtest: libEGL initialize failed (t=12.206) [GFX1-]: glxtest: libEGL initialize failed
Crash Annotation GraphicsCriticalError: |[0][GFX1-]: glxtest: libEGL initialize failed (t=12.206) |[1][GFX1-]: glxtest: libEGL initialize failed (t=12.206) [GFX1-]: glxtest: libEGL initialize failed
Missing chrome or resource URL: resource://gre/modules/UpdateListener.jsm
Missing chrome or resource URL: resource://gre/modules/UpdateListener.sys.mjs
==================================================
[root@localhost ~]# curl localhost
<!DOCTYPE html>
<html>
<head>
<title>Welcome to nginx!</title>
<style>
html { color-scheme: light dark; }
body { width: 35em; margin: 0 auto;
font-family: Tahoma, Verdana, Arial, sans-serif; }
</style>
</head>
<body>
<h1>Welcome to nginx!</h1>
<p>If you see this page, the nginx web server is successfully installed and
working. Further configuration is required.</p>
<p>For online documentation and support please refer to
<a href="http://nginx.org/">nginx.org</a>.<br/>
Commercial support is available at
<a href="http://nginx.com/">nginx.com</a>.</p>
<p><em>Thank you for using nginx.</em></p>
</body>
</html>
[root@localhost ~]#
Oracle GoldenGate Microservices 21.3.0.0.0 Linux Firefox
This took too many hours. Now if only I can get port forwarded to desktop.
This shows all the configurations.
Configure X11 For Vagrant Box
I had blogged about this many years and ago and for some reason it’s not working.
https://blog.pythian.com/how-to-configure-x11-for-vagrant-boxLuckily, I was able to find a simple version https://www.centlinux.com/2019/01/configure-x11-forwarding-putty-xming-windows.html
I know. Real DBA use silent install. However, Oracle can be FUBAR at times.
Here is an example.
Get Paid Before Pay Day at RheoData
Already got paid and it’s not Friday yet. Paycheck not held hostage for a week.
How To Flashback A BAD DBA?
I am working on improving Instantiating Oracle Golden Gate with an Initial Load using Datapump.
There are billions of rows and last import time was 11:18:20 hours with 102 errors ignored.
Following suggestions were made:
TEMPORARILY set db_block_checksum and flashback to OFF
alter system set db_block_checksum=OFF;
alter database flashback OFF;
Got reply:
I prefer not to turn this off. It's our standard so lets try to move on and leave the DB alone for now and focus on the export/import.
Replied back:
Ok – db_block_checksum – will leave as is.
However, flashback should be OFF for import – think about it for a minute and if you still don’t understand then let me know.
Keep in mind keyword: TEMPORARILY
I am hoping the DBA sees the light.
There is no need to have flashback enabled for an Initial Load.
If Initial Load fails, then fix the error and try again.
How To Trace GoldenGate To Find Bad Code
DBA for client panic after seeing 15:59:15 hours Lag at Chkpt
REPLICAT=XXXXX
GGSCI (xxxxxx07) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
REPLICAT RUNNING XXXXX 15:59:15 00:00:08 unsure why this one is now behind – non-stagingWithout access to the system, I requested following to trace what replicat is doing.
Please run and share results.
REPLICAT=XXXXX
send XXXXX trace2 /tmp/XXXXX.trc
wait for 2 minutes, then turn tracing off:
send XXXXX trace2 off
Email XXXXX.trc as attachment
Findings is bad coding practices by committing every 10 records.
Unknown – was this recently deployed? What is it doing? This is where development would know.
Was there any code review or BVT (basic validation testing)?
SCHEMA.TABLE_BS count is 26,946,719 rows with AVG row length 27
How did I know there as commit every 10 records?
Trace contains 21,648 lines:
$ wc -l XXXXX.trc
21648 XXXXX.trc
Find occurrences for table from trace:
$ grep -c SCHEMA.TABLE_BS XXXXX.trc
1267
Find occurrences for ORA-01403: no data found from trace:
$ grep -c 1403 XXXXX.trc
1632
Find occurrences for successful database updates
$ grep -c "successful database updates" XXXXX.trc
364
Committing every 10 rows.
$ grep "successful database updates" XXXXX.trc|head
15:26:53.745 (3100055) successful database updates 0.253% (execute=7.847,total=3100.044,count=60)
15:26:54.539 (3100848) successful database updates 0.278% (execute=8.641,total=3100.838,count=70)
15:26:55.326 (3101635) successful database updates 0.303% (execute=9.411,total=3101.625,count=80)
15:26:56.119 (3102428) successful database updates 0.328% (execute=10.204,total=3102.417,count=90)
15:26:56.200 (3102509) successful database updates 0.331% (execute=10.285,total=3102.498,count=100)
15:26:56.213 (3102522) successful database updates 0.331% (execute=10.297,total=3102.512,count=110)
15:26:56.219 (3102528) successful database updates 0.332% (execute=10.302,total=3102.517,count=120)
15:26:56.224 (3102533) successful database updates 0.332% (execute=10.306,total=3102.522,count=130)
15:26:56.235 (3102544) successful database updates 0.332% (execute=10.315,total=3102.533,count=140)
15:26:56.240 (3102549) successful database updates 0.332% (execute=10.320,total=3102.539,count=150)
$ grep "successful database updates" XXXXX.trc|tail
15:28:50.482 (3216791) successful database updates 3.841% (execute=123.585,total=3216.781,count=3600)
15:28:50.488 (3216797) successful database updates 3.842% (execute=123.590,total=3216.786,count=3610)
15:28:50.498 (3216807) successful database updates 3.842% (execute=123.597,total=3216.797,count=3620)
15:28:50.504 (3216813) successful database updates 3.842% (execute=123.602,total=3216.802,count=3630)
15:28:50.516 (3216825) successful database updates 3.842% (execute=123.609,total=3216.814,count=3640)
15:28:50.528 (3216837) successful database updates 3.842% (execute=123.619,total=3216.826,count=3650)
15:28:50.536 (3216845) successful database updates 3.843% (execute=123.624,total=3216.835,count=3660)
15:28:50.543 (3216852) successful database updates 3.843% (execute=123.631,total=3216.841,count=3670)
15:28:51.479 (3217788) successful database updates 3.870% (execute=124.556,total=3217.778,count=3680)
15:28:53.212 (3219522) successful database updates 3.922% (execute=126.289,total=3219.511,count=3690)
Use The Right Tool
I think I may have blogged about how most read from left to right and down unless you are reading Chinese.
Look at how nice that is versus using text file.
Back to the grind.
