Upgrade 11203 DB to 11204 Using RMAN

We recently had an application requirement when the source 11203 database was needed by application team on different server with different name. Also the the database needs to upgraded from 11203 to 11204.

Following note describes the steps one needs to take this kind of exercise.


SOURCE DB 11.2.0.3 - NWK3
NEW DUPLICATE DB 11.2.0.4- NWD6A

##
## FIRST IDENTIFY THE DISKS AND DISK GROUPS NEEDED FOR THE NEW DATABASE. I USED THE EXISTING DATABASE CONFIGURATION AS TEMPLATE
##

ASMCMD [+] > lsdg NWC6A*

 NWC6ADATA1
 NWC6AARCH1
 NWC6AARCH3

ASMCMD [+] > lsdsk -G NWC6ADATA1
Path
/dev/asmd0008
/dev/asmd0009
/dev/asmd0010
/dev/asmd0011

ASMCMD [+] >  lsdsk -G NWC6AARCH1
Path
/dev/asma0011
/dev/asma0012

ASMCMD [+] >  lsdsk -G NWC6AARCH3
Path
/dev/asma0013
/dev/asma0014
/dev/asma0015
/dev/asma0016

#############
## NWD6ADATA1 - check available disks for creation of disk group.
#############

select path, group_number group_#, disk_number disk_#, mount_status, header_status, state, total_mb, free_mb
from v$asm_disk where path like '/dev/asmd%' and header_status='CANDIDATE' order by path ;

PATH
--------------------------------------------------------------------------------
   GROUP_#     DISK_# MOUNT_S HEADER_STATU STATE      TOTAL_MB    FREE_MB
---------- ---------- ------- ------------ -------- ---------- ----------
/dev/asmd0012
         0         28 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asmd0013
         0         29 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asmd0014
         0         30 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asmd0015
         0         31 CLOSED  CANDIDATE    NORMAL            0          0


#############
## NWD6AARCH3
#############
select path, group_number group_#, disk_number disk_#, mount_status, header_status, state, total_mb, free_mb
from v$asm_disk where path like '/dev/asma%' and header_status='CANDIDATE' order by path ;


PATH
--------------------------------------------------------------------------------
   GROUP_#     DISK_# MOUNT_S HEADER_STATU STATE      TOTAL_MB    FREE_MB
---------- ---------- ------- ------------ -------- ---------- ----------

/dev/asma1001
         0          0 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asma1002
         0          1 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asma1003
         0          2 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asma1004
         0          3 CLOSED  CANDIDATE    NORMAL            0          0


#############
## NWD6AARCH1
#############

/dev/asma2001
         0          4 CLOSED  CANDIDATE    NORMAL            0          0

/dev/asma2002
         0          5 CLOSED  CANDIDATE    NORMAL            0          0


##
## CREATE A NEW DATA DISK GROUP FOR NWD6A DATABASE
##

CREATE DISKGROUP NWD6ADATA1 EXTERNAL REDUNDANCY
DISK '/dev/asmd0012' NAME asmd0012, '/dev/asmd0013' NAME asmd0013, '/dev/asmd0014' NAME asmd0014,'/dev/asmd0015' NAME asmd0015;

Diskgroup created.
 

##
## CREATE A NEW ARCH1 DISK GROUP FOR NWD6A DATABASE
##

CREATE DISKGROUP NWD6AARCH1 EXTERNAL REDUNDANCY
DISK '/dev/asma2001' NAME asma2001, '/dev/asma2002' NAME asma2002;

Diskgroup created.

##
## CREATE A NEW ARCH3 DISK GROUP FOR NWD6A DATABASE
##
CREATE DISKGROUP NWD6AARCH3 EXTERNAL REDUNDANCY
DISK '/dev/asma1001' NAME asma1001, '/dev/asma1002' NAME asma1002, '/dev/asma1003' NAME asma1003, '/dev/asma1004' NAME asma1004;

Diskgroup created.


##
## NOW PREPARE THE PARAMETER FILE. EITHER ONE CAN PREPARE THE NORMAL FILE OR ## USE THE PARAMETER FILE FROM SOURCE DATABASE
## 

*.audit_file_dest='/ora47/adump/nwd6a'
*.compatible='11.2.0.3.0'
*.control_files='+NWD6ADATA1/control01.ctl','+NWD6ADATA1/control02.ctl','+NWD6ADATA1/control03.ctl'
*.db_block_size=8192
*.DB_CREATE_FILE_DEST=('+NWD6ADATA1')
*.db_domain=''
*.db_files=2000
*.db_name='nwd6a'
*.db_unique_name='nwd6a'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/ora01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NWD6AXDB)'
*.log_archive_dest_1='LOCATION=+NWD6AARCH1'
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=10670309376
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_max_size=3G
*.sga_target=3G
*.undo_tablespace='UNDOTBS1'



##
## Start the database in nomount mode..
##


< xxx : nwd6a : oracle : /users/oracle/hshah/GOLD_MAROON_FROM_TORQUISE_DONOT_DEL/nwk3_gold_cpy >
$ rman auxiliary /
connected to auxiliary database: NWD6A (not mounted)

run
{
allocate auxiliary channel c1  type disk;
allocate auxiliary channel c2  type disk;
allocate auxiliary channel c3  type disk;
allocate auxiliary channel c4  type disk;
allocate auxiliary channel c5  type disk;
DUPLICATE DATABASE TO 'nwd6a' pfile='/ora01/app/oracle/product/11.2.0.4/db_1/dbs/initnwd6a.ora' 
BACKUP LOCATION '/users/oracle/hshah/GOLD_MAROON_FROM_TORQUISE_DONOT_DEL/nwk3_gold_cpy' noredo noopen
LOGFILE
      GROUP 1 ('+nwd6adata1') SIZE  100M  REUSE,
      GROUP 2 ('+nwd6adata1') SIZE  100M  REUSE,
      GROUP 3 ('+nwd6adata1') SIZE  100M  REUSE,
      GROUP 4 ('+nwd6adata1') SIZE  100M  REUSE;       
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}

##
## ISSUES THAT CAME ACROSS...
##

1/ SOURCE DISK GROUP DID NOT EXIST - diskgroup "NWK3DATA1"
   ORA-15001: diskgroup "NWK3DATA1" does not exist or is not mounted
   ORA-15001: diskgroup "NWK3DATA1" does not exist or is not mounted

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NWD6A" RESETLOGS NOARCHIVELOG
  MAXLOGFILES     64
  MAXLOGMEMBERS      3
  MAXDATAFILES     1000
  MAXINSTANCES     1
  MAXLOGHISTORY      584
 LOGFILE
  GROUP   1 ( '+nwd6adata1' ) SIZE 100 M  REUSE,
  GROUP   2 ( '+nwd6adata1' ) SIZE 100 M  REUSE,
  GROUP   3 ( '+nwd6adata1' ) SIZE 100 M  REUSE,
  GROUP   4 ( '+nwd6adata1' ) SIZE 100 M  REUSE
 DATAFILE
  '+NWD6ADATA1/nwd6a/datafile/system.286.877202211'
 CHARACTER SET WE8ISO8859P1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/16/2015 19:45:21
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00349: failure obtaining block size for '+nwk3data1'
ORA-15001: diskgroup "NWK3DATA1" does not exist or is not mounted
ORA-15001: diskgroup "NWK3DATA1" does not exist or is not mounted


CAUSE - This failure happend due log_file_name_convert and db_file_name_convert parameter. Hence those paraemeters were removed.

##
## AFTER MODIFYING THE PARAMETER FILE AND REMOVING CONVERT PARAMETER, THE DUPLICATION WORKED.
##
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 04/16/2015 21:19:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 29360134
Session ID: 3 Serial number: 5


##
## MADE CHANGES TO SCRIPT FOR NOREDO AND NOOPEN
## CHANGED THE LOCATION FOR THE ONLINE LOGFILE
##

$ rman auxiliary /
connected to auxiliary database: NWD6A (not mounted)

run
{
allocate auxiliary channel c1  type disk;
allocate auxiliary channel c2  type disk;
allocate auxiliary channel c3  type disk;
allocate auxiliary channel c4  type disk;
allocate auxiliary channel c5  type disk;
DUPLICATE DATABASE TO 'nwd6a' pfile='/ora01/app/oracle/product/11.2.0.4/db_1/dbs/initnwd6a.ora' 
BACKUP LOCATION '/users/oracle/hshah/GOLD_MAROON_FROM_TORQUISE_DONOT_DEL/nwk3_gold_cpy' noredo noopen
LOGFILE
      GROUP 1 ('+nwd6adata1') SIZE  100M  REUSE,
      GROUP 2 ('+nwd6adata1') SIZE  100M  REUSE,
      GROUP 3 ('+nwd6adata1') SIZE  100M  REUSE,
      GROUP 4 ('+nwd6adata1') SIZE  100M  REUSE;       
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}



##
## AFTER RUNNING THE DUPLICATE WITH NOREDO AND NO OPEN THE DUPLICATE WENT
## FINE...
##


datafile 22 switched to datafile copy
input datafile copy RECID=21 STAMP=877251535 file name=+NWD6ADATA1/nwd6a/datafile/i_dmrep.278.877250233
Leaving database unopened, as requested
Finished Duplicate Db at 17-APR-15

2. Issue with UNDO TABLE SPACE

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2248080 bytes
Variable Size            1442841200 bytes
Database Buffers          687865856 bytes
Redo Buffers                4931584 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE "NWD6A" RESETLOGS NOARCHIVELOG
  2    MAXLOGFILES     64
  3    MAXLOGMEMBERS      3
  4    MAXDATAFILES     1000
  MAXINSTANCES     1
  5    6    MAXLOGHISTORY      584
  7   LOGFILE
  8    GROUP   1 ( '+nwd6adata1' ) SIZE 100 M  REUSE,
  9    GROUP   2 ( '+nwd6adata1' ) SIZE 100 M  REUSE,
 10    GROUP   3 ( '+nwd6adata1' ) SIZE 100 M  REUSE,
 11    GROUP   4 ( '+nwd6adata1' ) SIZE 100 M  REUSE
 12   DATAFILE
  '+NWD6ADATA1/nwd6a/datafile/system.286.877202211'
 13   14   CHARACTER SET WE8ISO8859P1
 15  ;


SQL> spool upgrade.log
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30046: Undo tablespace UNDO1 not found in control file.
Process ID: 32571418
Session ID: 298 Serial number: 1


##
## AFTER THE RESTORE AND RECOVERY DONE, STOP & START THE DUPLICATED DATABASE.
## CHECK FOR TEMP FILES AND LOG FILES.
##

SQL> select * from gv$tempfile;

   INST_ID      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- 

         1          1       5.6483E+12 21-JAN-14          3          1 ONLINE  READ WRITE          0          0   9663676416       8192 +NWD6ADATA1
         1          2       5.6479E+12 10-MAY-13          3          2 ONLINE  READ WRITE          0          0   2147483648       8192 +NWD6ADATA1


##
## OPEN THE DB IN UPGRADE MODE.
##

SQL> alter database open resetlogs upgrade ;
Database altered.

SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
nwd6a            OPEN MIGRATE


##
## RUN THE UPGRADE SCRIPT.
##

sql > @$ORACLE_HOME/rdbms/admin/catupgrd.sql


SQL> @$ORAC1120/rdbms/admin'UTLU112S.SQL;

SQL> @$ORA11202/RDBMS/ADMIN/CATUPPST.SQL
SQL> @$ORAC1120/rdbms/admin/utlrp.sql;

##
## AFTER UPGRADE OPEN THE DATABASE IN OPEN MODE
##

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2250648 bytes
Variable Size            1174407272 bytes
Database Buffers         2013265920 bytes
Redo Buffers               16912384 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


##
## RECREATE SPFILE ON ASM
##

$ cat initnwd6a.ora
SPFILE='+nwd6adata1/nwd6a/parameterfile/spfile.257.877255249'


##
## ADD DATABASE TO OCR AS CRS RESOURCE
##

$ srvctl  add database -d nwd6a -o /ora01/app/oracle/product/11.2.0.4/db_1 -p '+nwd6adata1/nwd6a/parameterfile/spfile.257.877255249' -s OPEN -t NORMAL -y AUTOMATIC -a "nwd6adata1,nwd6aarch1,nwd6aarch3"


$ srvctl  start database -d nwd6a
< rri2rxodda201 : nwd6a : oracle : /ora01/app/oracle/product/11.2.0.4/db_1/dbs >
$ srvctl  status  database -d nwd6a
Database is running.

SQL> create or replace  directory EXPDP as '/ora49/dpdump/nwd6a' ;
Directory created.

Once you are done, your database is ready to roll...