Oracle Database Commands and Practical Examples: June 2021

Tuesday, 29 June 2021

OCI - Connection from AppTier ( Public Subnet ) to DBTier (Private Subnet)

Connection from AppTier ( Public Subnet ) to DBTier (Private Subnet)

Select the any of the bucket in OCI

Upload key to Objects bucket

Upload key to the Objects

Select Pre Authenticated Request

Create Pre -Authenticated Request

Copy Pre authenticated request URL

Login to app tier instance public subnet

[opc@appvm ~]$ mkdir dbtier1_key

[opc@appvm ~]$ cd dbtier1_key

[opc@appvm dbtier1_key]$ pwd

/home/opc/dbtier1_key

[opc@appvm dbtier1_key]$

Download the private key file into the keys subdirectory /home/opc/dbtier1_key

[opc@appvm dbtier1_key]$ wget https://objectstorage.ap-mumbai-1.oraclecloud.com/p/DK-PWGjxeY9XthOioCZIRO7ijKkT3ANVDkc9SsdpCs7fn4YOBuN7olL6TCME4i0/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/dbtier_private_keydb_ssh-key-2021-06-28.key.key

--2021-06-28 13:07:08-- https://objectstorage.ap-mumbai-1.oraclecloud.com/p/DK-PWGjxeY9XthOioCZIRO7i-jKkT3ANVDkc9SsdpCs7fn4YOBuN7olL6TCME4i0/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/dbtier_private_keydb_ssh-key-2021-06-28.key.key

Resolving objectstorage.ap-mumbai-1.oraclecloud.com (objectstorage.ap-mumbai-1.oraclecloud.com)... 134.70.76.3

Connecting to objectstorage.ap-mumbai-1.oraclecloud.com (objectstorage.ap-mumbai-1.oraclecloud.com)|134.70.76.3|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 1679 (1.6K) [application/octet-stream]

Saving to: ‘dbtier_private_keydb_ssh-key-2021-06-28.key.key’

100%[=============================================================================================================================>] 1,679 --.-K/s in 0s

2021-06-28 13:07:08 (270 MB/s) - ‘dbtier_private_keydb_ssh-key-2021-06-28.key.key’ saved [1679/1679]

[opc@appvm dbtier1_key]$ ll

total 4

-rw-rw-r--. 1 opc opc 1679 Jun 28 13:01 dbtier_private_keydb_ssh-key-2021-06-28.key.key

[opc@appvm dbtier1_key]$

Change key permission to chmod 600

[opc@appvm dbtier1_key]$ chmod 600 dbtier_private_keydb_ssh-key-2021-06-28.key.key

[opc@appvm dbtier1_key]$ ll

total 4

-rw-------. 1 opc opc 1679 Jun 28 13:01 dbtier_private_keydb_ssh-key-2021-06-28.key.key

[opc@appvm dbtier1_key]$

[opc@appvm dbtier1_key]$ hostname

appvm

Login dbtier instance private subnet from public subnet using downloaded key

[opc@appvm dbtier1_key]$ ssh -i dbtier_private_keydb_ssh-key-2021-06-28.key.key opc@10.0.2.118

Last login: Mon Jun 28 13:01:53 2021

Welcome to Autonomous Linux

Effective kernel version is 5.4.17-2102.201.3.el7uek.x86_64

Please add OCI notification service topic OCID with

$ sudo al-config -T [topic OCID]

[opc@dbvm1 ~]$ hostname

dbvm1

[opc@dbvm1 ~]$

[opc@dbvm1 ~]$ cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.2.118 dbvm1.dbtier1.varunvcn.oraclevcn.com dbvm1

[opc@dbvm1 ~]$

OCI - Create Instance and connect on linux machine

Configuring connection to the vm from a Linux shell session

Create Compute Instance

Select Image and shape

Networking : Select subnet

Save private Key and public key and create instance

Login to any linux server that we wanted to connect and create directory copy private and public key to linux directory so we can get connect to apptier instance

[root@srv3 app]# mkdir -p /u01/apptierkey

[root@srv3 app]# cp ssh-key-2021-06-28.key Public_ssh-key-2021-06-28.key.pub /u01/apptierkey

[root@srv3 app]# cd /u01/apptierkey

[root@srv3 apptierkey]# ll

total 8

-rwxr-x--- 1 root root 399 Jun 28 13:07 Public_ssh-key-2021-06-28.key.pub

-rwxr-x--- 1 root root 1679 Jun 28 13:07 ssh-key-2021-06-28.key

Provide chmod 600 permission to private key

[root@srv3 apptierkey]# chmod 600 ssh-key-2021-06-28.key

[root@srv3 apptierkey]#

Copy public IP address

[root@srv3 apptierkey]# ll

total 8

-rwxr-x--- 1 root root 399 Jun 28 13:07 Public_ssh-key-2021-06-28.key.pub

-rw------- 1 root root 1679 Jun 28 13:07 ssh-key-2021-06-28.key

[root@srv3 apptierkey]# ssh -i /u01/apptierkey/ssh-key-2021-06-28.key opc@140.238.242.52

The authenticity of host '140.238.242.52 (140.238.242.52)' can't be established.

RSA key fingerprint is f3:2a:55:5b:b4:c3:43:b4:80:82:cd:20:85:dd:d5:6e.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '140.238.242.52' (RSA) to the list of known hosts.

[opc@appvm ~]$ id

uid=1000(opc) gid=1000(opc) groups=1000(opc),4(adm),10(wheel),190(systemd-journal) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[opc@appvm ~]$ hostname

appvm

OCI - Create Virtual Cloud Network

Oracle Cloud Infrastructure - Create Virtual Cloud Network

Select Virtual Cloud Network

Create VCN

Create Public Subnet

Mention name , subnet type , CIDR Block, ROUTE TABLE COMPARTMENT , Security Lists

Subnet Access : Public Subnet / Private Subnet

DNS Label

DNS DOMAIN NAME

DHCP OPTIONS COMPARTMENT

Security Lists

Create PUBLIC SUBNET

Create Private Subnet named as DBTier1

Select Private Subnet options

Subnet Created

Create INTERNET GATEWAY for public and private subnet access

Create Route Table

Set the Destination to 0.0.0.0/0, which typically means any host

Monday, 21 June 2021

OCI - Expdp utilities dumpfile to bucket object storage directly

Login to autonomous DB with admin user and set DBA_CREDENTIALS to schema

 
C:\Users\varun>sqlplus admin/Cloud$123456@orcladb_medium
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 17 17:42:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 17 2021 17:27:17 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> col OWNER for a10
SQL> col CREDENTIAL_NAME for a20

SQL> SELECT OWNER, CREDENTIAL_NAME FROM DBA_CREDENTIALS;

OWNER      CREDENTIAL_NAME
---------- --------------------
ADMIN      ADMIN_CRED_NAME
TEST       ADMIN_CRED_NAME
SOE        SOE_CREDENTIAL
SOE        ADMIN_CRED_NAME


SQL> ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'SOE.ADMIN_CRED_NAME';

Database altered.

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_CREDENTIAL';

PROPERTY_VALUE
--------------------------------------------------------------------------------
SOE.ADMIN_CRED_NAME

SQL>

Take expdp backup mentioning credential name and OCI bucket URL

C:\Users\varun>expdp soe/Abc#123456789@orcladb_medium \ filesize=5GB \ dumpfile=default_credential:https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/expsoe%U.dmp \ parallel=2 \ logfile=expdosoeschema.log \ directory=staging2

Export: Release 19.0.0.0.0 - Production on Thu Jun 17 18:00:43 2021

Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "SOE"."SYS_EXPORT_SCHEMA_01": soe/********@orcladb_medium filesize=5GB \ dumpfile=default_credential:https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/expsoe%U.dmp \ parallel=2 \ logfile=expdosoeschema.log \ directory=staging2

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SOE"."ORDER_ITEMS" 170.4 MB 3735896 rows

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

. . exported "SOE"."ORDERS" 114.9 MB 1352070 rows

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

. . exported "SOE"."INVENTORIES" 15.27 MB 902171 rows

. . exported "SOE"."LOGON" 9.065 MB 442208 rows

. . exported "SOE"."CUSTOMERS" 4.983 MB 45703 rows

. . exported "SOE"."ADDRESSES" 4.084 MB 55703 rows

. . exported "SOE"."CARD_DETAILS" 2.402 MB 55703 rows

. . exported "SOE"."PRODUCT_DESCRIPTIONS" 224.1 KB 1000 rows

. . exported "SOE"."PRODUCT_INFORMATION" 186.4 KB 1000 rows

. . exported "SOE"."EMP" 51.39 KB 879 rows

. . exported "SOE"."SALES" 40.78 KB 1000 rows

. . exported "SOE"."WAREHOUSES" 36.03 KB 1000 rows

. . exported "SOE"."JOB_HISTORY" 14.60 KB 246 rows

. . exported "SOE"."JOBS" 5.976 KB 23 rows

. . exported "SOE"."ORDERENTRY_METADATA" 5.609 KB 4 rows

. . exported "SOE"."DEPT" 5.632 KB 7 rows

. . exported "SOE"."V" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SOE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SOE.SYS_EXPORT_SCHEMA_01 is:

https://swiftobjectstorage.ap-mumbai-1.oraclecloud.com/v1/bmdsoh48o0kf/bucket-20210415-1806/expsoe01.dmp

https://swiftobjectstorage.ap-mumbai-1.oraclecloud.com/v1/bmdsoh48o0kf/bucket-20210415-1806/expsoe02.dmp

Job "SOE"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 17 12:31:40 2021 elapsed 0 00:00:53

OCI - Rotating Wallets for autonomous database

As an good practice Changing database connections periodically recommended , Here we create new wallet for ATB after creating it. Existing wallet became invalid and we are unable to create connections using on premises client or operating system.