Page Comparison

set linesize 200
set echo off

spool redo_estimate.txt

select name,dbid, database_role, log_Mode from v$database;
select instance_name from v$instance;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';

ttitle left '***** Number of redo files for previous 7 days by hour *****' skip 2

btitle off


SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'0800',1,0)),'9999') "0800",
to_char(sum(decode(to_char(first_time,'HH24'),'0901',1,0)),'9999') "0901",
to_char(sum(decode(to_char(first_time,'HH24'),'1002',1,0)),'9999') "1002",
to_char(sum(decode(to_char(first_time,'HH24'),'1103',1,0)),'9999') "1103",
to_char(sum(decode(to_char(first_time,'HH24'),'1204',1,0)),'9999') "1204",
to_char(sum(decode(to_char(first_time,'HH24'),'1305',1,0)),'9999') "1305",
to_char(sum(decode(to_char(first_time,'HH24'),'1406',1,0)),'9999') "1406",
to_char(sum(decode(to_char(first_time,'HH24'),'1507',1,0)),'9999') "1507",
to_char(sum(decode(to_char(first_time,'HH24'),'1608',1,0)),'9999') "1608",
to_char(sum(decode(to_char(first_time,'HH24'),'1709',1,0)),'9999') "1709",
to_char(sum(decode(to_char(first_time,'HH24'),'1810',1,0)),'9999') "1810",
to_char(sum(decode(to_char(first_time,'HH24'),'1911',1,0)),'9999') "1911",
to_char(sum(decode(to_char(first_time,'HH24'),'2012',1,0)),'9999') "2012",
to_char(sum(decode(to_char(first_time,'HH24'),'2113',1,0)),'9999') "2113",
to_char(sum(decode(to_char(first_time,'HH24'),'2214',1,0)),'9999') "2214",
to_char(sum(decode(to_char(first_time,'HH24'),'2315',1,0)),'9999') "2315",
from
sys.v$log_history
where to_dateto_char(sum(decode(to_char(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

rem Size of logs to help do the math in bytes.
ttitle left '***** Size of redo logs. Multiple number of logs per hour by Size of Redo to get volume in Meg/Hr *****' skip 2
btitle off

select group#,members,archived, status, trunc((bytes)/1000000) Size_of_Redo_Megbytes
from sys.v$log
/

spool off 
exit

set linesize 200
set pagesize 20000
column owner format A40
column table_name format A60

set echo off

ttitle left '*****  Tables without PK or UK output to nokeys.lst  *****' skip 2
btitle off

spool nokeys.txt

select name,dbid, database_role, log_Mode from v$database;
select instance_name from v$instance;

select owner, table_name
from dba_tables
where owner not in (
'XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS')
and table_name not like 'BIN%'
and (owner, table_name) not in (
select owner, table_name
    from dba_constraints
    where constraint_type in ( 'P', 'U' )
    and owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS')
and table_name not like 'BIN%')
order by owner, table_name
/

spool off

-- Run on Source & post imported target db
spool delete_cascade_constraints.txt

column owner format A35
set pagesize 4000
set linesize 132

select owner, constraint_name, delete_rule,status 
FROM dba_constraints
where delete_rule = 'CASCADE'
and owner not in
(
'XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS')
order by 1,2;

spool off

-- Run on Source as a pre-check
REM ****************************
REM Compressed table list
REM ****************************
set linesize 200
spool list_compressed.txt
 
ttitle left '*****  Table Compression Assessment  *****' skip 2
btitle off

SELECT owner, table_name, compression
FROM dba_tables 
where owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',
'APEX_040200','LBACSYS')
and compression = 'ENABLED'
order by 1,2;

ttitle left '*****  Partitioned Table Compression Assessment  *****' skip 2
btitle off
SELECT table_owner,table_name, partition_name, compression
from dba_tab_partitions
where table_owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',
'APEX_040200','LBACSYS')
and compression = 'ENABLED'
order by 1,2,3;
 
spool off

-- Run on Source as a pre-check
REM ****************************
REM *** Encryption
REM ****************************
spool list_encrypt.txt

ttitle left '*****  Encryption Assessment  *****' skip 2
btitle off
select * from dict where lower(table_name) like '%encrypted%';

select owner, table_name, COLUMN_NAME, ENCRYPTION_ALG
from DBA_ENCRYPTED_COLUMNS
where owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',
'APEX_040200','LBACSYS')
order by 1,2,3;
spool off

-- Run on Source as a pre-check
REM ************************************
REM *** Check TDE if parameter found to be TRUE in init/pfile
REM ************************************

column timendate new_value spooltime noprint
set echo off
set linesize 132
set pagesize 50000
btitle off
ttitle off

prompt ***********************************************************************
prompt ** Check if TDE Feature is enabled, OK if query returns not found errors

select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'chk_tde'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual;
spool &spooltime-output.txt

set echo on
SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;

SELECT STATUS FROM V$ENCRYPTION_WALLET;

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

SELECT * FROM DBA_ENCRYPTED_COLUMNS
order by owner, table_name, column_name;

set echo off
spool off