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;

Archivelog File

 archive log list

show parameter archive_dest

alter system set log_archive_dest_1= 'LOCATION=F:\labs\archivelog' scope=both

list archivelog all; 

list copy of archivelog until time 'SYSDATE-1'; 

list copy of archivelog from time 'SYSDATE-1'; 

list copy of archivelog from time 'SYSDATE-1' until time 'SYSDATE-2'; 

list copy of archivelog from sequence 1000; 

list copy of archivelog until sequence 1500; 

list copy of archivelog from sequence 1000 until sequence 1500;

delete archivelog all;

delete archivelog until time 'SYSDATE-1'; 

delete archivelog from time 'SYSDATE-1'; 

delete archivelog from time 'SYSDATE-1' until time 'SYSDATE-2'; 

delete archivelog from sequence 1000; 

delete archivelog until sequence 1500; 

delete archivelog from sequence 1000 until sequence 1500;

delete noprompt archivelog until time 'SYSDATE-1';

crosscheck archivelog all;

list expired archivelog all;
delete expired archivelog all;

restore database preview;
restore datafile 14 preview;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW SUMMARY;

run{
recover table "LABS"."MM_ITEM_STOCK" until time "to_date('14-09-2023 08:16:00','dd-mm-yyyy hh24:mi:ss')"
auxiliary destination 'g:\oracle_backup';
}

RUN {
set until time "to_date('08-AUG-2023 09:00', 'DD-MON-YYYY HH24:MI')";
restore database;
recover database;
}

Duplicate a Database Using RMAN in Oracle Database 12c Release 2

startup mount;

alter database archivelog;

archive log list;

show parameter db_recovery_file

needed in both server

mkdir -p /u01/app/oracle/fast_recovery_area

alter system set db_recovery_file_dest_size=5g scope=both;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

scp orapworcl oracle@[auxiliary databasae]:/u01/app/oracle/product/12.2.0/db_1/dbs

create pfile='/home/oracle/initorcl.ora' from spfile;

scp initorcl.ora oracle@[auxiliary databasae]:/u01/app/oracle/product/12.2.0/db_1/dbs

listener configuration and tns configuration in both server such as global database name orcl,clone

mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/oradata/orcl/

setting below parameter in duplicate database pfile

*.db_recovery_file_dest_size=5368709120

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

in duplicate database pfile like this

orcl.__data_transfer_cache_size=0

orcl.__db_cache_size=603979776

orcl.__inmemory_ext_roarea=0

orcl.__inmemory_ext_rwarea=0

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=671088640

orcl.__sga_target=989855744

orcl.__shared_io_pool_size=50331648

orcl.__shared_pool_size=268435456

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='12.2.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'

*.db_recovery_file_dest_size=5368709120

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.diagnostic_dest='/u01/app/oracle'

*.db_block_size=8192

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.local_listener='LISTENER_ORCL'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

*.memory_target=1570m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=320

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

startup pfile=/u01/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora nomount

rman target sys/sys123@orcl auxiliary sys/sys123@clone

or 

rman target sys/sys123@orcl auxiliary /

duplicate database to 'orcl' from active database nofilenamecheck;

Data import using db link

create public database link link_orcl
connect to scott
identified by ***
usint 'local';

grant imp_full_database,exp_full_database to scott;
impdp network_link=link_orcl
impdp network_link=link_orcl directory=test logfile=test.log
impdp network_link=link_orcl directory=test logfile=test.log tables=dept