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

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

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_local

or 

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

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>

Connection syntax "/@db_alias"

sqlplus /@orcl

sqlplus /@orcl2

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;

Drop database manually

startup mount;

OR

RMAN>sql 'alter system enable restricted session'; 
Drop database including backups noprompt;

Or sql command
startup mount restrict;
drop database;

Cold Backup in Oracle database

 

Create directory where to install.

mkdir -p /u01/app/oracle/oradata

Move pfile/spfile to $ORACLE/HOME/dbs

scp initorcl.ora spfileorcl.ora oracle@machine_name:$ORACLE/HOME/dbs

Rename controlfile location name in parameter file

Copy file controlfile, datafile, logfile

scp * oracle@machine_name:/u01/app/oracle/oradata

startup mount;

Rename datafile,logfile like below.

alter database rename file '/u01/app/oracle/system01.dbf' to '/u01/app/oracle/oradata/system01.dbf';

alter database rename file '/u01/app/oracle/sysaux01.dbf' to '/u01/app/oracle/oradata/sysaux01.dbf';

alter database rename file '/u01/app/oracle/log01.dbf' to '/u01/app/oracle/oradata/log01.dbf';

alter database open;