steps to restore oracle 11.2.0.3 database to oracle 11.2.0.4 database and then upgrade

steps to restore oracle  11.2.0.3 database to oracle  11.2.0.4 database and then upgrade:
==============================================================

1) create the pfile based on the prod database and change the folder locations as per test env.

2) create the password file,listener files.

3) copy the rman backup files to test server.

4) start the database in nomount state.

  sqlplus / as sysdba
SQL> startup nomunt

5) restore the controlfile from backup and clean the all backup entries of prod.
 
rman target /
rman> restore controlfile from 'controlfile backup location'
rman> sql 'alter database mount';
rman> crosscheck backup;
rman> delete force expired backup;

6) catalog the full backup and archivelog backups to rman

rman > catalog start with 'backup location';

7) restore the database with below script.

run
{
restore force database;
}

8) we can do a recovery preview to see the minimun scn we need to do recovery.u will get the minimum SCN needs to me recover.

rman > recover database preview;

9) start the recover based the scn you got.

rman > recover database until scn 23207925635;

10) open the database in open resetlogs upgrade.

SQL> alter database open resetlogs upgrade;

Database altered.
SQL> select name,open_mode,log_mode,flashback_on,switchover_status from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON       SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------ --------------------
ADEDB01   READ WRITE           ARCHIVELOG   NO                 NOT ALLOWED

11) need to run precheck script before upgrade and fix the issues.

SQL> @$ORACLE_HOME/RDBMS/ADMIN/utlu112i.sql

12) run the ungrade script. (refer upgrade.log)

SQL> @$ORACLE_HOME/RDBMS/ADMIN/catupgrd.sql

13) to check the upgrade (refer check_upgrade.log)

SQL> startup;
SQL> @$ORACLE_HOME/RDBMS/ADMIN/utlu112s.sql

14) need to run below script and database no need to be in upgrade mode.

SQL> @$ORACLE_HOME/RDBMS/ADMIN/catuppst.sql

15) to recompile all the invalid objects after upgrade.

SQL> @$ORACLE_HOME/RDBMS/ADMIN/utlrp.sql


run below sql's to check component version:
============================================
col action_time for a30
col BUNDLE_SERIES for a15
col NAMESPACE for a10
col comments for a30
select * from dba_registry_history;

ACTION_TIME                    ACTION                         NAMESPACE  VERSION                        ID BUNDLE_SERIES   COMMENTS
------------------------------ ------------------------------ ---------- ------------------------------ ---------- --------------- ------------------------------
30-APR-07 01.52.32.546000 PM   CPU                            SERVER     10.2.0.2.0                     5912179            CPUApr2007
06-MAY-11 01.22.21.599000 PM   UPGRADE                        SERVER     10.2.0.4.0                                        Upgraded from 10.2.0.2.0
06-MAY-11 01.52.34.865000 PM   APPLY                          SERVER     10.2.0.4                       43 WINBUNDLE       Patch 43
30-JUN-12 03.38.34.480000 AM   VIEW INVALIDATE                                                          8289601            view invalidation
30-JUN-12 03.38.35.244000 AM   UPGRADE                        SERVER     11.2.0.3.0                                        Upgraded from 10.2.0.4.0
30-JUN-12 03.41.09.918000 AM   APPLY                          SERVER     11.2.0.3                        0 PSU             Patchset 11.2.0.2.0
19-AUG-17 02.11.05.030000 AM   VIEW INVALIDATE                                                          8289601            view invalidation
19-AUG-17 02.11.05.265000 AM   UPGRADE                        SERVER     11.2.0.4.0                                        Upgraded from 11.2.0.3.0
19-AUG-17 04.33.50.802000 AM   APPLY                          SERVER     11.2.0.4                        170418 PSU                WinBundle 11.2.0.4.170418

9 rows selected.

col comp_id for a10
col comp_name for a40
col version for a12
col status for a12
select comp_id, comp_name, version, status from dba_registry;

COMP_ID    COMP_NAME                                VERSION      STATUS
---------- ---------------------------------------- ------------ ------------
EM         Oracle Enterprise Manager                11.2.0.4.0   VALID
AMD        OLAP Catalog                             11.2.0.4.0   VALID
SDO        Spatial                                  11.2.0.4.0   VALID
ORDIM      Oracle Multimedia                        11.2.0.4.0   VALID
XDB        Oracle XML Database                      11.2.0.4.0   VALID
CONTEXT    Oracle Text                              11.2.0.4.0   VALID
EXF        Oracle Expression Filter                 11.2.0.4.0   VALID
RUL        Oracle Rules Manager                     11.2.0.4.0   VALID
OWM        Oracle Workspace Manager                 11.2.0.4.0   VALID
CATALOG    Oracle Database Catalog Views            11.2.0.4.0   VALID
CATPROC    Oracle Database Packages and Types       11.2.0.4.0   VALID
JAVAVM     JServer JAVA Virtual Machine             11.2.0.4.0   VALID
XML        Oracle XDK                               11.2.0.4.0   VALID
CATJAVA    Oracle Database Java Packages            11.2.0.4.0   VALID
APS        OLAP Analytic Workspace                  11.2.0.4.0   VALID
XOQ        Oracle OLAP API                          11.2.0.4.0   VALID

16 rows selected.