Oracle Database Commands and Practical Examples

Tuesday 26 May 2020

Crontab- Disk Threshold Alert

#### Disk Alert #################

Crontab Details :

#0,15,30,45 * * * * /u04/MasterDB/scripts/DR_check/diskalert_threshold.sh

Script Output:

oracle@test02:~> cat /u04/MasterDB/scripts/disk_threshod.sh

# Shell script to monitor or watch the disk space

# set admin email so that you can get email

ADMIN="varun.yadav@abc.com"

# set alert level 90% is default

ALERT=90

df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;

#echo $output

usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )

partition=$(echo $output | awk '{ print $2 }' )

if [ $usep -ge $ALERT ]; then

i echo "Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)" |

mail -s " Alert: Almost out of disk space" $usep $ADMIN

done

oracle@test02:~>

Crontab -RMAN Backup scripts

RMAN BACKUP Scripts

Crontab Details:

Scheduled scripts

05 04 * * * /u04/MasterDB/scripts/rman_DB_FULL.sh 2>&1 >>/u04/MasterDB/rmanbackup_PRODB/RMAN_PRODB_FULL.log

Rman Backup Status on Mail

35 06 * * * /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh

Scripts Details output :

RMAN SCRIPTS

oracle@test02:~> cat /u04/MasterDB/scripts/rman_PRODB_FULL.sh

ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1

DD=`date +%d%m%y`

export ORACLE_HOME

ORACLE_SID=PRODB

export ORACLE_SID

Rman_Log=/u04/MasterDB/scripts/"$ORACLE_SID"_bkp${DD}.log

touch $Rman_Log

/u02/app/oracle/product/12.1.0/dbhome_1/bin/rman target test/test1234 @/u04/MasterDB/scripts/rman_PRODB_FULL.rcv >> /u03/rmanbackup_PRODB/rman_RODB_FULL.log

oracle@test02:~>

oracle@test02:~> cat /u04/MasterDB/scripts/rman_PRODB_FULL.rcv

run

{

allocate channel c1 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';

allocate channel c2 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';

allocate channel c3 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';

allocate channel c4 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';

crosscheck backup;

crosscheck archivelog all;

report obsolete;

delete noprompt obsolete;

sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';

backup full database format '/u03/rmanbackup_PRODB/%d_%U.bckp' plus archivelog;

copy current controlfile to '/u03/rmanbackup_PRODB/control01_%d_%T_%U.bak';

backup current controlfile for standby format '/u03/rmanbackup_RODB/standbycontrol_%d_%T_%U.ctl';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

oracle@test02:~>

Rman Backup Status

oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh

. /home/oracle/.bash_profile

DR_log=/u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.log

printf "to:varun.yadav@abc.com\n" > $DR_log

#printf "cc:abc@abccom,core@abc.com\n" >> $DR_log

printf "subject: SMRPRODB Database RMAN Backup STATUS AND DETAILS " >> $DR_log

sqlplus -s "/as sysdba" <<EOF >> $DR_log

set lines 200

PROMPT DATABASE NAME

PROMPT =================

Select name,open_mode,database_role,db_unique_name from v\$database;

PROMPT RMAN backup and Size Details

PROMPT ==================================================

set lines 200

col START_TIME for a15

col END_TIME for a15

col TIME_TAKEN_DISPLAY for a10

col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10

col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11

col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10

col output_device_type heading "Device_TYPE" for a11

SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,

input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO

FROM v\$rman_backup_job_details

WHERE end_time > sysdate -3;

select ctime "Date"

, decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type

, bsize "Size MB"

from (select trunc(bp.completion_time) ctime

, backup_type

, round(sum(bp.bytes/1024/1024),2) bsize

from v\$backup_set bs, v\$backup_piece bp

where bs.set_stamp = bp.set_stamp

and bs.set_count = bp.set_count

and bp.status = 'A'

group by trunc(bp.completion_time), backup_type)

order by 1, 2;

EOF

/usr/sbin/sendmail -t < $DR_log

oracle@test02:~>

Monday 25 May 2020

RMAN - Loss of the Password File Recovery example

Recovery from Loss of the Password File

Remove or move password file at OS level

suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs # mv orapwplm orapwplm_pwd

Error during remote connection

oracle@suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> sqlplus sys/test@plm as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon May 25 13:48:35 2020

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

Create password file at OS level.

orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwplm password=test entries=5 format=12

Login database again remotely

oracle@suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> sqlplus sys/test@plm as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 24 11:43:24 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

PLM READ WRITE PRIMARY