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...
