GI & DB Pre-Upgrade Data Collection

As a DBA and/or Support Consultant, we frequently work with customers to upgrade their existing Oracle Databases.

Upgrading an Oracle Database is looked like very easy now a days. But the truth is, it's not that easy. Certainly the steps or activities are simple and straight forward. But after effects (Post Upgrade) of the upgrade sometimes ruin everything.

I always prefer to give effort and time for pre-upgrade activity rather during the upgrade or even post upgrade. With proper pre-upgrade analysis and correction can lead to successful & smooth upgrade of Oracle GI & Database.

For Database Upgrade activity, I always ask the customers to provide me the following information, mainly applicable for Exadata (RAC on Linux) -

• Latest exachk report (MOS # 1070954.1).
• Output from GI_HOME & DB_HOME: opatch lsinv from all servers
• sosreport (/usr/sbin/sosreport) from all the database servers.
• Output of the following SQL from one ASM instance

 SQL> set linesize 100  
 SQL> set pagesize 100  
 SQL> COL NAME FORMAT a30  
 SQL> COL VALUE FORMAT a20  
 SQL> select inst_id,name,value from gv$parameter where name in ('shared_pool_size','shared_pool_reserved_size','db_cache_size','large_pool_size','streams_pool_size','java_pool_size','sga_max_size','sga_target','memory_max_target','memory_target','pga_aggregate_target','use_large_pages','cluster_interconnects','asm_power_limit') order by name,inst_id;  
 SQL> select inst_id,name,value from gv$parameter where name like '%listener' order by name,inst_id;  
 SQL> select name, value from gv$parameter where name = 'spfile';  
 SQL> select name, value from gv$parameter where name = 'pfile';  

• Output of the following SQL from ALL databases

 SQL> set linesize 100  
 SQL> set pagesize 100  
 SQL> COL NAME FORMAT a30  
 SQL> COL VALUE FORMAT a20  
 SQL> select inst_id,name,value from gv$parameter where name in ('shared_pool_size','shared_pool_reserved_size','db_cache_size','large_pool_size','streams_pool_size','java_pool_size','sga_max_size','sga_target','memory_max_target','memory_target','pga_aggregate_target','log_buffer','use_large_pages','cluster_interconnects','listener_networks') order by name,inst_id;  
 SQL> select inst_id,name,value from gv$parameter where name like '%listener' order by name,inst_id;  
 SQL> select inst_id,name,value from gv$parameter where name like '%broker%' order by name,inst_id;  
 SQL> select name, value from gv$parameter where name = 'spfile';  
 SQL> select name, value from gv$parameter where name = 'pfile';  
 SQL> set linesize 100  
 SQL> set pagesize 100  
 SQL> COL COMPONENT FORMAT a25  
 SQL> SELECT COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,USER_SPECIFIED_SIZE,LAST_OPER_TYPE FROM V$MEMORY_DYNAMIC_COMPONENTS;  
 SQL> SELECT COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,USER_SPECIFIED_SIZE,LAST_OPER_TYPE FROM V$SGA_DYNAMIC_COMPONENTS;  
 SQL> set pagesize 50  
 SQL> set linesize 150  
 SQL> col COMPONENT form a25  
 SQL> col PARAMETER form a22  
 SQL> col OPER_MODE form a2  
 SQL> select COMPONENT,OPER_TYPE,PARAMETER,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS,START_TIME,END_TIME from v$sga_resize_ops;  

• Output of the Pre-Upgrade Information Tool (e.g. utlu112i_11204_009.sql) from MOS Note # 884522.1. From ALL databases (from any one node/instance).
• For all databases, from any one instance: SQL> create pfile=/<path>/<dbname>.ora from spfile;
• For ASM, from any one ASM instance: create pfile=/path/ASM.ora from spfile;
• Output of the following commands

 # srvctl config scan  
 # srvctl config scan_listener  
 # srvctl config nodeapps  
 # srvctl config service -d <db_unique_name> | grep -i preconnect | wc -l  

• File from both GI & DB HOME: listener.ora & sqlnet.ora
• Output of the scripts from ASM instance
• Script to Report the Percentage of Imbalance in all Mounted Diskgroups (Doc ID 367445.1)
• Understanding ASM Capacity and Reservation of Free Space in Exadata (Doc ID 1551288.1)
• Output from Standby Database: SQL> select dest_name from v$archive_dest_status where recovery_mode = 'MANAGED REAL TIME APPLY';
• Hugepages
• Use the script from MOS Note # Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
• Output of the script from all the database servers when all the databases/instances are up & running