Oracle Simple Help

মঙ্গলবার, ১১ নভেম্বর, ২০২৫

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

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_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;

মঙ্গলবার, ৭ নভেম্বর, ২০২৩

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;

মঙ্গলবার, ১১ নভেম্বর, ২০২৫

Business Intelligence

বৃহস্পতিবার, ৮ মে, ২০২৫

Oracle ASM Configure

শুক্রবার, ২২ নভেম্বর, ২০২৪

XML Data Uploading in oracle database

বৃহস্পতিবার, ২৪ অক্টোবর, ২০২৪

DeploymentRuleSet creation for java security

শুক্রবার, ১৯ এপ্রিল, ২০২৪

Oracle Wallet Setup

মঙ্গলবার, ২৬ মার্চ, ২০২৪

QR CODE IN ORACLE REPORT BUILDER

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

create or replace procedure qr_code(p_text varchar2) aslbQR 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 isbegin RETURN 'D:\QR\'||:P_QR||'.jpg';end;

মঙ্গলবার, ৭ নভেম্বর, ২০২৩

Drop database manually

মোট পৃষ্ঠাদর্শন

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

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;