Page Comparison

Prompt Specify owner for the tables that you want to replicate
Prompt Specify table_name(s) for the tables that you want to replicate. % for all.
column owner format A45
set linesize 200
set pagesize 5000

spool unsupported_LOB.txt
select owner, table_name, column_name 
from dba_lobs 
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 encrypt not in ('NO','NONE')
or compression not in ('NO','NONE')
or deduplication not in ('NO','NONE')
order by owner);
spool off

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'),'0700',1,0)),'9999') "0700",
to_char(sum(decode(to_char(first_time,'HH24'),'0801',1,0)),'9999') "0801",
to_char(sum(decode(to_char(first_time,'HH24'),'0902',1,0)),'9999') "0902",
to_char(sum(decode(to_char(first_time,'HH24'),'1003',1,0)),'9999') "1003",
to_char(sum(decode(to_char(first_time,'HH24'),'1104',1,0)),'9999') "1104",
to_char(sum(decode(to_char(first_time,'HH24'),'1205',1,0)),'9999') "1205",
to_char(sum(decode(to_char(first_time,'HH24'),'1306',1,0)),'9999') "1306",
to_char(sum(decode(to_char(first_time,'HH24'),'1407',1,0)),'9999') "1407",
to_char(sum(decode(to_char(first_time,'HH24'),'1508',1,0)),'9999') "1508",
to_char(sum(decode(to_char(first_time,'HH24'),'1609',1,0)),'9999') "1609",
to_char(sum(decode(to_char(first_time,'HH24'),'1710',1,0)),'9999') "1710",
to_char(sum(decode(to_char(first_time,'HH24'),'1811',1,0)),'9999') "1811",
to_char(sum(decode(to_char(first_time,'HH24'),'1912',1,0)),'9999') "1912",
to_char(sum(decode(to_char(first_time,'HH24'),'2013',1,0)),'9999') "2013",
to_char(sum(decode(to_char(first_time,'HH24'),'2114',1,0)),'9999') "2114",
to_char(sum(decode(to_char(first_time,'HH24'),'2215',1,0)),'9999') "2215",
to_char(sum(decode(to_char(first_time,'HH24'),'2316',1,0)),'9999') "2316",
from
sys.v$log_history
where to_dateto_char(sum(decode(to_char(first_time) > sysdate -8
GROUP by
,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'YYYY-MON-DD'),'HH24'),'18',1,0)),'9999') "18",
to_datechar(sum(decode(to_char(first_time,'HH24'),'19',1,0)
order by to_date),'9999') "19",
to_char(sum(decode(to_char(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 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