Friday, September 3, 2021

MySQL Locking Behaviour different from Oracle with FK invovled

 There is no locking observed to run following code in concurrent sessions in Oracle database: (S1 = first session, S2 = second session)

-- simulate FK locking in Oracle


create table p (id number, name varchar2(10), 

constraint p_pk primary key (id));

create table c (id number, name varchar2(10), pid number,

constraint c_pk primary key (id), 

constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a');

insert into p values (2,'b');

insert into c values (1,'a',1);

commit;


-- Test 1, update p while insert into c with same pid

(S1) update p set name='c' where id=2;

(S2) insert into c values (2,'b',2);


-- Test 2, insert into c while update p with same pid


(S1) insert into c values (2,'b',2);

(S2) update p set name='c' where id=2;


-- Test 3, update p1 while update p2 with same pid


(S1) update p set name='c' where id=;

(S2) update c set pid=2 where id=1;

(S2) update c set pid=1 where id=1;


Lock Error "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" observed in MySQL as child table update requires lock on parent table primary key.


-- simulate FK locking in MySQL

create table p (id int, name varchar(10), 

constraint p_pk primary key (id));

create table c (id int, name varchar(10), pid int,

constraint c_pk primary key (id), 

constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a');

insert into p values (2,'b');

insert into c values (1,'a',1);

commit;

SET autocommit=0;


-- Test 1, update p while insert into c with same pid

(S1) update p set name='c' where id=2;

(S2) insert into c values (2,'b',2);



MySQL [test]> insert into c values (2,'b',2);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


---TRANSACTION 2977, ACTIVE 48 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 50, OS thread handle 47659528083200, query id 16389 10.1.1.49 admin update

insert into c values (2,'b',2)

------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 2977 lock mode S locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000ba0; asc       ;;

 2: len 7; hex 02000000ac0151; asc       Q;;

 3: len 1; hex 63; asc c;;


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

---TRANSACTION 2976, ACTIVE 132 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16431 10.1.1.49 admin starting

show engine innodb status


-- Test 2, insert into c while update p with same pid


(S1) insert into c values (2,'b',2);

(S2) update p set name='c' where id=2;


MySQL [test]> update p set name='c' where id=2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


---TRANSACTION 2994, ACTIVE 12 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 50, OS thread handle 47659528083200, query id 16777 10.1.1.49 admin updating

update p set name='c' where id=2

------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 2994 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000b9e; asc       ;;

 2: len 7; hex 010000013202d3; asc     2  ;;

 3: len 1; hex 62; asc b;;


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

---TRANSACTION 2989, ACTIVE 24 sec

3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16762 10.1.1.49 admin


-- Test 3, update p1 while update p2 with same pid


(S1) update p set name='c' where id=1;

(S2) update c set pid=2 where id=1;

(S2) update c set pid=1 where id=1;


MySQL [test]> update c set pid=2 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MySQL [test]> update c set pid=1 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction



---TRANSACTION 3001, ACTIVE 69 sec updating or deleting

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2

MySQL thread id 50, OS thread handle 47659528083200, query id 16964 10.1.1.49 admin updating

update c set pid=1 where id=1

------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 3001 lock mode S locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 000000000bb8; asc       ;;

 2: len 7; hex 02000002040151; asc       Q;;

 3: len 1; hex 63; asc c;;


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

---TRANSACTION 3000, ACTIVE 78 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16942 10.1.1.49 admin



Tuesday, August 17, 2021

How to troubleshot "ORA-29106: Cannot import PKCS #12 wallet."

 [donghua@myhost ~]$ sql user/pass@db1_ssl

SQLcl: Release 21.2 Production on Tue Aug 17 03:01:21 2021

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

  USER          = 

  URL           = jdbc:oracle:oci8:@db1_ssl

  Error Message = ORA-29106: Cannot import PKCS #12 wallet.

  USER          = 

  URL           = jdbc:oracle:thin:@db1_ssl

  Error Message = IO Error: Unknown host specified


Make sure that wallet is created with -auto_login instead of -auto_login_local

orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd Welcome123 -auto_login

 

1. Use -auto_login_local option to create an auto login wallet (cwallet.sso) that is local to both the computer on which it is created and the user who created it.
2. Make sure "chmod 644 /u01/app/oracle/wallet/*" if you intend to share the wallet to other users 


Monday, August 16, 2021

How to troubleshoot "ORA-28367: wallet does not exist"

There are multiple ways to specify Oracle Wallet locations. Make sure you use "select wrl_parameter from v$encryption_wallet" to find out where the Oracle instance "think" the location should be.


Method 1: set Wallet location using "wallet_root" initialisation parameter (In this case, don't not need to specify keystone location in the SQL command lines)


SQL> alter system set wallet_root='/u01/app/oracle/admin/orcl/wallet' scope=spfile;

SQL> shutdown immediate

SQL> startup

SQL> alter system set tde_configuration='keystore_configuration=file' scope=both;


SQL> select con_id, keystore_mode from v$encryption_wallet;

    CON_ID KEYSTORE

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

     1 NONE

     2 UNITED

     3 UNITED

SQL> administer key management create keystore identified by Welcome123;

SQL> administer key management create auto_login keystore from keystore identified by Welcome123;



SQL> ! ls -l /u01/app/oracle/admin/orcl/wallet/tde/

total 8

-rw------- 1 oracle oinstall 2600 Aug 16 09:26 cwallet.sso

-rw------- 1 oracle oinstall 2555 Aug 16 09:22 ewallet.p12


SQL> administer key management set keystore open force keystore identified by Welcome123 container=all;

SQL>  select * from v$encryption_wallet;

WRL_TYPE         WRL_PARAMETER                 STATUS       WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC     CON_ID

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

FILE             /u01/app/oracle/admin/orcl/wallet/tde/    OPEN_NO_MASTER_KEY   PASSWORD         SINGLE    NONE UNDEFINED      1

FILE                                   CLOSED       UNKNOWN          SINGLE    UNITED   UNDEFINED      2

FILE                                   OPEN_NO_MASTER_KEY   PASSWORD         SINGLE    UNITED   UNDEFINED      3

administer key management set key force keystore identified by Welcome123 with backup; 

SQL> administer key management set key force keystore identified by Welcome123 with backup; 

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE         WRL_PARAMETER                 STATUS       WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC     CON_ID

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

FILE             /u01/app/oracle/admin/orcl/wallet/tde/    OPEN         PASSWORD         SINGLE    NONE NO         1

FILE                                   CLOSED       UNKNOWN          SINGLE    UNITED   UNDEFINED      2

FILE                                   OPEN_NO_MASTER_KEY   PASSWORD         SINGLE    UNITED   UNDEFINED      3

SQL> alter session set container=PDB1;


Session altered.

SQL> administer key management set key force keystore identified by Welcome123 with backup; 

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE         WRL_PARAMETER                 STATUS       WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC     CON_ID

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

FILE                                   OPEN         PASSWORD         SINGLE    UNITED   NO         3

SQL> SELECT con_id, key_id FROM v$encryption_keys;

    CON_ID KEY_ID

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

     3 Aex7NbwgzU+Nv346V0TJXRkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Method 2: Set Wallet location in $ORACLE_HOME/network/admin/sqlnet.ora using "ENCRYPTION_WALLET_LOCATION" or "WALLET_LOCATION" (In this case, please specify keystone location in the SQL command lines)


ENCRYPTION_WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /u01/app/oracle/admin/orcl/wallet)

     )

   )


WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /u01/app/oracle/admin/orcl/wallet)

     )

   )


SQL> administer key management create keystore '/u01/app/oracle/admin/orcl/wallet' identified by Welcome123;

SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/orcl/wallet' identified by Welcome123;


SQL>  ! ls -l /u01/app/oracle/admin/orcl/wallet

total 8

-rw------- 1 oracle oinstall 2600 Aug 16 08:55 cwallet.sso

-rw------- 1 oracle oinstall 2555 Aug 16 08:50 ewallet.p12


Saturday, August 14, 2021

Oracle 21c on Linux Silent installation

Oracle 21c for Linux platform is available today. You can download from here: https://www.oracle.com/database/technologies/oracle-database-software-downloads.html


To keep the information brief, I will just post commands I used for 21c installation. You can always reference to official installation guide and other posts for more information:


Take note "-sampleSchema true" is not longer supported in 21c.
>> [FATAL] [DBT-12513] Sample schema is not supported in oracle home version 21.0.0.0.0.

And OracleHome is ReadOnly Home

[oracle@ol8 bin]$ orabasehome 
/u01/app/oracle/homes/OraDB21Home1
[oracle@ol8 bin]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/db21c:/u01/app/oracle:OraDB21Home1:Y:

Unzip the software
$ unzip LINUX.X64_213000_db_home.zip -d /u01/db21c

Install the 21c database binary (21c is in-place installation)
$ cd /u01/db21c
$ ./runInstaller  -silent -ignorePrereqFailure -waitforcompletion \
    oracle.install.option=INSTALL_DB_SWONLY \
    UNIX_GROUP_NAME=oinstall \
    INVENTORY_LOCATION=/u01/app/oraInventory \
    ORACLE_HOME=/u01/db21c \
    ORACLE_HOME_NAME='OraDB21Home1' \
    ORACLE_BASE=/u01/app/oracle \
    oracle.install.db.InstallEdition=EE \
    oracle.install.db.OSDBA_GROUP=dba \
    oracle.install.db.OSOPER_GROUP=oper \
    oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
    oracle.install.db.OSDGDBA_GROUP=dgdba \
    oracle.install.db.OSKMDBA_GROUP=kmdba \
    oracle.install.db.OSRACDBA_GROUP=racdba \
    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
    DECLINE_SECURITY_UPDATES=true

Create Listener
export ORACLE_BASE=/u01/app/oracle; export ORACLE_HOME=/u01/db21c; /u01/db21c/bin/netca /orahome /u01/db21c /instype typical /inscomp client,oraclenet,javavm,server,ano /insprtcl tcp /cfg local /authadp NO_VALUE /responseFile /u01/db21c/network/install/netca_typ.rsp /lisport 1521 /silent /orahnam OraDB21Home1

Create Database
export ORACLE_HOME=/u01/db21c; export ORACLE_BASE=/u01/app/oracle; /u01/db21c/bin/dbca -silent -createDatabase  -emConfiguration NONE  -templateName 'General_Purpose.dbc' -storageType FS -datafileDestination '/u01/app/oracle/oradata' -datafileJarLocation '/u01/db21c/assistants/dbca/templates' -oratabLocation /etc/oratab  -runCVUChecks false -continueOnNonFatalErrors true -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb1 -gdbName 'orcl21c' -sid 'orcl21c' -initParams filesystemio_options=setall -ignorePrereqs -sysPassword WElcome123## -systemPassword WElcome123## -pdbAdminPassword WElcome123## -dbsnmpPassword WElcome123##   

Verify Database

[oracle@ol8 bin]$ sqlplus / as sysdba


SQL*Plus: Release 21.0.0.0.0 - Production on Sat Aug 14 22:12:48 2021

Version 21.3.0.0.0


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



Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0


SQL> select banner from v$version;


BANNER

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

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production


SQL> select comp_name,version,status from dba_registry;


COMP_NAME     VERSION   STATUS

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

Oracle Database Catalog Views     21.0.0.0.0   VALID

Oracle Database Packages and Types  21.0.0.0.0   VALID

Oracle Real Application Clusters    21.0.0.0.0   OPTION OFF

JServer JAVA Virtual Machine     21.0.0.0.0   VALID

Oracle XDK     21.0.0.0.0   VALID

Oracle Database Java Packages     21.0.0.0.0   VALID

OLAP Analytic Workspace     21.0.0.0.0   VALID

Oracle XML Database     21.0.0.0.0   VALID

Oracle Workspace Manager     21.0.0.0.0   VALID

Oracle Text     21.0.0.0.0   VALID

Oracle Multimedia     21.0.0.0.0   VALID

Oracle OLAP API     21.0.0.0.0   VALID

Spatial     21.0.0.0.0   VALID

Oracle Locator     21.0.0.0.0   VALID

Oracle Label Security     21.0.0.0.0   VALID

Oracle Database Vault     21.0.0.0.0   VALID


16 rows selected.


SQL> select name,description,type,priority from v$bgprocess where paddr<>'00' order by name;


NAME  DESCRIPTION       TYPE  PRIORITY

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

AQPC  AQ Process Coord     TS

BG00       SLAVE TS

BG00       SLAVE TS

BG00       SLAVE TS

BG01       SLAVE TS

BG01       SLAVE TS

BG01       SLAVE TS

CJQ0  Job Queue Coordinator     TS

CKPT  checkpoint     TS

CL00  process cleanup slave       SLAVE TS

CLMN  process cleanup     TS

D000  Dispatchers       SLAVE TS

DBRM  DataBase Resource Manager      TS

DBW0  db writer process 0     TS

DIA0  diagnosibility process 0     TS

DIAG  diagnosibility process     TS

FENC  IOServer fence monitor     TS

GEN0  generic0     TS

GEN1  generic1     TS

GEN2  generic2     TS

LG00  Log Writer Slave       SLAVE TS

LG01  Log Writer Slave       SLAVE TS

LGWR  Redo etc.      RT

LREG  Listener Registration     TS

M000  MMON slave class 1       SLAVE TS

M001  MMON slave class 1       SLAVE TS

M002  MMON slave class 1       SLAVE TS

M003  MMON slave class 1       SLAVE TS

M004  MMON slave class 1       SLAVE TS

MMAN  Memory Manager     TS

MMNL  Manageability Monitor Process 2     TS

MMON  Manageability Monitor Process     TS

OFSD  Oracle File Server BG     TS

P000  Parallel query slave       SLAVE TS

P001  Parallel query slave       SLAVE TS

P002  Parallel query slave       SLAVE TS

P003  Parallel query slave       SLAVE TS

PMAN  process manager     TS

PMON  process cleanup     TS

PSP0  process spawner 0      TS

PXMN  PX Monitor     TS

Q002  QMON MS       SLAVE TS

Q003  QMON MS       SLAVE TS

QM02  QMON MS       SLAVE TS

RCBG  Result Cache: Background     TS

RECO  distributed recovery     TS

S000  Shared servers       SLAVE TS

SCMN       SLAVE TS

SCMN       SLAVE TS

SCMN       SLAVE TS

SCMN       SLAVE TS

SMCO  Space Manager Process     TS

SMON  System Monitor Process     TS

SVCB  services background monitor     TS

TMON  Transport Monitor      TS

TT00  Redo Transport       SLAVE TS

TT01  Redo Transport       SLAVE TS

TT02  Redo Transport       SLAVE TS

VKRM  Virtual sKeduler for Resource Manager     TS

VKTM  Virtual Keeper of TiMe process     RT

VOSD  VOS Daemon     TS

W000  space management slave pool       SLAVE TS

W001  space management slave pool       SLAVE TS

W002  space management slave pool       SLAVE TS

W003  space management slave pool       SLAVE TS

W004  space management slave pool       SLAVE TS

W005  space management slave pool       SLAVE TS

W006  space management slave pool       SLAVE TS

W007  space management slave pool       SLAVE TS


69 rows selected.

Monday, August 9, 2021

What are the ports opened for Managed Microsoft AD integration?

 



% aws ec2 describe-security-groups --query "SecurityGroups[?GroupName=='d-90676d216f_controllers'].IpPermissions"

[

    [

        {

            "FromPort": 445,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 445,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 138,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 138,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 464,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 464,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 464,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 464,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 389,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 389,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 53,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 53,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 389,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 389,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 123,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 123,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 445,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 445,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": -1,

            "IpProtocol": "icmp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": -1,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 3268,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 3269,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 1024,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 65535,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 88,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 88,

            "UserIdGroupPairs": []

        },

        {

            "IpProtocol": "-1",

            "IpRanges": [],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "UserIdGroupPairs": [

                {

                    "GroupId": "sg-021192171c72d607a",

                    "UserId": "904546716712"

                }

            ]

        },

        {

            "FromPort": 135,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 135,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 636,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 636,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 53,

            "IpProtocol": "tcp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 53,

            "UserIdGroupPairs": []

        },

        {

            "FromPort": 88,

            "IpProtocol": "udp",

            "IpRanges": [

                {

                    "CidrIp": "0.0.0.0/0"

                }

            ],

            "Ipv6Ranges": [],

            "PrefixListIds": [],

            "ToPort": 88,

            "UserIdGroupPairs": []

        }

    ]

]

aws ec2 describe-security-groups --query "SecurityGroups[?GroupName=='d-90676d216f_controllers'].IpPermissions[*].{Protocol:IpProtocol,FromPort:FromPort,ToPort:ToPort,IpRange:IpRanges[0].CidrIp}" --output table

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

|            DescribeSecurityGroups             |

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

| FromPort |   IpRange   | Protocol   | ToPort  |

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

|  445     |  0.0.0.0/0  |  udp       |  445    |

|  138     |  0.0.0.0/0  |  udp       |  138    |

|  464     |  0.0.0.0/0  |  udp       |  464    |

|  464     |  0.0.0.0/0  |  tcp       |  464    |

|  389     |  0.0.0.0/0  |  udp       |  389    |

|  53      |  0.0.0.0/0  |  udp       |  53     |

|  389     |  0.0.0.0/0  |  tcp       |  389    |

|  123     |  0.0.0.0/0  |  udp       |  123    |

|  445     |  0.0.0.0/0  |  tcp       |  445    |

|  -1      |  0.0.0.0/0  |  icmp      |  -1     |

|  3268    |  0.0.0.0/0  |  tcp       |  3269   |

|  1024    |  0.0.0.0/0  |  tcp       |  65535  |

|  88      |  0.0.0.0/0  |  tcp       |  88     |

|  None    |  None       |  -1        |  None   |

|  135     |  0.0.0.0/0  |  tcp       |  135    |

|  636     |  0.0.0.0/0  |  tcp       |  636    |

|  53      |  0.0.0.0/0  |  tcp       |  53     |

|  88      |  0.0.0.0/0  |  udp       |  88     |

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


Monday, August 2, 2021

SSLAuthentication with Oracle database in Summary (Mutual authentication, 2 way authentication, certificate login)

 Setup steps, please reference doc here: https://docs.oracle.com/en-us/iaas/data-safe/doc/create-self-signed-certificate-db-system-client-authentication-enabled.html

Scenario 1: Client doesn't import Server certificate/CA and Server doesn't import client certificate/CA.

(Regardless SSL_CLIENT_AUTHENTICATION=TRUE or FALSE)

Result: connection failed

Message: ORA-29024: Certificate validation failure

Scenario 2: Client import Server certificate/CA and Server doesn't import client certificate/CA.

2.1 Testing with SSL_CLIENT_AUTHENTICATION = FALSE on server

Result: Success

2.2 Testing with SSL_CLIENT_AUTHENTICATION = TRUE on server

Result: Connection failed

Message: ORA-28860: Fatal SSL error

Message in Listener log:

2021-08-02T04:50:44.966526+00:00
02-AUG-2021 04:50:44 * (ADDRESS=(PROTOCOL=tcps)(HOST=10.2.0.80)(PORT=9782)) * <unknown connect data> * 542
TNS-00542: SSL Handshake failed
 TNS-12560: TNS:protocol adapter error

Scenario 3: Client import Server certificate/CA and Server import client certificate/CA.

(Regardless SSL_CLIENT_AUTHENTICATION=TRUE or FALSE)

Result: OK

Take note that 2-way SSL verification only happens if both client and server setting are true.

Scenario 4: Built-on Scenario 3, Authenticate user using SSL Certification

[oracle@ip-10-2-0-80 ~]$ orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123
Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject:        CN=ip-10-2-0-80.ap-southeast-1.compute.internal
Trusted Certificates: 
Subject:        CN=ip-10-2-0-80.ap-southeast-1.compute.internal
Subject:        CN=ip-10-2-0-83.ap-southeast-1.compute.internal
take note: 1. ops$ and 2. identified externally. (globally is EUS user, not Certificate user)
for certification login, need SSL_CLIENT_AUTHENTICATION = TRUE on both client/server
SQL> CREATE USER ops$clientuser1 IDENTIFIED EXTERNALLY AS 'CN=ip-10-2-0-80.ap-southeast-1.compute.internal';
SQL> grant resource,connect to ops$clientuser1;

[oracle@ip-10-2-0-80 admin]$ sqlplus /@pdb1_ssl
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 2 05:47:28 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show user;
USER is "OPS$CLIENTUSER1"
  
SQL> select * from v$session_connect_info where sid=userenv('SID');
{
  "results" : [
    {
      "items" : [
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        },
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "Authentication service for Linux: Version 19.0.1.0.0 - Production",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        },
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "Encryption service for Linux: Version 19.0.1.0.0 - Production",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        },
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        }
      ]
    }
  ]
}