Patching OEM 13c Cloud Control - part 2

This is for the OEM October 2019 Patch Set Update.

You will need an Oracle Metalink Account and search for :

"Patch 30203475: EMBP Patch Set Update 13.3.0.0.191015"

See also :

https://www.durham.computer/2019/05/patching-oem-13c-cloud-control.html

Daily / Hourly archive logs

Daily / Hourly archive log generation scripts - very handy.

Daily Archive Log Generation :

select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Hourly Archive Logs :

set pages 1000

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives from v$archived_log

group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;

** If you are running this on a dataguard env you need to run with applied='NO' or you get the log generation for both the primary and secondary and figure won't match what the o/s reports.

select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives_Generated from v$archived_log where applied = 'NO' group by trunc(COMPLETION_TIME,'DD'),thread# order by 1

Find the amount of archiving you are using (run from the archive directory)

# cat arc_by_day.sh - run from the directory with archive - if this already split into dated directories just run a du -skh

#!/bin/bash

# check days

array=($(ls -la | grep rman_arc | awk '{print $6 $7}'| uniq))

for ((i=0; i<${#array[@]}; i++));

mth=`echo ${array[$i]} | cut -c1-3`

day=`echo ${array[$i]} | cut -c4-5`

chk="$mth $day"

echo -n "$chk "

# sum the files for the list of days

ls -la | grep "$chk" | grep rman_arc | awk '{sum+=$5}END{print sum/1024/1024/1024}'

done

# sum the total

echo -n "Total "

ls -la | grep rman_arc | awk '{sum+=$5}END{print sum/1024/1024/1024}'

[root@db03 arc]# ./arc_by_day.sh

Jun 14 34.2387
Jun 15 38.9001
Jun 16 109.296
Jun 17 49.5452
Jun 18 46.5221
Jun 19 32.9514
Jun 20 55.9935
Jun 21 13.1455

Total 380.592

[root@db03 arc]# pwd

/u02/backups/rman/db03/arc

Similar script to the ones earlier for ASM :

select substr(FIRST_TIME, 1,9), count(FIRST_TIME) from V$ARCHIVED_LOG group by substr(FIRST_TIME, 1,9) order by 1

Column formatting SQL Plus

Column output in SQL Plus

Adding for reference.

Link to http://www.adp-gmbh.ch/ora/sqlplus/column.html

column [SQL*Plus]

SQL> column colum_name alias alias_name
SQL> column colum_name clear

SQL> column colum_name entmap on
SQL> column colum_name entmap off

SQL> column colum_name fold_after
SQL> column colum_name fold_before
SQL> column colum_name format a25
SQL> column colum_name heading header_text

SQL> column colum_name justify left
SQL> column colum_name justify right
SQL> column colum_name justify center

SQL> column colum_name like expr|alias
SQL> column colum_name newline
SQL> column colum_name new_value variable
SQL> column colum_name print 
SQL> column colum_name noprint 
SQL> column colum_name old_value 

SQL> column colum_name on 
SQL> column colum_name off 

SQL> column colum_name wrapped 
SQL> column colum_name word_wrapped 
SQL> column colum_name truncated

The command can be used to make the output prettier

http://www.adp-gmbh.ch/ora/sqlplus/beautify.html

format

Specifies the format for a column (that is, how the data of the column is printed).

column column_name format a20
column column_name format a50 word_wrapped
column column_name format 999.999  -- Decimal sign
column column_name format 999,999  -- Seperate thousands
column column_name format $999     -- Include leading $ sign

noprint

A column that is defined noprint will, not so strangly, not be printed at all.