Learn Oracle from Oracle

Wednesday, July 28, 2010

RMAN CLONING CRMPS2 DB TO CRM1JT DB

Source db : CRMPS2

Target db : CRM1JT

Server : au07qap0q0tels2

Steps 1: Create the required directories in filesystem:

mkdir /ora/db003/data001/CRM1JT
mkdir /ora/db003/redo001/CRM1JT
mkdir /ora/db003/redo002/CRM1JT
mkdir /ora/db003/data001/back
mkdir /ora/admin/CRM1JT

cd /ora/admin/CRM1JT

mkdir udump cdump bdump adump

Steps 2 : Take a list of all datafiles , control files, online redofiles from source db CRMPS2.

select TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1,2;

TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------
APP_CRAMERMVLOGTS2 /u02/oradata/data/CRMPS2/CRMPS2_app_cramermvlogts2_01.dbf
AUDIT1 /u02/oradata/data/CRMPS2/CRMPS2_audit_01.dbf
GENUSER /u02/oradata/data/CRMPS2/CRMPS2_genuser_01.dbf
SYSAUX /u02/oradata/data/CRMPS2/CRMPS2_sysaux_01.dbf
SYSTEM /u02/oradata/data/CRMPS2/CRMPS2_system_01.dbf
TOOLS /u02/oradata/data/CRMPS2/CRMPS2_tools_01.dbf
UNDOTBS /u07/oradata/undo/CRMPS2/CRMPS2_undotbs_01.dbf
USR /u02/oradata/data/CRMPS1/CRMPS2_usr_04.dbf
USR /u02/oradata/data/CRMPS2/CRMPS2_usr_01.dbf
USR /u02/oradata/data/CRMPS2/CRMPS2_usr_02.dbf
USR /u02/oradata/data/CRMPS2/CRMPS2_usr_03.dbf

select TABLESPACE_NAME,FILE_NAME from dba_temp_files order by 1,2;

TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------
TEMP /u08/oradata/temp/CRMPS2/CRMPS2temp_01.dbf
TEMPORARY /u08/oradata/temp/CRMPS2/CRMPS2temporary_01.dbf

select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/data/CRMPS2/CRMPS2_control_01.ctl
/u03/oradata/index/CRMPS2/CRMPS2_control_02.ctl
/u05/oradata/redo/CRMPS2/CRMPS2_control_03.ctl

select GROUP#,MEMBER from v$logfile order by 1,2;

GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 /u04/oradata/redo/CRMPS2/CRMPS2_redo_01_01.log
1 /u05/oradata/redo/CRMPS2/CRMPS2_redo_01_02.log
2 /u04/oradata/redo/CRMPS2/CRMPS2_redo_02_01.log
2 /u05/oradata/redo/CRMPS2/CRMPS2_redo_02_02.log
3 /u04/oradata/redo/CRMPS2/CRMPS2_redo_03_01.log
3 /u05/oradata/redo/CRMPS2/CRMPS2_redo_03_02.log

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u06/DBFR/CRMPS2
Oldest online log sequence 815
Next log sequence to archive 817
Current log sequence 817

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files;

SUM(BYTES)/1024/1024/1024
-------------------------
45.6855469

SQL> c/data_files/segments
1* select sum(bytes)/1024/1024/1024 from dba_segments
SQL> /

SUM(BYTES)/1024/1024/1024
-------------------------
32.5649414

Steps 3 : Take the source db backup on disk or in tape.

RMAN > run
{

allocate channel C1 type disk;

backup database format '/ora/db003/data001/back/b_%d_%t_%s.bk';

sql 'alter system archive log current';

BACKUP FORMAT '/ora/db003/data001/back/arch_CRMPS2_%t_%s.arc'
archivelog all;

release channel C1;
}

Create the initCRM1JT.ora and copy the password to

cd $ORACLE_HOME/dbs

cp initCRMPS2.ora initCRM1JT.ora

cp –p orapwCRMPS2 orapwCRM1JT

==>>>initCRM1JT.ora <<=====

*._optimizer_cost_based_transformation='OFF'
*._trace_files_public=TRUE
*.audit_file_dest='/tmp'
*.audit_trail='FALSE'
*.background_dump_dest='/ora/admin/CRM1JT/bdump'
*.compatible='10.2.0.4'
*.CONTROL_FILE_RECORD_KEEP_TIME=16
*.control_files='/ora/db003/data001/CRM1JT/CRM1JT_control_01.ctl','/ora/db003/redo001/CRM1JT/CRM1JT_control_02.ctl','/ora/db003/redo002/CRM1JT/CRM1JT_control_03.ctl'
*.core_dump_dest='/ora/admin/CRM1JT/cdump'
*.db_file_name_convert=('/u02/oradata/data/CRMPS2','/ora/db003/data001/CRM1JT','/u02/oradata/data/CRMPS1','/ora/db003/data001/CRM1JT','/u07/oradata/undo/CRMPS2','/ora/db003/data001/CRM1JT','/u08/oradata/temp/CRMPS2','/ora/db003/data001/CRM1JT')
*.log_file_name_convert=('/u04/oradata/redo/CRMPS2','/ora/db003/redo001/CRM1JT','/u05/oradata/redo/CRMPS2','/ora/db003/redo002/CRM1JT')
*.cursor_sharing='SIMILAR'
*.DB_BLOCK_SIZE=8192
*.db_cache_size=100000000
*.db_file_multiblock_read_count=16
*.db_files=999
*.db_name='CRM1JT'
*.db_writer_processes=20
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CRM1JTXBD)'
*.fast_start_mttr_target=300
*.instance_name='CRM1JT'
*.java_max_sessionspace_size=120000000
*.java_pool_size=120000000
*.java_soft_sessionspace_limit=100000000
*.job_queue_processes=0
CRM1JT.job_queue_processes=8
*.large_pool_size=8000000
*.log_archive_dest='/u06/DBFR/CRM1JT'
*.LOG_ARCHIVE_FORMAT='arch_CRM1JT_%s_%t_%r.arc'
*.log_buffer=512000
*.NLS_DATE_FORMAT='DD-MM-YYYY'
*.nls_length_semantics='CHAR'
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=2000
*.optimizer_index_caching=0
*.optimizer_index_cost_adj=100
*.OPTIMIZER_MODE='CHOOSE'
*.optimizer_secure_view_merging=FALSE
*.parallel_adaptive_multi_user=TRUE
*.parallel_max_servers=2500
*.pga_aggregate_target=50000000
*.processes=500
*.query_rewrite_enabled='TRUE'
*.rdbms_server_dn='cn=CRM1JT,cn=OracleContext,dc=telstra,dc=com'
*.REMOTE_OS_AUTHENT=FALSE
*.service_names='CRM1JT'
*.session_cached_cursors=500
*.sessions=555
*.sga_max_size=3785120000
*.sga_target=1895825408
*.shared_pool_size=536870912
*.sort_area_retained_size=0
*.sort_area_size=0
*.sqltune_category='TELSTRA'
*.trace_enabled=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.user_dump_dest='/ora/admin/CRM1JT/udump'
*.utl_file_dir='/amdocsftpxmldir/crmps2/export/'

==>>

Vi /var/opt/oracle/oratab

Add the below line

CRM1JT:/u01/ora/product/1020/db_1:Y

. oraenv
CRM1JT

Steps 4 ===>>>

SQL> startup nomount pfile=’ /u01/ora/product/1020/db_1/dbs/initCRM1JT.ora

==> Run the below script <===

#!/bin/ksh
for sid in CRMPS2
do
ORACLE_SID=$sid;export ORACLE_SID
ORAENV_ASK=NO
ORAENV=/usr/local/bin/oraenv
export ORAENV
. $ORAENV
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
echo $ORACLE_SID >> bkp_${ORACLE_SID}.log
echo $ORACLE_HOME >> bkp_${ORACLE_SID}.log
$ORACLE_HOME/bin/rman target / nocatalog < connect auxiliary sys/Alr3_ady@CRM1JT
@/ora/admin/pwp728628/rman_duplicate.rcv
exit
!
done

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

script ==>> rman_duplicate.rcv

au07qap0q0tels2:CRMPS2:/ora/admin/pwp728628>cat rman_duplicate.rcv

run {
set until scn 10560195594037;
allocate channel ch1 type disk;
allocate auxiliary channel aux1 type disk;
duplicate target database to CRM1JT;
}

====>>>>>>>>>output as given below <<===========

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 16:24:38 2010

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

connected to target database: CRMPS2 (DBID=1287191112)
using target database control file instead of recovery catalog

RMAN>
connected to auxiliary database: CRM1JT (not mounted)

RMAN>
RMAN> run {
2> set until scn 10560195594037;
3> allocate channel ch1 type disk;
4> allocate auxiliary channel aux1 type disk;
5> duplicate target database to CRM1JT;
6> }
executing command: SET until clause

allocated channel: ch1
channel ch1: sid=497 devtype=DISK

allocated channel: aux1
channel aux1: sid=522 devtype=DISK

Starting Duplicate Db at 06-JUL-10

contents of Memory Script:
{
set until scn 10560195594037;
set newname for datafile 1 to
"/ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf";
set newname for datafile 2 to
"/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf";
set newname for datafile 3 to
"/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf";
set newname for datafile 4 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf";
set newname for datafile 5 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf";
set newname for datafile 6 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf";
set newname for datafile 7 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf";
set newname for datafile 8 to
"/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf";
set newname for datafile 9 to
"/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf";
set newname for datafile 10 to
"/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf";
set newname for datafile 11 to
"/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf";
restore
check readonly
clone database
;
}
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

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 06-JUL-10

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf
restoring datafile 00002 to /ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf
restoring datafile 00003 to /ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf
restoring datafile 00004 to /ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf
restoring datafile 00005 to /ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf
restoring datafile 00006 to /ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf
restoring datafile 00007 to /ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf
restoring datafile 00011 to /ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf
channel aux1: reading from backup piece /ora/db003/data001/back/b_CRMPS2_723645314_1544.bk
channel aux1: restored backup piece 1
piece handle=/ora/db003/data001/back/b_CRMPS2_723645314_1544.bk tag=TAG20100706T123513
channel aux1: restore complete, elapsed time: 00:08:26
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf
restoring datafile 00009 to /ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf
restoring datafile 00010 to /ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf
channel aux1: reading from backup piece /ora/db003/data001/back/b_CRMPS2_723645610_1545.bk
channel aux1: restored backup piece 1
piece handle=/ora/db003/data001/back/b_CRMPS2_723645610_1545.bk tag=TAG20100706T123513
channel aux1: restore complete, elapsed time: 00:00:25
Finished restore at 06-JUL-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CRM1JT" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_01_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_01_02.log' ) SIZE 600 M REUSE,
GROUP 2 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_02_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_02_02.log' ) SIZE 600 M REUSE,
GROUP 3 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_03_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_03_02.log' ) SIZE 600 M REUSE
DATAFILE
'/ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf'
CHARACTER SET AL32UTF8

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

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf

contents of Memory Script:
{
set until scn 10560195594037;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 06-JUL-10

starting media recovery

archive log thread 1 sequence 828 is already on disk as file /u06/DBFR/CRMPS2/arch_CRMPS2_828_1_686533499.arc
archive log thread 1 sequence 829 is already on disk as file /u06/DBFR/CRMPS2/arch_CRMPS2_829_1_686533499.arc
archive log filename=/u06/DBFR/CRMPS2/arch_CRMPS2_828_1_686533499.arc thread=1 sequence=828
archive log filename=/u06/DBFR/CRMPS2/arch_CRMPS2_829_1_686533499.arc thread=1 sequence=829
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-JUL-10

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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 3791650816 bytes

Fixed Size 2045160 bytes
Variable Size 2583694104 bytes
Database Buffers 1191182336 bytes
Redo Buffers 14729216 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CRM1JT" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_01_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_01_02.log' ) SIZE 600 M REUSE,
GROUP 2 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_02_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_02_02.log' ) SIZE 600 M REUSE,
GROUP 3 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_03_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_03_02.log' ) SIZE 600 M REUSE
DATAFILE
'/ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf'
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile 2 to
"/ora/db003/data001/CRM1JT/CRMPS2temp_01.dbf";
set newname for tempfile 3 to
"/ora/db003/data001/CRM1JT/CRMPS2temporary_01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 2 to /ora/db003/data001/CRM1JT/CRMPS2temp_01.dbf in control file
renamed temporary file 3 to /ora/db003/data001/CRM1JT/CRMPS2temporary_01.dbf in control file

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf recid=1 stamp=723659659

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf recid=2 stamp=723659660

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf recid=3 stamp=723659660

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf recid=4 stamp=723659660

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf recid=5 stamp=723659660

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf recid=6 stamp=723659661

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf recid=7 stamp=723659661

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf recid=8 stamp=723659661

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf recid=9 stamp=723659661

cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf recid=10 stamp=723659662

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=723659659 filename=/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=723659662 filename=/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf

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

database opened
Finished Duplicate Db at 06-JUL-10
released channel: ch1

RMAN>
RMAN> **end-of-file**

RMAN>

Recovery Manager complete.

performace is improved 4 times if the SERVICE_NAME is replaced Instance

Problem Description: In the RAC Database the performace is improved 4 times if the SERVICE_NAME is replaced by connecting directly to a single node.

### PROBLEM STATEMENT ###
1. Configured the applocation with the SERVICE_NAME connection string which is load balanced across 3 nodes of the RAC. Ran test for 1 hr.

2. Then reconfigured the Application with the SID of 1 node. Re-ran the test for 1 hr.

WE can get 4 times better write performace by doing the 2nd method.
### GENERAL SYSTEM/DATABASE INFORMATION ###
3 Node RAC
DB=PFPS3
Service PFPS3SRV
SID = PFPS31, PFPS32 PFPS33

==>>

SERVICE
----------
PFPS3SRV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ole-rac10g-ole-vip1.sensis.com.au)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ole-rac10g-ole-vip2.sensis.com.au)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ole-rac10g-ole-vip3.sensis.com.au)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PFPS3SRV)
)
)

SID
-----
PFPS31 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ole-rac10g-ole-vip1.sensis.com.au)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PFPS3)
(INSTANCE_NAME = PFPS31)
)
)

============>>>>>>>>>>

Generic Note
------------
Can you do the following and upload the results

1. Run the test in RAC environment and then collect 2-3 snapshots of AWR and ADDM reports covering timeframe of 20-30 mins when you observe slow performance.

2. Also upload AWR reports from single instance which is good.

==>>

Generic Note
------------
From AWR report using service name, I see "gc buffer busy" is top wait event
=====
Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
gc buffer busy 1,036,259 17,618 17 50.6 Cluster
enq: TX - index contention 91,021 6,192 68 17.8 Concurrency
gc current block busy 115,149 3,524 31 10.1 Cluster
CPU time 3,245 9.3
enq: TX - row lock contention 3,645 1,141 313 3.3 Application

SQL ordered by Cluster Wait Time

Cluster Wait Time (s) CWT % of Elapsd Time Elapsed Time(s) CPU Time(s) Executions SQL Id SQL Module SQL Text
15,667.11 66.27 23,641.55 1,082.08 280,769 1g1nh993kad5r JDBC Thin Client insert into Search (phraseId, ...

Segments by Buffer Busy Waits

* % of Capture shows % of Buffer Busy Waits for each top segment compared
* with total Buffer Busy Waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture
PFP_OWNER PFP_LARGE_INDX PK_SEARCH MAY2010 INDEX PARTITION 218,851 82.08
PFP_OWNER PFP_SMALL_DATA SYSTEMACTIVITYSUMMARY TABLE 22,245 8.34
PFP_OWNER PFP_LARGE_INDX IDX_SEARCHLOCATION_02 MAY2010 INDEX PARTITION 5,892 2.21
PFP_OWNER PFP_LARGE_INDX PK_SEARCHLOCATION MAY2010 INDEX PARTITION 5,482 2.06
PFP_OWNER PFP_LARGE_INDX IDX_SEARCH_02 MAY2010 INDEX PARTITION 3,573 1.34
====

I see PK_SEARCH index partition is causing contention.
Can you please tune this or try to recreate this one.
Also upload ADDM reports during performance issue as requested.

========>>>>>>>>

you are quiet right in seeing the GC events being high when the service_name is used. The question is why is it not that high when we are connection directly to 1 node?

Also the index partition contention is only occuring when the service_name is used to connect. Not an issue when connection to the 1 node.

I have uploaded the ADDM report from the time it was running slow.

Generic Note
------------
Issue Analysis
---------------------

Performance is reported as different when connection is made through a service where load balancing is enabled versus a service where the connection is directed always at instance 1.
There is no connection occurring directly through an SID both are using SERVICE_NAME to direct the connection.
The connect string will only impact how the connection is directed to the RDBMS and ultimately which instance the session is established within.

Comments
--------
=== ODM Action Plan ===

1. Exactly what is being executed during the testing in each case, what is the SQL being executed here?

2. Has SQL trace been enabled in the session performing the workload? Have execution plans of the SQL been compared across the 2 tests to ensure that they are the same in each case? What method for collecting statistics has been implemented in the environment?

3. Is the same instance being connected to at the time each test is performed?

4. Exactly how are you measuring the difference in performance, what metrics are being used to determine the measurement and the difference? You mentioned that there is 4 times better write performance when connecting directly to instance PFPS31. How is this being measured, what were the metrics seen across the 2 tests? Please supply these via the service request.

Paul,

Answers below

1. The below SQL is what the application is running.

select this_.id as id39_0_, this_.version as version39_0_, this_.publicationStateId as publicat3_39_0_, this_.phrase as phrase39_0_, this_.floorPrice as floorPrice39_0_, this_.adultPhrase as adultPhr6_39_0_, this_.blockPhrase as blockPhr7_39_0_, this_.stopPhrase as stopPhrase39_0_, this_.watchPhrase as watchPhr9_39_0_, this_.created as created39_0_, this_.createdBy as createdBy39_0_, this_.updated as updated39_0_, this_.updatedBy as updatedBy39_0_ from Phrase this_ where this_.phrase=:1

insert into Search (phraseId, publicationChannelId, location, offset, searchTimeStamp, token, id) values (:1, :2, :3, :4, :5, :6, :7)

UPDATE PhraseSearchSummary
SET searches = searches + 1
WHERE phraseId = :NEW.phraseId
AND periodTypeCode= c_SearchSummaryPeriodMonthly
AND startDate = l_startMonthTimestamp
AND token = :NEW.token;

UPDATE ChannelPublicationSummary
SET searches = searches + 1
WHERE publicationChannelId = :NEW.publicationChannelId
AND periodTypeCode= c_PubSummaryPeriodDaily
AND startDate = l_startDayTimestamp
AND token = :NEW.token;

UPDATE SystemActivitySummary
SET searches = searches + 1
WHERE periodTypeCode = c_SystemActivityPeriodDaily
AND startDate = l_startDayTimestamp
AND token = :NEW.token;

2. Tracing hasn't been enabled. The explain plans have been compared and these are the same. Standard default statistics collection is being used.

3. Yes this is the same instance

4. There is a log table that collects statistics every 5 mins that retrieves this information. When going straight to the instance performance is much greater than going through the ServiceName.

Thanks

==>>

Name
--------
ODM Data Collection

AWR Reports Instance Specific V Load Balanced Service
----------------------------------------------

Connection via Instance Specific Connection:

PFPS3 2190308783 PFPS31 1 10.2.0.4.0 YES oledbrx05p
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 2150 02-Jun-10 15:00:01 182 .7
End Snap: 2151 02-Jun-10 16:00:03 166 .6
Elapsed: 60.04 (mins)
DB Time: 25.19 (mins)
Report Summary
Cache Sizes

Begin End
Buffer Cache: 7,008M 7,008M Std Block Size: 8K
Shared Pool Size: 816M 816M Log Buffer: 20,628K

Load Profile

Per Second Per Transaction
Redo size: 27,320.10 267.85
Logical reads: 4,269.63 41.86
Block changes: 196.74 1.93
Physical reads: 121.70 1.19
Physical writes: 8.05 0.08
User calls: 774.62 7.59
Parses: 233.23 2.29
Hard parses: 1.16 0.01
Sorts: 2.66 0.03
Logons: 0.10 0.00
Executes: 147.31 1.44
Transactions: 102.00
% Blocks changed per Read: 4.61 Recursive Call %: 20.69
Rollback per transaction %: 88.45 Rows per Sort: 64.30

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 636 42.1
enq: TX - row lock contention 2,103 543 258 35.9 Application
gc current block busy 4,742 89 19 5.9 Cluster
db file scattered read 9,303 75 8 5.0 User I/O
log file sync 42,023 59 1 3.9 Commit

Connection via Load Balanced Service:

DB Name DB Id Instance Inst num Release RAC Host
PFPS3 2190308783 PFPS31 1 10.2.0.4.0 YES oledbrx05p
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 2148 02-Jun-10 13:00:23 154 .4
End Snap: 2149 02-Jun-10 14:00:35 194 .9
Elapsed: 60.20 (mins)
DB Time: 580.40 (mins)
Report Summary
Cache Sizes

Begin End
Buffer Cache: 7,024M 7,008M Std Block Size: 8K
Shared Pool Size: 800M 816M Log Buffer: 20,628K
Load Profile

Per Second Per Transaction
Redo size: 489,747.74 3,588.99
Logical reads: 14,631.53 107.22
Block changes: 2,733.52 20.03
Physical reads: 121.34 0.89
Physical writes: 124.28 0.91
User calls: 1,539.97 11.29
Parses: 894.03 6.55
Hard parses: 1.23 0.01
Sorts: 2.91 0.02
Logons: 0.12 0.00
Executes: 909.11 6.66
Transactions: 136.46

FileName
----------------
awrrpt_1_2150_2151_using_sid_name.html

FileComment
----------------------

FileName
----------------
awrrpt_1_2148_2149_using_service_name.html

FileComment
----------------------

As discussed with Paul, the following test will be performed and reports created.

Perform Cold Backup of DB.
Backup Messages in Sun messaging. Around 7 million messages.

Test 1
--------
Configure 3 App servers to connect to RAC direct to each instance.
App1 -> PFPS3SRV1 - > instance 1 - > PFPS31
App2 -> PFPS3SRV2 - > instance 2 - > PFPS32
App3 -> PFPS3SRV3 - > instance 1 - > PFPS33
Peform Load test.

Test 2
---------
Restore Cold DB Backup
Restore Sun Messaging.
Configure 3 App servers to Connect to RAC through Load Balanced Service Name.
App1,App2,App3 -> PFPS3SRV -> Prefered on PFPS31, PFPS32 and PFPS33
Perform load test.

AWR reports will be uploaded of each instance for each Test run.

Ranjeet Singh.

As agreed previously, here are the 2 test results with exact same test data run twice under different connection methods.

File 1:
instance_service.zip - AWR reports for duration when the App servers are connected directly to 1 instance.

File 2:
loadbalanced_service.zip - AWR reports for the duration when the App servers are connectd to a load_balanced service name.

File3:
performance_results.xls - A summary comparison of the 2 tests at 5 minute intervals for 1 particular insert statement that is the bulk of all executions.

=== ODM Research ===

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i21879

Oracle® Database Performance Tuning Guide
10g Release 2 (10.2)
Part Number B14211-03

15 Using Indexes and Clusters

15.3 Using Partitioned Indexes for Performance

The hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. In some OLTP applications, index insertions happen only at the right edge of the index. This could happen when the index is defined on monotonically increasing columns. In such situations right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index maintenance activity, which results in performance degradation.

Hi

I have uploaded the text versions of the report. Got your voicemail.
I am a bit confused as to why this index contention is only obvious in one connection methd. Because the test we did is the same load just coming in the database a different way.

=== ODM Issue Clarification ===

There is a major difference in performance seen when the middle tier application servers connect directly to instances in the cluster via instance specific services versus having each application server connect via a load balanced service that distributes the load across the cluster instances. Performance is 4 times better when the instance specific connections are made.

The environment:

Three Application Servers are connecting to a three node RAC cluster.

The 2 tests performed are:

1. Application Servers connect directly to a specific RAC instance.

Apps Server 1 -----------Instance 1 Specific Service -------------Instance 1
Apps Server 2 -----------Instance 2 Specific Service -------------Instance 2
Apps Server 3 -----------Instance 3 Specific Service ------------ Instance 3

2. All Application Servers connect through a single load balanced service that sees the connections distributed automatically across the cluster instances.

Apps Server 1 ------------| |------------------Instance 1
Apps Server 2 ------------|----------Load Balanced Service------------|------------------Instance 2
Apps Server 3 ------------| |------------------Instance 3

Name
--------
ODM Data Collection

WORKLOAD REPOSITORY report for Test 1 where a Load Balanced Service is used to establish Application Server Connections

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
PFPS3 2190308783 PFPS31 1 10.2.0.4.0 YES oledbrx05p

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2365 10-Jun-10 17:40:02 97 1.1
End Snap: 2371 10-Jun-10 19:40:26 114 .7
Elapsed: 120.40 (mins)
DB Time: 1,031.24 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 7,008M 7,008M Std Block Size: 8K
Shared Pool Size: 816M 816M Log Buffer: 20,628K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 525,075.37 5,698.35
Logical reads: 16,097.35 174.70
Block changes: 2,908.22 31.56
Physical reads: 60.94 0.66
Physical writes: 112.25 1.22
User calls: 1,154.91 12.53
Parses: 867.83 9.42
Hard parses: 0.13 0.00
Sorts: 1.24 0.01
Logons: 0.08 0.00
Executes: 953.86 10.35
Transactions: 92.15
..
.

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc buffer busy 2,205,839 32,536 15 52.6 Cluster
enq: TX - index contention 230,833 12,266 53 19.8 Concurrenc
CPU time 6,805 11.0
gc current block busy 190,947 5,761 30 9.3 Cluster
gc current grant busy 104,268 1,267 12 2.0 Cluster
-------------------------------------------------------------
..
.

Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 377.21 4.09
Global Cache blocks served: 343.74 3.73
GCS/GES messages received: 1,292.31 14.02
GCS/GES messages sent: 1,320.13 14.33
DBWR Fusion writes: 33.55 0.36
Estd Interconnect traffic (KB) 6,277.90

Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 97.29
Buffer access - remote cache %: 2.34
Buffer access - disk %: 0.37
..
.

Wait Class DB/Inst: PFPS3/PFPS31 Snaps: 2365-2371
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Cluster 5,269,616 .3 42,027 8 7.9
Concurrency 1,731,622 .1 13,156 8 2.6
System I/O 1,056,377 .0 1,100 1 1.6
Commit 710,332 .0 1,005 1 1.1
Other 1,105,515 41.4 454 0 1.7
User I/O 91,326 .0 341 4 0.1
Configuration 5,876 .0 81 14 0.0
Network 5,657,455 .0 14 0 8.5
Application 1,581 .0 3 2 0.0
-------------------------------------------------------------
..
.

Segments by Global Cache Buffer Busy DB/Inst: PFPS3/PFPS31 Snaps: 2365-2371
-> % of Capture shows % of GC Buffer Busy for each top segment compared
-> with GC Buffer Busy for all segments captured by the Snapshot

GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
PFP_OWNER PFP_LARGE_ PK_SEARCH JUN2010 INDEX 485,569 45.35
PFP_OWNER PFP_SMALL_ SYSTEMACTIVITYSUMMAR TABLE 207,526 19.38
PFP_OWNER PFP_LARGE_ IDX_SEARCHLOCATION_0 JUN2010 INDEX 163,480 15.27
PFP_OWNER PFP_LARGE_ IDX_IMPRESSION_03 JUN2010 INDEX 49,915 4.66
PFP_OWNER PFP_LARGE_ PK_SEARCHLOCATION JUN2010 INDEX 42,736 3.99
-------------------------------------------------------------

FileName
----------------
textversion.zip:awrrpt_1_2365_2371_PFPS3SRV3.txt

FileComment
----------------------

Oracle Support - June 17, 2010 5:18:20 AM GMT+05:30
Name
--------
ODM Data Collection

WORKLOAD REPOSITORY report for Test 2 where a Load Balanced Service is used to establish Application Server Connections

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
PFPS3 2190308783 PFPS31 1 10.2.0.4.0 YES oledbrx05p

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2419 11-Jun-10 11:40:14 110 .9
End Snap: 2425 11-Jun-10 13:40:01 114 .7
Elapsed: 119.78 (mins)
DB Time: 1,265.03 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 7,072M 7,072M Std Block Size: 8K
Shared Pool Size: 752M 752M Log Buffer: 20,628K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 228,483.23 5,816.49
Logical reads: 8,311.97 211.60
Block changes: 1,295.03 32.97
Physical reads: 71.32 1.82
Physical writes: 73.46 1.87
User calls: 482.81 12.29
Parses: 361.79 9.21
Hard parses: 0.05 0.00
Sorts: 1.41 0.04
Logons: 0.07 0.00
Executes: 397.25 10.11
Transactions: 39.28
..
.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc buffer busy 1,769,167 40,895 23 53.9 Cluster
enq: TX - index contention 127,876 17,917 140 23.6 Concurrenc
gc current block busy 230,647 9,610 42 12.7 Cluster
CPU time 3,467 4.6
buffer busy waits 250,528 1,173 5 1.5 Concurrenc
-------------------------------------------------------------
..
.

Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 407.96 10.39
Global Cache blocks served: 443.07 11.28
GCS/GES messages received: 1,033.65 26.31
GCS/GES messages sent: 952.65 24.25
DBWR Fusion writes: 25.62 0.65
Estd Interconnect traffic (KB) 7,196.18

Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 94.25
Buffer access - remote cache %: 4.91
Buffer access - disk %: 0.84
..
.

Wait Class DB/Inst: PFPS3/PFPS31 Snaps: 2419-2425
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Cluster 4,834,225 .3 53,248 11 17.1
Concurrency 653,810 .8 19,112 29 2.3
System I/O 713,505 .0 579 1 2.5
Other 831,508 47.2 455 1 2.9
Commit 286,016 .0 336 1 1.0
User I/O 32,672 .0 195 6 0.1
Configuration 1,915 .6 79 41 0.0
Network 2,326,450 .0 5 0 8.2
Application 3,251 .0 4 1 0.0
-------------------------------------------------------------
..
.

Segments by Global Cache Buffer Busy DB/Inst: PFPS3/PFPS31 Snaps: 2419-2425
-> % of Capture shows % of GC Buffer Busy for each top segment compared
-> with GC Buffer Busy for all segments captured by the Snapshot

GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
PFP_OWNER PFP_LARGE_ PK_SEARCH JUN2010 INDEX 712,906 80.76
PFP_OWNER PFP_LARGE_ IDX_SEARCHLOCATION_0 JUN2010 INDEX 56,772 6.43
PFP_OWNER PFP_SMALL_ SYSTEMACTIVITYSUMMAR TABLE 53,302 6.04
PFP_OWNER PFP_LARGE_ PK_SEARCHLOCATION JUN2010 INDEX 30,380 3.44
PFP_OWNER PFP_LARGE_ IDX_IMPRESSION_03 JUN2010 INDEX 10,160 1.15
-------------------------------------------------------------

FileName
----------------
textversion.zip:awrrpt_1_2419_2425_PFPS3SRV.txt

FileComment
----------------------

Oracle Support - June 17, 2010 5:52:07 AM GMT+05:30

Generic Note
------------------------
Issue Analysis:

Test 1 - Apps Server Instances connects directly to a individual RDBMS Instances.
Test 2 - All Apps Servers connect through a load balanced service that distributes connections across the RAC Cluster.

A drop in throughput can be seen from the AWR report when the load balanced service is used, Test 1's load profile shows more redo processed and more transactions per second executed.

Test 1:

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 525,075.37 5,698.35
Executes: 953.86 10.35
Transactions: 92.15

Test 2:

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 228,483.23 5,816.49
Executes: 397.25 10.11
Transactions: 39.28

The Top waits in both tests however appear to be the same indicating the same issue is present across both tests

Test 1:

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc buffer busy 2,205,839 32, 536 15 52.6 Cluster
enq: TX - index contention 230,833 12, 266 53 19.8 Concurrenc

Test 2:

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc buffer busy 1,769,167 40, 895 23 53.9 Cluster
enq: TX - index contention 127,876 17, 917 140 23.6 Concurrenc

These 2 waits "gc buffer busy" and "enq: TX - index contention" are indicating that the RAC instances in both tests are having to wait for buffers/blocks to become available as they are in use somewhere within the cluster (gc buffer busy).

The blocks causing the contention are more than likely index blocks given the second wait is "enq: TX - index contention"

The segment predominantly causing these waits in both cases is

In Test 1
GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
PFP_OWNER PFP_LARGE_ PK_SEARCH JUN2010 INDEX 485,569 45.35

In Test 2

GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
PFP_OWNER PFP_LARGE_ PK_SEARCH JUN2010 INDEX 712,906 80.76

As seen from the statistics across the 2 tests the number of busy waits as jumped from 485,569 in Test 1 to 712,906 in Test 2 for index PK_SEARCH partition June2010. This updating of the index structure as rows are being inserted is being performed down the right hand side of the index is causing contention for specific blocks with in it. Different sessions/instances/transactions are all wanting to update the same index blocks.

The introduction of sessions connecting through load balanced services has resulted in a higher percentage of transactions contending for the same buffers across the cluster. This will be due the way the individual transactions are being distributed and executed in Test 2.

Test 1 sees this occur less than Test 2 as the index blocks updated are more often already available in the instance where the update is occurring and do not need to be shipped anywhere resulting in better performance.

Both cases are seeing many index block splits indicating this index is growing down the right hand side due an ever increasing key of some sort, for example some form of auto incrementing key.

Test 1

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
branch node splits 222 0.0 0.0
leaf node 90-10 splits 2,351 0.3 0.0
leaf node splits 36,539 5.1 0.1

Test 2

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
branch node splits 79 0.0 0.0
leaf node 90-10 splits 858 0.1 0.0
leaf node splits 15,095 2.1 0.1

Given this it may be better to use a hash partitioned index structure so that updates of the index can be better distributed across different index blocks as the index keys will be distributed across partitions rather than all updates of the index occurring in one partition and set of blocks.

This will mean that it is less likely that different transactions distributed across the cluster are going to require access to the same blocks and therefor introduce the waits seen in both tests.

You would need to investigate the use of either al hash partitioned or (preferred) a Range Hash partitioned index instead of what you currently appear to have which is a range based partitioned index.

A Range Hash partitioned index will have the benefit of allowing better partition level maintenance of the index structure should you use ranges of data as a mechanism for maintaining the partitioned table and index structure.

I will create these tables with range/hash partitioning and conduct the test again. Also you made the following comment

"Test 1 sees this occur less than Test 2 as the index blocks updated are more often already available in the instance where the update is occurring and do not need to be shipped anywhere resulting in better performance"

Does that mean we need to look at our interconnect as well and investigate ways of increasing the throughput? i.e. jumb frames etc.

=== ODM Action Plan ===

Ranjeet Singh, it is not the tables that require re-organisation, it is the indexes on the tables in particular PK_SEARCH.

As to:

"Test 1 sees this occur less than Test 2 as the index blocks updated are more often already available in the instance where the update is occurring and do not need to be shipped anywhere resulting in better performance"

This statement is by no means indicating there is an interconnect performance issue. It is indicating that in Test 1 and Test 2 the same performance bottleneck is seen.
However in Test 1 the problem is felt to a lesser degree as it appears the behaviour of the transaction processing when Apps Servers are connected directly to instances is incurring less of a performance hit through fewer block transfers across the interconnect.
Test 1 sees 2.3% of buffers being found in a remote instance and transferred across the interconnect , you can see it from below list …

Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 97.29
Buffer access - remote cache %: 2.34
Buffer access - disk %: 0.37

Test 2 sec 4.9% of buffers being found in a remote instance and transferred across the interconnect, you can see the out put from below list ……

Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 94.25
Buffer access - remote cache %: 4.91
Buffer access - disk %: 0.84

==>>>
I uderstand that it is index contention that is the issue that requires subpartitioning to resolve the issue. I had to composite partition the table because we only have local indexes. This is to help us in archiving data and dropping partitions with minimum effort.

We have repartitioned this table as Range/Hash composite and you are right, there is a remarkable improvement in write performance. But we still get a lot better performace going direct then via the load balanced service. I am attaching the reports again for the 2 runs.

Test 1
--------
Configure 3 App servers to connect to RAC direct to each instance.
App1 -> PFPS3SRV1 - > instance 1 - > PFPS31
App2 -> PFPS3SRV2 - > instance 2 - > PFPS32
App3 -> PFPS3SRV3 - > instance 1 - > PFPS33
Peform Load test.

Name
--------
ODM Data Collection

WORKLOAD REPOSITORY report for awrrpt_1_2923_2926_PFPS3SRV1.txt

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
PFPS3 2190308783 PFPS31 1 10.2.0.4.0 YES oledbrx05p

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2923 18-Jun-10 11:40:02 129 1.0
End Snap: 2926 18-Jun-10 12:40:09 125 .8
Elapsed: 60.10 (mins)
DB Time: 596.92 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 7,040M 7,040M Std Block Size: 8K
Shared Pool Size: 784M 784M Log Buffer: 20,628K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 4,913,211.83 2,037.36
Logical reads: 44,223.94 18.34
Block changes: 30,266.88 12.55
Physical reads: 93.51 0.04
Physical writes: 497.08 0.21
User calls: 4,825.42 2.00
Parses: 2,417.11 1.00
Hard parses: 0.02 0.00
Sorts: 2.32 0.00
Logons: 0.36 0.00
Executes: 2,417.51 1.00
Transactions: 2,411.56

% Blocks changed per Read: 68.44 Recursive Call %: 34.04
Rollback per transaction %: 0.05 Rows per Sort: 39.13

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 34,171 95.4
log file sync 8,709,790 26,032 3 72.7 Commit
log file parallel write 1,604,636 1,980 1 5.5 System I/O
log file sequential read 18,236 684 37 1.9 System I/O
db file parallel write 197,143 600 3 1.7 System I/O
-------------------------------------------------------------
^LRAC Statistics DB/Inst: PFPS3/PFPS31 Snaps: 2923-2926

Begin End
----- -----
Number of Instances: 3 3

Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 31.12 0.01
Global Cache blocks served: 2.40 0.00
GCS/GES messages received: 960.99 0.40
GCS/GES messages sent: 1,018.51 0.42
DBWR Fusion writes: 0.36 0.00
Estd Interconnect traffic (KB) 654.78

Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 99.72
Buffer access - remote cache %: 0.07
Buffer access - disk %: 0.21

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Commit 8,709,790 .0 26,032 3 1.0
System I/O 1,854,132 .0 3,290 2 0.2

SQL ordered by Executions DB/Inst: PFPS3/PFPS31 Snaps: 2923-2926
-> Total Executions: 8,718,093
-> Captured SQL account for 99.9% of Total

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
8,680,310 8,677,688 1.0 0.00 0.00 da17mzv9db2qa
Module: JDBC Thin Client
INSERT INTO SEARCH_RANGE_HASH_ON_ID_32 (id, phraseId, publicationChannelId, loca
tion, offset, searchTimestamp, token) VALUES(:1, :2, :3, :4, :5, :6, :7)

SQL ordered by Cluster Wait Time DB/Inst: PFPS3/PFPS31 Snaps: 2923-2926

Cluster CWT % of Elapsed CPU
Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id
------------- ---------- ----------- ----------- -------------- -------------
12.97 0.2 6,374.59 6,295.18 8,680,310 da17mzv9db2qa
Module: JDBC Thin Client
INSERT INTO SEARCH_RANGE_HASH_ON_ID_32 (id, phraseId, publicationChannelId, loca
tion, offset, searchTimestamp, token) VALUES(:1, :2, :3, :4, :5, :6, :7)

Segments by Global Cache Buffer Busy DB/Inst: PFPS3/PFPS31 Snaps: 2923-2926
-> % of Capture shows % of GC Buffer Busy for each top segment compared
-> with GC Buffer Busy for all segments captured by the Snapshot

GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
TEST_SEARC PFP_LARGE_ SEARCH_RANGE_HASH_ON N2010_SP10 TABLE 1 50.00
TEST_SEARC PFP_LARGE_ SEARCH_RANGE_HASH_ON N2010_SP30 TABLE 1 50.00
-------------------------------------------------------------

FileName
----------------
awrrpt_1_2923_2926_PFPS3SRV1.txt

FileComment

2. All Application Servers connect through a single load balanced service that sees the connections distributed automatically across the cluster instances.

Apps Server 1 ------------| |------------------Instance 1
Apps Server 2 ------------|----------Load Balanced Service------------|------------------Instance 2
Apps Server 3 ------------| |------------------Instance 3

Name
--------
ODM Data Collection

WORKLOAD REPOSITORY report for awrrpt_1_2920_2922_PFPS3SRV.txt

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
PFPS3 2190308783 PFPS31 1 10.2.0.4.0 YES oledbrx05p

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2920 18-Jun-10 10:40:03 119 1.1
End Snap: 2922 18-Jun-10 11:20:10 135 1.0
Elapsed: 40.12 (mins)
DB Time: 99.35 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 7,040M 7,040M Std Block Size: 8K
Shared Pool Size: 784M 784M Log Buffer: 20,628K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 440,324.69 2,111.26
Logical reads: 6,802.05 32.61
Block changes: 2,662.07 12.76
Physical reads: 68.43 0.33
Physical writes: 62.67 0.30
User calls: 418.44 2.01
Parses: 210.28 1.01
Hard parses: 0.02 0.00
Sorts: 1.27 0.01
Logons: 0.08 0.00
Executes: 211.02 1.01
Transactions: 208.56

% Blocks changed per Read: 39.14 Recursive Call %: 36.39
Rollback per transaction %: 0.58 Rows per Sort: 70.33

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc current block busy 138,758 2,767 20 46.4 Cluster
CPU time 2,398 40.2
gc current block 2-way 1,061,320 621 1 10.4 Cluster
log file sync 499,400 577 1 9.7 Commit
gc current block 3-way 479,001 398 1 6.7 Cluster
-------------------------------------------------------------
^LRAC Statistics DB/Inst: PFPS3/PFPS31 Snaps: 2920-2922

Begin End
----- -----
Number of Instances: 3 3

Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 718.69 3.45
Global Cache blocks served: 699.50 3.35
GCS/GES messages received: 3,427.48 16.43
GCS/GES messages sent: 3,003.85 14.40
DBWR Fusion writes: 15.78 0.08
Estd Interconnect traffic (KB) 12,601.64

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Cluster 1,802,206 .0 4,280 2 3.6

SQL ordered by Executions DB/Inst: PFPS3/PFPS31 Snaps: 2920-2922
-> Total Executions: 508,003
-> Captured SQL account for 99.9% of Total

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
498,939 498,935 1.0 0.00 0.01 da17mzv9db2qa
Module: JDBC Thin Client
INSERT INTO SEARCH_RANGE_HASH_ON_ID_32 (id, phraseId, publicationChannelId, loca
tion, offset, searchTimestamp, token) VALUES(:1, :2, :3, :4, :5, :6, :7)

SQL ordered by Cluster Wait Time DB/Inst: PFPS3/PFPS31 Snaps: 2920-2922

Cluster CWT % of Elapsed CPU
Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id
------------- ---------- ----------- ----------- -------------- -------------
4,282.02 86.4 4,958.38 1,648.46 498,939 da17mzv9db2qa
Module: JDBC Thin Client
INSERT INTO SEARCH_RANGE_HASH_ON_ID_32 (id, phraseId, publicationChannelId, loca
tion, offset, searchTimestamp, token) VALUES(:1, :2, :3, :4, :5, :6, :7)

Segments by Global Cache Buffer Busy DB/Inst: PFPS3/PFPS31 Snaps: 2920-2922
-> % of Capture shows % of GC Buffer Busy for each top segment compared
-> with GC Buffer Busy for all segments captured by the Snapshot

GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP26 INDEX 595 3.66
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP23 INDEX 550 3.39
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP25 INDEX 550 3.39
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP29 INDEX 531 3.27
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP31 INDEX 524 3.23

FileName
----------------
awrrpt_1_2920_2922_PFPS3SRV.txt

FileComment
----------------------

Generic Note
------------------------
Issue Analysis
-----------------------

Test 1 AWR Snaps 2923-2926, Each of the 3 Application Servers are connected directly to specific instances

GC Buffer Busy waits have been eliminated altogether and there is very little inter-instance traffic/ interconnect traffic. Overall throughput has also increased significantly now that the table and index structures are using range/hash partitioning.

Test 2 AWR Snaps 2920-2922, All 3 Application Servers are using a load balanced connection across all 3 instances.

GC Buffer busy have also been eliminated from this test however the a new wait has appeared as the top wait:

gc current block busy 138,758 2,767 20 46.4 Cluster

There is vast difference between the 2 tests in terms of interconnect traffic seen.

Test 1

Estd Interconnect traffic (KB) 654.78

Test 2

Estd Interconnect traffic (KB) 12,601.64

Cluster waits are still an causing contention however it is now for a different reason (gc current block busy)

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc current block busy 138,758 2,767 20 46.4 Cluster

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Cluster 1,802,206 .0 4,280 2 3.6

The index structure/segments that was causing the gc buffer busy wait in previous tests are greatly reduced and the contention on the index can now been seen being better distributed across the partitions of the index.

GC
Tablespace Subobject Obj. Buffer % of
Owner Name Object Name Name Type Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP26 INDEX 595 3.66
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP23 INDEX 550 3.39
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP25 INDEX 550 3.39
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP29 INDEX 531 3.27
TEST_SEARC PFP_LARGE_ IDX_TSTAMP_HASH_ON_I N2010_SP31 INDEX 524 3.23

The way the application load is being distributed when apps servers are connected directly to an instance versus load balanced across all instances is showing that is ensuring that blocks being updated as a result of the load have better instance affinity and therefor is reducing global cache based contention and waits and interconnect traffic.

This application level partitioning of the work load is seen to be the best approach eliminating the contention in this case.

Tuesday, July 27, 2010

Upgrade Notes from 10g RAC to 11g

Upgrade Notes from 10g RAC to 11g

Here is how I went about upgrading the aforementioned, 2-node 10g RAC cluster

Here are the basic steps I followed:

Download, mount, and unzip the 11g Database and Clusterware Software on the original installation node.

I ran the Clusterware preupdate.sh script on both nodes, since I don't have a shared clusterware installation:

root@rac2 # /u03/clusterware/upgrade/preupdate.sh -crshome /u03/app/oracle/product/crs/10.2 -crsuser oracle -shutdown

The aforementioned, unlocks and stops the 10g CRS Software in preparation for installation. The new software must be installed in the same directory as the existing CRS software.

Next, install the 11g Clusterware Software:

oracle@rac1 upgrade]$ echo $ORA_CRS_HOME
/u03/app/oracle/product/crs/10.2
[oracle@rac1 upgrade]$ export DISPLAY=192.168.1.4:0.0
[oracle@rac1 upgrade]$ /u03/clusterware/runInstaller

Follow the Universal Installer prompts to install on both nodes:

Next
> Next (ORA_CRS_HOME should be already selected)
> Next (Select all applicable nodes)
> Next Product Checks
> Install (after verifying)

# When prompted run root on both nodes:

[root@rac1 ~]# /u03/app/oracle/product/crs/10.2/install/rootupgrade

Here is the output from the first node:

Checking to see if Oracle CRS stack is already up...

copying ONS config file to 11.1 CRS home
/bin/cp: `/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' and `/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' are the same file
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config was copied successfully to
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config
WARNING: directory '/u03/app/oracle/product/crs' is not owned by root
WARNING: directory '/u03/app/oracle/product' is not owned by root
WARNING: directory '/u03/app/oracle' is not owned by root
WARNING: directory '/u03/app' is not owned by root
WARNING: directory '/u03' is not owned by root
Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab
Attempting to start Oracle Clusterware stack
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Cluster Synchronization Services daemon has started
Event Manager daemon has started
Cluster Ready Services daemon has started
Oracle CRS stack is running under init(1M)
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: rac1 rac1-priv rac1
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
CRS stack on this node, is successfully
upgraded to 11.1.0.6.0
Checking the existence of nodeapps on this node
Creating '/u03/app/oracle/product/crs/10.2/install/paramfile.crs'
with data used for CRS configuration
Setting CRS configuration values in
/u03/app/oracle/product/crs/10.2/install/paramfile.crs

Here is the output from the remote node:

root@rac2 ~]# /u03/app/oracle/product/crs/10.2/install/rootupgrade
Checking to see if Oracle CRS stack is already up...

copying ONS config file to 11.1 CRS home
/bin/cp: `/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' and
`/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' are the same file
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config was copied successfully to
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config
WARNING: directory '/u03/app/oracle/product/crs' is not owned by root
WARNING: directory '/u03/app/oracle/product' is not owned by root
WARNING: directory '/u03/app/oracle' is not owned by root
WARNING: directory '/u03/app' is not owned by root
WARNING: directory '/u03' is not owned by root
Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab
Attempting to start Oracle Clusterware stack
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Cluster Synchronization Services daemon has started
Event Manager daemon has started
Cluster Ready Services daemon has started
Oracle CRS stack is running under init(1M)
clscfg: EXISTING configuration version 4 detected.
clscfg: version 4 is 11 Release 1.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 2: rac2 rac2-priv rac2
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
CRS stack on this node, is successfully
upgraded to 11.1.0.6.0
Checking the existence of nodeapps on this node
Creating '/u03/app/oracle/product/crs/10.2/install/paramfile.crs' with data used for CRS configuration
Setting CRS configuration values in /u03/app/oracle/product/crs/10.2/install/paramfile.crs

After running the root scripts press 'OK' in the pop-up window, The Installer will run the Cluster Verification Utility. Once that runs successfully, press exit.

Now install the Database Software onto both nodes, afterwards, I will upgrade the database:

First, add a new entry to the oratab for your 11g Installation:

echo "11g:/u03/app/oracle/product/db/11g:N" >> /etc/oratab
[oracle@rac1 ~]$ export ORACLE_SID=11g
[oracle@rac1 ~]$ . oraenv
[oracle@rac1 ~]$ /u03/database/runInstaller

Follow the Universal Installer prompts to install on both nodes:

Next
> Choose Edition (ostensibly, Enterprise), Next
> Specify the Name and the path of the new $ORACLE_HOME, Next
> Specify the nodes for installation, Next
> Prerequisites, Next
> Select 'No' for Upgrade Database, Next
> Choose Install Software Only, Next
> Assign O/S groups to Operations, Next
> Install

Run the root.sh script on both nodes:

[root@rac1 crs] # /u03/app/oracle/product/db/11g/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u03/app/oracle/product/db/11g

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

After running the root scripts press 'OK' in the pop-up window. The Installer is now complete, press exit.

Next, I upgrade the listeners that runs out of the 10.2.0.3 homes to the new 11g $ORACLE_HOMEs - do this on both nodes:

[oracle@rac1 10.2]$ export ORACLE_SID=cubs1
[oracle@rac1 10.2]$ . oraenv
[oracle@rac1 10.2]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ ls -lart
total 52
-rw-r--r-- 1 oracle dba 172 Dec 26 2003 shrept.lst
drwxr-x--- 2 oracle dba 4096 Sep 21 19:40 samples
drwxr-x--- 11 oracle dba 4096 Sep 21 19:44 ..
-rw-r--r-- 1 oracle dba 574 Sep 25 15:54 listener.ora
-rw-r--r-- 1 oracle dba 230 Sep 28 15:11 ldap.ora
-rw-r--r-- 1 oracle dba 16776 Sep 28 15:29 sqlnet.log
-rw-r--r-- 1 oracle dba 3786 Sep 28 16:51 tnsnames.ora
-rw-r--r-- 1 oracle dba 218 Sep 28 16:52 sqlnet.ora
drwxr-x--- 3 oracle dba 4096 Sep 28 16:52 .
[oracle@rac1 admin]$ lsnrctl LISTENER_RAC1 stop
[oracle@rac1 admin]$ mv listener.ora /u03/app/oracle/product/db/11g/network/admin/.
[oracle@rac1 admin]$ cp tnsnames.ora sqlnet.ora ldap.ora
/u03/app/oracle/product/db/11g/network/admin/.
[oracle@rac1 admin]$ ps -ef grep tns grep -v grep awk '{print $2}' xargs kill
[oracle@rac1 admin]$ export ORACLE_SID=11g
[oracle@rac1 admin]$ . oraenv
[oracle@rac1 admin]$ lsnrctl start LISTENER_RAC1

Now that the listener has been moved to the highest version $ORACLE_HOME on the machine, I am ready to upgrade a 10.2.0.3 clustered database to 11g (11.1.0.6.0).

First, I will run the Pre-Upgrade Information Tool:

[oracle@rac1 admin]$ cp
/u03/app/oracle/product/db/11g/rdbms/admin/utlu111i.sql
/tmp
[oracle@rac1 admin]$ cd /tmp
[oracle@rac1 tmp]$ export ORACLE_SID=cubs1
[oracle@rac1 tmp]$ . oraenv
[oracle@rac1 tmp]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Sep 30 17:23:09 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> spool upgrade_info.log
SQL> @/tmp/utlu111i.sql
SQL> spool off;

After examining the upgrade log, I have to perform the following tasks:

* Upgrade sga_target parameter to 336MB

* Replace the deprecated *_dest parameters with the new all inclusive one, diagnostic_dest

* Patch - via opatch - the 10.2.0.3 databases and $ORACLE_HOMEs to use Version 4 of the timezone file; this is patch number: 5632264. You can verify correct application via v$timezone_file performance view. Remember that during the one-off patch as well as the upgrade to 11g the RAC database must have cluster_database parameter set to FALSE so that the database can be mounted exclusive.

The next step is to migrate the password file and modified init.ora.

On the first node:

[oracle@rac1 tmp]$ export ORACLE_SID=11g
[oracle@rac1 tmp]$ . oraenv
[oracle@rac1 tmp]$ echo $ORACLE_HOME
/u03/app/oracle/product/db/11g
[oracle@rac1 tmp]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@rac1 tmp]$ export ORACLE_SID=cubs1
[oracle@rac1 tmp]$ echo $PATH
/u03/app/oracle/product/db/11g/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin
[oracle@rac1 tmp]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 15:09:34 2007

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

Connected to an idle instance.

SQL> create spfile='/u02/app/oradata2/spfile/spfilecubs.ora' from pfile='/tmp/initcubs.ora';

File created.

SQL> exit
Disconnected
[oracle@rac1 dbs]$ ln -s /u02/app/oradata/dbs/orapwcubs orapwcubs2
[oracle@rac1 dbs]$ ln -s /u02/app/oradata2/spfile/spfilecubs.ora spfilecubs1.ora

On the second node:

[oracle@rac2 dbs]$ export ORACLE_SID=11g
[oracle@rac2 dbs]$ . oraenv
[oracle@rac2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@rac2 dbs]$ ln -s /u02/app/oradata/dbs/orapwcubs orapwcubs2
[oracle@rac2 dbs]$ ln -s /u02/app/oradata2/spfile/spfilecubs.ora spfilecubs2.ora

Next upgrade the database by running the appropriate scripts; on the primary node:

[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 15:16:49 2007

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 351522816 bytes
Fixed Size 1299876 bytes
Variable Size 155191900 bytes
Database Buffers 188743680 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

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

Once the database has been upgraded, the database will automatically be shutdown by the previous script. Startup the database and run the post scripts that don't require exclusive access to the database and verify that there are no invalid objects:

[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 16:15:08 2007

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 351522816 bytes
Fixed Size 1299876 bytes
Variable Size 184552028 bytes
Database Buffers 159383552 bytes
Redo Buffers 6287360 bytes
SQL> alter system set cluster_database=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 351522816 bytes
Fixed Size 1299876 bytes
Variable Size 184552028 bytes
Database Buffers 159383552 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

SQL> @$ORACLE_HOME/rdbms/admin/utlu111s.sql
SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Update the oratab on both nodes and re-source the environment via oratab.

On the first node:

[oracle@rac1 dbs]$ vi /etc/oratab
[oracle@rac1 dbs]$ grep cubs1 /etc/oratab
cubs1:/u03/app/oracle/product/db/11g:N
[oracle@rac1 dbs]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac1 dbs]$ echo $PATH
/u03/app/oracle/product/db/11g/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin

On the second node:

[oracle@rac2 dbs]$ vi /etc/oratab
[oracle@rac2 dbs]$ grep cubs2 /etc/oratab
cubs2:/u03/app/oracle/product/db/11g:N
[oracle@rac2 dbs]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac2 dbs]$ echo $PATH
/u03/app/oracle/product/db/11g/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin

Upgrade the Oracle Cluster Registry (OCR) to reflect the upgraded nature of the database, including the previously migrated listeners:

[oracle@rac1 bin]$ export ORACLE_SID=10.2.0.3
[oracle@rac1 bin]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/10.2 is /u03/app/oracle
[oracle@rac1 bin]$ srvctl remove database -d cubs
Remove the database cubs? (y/[n]) y
[oracle@rac1 bin]$ export ORACLE_SID=11g
[oracle@rac1 bin]$ . oraenv
[oracle@rac1 bin]$ srvctl add database -d cubs -o /u03/app/oracle/product/db/11g
[oracle@rac1 bin]$ srvctl add instance -d cubs -i cubs1 -n rac1
[oracle@rac1 bin]$ srvctl add instance -d cubs -i cubs2 -n rac2
[oracle@rac1 bin]$ srvctl modify listener -n rac1 -l LISTENER_RAC1 -o /u03/app/oracle/product/db/11g
[oracle@rac1 bin]$ srvctl modify listener -n rac2 -l LISTENER_RAC2 -o /u03/app/oracle/product/db/11g

Next I will upgrade the compatibility of the database and cycle the database in order to verify:

[oracle@rac1 bin]$ export ORACLE_SID=cubs1
[oracle@rac1 bin]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac1 bin]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 16:51:17 2007

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

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

SQL> alter system set compatible='11.0.0' scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 bin]$ srvctl stop database -d cubs
[oracle@rac1 bin]$ srvctl start database -d cubs
[oracle@rac1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 18:03:20 2007

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

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

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.0.0

If applicable, upgrade your EM Agents to reflect the software changes (both nodes):

$ export ORACLE_SID=agent10g
[oracle@rac2 ~]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/agent10g is /u03/app/oracle
[oracle@rac2 ~]$ agentca -d

Stopping the agent using /u03/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u03/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u03/app/oracle/product/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/u03/app/oracle/product/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/etc/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform - mode is starting for action: Configure

Perform - mode finished for action: Configure

You can see the log file: /u03/app/oracle/product/agent10g/cfgtoollogs/oui/configActions2007-10-01_06-05-43-PM.log

One of my last steps is to take a backup; therefore, I will need to upgrade my RMAN catalog and/or create a new one. I have opted to create a new one, so that the other databases that I backup are not disturbed. First I create a new rman database user for my repository database:

DROP USER RMAN_11g CASCADE;
CREATE USER RMAN_11g
IDENTIFIED BY "rman"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for RMAN
GRANT RECOVERY_CATALOG_OWNER TO RMAN_11g;
ALTER USER RMAN DEFAULT ROLE ALL;
-- 1 System Privilege for RMAN
GRANT CREATE SESSION TO RMAN_11g;
-- 1 Tablespace Quota for RMAN
ALTER USER RMAN_11G QUOTA UNLIMITED ON USERS;
DROP USER RMAN_11g CASCADE
Error at line 1
ORA-01918: user 'RMAN_11G' does not exist

User created.
Grant complete.
User altered.
Grant complete.
User altered.

Now that I have an RMAN catalog owner on the repository database, it is time for me to create a new catalog, register my 11g RAC database and add back my customizations:

[oracle@rac2 ~]$ export ORACLE_SID=cubs2
[oracle@rac2 ~]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac2 ~]$ rman target=/ catalog=rman_11g/rman@rman

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Oct 2 13:39:46 2007

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

connected to target database: CUBS (DBID=2121269038)
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> @/home/oracle/bin/backup_scripts.rman

RMAN> replace script 'cubs_full_backup' {
2> sql "alter session set optimizer_mode=RULE";
3> allocate channel ch device type disk format '/u03/app/oracle/orabackup/cubs_%U.rman';
4> backup full database plus archivelog delete input;
5> }
replaced script cubs_full_backup

RMAN>
RMAN> replace script 'cleanup_catalog' {
2> sql "alter session set optimizer_mode=RULE";
3> allocate channel ch device type disk;
4> crosscheck backup;
5> delete noprompt expired backup;
6> }
replaced script cleanup_catalog

RMAN> **end-of-file**

Afterwards, I perform a backup using one of my stored scripts:

RMAN> run { execute script cubs_full_backup; }

executing script: cubs_full_backup

sql statement: alter session set optimizer_mode=RULE

allocated channel: ch
channel ch: SID=103 instance=cubs2 device type=DISK

Starting backup at 02-OCT-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=108 RECID=241 STAMP=634918386
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1qitg5fm_1_1 tag=TAG20071002T141310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oradata/cubs/arch/cubs_2_108_633910001.arc RECID=241 STAMP=634918386
Finished backup at 02-OCT-07

Starting backup at 02-OCT-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oradata/cubs/system01.dbf
input datafile file number=00003 name=/u02/app/oradata/cubs/sysaux01.dbf
input datafile file number=00002 name=/u02/app/oradata/cubs/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oradata/cubs/undotbs02.dbf
input datafile file number=00004 name=/u02/app/oradata/cubs/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1ritg5fo_1_1 tag=TAG20071002T141311 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1sitg5hf_1_1 tag=TAG20071002T141311 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-OCT-07

Starting backup at 02-OCT-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=109 RECID=242 STAMP=634918451
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1titg5hl_1_1 tag=TAG20071002T141413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oradata/cubs/arch/cubs_2_109_633910001.arc RECID=242 STAMP=634918451
Finished backup at 02-OCT-07

On my test machine, I have a lot of products installed and simply export the TNS_ADMIN variable instead of doing a lot of symbolic linking and/or network file maintenanace. In this situation, I perform the following command for any CRS resource database that I still want to run out of the old 10.2.0.3 HOME (for more information see Metalink Note 360575.1):

srvctl setenv database -d rman -t TNS_ADMIN=/u03/app/oracle/product/db/11g/network/admin

Sunday, July 25, 2010

Disaster Recovery Scenario

Disaster Recovery Scenario
I had a disaster at primary site resulting in loss of a mission critical database. I had to restore and recover the database before 08:00 am EST and I received the information around 02:00 am EST.

I spent first 10 minutes to communicate with other groups to find out a spare server to restore and recover the lost production database. The database was Oracle 10.2.0.2 on Linux with 2 node RAC cluster. The backup policy was based on RMAN directly to tapes using net backup. There was full backup on every Sunday and incremental backup during weekdays. The database size was approximately 100 GB.

I prepared a quick plan to restore and recover the database in stand lone mode (non RAC).

Server Lost: Server A: SrvA
New Server: Server B: SrvB
DB Name: ASP

Steps Taken:

1. Sent out the communication to net backup group for “alternate client request”. This request was to map the backup originating from Server A to Server B.

2. I found out the NB_ORA_CLIENT information. It was same as server name.

3. Restore the spfile and control file.

Note: It was Monday morning so RMAN had to restore Sunday’s full and then Monday's incremental backup.The database lost time was around 01:55 am EST. During the conference call with application manager we decided to stick with 01:54 am EST and as per net backup logs we had the successful archive log backup till 01:54 am EST.The restore time stamp was '2007-03-26:01:55:00'

$rman target /
rman> set dbid=3494236093 (we had a policy to store the dbid in the DBA inventory after creation of each database)
rman> startup force nomount;
rman> run{
allocate channel tape_1 type sbt
SEND 'NB_ORA_CLIENT=SrvA';
restore spfile from autobackup;
restore until time '2007-03-26:01:55:00' controlfile from autobackup;
}

Time taken: approximately 12 minutes

4. Exit from RMAN.

SQL> shutdown abort
ORACLE instance shut down.

- Copy the spfile contents in an editor and modify the following entries.
Removed the following entries:

*.cluster_database_instances
*.cluster_database
ASP2.thread=2
ASP1.thread=1
*.remote_listener=
ASP2.undo_tablespace='UNDOTBS2'
ASP1.undo_tablespace='UNDOTBS1'

Added the following:
*.undo_tablespace='UNDOTBS2'
Modified the log_Archive_dest
*.log_archive_dest='/data02/oracle/home/testitaly/archive'

Created a file in the server with name /tmp/initasp.ora

$sqlplus sys as sysdba
Sql> startup nomount pfile=’/tmp/initasp.ora’
Sql> alter database mount;

5. I used the following to restore the files. I did not get the same mount point in the new server as it was in the server A.

run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=SrvA';
set newname for datafile 1 to '/data02/home/oracle/testitaly/oradata/system01.dbf';
set newname for datafile 2 to '/data02/home/oracle/testitaly/oradata/undotbs01.dbf';
set newname for datafile 3 to '/data02/home/oracle/testitaly/oradata/sysaux01.dbf';
set newname for datafile 4 to '/data02/home/oracle/testitaly/oradata/undotbs02.dbf';
set newname for datafile 5 to '/data02/home/oracle/testitaly/oradata/users01.dbf';
set newname for datafile 6 to '/data02/home/oracle/testitaly/oradata/s_ts_fr_data01.dbf';
set newname for datafile 7 to '/data02/home/oracle/testitaly/oradata/s_ts_fr_indx01.dbf';
set newname for datafile 8 to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_data01.dbf';
set newname for datafile 9 to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_indx01.dbf';
set newname for datafile 10 to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_data01.dbf';
set newname for datafile 11 to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_indx01.dbf';
set newname for datafile 12 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data01.dbf';
set newname for datafile 13 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx01.dbf';
set newname for datafile 14 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data01.dbf';
set newname for datafile 15 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx01.dbf';
set newname for datafile 16 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data02.dbf';
set newname for datafile 17 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data02.dbf';
set newname for datafile 18 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data03.dbf';
set newname for datafile 19 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data03.dbf';
set newname for datafile 20 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx02.dbf';
set newname for datafile 21 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx02.dbf';
set newname for datafile 22 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx03.dbf';
set newname for datafile 23 to '/data02/home/oracle/testitaly/oradata/s_ts_nr_indx01.dbf';
set newname for datafile 24 to '/data02/home/oracle/testitaly/oradata/s_ts_nr_data01.dbf';
set newname for datafile 25 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx03.dbf';
set newname for datafile 26 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx04.dbf';
restore database until time '2007-03-26:01:55:00';
}

Output:
allocated channel: ch00
channel ch00: sid=102 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: ch01
channel ch01: sid=98 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

sent command to channel: ch00
sent command to channel: ch01

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

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

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 2007-03-26:02:30:29

channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /data02/home/oracle/testitaly/oradata/undotbs02.dbf
restoring datafile 00013 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx01.dbf
restoring datafile 00014 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_data01.dbf
restoring datafile 00015 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx01.dbf
restoring datafile 00019 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_data03.dbf
channel ch00: reading from backup piece ASP_bk_6247_1_618128164
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /data02/home/oracle/testitaly/oradata/sysaux01.dbf
restoring datafile 00009 to /data02/home/oracle/testitaly/oradata/s_ts_fmxr_indx01.dbf
restoring datafile 00018 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_data03.dbf
restoring datafile 00023 to /data02/home/oracle/testitaly/oradata/s_ts_nr_indx01.dbf
restoring datafile 00026 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx04.dbf
channel ch01: reading from backup piece ASP_bk_6250_1_618130329
channel ch01: restored backup piece 1
piece handle=ASP_bk_6250_1_618130329 tag=ASP_HOT_DB_BK_LEVEL0
channel ch01: restore complete, elapsed time: 00:16:46
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /data02/home/oracle/testitaly/oradata/undotbs01.dbf
restoring datafile 00005 to /data02/home/oracle/testitaly/oradata/users01.dbf
restoring datafile 00006 to /data02/home/oracle/testitaly/oradata/s_ts_icfr_data01.dbf
restoring datafile 00017 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_data02.dbf
restoring datafile 00025 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx03.dbf
channel ch01: reading from backup piece ASP_bk_6249_1_618130143
channel ch00: restored backup piece 1
piece handle=ASP_bk_6247_1_618128164 tag=ASP_HOT_DB_BK_LEVEL0
channel ch00: restore complete, elapsed time: 00:47:03
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00012 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_data01.dbf
restoring datafile 00016 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_data02.dbf
restoring datafile 00020 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx02.dbf
restoring datafile 00021 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx02.dbf
restoring datafile 00024 to /data02/home/oracle/testitaly/oradata/s_ts_nr_data01.dbf
channel ch00: reading from backup piece ASP_bk_6248_1_618128165
channel ch01: restored backup piece 1
piece handle=ASP_bk_6249_1_618130143 tag=ASP_HOT_DB_BK_LEVEL0
channel ch01: restore complete, elapsed time: 00:35:23
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data02/home/oracle/testitaly/oradata/system01.dbf
restoring datafile 00008 to /data02/home/oracle/testitaly/oradata/s_ts_fmxr_data01.dbf
restoring datafile 00010 to /data02/home/oracle/testitaly/oradata/s_ts_fifa_data01.dbf
restoring datafile 00011 to /data02/home/oracle/testitaly/oradata/s_ts_fifa_indx01.dbf
restoring datafile 00022 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx03.dbf
channel ch01: reading from backup piece ASP_bk_6251_1_618131406
channel ch01: restored backup piece 1
piece handle=ASP_bk_6251_1_618131406 tag=ASP_HOT_DB_BK_LEVEL0
channel ch01: restore complete, elapsed time: 00:04:15
channel ch00: restored backup piece 1
piece handle=ASP_bk_6248_1_618128165 tag=ASP_HOT_DB_BK_LEVEL0
channel ch00: restore complete, elapsed time: 00:40:18
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00007 to /data02/home/oracle/testitaly/oradata/s_ts_icfr_indx01.dbf
channel ch00: reading from backup piece ASP_bk_6252_1_618131902
channel ch00: restored backup piece 1
piece handle=ASP_bk_6252_1_618131902 tag=ASP_HOT_DB_BK_LEVEL0
channel ch00: restore complete, elapsed time: 00:02:15
Finished restore
released channel: ch00
released channel: ch01

RMAN>
Time taken: 3 hours approximately

6. I executed the following after fetching the records from v$logfile.

alter database rename file '/oracle03/oradata/ASP/redo_g1_t1_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g1_t1_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g1_t1_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g1_t1_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g2_t1_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g2_t1_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g2_t1_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g2_t1_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g3_t1_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g3_t1_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g3_t1_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g3_t1_m2.dbf' ;
alter database rename file '/oracle03/oradata/ASP/redo_g4_t2_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g4_t2_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g4_t2_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g4_t2_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g5_t2_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g5_t2_m1.dbf' ;
alter database rename file '/oracle04/oradata/ASP/redo_g5_t2_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g5_t2_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g6_t2_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g6_t2_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g6_t2_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g6_t2_m2.dbf';

I verified the changes from the v$logfile. The above statements updated the controflile.

7. Login to database and executed the v$datafile to see the controlfile information. It was still showing old mount point information.

Sqlplus> select * from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /oracle11/oradata/ASP/system01.dbf
2 /oracle11/oradata/ASP/undotbs01.dbf
3 /oracle11/oradata/ASP/sysaux01.dbf
4 /oracle11/oradata/ASP/undotbs02.dbf
5 /oracle11/oradata/ASP/users01.dbf
6 /oracle11/oradata/ASP/s_ts_icfr_data01.dbf
7 /oracle12/oradata/ASP/s_ts_icfr_indx01.dbf
8 /oracle07/oradata/ASP/s_ts_fmxr_data01.dbf
9 /oracle09/oradata/ASP/s_ts_fmxr_indx01.dbf
10 /oracle08/oradata/ASP/s_ts_fifa_data01.dbf
11 /oracle10/oradata/ASP/s_ts_fifa_indx01.dbf
12 /oracle11/oradata/ASP/s_ts_gmcp_data01.dbf
13 /oracle12/oradata/ASP/s_ts_gmcp_indx01.dbf
14 /oracle08/oradata/ASP/s_ts_gmfr_data01.dbf
15 /oracle10/oradata/ASP/s_ts_gmfr_indx01.dbf
16 /oracle08/oradata/ASP/s_ts_gmcp_data02.dbf
17 /oracle08/oradata/ASP/s_ts_gmfr_data02.dbf
18 /oracle08/oradata/ASP/s_ts_gmfr_data03.dbf
19 /oracle07/oradata/ASP/s_ts_gmcp_data03.dbf
20 /oracle12/oradata/ASP/s_ts_gmcp_indx02.dbf
21 /oracle10/oradata/ASP/s_ts_gmfr_indx02.dbf
22 /oracle10/oradata/ASP/s_ts_gmfr_indx03.dbf
23 /oracle09/oradata/ASP/s_ts_nr_indx01.dbf
24 /oracle07/oradata/ASP/s_ts_nr_data01.dbf
25 /oracle10/oradata/ASP/s_ts_gmcp_indx03.dbf
26 /oracle09/oradata/ASP/s_ts_gmcp_indx04.dbf
27 /oracle08/oradata/ASP/s_ts_burn_data01.dbf
28 /oracle09/oradata/ASP/s_ts_burn_indx01.dbf

8. I executed the following:

$rman target /
$ run {
Switch datafile all;
}

Unfortunately it was little late (I did not see the desire output from v$datafile as it was still showing the same output as above)

Without wasting anytime I ran:

alter database rename file ‘/’ to '/data02/home/oracle/testitaly/oradata/ ';

alter database rename file '/oracle11/oradata/ASP/system01.dbf' to '/data02/home/oracle/testitaly/oradata/system01.dbf';
alter database rename file '/oracle11/oradata/ASP/undotbs01.dbf' to '/data02/home/oracle/testitaly/oradata/undotbs01.dbf';
alter database rename file '/oracle11/oradata/ASP/sysaux01.dbf' to '/data02/home/oracle/testitaly/oradata/sysaux01.dbf';
alter database rename file '/oracle11/oradata/ASP/undotbs02.dbf' to '/data02/home/oracle/testitaly/oradata/undotbs02.dbf';
alter database rename file '/oracle11/oradata/ASP/users01.dbf' to '/data02/home/oracle/testitaly/oradata/users01.dbf';
alter database rename file '/oracle11/oradata/ASP/s_ts_icfr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_icfr_data01.dbf';
alter database rename file '/oracle12/oradata/ASP/s_ts_icfr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_icfr_indx01.dbf';
alter database rename file '/oracle07/oradata/ASP/s_ts_fmxr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_data01.dbf';
alter database rename file '/oracle09/oradata/ASP/s_ts_fmxr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_indx01.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_fifa_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_data01.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_fifa_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_indx01.dbf';
alter database rename file '/oracle11/oradata/ASP/s_ts_gmcp_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data01.dbf';
alter database rename file '/oracle12/oradata/ASP/s_ts_gmcp_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx01.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmfr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data01.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmfr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx01.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmcp_data02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data02.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmfr_data02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data02.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmfr_data03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data03.dbf';
alter database rename file '/oracle07/oradata/ASP/s_ts_gmcp_data03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data03.dbf';
alter database rename file '/oracle12/oradata/ASP/s_ts_gmcp_indx02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx02.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmfr_indx02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx02.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmfr_indx03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx03.dbf';
alter database rename file '/oracle09/oradata/ASP/s_ts_nr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_nr_indx01.dbf';
alter database rename file '/oracle07/oradata/ASP/s_ts_nr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_nr_data01.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmcp_indx03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx03.dbf';
alter database rename file '/oracle09/oradata/ASP/s_ts_gmcp_indx04.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx04.dbf';

9. Recover the database.

$rman target /
$run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=SrvA';
recover database until time '2007-03-26:01:55:00';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

Time to recover: 1 hour approximately (RMAN applied the full and incremental changes)

I was doing tail –f alert_ASP.log to see the progress

10. Open the database
sql> alter database open resetlogs;

11. Added the temporary files.

12. Verified the following details.

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM

SQL> select * from v$recover_file;

no rows selected