REM Script to verify the Oracle 7.3.4 database for 8.1.x upgrade
REM
REM SET ECHO ON
set feedback off
spool /tmp/verify73to81.lst
PROMPT Check PL/SQL is Installed - Install if NOT
PROMPT ==========================================
select * 
from v$version;
PROMPT ==============================================
PROMPT Check for user or role named MIGRATE and OUTLN
PROMPT Drop if any rows are returned
PROMPT ==============================================
select username 
from dba_users 
where username in ('MIGRATE', 'OUTLN');
select role 
from dba_roles
where role in ('MIGRATE', 'OUTLN');
PROMPT ======================================================
PROMPT Check for Tablespaces not online. Make them online or
PROMPT make sure they are offline normal.
PROMPT ======================================================
select tablespace_name, status
from   dba_tablespaces
where  status != 'ONLINE';
PROMPT ==============================================
PROMPT Check for datafiles not online and read write. 
PROMPT Make sure no datafile is in the RECOVER status
PROMPT ==============================================
col name format a40
select name, status, enabled
from   v$datafile
where  status != 'ONLINE'
or     enabled != 'READ WRITE';
select * 
from   v$recover_file;
PROMPT ===================================================
PROMPT Check SYSTEM rollback segment. OPTIMAL should not
PROMPT be set. MAXEXTENTS above 121 (249 or 505 preferred)
PROMPT NEXT extent is not too small (>= 128K)
PROMPT ===================================================
alter  rollback segment SYSTEM shrink;
select next_extent, max_extents, pct_increase, optsize
from   dba_rollback_segs, v$rollstat
where  segment_name = 'SYSTEM'
and    usn = segment_id;
PROMPT ============================================
PROMPT Make sure AUD$ table is in SYSTEM tablespace
PROMPT ============================================
select table_name, tablespace_name 
from   dba_tables
where  table_name = 'AUD$';
PROMPT ==============================================
PROMPT Make sure default tablespace for SYS is SYSTEM
PROMPT ==============================================
select default_tablespace, temporary_tablespace 
from   dba_users
where  username = 'SYS';
PROMPT ==================================================
PROMPT Make sure SYS owned dictionary objects are not 
PROMPT in other tablespaces. If they are try to 
PROMPT run catproc.sql and catalog.sql with SYS
PROMPT default tablespace as SYSTEM
PROMPT ==================================================
col segment_name format a30
col segmmet_type format a10
col tablespace_name format a30
select segment_name, segment_type, tablespace_name
from   dba_segments
where  owner = 'SYS'
and    tablespace_name != 'SYSTEM'
and    segment_type != 'ROLLBACK';
PROMPT ==================================================
PROMPT Make sure you have 100MB free or double the amount
PROMPT of space used in SYSTEM tablespace available
PROMPT ==================================================
COLUMN tsname        FORMAT a17
COLUMN bytes         FORMAT 999,999,999
COLUMN Tot_Size      FORMAT 9,999,999 HEADING "TOTAL (M)"
COLUMN Tot_Free      FORMAT 9,999,999 HEADING "FREE (M)"
COLUMN Pct_Free      FORMAT 999 HEADING "FREE %"
SELECT a.tablespace_name TSNAME, SUM(a.tots)/1048576 Tot_Size,
       SUM(a.sumb)/1048576 Tot_Free,
       SUM(a.sumb)*100/sum(a.tots) Pct_Free
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb
        FROM   dba_free_space
	WHERE    tablespace_name = 'SYSTEM'
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0
        FROM   dba_data_files
	WHERE    tablespace_name = 'SYSTEM'
        GROUP BY tablespace_name) a
WHERE    a.tablespace_name = 'SYSTEM'
GROUP BY a.tablespace_name;
PROMPT ============================================================
PROMPT It is better to make the default INITIAL and NEXT extents
PROMPT of SYSTEM tablespace to 128K or higher and maxextents 
PROMPT also high enough (249 or 505).
PROMPT ============================================================
select initial_extent, next_extent, max_extents, pct_increase
from   dba_tablespaces
where  tablespace_name = 'SYSTEM';
PROMPT ============================================================
PROMPT These are the controlfiles of this database, make sure
PROMPT you have enough free space available in the file system
PROMPT Oracle8 creates large control files
PROMPT ============================================================
select value 
from v$parameter 
where name = 'control_files';
PROMPT ================================================
PROMPT Make sure there are no uncommitted transactions
PROMPT and uncommitted distributed transactions
PROMPT You will not see any rows returned if no problem
PROMPT Connect as SYS to run this query
PROMPT ================================================
select kttvstnm "TABLESPACE_NAME" 
from   sys.x$kttvs 
where  kttvstnm IS NOT null;
select * from sys.pending_trans$;
select * from sys.pending_sessions$;
select * from sys.pending_sub_sessions$;
PROMPT ==============================================
PROMPT It is suggested to make all tablespaces except
PROMPT SYSTEM, TEMP, RBS to READ ONLY. 
PROMPT You may use the script /tmp/readonly.sql 
PROMPT ==============================================
spool off
set feedback off pages 0 lines 120 trims on echo off termout off
spool /tmp/readonly.sql
select 'ALTER TABLESPACE ' || tablespace_name || ' READ ONLY;'
from   dba_tablespaces
where  tablespace_name not in ('SYSTEM', 'TEMP', 'RBS')
and    tablespace_name not in (select distinct tablespace_name 
       from dba_segments 
       where owner = 'SYS' and tablespace_name != 'SYSTEM')
order  by tablespace_name;
spool off
set pages 99 termout on feedback on
REM 
REM End of Script - Biju 02/29/2000