Archive for the ‘ORACLE Database’ Category
Dienstag, Juni 20th, 2023
‚Bad‘ Performance by Example in the SQL Where Clause
WHERE (:v_name IS NULL OR last_name = :v_name)
AND (:v_odate IS NULL OR TO_CHAR(order_date,’DD-MM-YYYY‘) = :v_odate)
AND (:v_cid IS NULL OR cust_id = :v_cid)
Das Problem dabei ist das ‚OR‘ – auf Grund von ‚OR‘ für jede Spalte schätzt der Oracle cost-based optimizer (CBO) einerseits die Selektivität falsch ein und andererseits werden die Kosten für Indexzugriffe oft genug so teuer veranschlagt dass das Resultat ein Full Table Scan ist
‚Better‘ Performance by Example in the SQL Where Clause
WHERE last_name = NVL(:v_name,last_name)
AND order_date = TO_DATE(NVL(:v_odate,order_date),’DD-MM-YYYY‘)
AND cust_id = NVL(:v_cid,cust_id)
Posted in Administration, ORACLE Database | No Comments »
Donnerstag, Juni 15th, 2023
WITH unit_table AS (
SELECT * FROM (VALUES (1, ‚B‘, 0, 899),
(2, ‚kB‘, 900, 9e5 – 1),
(3, ‚MB‘, 9e5, 9e8 – 1),
(4, ‚GB‘, 9e8, 9e100)
) t1 (id, unit, lo, hi)
)
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id – 1), 2) AS f_size, u.unit
FROM dba_data_files f
JOIN unit_table u
ON f.bytes BETWEEN u.lo AND u.hi
ORDER BY f.file_id;
Posted in Administration, ORACLE Database | No Comments »
Dienstag, Mai 23rd, 2023
Wenn man in einem ORACLE_HOME regelmäßig aktuelle Patches einspielt dauert es von Mal zu Mal länger und der Platzverbrauch im ORACLE_HOME wird immer größer das liegt daran dass OPatch und damit ein Rollback möglich ist im Verzeichnis $ORACLE_HOME/.patch_storage die alten Versionen der Software aufhebt und ein Patch Inventory File (oui-patch.xml) mit Informationen zu den Patches befüllt. Seit OPatch Version 26 (12.2.0.1.26) bietet Oracle mit dem OPatch Archive Utility die Möglichkeit nicht mehr benötigte Patches aus dem Verzeichnis $ORACLE_HOME/.patch_storage zu entfernen
# $ORACLE_HOME/OPatch/opatch util deleteinactivepatches -silent
Posted in Administration, ORACLE Database | No Comments »
Mittwoch, April 12th, 2023
Oracle Database 23c Free – an RPM-based installation performs preinstallation checks extracts the database software reassigns ownership of the extracted software to the preconfigured user and groups maintains the Oracle inventory,and runs all root operations required to configure the Oracle Database software for a single-instance Oracle Database creation and configuration the RPM–based installation process detects when the minimum requirements for an installation are not met and prompts you to finish these minimum preinstallation requirements
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# dnf makecache
Updating Subscription Management repositories.
Red Hat Enterprise Linux 8 for x86_64 – BaseOS (RPMs) 1.8 kB/s | 4.1 kB 00:02
Red Hat Enterprise Linux 8 for x86_64 – AppStream (RPMs) 11 kB/s | 4.5 kB 00:00
Metadata cache created.
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# dnf updateinfo
Updating Subscription Management repositories.
Last metadata expiration check: 0:02:33 ago on Mon 10 Apr 2023 11:19:38 AM CEST.
Updates Information Summary: available
210 Security notice(s)
4 Critical Security notice(s)
63 Important Security notice(s)
123 Moderate Security notice(s)
20 Low Security notice(s)
546 Bugfix notice(s)
53 Enhancement notice(s)
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# dnf update
Updating Subscription Management repositories.
Red Hat Enterprise Linux 8 for x86_64 – BaseOS (RPMs) 9.1 kB/s | 4.1 kB 00:00
Red Hat Enterprise Linux 8 for x86_64 – AppStream (RPMs) 3.5 kB/s | 4.5 kB 00:01
Last metadata expiration check: 0:00:19 ago on Mon 10 Apr 2023 11:19:38 AM CEST.
Dependencies resolved.
Nothing to do.
Complete!
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# curl -L -o oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 30688 100 30688 0 0 32508 0 –:–:– –:–:– –:–:– 32474
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# dnf -y localinstall oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
Updating Subscription Management repositories.
Last metadata expiration check: 2:00:36 ago on Mon 10 Apr 2023 02:28:46 PM CEST.
Dependencies resolved.
====================================================================
Package Architecture Version Repository Size
====================================================================
Installing:
oracle-database-preinstall-23c x86_64 1.0-0.5.el8 @commandline 30 k
Installing dependencies:
compat-openssl10 x86_64 1:1.0.2o-4.el8_6 rhel-8-for-x86_64-appstream-rpms 1.1 M
glibc-devel x86_64 2.28-211.el8 rhel-8-for-x86_64-baseos-rpms 82 k
glibc-headers x86_64 2.28-211.el8 rhel-8-for-x86_64-baseos-rpms 486 k
kernel-headers x86_64 4.18.0-425.19.2.el8_7 rhel-8-for-x86_64-baseos-rpms 10 M
ksh x86_64 20120801-257.el8 rhel-8-for-x86_64-appstream-rpms 929 k
libnsl x86_64 2.28-211.el8 rhel-8-for-x86_64-baseos-rpms 104 k
libxcrypt-devel x86_64 4.1.1-6.el8 rhel-8-for-x86_64-baseos-rpms 25 k
lm_sensors-libs x86_64 3.4.0-23.20180522git70f7e08.el8 rhel-8-for-x86_64-baseos-rpms 59 k
make x86_64 1:4.2.1-11.el8 rhel-8-for-x86_64-baseos-rpms 498 k
sysstat x86_64 11.7.3-7.el8_7.1 rhel-8-for-x86_64-appstream-rpms 426 k
Transaction Summary
====================================================================
Install 11 Packages
Total size: 14 M
Total download size: 14 M
Installed size: 17 M
Downloading Packages:
(1/10): libxcrypt-devel-4.1.1-6.el8.x86_64.rpm 80 kB/s | 25 kB 00:00
(2/10): lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64.rpm 161 kB/s | 59 kB 00:00
(3/10): make-4.2.1-11.el8.x86_64.rpm 995 kB/s | 498 kB 00:00
(4/10): glibc-headers-2.28-211.el8.x86_64.rpm 1.6 MB/s | 486 kB 00:00
(5/10): glibc-devel-2.28-211.el8.x86_64.rpm 423 kB/s | 82 kB 00:00
(6/10): kernel-headers-4.18.0-425.19.2.el8_7.x86_64.rpm 8.4 MB/s | 10 MB 00:01
(7/10): ksh-20120801-257.el8.x86_64.rpm 2.3 MB/s | 929 kB 00:00
(8/10): sysstat-11.7.3-7.el8_7.1.x86_64.rpm 1.5 MB/s | 426 kB 00:00
(9/10): libnsl-2.28-211.el8.x86_64.rpm 31 kB/s | 104 kB 00:03
(10/10): compat-openssl10-1.0.2o-4.el8_6.x86_64.rpm 325 kB/s | 1.1 MB 00:03
———————————————————————————————————————————–
Total 3.3 MB/s | 14 MB 00:04
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : make-1:4.2.1-11.el8.x86_64 1/11
Running scriptlet: make-1:4.2.1-11.el8.x86_64 1/11
Installing : compat-openssl10-1:1.0.2o-4.el8_6.x86_64 2/11
Running scriptlet: compat-openssl10-1:1.0.2o-4.el8_6.x86_64 2/11
Installing : ksh-20120801-257.el8.x86_64 3/11
Running scriptlet: ksh-20120801-257.el8.x86_64 3/11
Installing : kernel-headers-4.18.0-425.19.2.el8_7.x86_64 4/11
Running scriptlet: glibc-headers-2.28-211.el8.x86_64 5/11
Installing : glibc-headers-2.28-211.el8.x86_64 5/11
Installing : glibc-devel-2.28-211.el8.x86_64 6/11
Running scriptlet: glibc-devel-2.28-211.el8.x86_64 6/11
Installing : libxcrypt-devel-4.1.1-6.el8.x86_64 7/11
Installing : libnsl-2.28-211.el8.x86_64 8/11
Installing : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 9/11
Running scriptlet: lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 9/11
Installing : sysstat-11.7.3-7.el8_7.1.x86_64 10/11
Running scriptlet: sysstat-11.7.3-7.el8_7.1.x86_64 10/11
Installing : oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 11/11
Running scriptlet: oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 11/11
Verifying : libxcrypt-devel-4.1.1-6.el8.x86_64 1/11
Verifying : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 2/11
Verifying : make-1:4.2.1-11.el8.x86_64 3/11
Verifying : glibc-headers-2.28-211.el8.x86_64 4/11
Verifying : libnsl-2.28-211.el8.x86_64 5/11
Verifying : glibc-devel-2.28-211.el8.x86_64 6/11
Verifying : kernel-headers-4.18.0-425.19.2.el8_7.x86_64 7/11
Verifying : compat-openssl10-1:1.0.2o-4.el8_6.x86_64 8/11
Verifying : ksh-20120801-257.el8.x86_64 9/11
Verifying : sysstat-11.7.3-7.el8_7.1.x86_64 10/11
Verifying : oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 11/11
Installed products updated.
Installed:
compat-openssl10-1:1.0.2o-4.el8_6.x86_64 glibc-devel-2.28-211.el8.x86_64 glibc-headers-2.28-211.el8.x86_64
kernel-headers-4.18.0-425.19.2.el8_7.x86_64 ksh-20120801-257.el8.x86_64 libnsl-2.28-211.el8.x86_64
libxcrypt-devel-4.1.1-6.el8.x86_64 lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 make-1:4.2.1-11.el8.x86_64
oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 sysstat-11.7.3-7.el8_7.1.x86_64
Complete!
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 etc]# passwd oracle
Changing password for user oracle.
New password: <password>
Retype new password: <password>
passwd: all authentication tokens updated successfully.
[root@VA-RHEL8-01 etc]#
Download the oracle-database-free-23c-1.0-1.el8.x86_64.rpm RPM file required for performing an RPM-based installation to a directory of your choice e.g. /tmp
[root@VA-RHEL8-01 ~]# dnf install -y /tmp/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
Updating Subscription Management repositories.
Last metadata expiration check: 2:55:57 ago on Mon 10 Apr 2023 02:28:46 PM CEST.
Dependencies resolved.
====================================================================
Package Architecture Version Repository Size
====================================================================
Installing:
oracle-database-free-23c x86_64 1.0-1 @commandline 1.6 G
Transaction Summary
====================================================================
Install 1 Package
Total size: 1.6 G
Installed size: 5.2 G
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: oracle-database-free-23c-1.0-1.x86_64 1/1
Installing : oracle-database-free-23c-1.0-1.x86_64 1/1
Running scriptlet: oracle-database-free-23c-1.0-1.x86_64 1/1
[INFO] Executing post installation scripts…
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database Free, optionally modify the parameters in ‚/etc/sysconfig/oracle-free-23c.conf‘ and then run ‚/etc/init.d/oracle-free-23c configure‘ as root.
Verifying : oracle-database-free-23c-1.0-1.x86_64 1/1
Installed products updated.
Installed:
oracle-database-free-23c-1.0-1.x86_64
Complete!
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts: <password>
Confirm the password: <password>
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
******
Enter SYSTEM user password:
***********
Enter PDBADMIN User Password:
*******
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file „/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log“ for further details.
Connect to Oracle Database using one of the connect strings:
Pluggable database: VA-RHEL8-01/FREEPDB1
Multitenant container database: VA-RHEL8-01
[root@VA-RHEL8-01 ~]#
[root@VA-RHEL8-01 ~]# su – oracle
[oracle@VA-RHEL8-01 ~]$ vi ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=FREE
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@VA-RHEL8-01 ~]$
[oracle@VA-RHEL8-01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 – Developer-Release on Mon Apr 10 19:03:06 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 1608409464 bytes
Fixed Size 10043768 bytes
Variable Size 436207616 bytes
Database Buffers 1157627904 bytes
Redo Buffers 4530176 bytes
Database mounted.
Database opened.
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
SQL> exit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release
Version 23.2.0.0.0
[oracle@VA-RHEL8-01 ~]$
[oracle@VA-RHEL8-01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 – Developer-Release on Mon Apr 10 19:07:19 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release
Version 23.2.0.0.0
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-Release
Version 23.2.0.0.0
[oracle@VA-RHEL8-01 ~]$
Posted in Administration, ORACLE Database, RedHat Solution | No Comments »
