Learning Oracle: 2022

Saturday, December 31, 2022

RAC Installation Step By Step

All steps will be carried out on one of the node (node1) and later we will clone to other nodes or machines:

Install the system Linux prerequisites:

The package oracle-database-preinstall-19c contains all the prerequisites on Oracle Linux using the Oracle Unbreakable Enterprise Kernel (UEK).

yum install -y oracle-database-preinstall-19c
yum install -y oracleasm-support

sysctl -p
crosscheck >>> cat /etc/sysctl.conf

Group Creation: –

groupadd -g 5001 oinstall
groupadd -g 5002 dba
groupadd -g 5003 oper
groupadd -g 5004 asmadmin
groupadd -g 5005 asmdba
groupadd -g 5006 asmoper

User Creation: –

useradd -u 5007 -g oinstall -G dba,oper,asmdba oracle
useradd -u 5008 -g oinstall -G asmadmin,asmdba,asmoper,dba grid
usermod -g oinstall -G dba,oper,asmdba oracle
usermod -g oinstall -G asmadmin,asmdba,asmoper,dba grid

passwd oracle
passwd grid

Add below entries in /etc/security/limits.conf file which will detine limits

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 134217728
oracle soft memlock 134217728

grid soft nofile 1024
grid hard nofile 65536
grid soft nproc 16384
grid hard nproc 16384
grid soft stack 10240
grid hard stack 32768
grid hard memlock 134217728
grid soft memlock 134217728

Create the directories in which the Oracle / Grid software will be installed.

mkdir -p /u01/app/grid
mkdir -p /u01/app/19c/gridhome_1
mkdir -p /u02/app/oracle
mkdir -p /u02/app/oracle/product/19c/dbhome_1
mkdir -p /u01/app/oraInventory
mkdir -p /u01/LATEST_PATCH/
chown -R grid:oinstall /u01
chown -R oracle:oinstall /u02
chown -R grid:oinstall /u01/app/oraInventory
chmod -R 775 /u01
chmod -R 775 /u02

Set bash_profile for grid and oracle user

login to grid user and set .bash_profile :

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/19c/gridhome_1
export ORACLE_SID=+ASM1
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

login to oracle user and set .bash_profile :

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1
export ORACLE_SID=racdb1
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Add the following entry in /etc/hosts file and crosscheck node reachability

Primary Site:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

# Private

192.168.10.1 node1-priv.localdomain node1-priv
192.168.10.2 node2-priv.localdomain node2-priv

# Public

192.168.56.71 node1.localdomain node1
192.168.56.72 node2.localdomain node2

# Virtual

192.168.56.81 node1-vip.localdomain node1-vip
192.168.56.82 node2-vip.localdomain node2-vip

# SCAN

192.168.56.91 node-scan.localdomain node-scan
192.168.56.92 node-scan.localdomain node-scan
192.168.56.93 node-scan.localdomain node-scan

Firewall Stop and Disable

We can open firewall after installation

systemctl stop firewalld.service
systemctl disable firewalld.service

Chrony NTP Configuration

systemctl enable chronyd.service
systemctl restart chronyd.service

Verify the network reachability using ping command:

From Node1:
ping node2
ping node2-priv

From Node2:
ping node1
ping node1-priv

Create shared ASM Disk FOR OCR, DATAFILE and ARCHIVE on Both Sites.

Add new shared disk to virtual box machines it should be sharable

Login to system and run ls /dev/sd*
/dev/sda /dev/sda1 /dev/sda2 /dev/sdb
fdisk /dev/sdb

The sequence of answers is "n", "p", "1", "Return", "Return" and "w".

Set ASM disks >>>> oracleasm configure -i
Create ASM disks >>>> oracleasm createdisk ASM1 /dev/sdb1
Scan ASM disks >>>> oracleasm scandisks
List ASM disks >>>> oracleasm listdisks

######### On Primary Site ##########

Copy software to node1 on primary site and unzip software at grid home location and rdbms home locations by login to respective users.

Setup passwordless ssh configuration

cd /u01/app/19c/gridhome_1/deinstall
./sshUserSetup.sh -user grid -hosts "node1 node2" -noPromptPassphrase -confirm -advanced

Similarly do it for oracle user

cd /u02/app/oracle/product/19c/dbhome_1/deinstall

./sshUserSetup.sh -user oracle -hosts "node1 node2" -noPromptPassphrase -confirm –advanced
./sshUserSetup.sh -user root -hosts "node1 node2" -noPromptPassphrase -confirm –advanced

Set profile and go to ORACLE_HOME location from grid user and run following command and follow the steps.

cd $ORACLE_HOME
./gridSetup.sh -applyRU /u01/LATEST_PATCH/32545008

Once Grid Installation done, Install RDBMS Software through oracle user and create RAC database on production (PROD).

./runInstaller -applyRU /u01/LATEST_PATCH/32545008
Use DBCA to create database PROD.

Physical Standby Creation Steps For Single Instance

Physical Standby Single Instance

Tuesday, December 27, 2022

How to check the OS user status

linux chage -l username ---------------> Linux
passwd -s username --------------> Solaris

chage -l oracle

Last password change : Sep 18, 2022
Password expires : Dec 17, 2022
Password inactive : Mar 17, 2023
Account expires : never
Minimum number of days between password change : 0
Maximum number of days between password change : 90
Number of days of warning before password expires : 7

chage -l grid

Last password change : Jul 21, 2022
Password expires : Oct 19, 2022
Password inactive : Jan 17, 2023
Account expires : never
Minimum number of days between password change : 0
Maximum number of days between password change : 90
Number of days of warning before password expires : 7

How to find large files

Find recently modified tables in Oracle database

select a.OWNER, a.SEGMENT_NAME "TABLE_NAME", B.OBJECT_TYPE,b.CREATED, b.LAST_DDL_TIME "MODIFIED" , a.BYTES/1024/1024 "SIZE_MB"

from dba_segments a, dba_objects b

where a.OWNER=B.OWNER

AND A.SEGMENT_NAME=B.OBJECT_NAME

AND A.SEGMENT_TYPE=B.OBJECT_TYPE

and a.owner not in (select name from system.logstdby$skip_support where action = 0);

select tab.owner as table_schema,

tab.table_name,

obj.last_ddl_time as last_modify

from all_tables tab

join all_objects obj on tab.owner = obj.owner

and tab.table_name = obj.object_name

where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',

'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',

'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',

'WKSYS','WKPROXY','WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER','DIP',

'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',

'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')

and obj.last_ddl_time > (current_date - INTERVAL '60' DAY)

order by last_modify desc;

COLUMN TABLE_NAME FORMAT A32

COLUMN OBJECT_NAME FORMAT A32

COLUMN OWNER FORMAT A10

SELECT

owner,

table_name,

TRUNC(sum(bytes)/1024/1024) Meg,

ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX')

WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

;

-- Tables + Size MB

select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB

from all_tables

where owner not like 'SYS%' -- Exclude system tables.

and num_rows > 0 -- Ignore empty Tables.

order by MB desc -- Biggest first.

;

--Tables + Rows

select owner, table_name, num_rows

from all_tables

where owner not like 'SYS%' -- Exclude system tables.

and num_rows > 0 -- Ignore empty Tables.

order by num_rows desc -- Biggest first.

;

SELECT * FROM (

SELECT

owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,

tablespace_name, extents, initial_extent,

ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg

FROM (

-- Tables

SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,

segment_name AS table_name, bytes,

tablespace_name, extents, initial_extent

FROM dba_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

UNION ALL

-- Indexes

SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,

i.table_name, s.bytes,

s.tablespace_name, s.extents, s.initial_extent

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

-- LOB Segments

UNION ALL

SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,

l.table_name, s.bytes,

s.tablespace_name, s.extents, s.initial_extent

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type = 'LOBSEGMENT'

-- LOB Indexes

UNION ALL

SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,

l.table_name, s.bytes,

s.tablespace_name, s.extents, s.initial_extent

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX'

)

WHERE owner = UPPER('&owner')

)

WHERE total_table_meg > 10

ORDER BY total_table_meg DESC, meg DESC

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type in ('TABLE','TABLE PARTITION')

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type in ('INDEX','INDEX PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX')

---WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc;

SELECT * FROM (

SELECT

owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,

tablespace_name, extents, initial_extent,

ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg

FROM (

-- Tables

SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,

segment_name AS table_name, bytes,

tablespace_name, extents, initial_extent

FROM dba_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

UNION ALL

-- Indexes

SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,

i.table_name, s.bytes,

s.tablespace_name, s.extents, s.initial_extent

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

-- LOB Segments

UNION ALL

SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,

l.table_name, s.bytes,

s.tablespace_name, s.extents, s.initial_extent

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type = 'LOBSEGMENT'

-- LOB Indexes

UNION ALL

SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,

l.table_name, s.bytes,

s.tablespace_name, s.extents, s.initial_extent

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX'

)

WHERE owner = UPPER('&owner')

)

WHERE total_table_meg > 10

ORDER BY total_table_meg DESC, meg DESC

Single Instance DB Upgrade From 12c to 19c

-Oracle 12c database should be up and running and it should be applied with latest patches
-Download latest 19c software's from oracle website.
-Also download latest Oracle Patches for 19c
-Start Installation of 19c software on separate mount point and apply latest patches which was downloaded earlier.

At this stages two separate database software will be ready one is 12cR2 and other will be 19c on two different mount points

High Level Steps:

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
mkdir -p /u02/preupgrade
Run the pre-upgrade tool from 12c home referring file of new 19c home

/u01/app/oracle/product/12c/db_1/jdk/bin/java -jar /u02/app/oracle/product/19c/db_1/rdbms/admin/preupgrade.jar DIR /u02/preupgrade

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

purge dba_recyclebin;
purge recyclebin;

select name,open_mode,log_mode from v$database;
@/u02/preupgrade/preupgrade_fixups.sql

Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

==> Need to resize datafile as per the recommendations

select file_name from dba_data_files;

alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_system_j87d12w1_.dbf' resize 1g;

alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_sysaux_j87d2j6s_.dbf' resize 700m; alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_undotbs1_j87d3mbh_.dbf' resize 600m;

select file_name from dba_temp_files;
alter database tempfile '/u01/app/oradata/PROD/datafile/o1_mf_temp_j87d525p_.tmp' resize 300m;

==>Create restore point before_upgrade_19c guarantee flashback database;

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from v$restore_point;

sqlplus / as sysdba
shutdown immediate;
exit

Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME

cp orapwtest spfiletest.ora /u02/app/oracle/product/19c/db_1/dbs
ls -lrt /u02/app/oracle/product/19c/db_1/dbs
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 7680 Sep 23 21:12 orapwtest
-rw-r-----. 1 oracle oinstall 3584 Sep 23 21:12 spfiletest.ora

Stop listener running on 12c home and start it from 19c home. Start the database from 19c ORACLE_HOME and start the upgrade.

sqlplus / as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;

Run the DB Upgrade utility

$ORACLE_HOME/bin/dbupgrade -n 8

-Check output and wait for 30 min.
-Check the upgrade summary log,
-Startup database manually and

startup;
SELECT name, open_mode, status, version from v$database, v$instance;
@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Recompile the INVALID Objects using utlrp.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql

Connect to sqlplus and run the postupgrade_fixups.sql script

sqlplus / as sysdba
@/u02/preupgrade/postupgrade_fixups.sql

Upgrade the timezone file version

sqlplus / as sysdba
SELECT version FROM v$timezone_file;

@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
@$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;

SELECT version FROM v$timezone_file;
Gather statistics on fixed objects

Connect to sqlplus as sys user and execute
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Now re-run the postupgrade_fixups.sql
select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;

Update the compatible parameter:
alter system set compatible='19.0.0' scope=spfile;

shutdown immediate;
startup;
show parameter compatible;
select name, open_mode, version from v$database, v$instance;
select name,open_mode,database_role from V$database;
show parameter cluster

set lines 200 pages 500
col owner for a15
col OBJECT_NAME for a35
select owner,object_name,object_type,status from dba_objects where status='INVALID';

col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

col ACTION_TIME for a30
col action for a30
col version for a10
col BUNDLE_SERIES for a30
col COMMENTS for a47
select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;

12c Single Instance Patching

$ORACLE_HOME/OPatch/opatch lsinv | grep -i applied
$ORACLE_HOME/OPatch/opatch lspatches | sort -nr

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/12c_Soft/32518631/32328632
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/12c_Soft/32518631/31704029

/u01/app/oracle/product/12c/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/12c/db_1 -local -silent /u01/12c_Soft/32518631/32328632
/u01/app/oracle/product/12c/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/12c/db_1 -local -silent /u01/12c_Soft/32518631/31704029

$ORACLE_HOME/OPatch/opatch lsinv | grep -i applied
$ORACLE_HOME/OPatch/opatch lspatches | sort -nr

cd $ORACLE_HOME/OPatch
./datapatch -verbose

select name,open_mode,database_role from V$database;

show parameter cluster

set lines 200 pages 500
col owner for a15
col OBJECT_NAME for a35
select owner,object_name,object_type,status from dba_objects where status='INVALID';

col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

col ACTION_TIME for a30
col action for a30
col version for a10
col BUNDLE_SERIES for a30
col COMMENTS for a47
select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;

col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select con_id,comp_id,comp_name,version,status from cdb_registry;

col action_time for a28
col action for a8
col version for a8
col status for a8
col comments for a30
set line 999 pages 999

select con_id, patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;

select patch_id, patch_uid, version, status, description from cdb_registry_sqlpatch;
select patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;

Database Upgrade Using AutoUpgrade Option (12c to 19c)

Database Upgrade Using Autoupgrade

Saturday, December 3, 2022

Day to Day Commands

day2day commands

How to verify the OS exact details?

[oracle@qtest ~]$ uname -a
Linux qtest 3.10.0-862.el7.x86_64 #1 SMP Wed Mar 21 18:14:51 EDT 2018 x86_64 x86_64 x86_64 GNU/Linux

[oracle@qtest ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.5 (Maipo)

[oracle@qtest ~]$ more /etc/os-release

NAME="Red Hat Enterprise Linux Server"
VERSION="7.5 (Maipo)"
ID="rhel"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.5"
PRETTY_NAME="Red Hat Enterprise Linux"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:7.5:GA:server"
HOME_URL="https://www.redhat.com/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"
REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"
REDHAT_BUGZILLA_PRODUCT_VERSION=7.5
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="7.5"

[oracle@qtest ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.5 (Maipo)

Purging of Listener log file in Oracle Database

Solution 1 :

First find the location of listener log file.

[oracle@qtest ~]$ lsnrctl status
Go the listener log file location and execute the below commands.

[oracle@qtest]$ cp listener.log listener_backup.log
[oracle@qtest]$ cat /dev/null>listener.log
[oracle@qtest]$ gzip listener_backup.log

Solution 2 :

LSNRCTL>
LSNRCTL> set current_listener <listenername>
LSNRCTL> set log_status off

Login into another window and rename the log file and then return to the previous window
LSNRCTL> set log_status on
The above command will create a new and empty listener.log file which will continue logging.

Top CPU & MEMORY Consuming Sessions

Solution:

The ps (process status) command is handy for quickly identifying top resource-consuming processes. For example, this command displays the top 10 CPU-consuming resources on the box:

$ ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head

Here is a partial listing of the output:

65.5 5017 oracle ? oracleO12C (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
0.8 5014 oracle pts/1 sqlplus
0.8 23255 oracle ? ora_vktm_O12C

In the prior output, the process named oracleO12C is consuming an inordinate amount of CPU resources on the server. The process name identifies this as an Oracle process associated with the O12C database.

Similarly, you can also display the top memory-consuming processes:

$ ps -e -o pmem,pid,user,tty,args | sort -n -k 1 -r | head

Here is a snippet of the output:

11.2 5017 oracle ? oracleO12C (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
7.1 23317 oracle ? ora_mmon_O12C
6.3 23285 oracle ? ora_dbw0_O12C

Friday, December 2, 2022

Troubleshooting Database Network Connectivity Issues

Step 1.
Use the OS ping utility to determine whether the remote box is accessible

for example:

$ ping qproddb
qproddb is alive

If ping doesn’t work, work with your system or network administrator to ensure you have server-to-server connectivity in place.

Step 2.

Use telnet to see if you can connect to the remote server and port (that the listener is listening on)

for example:

$ telnet qproddb 1521
Trying 127.0.0.1...
Connected to qproddb.
Escape character is '^]'.

The prior output indicates that connectivity to a server and port is okay. If the prior command hangs, then contact your SA or network administrator for further assistance.

Step 3.

Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network.

for example:

$ tnsping qproddb

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = qproddb.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = QPRODDB)))
OK (500 msec)

If tnsping can’t contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).

Step 4.

Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.

Sometimes the client machine will have multiple TNS_ADMIN locations and tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is being used is to rename it and see whether you get a different error when attempting to connect to the remote database.

Step 5.

If you’re still having issues, examine the client sqlnet.log file and the remote server listener.log file.

Sometimes these log files will show additional information that will pinpoint the issue.

Sunday, August 28, 2022

ADOP Patching Basics

Tuesday, August 23, 2022

Full database export & import using datapump in Oracle

Step.1

Create a directory for export in source side.
create directory EXP_DIR as '/u01/dpdump_prod/EXP_DIR';

Step.2

Get the tablespace DDL metadata in source side.

set heading off;
set echo off;
set lines 1000 pages 40000
set long 999999
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

Step.3

Run expdp command.

expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=INDPROD_$(date +"%Y%m%d")exp.dmp logfile=INDPROD$(date +"%Y%m%d")_exp.log FULL=Y exclude=statistics

Step.4

Install oracle database binaries and create database.

Step.5

Create the directory for impdp in target side.
create or replace directory IMP_DIR as '/u01/dpdump_prod/IMP_DIR';

Step.6

Create the missing tablespaces in target side.

Please refer to spool ddl_tablespace.sql and create the tablespaces.

Step.7

Run impdp command.

impdp \'/ as sysdba\' dumpfile=INDPROD_20220823exp.dmp logfile=-INDPROD$(date +"%Y%m%d")_imp.log directory=DATA_PUMP_DIR full=y

Step.8

Run utlrp.sql to compile invalid objects in target side.
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Sunday, August 14, 2022

Script to take Daily Full Backups using RMAN In Oracle

Need to create required directories:

mkdir -p /u03/scripts

mkdir -p /u03/rman_backup

Actual Script:

[oratest@mqmhrd1 rman_backup]$ cd scripts/

vi rman_backup.sh

. /home/oratest/MQMGEMTEST.env
today="$(date --date '0 days ago' '+%d_%b_%Y')";
BKUP_DIR=/u03/rman_backup/
BKUP_LOG=$BKUP_DIR/logs/"$ORACLE_SID"_Full_hot_backup_"$today".log
mkdir -p $BKUP_DIR/$today/hot
mkdir -p $BKUP_DIR/$today/cfile
mkdir -p $BKUP_DIR/$today/alogbkp
rman target / log=$BKUP_LOG<<EOF
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired backup of archivelog all;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 format '$BKUP_DIR/$today/hot/%d-fullhot-%T-%t-%s-%p.bkp' filesperset 8 DATABASE plus archivelog format '$BKUP_DIR/$today/alogbkp/%d-alogs-fha-%T-%t-%s-%p.archbkp' filesperset 8;
delete noprompt obsolete;
backup current controlfile FORMAT='$BKUP_DIR/$today/cfile/%d-CTRL-%U.ctrl';
delete noprompt archivelog all completed before 'sysdate-4';
release channel d1;
release channel d2;
}
EOF

## Logfile deletion
find /u03/rman_backup/logs/*.log -mtime +2 -print -exec rm {} \;

## Empty Folder deletion
find /u03/rman_backup/ -type d -empty -delete
exit

Crontab:

[oratest@mqmhrd1 rman_backup]$ crontab -l
40 17 * * * /bin/sh /u03/rman_backup/scripts/rman_backup.sh
[oratest@mqmhrd1 rman_backup]$

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MQMTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_MQMTEST.f'; # default
RMAN>

Wednesday, August 10, 2022

Migrate Oracle Database Workload To Cloud Re-host (Lift and Shift), Re-platform, Refactor

When Migrating database and application to cloud, it is very important to keep in mind the business goals, application and database capabilities and cost of migration.

There are different strategies for different application and database migrations goals.

Re-host (Lift and Shift) - Oracle on EC2

-Same administration experience as on-premise

-Full Control over the environment

-All feature is available

-All version is supported

Re-platform - Oracle on RDS

-Optimized Architecture

-Database Install and Maintenance

-Automated Patching & Upgrade

-Automated Backup

-High Availability

-OS Patching & Maintenance

-Scaling

Refactor (Rearchitect) - Adopt Cloud Native Services

-Amazon Aurora

-Amazon Redshift

-PostgreSQL

-Other database engine

-Eliminate Oracle Licensing Cost

Oracle Database Migration Tools & Techniques

Oracle Native Migrations Tools

• Data Pump
• Transportable Tablespaces
• Full Transportable Export/Import
• Data Guard
• Incremental Backups
• Oracle GoldenGate
• RMAN

AWS Native Migration Tools

• Database Migration Service (DMS)
• CloudEndure
• AWS Application Migration Service

Ebs 12.2 Features / Components / Tools

Long Running Queries Troubleshoot

To check the OSPD details:

Long_running

To check the SID details:

SID_Info

How to Identify SID based on OSPID in Oracle

Get the overall process output using TOP command.

Verify the OSPID:

ps -ef|grep 28335
mqmprod 28501 28071 0 14:04:46 pts/1 0:00 grep 28335
mqmprod 28335 1 6 14:00:10 ? 4:06 oracleQPROD (LOCAL=NO)

set lines 200 pages 500
col sid format 99999
col username format a30
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser from gv$session s, gv$process p where s.paddr= p.addr and p.spid='&spid' order by p.spid;

==> 28335

Script To Delete Archivelogs In Oracle

Create necessary directories as per the requirement.

mkdir -p /home/oratest/rman_scripts
mkdir -p /home/oratest/rman_scripts/logs

cd /home/oratest/rman_scripts

vi RunDeleteArchive.sh

export ORACLE_SID=MQMTEST
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

rman target / log=/home/oratest/rman_scripts/logs/rman.log <<EOF

run{
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate -2';
crosscheck archivelog all;
}
EOF
exit

Crontab:

[oratest@MQMPROD rman_scripts]$ crontab -l
# Delete the archived redo log files on daily
00 6,12,18,23 * * * /home/oratest/rman_scripts/RunDeleteArchive.sh

File content:

[oratest@MQMPROD rman_scripts]$ cat /home/oratest/rman_scripts/RunDeleteArchive.sh
export ORACLE_SID=MQMTEST
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

rman target / log=/home/oratest/rman_scripts/logs/rman.log <<EOF
run{
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate -2';
crosscheck archivelog all;
}
EOF
exit

Thursday, July 28, 2022

Oracle E-Business Suite Config Files Backup

Ebs Config Files Backup Script

Oracle Database Configuration Files Backup

Database Config Files Backup

Thursday, June 23, 2022

How to stop or cancel mrp process in standby?

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;

For real time media recovery

alter database recover managed standby database using current logfile disconnect from session;

How to check the datafiles locations if it is in multiple locations ?

select distinct regexp_substr(name,'^.*/') from v$datafile;

select distinct regexp_substr(member,'^.*/') from v$logfile;
select distinct regexp_substr(name,'^.*/') from v$controlfile;

select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;

How to take backup using the cp command?

Backup plan:

cp -Pr 10.1.2 10.1.2_beforejws_bkp14062022

cp -Pr 10.1.3 10.1.3_beforejws_bkp14062022

How to increase a datafile or adding extra space to tablespace in oracle database?

show parameter db_create_file_dest;

select file_name from dba_data_files;

select count(*) from dba_data_files;

select file_name, bytes/1024/1024 from dba_data_files where file_name like '%&file%';

select file_name, status from dba_data_files ;

select file_name,file_id,autoextensible,bytes/1024/1024/1024,status,maxbytes/1024/1024/1024 from dba_data_files where tablespace_name='&TABLESPACE_NAME';

alter tablespace help_data add datafile '/u04/oradata/qr10.dbf' size 3000m autoextend off;

alter database datafile '/u02/dbdata/qprod/heqa_01.dbf' resize 5g;

alter tablespace nfhprod_biplatform add datafile '+data' size 16m autoextend on maxsize 31g;

alter tablespace kuls_img_tbs add datafile '/u02/oracle/datafiles/nfhprod/kuls_img_tbs_03' size 1g autoextend on maxsize 20g;

To enable datafile autoextend and set the Maxsize

SQL> alter database datafile 'datafile_name' autoextend on maxsize 120 g;

To Resize Datafiles

SQL> alter database datafile 'datafile_name' resize 20G;

How to run autoconfig if we lost adautocfg.sh script from $ADMIN_SCRIPTS_HOME location?

echo $CONTEXT_FILE

/u01/devapp/inst/apps/DEV_seefdev/appl/admin/DEV_seefdev.xml

cd $AD_TOP/bin

./adconfig.sh -contextfile=/u01/devapp/inst/apps/DEV_seefdev/appl/admin/DEV_seefdev.xml

Once this is executed, the original adautocfg.sh is also created in default location.

How to generate the excel file using sqlplus ?

set markup html on

spool wfmailer.xls

Run your SQL Query

spool off

How to find current SCN number from Oracle database?

col current_scn for 99999999999999999

select current_scn from v$database;

select to_char(current_scn) from v$database;

select current_scn from v$database;

select min(checkpoint_change#) from v$datafile_header where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

TAR and UNTAR commands

TAR:

$ORACLE_HOME:

nohup tar -czvf /u05/oradb/TEST_Home_AfterDBPatching_13DEC2021.tar.gz /u01/oracle/TEST/db/tech_st/11.2.0 &

oraInventory:

nohup tar -czvf /u05/oradb//TEST_Inventory_AfterDBPatching_13DEC2021.tar.gz /u01/app/oraInventory &

UNTAR:

tar -xvzf /Backup/TEST_Backup/DATA_After1220EBS_30DEC21.tar.gz

How to check the file versions in oracle EBS ?

1. adident Header appvndrb.pls

2. strings -a $AP_TOP/patch/115/sql/appvndrb.pls | grep '$Header'

$ cd $AP_TOP/patch/115/sql

$ strings -a appvndrb.pls | grep '$Header'

$ adident Header <FILE_NAME>

$ strings -a <FILE_NAME> | grep Header

How to enable/disable/control maintenance mode from backend?

sqlplus apps/*****

select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

How to check applied patches in oracle Ebs?

select bug_number,last_update_date from apps.ad_bugs where bug_number='&patchnumbe';

select bug_id, bug_number from ad_bugs where bug_number='&num';

select patch_name, patch_type from ad_applied_patches where patch_name='&num';

How to check the code levels in oracle EBS ?

EBS cpu patches:

col CPU format a9

select max(CODELEVEL) "CPU" from AD_TRACKABLE_ENTITIES where ABBREVIATION in ('ebscpu');

Code Level in EBS:

col ABBREVIATION for a10
set lines 1000
col NAME for a50
col CODELEVEL for a20
select abbreviation,name,codelevel from ad_trackable_entities where abbreviation in ('txk','ad','PJ_PF','ATG_PF','AD');

How to verify current workflow mailer log file location ?

select fl.meaning,fcp.process_status_code,Decode(fcq.concurrent_queue_name, 'WFMLRSVC', 'maile r container','WFALSNRSVC', 'listener container',fcq.concurrent_queue_name),fcp.concurrent_process_id,os_process_id,fcp.logfile_name FROM fnd_concurrent_queues fcq,fnd_concurrent_processes fcp,fnd_lookups fl WHERE fcq.concurrent_queue_id = fcp.concurrent_queue_id AND fcp.process_status_code = 'A' AND fl.lookup_type = 'CP_PROCESS_STATUS_CODE' AND fl.lookup_code = fcp.process_status_code AND concurrent_queue_name IN( 'WFMLRSVC', 'WFALSNRSVC' ) ORDER BY fcp.logfile_name;

Friday, April 22, 2022

How To Optimize MySQL Tables

mysql> show table status like "user" \G

*************************** 1. row ***************************

Name: user

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 6

Avg_row_length: 115

Data_length: 692

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 0

Auto_increment: NULL

Create_time: 2022-04-16 04:36:27

Update_time: 2022-04-16 04:36:27

Check_time: 2022-04-16 07:13:50

Collation: utf8_bin

Checksum: NULL

Create_options:

Comment: Users and global privileges

1 row in set (0.00 sec)

The output shows some general information about the table. The following two numbers are important:

Data_length represents the amount of space the database takes up in total.

Data_free shows the allocated unused bytes within the database table. This information helps identify which tables need optimization and how much space will be released afterward.

Show Unused Space for all tables.

select table_name, data_length, data_free from information_schema.tables where table_schema='user' order by data_free desc;

Display Data in Megabytes.

select table_name, round(data_length/1024/1024), round(data_free/1024/1024) from information_schema.tables where table_schema='user' order by data_free desc;

Optimize a Table Using MySQL

optimize table user;

Optimize Multiple Tables at Once

optimize table user, db, proc;

Optimize Tables Using the Terminal

Syntax:

mysqlcheck -o <schema> <table> -u <username> -p <password>

How to findout of MySQL my.cnf file?

mysql --help | grep "Default options" -A 1

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

What is MySQL Query Caching

What is MySQL Query Caching?

MySQL Query Caching provides database caching functionality. The SELECT statement text and the retrieved result are stored in the cache. When you make a similar query to the one already in the cache, MySQL will respond and give a query already in the cache. In this way, fewer resources are used, and your query runs faster.

To check the status

show variables like 'have_query_cache';

show variables like 'query_cache_%';

To adjust in MySQL configuration file:

query_cache_type=1

query_cache_size = 10M

query_cache_limit=256k

Disable the MySQL query cache without restarting MySQL.

set global query_cache_size = 0;

MySQL Troubleshooting

1. Not able to start the database.

-MySQL configuration file /etc/my.cnf is present and have all valid configuration.

-System has enough free resources to cater MySQL.

-Filesystems are not in READ ONLY mode.

-Check and fix the errors reported in /var/log/mysqld.log

2. MySQL is running, But users are unable to connect remotely.

-Verify you are connected to correct port of the database.

-Check firewall status on MySQL connectivity.

-Verify you are using correct user/password.

3. Users are unable to create new connections after a certain limit.

-Verify you are not hitting "max_connections limits" if yes, you need to increase it as per requirement.

show status like '%onn%';

show variables like "max_connections";

set global max_connections = 200;

MySQL Statements For Table Maintenance

-CHECK TABLE -------> For integrity checking

-REPAIR TABLE -------> For repairs

-ANALYZE TABLE -------> For analysis

-OPTIMIZE TABLE -------> For optimization

1.CHECK TABLE

The check table statement performs an integrity check on table structure and contents, and if the output from CHECK TABLE indicates that a table has problems, the table structure should be repaired.

check table <table_name>

check table <table_name>,<table_name>,<table_name>

2.REPAIR TABLE

The repair table statement corrects the problem in a table that has become corrupted.

repair table <table_name>

repair table <table_name>,<table_name>,<table_name>

3.ANALYZE TABLE

The analyze table statement updates a table with information about the distribution of key values in the table, and this information is used the optimizer to make better choices about query execution plans.

analyzer table <table_name>

analyzer table <table_name>,<table_name>,<table_name>

4.OPTIMIZE TABLE

The optimize table statement cleans up a MyISAM table by defragmenting it, It involves reclaiming unused space resulting from deletes & updates, coalescing split records and stored non-contiguously and it also sorts the index pages if they are out of order and updates the index statistics.

optimize table <table_name>

optimize table <table_name>,<table_name>,<table_name>

To optimize all tables in all MySQL database using mysqlcheck

mysqlcheck -o --all-databases -u root -p

To repair multiple MySQL databases using mysqlcheck

mysqlcheck -r --databases mysql qtest

To analyze databases using mysqlcheck

mysqlcheck -u root -p --analyze mysql

Tuesday, April 19, 2022

Sed Filter Command In Linux

[root@mqmopensource ~]# cat qtest

My name is khadar and this is my first testing file

My son name is mohammed shoaib

My daughter name is rumaysa

[root@mqmopensource ~]# sed -n -e '/My son name is mohammed shoaib/p' qtest > shoaib.txt

[root@mqmopensource ~]# cat shoaib.txt

My son name is mohammed shoaib

Revoke privileges In MySQL

Example:

revoke privileges on <object> from user;

revoke delete, update on <table_name> from 'user'@'localhost';

revoke delete, update on <table_name> from 'user'@'%';

revoke all on <table_name> from 'user'@'localhost';

revoke all on <table_name> from 'user'@'%';

revoke select on <object> from 'username'@'localhost';

Granting a user in MySQL

Examples on GRANT command

grant all privileges on *.* to 'user_name'@'localhost';

grant all privileges on *.* to 'user_name'@'%';

grant all privileges on *.* to 'user_name'@'localhost' with grant option;

grant all privileges on *.* to 'user_name'@'%' with grant option;

grant all privileges on dbname.* to 'user_name'@'localhost';

grant all privileges on dbname.* to 'user_name'@'%';

grant insert,update,delete on table dbname.tablename to 'user_name'@'localhost';

grant insert,update,delete on table dbname.tablename to 'user_name'@'%';

grant create on database.* to 'user_name'@'localhost';

grant create on database.* to 'user_name'@'%';

grant select , execute on database.* to 'user_name'@'%';

grant select , execute on database.* to 'user_name'@'localhost';

How to convert a user In MySQL

Make sure you are connected with root/admin user:

mysql> select current_user();

Create normal user if not created.

mysql> CREATE user 'super'@'%' IDENTIFIED BY 'super';

mysql> CREATE user 'super'@'localhost' IDENTIFIED BY 'super';

Grant privileges for making it super user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

Reload all the privileges

mysql> flush privileges;

View the grants:

mysql> show grants for super;

MySQL User Management

-- List all users present in mysql

mysql> select user,host,account_locked,password_expired from mysql.user;

Create user in mysql:

mysql> create user 'mqmtest_user'@'%' identified by 'mqmtest_user';

mysql> GRANT ALL ON *.* TO 'mqmtest_user'@'localhost';

View already created users:

mysql> select host, user from mysql.user;

Drop user:

mysql> drop user 'mqmtest_user'@'localhost';

Rename a user:

mysql> rename user 'mqmtest_user3' to 'mqmtest_user007';

change password of a user:

mysql> ALTER USER 'dbaprod'@'%' IDENTIFIED BY 'dbaprod';

Change resource option:

mysql> alter user 'dbaprod'@'%' with MAX_USER_CONNECTIONS 10;

Lock/unlock an account:

mysql> alter user 'dbaprod'@'%' account lock;

mysql> alter user 'dbaprod'@'%' account unlock;

MySQL General Queries

-- Drop database:

mysql> drop database qtest1;

mysql> show databases like 'information_schema%';

mysql> SELECT schema_name FROM information_schema.schemata;

How to get MySQL / MariaDB db size:

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 / 1024 as "size (gb)" from information_schema.tables group by table_schema;

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 as "size (mb)" from information_schema.tables group by table_schema;

How to find timezone info

Check whether time_zone table is updated or not.

mysql> select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u');

mysql> select @@global.time_zone, @@session.time_zone;

How to check the time and date?

mysql> select now();

Below commands can be used to find MySQL / MariaDB version:

mysql> select version();

#[root@mqmopensource ~]# mysql --version

mysql> show variables like "%version%";

show all processes in mysql cluster.

mysql> show processlist;

show processes for a specific user:

mysql> select * from information_schema.processlist where user='root'\G;

Show processes for a particular database:

mysql> select * from information_schema.processlist where DB='information_schema';

Get the processid for the session:

mysql> show processlist;

Uptime of server

mysql> status;

Alternative command

mysql> \s

Server startup time:

mysql> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';

How to check the data directory:

mysql> show variables like '%data%';

mysql> select variable_value from information_schema.global_variables where variable_name = 'datadir';

mysql> select @@datadir;

Find current data/time on MySQL / MariaDB

mysql> select current_timestamp,current_date,localtime(0),localtimestamp(0);

Find MySQL / MariaDB configuration values

mysql> select variable_name,variable_value from information_schema.global_variables;

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'port';

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'datadir';

2. Alternatively you can check my.cnf file in /etc folder in Linux

cat /etc/my.cnf

Find the last MySQL / MariaDB Database service restarted / server reboot time

mysql> status;

-- In older version:

mysql> select time_format(sec_to_time(variable_value ),'%hh %im') as uptime from information_schema.global_status where variable_name='uptime';

-- In latest mysql version:

mysql> show global status like 'uptime';

mysql> show engines;

If the valid backup file is available with you, then you can restore the same using below command.

mysqldump -u root -p classicmodels < mysqldump.sql

-- If dbdump.sql is the backup file , then restore command will be

[mysql@localhost]# mysqldump –u root –pmysql sampledb > dbdump.sql

-- Backup multiple databases

For example, if you want to take backup of both sampledb and newdb database, execute the mysqldump as shown below:

[root@localhost]# mysqldump –u root –p --databases sampled newdb > /tmp/dbdump.sql

We cannot take backup of information_schema and performance_schema databases as these are metadata databases.

-- Backup all databases:

root# mysqldump -u root -p --all-databases > all-database.sql

Backup a specific table

SYNATX:

mysqldump –c –u username –p databasename tablename > /tmp/databasename.tablename.sql

In this example, we backup only the ta2 table from sampledb database.

root$ mysqldump –u root –p sample ta2 > /tmp/nwedb_ta2.sql

Restore all databases

[root]$ mysql -u root -p < /tmp/alldbs55.sql

Backup databases in compress format

With bzip2:

mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2

With gzip:

mysqldump --all-databases | gzip> databasebackup.sql.gz

How to connect to MySQL / MariaDB database

hostname$ export PATH=/usr/local/mysql/bin:$PATH

hostname$ which mysql

/usr/local/mysql/bin

SYNTAX - mysql -u user -p

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 19.Server version: 10.4.18-MariaDB

Find current connection info:

mysql> \s

Below is the alternative command:

mysql> status;

Get current user and current database:

mysql> select current_user,database();

switch to another database:

mysql> use qtest1;

Database changed

mysql> select database();

Database Management In MySQL?

Create a database in MySQL / MariaDB

-- Below commands can be used to create database

mysql> CREATE DATABASE QTEST;

Query OK, 1 row affected (0.00 sec)

mysql> create database qtest1 character set UTF8mb4 collate utf8mb4_bin;

Query OK, 1 row affected (0.00 sec)

-- View the create database statement used for creating db

mysql> SHOW CREATE DATABASE qtest1;

View database list

mysql> show databases;

mysql> select * from information_schema.schemata;

mysql> show databases like 'QTEST%';

Backups and Recovery in MYSQL ?

1. Physical backup (cold backup/offline backup)

Taking backup of physical files.

2. Logical backups (online backups)

Taking backup of logical objects

3. Incremental backups ( we can perform this on only logical backups only)

Note:

Take backup using MYSQLDUMP utility.

Restore using MYSQLIMPORT or MYSQL utility.

1. Physical backup (cold backup/offline backup)

-Stop the mysql services

service mysqld stop

-Copy the datadir and mysql config file to different locations

select @@datadir;

find / -name *cnf*

find / -name *.cnf*

cp -rf /var/lib/mysql/auto.cnf /root/mysqlcoldbackup_16042022/

cp -rf /var/lib/mysql /root/mysqlcoldbackup_16042022/mysqlbkp

cp -rfv /var/lib/mysql /root/mysqlcoldbackup_16042022/mysql$(date +%s)

service mysqld start

2. Logical backups (online backups)

To check the databases:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| QTEST |

| classicmodels |

| mysql |

| performance_schema |

+--------------------+

5 rows in set (0.00 sec)

Database Level:

Backup a single database:

mysqldump -u root -p mysql > mysqldump.sql

Backup multiple databases:

mysqldump -u root -p --databases mysql classicmodels > multidatabasesdump.sql

Backup all databases:

mysqldump -u root -p --all-databases > all_databases.sql

Compressed MySQL Database Backup:

mysqldump -u root -p mysql | gzip > mysql.sql.gz

Backup with timestamp option:

mysqldump -u root -p mysql > mysql-$(date +%Y%m%d).sql

Table Level:

Backup a single table:

mysqldump -u root -p mysql servers > singletable.sql

Backup a multiple tables:

mysqldump -u root -p mysql servers db user > multi_table.sql

Restore Commands:

mysql> drop database mysql;

Query OK, 28 rows affected, 2 warnings (0.05 sec)

mysql> use mysql

ERROR 1049 (42000): Unknown database 'mysql'

mysql> create database mysql;

Query OK, 1 row affected (0.00 sec)

Restoring a database using backups:

[root@mqmopensource ~]# mysql -u root -p mysql > mysqldbkp.sql------> using same database backup dump

[root@mqmopensource ~]# mysql -u root -p mysql < all_database.sql ------> using all database backup dump

mysql> use mysql

Database changed

mysql> show tables;

Empty set (0.00 sec)

Restore Command:

mysql -u root -p mysql < mysqldbkp.sql

mysql> use mysql

mysql> show tables;

To restore a single table from full/db backups in MYSQL:

mysql> use mysql;

mysql> show tables;

| user

| columns_priv

| db

| event

| func

| general_log

mysql> drop table user;

Query OK, 0 rows affected (0.00 sec)

MySQL Important Logfiles

1. Error log file:

During instalation time this file is created , startup & shutdown, hang, maintenance related details are recorded.

show variables like '%log%';

log_error | /var/log/mysqld.log

2. General query log file:

Query related issues, instert, update, delete statements are recorded here.

Bydefault this log is not enabled.

show variables like '%log%';

general_log | OFF

general_log_file | /var/lib/mysql/mqmopensource.log

set global general_log=on/off;

3. Binary log file (STATIC VALUE):

Very useful for auditing like how many records are updated/deleted.

It records changes made to the database.

show variables like '%log%';

log_bin | OFF

log_bin_basename

set global log_bin=on/off;

show binary logs;

flush logs;

4. Slow query log:

This is also very useful for slow query performance logs

slow_query_log | OFF |

slow_query_log_file | /var/lib/mysql/mqmopensource-slow.log |

set global slow_query_log=on/off;

show variables like '%time%';

long_query_time | 10.000000 (Seconds)

Check data file location and tablespaces in MariaDB or MySQL

Check size and location of data files or tablespaces present in MySQL or MariaDB

Check innodb_file_per_table parameter.

innodb_file_per_table=ON, InnoDB uses one tablespace file per table.

innodb_file_per_table=OFF, InnoDB stores all tables in the InnoDB system tablespace.

show variables like 'innodb_file_per_table'

Check the location of datafile/tablespace present in MySQL or MariaDB:

-- On MySQL

show variables like 'datadir';

Variable_name|Value

-------------+------------------------+

datadir |/var/lib/mysql/|

Check files and tablespace for MySQL:

select * from information_schema.FILES;

Check files and tablespace detail for MariaDB:

select * from information_schema.innodb_sys_tablespaces;

Check the Version of MariaDB / MySQL

The first way to open a Command prompt and connect with MariaDB will show the version as follows:

C:\WINDOWS\system32>mysql -u root -p

Enter password: ********

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 14

Server version: 10.6.5-MariaDB mariadb.org binary distribution

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

mysql> select version();

Check used & available maximum connection in MySQL

Check maximum available connection in MySQL

mysql> show global variables like 'max_connections';

Check used connection in MySQL

mysql> show global status like 'max_used_connections';

Check connected session list or Process list in MySQL

select id,user,host,db,command,time,state,info from information_schema.processlist;

Create structure, duplicate & back up the table in MySQL

Use of CTAS for creating the Duplicate table with data or only structure (by using where 1 = 0) in the following database as follows:

MySQL: Use CTAS Syntax:

-- Create structure only

create table test_table_new like test_table;

-- Insert data into backup table

insert test_table_new select * from test_table;

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Kill idle connection in MySQL

Check the session is in sleep state and get kill command for release the sessions:

select count(*) from information_schema.processlist where Command='Sleep';

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

concat('KILL ',id,';')|

----------------------+

KILL 10; |

KILL 9; |

Execute the command to Kill the session got from upper query output.

KILL 10;

Automatic Clean the idle Connection in MariaDB/MySQL:

Interactive operation: this means opening the MySQL client on your local computer and doing various SQL operations on the command prompt.

INTERACTIVE_TIMEOUT is used to automatically clean the interactive connection in MariaDB/MySQL. The number of seconds the server waits for activity on an interactive connection before closing it

Non-interactive: means calls from the program. Example Tomcat Web service call to the database server through JDBC to connect etc.

WAIT_TIMEOUT is used to automatically clean the idle connection in MariaDB/MySQL.

The number of seconds the server waits for activity on a connection before closing it.

Note: Default time for both is 28800 seconds i.e. 8 hours

Check the Variables Values:

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

Variable_name |Value|

------------------------+-----+

wait_timeout |28800|

show global variables like '%interactive_timeout%';

Variable_name |Value|

-------------------+-----+

interactive_timeout|28800|

Change the time limit to 30 minutes for idle connection timeout: The value used in both variables is in Seconds i.e. 30 min – 1800 seconds or 1 hour – 3600 seconds.

SET GLOBAL wait_timeout=1800;

SET GLOBAL interactive_timeout=1800;

You can also change to the Default value:

SET GLOBAL wait_timeout=DEFAULT;

SET GLOBAL interactive_timeout=DEFAULT;

Find the dependence on Table in MYSQL / MariaDB

Check the Procedures and functions depending on the table:

select a.routine_name,b.table_name,a.routine_schema,a.routine_type from information_schema.routines a inner join (select table_name , table_schema from information_schema.tables ) b on a.routine_definition like concat('%',b.table_name,'%') where b.table_schema = 'dbname' and b.table_name = 'test_table' ;

Check the views dependence on the table.

select * from information_schema.views where table_schema='mqm1' and table_name = 'test_table';

Check the foreign key constraints depend on the table.

SELECT Constraint_Type ,Constraint_Name ,Table_Schema ,Table_Name FROM information_schema.table_constraints

WHERE Table_Schema ='dbname' AND Table_Name = 'tablename' and Constraint_Type = 'FOREIGN KEY';

Start and Stop and restart the MariaDB or MySQL on Linux

Check status for the MariaDB or MySQL

ps -ef|grep mysql

Check status for the MariaDB or MySQL

service mysqld status

Service mysql status

/etc/init.d/mysql status

/etc/init.d/mysqld status

Start the MariaDB or MySQL in Linux Environment:

service mysqld start

service mysql start

/etc/init.d/mysql start

/etc/init.d/mysqld start

Stop the MariaDB or MySQL in Linux Environment

service mysqld stop

service mysql stop

/etc/init.d/mysql stop

/etc/init.d/mysqld stop

Restart the MariaDB or MySQL Services in Linux Environment

service mysqld restart

service mysql restar

/etc/init.d/mysql restart

/etc/init.d/mysqld restart

Pages

Saturday, December 31, 2022

RAC Installation Step By Step

Physical Standby Creation Steps For Single Instance

Tuesday, December 27, 2022

How to check the OS user status

How to find large files

Find recently modified tables in Oracle database

Single Instance DB Upgrade From 12c to 19c

12c Single Instance Patching

Database Upgrade Using AutoUpgrade Option (12c to 19c)

Saturday, December 3, 2022

Day to Day Commands

How to verify the OS exact details?

Purging of Listener log file in Oracle Database

Top CPU & MEMORY Consuming Sessions

Friday, December 2, 2022

Troubleshooting Database Network Connectivity Issues

Sunday, August 28, 2022

ADOP Patching Basics

Tuesday, August 23, 2022

Full database export & import using datapump in Oracle

Sunday, August 14, 2022

Script to take Daily Full Backups using RMAN In Oracle

Wednesday, August 10, 2022

Migrate Oracle Database Workload To Cloud Re-host (Lift and Shift), Re-platform, Refactor

Oracle Database Migration Tools & Techniques

Ebs 12.2 Features / Components / Tools

Long Running Queries Troubleshoot

How to Identify SID based on OSPID in Oracle

Script To Delete Archivelogs In Oracle

Thursday, July 28, 2022

Oracle E-Business Suite Config Files Backup

Oracle Database Configuration Files Backup

Thursday, June 23, 2022

How to stop or cancel mrp process in standby?

How to check the datafiles locations if it is in multiple locations ?

How to take backup using the cp command?

How to increase a datafile or adding extra space to tablespace in oracle database?

How to run autoconfig if we lost adautocfg.sh script from $ADMIN_SCRIPTS_HOME location?

How to generate the excel file using sqlplus ?

How to find current SCN number from Oracle database?

TAR and UNTAR commands

How to check the file versions in oracle EBS ?

How to enable/disable/control maintenance mode from backend?

How to check applied patches in oracle Ebs?

How to check the code levels in oracle EBS ?

How to verify current workflow mailer log file location ?

Friday, April 22, 2022

How To Optimize MySQL Tables

How to findout of MySQL my.cnf file?

What is MySQL Query Caching?

MySQL Troubleshooting

MySQL Statements For Table Maintenance

Tuesday, April 19, 2022

Sed Filter Command In Linux

Revoke privileges In MySQL

Granting a user in MySQL

How to convert a user In MySQL

MySQL User Management

MySQL General Queries

How to connect to MySQL / MariaDB database

Database Management In MySQL?

Backups and Recovery in MYSQL ?

MySQL Important Logfiles

Check data file location and tablespaces in MariaDB or MySQL

Check the Version of MariaDB / MySQL

Check used & available maximum connection in MySQL

Create structure, duplicate & back up the table in MySQL

Kill idle connection in MySQL

Find the dependence on Table in MYSQL / MariaDB

Start and Stop and restart the MariaDB or MySQL on Linux

Translate

Followers