VNC Server Install

 dnf install tigervnc tigervnc-server-module

optional checking : dnf group install "Server with GUI"

vncpasswd

vi /etc/tigervnc/tigervnc.users

need added 

:1=oracle

vi /etc/tigervnc/tigervnc-config-defaults

need added

session=gnome
geometry=1280x1024

systemctl daemon-reload

systemctl enable --now vncserver@:1.service

optional : cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver@.service 

systemctl start --now vncserver@:1.service

systemctl status --now vncserver@:1.service

Business Intelligence

 [oracle@alamin bin]$ ./datamodel.sh uploadrpd -I /home/oracle/voucher_pool.rpd -SI ssi -U weblogic -P weblogic123

Oracle ASM Configure

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

yum install -y oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

oracle-rdbms-server-11gR2-preinstall (For 11g Forms)

systemctl stop firewalld
systemctl disable firewalld

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper 
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin
groupadd -g 54330 racdba

useradd oracle
passwd oracle
useradd grid
passwd grid

usermod -g oinstall -G dba,asmadmin,asmdba,racdba oracle
usermod -g oinstall -G dba,asmadmin,asmdba,asmoper grid

Manually install pre-requisites

yum install -y bc    
yum install -y binutils
#yum install -y compat-libcap1
yum install -y compat-libstdc++-33
#yum install -y dtrace-modules
#yum install -y dtrace-modules-headers
#yum install -y dtrace-modules-provider-headers
#yum install -y dtrace-utils
yum install -y elfutils-libelf
yum install -y elfutils-libelf-devel
yum install -y fontconfig-devel
yum install -y glibc
yum install -y glibc-devel
yum install -y ksh
yum install -y libaio
yum install -y libaio-devel
#yum install -y libdtrace-ctf-devel
yum install -y libXrender
yum install -y libXrender-devel
yum install -y libX11
yum install -y libXau
yum install -y libXi
yum install -y libXtst
yum install -y libgcc
yum install -y librdmacm-devel
yum install -y libstdc++
yum install -y libstdc++-devel
yum install -y libxcb
yum install -y make
yum install -y net-tools # Clusterware
yum install -y nfs-utils # ACFS
yum install -y python # ACFS
yum install -y python-configshell # ACFS
yum install -y python-rtslib # ACFS
yum install -y python-six # ACFS
yum install -y targetcli # ACFS
yum install -y smartmontools
yum install -y sysstat
yum install -y unixODBC

 

# New for OL8

yum install -y libnsl
yum install -y libnsl.i686
yum install -y libnsl2
yum install -y libnsl2.i686

useradd grid
passwd grid

mkdir -p /u01/app/grid/19c/grid_home
chown -R grid:oinstall /u01
chmod -R 775 /u01/

# Grid bash profile

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/grid/19c/grid_home
export ORACLE_SID=+ASM
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

#Oracle bash profile

export TMP=/tmp
export TMPDIR=$TMP
export inventory_loc=/u01/app/oraInventory
export inst_group=dba
export ORACLE_HOSTNAME=alamin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/19c/db_1
export ORACLE_SID=orcl
#export DATA_DIR=/u02/oradata
JAVA_HOME=/u02/middleware/jdk1.8.0_391
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$JAVA_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

yum -y install wget oracleasm kmod-oracleasm oracleasm-support

fdisk /dev/sdb
fdisk /dev/sdc
fdisk /dev/sdd

oracleasm init
oracleasm configure -i

oracleasm createdisk ASMDISK_DATA /dev/sdb1
oracleasm createdisk ASMDISK_DATA1 /dev/sdc1
oracleasm createdisk ASMDISK_FRA /dev/sdd1

oracleasm scandisks

unzip asm binary 

cd /u01/app/19/grid/cv/rpm/
rpm -ivh cvuqdisk-1.0.10-1.rpm

Install grid binary

chown -R grid:asmadmin /dev/sdb1
chown -R grid:asmadmin /dev/sdc1
chown -R grid:asmadmin /dev/sdd1

chmod -R 660 /dev/sdb1
chmod -R 660 /dev/sdc1
chmod -R 660 /dev/sdd1

vi /usr/local/bin/set_permission.sh
chmod +x /usr/local/bin/set_permission.sh

vi /etc/systemd/system/set_permission.service

[Unit]
Description=Fix Raw Disk Permission

[Service]
Type=oneshot
ExecStart=/usr/local/bin/set_permission.sh

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable set_permission.service

cd /u01/app/19c/grid_home/crs/install
./roothas.sh

export CV_ASSUME_DISTID=OEL7.8

XML Data Uploading in oracle database

 CREATE TABLE tablexml1 ( xml_data  XMLTYPE) ; 

create directory xml_dir as 'G:\';

Demo xml data

<records>
<record>
<column1>Value1</column1>
<column2>123</column2>
</record>
<record>
<column1>Value2</column1>
<column2>456</column2>
</record>
</records>

create or replace PROCEDURE load_xml (p_dir       IN  VARCHAR2,
                                  p_filename  IN  VARCHAR2) AS
 l_bfile  BFILE := BFILENAME(p_dir, p_filename);
 l_clob   CLOB;

 l_dest_offset   INTEGER := 1;
 l_src_offset    INTEGER := 1;
 l_bfile_csid    NUMBER  := 0;
 l_lang_context  INTEGER := 0;
 l_warning       INTEGER := 0;
 BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);

 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
 -- loadfromfile deprecated.
 -- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
 DBMS_LOB.loadclobfromfile (
   dest_lob      => l_clob,
   src_bfile     => l_bfile,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset,
   bfile_csid    => l_bfile_csid ,
   lang_context  => l_lang_context,
   warning       => l_warning);
   DBMS_LOB.fileclose(l_bfile);

  INSERT INTO tablexml1(
   xml_data
  )
 VALUES (
    XMLTYPE.createXML(l_clob)
 );


--delete tablexml2;
/*
insert into tablexml2       
SELECT xt.*
FROM   tablexml1 x, 
       XMLTABLE('/records/record' 
         PASSING x.xml_data 
         COLUMNS  
           column1     VARCHAR2(10)  PATH 'column1', 
           column2     number PATH 'column2'
         ) xt ; 

delete tablexml1;*/

 COMMIT;

 DBMS_LOB.freetemporary (l_clob);
 END load_xml;

SELECT xt.*

FROM   tablexml1 x, 

       XMLTABLE('/records/record' 

         PASSING x.xml_data 

         COLUMNS  

           column1     VARCHAR2(4)  PATH 'column1', 

           column2     VARCHAR2(10) PATH 'column2'

         ) xt ; 

exec load_xml('XML_DIR','data_file.xml');

SELECT XMLELEMENT("Emp", XMLELEMENT("name", e.first_name ||' '|| e.last_name),

                           XMLELEMENT ( "hiredate", e.hire_date)) AS "result" 

FROM employees e 

WHERE employee_id > 200 ;

JSON

CREATE OR REPLACE PROCEDURE get_customer_json (
customer_id IN NUMBER,
json_output OUT CLOB -- OUT parameter for JSON output
) IS
BEGIN
SELECT JSON_OBJECT(
'Customer_Name' VALUE c.cust_name,
'Customer_ID' VALUE c.cust_id,
'Accounts' VALUE JSON_ARRAYAGG(a.acct_id)
)
INTO json_output
FROM customers c, accounts a
WHERE c.cust_id =a.cust_id
GROUP BY c.cust_name, c.cust_id;
END;
⚡ Call Procedure ⚡
DECLARE
l_json CLOB;
BEGIN
get_customer_json(1, l_json);
DBMS_OUTPUT.PUT_LINE(l_json);
END;
⚡ Output ⚡
{"Customer_Name":"Alice","Customer_ID":1,"Accounts":[101,102]}

DeploymentRuleSet creation for java security

 Java Signing directory
cd D:\middleware\oracle_common\jdk\bin

Making ruleset.xml

<ruleset version="1.0+">
<rule>
    <id location="http://localhost:9001" />
    <action permission="run" version="1.8*" />
</rule>
</ruleset>

Making jar file
jar.exe -cvf DeploymentRuleSet.jar ruleset.xml

Making jks file
keytool.exe -genkey -alias Selfsigned -keystore "Selfsigned.jks" -keyalg RSA -keysize 2048 -startdate "2000/01/01 00:00:00" -validity 18262

Making cer file
keytool.exe -export -file "Selfsigned.cer" -alias Selfsigned -keystore "Selfsigned.jks"
c:\windows\system32\certutil.exe -addstore "Root" .\Selfsigned.cer

Jar signer
jarsigner.exe -keystore "Selfsigned.jks" -signedjar "DeploymentRuleSet.jar" DeploymentRuleSet.jar selfsigned

Testing
jarsigner -verify -verbose -certs DeploymentRuleSet.jar
jarsigner -verify -keystore Selfsigned.jks DeploymentRuleSet.jar

Oracle Wallet Setup

 select * from v$encryption_wallet

mkdir -p /home/oracle/wallet

export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin

Add the following entries into the client "/u01/app/oracle/product/19.0.0/dbhome_1/network/admin" file, with your preferred wallet location.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /home/oracle/wallet)
     )
   )
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

orapki wallet create -wallet "/home/oracle/wallet" -pwd "password" -auto_login

or 

mkstore -wrl "/home/oracle/wallet" -create

orapki wallet add -wallet D:\app\oracle\admin\orcl\wallet -trusted_cert -cert D:\app\oracle\admin\orcl\wallet\WE2.crt -pwd password

orapki wallet display -wallet D:\app\oracle\admin\orcl\wallet -pwd password

mkstore -wrl "/home/oracle/wallet" -createCredential orcl test test

mkstore -wrl "/home/oracle/wallet" -createCredential orcl2 test2 test2

mkstore -wrl "/home/oracle/wallet" -listCredential

mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

mkstore -wrl <wallet_location> -deleteCredential <db_alias>

administer key management create keystore 'C:\wallet' identified by password;

administer key management set keystore open identified by password container=all

Connection syntax "/@db_alias"

sqlplus /@orcl

sqlplus /@orcl2

begin
apex_instance_admin.validate_email_config;
end;

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => 'mail.xml', 

    description  => 'Allow connections using UTL_TCP',

    principal    => 'APEX_240200',

    is_grant     => TRUE, 

    privilege    => 'connect');

end;

begin

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'mail.xml',

    host        => '*'

);

end;

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

 host => '*',

 ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),

                      principal_name => 'APEX_240200',

                      principal_type => xs_acl.ptype_db)); 

END;

DECLARE

  l_acl_name         VARCHAR2(30) := 'utl_tcp01.xml';

  l_ftp_server_ip    VARCHAR2(20) := 'smtp.office365.com';

  l_ftp_server_name  VARCHAR2(20) := 'smtp.office365.com'; 

  l_username         VARCHAR2(30) := 'APEX_240200';

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => l_acl_name, 

    description  => 'Allow connections using UTL_TCP',

    principal    => l_username,

    is_grant     => TRUE, 

    privilege    => 'connect',

    start_date   => SYSTIMESTAMP,

    end_date     => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.add_privilege ( 

    acl         => l_acl_name, 

    principal   => l_username,

    is_grant    => FALSE, 

    privilege   => 'connect', 

    position    => NULL, 

    start_date  => NULL,

    end_date    => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => l_acl_name,

    host        => l_ftp_server_ip, 

    lower_port  => NULL,

    upper_port  => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => l_acl_name,

    host        => l_ftp_server_name, 

    lower_port  => NULL,

    upper_port  => NULL);

  COMMIT;

END;

BEGIN

   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(

      acl => '/sys/acls/utl_tcp01.xml');

END;

declare

l_body_html clob;

l_body clob;

begin

l_body:='<p>This is Header</p>';

l_body_html:='<html><head></head><body>'||

'<p>This is paragraph</p>'||'<br>

<h1>This is a header</h1>'||

'</body></html>';

  apex_mail.send(

    p_to   => 'mdalamin@lantaburgroup.com',

    p_from => 'mdalamin@lantaburgroup.com',

    p_body=>l_body,

    p_body_html => l_body_html,

    p_subj => 'Test Mail '||to_char(SYSDATE,'YY.MM.DD HH12AM:MI:SS'));

  apex_mail.push_queue();  

end;

QR CODE IN ORACLE REPORT BUILDER

 https://drive.google.com/drive/folders/1Kv901xiIp7dbzcK71CVgHzOylrGmAw-b?usp=sharing

create directory qr_dir as 'D:\QR';
grant read,write on directory qr_dir to test;

@01-ZT_QR_PACKAGE.plb

@02-ZT_QR_BODY.plb

@03-bmp2jpg java

@04-f_bmp2jpg

create or replace procedure qr_code(p_text varchar2) as
lbQR blob;
begin
  lbQR := f_bmp2jpg( 
ZT_QR.F_QR_AS_BMP(
p_data => p_text,
p_error_correction => 'M')
);
    ZT_QR.p_save_file(lbQR, p_text||'.jpg' ,'QR_DIR');
END;

function CF_QRFormula return Char is
begin
  RETURN 'D:\QR\'||:P_QR||'.jpg';
end;