Oracle Database Upgrade 10g to 11g

Oracle Database Upgrade 10g to 11g

At this article I'll present Step by step upgrading Oracle 10g database to 11g.During my testing to upgrade i have tried DBUA(database upgrade assistant) and Manual upgrade,however i would like to share Manually upgrading.

Before starting upgrade we should know that database binary,files,listeners,tnsname,spfile and passwordfile had backed.

1. Stop isqlplus  

[oracle@localhost ]$ isqlplusctl stop 

iSQL*Plus 10.2.0.4.0 Copyright (c) 2003, 2005, Oracle. All rights reserved.

 Stopping iSQL*Plus

 iSQL*Plus stopped.

2.Stop listener 

[oracle@localhost ]$lsnrctl stop Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))The command completed successfully

3.Stop Enterprise manager

 [oracle@localhost ]$ emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.

 Now Installing Oracle 11g Software 

The Oracle 10g  had installed on /u01/app/oracle/oracle/product/10.2.0/db_1

The Oracle 11g may install on  /u01/app/oracle/oracle/product/11.2.0/db_1

1. Go to /etc/sysctl.conf  Put the following setting in.

fs.file-max = 65536
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

2. Run /sbin/sysctl -p 

3. Now Create New directory for new version Oracle 11g as above

 [oracle@localhost ]$ echo $ORACLE_BASE
/u01/app/oracle
 [oracle@localhost ]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
 [oracle@localhost ]$ cd /u01/app/oracle/product
 [oracle@localhost ]$ mkdir -p 11.1.0/db_1
 [oracle@localhost ]$ mkdir -p 11.1.0/oraInventory

4   4. Now we should change the Bash_profile 

      [oracle@localhost ]$ vi .bash_profile

      # User specific environment and start up programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Oracle 11g Home settings
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl11g; export ORACLE_SID

# Oracle 10g Home settings
#ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
#ORACLE_SID=orcl10g; export ORACLE_SID
#ORACLE_TERM=xterm; export ORACLE_TERM
#PATH=/usr/sbin:$PATH; export PATH
#PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; 
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; 
export CLASSPATH

if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
            ulimit -p 16384
            ulimit -n 65536
      else
            ulimit -u 16384 -n 65536
      fi
fi

So its time to install Oracle 11g  

Go to Oracle 11g database file and 

[oracle@localhost ]$ ./runinstaller

2.Pre-Upgrade Information Tools

After installation Oracle 11g we should analyze our database. 

This is checking the database for upgrading and replay a report for us
$cp /u01/app/oracle/oracle/product/product/11.2.0/db_1/rdbms/admin/utlu112i.sql /tmp   

Now Lets start. Log in as Oracle 10g Binary
$/u01/app/oracle/oracle/product/product/10.2.0/db_1/bin/sqlplus / as sysdba 

Use the Spool for a report. SQL> SPOOL upgrade_check_veysi.log 

Run the script to check database
SQL> @/tmp/utlu112i.sql

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

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

.

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

So put down the important information, As you see the all should  Valid, otherwise we have to make valid manually the invalid tools.

Now close spool.
SQL> SPOOL OFF
SQL> exit
If we want to looking at the check log,

$vi upgrade_check_veysi.log

Now remove dump_dest parameters from spfile.


SQL> alter system reset background_dump_dest scope=spfile sid='*';

System altered.

SQL> alter system reset core_dump_dest scope=spfile sid='*';

System altered.

SQL> alter system reset user_dump_dest scope=spfile sid='*';

System altered.

 Shutdown the database and create pfile from spfile.

SQL> shutdown immediate

SQL> create pfile from spfile;

File created.

SQL> exit

Now Copy the init.ora and oraclepassword under Oracle 11g binary

cp initora10g.ora orapwora10g /u01/app/oracle/product/11.1.0/db_1/dbs/

$cd $ORACLE_HOME/rdbms/admin
$sqlplus / as sysdba

Open the database upgrade mode

SQL> STARTUP UPGRADE; 

Use spool again for report
SQL> SPOOL upgrade.log 

Now run the upgrade after it finished we should check upgrade log if we got any error we would fix it.
SQL> @catupgrd.sql 

Close the database normally

SQL> SHUTDOWN IMMEDIATE; 

and startup normally,
SQL> STARTUP;

And now again check the upgrade summary if there is any invalid we would set it valid.
SQL> @utlu112s.sql 

Run the 
SQL> @catuppst.sql 

Compile the invalid objects
SQL> @utlrp.sql

Now check your object.
SQL> SELECT count(*) FROM dba_invalid_objects; 

Now change the listener to Oracle 11g

# listener.ora Network Configuration:

 /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = Orcl11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = xxxxxxxxx))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl11g.oracle.com)(PORT = 1521))
    )
  )

And Start Listener

[oracle@localhost ]$ Start lsnrctl

I hope you will successful on this important issue... Have a nice oracle day :)

Finding Oracle Alert Log

The Location of Alert log

At this article i will present the alert log's location. which is very important for DBAs.

First we are getting connect the Sqlplus.

SQL> sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 01 18:49:29 2014

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

Connected to an idle instance.

Looking our version...

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

Getting our Location

SQL> show parameter background

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orcl/orcl/trace

And finding our ALERT log

SQL> !ls /u01/app/oracle/diag/rdbms/orcl/orcl/trace/a*

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log