########################################################################################################
## Database Health Check Report for RAC. You must create two scripts tbs_usage.ksh and disk_space.sh ##
########################################################################################################
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@ Create tbs_usage.ksh @@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
$ vi /u01/scripts/tbs_usage.ksh
#!/bin/ksh
#This script determines the percentage of disk usage.
# If that percentage is greater than 90% then a mail will be sent to user
# a report notifying them of the usage.
######### ENVIORNMENT SETUP##############################
typeset -i warn="80"
typeset -i crit="90"
typeset -i cnt="0"
logdir=/u01/datapump/daily_report
export logdir
currsid=$1
currdb=$2
logfile=$logdir/daily_usage_report_${currsid}_`date +%y%m%d`.txt
tbsfile=$logdir/tbs_usage_report_$currsid_`date +%y%m%d`.txt
asmfile=$logdir/asm_usage_report_$currsid_`date +%y%m%d`.txt
email_list=`cat /u01/scripts/mail_list.lis`
export logfile tbsfile asmfile email_list
progname=`basename $0`
export ORAENV_ASK=NO
export ORACLE_SID=$currsid
. oraenv
pct_thresh=90
HOSTNAME=`hostname`
export HOSTNAME
######### LOG FILE CLEANUP#####################
if [ -e "$logfile" ]; then
rm -f $logfile
fi
if [ -e "$tbsfile" ]; then
rm -f $tbsfile
fi
if [ -e "$asmfile" ]; then
rm -f $asmfile
fi
######### LOGIC FOR CHECKING DISK USAGE ON NODE 1 START ######################
echo "\n" >> $logfile
echo "################## TODAYS DATE IS:" `date` " ###################################" > $logfile
echo "\n" >> $logfile
echo "############################# DISK USAGE REPORT FOR " `hostname` " ########################" >> $logfile
echo "\n" >> $logfile
for disc in `df| egrep '^/dev|li' | egrep -iv 'cdrom|proc|sys|tmp|pts' |awk '{print $7}'`
do
discUsage=`df -g $disc| awk '{print $4}' | cut -d'%' -f1|grep -v Free`
if [ "$discUsage" -ge "$crit" ]; then
Free=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Critical: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free GB" >> $logfile
typeset -i cnt="1"
elif [ "$discUsage" -ge "$warn" ]; then
Free1=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Warning: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free1 GB" >> $logfile
typeset -i cnt="1"
fi
done
if [ "$cnt" -eq "0" ]; then
echo "All Disc Usage of node '$HOSTNAME' is 'Normal'" >> $logfile
fi
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DISK USAGE ON NODE 1 END ##############################
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 START ##############################
ssh node2 /u01/scripts/disk_space.sh
ssh node2 cat /u01/datapump/daily_report/daily_usage_report.txt >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 END ##############################
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 START ##############################
ssh node3 /u01/scripts/disk_space.sh
ssh node3 cat /u01/datapump/daily_report/daily_usage_report.txt >> $logfile
echo "\n" >> $logfile
############### LOGIC FOR CHECKING DISK USAGE ON NODE 2 END #############################
echo "###################### SWAP SPACE SIZE REPORT node1 #######################" >> $logfile
echo "\n" >> $logfile
./check_swap.ksh
cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "###################### SWAP SPACE SIZE REPORT node2 #######################" >> $logfile
echo "\n" >> $logfile
ssh node2 /u01/scripts/check_swap.ksh
ssh node2 cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "###################### SWAP SPACE SIZE REPORT node3 #######################" >> $logfile
echo "\n" >> $logfile
ssh node3 /u01/scripts/check_swap.ksh
ssh node3 cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "############################# END OF SWAP SPACE SIZE REPORT ########################"
########### LOGIC FOR CHECKING TABLESPACE USAGE START ##############################
sqlplus -s '/ as sysdba' << EOF
set echo off
set feedback off
set lines 150 pages 66 feedback off
spool ${tbsfile}
column tablespace_name format a35 heading 'Tablespace'
column used_pct_of_max format 999 heading 'Used % of Max'
column actual_free_gb format 999 heading 'Actual Free GB'
with
tbs_auto as
(select distinct tablespace_name, autoextensible
from dba_data_files
where autoextensible = 'YES'),
files as
(select tablespace_name, count (*) tbs_files,
sum (bytes) total_tbs_bytes
from dba_data_files
group by tablespace_name),
fragments as
(select tablespace_name, count (*) tbs_fragments,
sum (bytes) total_tbs_free_bytes,
max (bytes) max_free_chunk_bytes
from dba_free_space
group by tablespace_name),
autoextend as
(select tablespace_name, sum (size_to_grow) total_growth_tbs
from (select tablespace_name, sum (maxbytes) size_to_grow
from dba_data_files
where autoextensible = 'YES'
group by tablespace_name
union
select tablespace_name, sum (bytes) size_to_grow
from dba_data_files
where autoextensible = 'NO'
group by tablespace_name)
group by tablespace_name)
select a.tablespace_name,
round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) used_pct_of_max,
round(autoextend.total_growth_tbs/1024/1024/1024 - round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024/1024)) actual_free_gb
from dba_tablespaces a, files, fragments, autoextend, tbs_auto
where a.tablespace_name = files.tablespace_name
and a.tablespace_name = fragments.tablespace_name
and a.tablespace_name = autoextend.tablespace_name
and a.tablespace_name = tbs_auto.tablespace_name(+)
-- and round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) >=$pct_thresh
order by used_pct_of_max desc;
spool off
EOF
echo "############################# TABLESPACE USAGE REPORT for $currdb ########################" >> $logfile
echo "\n" >> $logfile
cat $tbsfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING TABLESPACE USAGE END ##############################
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
select name,state,total_mb,free_mb,round(((TOTAL_MB - FREE_MB) / TOTAL_MB)*100) as PCT_USED from v\$asm_diskgroup;
spool off
EXIT;
ENDSQL
#
#
echo "############################# ASM DISKGROUP USAGE REPORT ########################" >> $logfile
cat $asmfile >> $logfile
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
col disk for a20
spool $asmfile
select path DISK,OS_MB "Size in MB",header_status "Disk Type" from v\$asm_disk where header_status IN('CANDIDATE','FORMER') order by os_mb desc;
set heading off
set feedback off
select '*** ASM FREE DISK REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
echo "############################# ASM AVAILABLE DISKS REPORT ########################" >> $logfile
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE END ##############################
#
#
echo "############################# GET DATABASE SIZE REPORT ########################" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
--col disk for a20
spool $asmfile
SELECT ' Data: ' || lpad( round( SUM (bytes) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_data_files
union
SELECT ' Temp: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_temp_files
union
SELECT ' Redo Log: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM v_\$log
order by 1;
set heading off
set feedback off
select '*** DATABASE SIZE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
#
#
echo "\n" >> $logfile
echo "############################# CHECKING INSTANCE STATUS ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
column HOST_NAME format a15
column STATUS format a6
column INSTANCE_NAME format a13
column STIME format a36
column uptime format a55
select Host_Name, Status, Instance_Name
,'Started At: ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime: ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.gv_\$instance;
set heading off
set feedback off
select '*** INSTANCE STATUS END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS END #################################
#
#
echo "############################# GET ALERT LOG ERRORS ########################" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 200
set pagesize 100
spool $asmfile
column MESSAGE_TEXT FORMAT A100;
column ORIGINATING_TIMESTAMP format a40;
select INSTANCE_NUMBER || '-' || INSTANCE_NAME || '-' || HOST_NAME ALERT_LOG_MESSAGE_TEXT from V\$INSTANCE;
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%ORA-%'
union all
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%Global Enqueue Services Deadlock detected%'
order by ORIGINATING_TIMESTAMP desc
;
set heading off
set feedback off
select '*** ALERT LOG SQL END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG END #################################
#
#
echo "############################# GET RMAN REPORT ########################" >> $logfile
########### LOGIC FOR RMAN REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set pagesize 200
set linesize 200
COL STATUS FORMAT a25
COL INPUT_BYTES_DISPLAY FORMAT a20
COL OUTPUT_BYTES_DISPLAY FORMAT a20
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES_DISPLAY,
OUTPUT_BYTES_DISPLAY
FROM V\$RMAN_BACKUP_JOB_DETAILS
where START_TIME between sysdate -7 and sysdate
ORDER BY SESSION_KEY;
set heading off
set feedback off
select '*** RMAN REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT END #################################
#
#
echo "############################# FLASHBACK USAGE REPORT ########################" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set lines 100
col name format a20
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
,round((floor(space_limit / 1024 / 1024) - ceil(space_used / 1024 / 1024) )/ floor(space_limit / 1024 / 1024)*100,2) as PCT_FREE
,round((ceil(space_used / 1024 / 1024) / floor(space_limit / 1024 / 1024))*100,2) as PCT_USED
from v\$recovery_file_dest
order by name;
set heading off
set feedback off
select '*** FLASHBACK USAGE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT END #################################
#
#
echo "############################# GET LOG SWITCH REPORT ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR SWITCH REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 500
set pagesize 500
spool $asmfile
set echo off
SET VERIFY OFF
--Define start date and end date. Enter 0 for today's date.
variable START_DATE number;
variable END_DATE number;
begin
select 30, 0 into :START_DATE, :END_DATE from dual;
end;
/
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
COLUMN DAY FORMAT a6
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v\$log_history a
WHERE
(TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
>=
TO_CHAR(sysdate-:START_DATE, 'DD-MON-YYYY')
)
AND
(TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
<=
TO_CHAR(sysdate-:END_DATE, 'DD-MON-YYYY')
)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
order by 1;
set heading off
set feedback off
select '*** LOG SWITCH REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR LOG SWITCH REPORT END #################################
#
#
rm -f $tbsfile
rm -f $asmfile
mailx -s "Disk Usage report of $currdb for `date +%m-%d-%y`" $email_list < $logfile
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@ Create disk_space.sh @@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
$ vi /u01/scripts/disk_space.sh
#!/bin/ksh
#This script determines the percentage of disk usage.
# If that percentage is greater than 90% then a mail will be sent to user
# a report notifying them of the usage.
######### ENVIORNMENT SETUP##############################
typeset -i warn="80"
typeset -i crit="90"
typeset -i cnt="0"
logdir=/u01/datapump/daily_report
export logdir
logfile=$logdir/daily_usage_report.txt
export logfile
progname=`basename $0`
pct_thresh=90
HOSTNAME=`hostname`
export HOSTNAME
######### LOG FILE CLEANUP#####################
if [ -e "$logfile" ]; then
rm -f $logfile
fi
if [ -e "$tbsfile" ]; then
rm -f $tbsfile
fi
if [ -e "$asmfile" ]; then
rm -f $asmfile
fi
######### LOGIC FOR CHECKING DISK USAGE ON NODE 2 START ######################
echo "#############################DISK USAGE REPORT FOR " `hostname` " ########################" >> $logfile
echo "\n" >> $logfile
for disc in `df| egrep '^/dev|li' | egrep -iv 'cdrom|proc|sys|tmp|pts|shared' |awk '{print $7}'`
do
discUsage=`df -g $disc| awk '{print $4}' | cut -d'%' -f1|grep -v Free`
if [ "$discUsage" -ge "$crit" ]; then
Free=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Critical: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free GB" >> $logfile
typeset -i cnt="1"
elif [ "$discUsage" -ge "$warn" ]; then
Free1=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Warning: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free1 GB" >> $logfile
typeset -i cnt="1"
fi
done
if [ "$cnt" -eq "0" ]; then
echo "All Disc Usage of node '$HOSTNAME' is 'Normal'" >> $logfile
fi
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 END ##############################
###############################################
## Database Health Check Report for NON-RAC ##
###############################################
$ vi /u01/scripts/tbs_usage.ksh
#!/bin/ksh
#This script determines the percentage of disk usage.
# If that percentage is greater than 90% then a mail will be sent to user
# a report notifying them of the usage.
######### ENVIORNMENT SETUP##############################
typeset -i warn="80"
typeset -i crit="90"
typeset -i cnt="0"
logdir=/u01/datapump/daily_report
export logdir
currsid=$1
currdb=$2
logfile=$logdir/daily_usage_report_${currsid}_`date +%y%m%d`.txt
tbsfile=$logdir/tbs_usage_report_$currsid_`date +%y%m%d`.txt
asmfile=$logdir/asm_usage_report_$currsid_`date +%y%m%d`.txt
email_list=`cat /u01/scripts/mail_list.lis`
export logfile tbsfile asmfile email_list
progname=`basename $0`
export ORAENV_ASK=NO
export ORACLE_SID=$currsid
. oraenv
pct_thresh=90
HOSTNAME=`hostname`
export HOSTNAME
######### LOG FILE CLEANUP#####################
if [ -e "$logfile" ]; then
rm -f $logfile
fi
if [ -e "$tbsfile" ]; then
rm -f $tbsfile
fi
if [ -e "$asmfile" ]; then
rm -f $asmfile
fi
######### LOGIC FOR CHECKING DISK USAGE ON NODE 1 START ######################
echo "\n" >> $logfile
echo "################## TODAYS DATE IS:" `date` " ###################################" > $logfile
echo "\n" >> $logfile
echo "############################# DISK USAGE REPORT FOR " `hostname` " ########################" >> $logfile
echo "\n" >> $logfile
for disc in `df| egrep '^/dev|li' | egrep -iv 'cdrom|proc|sys|tmp|pts' |awk '{print $7}'`
do
discUsage=`df -g $disc| awk '{print $4}' | cut -d'%' -f1|grep -v Free`
if [ "$discUsage" -ge "$crit" ]; then
Free=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Critical: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free GB" >> $logfile
typeset -i cnt="1"
elif [ "$discUsage" -ge "$warn" ]; then
Free1=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Warning: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free1 GB" >> $logfile
typeset -i cnt="1"
fi
done
if [ "$cnt" -eq "0" ]; then
echo "All Disc Usage of node '$HOSTNAME' is 'Normal'" >> $logfile
fi
echo "\n" >> $logfile
################ LOGIC FOR CHECKING DISK USAGE ON NODE 1 END ################################
echo "###################### SWAP SPACE SIZE REPORT #######################" >> $logfile
echo "\n" >> $logfile
/check_swap.ksh
cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "############################# END OF SWAP SPACE SIZE REPORT for ########################"
# LOGIC FOR CHECKING TABLESPACE USAGE START ##############################
sqlplus -s '/ as sysdba' << EOF
set echo off
set feedback off
set lines 150 pages 66 feedback off
spool ${tbsfile}
column tablespace_name format a20 heading 'Tablespace'
column used_pct_of_max format 999 heading 'Used % of Max'
column actual_free_gb format 999 heading 'Actual Free GB'
with
tbs_auto as
(select distinct tablespace_name, autoextensible
from dba_data_files
where autoextensible = 'YES'),
files as
(select tablespace_name, count (*) tbs_files,
sum (bytes) total_tbs_bytes
from dba_data_files
group by tablespace_name),
fragments as
(select tablespace_name, count (*) tbs_fragments,
sum (bytes) total_tbs_free_bytes,
max (bytes) max_free_chunk_bytes
from dba_free_space
group by tablespace_name),
autoextend as
(select tablespace_name, sum (size_to_grow) total_growth_tbs
from (select tablespace_name, sum (maxbytes) size_to_grow
from dba_data_files
where autoextensible = 'YES'
group by tablespace_name
union
select tablespace_name, sum (bytes) size_to_grow
from dba_data_files
where autoextensible = 'NO'
group by tablespace_name)
group by tablespace_name)
select a.tablespace_name,
round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) used_pct_of_max,
round(autoextend.total_growth_tbs/1024/1024/1024 - round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024/1024)) actual_free_gb
from dba_tablespaces a, files, fragments, autoextend, tbs_auto
where a.tablespace_name = files.tablespace_name
and a.tablespace_name = fragments.tablespace_name
and a.tablespace_name = autoextend.tablespace_name
and a.tablespace_name = tbs_auto.tablespace_name(+)
-- and round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) >=$pct_thresh
order by used_pct_of_max desc;
spool off
EOF
echo "############################# TABLESPACE USAGE REPORT for $currdb ########################" >> $logfile
echo "\n" >> $logfile
cat $tbsfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING TABLESPACE USAGE END ##############################
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
select name,state,total_mb,free_mb,round(((TOTAL_MB - FREE_MB) / TOTAL_MB)*100) as PCT_USED from v\$asm_diskgroup;
spool off
EXIT;
ENDSQL
echo "############################# ASM DISKGROUP USAGE REPORT ########################" >> $logfile
cat $asmfile >> $logfile
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
col disk for a20
spool $asmfile
select path DISK,OS_MB "Size in MB",header_status "Disk Type" from v\$asm_disk where header_status IN('CANDIDATE','FORMER') order by os_mb desc;
set heading off
set feedback off
select '*** ASM FREE DISK REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
echo "############################# ASM AVAILABLE DISKS REPORT ########################" >> $logfile
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE END ##############################
#
#
echo "############################# GET DATABASE SIZE REPORT ########################" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
--col disk for a20
spool $asmfile
SELECT ' Data: ' || lpad( round( SUM (bytes) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_data_files
union
SELECT ' Temp: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_temp_files
union
SELECT ' Redo Log: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM v_\$log
order by 1;
set heading off
set feedback off
select '*** DATABASE SIZE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
#
#
echo "\n" >> $logfile
echo "############################# CHECKING INSTANCE STATUS ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
column HOST_NAME format a15
column STATUS format a6
column INSTANCE_NAME format a13
column STIME format a36
column uptime format a55
select Host_Name, Status, Instance_Name
,'Started At: ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime: ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_\$instance;
set heading off
set feedback off
select '*** INSTANCE STATUS END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS END #################################
#
#
echo "############################# GET ALERT LOG ERRORS ########################" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 200
set pagesize 100
spool $asmfile
column MESSAGE_TEXT FORMAT A100;
column ORIGINATING_TIMESTAMP format a40;
select INSTANCE_NUMBER || '-' || INSTANCE_NAME || '-' || HOST_NAME ALERT_LOG_MESSAGE_TEXT from V\$INSTANCE;
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%ORA-%'
union all
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%Global Enqueue Services Deadlock detected%'
order by ORIGINATING_TIMESTAMP desc
;
set heading off
set feedback off
select '*** ALERT LOG SQL END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG END #################################
#
#
echo "############################# GET RMAN REPORT ########################" >> $logfile
########### LOGIC FOR RMAN REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set pagesize 200
set linesize 200
COL STATUS FORMAT a25
COL INPUT_BYTES_DISPLAY FORMAT a20
COL OUTPUT_BYTES_DISPLAY FORMAT a20
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES_DISPLAY,
OUTPUT_BYTES_DISPLAY
FROM V\$RMAN_BACKUP_JOB_DETAILS
where START_TIME between sysdate -7 and sysdate
ORDER BY SESSION_KEY;
set heading off
set feedback off
select '*** RMAN REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT END #################################
#
#
echo "############################# FLASHBACK USAGE REPORT ########################" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set lines 100
col name format a20
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
,round((floor(space_limit / 1024 / 1024) - ceil(space_used / 1024 / 1024) )/ floor(space_limit / 1024 / 1024)*100,2) as PCT_FREE
,round((ceil(space_used / 1024 / 1024) / floor(space_limit / 1024 / 1024))*100,2) as PCT_USED
from v\$recovery_file_dest
order by name;
set heading off
set feedback off
select '*** FLASHBACK USAGE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT END #################################
#
#
echo "############################# GET LOG SWITCH REPORT ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR SWITCH REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 500
set pagesize 500
spool $asmfile
set echo off
SET VERIFY OFF
--Define start date and end date. Enter 0 for today's date.
variable START_DATE number;
variable END_DATE number;
begin
select 30, 0 into :START_DATE, :END_DATE from dual;
end;
/
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
COLUMN DAY FORMAT a6
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v\$log_history a
WHERE
(TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
>=
TO_CHAR(sysdate-:START_DATE, 'DD-MON-YYYY')
)
AND
(TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
<=
TO_CHAR(sysdate-:END_DATE, 'DD-MON-YYYY')
)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
order by 1;
set heading off
set feedback off
select '*** LOG SWITCH REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR LOG SWITCH REPORT END #################################
#
#
rm -f $tbsfile
rm -f $asmfile
mailx -s "Disk Usage report of $currdb for `date +%m-%d-%y`" $email_list < $logfile
Friday, July 8, 2016
Thursday, June 30, 2016
Useful Commands for 12c Multitenant
############################################################################
### 12c How to plugin a non-CDB database to a Container Database (CDB) ###
############################################################################
$ sqlplus sys as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
SQL> alter database open read only;
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/u01/scripts/rac.xml');
END;
/
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/scripts/rac.xml',
pdb_name => 'RAC')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
CREATE PLUGGABLE DATABASE RAC using '/u01/scripts/rac.xml' nocopy tempfile reuse;
select CON_ID, NAME, OPEN_MODE from V$PDBS;
select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
ALTER SESSION SET CONTAINER =rac;
show con_name
alter session SET CONTAINER=CDB$ROOT;
show con_name
select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
@/u01/products/rdbms_12102/rdbms/admin/noncdb_to_pdb.sql
#####################################
### Add and Start New PDB service ###
#####################################
./srvctl add service -db cdb12c -service pdb_srv -preferred cdb12c1 -pdb RAC
./srvctl start service -db cdb12c -s pdb_srv
########################################################################################################################
### Save state of PDB. By defualt the PDB's starup in mounted state. Use the following cmd to persere the open state.###
########################################################################################################################
SQL> alter pluggable database UCPDB save state;
alter pluggable database JOHNPDB save state;
##############################
### Connect to PDB Service ###
##############################
sqlplus system/racattack@collabn1:1521/ucpdb
Schema/Password@HOST:Port/Service_Name
################################################
### Open all PDB's that are on mounted mode ###
################################################
SQL> alter pluggable database all open;
##################
### Switch CDB ###
##################
alter session set container=CD$ROOT;
###############################
### Display active services ###
###############################
select name, con_id from v$active_services order by 1;
NAME CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
cdb12c 1
cdb12cXDB 1
pdb_srv 3
rac 3
ucpdb 4
##########################
### Display Containers ###
##########################
SQL> select con_id, name, open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 RAC READ WRITE
4 UCPDB READ WRITE
###########################
### Display PDB Status ###
###########################
set linesize 500
column pdb_name format a50
select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
-------------------------------------------------- ---------
PDB$SEED NORMAL
RAC NORMAL
UCPDB NORMAL
###############################
### Display Open_Mode of PDB###
###############################
select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
RAC READ WRITE
UCPDB READ WRITE
#########################################################
### Display all datafile at the root container level ###
#########################################################
set linesize 500
column FILE_NAME format a100
select file_name,con_id from cdb_data_files order by con_id;
FILE_NAME CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDB12C/DATAFILE/system.295.908285565 1
+DATA/CDB12C/DATAFILE/users.303.908285931 1
+DATA/CDB12C/DATAFILE/sysaux.297.908285699 1
+DATA/CDB12C/DATAFILE/undotbs1.299.908285801 1
+DATA/CDB12C/DATAFILE/undotbs2.302.908285907 1
+DATA/RAC/DATAFILE/users.260.839965263 3
+DATA/RAC/DATAFILE/system.259.839965187 3
+DATA/RAC/DATAFILE/sysaux.258.839965083 3
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/system.308.908621995 4
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/users.311.908622247 4
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/sysaux.309.908621995 4
###########################################################
### Display all tablespaces at the root container level ###
###########################################################
set linesize 500
set pagesize 500
column tablespace_name format a50
select tablespace_name, con_id from cdb_tablespaces order by con_id;
TABLESPACE_NAME CON_ID
-------------------------------------------------- ----------
SYSTEM 1
USERS 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
UNDOTBS2 1
SYSTEM 3
SYSAUX 3
UNDOTBS2 3
TEMP 3
USERS 3
UNDOTBS1 3
SYSTEM 4
USERS 4
TEMP 4
SYSAUX 4
#########################################################
### Display all tempfiles at the root container level ###
#########################################################
set linesize 500
column FILE_NAME format a100
select file_name,con_id from cdb_temp_files order by con_id;
FILE_NAME CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDB12C/TEMPFILE/temp.300.908285829 1
+DATA/CDB12C/F2CCC568CEF64318E043334EA8C05910/TEMPFILE/temp.307.908366901 3
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/TEMPFILE/temp.310.908622149 4
##################################################
### Create common user while connected to root ###
##################################################
create user c##1 identified by racattack container=all;
set linesize 500
set pagesize 500
column username format a50
select username, common, con_id from cdb_users where username like 'C##%';
USERNAME COM CON_ID
-------------------------------------------------- --- ----------
C##1 YES 3
C##1 YES 1
C##1 YES 4
##################
### Drop PDB ###
##################
--Connect to container DB
$ export ORACLE_SID=cdb1
$ . oraenv
$ sqlplus / as sydba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
--Close all or just the PDB to drop
SQL> alter pluggable database all close immediate;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
JOHNPDB MOUNTED
TIGGER MOUNTED
--DROP PDB
SQL> drop pluggable database TIGGER including datafiles;
Pluggable database dropped.
--Verify PDB is gone.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
JOHNPDB MOUNTED
#############
Overview
#############
Purpose
This tutorial covers the steps for using Oracle Recovery Manager (Oracle RMAN) to perform a point-in-time recovery for a pluggable database (PDB).
Time to Complete
Approximately 30 minutes
Introduction
A database point-in-time recovery (DBPITR) is sometimes referred to as an incomplete recovery. This recovery capability is suitable for the following situations:
A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA erroneously deletes or updates the contents of one or more tables, drops database objects that are still needed during an update to an application, or runs a large batch update that fails midway.
A database upgrade fails or an upgrade script goes awry.
A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.
Here are the DBPITR requirements:
Your database must be running in ARCHIVELOG mode.
You must have backups of all data files before the target SCN for DBPITR.
You must have archived redo logs between the SCN of the backups and the target SCN.
Scenario
In this tutorial, you use Oracle RMAN to perform a point-in-time recovery on a PDB. You make some erroneous data updates and then recover the database to the state it was just before you performed the data updates.
Prerequisites
Before starting this tutorial, you should:
Ensure that you have enough disk space to hold a complete backup copy of the CDB, including all of the PDBs.
Install Oracle Database 12c.
Create one CDB with two PDBs in it.
The environment used in the development of this tutorial is as follows:
ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Container databases:
SID: cdb1
SID: cdb2
Pluggable databases (in cdb1):
pdb1
pdb2
##################################
Preparing to Back up the CDB
##################################
Before backing up the database, you must create the backup directory and then put the database in ARCHIVELOG mode.
Creating a Backup Directory
Navigate to /stage or wherever you can find enough available space to store the backup files.
cd /stage
Create a subdirectory called db_backup_files under /stage.
mkdir db_backup_files
Create a subdirectory called cdb1 under /stage/db_backup_files.
cd db_backup_files
mkdir cdb1
Placing the CDB in ARCHIVELOG Mode
In SQL*Plus, connect as sysdba to the cdb1 container database.
. oraenv
[enter cdb1 at the prompt]
sqlplus / as sysdba
Shut down the database.
shutdown immediate
Start the database in mount mode.
startup mount
Turn on database archiving.
alter database archivelog;
Open the CDB.
alter database open;
Open all of the PDBs.
alter pluggable database all open;
Set the Flash Recovery Area (FRA) size.
alter system set db_recovery_file_dest_size = 2G scope=both;
exit
############################
Backing Up the CDB
############################
Use Oracle RMAN to back up the database to the directory that was specified in the previous section.
Perform the following steps in a new terminal window:
Set the NLS_DATE_FORMAT environment variable so that the date and time values displayed by RMAN can be more easily read.
Make sure that ORACLE_HOME is set correctly.
Set ORACLE_SID to cdb1.
export NLS_DATE_FORMAT='DD-MM-YYYY HH:MI:SS'
Connect to Oracle RMAN.
rman target /
Set the Oracle RMAN backup device type and backup file location.
configure channel device type disk format '/stage/db_backup_files/cdb1/%U';
Turn on automatic backup of control files.
configure controlfile autobackup on;
Back up the database and archive logs.
backup database plus archivelog;
exit
###########################################
Performing Erroneous updates in the PDB
###########################################
In this section, you set up a tablespace, schema, and table in each PDBs. Next, you insert some records in the tables. One of the batch inserts will be done "in error."
Creating a Tablespace, Schema, and Table in PDB1
In SQL*Plus, connect to pdb1 as sysdba.
sqlplus sys/oracle@localhost:1521/pdb1 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace dj_pdb1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf' size 10m;
Create a schema that will own the table.
create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to dj;
Create the table in the schema.
create table dj.t1(c varchar2(100)) tablespace dj_pdb1;
###########################################
Performing Data Updates in PDB1
###########################################
Make a note of the current SCN.
select timestamp_to_scn(sysdate) from v$database;
Insert data in the table.
begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN and remain in SQL*Plus.
select timestamp_to_scn(sysdate) from v$database;
#################################################
Creating a Tablespace, Schema, and Table in PDB2
#################################################
In SQL*Plus, connect to pdb2 as sysdba.
connect sys/oracle@localhost:1521/pdb2 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace jfv_pdb2 datafile '+DATA' size 10m;
Create a schema that will own the table.
create user jfv identified by jfv temporary tablespace temp default tablespace jfv_pdb2;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to jfv;
Create the table in the schema.
create table jfv.t2(c varchar2(100)) tablespace jfv_pdb2;
#################################################
Performing Data Updates in PDB2
#################################################
Insert a row in the table and commit the transaction.
insert into jfv.t2 values ('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
commit;
Make a note of the current SCN, which is the point to which you will recover the database.
select timestamp_to_scn(sysdate) from v$database;
Insert 10,000 more rows into the table.
begin
for i in 1.. 10000 loop
insert into jfv.t2 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN.
select timestamp_to_scn(sysdate) from v$database;
################################################
Recovering the PDB to a Certain Point in Time
################################################
To perform point-in-time recovery, you must first close the PDB. Use Oracle RMAN to recover until the SCN before the erroneous data updates to the table in PDB2.
Close the pdb2 pluggable database and exit SQL*Plus.
alter pluggable database pdb2 close;
exit
Connect to Oracle RMAN.
rman target /
Perform point-in-time recovery in Oracle RMAN.
Make sure that you change the SET UNTIL SCN value to the SCN that you recorded in step #1 in the "Performing Data Updates in PDB2" section. Also, set the AUXILIARY DESTINATION value to a location with enough free space to hold a copy of the database files.
run {
set until SCN = 2263440 ;
restore pluggable database pdb2;
recover pluggable database pdb2 auxiliary destination='/stage/db_backup_files/cdb1';
alter pluggable database pdb2 open resetlogs;
}
Exit Oracle RMAN.
exit
################################################
Verifying Sucess of the Point-in-Time Recovery
################################################
If the point-in-time recovery was successful, you should see only one row in the jfv.t2 table.
In SQL*Plus, connect to PDB2.
sqlplus jfv/jfv@localhost:1521/pdb2
Check to see that the table contains only the one row inserted before the 10,000 record batch insert.
select * from t2;
exit
################################################
Resetting your environment
################################################
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
Remove the jfv user and jfv_pdb2 tablespace from pdb2 .
. oraenv
[enter cdb2 at the prompt]
sqlplus sys/oracle@localhost:1521/pdb2 as sysdba
drop user jfv cascade;
drop tablespace jfv_pdb2 including contents;
Remove the dj user and dj_pdb1 tablespace from pdb1.
connect sys/oracle@localhost:1521/pdb1 as sysdba
drop user dj cascade;
drop tablespace dj_pdb1 including contents;
Take the database out of ARCHIVELOG mode .
connect / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
alter pluggable database all open;
exit
Remove the backup files.
rm -rf /stage/db_backup_files
sqlplus system/racattack@dgprm:1521/TIGGER
###########################################
Performing Erroneous updates in the PDB
###########################################
In this section, you set up a tablespace, schema, and table in each PDBs. Next, you insert some records in the tables. One of the batch inserts will be done "in error."
Creating a Tablespace, Schema, and Table in PDB1
In SQL*Plus, connect to pdb1 as sysdba.
sqlplus sys/oracle@localhost:1521/pdb1 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace dj_pdb1 datafile '+DATA' size 10m;
Create a schema that will own the table.
create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to dj;
Create the table in the schema.
create table dj.t1(c varchar2(100)) tablespace dj_pdb1;
###########################################
Performing Data Updates in PDB1
###########################################
Make a note of the current SCN.
select timestamp_to_scn(sysdate) from v$database;
Insert data in the table.
begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN and remain in SQL*Plus.
select timestamp_to_scn(sysdate) from v$database;
### 12c How to plugin a non-CDB database to a Container Database (CDB) ###
############################################################################
$ sqlplus sys as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
SQL> alter database open read only;
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/u01/scripts/rac.xml');
END;
/
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/scripts/rac.xml',
pdb_name => 'RAC')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
CREATE PLUGGABLE DATABASE RAC using '/u01/scripts/rac.xml' nocopy tempfile reuse;
select CON_ID, NAME, OPEN_MODE from V$PDBS;
select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
ALTER SESSION SET CONTAINER =rac;
show con_name
alter session SET CONTAINER=CDB$ROOT;
show con_name
select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
@/u01/products/rdbms_12102/rdbms/admin/noncdb_to_pdb.sql
#####################################
### Add and Start New PDB service ###
#####################################
./srvctl add service -db cdb12c -service pdb_srv -preferred cdb12c1 -pdb RAC
./srvctl start service -db cdb12c -s pdb_srv
########################################################################################################################
### Save state of PDB. By defualt the PDB's starup in mounted state. Use the following cmd to persere the open state.###
########################################################################################################################
SQL> alter pluggable database UCPDB save state;
alter pluggable database JOHNPDB save state;
##############################
### Connect to PDB Service ###
##############################
sqlplus system/racattack@collabn1:1521/ucpdb
Schema/Password@HOST:Port/Service_Name
################################################
### Open all PDB's that are on mounted mode ###
################################################
SQL> alter pluggable database all open;
##################
### Switch CDB ###
##################
alter session set container=CD$ROOT;
###############################
### Display active services ###
###############################
select name, con_id from v$active_services order by 1;
NAME CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
cdb12c 1
cdb12cXDB 1
pdb_srv 3
rac 3
ucpdb 4
##########################
### Display Containers ###
##########################
SQL> select con_id, name, open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 RAC READ WRITE
4 UCPDB READ WRITE
###########################
### Display PDB Status ###
###########################
set linesize 500
column pdb_name format a50
select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
-------------------------------------------------- ---------
PDB$SEED NORMAL
RAC NORMAL
UCPDB NORMAL
###############################
### Display Open_Mode of PDB###
###############################
select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
RAC READ WRITE
UCPDB READ WRITE
#########################################################
### Display all datafile at the root container level ###
#########################################################
set linesize 500
column FILE_NAME format a100
select file_name,con_id from cdb_data_files order by con_id;
FILE_NAME CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDB12C/DATAFILE/system.295.908285565 1
+DATA/CDB12C/DATAFILE/users.303.908285931 1
+DATA/CDB12C/DATAFILE/sysaux.297.908285699 1
+DATA/CDB12C/DATAFILE/undotbs1.299.908285801 1
+DATA/CDB12C/DATAFILE/undotbs2.302.908285907 1
+DATA/RAC/DATAFILE/users.260.839965263 3
+DATA/RAC/DATAFILE/system.259.839965187 3
+DATA/RAC/DATAFILE/sysaux.258.839965083 3
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/system.308.908621995 4
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/users.311.908622247 4
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/sysaux.309.908621995 4
###########################################################
### Display all tablespaces at the root container level ###
###########################################################
set linesize 500
set pagesize 500
column tablespace_name format a50
select tablespace_name, con_id from cdb_tablespaces order by con_id;
TABLESPACE_NAME CON_ID
-------------------------------------------------- ----------
SYSTEM 1
USERS 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
UNDOTBS2 1
SYSTEM 3
SYSAUX 3
UNDOTBS2 3
TEMP 3
USERS 3
UNDOTBS1 3
SYSTEM 4
USERS 4
TEMP 4
SYSAUX 4
#########################################################
### Display all tempfiles at the root container level ###
#########################################################
set linesize 500
column FILE_NAME format a100
select file_name,con_id from cdb_temp_files order by con_id;
FILE_NAME CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDB12C/TEMPFILE/temp.300.908285829 1
+DATA/CDB12C/F2CCC568CEF64318E043334EA8C05910/TEMPFILE/temp.307.908366901 3
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/TEMPFILE/temp.310.908622149 4
##################################################
### Create common user while connected to root ###
##################################################
create user c##1 identified by racattack container=all;
set linesize 500
set pagesize 500
column username format a50
select username, common, con_id from cdb_users where username like 'C##%';
USERNAME COM CON_ID
-------------------------------------------------- --- ----------
C##1 YES 3
C##1 YES 1
C##1 YES 4
##################
### Drop PDB ###
##################
--Connect to container DB
$ export ORACLE_SID=cdb1
$ . oraenv
$ sqlplus / as sydba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
--Close all or just the PDB to drop
SQL> alter pluggable database all close immediate;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
JOHNPDB MOUNTED
TIGGER MOUNTED
--DROP PDB
SQL> drop pluggable database TIGGER including datafiles;
Pluggable database dropped.
--Verify PDB is gone.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
JOHNPDB MOUNTED
#############
Overview
#############
Purpose
This tutorial covers the steps for using Oracle Recovery Manager (Oracle RMAN) to perform a point-in-time recovery for a pluggable database (PDB).
Time to Complete
Approximately 30 minutes
Introduction
A database point-in-time recovery (DBPITR) is sometimes referred to as an incomplete recovery. This recovery capability is suitable for the following situations:
A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA erroneously deletes or updates the contents of one or more tables, drops database objects that are still needed during an update to an application, or runs a large batch update that fails midway.
A database upgrade fails or an upgrade script goes awry.
A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.
Here are the DBPITR requirements:
Your database must be running in ARCHIVELOG mode.
You must have backups of all data files before the target SCN for DBPITR.
You must have archived redo logs between the SCN of the backups and the target SCN.
Scenario
In this tutorial, you use Oracle RMAN to perform a point-in-time recovery on a PDB. You make some erroneous data updates and then recover the database to the state it was just before you performed the data updates.
Prerequisites
Before starting this tutorial, you should:
Ensure that you have enough disk space to hold a complete backup copy of the CDB, including all of the PDBs.
Install Oracle Database 12c.
Create one CDB with two PDBs in it.
The environment used in the development of this tutorial is as follows:
ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Container databases:
SID: cdb1
SID: cdb2
Pluggable databases (in cdb1):
pdb1
pdb2
##################################
Preparing to Back up the CDB
##################################
Before backing up the database, you must create the backup directory and then put the database in ARCHIVELOG mode.
Creating a Backup Directory
Navigate to /stage or wherever you can find enough available space to store the backup files.
cd /stage
Create a subdirectory called db_backup_files under /stage.
mkdir db_backup_files
Create a subdirectory called cdb1 under /stage/db_backup_files.
cd db_backup_files
mkdir cdb1
Placing the CDB in ARCHIVELOG Mode
In SQL*Plus, connect as sysdba to the cdb1 container database.
. oraenv
[enter cdb1 at the prompt]
sqlplus / as sysdba
Shut down the database.
shutdown immediate
Start the database in mount mode.
startup mount
Turn on database archiving.
alter database archivelog;
Open the CDB.
alter database open;
Open all of the PDBs.
alter pluggable database all open;
Set the Flash Recovery Area (FRA) size.
alter system set db_recovery_file_dest_size = 2G scope=both;
exit
############################
Backing Up the CDB
############################
Use Oracle RMAN to back up the database to the directory that was specified in the previous section.
Perform the following steps in a new terminal window:
Set the NLS_DATE_FORMAT environment variable so that the date and time values displayed by RMAN can be more easily read.
Make sure that ORACLE_HOME is set correctly.
Set ORACLE_SID to cdb1.
export NLS_DATE_FORMAT='DD-MM-YYYY HH:MI:SS'
Connect to Oracle RMAN.
rman target /
Set the Oracle RMAN backup device type and backup file location.
configure channel device type disk format '/stage/db_backup_files/cdb1/%U';
Turn on automatic backup of control files.
configure controlfile autobackup on;
Back up the database and archive logs.
backup database plus archivelog;
exit
###########################################
Performing Erroneous updates in the PDB
###########################################
In this section, you set up a tablespace, schema, and table in each PDBs. Next, you insert some records in the tables. One of the batch inserts will be done "in error."
Creating a Tablespace, Schema, and Table in PDB1
In SQL*Plus, connect to pdb1 as sysdba.
sqlplus sys/oracle@localhost:1521/pdb1 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace dj_pdb1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf' size 10m;
Create a schema that will own the table.
create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to dj;
Create the table in the schema.
create table dj.t1(c varchar2(100)) tablespace dj_pdb1;
###########################################
Performing Data Updates in PDB1
###########################################
Make a note of the current SCN.
select timestamp_to_scn(sysdate) from v$database;
Insert data in the table.
begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN and remain in SQL*Plus.
select timestamp_to_scn(sysdate) from v$database;
#################################################
Creating a Tablespace, Schema, and Table in PDB2
#################################################
In SQL*Plus, connect to pdb2 as sysdba.
connect sys/oracle@localhost:1521/pdb2 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace jfv_pdb2 datafile '+DATA' size 10m;
Create a schema that will own the table.
create user jfv identified by jfv temporary tablespace temp default tablespace jfv_pdb2;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to jfv;
Create the table in the schema.
create table jfv.t2(c varchar2(100)) tablespace jfv_pdb2;
#################################################
Performing Data Updates in PDB2
#################################################
Insert a row in the table and commit the transaction.
insert into jfv.t2 values ('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
commit;
Make a note of the current SCN, which is the point to which you will recover the database.
select timestamp_to_scn(sysdate) from v$database;
Insert 10,000 more rows into the table.
begin
for i in 1.. 10000 loop
insert into jfv.t2 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN.
select timestamp_to_scn(sysdate) from v$database;
################################################
Recovering the PDB to a Certain Point in Time
################################################
To perform point-in-time recovery, you must first close the PDB. Use Oracle RMAN to recover until the SCN before the erroneous data updates to the table in PDB2.
Close the pdb2 pluggable database and exit SQL*Plus.
alter pluggable database pdb2 close;
exit
Connect to Oracle RMAN.
rman target /
Perform point-in-time recovery in Oracle RMAN.
Make sure that you change the SET UNTIL SCN value to the SCN that you recorded in step #1 in the "Performing Data Updates in PDB2" section. Also, set the AUXILIARY DESTINATION value to a location with enough free space to hold a copy of the database files.
run {
set until SCN = 2263440 ;
restore pluggable database pdb2;
recover pluggable database pdb2 auxiliary destination='/stage/db_backup_files/cdb1';
alter pluggable database pdb2 open resetlogs;
}
Exit Oracle RMAN.
exit
################################################
Verifying Sucess of the Point-in-Time Recovery
################################################
If the point-in-time recovery was successful, you should see only one row in the jfv.t2 table.
In SQL*Plus, connect to PDB2.
sqlplus jfv/jfv@localhost:1521/pdb2
Check to see that the table contains only the one row inserted before the 10,000 record batch insert.
select * from t2;
exit
################################################
Resetting your environment
################################################
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
Remove the jfv user and jfv_pdb2 tablespace from pdb2 .
. oraenv
[enter cdb2 at the prompt]
sqlplus sys/oracle@localhost:1521/pdb2 as sysdba
drop user jfv cascade;
drop tablespace jfv_pdb2 including contents;
Remove the dj user and dj_pdb1 tablespace from pdb1.
connect sys/oracle@localhost:1521/pdb1 as sysdba
drop user dj cascade;
drop tablespace dj_pdb1 including contents;
Take the database out of ARCHIVELOG mode .
connect / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
alter pluggable database all open;
exit
Remove the backup files.
rm -rf /stage/db_backup_files
sqlplus system/racattack@dgprm:1521/TIGGER
###########################################
Performing Erroneous updates in the PDB
###########################################
In this section, you set up a tablespace, schema, and table in each PDBs. Next, you insert some records in the tables. One of the batch inserts will be done "in error."
Creating a Tablespace, Schema, and Table in PDB1
In SQL*Plus, connect to pdb1 as sysdba.
sqlplus sys/oracle@localhost:1521/pdb1 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace dj_pdb1 datafile '+DATA' size 10m;
Create a schema that will own the table.
create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to dj;
Create the table in the schema.
create table dj.t1(c varchar2(100)) tablespace dj_pdb1;
###########################################
Performing Data Updates in PDB1
###########################################
Make a note of the current SCN.
select timestamp_to_scn(sysdate) from v$database;
Insert data in the table.
begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN and remain in SQL*Plus.
select timestamp_to_scn(sysdate) from v$database;
Subscribe to:
Posts (Atom)