-First check the instance name:
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
oradb
-and check banner also:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
-first check pre-upgradescript:
Get the latest version of utlu1nmi.sql
– Download it
– Note:884522.1
SQL> @utlu112i.sql // 10g
Oracle Database 11.2 Pre-Upgrade Information Tool 11-23-2010 10:03:59
Script Version: 11.2.0.2.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORADB
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 693 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 467 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 481 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [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.5.0 database timezone version
.... to the latest version which comes with the new release.
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
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
V$SYSTEM_PARAMETER2 displays information about the initialization parameters that are currently in effect for the instance,
with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
-Need speed for upgrading, so truncate AUD$ table and gather dictionary statistics
SQL> truncate table SYS.AUD$ drop storage;
Table truncated.
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
- Start to upgrade:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
take a cold bkup for fall back plan
-Copy spfile + orapw from Old home -> New home.
$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/*oradb* /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
-Use New home to startup upgrade
$export sid of 10g
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2
$export path
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 676433920 bytes
Fixed Size 2229560 bytes
Variable Size 192940744 bytes
Database Buffers 473956352 bytes
Redo Buffers 7307264 bytes
Database mounted.
Database opened.
------------- donot execute this this will give an error, try this after catupgr.sql script --------------------------------------------------------------------
-Run "catrelod.sql" script reloads the appropriate version of all of the database components in the downgraded database
SQL> SPOOL reload.log
SQL> @?/rdbms/admin/catrelod.sql ///do not execute
.
.
.
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types VALID 10.2.0.5.0
Oracle Database Catalog Views VALID 10.2.0.5.0
JServer JAVA Virtual Machine VALID 10.2.0.5.0
Oracle XDK VALID 10.2.0.5.0
Oracle Database Java Packages VALID 10.2.0.5.0
Oracle Text VALID 10.2.0.5.0
Oracle XML Database VALID 10.2.0.5.0
Oracle Workspace Manager VALID 10.2.0.5.0
Oracle Data Mining VALID 10.2.0.5.0
OLAP Analytic Workspace VALID 10.2.0.5.0
OLAP Catalog VALID 10.2.0.5.0
Oracle OLAP API VALID 10.2.0.5.0
Oracle interMedia VALID 10.2.0.5.0
.
.
.
SQL> spool upgrade.log
SQL> set echo on
SQL> set termout on
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> spool off
-Check error in spool file and startup again
SQL> startup
ORACLE instance started.
Total System Global Area 676433920 bytes
Fixed Size 2229560 bytes
Variable Size 331352776 bytes
Database Buffers 335544320 bytes
Redo Buffers 7307264 bytes
Database mounted.
Database opened.
--This is post upgrade script: only necessary when upgrading from = 10.1
SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql
--Generate fixed object stats
SQL> exec dbms_stats.gather_fixed_objects_stats;
PL/SQL procedure successfully completed.
--Recompile
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
--During recompilation: check number of invalid objects
SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
--During recompilation: check number of invalid objects
SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
--Compare invalid objects scripts
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
--Create pfile from spfile and modify some parameters
SQL> create pfile='/tmp/pfile' from spfile;
File created.
--Adjust time zone data
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 676433920 bytes
Fixed Size 2229560 bytes
Variable Size 192940744 bytes
Database Buffers 473956352 bytes
Redo Buffers 7307264 bytes
Database mounted.
Database opened.
SQL> exec dbms_dst.begin_upgrade(new_version => 11);
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 676433920 bytes
Fixed Size 2229560 bytes
Variable Size 192940744 bytes
Database Buffers 473956352 bytes
Redo Buffers 7307264 bytes
Database mounted.
Database opened.
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;
/
- Check
-- Check Oracle Version
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
oradb
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
then copy/check listener and etc (from Old home to New home)
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0
Regards,
Trinadh
No comments:
Post a Comment