RAC Installation Step By Step

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 Creation Steps For Single Instance

Physical Standby Single Instance

How to check the OS user status

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

How to find large files

$ cd $ORACLE_HOME

$ find . -ls | sort -nrk7 | head -10
$ find . -ls | sort -nrk7 | head -10
$ du -S . | sort -nr | head -10

Find recently modified tables in Oracle database

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

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

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 Option (12c to 19c)

Database Upgrade Using Autoupgrade

Day to Day Commands

Day to Day Commands

day2day commands

How to verify the OS exact details?

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

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

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

Troubleshooting Database Network Connectivity Issues

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.