Upgrading the database from 11.1.0.6 TO 11.2.0.2

UPGRADE 11.1.0.6 TO 11.2.0.2
---------------------------------------
1. Install required version 11.2.0.1
old version 11.1.0.6 (db_1)
new version 11.2.0.1 (db_2)

2. Install patches on new ORACLE_HOME if required.

Using Opatch apply

3. goto ORACLE_HOME (db_2) location
 copy /rdbms/admin/utlu112i.sql to  /tmp/utlu112i.sql

4. copy network configuration files tnsnames.ora and listener.ora to new ORACLE_HOME

5. Copy pfile or spfile from old ORACLE_HOME/dbs to new ORACLE_HOME/dbs location

6. ensure your system, sys user using same tablespace or not.

7. check for backups.
select * from v$backup;
if any wait to complete.

8. purge dba_recyclebin;

9. collect the dictionary statistics
exec dbas_stats.gather_dictionary_stats();

10. select * from dba_2pc_pending;
If there is any transactions close them.

11. Disable all cronjobs.

12. check the integrity of the database
sql> spool /tmp/dbup.txt
sql> @/tmp/utlu112i.sql
sql> spool off
take necessary steps if needed.

13. vi /etc/oratab
change old ORACLE_HOME (db_1)TO new ORACLE_HOME (db_2)

14. shut immediate

15. Take full backup.

16.
. oraenv
sqlplus / as sysdba
startup upgrade;
[check file version, home version
it will start the database in temp mode]

17. sql> spool /tmp/dbup.txt
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/utlu112s.sql
@?/rdbms/admin/catuppst.sql
sql> spool off

18. sql> select * from dba_registry;

19.check for invalid objects.
sql> select object_name, object_type, status from dba_objects where status like 'INVALID';

If there is any invalid objects
sql> @?/rdbms/admin/utlrp.sql

still any invalid objects
sql> alter object_type, object_name compile;
sql> alter table <table_name> compile;
sql> alter procedure <procedure_name> compile;

20. take full backup

21. collect the statistics.

22. stop the listener and start the new listener in new ORACLE_HOME.

23. Enable crontab job.

Database slient Installation using response file

1. In database software 
copy database/response/db_install.rsp    file to /tmp  folder

2. modify the response file which is in   /tmp/db_install.rsp    file (for 11.2)

For reference (what are the parameters u have to modify) verify the response file shown below.

3. create the database path where u want to install (must be specified in response file)
mkdir -p /u01/app/oracle/product/11.2.0/db_1

4. modify the permissions for the folders.

chown  -R oracle:dba    /path

chmod  -R 775   /path

5. su - oracle

goto database folder where runInstaller is present.

$ runInstaller   -slient  -responseFile  /tmp/db_install.rsp

responsefile parameters:

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

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0

#-------------------------------------------------------------------------------
# The installation option can be one of the following
# 1. INSTALL_DB_SWONLY
# 2. INSTALL_DB_AND_CONFIG
# 3. UPGRADE_DB
#-------------------------------------------------------------------------------

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=rac2.oracl.com
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/u01/app/oracle

#-------------------------------------------------------------------------------
# Installation Edition of the component.                      
#                                                           
# The value should contain only one of these choices.      
# EE     : Enterprise Edition                              
# SE     : Standard Edition                                
# SEONE  : Standard Edition One
# PE     : Personal Edition (WINDOWS ONLY)
#-------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=false
oracle.install.db.customComponents=
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.CLUSTER_NODES=

#-------------------------------------------------------------------------------
# One of the following
# - GENERAL_PURPOSE                            
# - TRANSACTION_PROCESSING                     
# - DATAWAREHOUSE                              
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.SYSMAN=
oracle.install.db.config.starterdb.password.DBSNMP=

#-------------------------------------------------------------------------------
# Can be one of the following
# 1. GRID_CONTROL
# 2. DB_CONTROL
# -----------------------------------------------------------------------

oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.dbcontrol.emailAddress=
oracle.install.db.config.starterdb.dbcontrol.SMTPServer=
oracle.install.db.config.starterdb.automatedBackup.enable=false
oracle.install.db.config.starterdb.automatedBackup.osuid=
oracle.install.db.config.starterdb.automatedBackup.ospwd=

#-------------------------------------------------------------------------------
# Can be one of the following
# - FILE_SYSTEM_STORAGE
# - ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=

Database Switchover and Switchback

Prerequisites:
1. Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

Query to check if the managed recovery process is running on the standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CLOSING             157
ARCH      CLOSING             155
ARCH      CONNECTED             0
ARCH      CLOSING             156
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                158
MRP0      WAIT_FOR_LOG        158

The above fig. shows that the Managed Recovery Process (MRP0) is running on the standby database. If MRP is not running, then start the process with real time enabled using the below query in the standby database.

SQL>alter database recover managed standby disconnect from session;

Once when the MRP has started on the standby database, make sure that the archive logs generated at the primary end are shipped and getting applied to the standby database.

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)
---------- --------------
     1            163

In the above case, sequence# 163 is the maximum sequence generated at the primary database and the same has been applied to the standby database.

2. Verify primary and standby tempfiles match

For each temporary tablespace on the standby, verify that temporary files associated with that tablespace on the primary database also exist on the standby database. Tempfiles added after initial standby creation are not propagated to the standby. Run this query on both the primary and target physical standby databases and verify that they match.

 SQL>select tmp.name filename,bytes,ts.name tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

3.  Verify that all datafiles are online on both primary and standby databases

Check whether all the datafiles are online prior to the switchover on both primary and standby databases

SQL>select name from v$datafile where status=’OFFLINE’;no rows selected

4. Check if there are any jobs running on the primary database using the below query.

SQL> select * from dba_jobs_running;

no rows selected

If there are any jobs running on the primary database and if its execution is not very important, then terminate the job to continue further.

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.

SQL> sho parameter job_que

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.

SQL>alter system set job_queue_processes=0 scope=spfile;

System altered

5. Query the switchover_status column of the v$database view on the primary database to determine whether the primary database can be switched over to the standby.

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

--------------------
TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.

6.  Ensure standby log files exist on the primary database.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

7. Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database.

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS GAP_STATUS
---------    ------------------------
VALID    NO GAP

Do not proceed until the value of the STATUS column is VALID and the value of the GAP_STATUS column is NOGAP, for the row that corresponds to the standby database.

8.  Ensure both tns listeners are pinging and registered with the database.

$tnsping  tnsstby

9. Make sure all datagaurd parameters for switchover configured. Like fall_server, log_archive_dest and log_archive_dest_state

Procedure:

primary name: db1
standby name: stby

on primary (db1)

sql> alter system set log_archive_dest_state_2='defer';
sql> select max(sequence#) from v$log_history;
sql> atler database commit to switchover to physical standby;
sql> shut immediate;

sql> startup mount;
sql> select name, open_mode, database_role from v$database;
sql> select max(sequence#) from v$log_history;

on standby (stby)

sql> alter database recover managed standby database cancel;
sql> select max(sequence#) from v$log_history;
sql> atler database commit to switchover to primary;
sql> shut immediate;

sql> startup 
sql> select name, open_mode, database_role from v$database;
sql> select max(sequence#) from v$log_history;
sql> alter system switch logfile;
sql> alter system switch logfile;
sql> select max(sequence#) from v$log_history;

on primary (db1)
sql> alter database recover managed standby database disconnect from session;
sql> select max(sequence#) from v$log_history;


switching back

on standby (db1)

sql> alter database recover managed standby database cancel;
sql> shut immediate

sql> startup 
sql> alter database commit to switchover to primary;
sql> shut immediate
sql> startup

on primary (stby)

sql> select name, open_mode, database_role from v$database;
sql> atler database commit to switchover to physical standby;
sql> shut immediate;

sql> startup mount;
sql> startup nomount
sql> alter database mount standby database;

sql> select name, open_mode, database_role from v$database;

sql> alter database recover managed standby database disconnect from session;
sql> select max(sequence#) from v$log_history;

on standby (db1)

sql> alter system set log_archive_dest_state_2='enable';
sql> select max(sequence#) from v$log_history;
sql> alter system switch logfile;
sql> /
sql>/
sql> select max(sequence#) from v$log_history;

on primary (stby)

sql> select max(sequence#) from v$log_history;