Tuesday, November 23, 2010

Just upgrade Oracle Database 10gR2 to 11gR2 by command-line

Test to upgrade Oracle Database 10gR2 to 11gR2 by command-line. make sure my database >= 10.2.0.2
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
oradb

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
- Use Pre upgrade script to check (utlu112i.sql)
SQL> @utlu112i.sql
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.

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.

- 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 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2
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.

SQL> spool upgrade.log
SQL> set echo on
SQL> set termout on
SQL> @?/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> @?/rdbms/admin/catuppst.sql

-- Generate fixed object stats
SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

-- Recompile
SQL> @?/rdbms/admin/utlrp.sql

-- During recompilation: check number of invalid objects
SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

-- Post upgrade script
SQL> @?/rdbms/admin/utlu112s.sql
Oracle Database 11.2 Post-Upgrade Status Tool 11-23-2010 15:40:48
.
.
.
Total Upgrade Time: 04:00:52

-- Compare invalid objects scripts
SQL> @?/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)

6 comments:

Anonymous said...

Very nice. I used to do manual upgrade until 11.2.0.2. Time for me to change and used DBUA. Blogged about it here - http://bit.ly/gjtX80

Surachart said...

Great...
thank you for link.

nagaraju gunti said...

great post
thank you....

Anonymous said...

Hi,

I have found Loading status in utlu112i.sql output in 10.2.0.2 database

plan to upgrade database to 11.2.0.3

**********************************************************************
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
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] LOADING
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
**********************************************************************

and i found error after upgrade (utlu112s.sql), please assist me

. VALID 11.2.0.3.0 00:06:00
Oracle Workspace Manager
. ORA-00942: table or view does not exist
. ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1579
. ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1596
. ORA-06512: at line 1
. VALID 11.2.0.3.0 00:00:48
Oracle XDK
. VALID 11.2.0.3.0 00:01:22
Oracle Text
. VALID 11.2.0.3.0 00:01:21
Oracle XML Database
. VALID 11.2.0.3.0 00:03:53
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:11
Oracle Multimedia
. ORA-20000: Oracle ORDIM component in registry is status: LOADING. Oracle
ORDIM
. must be installed and valid prior to Oracle Multimedia upgrade, downgrade,
or
. patch.
. ORA-06512: at line 7
. VALID 11.2.0.3.0 00:02:56
Spatial

Surachart said...

Please check Oracle Support 1078222.1 and etc for more information about Oracle Multimedia/interMedia.
In this case, You should open SR with Oracle for checking something in your database environment.

Maybe it's bug.

Jose Marin said...

see ORA-00942 during database upgrade of Oracle Workspace Manager (Doc ID 1399508.1)