Oracle Database Query Running more than 1 minutes and will kill automatically

Hello Friends, 

Below script help us to get SID and Serial details which is running more than 1 minutes and will kill automatically if you will keep below steps into a .sql file and run it from sql prompt. Please refer below screenshot. 

SQL> select  round(last_call_et / 60,2) Time_in_mins, sid, serial# from gv$session where type<>'BACKGROUND' and username<>'SYS' and username <> 'DBSNMP' and last_call_et / 60 > 1;

no rows selected

SQL> select  round(last_call_et / 60,2) Time_in_mins, sid, serial# from gv$session where type<>'BACKGROUND' and username<>'SYS' and username <> 'DBSNMP' and last_call_et / 60 > 1;

TIME_IN_MINS        SID    SERIAL#

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

        12.6         15         21

        12.6        125         63

        12.6        138         55

SQL> set pagesize 0 linesize 1000 feedback off verify off trimspool on heading off echo off

SQL> SET SERVEROUTPUT ON

SQL> SET PAGESIZE 500

SQL> SET LINESIZE 300

SQL> spool /tmp/sid_kill.sql

SQL> select  'alter system kill session '''||sid||','||serial#||'''immediate ;' from gv$session where type<>'BACKGROUND' and username<>'SYS' and username <> 'DBSNMP' and last_call_et / 60 > 1;

alter system kill session '15,21'immediate ;

alter system kill session '125,63'immediate ;

alter system kill session '138,55'immediate ;

SQL> spool off;

SQL> set pagesize 0 linesize 1000 feedback on verify on trimspool on heading on echo on

SQL> @/tmp/sid_kill.sql

SQL> SQL> select  'alter system kill session '''||sid||','||serial#||'''immediate ;' from gv$session where type<>'BACKGROUND' and username<>'SYS' and username <> 'DBSNMP' and last_call_et / 60 > 1;

SQL> alter system kill session '15,21'immediate ;

System altered.

SQL> alter system kill session '125,63'immediate ;

System altered.

SQL> alter system kill session '138,55'immediate ;

System altered.

SQL> spool off;

SQL> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL> select  sid, serial# from gv$session where type<>'BACKGROUND' and username<>'SYS' and username <> 'DBSNMP' and last_call_et / 60 > 1;

no rows selected

TRANSPOSE Column to Rows in Linux script

Below commands I have used in Oracle query where some time I need to put data in IN clause from txt file manually or with other method. But with this commands you can put in script it will automatically generate syntax format for ORACLE IN Clause  

[oracle@oracle11g ]cat sqlid.txt    <<--- Input

c3yw7auzsah15

378r7ukq8caam

9y55n98wubu9d

f7m1bh706uqss

[oracle@oracle11g ]$ cat /tmp//sqlid.txt | while read a

> do

> _sqlid+="'$a',"

> echo $_sqlid >/tmp//_sqlid1.log

> done

[oracle@oracle11g ]$ cat /tmp//_sqlid1.log

'c3yw7auzsah15','378r7ukq8caam','9y55n98wubu9d','f7m1bh706uqss',

[oracle@oracle11g ]$

[oracle@oracle11g ]$ _sid_lst=`  cat /tmp//_sqlid1.log | rev | cut -c2- | rev`

[oracle@oracle11g ]$ echo $_sid_lst

'c3yw7auzsah15','378r7ukq8caam','9y55n98wubu9d','f7m1bh706uqss'     <-- Output

Direct upgrade and Migrate DB from 11.2.0.4 to 19c PDB

 /opt/oracle/product/19c/dbhome_1/jdk/bin/java -jar /opt/oracle/product/19c/dbhome_1/rdbms/admin/autoupgrade.jar -create_sample_file config

cat /home/oracle/autoupg/11204upgrade.cfg

global.autoupg_log_dir=/opt/oracle/cfgtoollogs/autoupgrade

upg1.sid=orcl1

upg1.dbname=orcl1

upg1.start_time=NOW

upg1.source_home=/opt/oracle/product/11g/dbhome_1

upg1.log_dir=/home/oracle/autoupg

upg1.target_home=/opt/oracle/product/19c/dbhome_1

upg1.upgrade_node=oracle11g

upg1.target_version=19.1.0

upg1.run_utlrp=yes

upg1.timezone_upg=yes

upg1.target_cdb=cdb1

/opt/oracle/product/19c/dbhome_1/jdk/bin/java -jar /opt/oracle/product/19c/dbhome_1/rdbms/admin/autoupgrade.jar -config /home/oracle/autoupg/11204upgrade.cfg -mode ANALYZE

/opt/oracle/product/19c/dbhome_1/jdk/bin/java -jar /opt/oracle/product/19c/dbhome_1/rdbms/admin/autoupgrade.jar -config /home/oracle/autoupg/11204upgrade.cfg -mode DEPLOY

How to cut specific characters from string or file name in Linux shell script..

Hello Guys, 

Today I am going to show you how we can cut or get particular string from a file name which is having special char (underscore) and extension of file name in Linux shell script. 

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

Please refer below command which I have used.

ls -ltrh  | grep DBMS_XPLAN_OUTLINE_ > a.log

cat a.log | awk '{print $9}' > /tmp/_a.log

cat  /tmp/_a.log | sed 's/DBMS_XPLAN_OUTLINE_//' > /tmp/_a1.log

cat /tmp/_a1.log | sed "s/\.log$//" > /tmp/_a2.log

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

Please refer output of above command steps by step.

 1.  [oracle@oracle11g newsql]$ ls -ltrh

total 80K

-rw-rw-r-- 1 oracle oracle 5.6K Mar 23 15:34 DBMS_XPLAN_OUTLINE_aykvshm7zsabd.log

-rw-rw-r-- 1 oracle oracle 3.8K Mar 23 15:34 DBMS_XPLAN_OUTLINE_53saa2zkr6wc3.log

-rw-rw-r-- 1 oracle oracle 4.4K Mar 23 15:34 DBMS_XPLAN_OUTLINE_08bqjmf8490s2.log

-rw-rw-r-- 1 oracle oracle 3.5K Mar 23 15:34 DBMS_XPLAN_OUTLINE_bsa0wjtftg3uw.log

-rw-rw-r-- 1 oracle oracle 3.8K Mar 23 15:34 DBMS_XPLAN_OUTLINE_bfujkg8dw1aax.log

-rw-rw-r-- 1 oracle oracle 3.9K Mar 23 15:34 DBMS_XPLAN_OUTLINE_0k8522rmdzg4k.log

-rw-rw-r-- 1 oracle oracle 5.4K Mar 23 15:34 DBMS_XPLAN_OUTLINE_cm5vu20fhtnq1.log

-rw-rw-r-- 1 oracle oracle 3.6K Mar 23 15:34 DBMS_XPLAN_OUTLINE_8hpppyj5kdn3y.log

-rw-rw-r-- 1 oracle oracle 3.8K Mar 23 15:34 DBMS_XPLAN_OUTLINE_g3wrkmxkxzhf2.log

-rw-rw-r-- 1 oracle oracle 3.6K Mar 23 15:34 DBMS_XPLAN_OUTLINE_459f3z9u4fb3u.log

-rw-rw-r-- 1 oracle oracle 3.9K Mar 23 15:34 DBMS_XPLAN_OUTLINE_3w4qs0tbpmxr6.log

-rw-rw-r-- 1 oracle oracle 4.3K Mar 23 15:34 DBMS_XPLAN_OUTLINE_6aq34nj2zb2n7.log

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

2. [oracle@oracle11g newsql]$ ls -ltrh  | grep DBMS_XPLAN_OUTLINE_ > a.log

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

3. [oracle@oracle11g newsql]$ cat a.log

-rw-rw-r-- 1 oracle oracle 5.6K Mar 23 15:34 DBMS_XPLAN_OUTLINE_aykvshm7zsabd.log

-rw-rw-r-- 1 oracle oracle 3.8K Mar 23 15:34 DBMS_XPLAN_OUTLINE_53saa2zkr6wc3.log

-rw-rw-r-- 1 oracle oracle 4.4K Mar 23 15:34 DBMS_XPLAN_OUTLINE_08bqjmf8490s2.log

-rw-rw-r-- 1 oracle oracle 3.5K Mar 23 15:34 DBMS_XPLAN_OUTLINE_bsa0wjtftg3uw.log

-rw-rw-r-- 1 oracle oracle 3.8K Mar 23 15:34 DBMS_XPLAN_OUTLINE_bfujkg8dw1aax.log

-rw-rw-r-- 1 oracle oracle 3.9K Mar 23 15:34 DBMS_XPLAN_OUTLINE_0k8522rmdzg4k.log

-rw-rw-r-- 1 oracle oracle 5.4K Mar 23 15:34 DBMS_XPLAN_OUTLINE_cm5vu20fhtnq1.log

-rw-rw-r-- 1 oracle oracle 3.6K Mar 23 15:34 DBMS_XPLAN_OUTLINE_8hpppyj5kdn3y.log

-rw-rw-r-- 1 oracle oracle 3.8K Mar 23 15:34 DBMS_XPLAN_OUTLINE_g3wrkmxkxzhf2.log

-rw-rw-r-- 1 oracle oracle 3.6K Mar 23 15:34 DBMS_XPLAN_OUTLINE_459f3z9u4fb3u.log

-rw-rw-r-- 1 oracle oracle 3.9K Mar 23 15:34 DBMS_XPLAN_OUTLINE_3w4qs0tbpmxr6.log

-rw-rw-r-- 1 oracle oracle 4.3K Mar 23 15:34 DBMS_XPLAN_OUTLINE_6aq34nj2zb2n7.log

-rw-rw-r-- 1 oracle oracle 4.1K Mar 23 15:34 DBMS_XPLAN_OUTLINE_0b84axbzj7g59.log

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

4. [oracle@oracle11g newsql]$ cat a.log | awk '{print $9}' > /tmp/_a.log

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

5. [oracle@oracle11g newsql]$ cat /tmp/_a.log

DBMS_XPLAN_OUTLINE_aykvshm7zsabd.log

DBMS_XPLAN_OUTLINE_53saa2zkr6wc3.log

DBMS_XPLAN_OUTLINE_08bqjmf8490s2.log

DBMS_XPLAN_OUTLINE_bsa0wjtftg3uw.log

DBMS_XPLAN_OUTLINE_bfujkg8dw1aax.log

DBMS_XPLAN_OUTLINE_0k8522rmdzg4k.log

DBMS_XPLAN_OUTLINE_cm5vu20fhtnq1.log

DBMS_XPLAN_OUTLINE_8hpppyj5kdn3y.log

DBMS_XPLAN_OUTLINE_g3wrkmxkxzhf2.log

DBMS_XPLAN_OUTLINE_459f3z9u4fb3u.log

DBMS_XPLAN_OUTLINE_3w4qs0tbpmxr6.log

DBMS_XPLAN_OUTLINE_6aq34nj2zb2n7.log

DBMS_XPLAN_OUTLINE_0b84axbzj7g59.log

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

6. [oracle@oracle11g newsql]$ cat  /tmp/_a.log | sed 's/DBMS_XPLAN_OUTLINE_//' > /tmp/_a1.log

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

[oracle@oracle11g newsql]$ cat /tmp/_a1.log

aykvshm7zsabd.log

53saa2zkr6wc3.log

08bqjmf8490s2.log

bsa0wjtftg3uw.log

bfujkg8dw1aax.log

0k8522rmdzg4k.log

cm5vu20fhtnq1.log

8hpppyj5kdn3y.log

g3wrkmxkxzhf2.log

459f3z9u4fb3u.log

3w4qs0tbpmxr6.log

6aq34nj2zb2n7.log

0b84axbzj7g59.log

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

7. [oracle@oracle11g newsql]$ cat /tmp/_a1.log | sed "s/\.log$//" > /tmp/_a2.log

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

8. [oracle@oracle11g newsql]$ cat /tmp/_a2.log

aykvshm7zsabd

53saa2zkr6wc3

08bqjmf8490s2

bsa0wjtftg3uw

bfujkg8dw1aax

0k8522rmdzg4k

cm5vu20fhtnq1

8hpppyj5kdn3y

g3wrkmxkxzhf2

459f3z9u4fb3u

3w4qs0tbpmxr6

6aq34nj2zb2n7

0b84axbzj7g59

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

Restarting (1) 9 seconds ago while running docker-compose up -d for bitnami/postgresql-repmgr:12.11.0 image.

 Hello Guys, 

This is blog is related to issue of bitnami/postgresql-repmgr:12.11.0  docker image setup. While I was performing setup of bitnami/postgresql-repmgr image, I was facing below error and docker was running Restarting mode.

INFO  ==> ** Starting PostgreSQL with Replication Manager setup **

INFO  ==> Validating settings in REPMGR_* env vars...

ERROR ==> The node name does not follow the required format. Valid format: ^.*+-[0-9]+$

$ docker ps 

CONTAINER ID   IMAGE                               COMMAND                  CREATED         STATUS                          PORTS     NAMES

f9be9b16b283   bitnami/postgresql-repmgr:12.11.0   "/opt/bitnami/script…"   5 minutes ago   Restarting (1) 42 seconds ago             instance-1-a1

Solution : In my case below docker-compose.yml was used and  found recommendation was do not use  container/service/rep name like instance-1-a1, mean don't use double (-). 

Same updated and fixed my issue.

Original Creating problem:

------------------------------------
version: "3.0"
services:
  instance-1-a1:
    image: bitnami/postgresql-repmgr:12.11.0
    container_name: instance-1-a1
    restart: always
    networks:
      - database
    ports:
      - 5432:5432
    volumes:
      - ./instance-1-a1/conf/:/bitnami/repmgr/conf/
      - ./instance-1-a1/logs/:/opt/bitnami/postgresql/logs/
    environment:
      - POSTGRESQL_PASSWORD=XXXXXXXXXX
      - POSTGRESQL_POSTGRES_PASSWORD=XXXXXXXXX
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_DATABASE=pg-db
      - REPMGR_NODE_NETWORK_NAME=host1
      - REPMGR_NODE_ID=1
      - REPMGR_PASSWORD=xxxxxxxxx
      - REPMGR_PRIMARY_HOST=host1
      - REPMGR_PRIMARY_PORT=5432
      - REPMGR_PARTNER_NODES=host1,host2:5432,host3:5432
      - REPMGR_NODE_NAME=instance-1-a1
      - REPMGR_LOG_LEVEL=DEBUG
      - REPMGR_PORT_NUMBER=5432

networks:
  database:

    external: true

Updated docker-compose.yml which fixed the problem:
--------------------------------------------------------------------

version: "3.0"
services:
  instance-1:
    image: bitnami/postgresql-repmgr:12.11.0
    container_name: instance-1
    restart: always
    networks:
      - database
    ports:
      - 5432:5432
    volumes:
      - ./instance-1-a1/conf/:/bitnami/repmgr/conf/
      - ./instance-1-a1/logs/:/opt/bitnami/postgresql/logs/
    environment:
      - POSTGRESQL_PASSWORD=XXXXXXXXXX
      - POSTGRESQL_POSTGRES_PASSWORD=XXXXXXXXX
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_DATABASE=pg-db
      - REPMGR_NODE_NETWORK_NAME=host1
      - REPMGR_NODE_ID=1
      - REPMGR_PASSWORD=xxxxxxxxx
      - REPMGR_PRIMARY_HOST=host1
      - REPMGR_PRIMARY_PORT=5432
      - REPMGR_PARTNER_NODES=host1,host2:5432,host3:5432
      - REPMGR_NODE_NAME=instance-1
      - REPMGR_LOG_LEVEL=DEBUG
      - REPMGR_PORT_NUMBER=5432

networks:
  database:

    external: true

Note: Updated parameter marked in bold above, do not change hostname or any folder name.

$docker-compose down -v 

$docker-compose up -d 

$  docker ps

CONTAINER ID   IMAGE                               COMMAND                  CREATED          STATUS          PORTS                                       NAMES

c5ad890ff90b   bitnami/postgresql-repmgr:12.11.0   "/opt/bitnami/script…"   37 minutes ago   Up 37 minutes   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   instance-1

Thanks !!!

How to enable or disable Hyper-v in 11th Gen Intel(R) Core(TM) i5-1135G7 @ 2.40GHz[Cores 4] [Logical processors 8]

 Hello Guy's

Last 6 months back I purchased new DELL Inspiron 3501 laptop with below configuration.

Memory: 32 GB

Processor: 11th Gen Intel(R) Core(TM) i5-1135G7 @ 2.40GHz[Cores 4] [Logical processors 8]

Operating System: Microsoft Windows 10 Home Single Language

Due to some project related testing I need to test about hyper-v  enable and disable, but I was surprised because I can't see hyper-v option in "Windows Features" also checked in BIOS unfortunately it was enabled by default and not allowing me to disable it. 

Tried to communicate with Dell they told it is fee-paid services and not ready to tell anything about how to enable or disable this hyper-v option. 

Please refer my snapshot of "Windows Features" box take before perform R&D on my laptop.

I asked with help of some colleague found below code which fixed my problem and I did my testing successfully. 

--> Please copy below lines between # into a .bat file and run. It would take approx 20 minutes to complete download and setup hyper-v, at last it will ask to reboot take a reboot it will show you hyper-v option along with hyper-v console.

#------------------------------------------------------

pushd "%~dp0"

dir /b %SystemRoot%\servicing\Packages\*Hyper-V*.mum >hyper-v_install.txt

for /f %%i in ('findstr /i . hyper-v.txt 2^>nul') do dism /online /norestart /add-package:"%SystemRoot%\servicing\Packages\%%i"

del hyper-v_install.txt

Dism /online /enable-feature /featurename:Microsoft-Hyper-V -All /LimitAccess /ALL

pause

#------------------------------------------------------

Note: Pleas perform above steps at your own risk.

Thanks a lot Team. 

How To Set new hostname or Rename or Change HostName in Linux

 Hello Friends, 

Most of the time when we are working VM or preparing Lab to work on any issue , we can setup one VM machine and don't want to create any new machine so we decided to copy the VM. but Most of us face problem or challenges to change hostname. Like rename node1 to node2 etc.

 We have few steps to change hostname as below.

Note: I have testing these steps on  "3.10.0-1160.el7.x86_64"  OS kernel.

Command list which has been perform for above demo.

1. uname -a

2. hostname 

3. hostname -I

4. hostnamectl status

5. hostnamectl set-hostname node2

6. hostnamectl status

7. ssh 192.168.3.4 (take IP from step 3)

8. hostname # It will show new hostname as "node2"