Oracle DB migration - Information collection for Primary server

 OS level info:

Oracle_Home for grid :

Oracle_Home for RDBMS :

Oracle_base :

cat /etc/sysctl.conf |grep -v \# |sort

cat /etc/security/limits.conf  |grep oracle

uname -a

cat /etc/redhat-release

cat /etc/hosts

Files required for DB configuration :

Init.ora, TNSNAMES.ORA, Listener.ora , Sqlnet.ora from both grid and rdbms home.

Patch Details of Oracle grid   "opatch lspatches"

Patch details of Oracle RDBMS  "opatch lspatches"

Yesterday awr report of 10AM to 11AM AND 3pm TO 4pm in html format

init.ora, tnsnames.ora, listener.ora , sqlnet.ora from both grid and rdbms home.

output of "asmcmd lsdg"

output of os command "uname -mrs"

output of df-h of running database server

Database Info:

SET lines 170 NUMWIDTH 12 PAGES 10000 LONG 2000000000

COL version FORMAT a12

COL comp_id FORMAT a8

COL schema LIKE version

COL status FORMAT a12

COL comp_name FORMAT a35

COL value FORMAT a15

COL member FORMAT a100

COL file_name FORMAT a100

COL owner FORMAT a25

COL ddl FORMAT a100

col PLATFORM_NAME format a50;

define fileName=dba_snapshot

COLUMN spool_time NEW_VALUE _spool_time NOPRINT

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;

COLUMN dbname NEW_VALUE _dbname NOPRINT

SELECT name dbname FROM v$database;

spool &FileName._&_dbname._&_spool_time..txt

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

select instance_name,status from gv$instance;

SELECT * FROM v$version;

var OHM varchar2(100);

EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PRINT OHM

SELECT comp_id,schema,status,version,comp_name  FROM dba_registry ORDER BY 1;

SELECT owner, count(*) FROM dba_objects WHERE owner IN ('CTXSYS', 'OLAPSYS', 'MDSYS', 'DMSYS', 'WKSYS', 'LBACSYS','ORDSYS', 'XDB', 'EXFSYS', 'OWBSYS', 'WMSYS', 'SYSMAN')  OR owner LIKE 'APEX%' and status='INVALID' GROUP BY owner  ORDER BY 1;

SELECT owner, object_type, COUNT(*)  FROM dba_objects WHERE object_type LIKE 'JAVA%' GROUP BY owner, object_type ORDER BY 1,2;

SELECT owner, object_type,status, COUNT(*)  FROM dba_objects GROUP BY owner, object_type,status ORDER BY 1,2;

SELECT * FROM nls_database_parameters  WHERE  parameter LIKE '%SET'  ORDER  BY 1;

SELECT group#,bytes,blocksize,members,status FROM v$log ORDER BY 1;

SELECT group#,bytes/1024/1024,members,status FROM v$log ORDER BY 1;

SELECT * FROM v$logfile  ORDER BY 1,3;

SELECT * FROM v$pwfile_users;

select filename, status, bytes from v$block_change_tracking;

Patch details:

set pagesize 1000

set linesize 300

col comments format a50;

col ACTION_TIME format a60;

select ACTION_TIME,ACTION,VERSION,COMMENTS from registry$history;

select PATCH_ID,ACTION,ACTION_TIME,DESCRIPTION from DBA_REGISTRY_SQLPATCH;

select ENDIAN_FORMAT,PLATFORM_NAME from V$TRANSPORTABLE_PLATFORM where PLATFORM_NAME=(select PLATFORM_NAME from v$database);

select status,instance_name,database_role,open_mode,platform_name,platform_id from v$database,v$instance;

select sum(bytes)/1024/1024/1024 from dba_data_files;

select sum(bytes)/1024/1024/1024 from dba_temp_files;

col value format a100;

col name format a50;

select 'Alter system set '|| name || '=' || value || ' scope=spfile sid=*;'  from v$parameter where ISDEFAULT='FALSE';

select name, value from v$parameter where ISDEFAULT='FALSE';

Redo Logs if RAC:

select thread#, group#,MEMBERS, count(1) from gv$log group by thread#, group#,MEMBERS order by 1,2 ; 

select thread#, group#,MEMBERS, (bytes/1024/1024/1024)as "Size in GB"  from gv$log order by 1,2; 

select thread#,group#,(bytes/1024/1024/1024)"Size in GB" from gv$standby_log order by 1,2;

Redo if Single Instance:

col MEMBER format a50;

select member from v$logfile;

select thread#, group#,MEMBERS, (bytes/1024/1024/1024)"Size in GB" from v$log order by 1,2;

select thread#,group#,(bytes/1024/1024/1024)"Size in GB" from v$standby_log order by 1,2;

Archive location 

select * from v$recovery_area_usage where file_type='ARCHIVED LOG';

select round(sum(blocks*block_size)/1024/1024/1024) as "Archive_per_day_GB" from v$archived_log where first_time>sysdate-7 ;

select * from (select round(sum(blocks*block_size)/1024/1024/1024) "Archive_per_day_GB", to_char(first_time,'dd-mm-yyyy') "Date" from v$archived_log where first_time>sysdate-7 group by to_char(first_time,'dd-mm-yyyy')) order by 2 asc;

Users profile:

col profile format a20;

col RESOURCE_NAME format a40;

col LIMIT format a20;

select distinct profile,RESOURCE_NAME,LIMIT from dba_profiles where profile in (select profile from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM',

'XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES',

'DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA',

'OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS')) order by profile,RESOURCE_NAME;

Select 'alter PROFILE '||  profile || ' limit ' ||  RESOURCE_NAME  ||' '|| limit ||';'  from dba_profiles where profile in (select profile from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM',

'XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES',

'DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA',

'OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS')) order by profile,RESOURCE_NAME;

Tablespace Information:

set linesize 300

select TABLESPACE_NAME, round(sum(bytes)/1024/1024/1024) TBS_GB  from dba_data_files group by TABLESPACE_NAME order by 2;

select TABLESPACE_NAME, round(sum(bytes)/1024/1024) TBS_MB from dba_data_files group by TABLESPACE_NAME order by 2;

select TABLESPACE_NAME, round(sum(bytes)/1024/1024/1024)  from dba_temp_files group by TABLESPACE_NAME order by 2;

select 'CREATE TABLESPACE '||TABLESPACE_NAME||' DATAFILE ''/oradata01/oraclesid/'||lower(TABLESPACE_NAME)||'_01.dbf'''||' SIZE '||(round(sum(Bytes)/1024/1024/1024)+1) ||'G AUTOEXTEND off '|| 'EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M ONLINE PERMANENT;' from dba_data_files  where TABLESPACE_NAME not in('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS') and TABLESPACE_NAME not in (select TABLESPACE_NAME from dba_temp_files) group by tablespace_name order by TABLESPACE_NAME; 

select 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' tempFILE ''/oradata01/oraclesid/'||lower(TABLESPACE_NAME)||'_01.dbf'''||' SIZE '||(round(sum(Bytes)/1024/1024/1024)+1)||'G AUTOEXTEND off EXTENT MANAGEMENT'  || ' LOCAL UNIFORM SIZE 8M ONLINE PERMANENT;' from dba_temp_files  where TABLESPACE_NAME in (select TABLESPACE_NAME from dba_temp_files) group by tablespace_name order by TABLESPACE_NAME; 

select username,profile,account_status  from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM','XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES','DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA','OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS') order by 3;

col PATH format a60;

select name,path,  disk_number disk_#, mount_status,header_status, state, round(total_MB/1024), round(free_MB/1024) from v$asm_disk order by group_number;

select NAME, BLOCK_SIZE, round(TOTAL_MB/1024), round(FREE_MB/1024) ,state,type from  v$ASM_DISKGROUP;

var OHM varchar2(100);

EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PRINT OHM

> host df -h