RMAN duplicate database using backup location and skip oracle tablespace option

RMAN duplicate database using backup location and skip oracle tablespace option:(PROD to TEST)
----------------------------------------------------------------------------------------------

1) create pfile from prodution database

2) modify the pfile according to test environment
  db_name
  admup dest
  control_fiiles
  db_file_name_conver
  log_file_name_convert
 
3) create the required directories for controlfiles,datafiles,archivelogs,audit files etc

4) start the auxiliary database in nomount

   startup nomount

4) connect to rman no need to connect to target database as we are using backup location.

rman auxiliary /

RMAN> run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
duplicate database to 'TEST' backup location '/data/oracle/archive/PROD/PRODBKP/' nofilenamecheck  skip tablespace USERS,HIST_DETAIL,HIST_IDX;
}

5) we can ignore below errors

RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace


6) check the TEST database

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 12 11:16:21 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE

SQL> archive log lsit
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/oracle/archive/PROD/TEST/
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



Rman log file:
=================
rman auxiliary /

RMAN> run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
duplicate database to 'TEST' backup location '/data/oracle/archive/PROD/PRODBKP/' nofilenamecheck  skip tablespace USERS,HIST_DETAIL,HIST_IDX;
}

allocated channel: aux1
channel aux1: SID=1284 device type=DISK

allocated channel: aux2
channel aux2: SID=2565 device type=DISK

allocated channel: aux3
channel aux3: SID=3847 device type=DISK

Starting Duplicate Db at 12-NOV-15

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2137886720 bytes

Fixed Size                     2223208 bytes
Variable Size               1056965528 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3847 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/data/oracle/archive/PROD/PRODBKP/PROD_control_file_c-4235066977-20151108-03.bak';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    2137886720 bytes

Fixed Size                     2223208 bytes
Variable Size               1056965528 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3846 device type=DISK

Starting restore at 12-NOV-15

channel aux2: skipped, AUTOBACKUP already found
channel aux3: skipped, AUTOBACKUP already found
channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:08
output file name=/data/oracle/PROD/TEST/control01.ctl
output file name=/data/oracle/PROD/TEST/control02.ctl
Finished restore at 12-NOV-15

database mounted
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 4 skipped by request
Datafile 5 skipped by request
Datafile 6 skipped by request


contents of Memory Script:
{
   set until scn  13642258978044;
   set newname for datafile  1 to
 "/data/oracle/PROD/TEST/system01.dbf";
   set newname for datafile  2 to
 "/data/oracle/PROD/TEST/sysaux01.dbf";
   set newname for datafile  3 to
 "/data/oracle/PROD/TEST/undotbs01.dbf";
   set newname for datafile  10 to
 "/data/oracle/PROD/TEST/MISC_01.dbf";
   set newname for datafile  11 to
 "/data/oracle/PROD/TEST/MISC_02.dbf";
   set newname for datafile  12 to
 "/data/oracle/PROD/TEST/MISC_03.dbf";
   set newname for datafile  30 to
 "/data/oracle/PROD/TEST/undotbs02.dbf";
   set newname for datafile  31 to
 "/data/oracle/PROD/TEST/MISC_04.dbf";
   set newname for datafile  33 to
 "/data/oracle/PROD/TEST/MISC_05.dbf";
   restore
   clone database
   skip forever tablespace  "USERS",
 "HIST_IDX",
 "HIST_DETAIL" ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 12-NOV-15

channel aux1: starting datafile backup set restore
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to /data/oracle/PROD/TEST/system01.dbf
channel aux1: restoring datafile 00010 to /data/oracle/PROD/TEST/MISC_01.dbf
channel aux1: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80875_1_895196715.bak
channel aux2: starting datafile backup set restore
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00002 to /data/oracle/PROD/TEST/sysaux01.dbf
channel aux2: restoring datafile 00031 to /data/oracle/PROD/TEST/MISC_04.dbf
channel aux2: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80873_1_895195794.bak
channel aux3: starting datafile backup set restore
channel aux3: specifying datafile(s) to restore from backup set
channel aux3: restoring datafile 00003 to /data/oracle/PROD/TEST/undotbs01.dbf
channel aux3: restoring datafile 00012 to /data/oracle/PROD/TEST/MISC_03.dbf
channel aux3: restoring datafile 00033 to /data/oracle/PROD/TEST/MISC_05.dbf
channel aux3: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80874_1_895196270.bak
channel aux2: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80873_1_895195794.bak tag=HOT_DB_BK_LEVEL0
channel aux2: restored backup piece 1
channel aux2: restore complete, elapsed time: 00:08:36
channel aux2: starting datafile backup set restore
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00011 to /data/oracle/PROD/TEST/MISC_02.dbf
channel aux2: restoring datafile 00030 to /data/oracle/PROD/TEST/undotbs02.dbf
channel aux2: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80872_1_895195349.bak
channel aux3: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80874_1_895196270.bak tag=HOT_DB_BK_LEVEL0
channel aux3: restored backup piece 1
channel aux3: restore complete, elapsed time: 00:15:21
channel aux1: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80875_1_895196715.bak tag=HOT_DB_BK_LEVEL0
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:18:51
channel aux2: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80872_1_895195349.bak tag=HOT_DB_BK_LEVEL0
channel aux2: restored backup piece 1
channel aux2: restore complete, elapsed time: 00:13:25
Finished restore at 12-NOV-15

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=895576491 file name=/data/oracle/PROD/TEST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=895576492 file name=/data/oracle/PROD/TEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=895576492 file name=/data/oracle/PROD/TEST/undotbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_02.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=15 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_03.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=16 STAMP=895576492 file name=/data/oracle/PROD/TEST/undotbs02.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=17 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_04.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=18 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_05.dbf

contents of Memory Script:
{
   set until scn  13642258978044;
   recover
   clone database
   skip forever tablespace   "USERS",
 "HIST_IDX",
 "HIST_DETAIL"   delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 12-NOV-15

Executing: alter database datafile 4 offline drop
Executing: alter database datafile 5 offline drop
Executing: alter database datafile 6 offline drop

starting media recovery

channel aux1: starting archived log restore to default destination
channel aux1: restoring archived log
archived log thread=1 sequence=50993
channel aux1: restoring archived log
archived log thread=1 sequence=50994
channel aux1: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_arc_80877_1_895197296.bak
channel aux1: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_arc_80877_1_895197296.bak tag=TAG20151108T015456
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:00:25
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50993_811696399.arc thread=1 sequence=50993
channel clone_default: deleting archived log(s)
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50993_811696399.arc RECID=2 STAMP=895576512
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50994_811696399.arc thread=1 sequence=50994
channel clone_default: deleting archived log(s)
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50994_811696399.arc RECID=1 STAMP=895576493
media recovery complete, elapsed time: 00:00:04
Finished recover at 12-NOV-15
Oracle instance started

Total System Global Area    2137886720 bytes

Fixed Size                     2223208 bytes
Variable Size               1073742744 bytes
Database Buffers            1056964608 bytes
Redo Buffers                   4956160 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2137886720 bytes

Fixed Size                     2223208 bytes
Variable Size               1073742744 bytes
Database Buffers            1056964608 bytes
Redo Buffers                   4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3846 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     8
  MAXLOGHISTORY     1168
 LOGFILE
  GROUP   1 ( '/data/oracle/redo/PROD/redo01a.log', '/data/oracle/redo/PROD/redo01b.log' ) SIZE 200 M  REUSE,
  GROUP   2 ( '/data/oracle/redo/PROD/redo02a.log', '/data/oracle/redo/PROD/redo02b.log' ) SIZE 200 M  REUSE,
  GROUP   3 ( '/data/oracle/redo/PROD/redo03b.log', '/data/oracle/redo/PROD/redo03a.log' ) SIZE 200 M  REUSE,
  GROUP   4 ( '/data/oracle/redo/PROD/redo04a.log', '/data/oracle/redo/PROD/redo04b.log' ) SIZE 200 M  REUSE,
  GROUP   5 ( '/data/oracle/redo/PROD/redo05b.log', '/data/oracle/redo/PROD/redo05a.log' ) SIZE 200 M  REUSE
 DATAFILE
  '/data/oracle/PROD/TEST/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/data/oracle/PROD/TEST/temp01.dbf";
   set newname for tempfile  2 to
 "/data/oracle/PROD/TEST/temp02.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/data/oracle/PROD/TEST/sysaux01.dbf",
 "/data/oracle/PROD/TEST/undotbs01.dbf",
 "/data/oracle/PROD/TEST/MISC_01.dbf",
 "/data/oracle/PROD/TEST/MISC_02.dbf",
 "/data/oracle/PROD/TEST/MISC_03.dbf",
 "/data/oracle/PROD/TEST/undotbs02.dbf",
 "/data/oracle/PROD/TEST/MISC_04.dbf",
 "/data/oracle/PROD/TEST/MISC_05.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /data/oracle/PROD/TEST/temp01.dbf in control file
renamed tempfile 2 to /data/oracle/PROD/TEST/temp02.dbf in control file

cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/sysaux01.dbf RECID=1 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/undotbs01.dbf RECID=2 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_01.dbf RECID=3 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_02.dbf RECID=4 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_03.dbf RECID=5 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/undotbs02.dbf RECID=6 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_04.dbf RECID=7 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_05.dbf RECID=8 STAMP=895576536

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=895576536 file name=/data/oracle/PROD/TEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=895576536 file name=/data/oracle/PROD/TEST/undotbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=3 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=4 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_02.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=5 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_03.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=6 STAMP=895576536 file name=/data/oracle/PROD/TEST/undotbs02.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=7 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_04.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=8 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_05.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace

RMAN> exit

oracle Transportable Tablespace

Limitations on Transportable Tablespace
=========================================
The following are the limitations of using Transportable tablespace.
1.   The source and target database must use the same character set and national character set.
2.   We cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
3.   Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
4.   Tablespaces that do not use block encryption but that contain tables with encrypted columns cannot be transported. You must use Oracle Data Pump to export and import the tablespace's schema objects. You can take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported.
5.   Beginning with Oracle Database 10g Release 2, we can transport tablespaces that contain XMLTypes. Beginning with Oracle Database 11g Release 1, you must use only Data Pump to export and import the tablespace metadata for tablespaces that contain XMLTypes.
6.   We cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.
7.   Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients

steps for Oracle TTS:
===========================

1. first need to Find the Endian fromat for target and source DB using below query.

 SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;


2. on the source DB : Check any self contained violations present in the tablespaces 
SQL> select name from v$tablespace;
NAME
 ------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS1
 USERS
 TEMP
 SAMPLE

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS,SAMPLE',TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;


3. on the source DB

Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.

$ expdp system/sys DUMPFILE=expdata.dmp DIRECTORY=expdp_dir TRANSPORT_TABLESPACES =USERS,SAMPLE LOGFILE=expdata.log
Result: Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed 

Collect the required users,roles,grants,profile  to  create again in the target database

$ ./expdp system/sys DUMPFILE=exp_meta_dmp.dmp DIRECTORY=expdp_dir full=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE content=METADATA_ONLY
Result: Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed 

Convert the datafiles into a backup location on the source platform. (its based on the indianess)

$ RMAN TARGET /
RMAN> CONVERT TABLESPACE 'USERS','SAMPLE' TO PLATFORM 'Linux IA (32-bit)' FORMAT='/u02/backup/%U'

RMAN> exit

4. copy the dump and backup file to target server.

$ RMAN TARGET /
RMAN> CONVERT DATAFILE '/u02/backup/data_D-RAN_I-2884613691_TSSAMPLE_FNO-5_0em3mj05'  format "/*existing database data file path*/sample01.dbf";

Result:
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished conversion at target at 04-FEB-11

RMAN> CONVERT DATAFILE '/u02/ran/data_D-RAN_I-2884613691_TS-USERS_FNO4_0fm3mj0r' format '/* existing database data file path* /users01.dbf';
Result:
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 04-FEB-11

RMAN> exit
Recovery Manager complete.

Import the required users, roles, grants, profiles   metadata using the Data Pump Import utility, impdp.

$ impdp system/sys DIRECTORY=expdp_dir    DUMPFILE=exp_meta_dmp.dmp full=y 

Result:
Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed

Now Import the tablespace metadata using Data Pump Import utility, impdp.

impdp system/sys DIRECTORY=expdp_dir DUMPFILE=expdata.dmp TRANSPORT_DATAFILES='/ existing database data file path/users01.dbf','/ existing database data file path/sample01.dbf';

Result:
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed

List the tablespaces for verify the imported tablespace

SQL> select name from v$tablespace;

Enable read/write mode for the specific imported tablespaces.

SQL> alter tablespace USERS read write; Tablespace altered.
SQL> alter tablespace SAMPLE read write; Tablespace altered.
SQL> exit

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.

ORACLE DATAGUARD

http://dba-tutorials.blogspot.in/p/oracle-dataguard.html

ORACLE DATABASE CLONING

http://dba-tutorials.blogspot.in/p/database-cloning.html

ORACLE BACKUP,RESTORE AND RECOVERY

http://dba-tutorials.blogspot.in/p/backuprestore-and-recovery_22.html

ORACLE USER MANAGEMENT

http://dba-tutorials.blogspot.in/p/user-management.html

ORACLE REDOLOG AND UNDO MANAGEMENT

http://dba-tutorials.blogspot.in/p/redolog-and-undo-management.html

ORACLE TABLESPACE MANAGEMENT

http://dba-tutorials.blogspot.in/p/tablespace-management.html

ORACLE DATABASE ARCHITECTURE

http://dba-tutorials.blogspot.in/p/oracle-database-architecture.html