Monthly Archives: December 2015

Upgrade Oracle Database

These steps are for Upgrading Oracle Document, I have used 11.2.0.4 as target and 11.1.0.7 as source. You might have different versions while upgrading but the step remains same if the path you are following is certified from Oracle.

Always good to check the Oracle Support for certified path.

Task A: Installing new Oracle Software on UNIX box.

1.    Go to Binary location

  1. Will find the 11gR2 release under: “/oracle_cds/staging/64bit/11_2_release”
  2. Installing 11.2.0.4  [This is just an example, in your case database version may vary]:
    “/oracle_cds/staging/64bit/Oracle11.2.0.4/database”

2.    Start X server at your Windows Workstation

  1. Set Display environmental variable
    1. “export DISPLAY=<your_IP>.0:0

3.    Installation Wizard – 30 min

  1. Start the “runInstaller” from -20 min
    “/oracle_cds/ibm/64bit/11_2_release/Oracle11.2.0.4/database” as oracle user.
  2. Execute root.sh script as a root user – 2 min

 

Task B : Upgrading the Database from 11gR1 to 11.2.0.4

Task B-1: Pre-upgrade steps

1.    Collecting Pre-upgrade report

  • Copy the “utlu112i.sql” from new ORACLE_HOME/rdbms/admin to a temp location
    “/opt/oracle/product/11.2.0/db4/rdbms/admin”

2.    Executing the pre-upgrade tool
time – 10 mins

  • Change the location to that temporary directory
  • Start the sqlplus and login as sysdba into the target database (old version)
  • Execute the script which was copied at temporary location with spooling enabled
  • Read the spool log to get the further steps.

3.    Analyze the log for recommended changes.

 

Task B-2: Preparing for the Upgrade

1.    Check the integrity of database — used for lower versions [Prior to 11gR1]

  • Download “DBUPGDIAG.SQL” from Oracle support
  • Execute the script to check the integrity issues
  • Ask for the log location, give the temporary directory used above as the temporary log directory, all logs will be generated under that directory.

2.    Optimizer Statistics

Gather stats on SYS Schema

4.    Disable Oracle Database Vault

  • SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;
  • Follow Oracle Support Document 453903.1
  • Link

5.    Backing up Enterprise Manager Database Control Data – Skip

  • Initiate the oraenv for the old version of oracle
  • Change the current directory to new ORACLE_HOME
    • $ emdwgrd -save -sid old_SID -path save_directory
  • where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data.

7.    Ensure the users SYS and SYSTEM have ‘SYSTEM’ as their default tablespace.

  • Check using:
  • SELECT username, default_tablespace
    FROM dba_users
    WHERE username in (‘SYS’,’SYSTEM’);
  • Alter by this code:
    SQL> ALTER user SYS default tablespace SYSTEM;
    SQL> ALTER user SYSTEM default tablespace SYSTEM;

8.    Note down the location of datafiles, redo logs and control files

  • SQL> SELECT name FROM v$controlfile;
    SQL> SELECT file_name FROM dba_data_files;
    SQL> SELECT group#, member FROM v$logfile;.
  • Take a backup of all files

9.    Stop the listener

  • lsnrctl stop testdb_lsnr
  • initiate the netca from previous version of ORACLE_HOME
  • delete the existing listener using netca
  • stop the dbconsole also

10. Shutdown the Database

11. Take a cold backup/rman backup

  • backup database format ‘/oracle/tempdb/andy/%u’ tag ‘before_upgrade’
  • BACKUP CURRENT CONTROLFILE TO ‘/oracle/tempdb/andy’;
  • Backup the whole db and pfile/spfile/control file

 

 

12. Modify the init parameters

  • 11gR1 parameters are similar to 11gR2
  • We done need to modify the parameters in our scenario but may vary on your database, best thing is to refer pre-upgrade report generated earlier

13. Set Environment Variables at OS

  • Set Oracle_HOME to new HOME [ 11gR2 home]
  • Set Oracle_BASE
  • Set Oracle_SID
  • Update the oratab entried according to the database which you are upgrading
  • Copy the .profile from user home to a new file and edit the oracle home value

 

Task B-3: Upgrading steps

 

  • Go to $ORACLE_HOME/rdbms/admin
  • Start the database in upgrade mode
  • Run the following: – 40 min
    1. SQL> set echo on
      SQL> SPOOL upgrade.log
      SQL> @catupgrd.sql
      SQL> spool off

 

 

TASK B-4: Post Upgrade Steps

 

  • Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql
  • Run utlrp.sql to compile all system objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

  • select * from v$version;
  • Create the listener entry in the listener.ora
  • Start the listener
  • Set the listener parameter in the database
  • Gather the statistics on the database
    1. Not gathering statistics can cause performance issues.

 

 

ISSUES

  1. RMAN Catalog

Your rman catalog might be at the lower version, whereas rman catalog have to be at upper version of the database. Hence, upgrade your catalog version.

Advertisements