Pages

Wednesday, April 11, 2012

Manual database upgrade to oracle 11g on windows

DBUA is what mostly used for for upgrade specially on windows. That is good and oracle recommended option. But for some reason I chose to do a manual upgrade on windows.

Starting with that I had database 10.2.0.3 running on windows server.
First of all I need to have all the details of current state of database. Below is what all I checked.

set ORACLE_SID=TJAIN

sqlplus ‘/ as sysdba’
SQL> show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      G:\ORACLE\PRODUCT\10.2.0\DB_1\
                                                 DATABASE\SPFILETJAIN.ORA

SQL> create pfile='G:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTJAIN.ORA' from spfile;
File created.

SQL> show parameter dump
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      G:\ORACLE\ADMIN\TJAIN\BDUMP
core_dump_dest                       string      G:\ORACLE\ADMIN\TJAIN\CDUMP
max_dump_file_size                   string      20480
shadow_core_dump                     string      partial
user_dump_dest                       string      G:\ORACLE\ADMIN\TJAIN\UDUMP

SQL>show parameter control
control_files                        string      H:\ORADATA\TJAIN\TJAIN_C
                                                 ONTROL01.DBF, H:\ORADATA\TELTO
                                                 BAW\TJAIN_CONTROL02.DBF, G:
                                                 \ORADATA\TJAIN\TJAIN_CON
                                                 TROL03.DBF


Now we need to install oracle 11g software on windows. Installation is GUI based and a simple step by step approach.

After installation of oracle home you will find that in your path variable the location "11gOracleHOME/bin" comes prior to 10g one, so all executable you will use will be picked from this location.
To use 10g related  binaries you may need to use the complete path of executable.

Pre Upgrade

Copy utlu112i.sql from 11g home to some location and run it in 10G home.

SQL> spool 'G:\upgrade_logs\utlu112i.log'
SQL> @utlu112i.sql

See the log and check the suggestions. In my case below were some messages
Database:                                                                                          
**********************************************************************                             
--> name:          TJAIN                                                                        
--> version:       10.2.0.3.0                                                                      
--> compatible:    10.2.0.3.0                                                                      
--> blocksize:     16384                                                                           
--> platform:                                                                                      
--> timezone file: V3                                                                              
.                                                                                                  
**********************************************************************                             
Tablespaces: [make adjustments in the current environment]                                         
**********************************************************************                             
--> SYSTEM tablespace is adequate for the upgrade.                                                 
.... minimum required size: 801 MB                                                                 
--> UNDOTBS1 tablespace is adequate for the upgrade.                                               
.... minimum required size: 400 MB                                                                 
--> SYSAUX tablespace is adequate for the upgrade.                                                 
.... minimum required size: 992 MB                                                                 
--> TEMP tablespace is adequate for the upgrade.                                                   
.... minimum required size: 60 MB                                                                  
.                                                                                                  
**********************************************************************                             
Flashback: ON                                                                                      
**********************************************************************                             
FlashbackInfo:                                                                                     
--> name:          H:\oracle\oradata\teldobaw                                                      
--> limit:         30720 MB                                                                        
--> used:          9667 MB                                                                         
--> size:          30720 MB                                                                        
--> reclaim:       876.072265625 MB                                                                
--> files:         341                                                                             
WARNING: --> Flashback Recovery Area Set.  Please ensure adequate disk space in recovery areas before performing an upgrade.                                                              
.                                                                                                  
**********************************************************************                             
.
.
.
.                            
.                                                                                                  
**********************************************************************                             
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]                   
**********************************************************************                             
--> log_archive_start            10.1       DEPRECATED                                             
--> cursor_space_for_time        11.1       DEPRECATED                                             
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"            
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"            
--> cursor_space_for_time        11.2       DEPRECATED                                             
--> plsql_native_library_dir     11.2       OBSOLETE                                               
--> plsql_native_library_subdir_ 11.2       OBSOLETE                                               
.                                                                                                  
                                                                                                   
**********************************************************************                             
Components: [The following database components will be upgraded or installed]                      
**********************************************************************                             
--> Oracle Catalog Views         [upgrade]  VALID                                                  
.
.
.                                               
.                                                                                                  
**********************************************************************                             
Miscellaneous Warnings                                                                             
**********************************************************************                             
WARNING: --> Database is using a timezone file older than version 14.                              
.... After the release migration, it is recommended that DBMS_DST package                          
.... be used to upgrade the 10.2.0.3.0 database timezone version                                   
.... to the latest version which comes with the new release.                                       
WARNING: --> Database contains INVALID objects prior to upgrade.                                   
.... The list of invalid SYS/SYSTEM objects was written to                                         
.... registry$sys_inv_objs.                                                                        
.... The list of non-SYS/SYSTEM objects was written to                                             
.... registry$nonsys_inv_objs.                                                                     
.... Use utluiobj.sql after the upgrade to identify any new invalid                                
.... objects due to the upgrade.                                                                   
.... USER SYSTEM has 2 INVALID objects.                                                            
WARNING: --> EM Database Control Repository exists in the database.                                
.... Direct downgrade of EM Database Control is not supported. Refer to the                        
.... Upgrade Guide for instructions to save the EM data prior to upgrade.                          
WARNING: --> Your recycle bin is turned on and currently contains no objects.                      
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading                       
.... and your recycle bin is turned on, you may need to execute the command:                       
        PURGE DBA_RECYCLEBIN                                                                       
.... prior to executing your upgrade to confirm the recycle bin is empty.                          
.                                                                                                  
**********************************************************************                             
Recommendations                                                                                    
**********************************************************************                             
.
.
.
.SQL> spool off;

After seeing this, I decided to perform below activities.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
no rows selected
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
no rows selected
SQL> select * from v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg.dat          3

SQL> select count(1) from dba_objects where status = 'INVALID';
  COUNT(1)
----------
         2   

SQL> select distinct status from dba_indexes;
VALID
N/A

Create New Service

Open services.msc, there we check the properties of the service.
Check properties of respective oracle service, and this will be show path of oracle binary in 10g oracle home. For new database we need to delete and create a new service with below command.

Stop the service containing ORACLE and <SID>.

oradim -DELETE -SID TJAIN
oradim -new -SID TJAIN -startmode manual -pfile G:\Oracle11g\product\11.2.0\dbhome_1\database\initTJAIN.ORA

Now if you will see the properties the path of oracle executable must be from 11g home. The status should be started for particular service "OracleServiceTJAIN".

Now create the new parameter file for 11g database from the pfile created earlier, changing the parameters as suggested earlier in preupgrade script.

Login using 11g oracle home and run below

If the flashback is ON.

SQL> Startup mount pfile=initTJAIN.ORA
SQL> alter database flashback off;
SQL> shutdown immediate;

Starting the upgrade


SQL> startup upgrade pfile=initTJAIN.ORA
SQL> spool 'G:\upgrade_logs\catupgrd.log'
SQL> @catupgrd.sql

The script will take a while and then shuts down the database after completion of the activity.

After completion of script start the database and check the status for registry;

SQL>startup
SQL>select comp_name, status from dba_registry;

All of them should be valid, or as it was earlier in 10g.

Run utlrp.sql or you may compile the objects your own way.

SQL> exec utl_recomp.recomp_parallel(4);

After completion of above check for invalids.

SQL> select count(1) from dba_objects where status = 'INVALID';
  COUNT(1)
----------
         2   
SQL> select distinct status from dba_indexes;
STATUS
--------
VALID
N/A

SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_3.dat              3

Upgrading the time zone 

It should be 14 for 11.2.0.3

SQL> shutdowm immediate;
SQL> startup upgrade
SQL> exec dbms_dst.begin_upgrade(new_version => 14);
SQL> shutdown immediate;

SQL> startup;
SQL> set serveroutput on;

SQL> declare
num_of_failures number;
begin
dbms_dst.upgrade_database(num_of_failures);
dbms_output.put_line(num_of_failures);
dbms_dst.end_upgrade(num_of_failures);
dbms_output.put_line(num_of_failures);
end;
/

SQL> select * from v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14


SQL> select count(1) from dba_objects where status = 'INVALID';
  COUNT(1)
----------
         2

The upgrade is completed. You can turn the flashback on now.

No comments:

Post a Comment

Post a Comment