Failed to restart ntpd.service: Unit ntpd.service not found.
Oracle Linux: How to Start NTP Service With Slewing Enabled (Doc ID 2422934.1)
yum clean all && yum makecache
history
cat /etc/resolv.conf
# Generated by NetworkManager
#search lan
nameserver 127.0.0.1
search localdomain
nameserver 192.168.1.214
nameserver 9.9.9.9
yum install ntp
{
echo OPTIONS=\"-x -u ntp:ntp -p /var/run/ntpd.pid\"
} > /etc/sysconfig/ntpd
==
{ echo OPTIONS=\"-x -u ntp:ntp -p /var/run/ntpd.pid\"; } > /etc/sysconfig/ntpd
systemctl restart ntpd.service
cat /etc/sysconfig/ntpd
cat /etc/ntp.conf
server 0.rhel.pool.ntp.org iburst
server 1.rhel.pool.ntp.org iburst
server 2.rhel.pool.ntp.org iburst
server 3.rhel.pool.ntp.org iburst
[root@ora-rac1 ~]#
systemctl restart ntpd.service
[root@ora-rac1 ~]# ps -ef|grep -i ntpd
ntp 14258 1 0 08:06 ? 00:00:00 /usr/sbin/ntpd -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid
root 14292 12045 0 08:07 pts/1 00:00:00 grep --color=auto -i ntpd
[root@ora-rac1 ~]# netstat -pl | grep ntp
udp 0 0 ora-rac1:ntp 0.0.0.0:* 14258/ntpd
udp 0 0 ora-rac1-priv.uk.co:ntp 0.0.0.0:* 14258/ntpd
udp 0 0 ora-rac1.uk.com:ntp 0.0.0.0:* 14258/ntpd
udp 0 0 localhost:ntp 0.0.0.0:* 14258/ntpd
udp 0 0 0.0.0.0:ntp 0.0.0.0:* 14258/ntpd
udp6 0 0 ora-rac1:ntp [::]:* 14258/ntpd
udp6 0 0 ora-rac1:ntp [::]:* 14258/ntpd
udp6 0 0 ora-rac1:ntp [::]:* 14258/ntpd
udp6 0 0 localhost:ntp [::]:* 14258/ntpd
udp6 0 0 ora-rac1:ntp [::]:* 14258/ntpd
udp6 0 0 [::]:ntp [::]:* 14258/ntpd
[root@ora-rac1 ~]# pstree -l -s -p -a 14258
systemd,1 --switched-root --system --deserialize 22
└─ntpd,14258 -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid
[root@ora-rac1 ~]# ps -P 14258
PID PSR TTY STAT TIME COMMAND
14258 0 ? Ss 0:00 /usr/sbin/ntpd -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid
[root@ora-rac1 ~]#
=========
yum install ntp -y
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package ntp.x86_64 0:4.2.6p5-29.el7.centos.2 will be installed
--> Processing Dependency: libopts.so.25()(64bit) for package: ntp-4.2.6p5-29.el7.centos.2.x86_64
--> Running transaction check
---> Package autogen-libopts.x86_64 0:5.18-5.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================================
Installing:
ntp x86_64 4.2.6p5-29.el7.centos.2 base 549 k
Installing for dependencies:
autogen-libopts x86_64 5.18-5.el7 base 66 k
Transaction Summary
==============================================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 615 k
Installed size: 1.5 M
Downloading packages:
No Presto metadata available for base
(1/2): autogen-libopts-5.18-5.el7.x86_64.rpm | 66 kB 00:00:00
(2/2): ntp-4.2.6p5-29.el7.centos.2.x86_64.rpm | 549 kB 00:00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 2.2 MB/s | 615 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : autogen-libopts-5.18-5.el7.x86_64 1/2
Installing : ntp-4.2.6p5-29.el7.centos.2.x86_64 2/2
warning: /etc/ntp.conf created as /etc/ntp.conf.rpmnew
warning: /etc/sysconfig/ntpd created as /etc/sysconfig/ntpd.rpmnew
Verifying : autogen-libopts-5.18-5.el7.x86_64 1/2
Verifying : ntp-4.2.6p5-29.el7.centos.2.x86_64 2/2
Installed:
ntp.x86_64 0:4.2.6p5-29.el7.centos.2
Dependency Installed:
autogen-libopts.x86_64 0:5.18-5.el7
Complete!
[root@ora-rac2 ~]#
Friday, 29 November 2024
How to install NTP ?
Tuesday, 26 November 2024
Oracle Grid home and Grid base path
https://docs.oracle.com/en/database/oracle/////oracle-database/19/cwlin/about-creating-oracle-base-oracle-home-directories.html mkdir -p /u01/app/19.0.0/grid mkdir -p /u01/app/grid mkdir -p /u01/app/oracle chown -R grid:oinstall /u01 chown oracle:oinstall /u01/app/oracle chmod -R 775 /u01/
Friday, 22 November 2024
oracleasm module not loaded
[root@oraafd ~]# oracleasm createdisk DATA1 /dev/sdb1
oracleasm module not loaded or /dev/oracleasm not mounted.
oracleasm init <<<< run this command
[root@oraafd ~]# df -ha |grep -i oracleasm
oracleasmfs 0 0 0 - /dev/oracleasm
[root@oraafd ~]#
[root@oraafd ~]# oracleasm createdisk DATA1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oraafd ~]#
*********************************************************************************
[root@localhost rpm]# oracleasm configure -i
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: grid
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@localhost rpm]# /usr/sbin/oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@localhost rpm]#
dmesg | grep Linux
oracleasm configure -i
[root@localhost rpm]# oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=grid
ORACLEASM_GID=oinstall
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_SCAN_DIRECTORIES=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
[root@localhost rpm]#
/usr/sbin/oracleasm init
systemctl enable oracleasm
systemctl start oracleasm
in CentOS oracleasm >= 1.0.4 is needed by oracleasmlib-2.0.12-1.el7.x86_64
in CentOS oracleasm >= 1.0.4 is needed by oracleasmlib-2.0.12-1.el7.x86_64
=======================================
download link
https://www.oracle.com/linux/downloads/linux-asmlib-v8-downloads.html
https://public-yum.oracle.com/repo/OracleLinux/OL8/addons/x86_64/index.html
Oracle ASMLib Software Update and Support Policy (Doc ID 1089399.1)
https://public-yum.oracle.com/repo/OracleLinux/OL8/5/baseos/base/x86_64/index.html kmod
Oracle ASMLib 3.0 oracleasmlib-3.0.0.-13.el8.x86_64.rpm Installing and Configuring Oracle ASMLib v3
Oracle ASMLib 2.0 oracleasmlib-2.0.17-1.el8.x86_64.rpm Customers should upgrade to ASMLib v3
=======================================================================================================================================
[root@oraafd ~]# cat /etc/redhat-release
CentOS Linux release 8.5.2111
[root@oraafd ~]#
[root@oraafd ~]# ls -ltr
total 188
-rw-r--r-- 1 root root 27092 Jun 1 2020 oracleasmlib-2.0.17-1.el8.x86_64.rpm
-rw-r--r-- 1 root root 18204 Nov 21 13:54 oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 31828 Nov 21 14:01 oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
-rw-r--r-- 1 root root 99852 Nov 22 12:06 oracleasm-support-2.1.12-1.el8.x86_64.rpm
[root@oraafd ~]# yum localinstall ./oracleasm-support-2.1.12-1.el8.x86_64.rpm ./oracleasmlib-2.0.17-1.el8.x86_64.rpm
Last metadata expiration check: 20:41:08 ago on Thu 21 Nov 2024 15:27:04 GMT.
Error:
Problem: conflicting requests
- nothing provides oracleasm >= 1.0.4 needed by oracleasmlib-2.0.17-1.el8.x86_64
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)
[root@oraafd ~]# yum localinstall oracleasmlib-2.0.17-1.el8.x86_64.rpm
Last metadata expiration check: 20:41:34 ago on Thu 21 Nov 2024 15:27:04 GMT.
Error:
Problem: conflicting requests
- nothing provides oracleasm >= 1.0.4 needed by oracleasmlib-2.0.17-1.el8.x86_64
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)
use below command
###################################################################################################################################
[root@oraafd ~]# rpm -Uvh --nodeps --force oracleasmlib-2.0.17-1.el8.x86_64.rpm
warning: oracleasmlib-2.0.17-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:oracleasmlib-2.0.17-1.el8 ################################# [100%]
########################################################################################################################################################
[root@oraafd ~]# yum localinstall oracleasm-support-2.1.12-1.el8.x86_64.rpm
Last metadata expiration check: 20:43:51 ago on Thu 21 Nov 2024 15:27:04 GMT.
Dependencies resolved.
==============================================================================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================================================================
Installing:
oracleasm-support x86_64 2.1.12-1.el8 @commandline 98 k
Transaction Summary
==============================================================================================================================================================================================================
Install 1 Package
Total size: 98 k
Installed size: 349 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : oracleasm-support-2.1.12-1.el8.x86_64 1/1
Running scriptlet: oracleasm-support-2.1.12-1.el8.x86_64 1/1
Note: Forwarding request to 'systemctl enable oracleasm.service'.
Synchronizing state of oracleasm.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable oracleasm
Created symlink /etc/systemd/system/multi-user.target.wants/oracleasm.service → /usr/lib/systemd/system/oracleasm.service.
Verifying : oracleasm-support-2.1.12-1.el8.x86_64 1/1
Installed:
oracleasm-support-2.1.12-1.el8.x86_64
Complete!
[root@oraafd ~]# yum localinstall ./kmod-redhat-oracleasm-2.0.8-12.0.1.el8.x86_64.rpm
Last metadata expiration check: 0:12:17 ago on Fri 22 Nov 2024 12:11:05 GMT.
Dependencies resolved.
==============================================================================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================================================================
Installing:
kmod-redhat-oracleasm x86_64 8:2.0.8-12.0.1.el8 @commandline 44 k
Transaction Summary
==============================================================================================================================================================================================================
Install 1 Package
Total size: 44 k
Installed size: 138 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64 1/1
Running scriptlet: kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64 1/1
Verifying : kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64 1/1
Installed:
kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64
Complete!
==========
[root@oraafd ~]# rpm -qa | grep oracleasm
kmod-redhat-oracleasm-2.0.8-12.0.1.el8.x86_64
oracleasmlib-2.0.17-1.el8.x86_64
oracleasm-support-2.1.12-1.el8.x86_64
[root@oraafd ~]#
=======================
another installation
[root@localhost ~]# ls -ltr *.rpm -rw-r--r-- 1 root root 18204 Dec 9 12:22 oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm -rw-r--r-- 1 root root 110724 Dec 9 14:51 oracleasm-support-3.0.0-6.el8.x86_64.rpm -rw-r--r-- 1 root root 44920 Dec 9 14:56 kmod-redhat-oracleasm-2.0.8-12.0.1.el8.x86_64.rpm -rw-r--r-- 1 root root 47512 Dec 9 15:33 oracleasmlib-3.0.0-13.el8.x86_64.rpm
[root@localhost ~]# yum localinstall oracleasm-support-3.0.0-6.el8.x86_64.rpm
Last metadata expiration check: 0:32:00 ago on Tue 10 Dec 2024 08:12:01 GMT.
Error:
Problem: conflicting requests
- nothing provides libbpf >= 0.6.0 needed by oracleasm-support-3.0.0-6.el8.x86_64
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)
[root@localhost ~]# yum localinstall oracleasmlib-3.0.0-13.el8.x86_64.rpm
Last metadata expiration check: 0:33:24 ago on Tue 10 Dec 2024 08:12:01 GMT.
Dependencies resolved.
===============================================================================================================================================================================================================
Package Architecture Version Repository Size
===============================================================================================================================================================================================================
Installing:
oracleasmlib x86_64 3.0.0-13.el8 @commandline 46 k
Transaction Summary
===============================================================================================================================================================================================================
Install 1 Package
Total size: 46 k
Installed size: 91 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : oracleasmlib-3.0.0-13.el8.x86_64 1/1
Running scriptlet: oracleasmlib-3.0.0-13.el8.x86_64 1/1
Verifying : oracleasmlib-3.0.0-13.el8.x86_64 1/1
Installed:
oracleasmlib-3.0.0-13.el8.x86_64
=
error
[root@localhost ~]# yum localinstall oracleasm-support-3.0.0-6.el8.x86_64.rpm
Last metadata expiration check: 0:33:36 ago on Tue 10 Dec 2024 08:12:01 GMT.
Error:
Problem: conflicting requests
- nothing provides libbpf >= 0.6.0 needed by oracleasm-support-3.0.0-6.el8.x86_64
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)
[root@localhost ~]# dnf install oracleasm-support
Last metadata expiration check: 0:34:06 ago on Tue 10 Dec 2024 08:12:01 GMT.
No match for argument: oracleasm-support
Error: Unable to find a match: oracleasm-support
====
[root@localhost ~]# rpm -Uvh --nodeps --force oracleasm-support-3.0.0-6.el8.x86_64.rpm
warning: oracleasm-support-3.0.0-6.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:oracleasm-support-3.0.0-6.el8 ################################# [100%]
Note: Forwarding request to 'systemctl enable oracleasm.service'.
Synchronizing state of oracleasm.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable oracleasm
[root@localhost ~]# yum localinstall ./kmod-redhat-oracleasm-2.0.8-12.0.1.el8.x86_64.rpm
Last metadata expiration check: 0:51:59 ago on Tue 10 Dec 2024 08:12:01 GMT.
Dependencies resolved.
===============================================================================================================================================================================================================
Package Architecture Version Repository Size
===============================================================================================================================================================================================================
Installing:
kmod-redhat-oracleasm x86_64 8:2.0.8-12.0.1.el8 @commandline 44 k
Transaction Summary
===============================================================================================================================================================================================================
Install 1 Package
Total size: 44 k
Installed size: 138 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64 1/1
Running scriptlet: kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64 1/1
Verifying : kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64 1/1
Installed:
kmod-redhat-oracleasm-8:2.0.8-12.0.1.el8.x86_64
Complete!
Oracle public repositories | Oracle public yum
https://public-yum.oracle.com/index.html https://public-yum.oracle.com/oracle-linux-8.html
Thursday, 21 November 2024
Oracle 19c Preinstallation RPM for CentOs
Oracle 19c Preinstallation RPM for CentOs
[root@oraafd ~]# curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 31828 100 31828 0 0 119k 0 --:--:-- --:--:-- --:--:-- 120k
======================================
[root@oraafd ~]# dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
Last metadata expiration check: 0:04:23 ago on Thu 21 Nov 2024 13:58:05 GMT.
Dependencies resolved.
===============================================================================================================================================================================================================
Package Architecture Version Repository Size
===============================================================================================================================================================================================================
Installing:
oracle-database-preinstall-19c x86_64 1.0-2.el8 @commandline 31 k
Installing dependencies:
ksh x86_64 20120801-254.el8 appstream 926 k
libXxf86dga x86_64 1.1.5-1.el8 appstream 26 k
libaio-devel x86_64 0.3.112-1.el8 baseos 19 k
libdmx x86_64 1.1.4-3.el8 appstream 22 k
libnsl x86_64 2.28-164.el8 baseos 103 k
lm_sensors-libs x86_64 3.4.0-23.20180522git70f7e08.el8 baseos 59 k
sysstat x86_64 11.7.3-6.el8 appstream 425 k
xorg-x11-utils x86_64 7.5-28.el8 appstream 136 k
Transaction Summary
===============================================================================================================================================================================================================
Install 9 Packages
Total size: 1.7 M
Total download size: 1.7 M
Installed size: 5.3 M
Downloading Packages:
(1/8): libXxf86dga-1.1.5-1.el8.x86_64.rpm 27 kB/s | 26 kB 00:00
(2/8): libdmx-1.1.4-3.el8.x86_64.rpm 23 kB/s | 22 kB 00:00
(3/8): ksh-20120801-254.el8.x86_64.rpm 908 kB/s | 926 kB 00:01
(4/8): xorg-x11-utils-7.5-28.el8.x86_64.rpm 1.9 MB/s | 136 kB 00:00
(5/8): sysstat-11.7.3-6.el8.x86_64.rpm 4.2 MB/s | 425 kB 00:00
(6/8): libnsl-2.28-164.el8.x86_64.rpm 3.0 MB/s | 103 kB 00:00
(7/8): lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64.rpm 1.6 MB/s | 59 kB 00:00
(8/8): libaio-devel-0.3.112-1.el8.x86_64.rpm 59 kB/s | 19 kB 00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.2 MB/s | 1.7 MB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 1/9
Running scriptlet: lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 1/9
Installing : sysstat-11.7.3-6.el8.x86_64 2/9
Running scriptlet: sysstat-11.7.3-6.el8.x86_64 2/9
Installing : libnsl-2.28-164.el8.x86_64 3/9
Installing : libaio-devel-0.3.112-1.el8.x86_64 4/9
Installing : libdmx-1.1.4-3.el8.x86_64 5/9
Installing : libXxf86dga-1.1.5-1.el8.x86_64 6/9
Installing : xorg-x11-utils-7.5-28.el8.x86_64 7/9
Installing : ksh-20120801-254.el8.x86_64 8/9
Running scriptlet: ksh-20120801-254.el8.x86_64 8/9
Running scriptlet: oracle-database-preinstall-19c-1.0-2.el8.x86_64 9/9
Installing : oracle-database-preinstall-19c-1.0-2.el8.x86_64 9/9
Running scriptlet: oracle-database-preinstall-19c-1.0-2.el8.x86_64 9/9
Verifying : ksh-20120801-254.el8.x86_64 1/9
Verifying : libXxf86dga-1.1.5-1.el8.x86_64 2/9
Verifying : libdmx-1.1.4-3.el8.x86_64 3/9
Verifying : sysstat-11.7.3-6.el8.x86_64 4/9
Verifying : xorg-x11-utils-7.5-28.el8.x86_64 5/9
Verifying : libaio-devel-0.3.112-1.el8.x86_64 6/9
Verifying : libnsl-2.28-164.el8.x86_64 7/9
Verifying : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 8/9
Verifying : oracle-database-preinstall-19c-1.0-2.el8.x86_64 9/9
Installed:
ksh-20120801-254.el8.x86_64 libXxf86dga-1.1.5-1.el8.x86_64 libaio-devel-0.3.112-1.el8.x86_64 libdmx-1.1.4-3.el8.x86_64 libnsl-2.28-164.el8.x86_64
lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 oracle-database-preinstall-19c-1.0-2.el8.x86_64 sysstat-11.7.3-6.el8.x86_64 xorg-x11-utils-7.5-28.el8.x86_64
Complete!
[root@oraafd ~]#
Wednesday, 20 November 2024
How to install pre install oracle rpm ? oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
How to install pre install oracle rpm ?
on
[root@oraasm ~]# cat /etc/redhat-release
CentOS Stream release 8
[root@oraasm ~]#
[root@oraasm ~]# curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 31828 100 31828 0 0 383k 0 --:--:-- --:--:-- --:--:-- 388k
[root@oraasm ~]# ls -ltr
total 40
-rw-------. 1 root root 1503 Nov 20 11:55 anaconda-ks.cfg
-rw-r--r--. 1 root root 1669 Nov 20 11:57 initial-setup-ks.cfg
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Videos
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Templates
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Public
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Pictures
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Music
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Downloads
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Documents
drwxr-xr-x. 2 root root 6 Nov 20 12:21 Desktop
-rw-r--r--. 1 root root 31828 Nov 20 14:12 oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
[root@oraasm ~]# dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
Last metadata expiration check: 1:30:29 ago on Wed 20 Nov 2024 12:42:46 GMT.
Dependencies resolved.
==============================================================================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================================================================
Installing:
oracle-database-preinstall-19c x86_64 1.0-2.el8 @commandline 31 k
Installing dependencies:
ksh x86_64 20120801-267.el8 appstream 927 k
libaio-devel x86_64 0.3.112-1.el8 baseos 19 k
libnsl x86_64 2.28-251.el8.2 baseos 110 k
lm_sensors-libs x86_64 3.4.0-23.20180522git70f7e08.el8 baseos 59 k
sysstat x86_64 11.7.3-12.el8 appstream 443 k
Transaction Summary
==============================================================================================================================================================================================================
Install 6 Packages
Total size: 1.6 M
Total download size: 1.5 M
Installed size: 4.9 M
Downloading Packages:
(1/5): sysstat-11.7.3-12.el8.x86_64.rpm 1.7 MB/s | 443 kB 00:00
(2/5): libnsl-2.28-251.el8.2.x86_64.rpm 4.3 MB/s | 110 kB 00:00
(3/5): libaio-devel-0.3.112-1.el8.x86_64.rpm 53 kB/s | 19 kB 00:00
(4/5): lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64.rpm 373 kB/s | 59 kB 00:00
(5/5): ksh-20120801-267.el8.x86_64.rpm 823 kB/s | 927 kB 00:01
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.3 MB/s | 1.5 MB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 1/6
Running scriptlet: lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 1/6
Installing : sysstat-11.7.3-12.el8.x86_64 2/6
Running scriptlet: sysstat-11.7.3-12.el8.x86_64 2/6
Installing : libnsl-2.28-251.el8.2.x86_64 3/6
Installing : libaio-devel-0.3.112-1.el8.x86_64 4/6
Installing : ksh-20120801-267.el8.x86_64 5/6
Running scriptlet: ksh-20120801-267.el8.x86_64 5/6
Running scriptlet: oracle-database-preinstall-19c-1.0-2.el8.x86_64 6/6
Installing : oracle-database-preinstall-19c-1.0-2.el8.x86_64 6/6
Running scriptlet: oracle-database-preinstall-19c-1.0-2.el8.x86_64 6/6
Verifying : ksh-20120801-267.el8.x86_64 1/6
Verifying : sysstat-11.7.3-12.el8.x86_64 2/6
Verifying : libaio-devel-0.3.112-1.el8.x86_64 3/6
Verifying : libnsl-2.28-251.el8.2.x86_64 4/6
Verifying : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 5/6
Verifying : oracle-database-preinstall-19c-1.0-2.el8.x86_64 6/6
Installed:
ksh-20120801-267.el8.x86_64 libaio-devel-0.3.112-1.el8.x86_64 libnsl-2.28-251.el8.2.x86_64 lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 oracle-database-preinstall-19c-1.0-2.el8.x86_64
sysstat-11.7.3-12.el8.x86_64
Complete!
[root@oraasm ~]#
======
oracleasm configure -i
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Maximum number of disks that may be used in ASM system [2048]:
Enable iofilter if kernel supports it (y/n) [y]: y
Writing Oracle ASM library driver configuration: done[root@oragrid ~]# oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=grid
ORACLEASM_GID=asmadmin
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_SCAN_DIRECTORIES=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
ORACLEASM_CONFIG_MAX_DISKS="2048"
ORACLEASM_ENABLE_IOFILTER="true"
[root@oragrid ~]# oracleasm status
Checking if the oracleasm kernel module is loaded: yes
Checking if /dev/oracleasm is mounted: yes
Checking which I/O Interface is in use: oracleasm driver (KABI_V2)
cat /etc/sysconfig/oracleasm
#
# This is a configuration file for automatic loading of the Oracle
# Automatic Storage Management library kernel driver. It is generated
# By running /etc/init.d/oracleasm configure. Please use that method
# to modify this file
#
# ORACLEASM_ENABLED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true
# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=grid
# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=asmadmin
# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.
ORACLEASM_SCANBOOT=true
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER=""
# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE=""
# ORACLEASM_SCAN_DIRECTORIES: Scan disks under these directories
ORACLEASM_SCAN_DIRECTORIES=""
# ORACLEASM_USE_LOGICAL_BLOCK_SIZE: 'true' means use the logical block size
# reported by the underlying disk instead of the physical. The default
# is 'false'
ORACLEASM_USE_LOGICAL_BLOCK_SIZE=false
# ORACLEASM_CONFIG_MAX_DISKS: Maximum number of ASM disks that may be
# used in the system. The default is 2048.
ORACLEASM_CONFIG_MAX_DISKS=2048
# ORACLEASM_ENABLE_IOFILTER: 'true' means to setup iofilter for ASM disks
# if the kernel supports BPF.
ORACLEASM_ENABLE_IOFILTER=true
oracleasm configure oracleasm status /usr/sbin/oracleasm init oracleasm status
https://docs.oracle.com/en/database/oracle/oracle-database/23/ladbi/installing-and-configuring-oracle-asmlib-software.html
systemctl enable oracleasm systemctl start oracleasm systemctl status oracleasm
for new disk !!!
[root@oragrid ~]# ls -ltr /dev/sd* brw-rw---- 1 root disk 8, 0 Dec 10 14:32 /dev/sda brw-rw---- 1 root disk 8, 2 Dec 10 14:32 /dev/sda2 brw-rw---- 1 root disk 8, 16 Dec 10 14:32 /dev/sdb <<<< add new disk on brw-rw---- 1 root disk 8, 1 Dec 10 14:32 /dev/sda1 [root@oragrid ~]# date Tue 10 Dec 14:54:10 GMT 2024
[root@oragrid ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 100G 0 disk ├─sda1 8:1 0 1G 0 part /boot └─sda2 8:2 0 97.6G 0 part ├─cl-root 253:0 0 18G 0 lvm / ├─cl-swap 253:1 0 6.6G 0 lvm [SWAP] ├─cl-home 253:2 0 25G 0 lvm /home └─cl-u01 253:3 0 48G 0 lvm /u01 sdb 8:16 0 40G 0 disk sr0 11:0 1 51M 0 rom
[root@oragrid ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.32.1).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table.
Created a new DOS disklabel with disk identifier 0x578291d8.
Command (m for help): n
Partition type
p primary (0 primary, 0 extended, 4 free)
e extended (container for logical partitions)
Select (default p):
Using default response p.
Partition number (1-4, default 1):
First sector (2048-83886079, default 2048):
Last sector, +sectors or +size{K,M,G,T,P} (2048-83886079, default 83886079):
Created a new partition 1 of type 'Linux' and of size 40 GiB.
Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.
[root@oragrid ~]# ls -ltr /dev/sd*
brw-rw---- 1 root disk 8, 0 Dec 10 14:32 /dev/sda
brw-rw---- 1 root disk 8, 2 Dec 10 14:32 /dev/sda2
brw-rw---- 1 root disk 8, 1 Dec 10 14:32 /dev/sda1
brw-rw---- 1 root disk 8, 16 Dec 10 14:56 /dev/sdb
brw-rw---- 1 root disk 8, 17 Dec 10 14:56 /dev/sdb1
[root@oragrid ~]#
[root@oragrid ~]# oracleasm createdisk DATA1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oragrid ~]#
[root@oragrid ~]# blkid | grep asm
/dev/sdb1: LABEL="DATA1" TYPE="oracleasm" PARTUUID="578291d8-01"
[root@oragrid ~]# oracleasm listdisks
DATA1
/usr/sbin/oracleasm scandisks
ls -ltra /dev/oracleasm/disks/*
brw-rw---- 1 grid asmadmin 8, 17 Dec 10 14:57 /dev/oracleasm/disks/DATA1
vm tool on vm box
How to install Vm tool on vm box ?
you will get below media
[root@localhost VBox_GAs_7.0.10]# df -Ph
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.1G 0 3.1G 0% /dev
tmpfs 3.1G 0 3.1G 0% /dev/shm
tmpfs 3.1G 18M 3.1G 1% /run
tmpfs 3.1G 0 3.1G 0% /sys/fs/cgroup
/dev/mapper/cs-root 44G 6.2G 38G 15% /
/dev/sda1 1014M 272M 743M 27% /boot
tmpfs 626M 20K 626M 1% /run/user/1000
/dev/sr0 52M 52M 0 100% /run/media/root/VBox_GAs_7.0.10 <<<----
[root@localhost VBox_GAs_7.0.10]#
====
[root@localhost ~]# cd /run/media/root/VBox_GAs_7.0.10
[root@localhost VBox_GAs_7.0.10]# pwd
/run/media/root/VBox_GAs_7.0.10
[root@localhost VBox_GAs_7.0.10]# ls -ltr
total 42179
-r--r--r--. 1 root root 1048 Jan 31 2023 AUTORUN.INF
-r--r--r--. 1 root root 261 Jan 31 2023 windows11-bypass.reg
-r--r--r--. 1 root root 9402368 Jul 12 2023 VBoxSolarisAdditions.pkg
-r-xr-xr-x. 1 root root 4224 Jul 12 2023 VBoxDarwinAdditionsUninstall.tool
-r-xr-xr-x. 1 root root 5096 Jul 12 2023 runasroot.sh
-r-xr-xr-x. 1 root root 6848 Jul 12 2023 autorun.sh
-r-xr-xr-x. 1 root root 6296008 Jul 12 2023 VBoxLinuxAdditions.run
-r--r--r--. 1 root root 2199917 Jul 12 2023 VBoxDarwinAdditions.pkg
-r-xr-xr-x. 1 root root 243712 Jul 12 2023 VBoxWindowsAdditions.exe
-r-xr-xr-x. 1 root root 9262048 Jul 12 2023 VBoxWindowsAdditions-x86.exe
-r-xr-xr-x. 1 root root 15760728 Jul 12 2023 VBoxWindowsAdditions-amd64.exe
-r--r--r--. 1 root root 592 Jul 12 2023 TRANS.TBL
dr-xr-xr-x. 2 root root 2828 Jul 12 2023 OS2
dr-xr-xr-x. 2 root root 1252 Jul 12 2023 NT3x
dr-xr-xr-x. 2 root root 1468 Jul 12 2023 cert
[root@localhost VBox_GAs_7.0.10]# ./VBoxLinuxAdditions.run
Verifying archive integrity... 100% MD5 checksums are OK. All good.
Uncompressing VirtualBox 7.0.10 Guest Additions for Linux 100%
VirtualBox Guest Additions installer
Removing installed version 7.0.10 of VirtualBox Guest Additions...
Copying additional installer modules ...
Installing additional modules ...
VirtualBox Guest Additions: Starting.
VirtualBox Guest Additions: Setting up modules
VirtualBox Guest Additions: Building the VirtualBox Guest Additions kernel
modules. This may take a while.
VirtualBox Guest Additions: To build modules for other installed kernels, run
VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup <version>
VirtualBox Guest Additions: or
VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup all
VirtualBox Guest Additions: Building the modules for kernel
4.18.0-552.1.1.el8.x86_64.
VirtualBox Guest Additions: Look at /var/log/vboxadd-setup.log to find out what
went wrong
File context for /opt/VBoxGuestAdditions-7.0.10/other/mount.vboxsf already defined, modifying instead
[root@localhost VBox_GAs_7.0.10]#
Thursday, 14 November 2024
Cent OS repositories
vi /etc/yum.repos.d/CentOS-Base.repo copy/paste the following [base] name=CentOS-$releasever - Base baseurl=http://vault.centos.org/7.9.2009/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 [updates] name=CentOS-$releasever - Updates baseurl=http://vault.centos.org/7.9.2009/updates/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 [extras] name=CentOS-$releasever - Extras baseurl=http://vault.centos.org/7.9.2009/extras/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 [centosplus] name=CentOS-$releasever - Plus baseurl=http://vault.centos.org/7.9.2009/centosplus/$basearch/ gpgcheck=1 enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 yum clean all
======================
[root@localhost VBox_GAs_7.0.10]# yum update -y CentOS Stream 8 - AppStream 0.0 B/s | 0 B 00:03 Errors during downloading metadata for repository 'appstream': - Curl error (6): Couldn't resolve host name for http://mirrorlist.centos.org/?release=8-stream&arch=x86_64&repo=AppStream&infra=stock [Could not resolve host: mirrorlist.centos.org] Error: Failed to download metadata for repo 'appstream': Cannot prepare internal mirrorlist: Curl error (6): Couldn't resolve host name for http://mirrorlist.centos.org/?release=8-stream&arch=x86_64&repo=AppStream&infra=stock [Could not resolve host: mirrorlist.centos.org]
Do Below !!!!!!
===================================== [root@localhost VBox_GAs_7.0.10]# cd /etc/yum.repos.d/ [root@localhost yum.repos.d]# sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-* or [root@oraafd yum.repos.d]# sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-* [root@oraafd yum.repos.d]# yum clean all ==================================== [root@localhost yum.repos.d]# yum update -y CentOS Stream 8 - AppStream 14 MB/s | 29 MB 00:02 CentOS Stream 8 - BaseOS 19 MB/s | 10 MB 00:00 CentOS Stream 8 - Extras 127 kB/s | 18 kB 00:00 CentOS Stream 8 - Extras common packages 56 kB/s | 8.0 kB 00:00 Dependencies resolved. ============================================================================================================================================================================================================== Package Architecture Version Repository Size ============================================================================================================================================================================================================== Installing: kernel x86_64 4.18.0-553.6.1.el8 baseos 10 M Upgrading: bpftool x86_64 4.18.0-553.6.1.el8 baseos 11 M firefox x86_64 115.11.0-1.el8 appstream 116 M glibc x86_64 2.28-251.el8.2 baseos 2.3 M glibc-all-langpacks x86_64 2.28-251.el8.2 baseos 26 M glibc-common x86_64 2.28-251.el8.2 baseos 1.0 M glibc-devel x86_64 2.28-251.el8.2 baseos 89 k glibc-gconv-extra x86_64 2.28-251.el8.2 baseos 1.7 M glibc-headers x86_64 2.28-251.el8.2 baseos 585 k glibc-langpack-en x86_64 2.28-251.el8.2 baseos 941 k hostname x86_64 3.20-7.el8.0.1 baseos 32 k hyperv-daemons x86_64 0-1.31.20180415git.el8 appstream 13 k hyperv-daemons-license noarch 0-1.31.20180415git.el8 appstream 20 k hypervfcopyd x86_64 0-1.31.20180415git.el8 appstream 19 k hypervkvpd x86_64 0-1.31.20180415git.el8 appstream 28 k hypervvssd x86_64 0-1.31.20180415git.el8 appstream 21 k jq x86_64 1.6-9.el8 appstream 203 k kernel-headers x86_64 4.18.0-553.6.1.el8 baseos 12 M kernel-tools x86_64 4.18.0-553.6.1.el8 baseos 11 M kernel-tools-libs x86_64 4.18.0-553.6.1.el8 baseos 10 M less x86_64 530-3.el8 baseos 165 k libtirpc x86_64 1.1.4-12.el8 baseos 114 k python3-perf x86_64 4.18.0-553.6.1.el8 baseos 10 M sos noarch 4.7.1-2.el8 baseos 1.2 M tuned noarch 2.22.1-4.el8.1 baseos 460 k xorg-x11-server-Xwayland x86_64 21.1.3-16.el8 appstream 967 k Installing dependencies: kernel-core x86_64 4.18.0-553.6.1.el8 baseos 44 M kernel-modules x86_64 4.18.0-553.6.1.el8 baseos 36 M Installing weak dependencies: kernel-devel x86_64 4.18.0-553.6.1.el8 baseos 28 M Transaction Summary ============================================================================================================================================================================================================== Install 4 Packages Upgrade 25 Packages
======
cat /etc/centos-release CentOS Linux release 8.5.2111
yum clean all
yum update -y sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-* sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-* yum update -y
===================
cd /etc/yum.repos.d
[root@localhost yum.repos.d]# pwd /etc/yum.repos.d
then ..
sed -i s/mirror.centos.org/vault.centos.org/g /etc/yum.repos.d/*.repo
sed -i s/^#.*baseurl=http/baseurl=http/g /etc/yum.repos.d/*.repo
sed -i s/^mirrorlist=http/#mirrorlist=http/g /etc/yum.repos.d/*.repo
sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-*
sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-*
Wednesday, 13 November 2024
Oracle 23ai Tablespace shrink
Table space shrink on 23ai !!!!
Using the shrink_tablespace procedure is an easy way to reorganize objects, so we can reclaim free space in the tablespace. Oracle Database 23ai introduces the shrink_tablespace procedure, which simplifies the process of resizing datafiles and organizing objects. shrink_tablespace can be used in three modes: Default - You can specify the tablespace and let the process shrink the file as small as possible (this uses an online move). Analyze - Get information about the shrink prior to doing it. Force - For the instances where you don't care if the move is done online or offline, the procedure tries to move objects online (the default), and if it fails, an offline move is used. sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Wed Nov 13 09:37:45 2024 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.5.0.24.07 set serveroutput on SQL> BEGIN dbms_space.shrink_tablespace('SYSTEM', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE); END; 2 / PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / -------------------ANALYZE RESULT------------------- Total Movable Objects: 2 Total Movable Size(GB): .06 Original Datafile Size(GB): 6.88 Suggested Target Size(GB): 1.11 Process Time: +00 00:00:01.177637 PL/SQL procedure successfully completed. SQL> execute dbms_space.shrink_tablespace('SYSTEM'); -------------------SHRINK RESULT------------------- Total Moved Objects: 2 Total Moved Size(GB): .06 Original Datafile Size(GB): 6.88 New Datafile Size(GB): 6.21 Process Time: +00 00:00:26.764780 PL/SQL procedure successfully completed.
Monday, 11 November 2024
DBMS_METADATA ......
Oracle DBMS_METADATA ....
https://anuj-singh.blogspot.com/2018/01/oracle-profile-metadata.html https://anuj-singh.blogspot.com/2012/04/oracle-job-metadata.html
from https://github.com/oraclebase/dba/blob/master/
--- Table and index metadata !!!SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
define tab='XXXXXXX'
SELECT DBMS_METADATA.get_ddl ('TABLE', TABLE_name, owner)
FROM all_tables
WHERE 1=1
--and owner = UPPER('1')
AND table_name = '&tab'
;
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM all_indexes
WHERE 1=1
--and owner = UPPER('1')
AND table_name = '&tab'
;SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 80 LINESIZE 100 FEEDBACK OFF VERIFY OFF TRIMSPOOL ONdefine O='ANUJ'
define sequence_name='S'
SET LONG 50000 LONGCHUNKSIZE 50000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
FROM all_sequences
WHERE sequence_owner = UPPER('&O')
AND sequence_name = DECODE(UPPER('&sequence_name'), 'ALL', sequence_name, UPPER('&sequence_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
===========
define O='ANUJ'
define SYNONYM='S'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('SYNONYM', synonym_name, owner)
FROM all_synonyms
WHERE owner = UPPER('&O')
--AND synonym_name = DECODE(UPPER('&SYNONYM'), 'ALL', synonym_name, UPPER('&SYNONYM'))
;
SET PAGESIZE 14 FEEDBACK ON VERIFY ON
=================
define O='ANUJ'
define table_name='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true)
;
END;
/
SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner)
FROM all_constraints
WHERE owner = UPPER('&O')
AND table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
AND constraint_type IN ('U', 'P');
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
============================
define O='ANUJ'
define table_name='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
-- Uncomment the following lines if you need them.
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE owner = UPPER('&O')
AND table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
====================================
define O='ANUJ'
define OBJECT_GRANT='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT', UPPER('&OBJECT_GRANT'), UPPER('&O')) from dual
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
======================================
define O='ANUJ'
define table_name='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
-- Uncomment the following lines if you need them.
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM all_indexes
WHERE owner = UPPER('&O')
AND table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
===
define O='ANUJ'
define table_name='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM all_triggers
WHERE table_owner = UPPER('&O')
AND table_name = UPPER('&table_name');
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
==
define tablespace_name='SYSTEM'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM dba_tablespaces
WHERE tablespace_name = DECODE(UPPER('&tablespace_name'), 'ALL', tablespace_name, UPPER('&tablespace_name'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
====
define O='ANUJ'
define trigger_name='T'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM all_triggers
WHERE owner = UPPER('&O')
AND trigger_name = DECODE(UPPER('&trigger_name'), 'ALL', trigger_name, UPPER('&trigger_name'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
===
define O='ANUJ'
define view_name='T'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, owner)
FROM all_views
WHERE owner = UPPER('&O')
AND view_name = DECODE(UPPER('&view_name'), 'ALL', view_name, UPPER('&view_name'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
========define v_owner='ANUJ'
define v_table_name='TEST'
set feed off term off
select upper('&2') v_table_name from dual;
set feed on term on
var v_owner varchar2(30)
var v_table_name varchar2(30)
begin
:v_owner := '&&v_owner';
:v_table_name := '&&v_table_name';
end;
/
set pagesize 50000 linesize 200 trimspool on long 2000000
col ddl format a150
begin
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
end;
/
set pagesize 500 linesize 200 trimspool on
col ddl format a125
prompt set echo on
prompt --########################################
prompt --## TABLE DDL
prompt --########################################
select replace(dbms_metadata.get_ddl('TABLE',:v_table_name,:v_owner),'"','') ddl from dual
/
prompt --########################################
prompt --## INDEX DDL
prompt --########################################
select replace(dbms_metadata.get_ddl('INDEX',i.index_name, i.owner),'"','') ddl
from dba_indexes i
where i.owner = :v_owner
and i.table_name = :v_table_name
/
prompt --########################################
prompt --## PRIMARY KEY
prompt --########################################
select replace(dbms_metadata.get_ddl('CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'P'
/
prompt --########################################
prompt --## FOREIGN KEYS
prompt --########################################
select replace(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'R'
/
prompt --########################################
prompt --## CHECK CONSTRAINTS
prompt --########################################
select replace(dbms_metadata.get_ddl('CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'C'
and c.generated not like 'GENERATED%'
/
undef 1 2
============
Index metadata !!!
set long 5000 pagesize 0
select DBMS_METADATA.GET_DDL('INDEX', index_name,owner)
from all_indexes
where table_name in ('XXX')
and owner='XXXX'
;
====
---materialized view
from Web--
SET serveroutput on feedback off
UNDEF v_sql
define ENTER_MVIEW_OWNER=ANUJ'
define ENTER_MVIEW_NAME='XXX_MVIEW'
DECLARE
v_task_name VARCHAR2 (100);
v_mview_owner VARCHAR2 (30) := UPPER ('&&ENTER_MVIEW_OWNER');
v_mview_name VARCHAR2 (30) := UPPER ('&&ENTER_MVIEW_NAME');
v_mview_sql VARCHAR2 (4000);
v_mview_log_sql VARCHAR2 (4000);
BEGIN
-- get mview text from data dictionary
SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
INTO v_mview_sql
FROM dba_mviews
WHERE owner = v_mview_owner
AND mview_name = v_mview_name
;
SELECT DBMS_METADATA.get_dependent_ddl ('MATERIALIZED_VIEW_LOG',
referenced_name,
referenced_owner
)
INTO v_mview_log_sql
FROM dba_dependencies
WHERE referenced_type = 'TABLE'
AND referenced_name != v_mview_name
AND owner = v_mview_owner
AND NAME = v_mview_name
;
DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
DBMS_OUTPUT.put_line ('MVIEW LOG SQL Is: ' || v_mview_log_sql);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
=====
SET serveroutput on feedback off lines 400 long 999999
DECLARE
v_task_name VARCHAR2 (100);
v_mview_sql VARCHAR2 (4000);
v_mview_log_sql VARCHAR2 (4000);
cursor c1 is select mview_name from dba_mviews where owner='ANUJ';
BEGIN
FOR MV_REC IN C1
LOOP
SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
INTO v_mview_sql
FROM dba_mviews
WHERE owner = 'ANUJ'
AND mview_name = MV_REC.mview_name;
DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
END loop;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
===========================
-- PROCEDURE text
set linesize 400 pagesize 300
col name for a20
col TEXT for a50 wrap
SELECT NAME,TYPE
--,LINE
,TEXT FROM dba_source
WHERE TYPE = 'PROCEDURE'
and NAME='XXX'
and OWNER='ANUJ'
;
define O='XXX'
define T='EMP1'
SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT REPLACE(
DBMS_METADATA.GET_DDL('FUNCTION', object_name, OWNER),
'"' || USER || '".',
''
) AS text
FROM dba_objects
WHERE 1=1
--and object_type ='FUNCTION'
and object_type = 'PACKAGE'
--and object_type = 'PROCEDURE'
and OWNER='&O'
/
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE = 'PROCEDURE'
AND OWNER='&O'
;
SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE = 'FUNCTION'
AND OWNER='&O'
;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE = 'PROCEDURE'
AND OWNER='&O'
;
*********************************************************************************
define v_owner='ANUJ'
define v_table_name='TEST'
set feed off term off
select upper('&2') v_table_name from dual;
set feed on term on
var v_owner varchar2(30)
var v_table_name varchar2(30)
begin
:v_owner := '&&v_owner';
:v_table_name := '&&v_table_name';
end;
/
set long 50000 pagesize 300 linesize 300select replace(dbms_metadata.get_ddl(o.OBJECT_TYPE,o.OBJECT_NAME , o.owner),'"','') ddl
from dba_objects o
where 1=1
--and o.owner = :v_owner
and o.OBJECT_NAME = 'LOG'
/
*********************************************************************************
set pagesize 0 long 90000
define N='LLLLL' ---object name
define OWNER='ANUJ1'
select dbms_metadata.get_ddl('FUNCTION','&N','&OWNER') FROM DUAL;
select dbms_metadata.get_ddl('PROCEDURE','&N','&OWNER') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','&N','&OWNER') FROM DUAL;
set pagesize 100 long 90000
********************************
uncomment based on your requirement
set long 200000 pages 0 lines 131 doc off
column txt format a121 word_wrapped
define O='ANUJ1'
define T='EMP'
select DBMS_METADATA.GET_DDL('INDEX',u.index_name,TABLE_OWNER) txt from DBA_INDEXES u where table_name='&T' and OWNER ='&O'
union all
--prompt DDL TABLES
--prompt ******************************
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) TABLES from DBA_TABLES where table_name='&T' and OWNER ='&O'
union all
--prompt DDL TB_CONSTRAINTS
--prompt ******************************
select dbms_metadata.get_dependent_ddl('CONSTRAINT',TABLE_NAME,OWNER) TBCONSTRAINTS from dba_constraints where table_name='&T' and OWNER ='&O'
union all
--prompt DDL REF_CONSTRAINTS
--prompt ******************************
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT',TABLE_NAME,OWNER) REFCONSTRAINTS from dba_constraints where table_name='&T' and OWNER ='&O'
union all
--prompt INDEXES
--prompt ******************************
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER(TABLE_NAME),UPPER(OWNER)) from DBA_TABLES where table_name='&T' and OWNER ='&O'
union all
--prompt DDL TRIGGERS
--prompt ******************************
select dbms_metadata.get_dependent_ddl('TRIGGER',TABLE_NAME,OWNER) from dba_triggers where table_name='&T' and OWNER ='&O'
union all
--prompt DDL VIEWS
--prompt ******************************
--select dbms_metadata.get_ddl('VIEW',view_name,OWNER) from dba_views where VIEW_NAME='&T' and OWNER ='&O'
--union all
/* SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', master, LOG_OWNER) txt
FROM dba_mview_logs
WHERE LOG_OWNER = UPPER('&O')
and master= UPPER('&T')
union all
*/
SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW_LOG', LOG_TABLE, LOG_OWNER)
FROM dba_mview_logs
WHERE LOG_OWNER = UPPER('&O')
and master= UPPER('&T')
;
set long 1000 pages 100 lines 100
MV logs row count
SET SERVEROUTPUT ON;
DECLARE
CURSOR cur IS
SELECT log_owner, log_table
FROM dba_snapshot_logs
where LOG_OWNER='ANUJ'
order by 2
;
num_rows NUMBER;
cnt NUMBER := 0;
BEGIN
FOR x IN cur LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || x.log_owner || '.' || x.log_table
INTO num_rows;
IF num_rows > 0 THEN
DBMS_OUTPUT.PUT_LINE(x.log_owner || '.' || x.log_table ||
' has ' || num_rows || ' rows');
cnt := cnt + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Cannot access ' || x.log_owner || '.' || x.log_table ||
' -> ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(cnt || ' materialized view logs have entries');
END;
/
Subscribe to:
Comments (Atom)
