Oracle DBA: Upgrade oracle database 10gR2 to 11gR2

Upgrade oracle database 10gR2 to 11gR2

This is just basic steps for oracle database upgradation. In real environment some other pre-requisite checks and changes will be there.

The oracle 10g database details

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

manudb

SQL> select banner from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod

PL/SQL Release 10.2.0.2.0 - Production

CORE 10.2.0.2.0 Production

TNS for Linux: Version 10.2.0.2.0 - Production

NLSRTL Version 10.2.0.2.0 - Production

SQL>

Install the oracle 11g software

Install the oracle 11gR2 software in an oracle_home seperate from 10gR2.

Here the 10g oracle_home is /u01/app/oracle/product/10.2.0/db_1 and the new installed 11g oracle_home is /u01/app/oracle/product/11.2.0/db_1.

Run the pre-upgrade scripts

copy the utlu112i.sql script from oracle 11g $ORACLE_HOME/rdbms/admin/ to /tmp directory and

run the script.

SQL> @/tmp/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 05-18-2013 22:04:27

**********************************************************************

Database:

**********************************************************************

--> name: MANUDB

--> version: 10.2.0.2.0

--> compatible: 10.2.0.1.0

--> blocksize: 8192

--> platform: Linux IA (32-bit)

--> timezone file: V2

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 734 MB

.... AUTOEXTEND additional space required: 254 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 513 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 445 MB

.... AUTOEXTEND additional space required: 215 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

.... AUTOEXTEND additional space required: 35 MB

**********************************************************************

Flashback: OFF

**********************************************************************

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

WARNING: --> "java_pool_size" needs to be increased to at least 64 MB

**********************************************************************

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"

--> core_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 11.

.... After the release migration, it is recommended that DBMS_DST package

.... be used to upgrade the 10.2.0.2.0 database timezone version

.... to the latest version which comes with the new release.

WARNING: --> Database contains schemas with stale optimizer statistics.

.... Refer to the Upgrade Guide for instructions to update

.... schema statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

.... SYSMAN

WARNING: --> Database contains schemas with objects dependent on network packages.

.... Refer to the Upgrade Guide for instructions to configure Network ACLs.

.... USER SYSMAN has dependent 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:--> recycle bin in use.

.... Your recycle bin is turned on and it contains

.... 20 object(s). It is REQUIRED

.... that the recycle bin is empty prior to upgrading

.... your database.

.... The command: PURGE DBA_RECYCLEBIN

.... must be executed immediately prior to executing your upgrade.

PL/SQL procedure successfully completed.

Check the recommendations and warnings and do the necessary actions.

SQL> exec dbms_stats.gather_schema_stats('sys');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('sysman')

PL/SQL procedure successfully completed.

SQL> PURGE DBA_RECYCLEBIN

2 ;

DBA Recyclebin purged.

SQL>

Copy the spfile and password file.

Copy the spfilemanudb.ora and orapwmanudb file from oracle 10g $ORACLE_HOME/dbs to 11g $ORACLE_HOME/dbs.

Restart the oracle database manudb from oracle 11g environment.

shutdown the oracle database manudb and start it from the oracle 11g environment.

[oracle@test1 bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@test1 bin]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 19 01:06:25 2013

Enter user-name: /as sysdba

Connected to an idle instance.

SQL>

upgrade the database

startup the database in upgrade mode and run the catupgrd.sql script.

SQL> startup

ORACLE instance started.

Total System Global Area 435224576 bytes

Fixed Size 1337044 bytes

Variable Size 222300460 bytes

Database Buffers 205520896 bytes

Redo Buffers 6066176 bytes

Database mounted.

Database opened.

SQL>

SQL>@?/rdbms/admin/catupgrd.sql

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

31 /

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> /*****************************************************************************/

SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!

SQL> */

SQL> /*****************************************************************************/

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC> The above sql script is the final step of the upgrade. Please

DOC> review any errors in the spool log file. If there are any errors in

DOC> the spool file, consult the Oracle Database Upgrade Guide for

DOC> troubleshooting recommendations.

DOC>

DOC> Next restart for normal operation, and then run utlrp.sql to

DOC> recompile any invalid application objects.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> Rem *********************************************************************

SQL> Rem END catupgrd.sql

SQL> Rem *********************************************************************

SQL>

SQL>

The database upgradation is completed.

Startup the database

SQL> startup

ORACLE instance started.

Total System Global Area 435224576 bytes

Fixed Size 1337044 bytes

Variable Size 222300460 bytes

Database Buffers 205520896 bytes

Redo Buffers 6066176 bytes

Database mounted.

Database opened.

SQL>

Run the post upgrade scripts

SQL> @?/rdbms/admin/catuppst.sql

SQL> exec dbms_stats.gather_fixed_objects_stats; -- generate fixed object statistics

PL/SQL procedure successfully completed.

SQL>

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

TIMESTAMP

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2013-05-19 13:29:32

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

COMP_TIMESTAMP UTLRP_END 2013-05-19 13:33:27

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

-------------------

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

---------------------------

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/utlu112s.sql

Oracle Database 11.2 Post-Upgrade Status Tool 05-19-2013 13:36:07

Component Status Version HH:MM:SS

Oracle Server

. VALID 11.2.0.1.0 00:25:04

JServer JAVA Virtual Machine

. VALID 11.2.0.1.0 00:08:17

Oracle Workspace Manager

. VALID 11.2.0.1.0 00:01:12

OLAP Analytic Workspace

. VALID 11.2.0.1.0 00:00:50

OLAP Catalog

. VALID 11.2.0.1.0 00:01:18

Oracle OLAP API

. VALID 11.2.0.1.0 00:01:36

Oracle Enterprise Manager

. VALID 11.2.0.1.0 00:12:51

Oracle XDK

. VALID 11.2.0.1.0 00:02:24

Oracle Text

. VALID 11.2.0.1.0 00:01:37

Oracle XML Database

. VALID 11.2.0.1.0 00:06:00

Oracle Database Java Packages

. VALID 11.2.0.1.0 00:01:06

Oracle Multimedia

. VALID 11.2.0.1.0 00:08:02

Spatial

. VALID 11.2.0.1.0 00:08:11

Oracle Expression Filter

. VALID 11.2.0.1.0 00:00:32

Oracle Rule Manager

. VALID 11.2.0.1.0 00:00:17

Gathering Statistics

. 00:09:24

Total Upgrade Time: 01:28:55

PL/SQL procedure successfully completed.

SQL>

SQL> @?/rdbms/admin/utluiobj.sql

Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 05-19-2013 13:40:14

This tool lists post-upgrade invalid objects that were not invalid

prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

Owner Object Name Object Type

PL/SQL procedure successfully completed.

SQL>

Restart again the database in upgrade mode and execute the below procedure

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup upgradeORACLE instance started.Total System Global Area 435224576 bytesFixed Size 1337044 bytesVariable Size 222300460 bytesDatabase Buffers 205520896 bytesRedo Buffers 6066176 bytesDatabase mounted.Database opened.

SQL> exec dbms_dst.begin_upgrade(new_version => 11);PL/SQL procedure successfully completed.

Startup the database in normal mode

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 435224576 bytes

Fixed Size 1337044 bytes

Variable Size 222300460 bytes

Database Buffers 205520896 bytes

Redo Buffers 6066176 bytes

Database mounted.

Database opened.

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;

/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

Now the database is completely upgraded to oracle 11gR2 and all the post upgradation scripts has been successfully executed.

Upgraded database status

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

manudb

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> select VERSION from dba_registry;

VERSION

------------------------------

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

17 rows selected.

SQL>

Oracle DBA

Monday, 20 May 2013

Upgrade oracle database 10gR2 to 11gR2

Upgrade oracle database 10gR2 to 11gR2

The oracle 10g database details

Install the oracle 11g software

Run the pre-upgrade scripts

Copy the spfile and password file.

Restart the oracle database manudb from oracle 11g environment.

upgrade the database

Startup the database

Run the post upgrade scripts

Restart again the database in upgrade mode and execute the below procedure

Startup the database in normal mode

Upgraded database status

2 comments: