SQL> CONN SYS/SYS AS SYSDBA Connected. SQL> set serveroutput on SQL> SQL> set linesize 121 SQL> SQL> DECLARE l_credit_card_no VARCHAR2(19) := ‘1234-5678-9012-3456’; l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no); l_key RAW(128) := utl_raw.cast_to_raw(‘abcdefgh’); l_encrypted_raw RAW(2048); l_decrypted_raw RAW(2048); BEGIN dbms_output.put_line(‘Original : ‘ || l_credit_card_no); l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw, dbms_crypto.des_cbc_pkcs5, l_key); dbms_output.put_line(‘Encrypted : ‘ || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw))); l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkcs5, key => […]


FAST START FAILOVER (FSFO) CONFIGURATION USING  FAR SYNC NODE AS OBSERVER IN DATAGUARD USING DGMGRL   1.For data guard DGMGRL configuration and Far Sync setup please follow below link:- http://clouddba.co/step-step-oracle-active-data-guard-far-sync-rac/   2.Please check current database status DGMGRL> show database  ebilpd1;   Database – ebilpd1   Role:               PRIMARY Intended State:     TRANSPORT-ON Instance(s): EBILPD11 EBILPD12   Database Status: SUCCESS   DGMGRL> show […]

Clean *.trc ,*.trm and *.aud older than 1 day clean oracle home

#!/bin/bash . /home/oracle/.bash_profile export log=/u01/app/oracle/shells/clean_rdbms_audit.log echo $ORACLE_HOME | tee $log echo “DELETING AUDIT LOGS FROM RDBMS” | tee -a $log date | tee -a $log df -m $ORACLE_HOME | tee -a $log /bin/find /u01/app/oracle/ -type f -name “*.trc” -mtime +1 -exec rm {} \; find $ORACLE_BASE/admin -type f -name “*.aud” -mtime +5 -exec rm {} \; find $ORACLE_BASE/diag/rdbms -type f […]

Tablespace usage considering autoextend on

This script will show  tablespaces from all databases in server which are full by 85 percent.This script will take care autoextend on clause.This means it will show tablespace usage if growth is exceeding autoextended space. 1.The shell script:- for j in ORCL1 ORCL2 do export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export ORACLE_SID=$i #$ORACLE_HOME/bin/sqlplus -S monitor/m0n1t0r@”$j” << EOF >> tb_check_all_`date +\%d\%m\%Y\%H`.log $ORACLE_HOME/bin/sqlplus -S […]

Script to sync check of applied archive log between 2 RAC databases

1.First create the shell script mentioning all environmental variables adjusted to your environment. vi dataguard_dr_sync_check.sh #!/bin/bash cd /u01/app/oracle/shells for k in ORCL do export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export ORACLE_SID=$k $ORACLE_HOME/bin/sqlplus -S monitor@”$k”/xxx << EOF >> dataguard_dr_sync_check_orcl_`date +\%d\%m\%Y\%H`.log @dataguard_dr_sync_check_orcl.sql exit EOF done 2.For 2 node RAC,below will be dataguard_dr_sync_check_orcl.sql script .For 4 nodes,just add another 2 sql with thread and union […]

RMAN incremental Backup cumulative and differential

RMAN Incremental backup This backups only datafile blocks changed since specified previous backup. 1.This strategy could be followed to make incrementally updated backup .These incrementally updated backup will be used to make updated image copy will all roll forward image. 2.Reduce amount of time to take backup. 3.Save network bandwidth To be able to recover changes to objects created with […]

ASM overview and commands

  Automatic Storage Management (ASM) A.Overview of Automatic Storage Management (ASM) Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to […]

Unix basic command and shell script for DBA and SYSADMIN

DIRECTORY STRUCTURE IN UNIX   Directory Contains bin/ Required Boot-time binaries boot/ Boot configuration files for the OS loader and kernel image dev/ Device files etc/ System configuration files and scripts home/ User/Sub branch directories lib/ Main OS shared libraries and kernel modules Lost+found/ Storage directory for “recovered” files mnt/ Temporary point to connect devices to proc/ Pseudo directory structure containing […]

Basic oracle database creation and maintenance in windows and Linux

  DB CREATION STEPS IN WINDOWS SETTING AN ENVIRONMENT(FOR WINDOWS) ORADIM -new -sid <new_sid> -intpwd oracle -startmode AUTO -pfile c:\oracle\admin\<new_sid>\pfile\init<new_sid>.ora   PASSWORD FILE MANAGEMENT   Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to    exclusive or shared.  If this is set, then a valid passwordfile should exist in ORACLE_HOME/dbs or created using orapwd  as orapwd file=/u01/oracle/V816/dbs/orapwV722 password=oracle entries=1  STARTING UP WITH […]