Solaris How Tos

12. Shutdown Oracle 10g.
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jan 10 16:34:41 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

13. Change ORACLE_HOME and ORACLE_SID in .profile file and re-login for changes take effect.

Now, ORACLE_HOME points Oracle 11g home directory and ORACLE_SID is set for Oracle 11g SID.

14. Now, lets startup database with upgrade option
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 10 16:38:50 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 5062303744 bytes
Fixed Size 2156816 bytes
Variable Size 922752752 bytes
Database Buffers 4127195136 bytes
Redo Buffers 10199040 bytes
Database mounted.
Database opened.
SQL>

15.Find following line in catupstr.sql under /u01/app/oracle/product/11.2.0/db_1/rdbms/admin and change it from 10.2.0.1 to 10.2.0.2

BEGIN

– For 11.2, direct uppgrades are supported from 9.2.0.8, 10.1.0.5,
– 10.2.0.2 and above, and 11.1.0.6 and above
–
SELECT version INTO p_version FROM registry$ where cid=’CATPROC’;
IF substr(p_version,1,7) = ’9.2.0.8′ THEN
RETURN ’0902000′;
ELSIF substr(p_version,1,8) = ’10.1.0.5′ THEN
RETURN ’1001000′;
ELSIF substr(p_version,1,6) = ’10.2.0′ AND
substr(p_version,1,8) != ’10.2.0.1′ THEN
RETURN ’1002000′;
ELSIF substr(p_version,1,6) = ’11.1.0′ THEN
RETURN ’1101000′;
ELSIF substr(p_version,1,6) = ’11.2.0′ THEN — current version
SELECT version INTO server_version FROM v$instance;
IF substr(p_version,1,8) != substr(server_version,1,8) THEN — run c1101000
RETURN ’1101000′;
ELSE — version is the same as instance, so rerun the previous upgrade
– rerun upgrade of previous release
EXECUTE IMMEDIATE

16. Upgrade all tables, schemas etc… It will take hours to upgrade depending on HW and number of invalid object in database, not size of database.
SQL>@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catupgrd.sql

17. After all, database automatically will be shutdown.
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 *********************************************************************

18. Check alert logs under “/u01/app/oracle/diag/rdbms/SID/SID/trace/alert_[ORACLE_SID].log”.

19. Startup database
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 11 07:57:44 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 5062303744 bytes
Fixed Size 2156816 bytes
Variable Size 922752752 bytes
Database Buffers 4127195136 bytes
Redo Buffers 10199040 bytes
Database mounted.
Database opened.
SQL>

20. Check all database components are VALID.
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 11 08:06:39 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning and OLAP options

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 01-11-2012 08:07:19
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.2.0.1.0 00:59:09
JServer JAVA Virtual Machine
. VALID 11.2.0.1.0 00:28:16
Oracle Workspace Manager
. VALID 11.2.0.1.0 00:02:42
OLAP Analytic Workspace
. VALID 11.2.0.1.0 00:02:00
OLAP Catalog
. VALID 11.2.0.1.0 00:04:29
Oracle OLAP API
. VALID 11.2.0.1.0 00:02:07
Oracle Enterprise Manager
. VALID 11.2.0.1.0 00:38:44
Oracle XDK
. VALID 11.2.0.1.0 00:09:19
Oracle XML Database
. ORA-04043: object XDB_DATASTORE_PROC does not exist
. ORA-04043: object XDB_DATASTORE_PROC does not exist
. VALID 11.2.0.1.0 00:15:41
Oracle Database Java Packages
. VALID 11.2.0.1.0 00:01:09
Oracle Expression Filter
. VALID 11.2.0.1.0 00:00:49
Oracle Rules Manager
. VALID 11.2.0.1.0 00:00:43
Gathering Statistics
. 00:19:48
Total Upgrade Time: 03:05:05

PL/SQL procedure successfully completed.

21. Migrate the Baseline data on a pre-11g database to the 11g database.
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catuppst.sql

22. Compile invalid objects.
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql
….
COMP_TIMESTAMP UTLRP_END 2012-01-11 08:19:11

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
——————-
0

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
—————————
0
23. Check INVALID objects
SQL> select count(*) from dba_objects where status like ‘INVALID’;

COUNT(*)
———-
0

24. Check Database components, versions and status.
SQL> select comp_name,version, status from dba_registry;

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle Enterprise Manager
11.2.0.1.0 VALID

OLAP Catalog
11.2.0.1.0 VALID

Oracle XML Database
11.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle Expression Filter
11.2.0.1.0 VALID

Oracle Rules Manager
11.2.0.1.0 VALID

Oracle Workspace Manager
11.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle Database Catalog Views
11.2.0.1.0 VALID

Oracle Database Packages and Types
11.2.0.1.0 VALID

JServer JAVA Virtual Machine
11.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle XDK
11.2.0.1.0 VALID

Oracle Database Java Packages
11.2.0.1.0 VALID

OLAP Analytic Workspace
11.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle OLAP API
11.2.0.1.0 VALID

13 rows selected.

5. /u01/app/oracle/product/11.2.0/db_1/root.sh
bash-3.00# /u01/app/oracle/product/11.2.0/db_1/root.sh
Running Oracle 11g root.sh script…

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin …
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin …

Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

6. Pre-check on Oracle 10g before upgrade.
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jan 10 16:06:13 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> spool upgrade10gto11g.log
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 01-10-2012 16:08:10
.
**********************************************************************
Database:
**********************************************************************
–> name: DB_NAME
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Solaris[tm] OE (64-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 645 MB
…. AUTOEXTEND additional space required: 205 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 335 MB
…. AUTOEXTEND additional space required: 135 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 378 MB
…. AUTOEXTEND additional space required: 168 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
…. AUTOEXTEND additional space required: 34 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
– 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”
–> 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 XML Database [upgrade] VALID
–> Oracle Java Packages [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.1.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
…. OLAPSYS
…. SYSMAN
…. XDB
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.
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
…. 53 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.

7. According to upgrade documents, you should install patchset to have version 4 but it is OK to continue.
If you need version 4, then install required patch set(time zone patch 5632264).
SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 2

8. Check INVALID object in database.
SQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’;

9. Check database components and their versions.
SQL> select comp_name,version, status from dba_registry;

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle Database Catalog Views
10.2.0.1.0 VALID

Oracle Database Packages and Types
10.2.0.1.0 VALID

Oracle Workspace Manager
10.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
JServer JAVA Virtual Machine
10.2.0.1.0 VALID

Oracle XDK
10.2.0.1.0 VALID

Oracle Database Java Packages
10.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle Expression Filter
10.2.0.1.0 VALID

Oracle XML Database
10.2.0.1.0 VALID

Oracle Rules Manager
10.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
OLAP Analytic Workspace
10.2.0.1.0 VALID

Oracle OLAP API
10.2.0.1.0 VALID

OLAP Catalog
10.2.0.1.0 VALID

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ——————————————–
Oracle Enterprise Manager
10.2.0.1.0 VALID

13 rows selected.

10. Create pfile from spfile of Oracle 10g.
SQL> create pfile from spfile ;
File created.

Now you have a new pfile under(oracle 10g) /u01/app/oracle/product/10.2.0/db_1/dbs/init[ORACLE_SID].ora
Copy this file under oracle 11g folder.
/u01/app/oracle/product/11.2.0/db_1/dbs/init[ORACLE_SID].ora

11. in pre-check log file, we have following warnings.
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”

So, we have to edit spfile for Oracle 11g.

Remove following lines in spfile of Oracle 11g:
*.user_dump_dest=’/u01/app/oracle/admin/ORACLE_SID/udump’
*.background_dump_dest=’/u01/app/oracle/admin/ORACLE_SID/bdump’
*.core_dump_dest=’/u01/app/oracle/admin/ORACLE_SID/cdump’

Add following line in spfile of Oracle 11g;
*.diagnostic_dest=’/u01/app/oracle’

And change following line;
*.compatible=’10.2.0.1.0′ to *.compatible=’11.2.0.1.0′

now save the file.

Pre-requirements:
1. Oracle 10g(10.2.0.1.0) is running
2. Listener(10.2.0.1.0) are running.
3. Have following lines in .profile of oracle user and executed.(check with “env” command)
ORACLE_TERM=dtterm
export ORACLE_TERM
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORACLE_HOME
PATH=$PATH:/usr/bin:/usr/ucb:/etc:$ORACLE_HOME/bin:/bin:/usr/sbin:/usr/local/bin:/usr/openwin/bin:/opt/bin:/usr/ccs/bin:/usr/dt/bin:
/usr/ucb/bin:/usr/java/bin:.
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/openwin/lib:/usr/dt/lib:/usr/ucb/lib:/usr/local/lib
export LD_LIBRARY_LIB
ORACLE_SID=ORACLE10GSID
export ORACLE_SID
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export ORA_NLS33
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/product/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib:$ORACLE_HOM
E/jre
export CLASSPATH
TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN
TMP=/u01/tmp
TMPDIR=/u01/tmp
export TMP TMPDIR
4. Oracle 11g Enterprise Ed.(11.2.0.1.0) setup files
5. BE CAREFUL! Before taking following steps, it is strongly recommended to take a proper backup of your database.

Upgrade from Oracle 10g to 11g.

1. Install Oracle 11g to different ORACLE_HOME
2. Change .profile file to set new ORACLE_HOME for 11G and ORACLE_SID to temporary value. Then re-login to changes take effect
ORACLE_TERM=dtterm
export ORACLE_TERM
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
PATH=$PATH:/usr/bin:/usr/ucb:/etc:$ORACLE_HOME/bin:/bin:/usr/sbin:/usr/local/bin:/usr/openwin/bin:/opt/bin:/usr/ccs/bin:/usr/dt/bin:
/usr/ucb/bin:/usr/java/bin:.
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/openwin/lib:/usr/dt/lib:/usr/ucb/lib:/usr/local/lib
export LD_LIBRARY_LIB
ORACLE_SID=ORACLE11GSID
export ORACLE_SID
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export ORA_NLS33
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/product/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib:$ORACLE_HOME/jre
export CLASSPATH
TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN
TMP=/u01/tmp
TMPDIR=/u01/tmp
export TMP TMPDIR

3. Check new env. variables with env command. ORACLE_HOME AND ORACLE_SID must have been changed.

4. Start Oracle 11g installer
-bash-3.00$ ./runInstaller
Starting Oracle Universal Installer…

Checking Temp space: must be greater than 180 MB. Actual 52164 MB Passed
Checking swap space: must be greater than 150 MB. Actual 52493 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /u01/tmp/OraInstall2012-01-10_03-41-58PM. Please wait …

How to upgrade Oracle 10g to 11g R2 on Solaris 10 – Part 2/3