Michael Dinh
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.
Datapump Import Performance Improvement for IOT with 1.9B rows
The root cause of the issue is source (DW) and target (OLTP) have different partition design for index organized table (IOT).
Source: PARTITION BY HASH and Target: PARTITION BY VALUES
IOT has approximately 1.9B rows.
Version 19.12.2.0.0 ### References: In What Order Are Indexes Built During Datapump Import (IMPDP) and How to Optimize the Index Creation (Doc ID 1966442.1) Normally index creation in a schema level import will follow this order: 1. Metadata import (user, roles & system privileges) 2. Objects like type, sequences and related grants 3. Tables, table data, table grants 4. Indexes ### This is the parameter file for export. exclude=STATISTICS compression=ALL # Is it necessary to export staging? schemas=staging,s2,s3,s4,s5 flashback_scn=61727639035 # There are 8 CPUs and possible to use up to 1.5xCPU and monitor. # FUD in play. parallel=8 content=DATA_ONLY # Added for improvements to avoid 1 process doing all the work. # Tested using 1G/2G and no huge improvements. filesize=4G logfile=expdp.log dumpfile=schema%U.dmp directory=dpump_dir ### This is the parameter file for import. table_exists_action=TRUNCATE # Added for performance improvements since there is no standby database. transform=DISABLE_ARCHIVE_LOGGING:Y logtime=ALL metrics=Y # There are 8 CPUs and possible to use up to 1.5xCPU and monitor. # FUD in play. parallel=8 cluster=N schemas=staging,s2,s3,s4,s5 # STATISTICS was already exclude from import. # Exclude is not necessary, since will import everything. exclude=STATISTICS,REF_CONSTRAINT,GRANT,INDEX,TRIGGER content=DATA_ONLY logfile=impdp.log dumpfile=schema%U.dmp directory=dpump_dir ### Here is the export dmp. Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_09 is: /export/schema01.dmp /export/schema02.dmp /export/schema03.dmp /export/schema04.dmp /export/schema05.dmp /export/schema06.dmp /export/schema07.dmp /export/schema08.dmp /export/schema09.dmp /export/schema10.dmp /export/schema11.dmp /export/schema12.dmp /export/schema13.dmp /export/schema14.dmp /export/schema15.dmp /export/schema16.dmp /export/schema17.dmp /export/schema18.dmp /export/schema19.dmp ### Here is the import for IOT. 16-MAR-23 20:25:48.971: W-4 . . imported "H01" 3.946 GB 474235896 rows in 12183 seconds using external_table 16-MAR-23 14:42:56.470: W-7 . . imported "H02" 3.952 GB 475007010 rows in 7113 seconds using external_table 16-MAR-23 23:45:07.344: W-8 . . imported "H03" 3.945 GB 474120834 rows in 11956 seconds using external_table 16-MAR-23 17:02:44.346: W-6 . . imported "H04" 3.949 GB 474655428 rows in 8386 seconds using external_table 16-MAR-23 23:58:02.815: Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed at Thu Mar 16 23:58:02 2023 elapsed 0 11:18:20 The result for import was 50% faster for IOT with ~1.90B rows.
There are probably more options to test and tune; however, would be too time consuming.
Writing this blog post, I realized I have made the mistake of not requesting export/import parameter files and logs as there are redundancies for export and import parameters.
Some Quirks With oggca_response File Errors
I have just completed 6 deployments and thought I would things I was not are of.
First issue is relared to password requirements.
Password M1llionD0llar$ will not work.
Who doesn’t like million dollar?
[oracle@dinh ~]$ /u01/oracle/app/goldengate/ogg21ma/bin/oggca.sh -silent -responseFile /tmp/dinh_PROD1-PROD2_oggca_response.rsp
[FATAL] [INS-85080] Invalid password.
CAUSE: The provided password is invalid for the current strong password policy settings. The strong password policy is enabled.
Password should contain at least:
one lowercase [a..z] character,
one uppercase [A..Z] character,
one digit [0..9], and
one special character [- ! @ % & * . # _].
Length should be between 8..30 characters.
ACTION: Specify a valid password.
[oracle@dinh ~]$EZCONNECT will be used for the deployment; there is no need to install client or database.
[oracle@dinh ~]$ /u01/oracle/app/goldengate/ogg21ma/bin/oggca.sh -silent -responseFile /tmp/dinh_PROD1-PROD2_oggca_response.rsp
[FATAL] [INS-85077] Empty value for TNS_ADMIN environment variable.
ACTION: Specify a value for the TNS_ADMIN environment variable.
[oracle@dinh ~]$The work around is just to create an empty diretory. In my case, just create it under $OGG_HOME
ENV_TNS_ADMIN=/u01/oracle/app/goldengate/ogg21ma/network/admin
SERVICEMANAGER_DEPLOYMENT_HOME
[oracle@dinh ~]$ /u01/oracle/app/goldengate/ogg21ma/bin/oggca.sh -silent -responseFile /tmp/dinh_PROD1-PROD2_oggca_response.rsp
[FATAL] [INS-85103] The path /u01/oracle/app/ogg_deploy/ServiceManager/PROD1-PROD2 cannot be used as a deployment directory.
CAUSE: Deployment directories cannot be located inside the Service Manager deployment directory.
ACTION: Provide a valid path for the deployment directory that is not located inside the Service Manager deployment home.
[oracle@dinh ~]$Move it
SERVICEMANAGER_DEPLOYMENT_HOME=/u01/oracle/app/ogg_deploy/ServiceManager
OGG_DEPLOYMENT_HOME=/u01/oracle/app/ogg_deploy/PROD1-PROD2I know that you are thinking -RTFM. I would still be reading vs delivering.
Just like learning how to ride a bike. You fall and get back on.
It’s also good to have someone there when youu fall.
Oracle GoldenGate Microservices Architecture Silent Deployment Creation – Better Port Selection
I was working on 6 deployments – 3 primary and 3 standy.
Was heading to home base but then tripped and fell – had port collision.
How fastt can you spot the error?
### DEPLOYMENT #1
PORT_ADMINSRVR=21001
PORT_DISTSRVR=21002
PORT_RCVRSRVR=21003
PORT_PMSRVR=21004
UDP_PORT_PMSRVR=21005
### DEPLOYMENT #2
PORT_ADMINSRVR=21021
PORT_DISTSRVR=21012
PORT_RCVRSRVR=21013
PORT_PMSRVR=21014
UDP_PORT_PMSRVR=21015
### DEPLOYMENT #3
PORT_ADMINSRVR=21021
PORT_DISTSRVR=21022
PORT_RCVRSRVR=21023
PORT_PMSRVR=21024
UDP_PORT_PMSRVR=21025
Remember, we read from left to right.
Now, how fast can you spot the error?
### DEPLOYMENT #1
PORT_ADMINSRVR=10001
PORT_DISTSRVR=10002
PORT_RCVRSRVR=10003
PORT_PMSRVR=10004
UDP_PORT_PMSRVR=10005
### DEPLOYMENT #2
PORT_ADMINSRVR=20001
PORT_DISTSRVR=20002
PORT_RCVRSRVR=20003
PORT_PMSRVR=20004
UDP_PORT_PMSRVR=20005
### DEPLOYMENT #3
PORT_ADMINSRVR=20001
PORT_DISTSRVR=30002
PORT_RCVRSRVR=30003
PORT_PMSRVR=30004
UDP_PORT_PMSRVR=30005
Some good news, with 21c version, it was an easy fix.
Click on bad port to edit and save.
OGG Microservices Silent Installation and Deployment – FUBAR?
There are 2 steps to Implement Oracle GoldenGate Microservices Architecture.
Step1 – Install Oracle GoldenGate – oggcore.rsp is provided
Step2 – Create the Oracle GoldenGate Deployment – oggca.rsp is not provided
To get oggca.rsp, one will need to perform installation using GUI and save the response file as oggca.rsp.
Here is an example for differences for response file header – ogginstall vs oggca – don’t get this wrong.
Install Oracle GoldenGate
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v21_1_0
Create the Oracle GoldenGate Deployment
oracle.install.responseFileVersion=/oracle/install/rspfmt_oggca_response_schema_v21_1_0
Next, PMSRVR_DATASTORE_TYPE : LMDB or BDB?
System Properties Comparison LMDB vs. Oracle Berkeley DB vs. SQLite
Retrieving and understanding a response file for Oracle GoldenGate Deployment configuration