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