Kafka installation and setup

 Kafka installation and setup

Once you unzipped Kafka tar create below data directory:

Datadir:
================
/u03/kafkadata1
/u03/kafkadata2
/u03/kafkadata3

Create PID 
================
echo 1 > /u03/kafkadata1/myid
echo 2 > /u03/kafkadata2/myid
echo 3 > /u03/kafkadata3/myid
Modify zookeeper1.properties,zookeeper2.properties and zookeeper3.properties
=================

[root@kafkaserv config]# pwd
/u03/kafka/config
[root@kafkaserv config]# vi zookeeper1.properties
[root@kafkaserv config]# vi zookeeper2.properties
[root@kafkaserv config]# vi zookeeper3.properties
add below lines
tickTime=2000
initLimit=5
syncLimit=2
server.1=localhost:2887:3887
server.2=localhost:2888:3888
server.3=localhost:2889:3889
maxClientCnxns=0

Then start zookeeper services:
==============================
[root@kafkaserv bin]# ./zookeeper-server-start.sh -daemon /u03/kafka/config/zookeeper1.properties
[root@kafkaserv bin]# ./zookeeper-server-start.sh -daemon /u03/kafka/config/zookeeper2.properties
[root@kafkaserv bin]# ./zookeeper-server-start.sh -daemon /u03/kafka/config/zookeeper3.properties

Check if services came up using
===============================
[root@kafkaserv bin]# nc -v localhost 2181
Ncat: Version 6.40 ( http://nmap.org/ncat )
Ncat: Connected to ::1:2181.
^C
[root@kafkaserv bin]#
[root@kafkaserv bin]# nc -v localhost 2182
Ncat: Version 6.40 ( http://nmap.org/ncat )
Ncat: Connected to ::1:2182.
^C
[root@kafkaserv bin]#
[root@kafkaserv bin]# nc -v localhost 2183
Ncat: Version 6.40 ( http://nmap.org/ncat )
Ncat: Connected to ::1:2183.
^C

Optional, if you wanted to start using OS service command then create below service:
============================
cd /etc/systemd/system
vi zookeeper1.service
[Unit]
Description=Zookeeper1 Service
[Service]
Type=simple
WorkingDirectory=/u03/kafkadata1
PIDFile=/u03/kafkadata1/myid
SyslogIdentifier=zookeeper1
User=root
Group=root
ExecStart=/u03/kafka/bin/zookeeper-server-start.sh /u03/kafka/config/zookeeper1.properties
ExecStop=/u03/kafka/bin/zookeeper-server-stop.sh
Restart=always
TimeoutSec=20
SuccessExitStatus=130 143
Restart=on-failure
[Install]
WantedBy=multi-user.target
systemctl daemon-reload
systemctl enable zookeeper1.service
[root@kafkaserv system]# systemctl enable zookeeper1.service
ln -s '/etc/systemd/system/zookeeper1.service' '/etc/systemd/system/multi-user.target.wants/zookeeper1.service'
[root@kafkaserv system]#
[root@kafkaserv system]# systemctl enable zookeeper1.service
ln -s '/etc/systemd/system/zookeeper1.service' '/etc/systemd/system/multi-user.target.wants/zookeeper1.service'
[root@kafkaserv system]# systemctl status zookeeper1.service
zookeeper1.service - Zookeeper1 Service
   Loaded: loaded (/etc/systemd/system/zookeeper1.service; enabled)
   Active: inactive (dead)
[root@kafkaserv system]# systemctl start zookeeper1.service
[root@kafkaserv system]# systemctl status zookeeper1.service
zookeeper1.service - Zookeeper1 Service
   Loaded: loaded (/etc/systemd/system/zookeeper1.service; enabled)
   Active: active (running) since Sun 2022-12-04 12:29:22 IST; 3s ago
 Main PID: 24948 (java)
   CGroup: /system.slice/zookeeper1.service
           └─24948 java -Xmx512M -Xms512M -server -XX:+UseG1GC -XX:MaxGCPauseMillis=20 -XX:InitiatingHeapOccupancyPercent=35 -XX:+Ex...
Dec 04 12:29:22 kafkaserv.nsdr.com systemd[1]: Started Zookeeper1 Service.
Modify /u03/kafka/config/server.properties file 
===============================
Check if any error is there:
[root@kafkaserv bin]# ./kafka-server-start.sh /u03/kafka/config/server.properties
If above commands completed find then run it from backend
[root@kafkaserv bin]# ./kafka-server-start.sh -daemon /u03/kafka/config/server.properties
[root@kafkaserv bin]#

===============================
Create a TOPIC
=================================
[root@kafkaserv bin]# ./kafka-topics.sh --bootstrap-server 192.168.137.76:9092  --create --topic BroadcastTopic --replication-factor 1-partitions 1
Created topic BroadcastTopic.
[root@kafkaserv bin]#
[root@kafkaserv bin]# ./kafka-topics.sh --list --bootstrap-server 192.168.137.76:9092                                  BroadcastTopic
BroadcastTopic1

Produce and Consume Msg
===============================
[root@kafkaserv bin]# ./kafka-console-producer.sh --broker-list 192.168.137.76:9092 --topic BroadcastTopic
>Apple
>Oranage
>Lemon
>Grapes
[root@kafkaserv bin]# ./kafka-console-consumer.sh --bootstrap-server 192.168.137.76:9092 --topic BroadcastTopic --from-beginning
Apple
Oranage
Lemon
Grapes



Thanks,

RPM based Oracle 19c Database installation and Creation in RHEL7 64bit

 RPM-based Oracle 19c Database installation and Creation in RHEL7 64bit

Installed few OS important rpm first. Please download rpm software from the oracle site.

[root@db101 Packages]# rpm -ivh ksh-20120801-19.el7.x86_64.rpm

warning: ksh-20120801-19.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:ksh-20120801-19.el7              ################################# [100%]

[root@db101 Packages]# rpm -ivh libaio-devel-0.3.109-12.el7.x86_64.rpm

warning: libaio-devel-0.3.109-12.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:libaio-devel-0.3.109-12.el7      ################################# [100%]

[root@db101 Packages]# rpm -ivh libaio-0.3.109-12.el7.x86_64.rpm

warning: libaio-0.3.109-12.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY

Preparing...                          ################################# [100%]

        package libaio-0.3.109-12.el7.x86_64 is already installed

[root@db101 GRID]# rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64\ \(1\).rpm

warning: compat-libstdc++-33-3.2.3-72.el7.x86_64 (1).rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:compat-libstdc++-33-3.2.3-72.el7 ################################# [100%]

Download preinstall RPM from Oracle as mentioned below:

https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

[root@db101 GRID]# rpm -ivh oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

warning: oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:oracle-database-preinstall-19c-1.################################# [100%]

[root@db101 GRID]# rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm

warning: oracle-database-ee-19c-1.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Preparing...                          ################################# [100%]

        installing package oracle-database-ee-19c-1.0-1.x86_64 needs 2055MB on the / filesystem

[root@db101 GRID]#

Solution - Cleanup/mount to have enough space and proceed ahead.

[root@db101 GRID]# rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm

warning: oracle-database-ee-19c-1.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Preparing...                          ################################# [100%]

[SEVERE] The install cannot proceed because ORACLE_BASE directory (/opt/oracle)

is not owned by "oracle" user. You must change the ownership of ORACLE_BASE

directory to "oracle" user and retry the installation.

error: %pre(oracle-database-ee-19c-1.0-1.x86_64) scriptlet failed, exit status 1

error: oracle-database-ee-19c-1.0-1.x86_64: install failed

[root@db101 GRID]# 

Solution - Change the permission of /opt and proceed ahead.

Now running main installation rpm:

[root@db101 GRID]# rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm

warning: oracle-database-ee-19c-1.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:oracle-database-ee-19c-1.0-1     ################################# [100%]

[INFO] Executing post installation scripts...

[INFO] Oracle home installed successfully and ready to be configured.

To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-19c configure

[root@db101 GRID]# /etc/init.d/oracledb_ORCLCDB-19c configure

Configuring Oracle Database ORCLCDB.

Prepare for db operation

8% complete

Copying database files

31% complete

Creating and starting Oracle instance

32% complete

36% complete

40% complete

43% complete

46% complete

Completing Database Creation

51% complete

54% complete

Creating Pluggable Databases

58% complete

77% complete

Executing Post Configuration Actions

100% complete

Database creation complete. For details check the logfiles at:

 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.

Database Information:

Global Database Name:ORCLCDB

System Identifier(SID):ORCLCDB

Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.

And here is the magic:

[oracle@db101 ~]$ ps -ef|grep pmon

oracle    44672      1  0 21:02 ?        00:00:00 ora_pmon_ORCLCDB

oracle    45135  45088  0 21:06 pts/1    00:00:00 grep --color=auto pmon

[oracle@db101 ~]$ ps -ef|grep tns

root        274      2  0 08:45 ?        00:00:00 [netns]

oracle    41638      1  0 20:43 ?        00:00:00 /opt/oracle/product/19c/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle    45137  45088  0 21:06 pts/1    00:00:00 grep --color=auto tns

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

ORCLCDB   READ WRITE           PRIMARY

[oracle@db101 ~]$ lsnrctl status LISTENER

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db101.test.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                22-Aug-2021 20:43:53

Uptime                    0 days 0 hr. 23 min. 46 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/db101/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db101.test.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db101.test.com)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "ORCLCDB" has 1 instance(s).

  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...

Service "ORCLCDBXDB" has 1 instance(s).

  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...

Service "e9bf7aa0b829afd5e0535289a8c0e740" has 1 instance(s).

  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...

Service "orclpdb1" has 1 instance(s).

  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...

The command completed successfully

Undo tablespace usage

 Below are the undo tablespace usage sql queries

1.

select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks
from v$session a, v$transaction b
where a.saddr=b.ses_addr ;

2.

select
s.sid,s.serial#,
NVL(s.username, 'NA') orauser,
s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
from
sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
where s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

3.

SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

4.

SELECT s.inst_id,
        r.name                   rbs,
        nvl(s.username, ‘None’)  oracle_user,
        s.osuser                 client_user,
        p.username               unix_user,
        to_char(s.sid)||’,’||to_char(s.serial#) as sid_serial,
        p.spid                   unix_pid,
        TO_CHAR(s.logon_time, ‘mm/dd/yy hh24:mi:ss’) as login_time,
        t.used_ublk * 8192  as undo_BYTES,
                st.sql_text as sql_text
   FROM gv$process     p,
        v$rollname     r,
        gv$session     s,
        gv$transaction t,
        gv$sqlarea     st
  WHERE p.inst_id=s.inst_id
    AND p.inst_id=t.inst_id
    AND s.inst_id=st.inst_id
    AND s.taddr = t.addr
    AND s.paddr = p.addr(+)
    AND r.usn   = t.xidusn(+)
    AND s.sql_address = st.address
  AND t.used_ublk * 8192 > 1073741824
  ORDER
       BY undo_BYTES desc
/

5.
select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'
order by s.value desc;

6.
select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';

7.
select SID,PROGRAM from v$session where TYPE='BACKGROUND';

8.
col machine for a10;
select  s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name
from    v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where   t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;

While startup "ORA-00845: MEMORY_TARGET not supported on this system"

 While Startup got "While startup "ORA-00845: MEMORY_TARGET not supported on this system"

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

Solution - on Linux not enough space allocated to /dev/shm during setup as below:

tmpfs                  1.4G   96K  1.4G   1% /dev/shm   >> Need to increase it

Solution:

[root@oranode1 ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm

[oracle@oranode1 ~]$ df -h /dev/shm

Filesystem      Size  Used Avail Use% Mounted on

shmfs           2.0G  1.5G  576M  72% /dev/shm

After above DB came up fine:

[oracle@oranode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 07:55:47 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1593835520 bytes

Fixed Size                  8621184 bytes

Variable Size            1459618688 bytes

Database Buffers          117440512 bytes

Redo Buffers                8155136 bytes

Database mounted.

Database opened.

SQL>

Thanks

Oralce Golden Gate few known issue

Below are the few known issue which we have encountered and it is documented in Oracle. 

Issue - ggsci: error while loading shared libraries: libnnz12.so: cannot open shared object file: No such file or directory

Solution - LD_LIBRARY_PATH should contain oracle client path.

[oracle@oranode1 ~]$ ggsci

ggsci: error while loading shared libraries: libnnz12.so: cannot open shared object file: No such file or directory

[oracle@oranode1 ~]$ echo $LD_LIBRARY_PATH

/u02/app/gg_home/lib:

[oracle@oranode1 ~]$ export LD_LIBRARY_PATH=/u02/app/gg_home/lib:/u02/app/oracle/product/12.2.0/dbhome_1/lib

[oracle@oranode1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (oranode1.test.com) 1> 

Able to login . 

=====================================================

Issue - Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory

Solution - permission issue on ggMessage.dat file.

[oracle@oranode1 ~]$ ggsci

Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory

Aborted (core dumped)

[oracle@oranode1 ~]$ cd $GG_HOME

[oracle@oranode1 gg_home]$ ls -ltr *.dat

-rw-r-----. 1 oracle oracle 43150372 Jun 30  2017 ggparam.dat

-rw-r-----. 1 oracle oracle  1866528 Jun 30  2017 ggMessage.dat

[oracle@oranode1 gg_home]$ chmod 775 ggMessage.dat ggparam.dat

[oracle@oranode1 gg_home]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (oranode1.test.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

GGSCI (oranode1.test.com) 2> exit

Thanks

Oracle 12c runInstaller pre-check failed - ( Soft Limit - Maximum stack size )

 Oracle 12c runInstaller pre-check failed - ( Soft Limit - Maximum stack size )

During the run of 12c runInstaller we got below error:

As a super user ran below:

ulimit -Ss 10240

and set the below on /etc/security/limits.conf

oracle soft stack 10240

Rerun the runInstaller.

Thanks,

PRVF-0002 while running 12c runInstaller

 PRVF-0002 while running runInstaller

While running 12c runInstaller you might get below error:

This error might come if you haven't set host entries like below:

[root@oranode1 software]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.135.40 oranode1.test.com oranode1

So update /etc/hosts using super user and run the runInstaller again.

Thanks,

Be careful while remove the /var/tmp/.oracle directory .. RAC hidden socket information

 Be careful while remove the /var/tmp/.oracle directory .. RAC hidden socket information

    Sometimes oracle suggests you remove files from the .oracle directory. Be careful while removing the contents from this hidden directory. We had landed one scenario where one of the colleagues removed the .oracle directory. Good thing is that it was in test instance.

Impact - Clusterware services are not coming up

Solution:

Stop all the clusterware services from all the nodes.

Create the directory  and give the permission. 

cd /var/tmp
mkdir .oracle
chmod 01777 .oracle

After above action plan services came up fine.

Thanks,
SM

Invalid objects sys.dv$configuration_audit and sys.dv$enforcement_audit - After 19c upgrade

Hello ,

After the 19c DB upgrade we found 2 SYS objects showing invalid. The good thing about these objects is that we have Oracle DOC ID for the same. Below action plan that has been performed:

Execute the below steps:

SQL> select owner,object_name from dba_objects where status='INVALID';

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

SYS

DV$CONFIGURATION_AUDIT

SYS

DV$ENFORCEMENT_AUDIT

SQL> drop view sys.dv$configuration_audit;

View dropped.

SQL> drop view sys.dv$enforcement_audit;

View dropped.

SQL> CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "AUDSYS"."DV$CONFIGURATION_AUDIT" ("OS_USER", "USERID", "HOST_NAME", "TERMINAL", "EVENT_TIMESTAMP", "OBJ_OWNER", "OBJ_NAME", "DV_ACTION_CODE", "DV_ACTION_NAME", "DV_ACTION_OBJECT_NAME", "SQL_TEXT", "DV_RULE_SET_NAME", "DV_FACTOR_CONTEXT", "DV_COMMENT", "SESSIONID", "ENTRY_ID", "STATEMENT_ID", "DV_RETURN_CODE", "PROXY_USERID", "GLOBAL_USERID", "INSTANCE_ID", "OS_PROCESS", "DV_GRANTEE", "DV_OBJECT_STATUS") AS

  SELECT

  OS_USERNAME

  , DBUSERNAME

  , USERHOST

  , TERMINAL

  , EVENT_TIMESTAMP

  , OBJECT_SCHEMA

  , OBJECT_NAME

  , DV_ACTION_CODE

  , DV_ACTION_NAME

  , DV_ACTION_OBJECT_NAME

  , SQL_TEXT

  , DV_RULE_SET_NAME

  , DV_FACTOR_CONTEXT

  , DV_COMMENT

  , SESSIONID

  , ENTRY_ID

  , STATEMENT_ID

  , DV_RETURN_CODE

  , DBPROXY_USERNAME

  , GLOBAL_USERID

  , INSTANCE_ID

  , OS_PROCESS

  , DV_GRANTEE

  , DV_OBJECT_STATUS

FROM audsys.unified_audit_trail where audit_type = 'Database Vault' and DV_ACTION_CODE > 20000;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27

View created.

SQL> CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "AUDSYS"."DV$ENFORCEMENT_AUDIT" ("OS_USER", "USERID", "HOST_NAME", "TERMINAL", "EVENT_TIMESTAMP", "OBJ_OWNER", "OBJ_NAME", "DV_ACTION_CODE", "DV_ACTION_NAME", "DV_ACTION_OBJECT_NAME", "SQL_TEXT", "DV_RULE_SET_NAME", "DV_FACTOR_CONTEXT", "DV_COMMENT", "SESSIONID", "ENTRY_ID", "STATEMENT_ID", "DV_RETURN_CODE", "PROXY_USERID", "GLOBAL_USERID", "INSTANCE_ID", "OS_PROCESS", "DV_GRANTEE", "DV_OBJECT_STATUS") AS

  SELECT

  OS_USERNAME

  , DBUSERNAME

  , USERHOST

  , TERMINAL

  , EVENT_TIMESTAMP

  , OBJECT_SCHEMA

  , OBJECT_NAME

  , DV_ACTION_CODE

  , DV_ACTION_NAME

  , DV_ACTION_OBJECT_NAME

  , SQL_TEXT

  , DV_RULE_SET_NAME

  , DV_FACTOR_CONTEXT

  , DV_COMMENT

  , SESSIONID

  , ENTRY_ID

  , STATEMENT_ID

  , DV_RETURN_CODE

  , DBPROXY_USERNAME

  , GLOBAL_USERID

  , INSTANCE_ID

  , OS_PROCESS

  , DV_GRANTEE

  , DV_OBJECT_STATUS

FROM audsys.unified_audit_trail where audit_type = 'Database Vault' and DV_ACTION_CODE < 20000;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27

View created.

SQL> GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO AUDIT_ADMIN;

GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO AUDIT_VIEWER;

GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_MONITOR;

GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_SECANALYST;

GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO AUDIT_ADMIN;

GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO AUDIT_VIEWER;

GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_MONITOR;

GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_SECANALYST;

Grant succeeded.

SQL>

Grant succeeded.

SQL> GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_MONITOR

                                               *

ERROR at line 1:

ORA-01917: user or role 'DV_MONITOR' does not exist

SQL> GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_SECANALYST

                                               *

ERROR at line 1:

ORA-01917: user or role 'DV_SECANALYST' does not exist

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL> GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_MONITOR

                                             *

ERROR at line 1:

ORA-01917: user or role 'DV_MONITOR' does not exist

SQL>

GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_SECANALYST

                                             *

ERROR at line 1:

ORA-01917: user or role 'DV_SECANALYST' does not exist

SQL>

SQL>

SQL>

SQL> alter view audsys.dv$configuration_audit compile;

alter view audsys.dv$enforcement_audit compile;

View altered.

SQL>

View altered.

SQL>

SQL> select owner,object_name,object_type from dba_objects where status='INVALID' and object_type not like '%JAVA%' and owner='SYS';

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

-----------------------

SYS

DRIPARSE

PACKAGE BODY

SYS

DRIXMD

PACKAGE BODY

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

-----------------------

SYS

DRVDDL

PACKAGE BODY

SYS

DRVXTAB

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

-----------------------

PACKAGE BODY

SYS

LBAC_EXP

PACKAGE BODY

SYS

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

-----------------------

OLS_ENFORCEMENT

PACKAGE BODY

6 rows selected.

SQL> set linesize 150

column owner format a20

column object_name format a30

select owner, object_name, object_type, status from dba_objects

where object_name like ('%CONFIGURATION_AUDIT%') or object_name like ('%ENFORCEMENT_AUDIT%')

order by 1,2;SQL> SQL> SQL>   2    3

OWNER                OBJECT_NAME                    OBJECT_TYPE             STATUS

-------------------- ------------------------------ ----------------------- -------

AUDSYS               DV$CONFIGURATION_AUDIT         VIEW                    VALID

AUDSYS               DV$ENFORCEMENT_AUDIT           VIEW                    VALID

Thanks

Listener is not coming up with error TNS-12560 TNS-12557 TNS-00527 NL-08014

 Hi All,

Today one of production system we got below error while starting listener.

TNS-12557: TNS:protocol adapter not loadable
 TNS-12560: TNS:protocol adapter error
  TNS-00527: Protocol Adapter not loadable
    NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging
     NL-08002: Diagnosability  context creation failed

Found issue related to permission on directory /var/tmp

Where oracle user doesn't have permission to write. After permission issue fixed listener came up fine.

Thanks,

How to increase SWAP space | On linux increase swap space

 [root@oranode2 Packages]# free -g
             total       used       free     shared    buffers     cached
Mem:             3          3          0          0          0          1
-/+ buffers/cache:          1          1
Swap:            2          0          2
[root@oranode2 Packages]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       3145724 284     -1

[root@oranode2 Packages]#
[root@oranode2 Packages]# dd if=/dev/zero of=/swap_file bs=2G count=1
0+1 records in
0+1 records out
2147479552 bytes (2.1 GB) copied, 8.89754 s, 241 MB/s
[root@oranode2 Packages]# chmod 600 /swap_file
[root@oranode2 Packages]# mkswap /swap_file
Setting up swapspace version 1, size = 2097144 KiB
no label, UUID=4b4ba791-fc09-4eb7-89a1-7571f11a5f20
[root@oranode2 Packages]# vi /etc/fstab

[root@oranode2 Packages]# cat /etc/fstab

#

# /etc/fstab

# Created by anaconda on Fri May  6 22:51:13 2022

#

# Accessible filesystems, by reference, are maintained under '/dev/disk'

# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info

#

/dev/mapper/rhel-root   /                       xfs     defaults        1 1

UUID=05356daf-d70a-4ad8-b3cd-e723f9b11202 /boot                   xfs     defaults        1 2

/dev/mapper/rhel-swap   swap                    swap    defaults        0 0

/dev/sdb1 /u02 xfs     defaults        0 0

/swap_file  swap  swap  defaults  0 0

[root@oranode2 Packages]#  swapon /swap_file
[root@oranode2 Packages]#  free -g
             total       used       free     shared    buffers     cached
Mem:             3          1          2          0          0          0
-/+ buffers/cache:          1          2
Swap:            4          0          4

Thanks,

Add a new disk in Linux

 Hi All,

Sometimes we might need to a disk to the linux systems. Once storage side allocation done we can follow below steps. Modify path as per your environment and requirement.

[root@oranode1 ~]# lsblk
NAME          MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda             8:0    0   30G  0 disk
├─sda1          8:1    0  500M  0 part /boot
└─sda2          8:2    0 29.5G  0 part
  ├─rhel-root 253:0    0 26.5G  0 lvm  /
  └─rhel-swap 253:1    0    3G  0 lvm  [SWAP]
sdb             8:16   0   30G  0 disk
sr0            11:0    1 1024M  0 rom
[root@oranode1 ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-62914559, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-62914559, default 62914559):
Using default value 62914559
Partition 1 of type Linux and of size 30 GiB is set
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@oranode1 ~]# lsblk
NAME          MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda             8:0    0   30G  0 disk
├─sda1          8:1    0  500M  0 part /boot
└─sda2          8:2    0 29.5G  0 part
  ├─rhel-root 253:0    0 26.5G  0 lvm  /
  └─rhel-swap 253:1    0    3G  0 lvm  [SWAP]
sdb             8:16   0   30G  0 disk
└─sdb1          8:17   0   30G  0 part
sr0            11:0    1 1024M  0 rom
[root@oranode1 ~]# lsblk -f
NAME          FSTYPE      LABEL UUID                                   MOUNTPOINT
sda
├─sda1        xfs               05356daf-d70a-4ad8-b3cd-e723f9b11202   /boot
└─sda2        LVM2_member       og6zr5-d0yl-edhh-CFFl-aqL2-gBCA-rqkdsW
  ├─rhel-root xfs               b0c3ea90-e651-40e8-bd27-62b461914edd   /
  └─rhel-swap swap              5a9c4a80-1b23-4065-8a74-33aab0a167e4   [SWAP]
sdb
└─sdb1
sr0
[root@oranode1 ~]# mkfs.xfs /dev/sdb1
meta-data=/dev/sdb1              isize=256    agcount=4, agsize=1966016 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=0
data     =                       bsize=4096   blocks=7864064, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
log      =internal log           bsize=4096   blocks=3839, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

[root@oranode1 u01]# mkdir /u02

[root@oranode1 u01]# mount /dev/sdb1 /u02

[root@oranode1 u01]# lsblk
NAME          MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda             8:0    0   30G  0 disk
├─sda1          8:1    0  500M  0 part /boot
└─sda2          8:2    0 29.5G  0 part
  ├─rhel-root 253:0    0 26.5G  0 lvm  /
  └─rhel-swap 253:1    0    3G  0 lvm  [SWAP]
sdb             8:16   0   30G  0 disk
└─sdb1          8:17   0   30G  0 part /u02
sr0            11:0    1 1024M  0 rom

[root@oranode1 u01]# df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root   27G   15G   12G  57% /
devtmpfs               697M     0  697M   0% /dev
tmpfs                  706M   96K  706M   1% /dev/shm
tmpfs                  706M  9.0M  697M   2% /run
tmpfs                  706M     0  706M   0% /sys/fs/cgroup
/dev/sda1              497M  122M  376M  25% /boot
/dev/sdb1               30G   33M   30G   1% /u02

Add an entry like below in  /etc/fstab

/dev/sdb1 /u02 xfs     defaults        0 0

We are done!!!!

Thanks,

PostgreSQL - How to handle "duplicate key value violates unique constraint"

One of application was not coming up and during DB connection it was showing:

duplicate key value violates unique constraint"

To reset the Primary Key performed below steps and application started successfully.

PostgreSQL hot standby. ( DR solution for PostgreSQL)

I followed below steps to create the hot standby.

PRIMARY SERVER - 192.168.230.50

STANDBY SERVER - 192.168.230.51 

On Primary:
===========

postgres=# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'H1j@ck123';

CREATE ROLE

postgres=# \du

                                     List of roles

 Role name  |                         Attributes                         |  Member of

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

 hecm_read | Password valid until infinity                              | {}

 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 read_user  |                                                            | {readaccess}

 readaccess | Cannot login                                               | {}

 replica    | Replication                                                | {}

Change below settings on postgresql.conf:

archive_mode = on

#archive_command = 'true'

archive_command = 'cp -i %p /u01/PostgreSQL/10/data/archive/%f'

hot_standby = on

max_wal_size = 1GB

max_wal_senders = 10

wal_level = hot_standby

wal_log_hints = on

wal_keep_segments = 10

wal_receiver_status_interval = 5s

hot_standby_feedback = on

listen_addresses = '*' 

Add below entries in pg_hba.conf

host    replication     replica      192.168.230.51/24            md5

On Standby:
===========

Set below parameters in postgresql.conf:

archive_mode = on

#archive_command = 'true'

archive_command = 'cp -i %p /u01/PostgreSQL/10/data/archive/%f'

hot_standby = on

max_wal_size = 1GB

max_wal_senders = 10

wal_level = hot_standby

wal_log_hints = on

wal_keep_segments = 10

wal_receiver_status_interval = 5s

hot_standby_feedback = on

-bash-4.2$ telnet 192.168.230.50 5432

Trying 192.168.230.50...

telnet: connect to address 192.168.230.50: No route to host

Firewall disabled on Primary so it able to connect from standby

-bash-4.2$ ./pg_basebackup -h 192.168.230.50 -D /u01/PostgreSQL/10/data -U replica -v -P

Password:

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

pg_basebackup: write-ahead log start point: 0/15000028 on timeline 1

pg_basebackup: starting background WAL receiver

293182/293182 kB (100%), 1/1 tablespace

pg_basebackup: write-ahead log end point: 0/15000130

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

[root@pgstand ~]# cd /u01/PostgreSQL/10/data/

[root@pgstand data]# cat recovery.conf

standby_mode = 'on'

primary_conninfo = 'host=192.168.230.50 port=5432 user=replica password=replicauser@'

restore_command = 'cp ///u01/PostgreSQL/10/data/archive/%f %p'

trigger_file = '/tmp/postgresql.trigger.5432'

-bash-4.2$ ./pg_ctl start -D /u01/PostgreSQL/10/data

waiting for server to start....2020-06-18 18:15:52.293 IST [4117] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2020-06-18 18:15:52.293 IST [4117] LOG:  listening on IPv6 address "::", port 5432

2020-06-18 18:15:52.295 IST [4117] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2020-06-18 18:15:52.406 IST [4117] LOG:  redirecting log output to logging collector process

2020-06-18 18:15:52.406 IST [4117] HINT:  Future log output will appear in directory "log".

 done

server started

Now standby is ready as a hot standby.

Check replication status on Primary:

====================================

postgres=# \x

Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+---------------------------------
pid              | 24732
usesysid         | 75624
usename          | replica
application_name | walreceiver
client_addr      | 192.168.230.51
client_hostname  |
client_port      | 41052
backend_start    | 2020-06-18 18:15:52.940476+05:30
backend_xmin     | 219065
state            | streaming
sent_lsn         | 0/16000140
write_lsn        | 0/16000140
flush_lsn        | 0/16000140
replay_lsn       | 0/16000140
write_lag        | 00:00:00.000509
flush_lag        | 00:00:00.00106
replay_lag       | 00:00:00.00122
sync_priority    | 0
sync_state       | async

Testing:
========

Create an object in Primary and see if that replicates to Secondary:

On Primary:
===========

postgres=# create database newtestdb;

CREATE DATABASE

On Standby:
===========

postgres=# \l

                                  List of databases

    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

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

 newtestdb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 newdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +

            |          |          |             |             | postgres=CTc/postgres+

            |          |          |             |             | readaccess=c/postgres

 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +

            |          |          |             |             | postgres=CTc/postgres+

            |          |          |             |             | readaccess=c/postgres

 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

            |          |          |             |             | postgres=CTc/postgres

 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

            |          |          |             |             | postgres=CTc/postgres

postgres=# \c newtestdb

You are now connected to database "newtestdb" as user "postgres".

newtestdb=# \dt+ t

 public | t    | table | postgres | 8192 bytes |

newtestdb=# create table D  ( name text);

ERROR:  cannot execute CREATE TABLE in a read-only transaction

Thanks,