UAT DB Migration using RMAN Production backup.

ABA Bank

July 2023
 Document Control

Author/s : Vijay Group: TDMS FLEXCUBE

Consulting

Created on: 18/Jul/2023 Revision: INITIAL

Updated by: Vijay Reviewed by: Rattan Approved by:

Updated on: 21/Jul/2023 Reviewed on: 18/Jul/2023 Approved on:

Build UAT database from RMAN backup:

Source:

ABA Production database.

Target:

IP: 10.6.10.4, 10.6.10.5

Hostname: exauatdbadm01,exauatdbadm02

Instance: FCXUAT1,FCXUAT2

1. Step 1: Create a pfile with the below parameters.:

vi inituatdb.ora
*.audit_file_dest='/u01/app/oracle/admin/fcxuat/adump'

*.audit_trail='db'

*.cluster_database=FALSE

*.control_files='+DATA'

*.compatible='12.1.0.2.0'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_files=1024

*.db_name='FCXUAT'

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_dest='+RECO'

*.log_archive_format='%t_%s_%r.dbf'

*.log_buffer=134217728

*.os_authent_prefix=''

*.parallel_adaptive_multi_user=FALSE

*.pga_aggregate_target=61384m

*.processes=1024

*.remote_login_passwordfile='exclusive'

*.sga_target=244576m
 *.use_large_pages='ONLY'

Step 2: Create the required directories for starting the database in nomount by
using the pfile.
mkdir -p /u01/app/oracle/admin/fcxuat/adump

Step 3: Start the database in nomount using pfile.

SQL>startup nomount pfile=’/home/oracle/inituatdb.ora’

Step 4: Create a shell script by using the below details for cloning the database
from RMAN backup.
Please change the dbnames as per your request. FCXUAT used for UATDB.

vi rman_duplicate.sh
ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1/

BACKUP_LOG_PATH=/home/oracle/duplicate

export ORACLE_SID=FCXUAT

export BACKUP_LOG_PATH

LOG_FILE=${BACKUP_LOG_PATH}/RESTORE_FCXUAT_05JUL2023.log

$ORACLE_HOME/bin/rman AUXILIARY / msglog=${LOG_FILE}<< EOF

set decryption identified by ‘*********’;

run {

allocate auxiliary channel a1 type disk;

allocate auxiliary channel a2 type disk;

allocate auxiliary channel a3 type disk;

allocate auxiliary channel a4 type disk;

allocate auxiliary channel a5 type disk;

allocate auxiliary channel a6 type disk;

allocate auxiliary channel a7 type disk;

allocate auxiliary channel a8 type disk;

duplicate target database to FCXUAT

spfile

parameter_value_convert ('FCLIVE','FCXUAT')

set db_name='FCXUAT'

set db_unique_name='FCXUAT'

set db_create_file_dest='+DATA'
 set db_recovery_file_dest='+RECO'

set sga_max_size='440G'

set sga_target='440G'

set pga_aggregate_limit='180G'

set pga_aggregate_target='80G'

set control_files='+DATA'

set log_archive_dest_1=''

set db_file_name_convert='/oradata/FCLIVE/','+DATA/FCXUAT/','/oradata/','+DATA/FCXUAT/','/oratemp/
FCCLIVE/','+DATA/FCXUAT/'

set log_file_name_convert='/redo1/FCLIVE/','+REDO1/FCXUAT/','/redo2/FCLIVE/','+REDO2/FCXUAT/'

BACKUP LOCATION '/local_backup/FCCLIVE_20230625_183001/'

nofilenamecheck;

exit;

EOF

chmod +x rman_duplicate.sh

Step 5: Start the restore activity by using the duplicate script in nohup mode.

nohup sh /home/oracle/duplicate/rman_duplicate.sh &

Step 6: Once restore gets complete, create pfile from spfile and add the below
lines to pfile.

SQL> create pfile from spfile;

*.cluster_database_instances=2

*.cluster_database=true

*.remote_listener='exauat-scan1:1521'

FCXUAT1.instance_number=1

FCXUAT2.instance_number=2

FCXUAT1.thread=1

FCXUAT2.thread=2

FCXUAT1.undo_tablespace='UNDOTBS'

FCXUAT2.undo_tablespace='UNDOTBS2'

Step 7: Rename the pfile to instance name and copy another pfile for node 2.
 cd $ORACLE_HOME/dbs

cp initFCXUAT.ora initFCXUAT1.ora

cp initFCXUAT1.ora initFCXUAT2.ora

mv initFCXUAT2.ora oracle@ exauatdbadm02:$ORACLE_HOME/dbs

Step 8: Transfer the new pfile to node2 and start the instance in the other node.

mv initFCXUAT2.ora oracle@ exauatdbadm02:$ORACLE_HOME/dbs

export ORACLE_SID=FCXUAT2

SQL> startup

Step 9: Run the below commands to add the database to server control utility.

srvctl add database -d FCXUAT -o /u01/app/oracle/product/12.1.0.2/dbhome_1/

srvctl add instance -d FCXUAT -i FCXUAT1 -n exauatdbadm01

srvctl add instance -d FCXUAT -i FCXUAT2 -n exauatdbadm02

Step 10: Create spfile to ASM and update the same path to pfile in both the
nodes.
SQL> create spfile=’+DATA/FCXUAT/PARAMETERFILE/spfilefcxuat.ora’ from pfile;

cd $ORACLE_HOME/dbs

mv initFCXUAT1.ora initFCXUAT1.ora_bkp

vi initFCXUAT1.ora

spfile=’+DATA/FCXUAT/PARAMETERFILE/spfilefcxuat.ora’

Do the same in node2 as well.

Step 11: Verify the status of the database using srvctl command.

srvctl status database -d fcxuat

srvctl stop database -d fcxuat

 srvctl start database -d fcxuat

Step 12: Change the FC objects PLSQL_CODE_TYPE to NATIVE using the below
command.
Select PLSQL_OPTIMIZE_LEVEL,type,plsql_code_type,count(*) "Count" from
dba_plsql_object_settings where owner='FCCHOST' group by
PLSQL_OPTIMIZE_LEVEL,type,plsql_code_type;

exec
dbms_utility.compile_schema(schema=>'FCCHOST',compile_all=>true,REUSE_SETTINGS=>fals
e);

Step 13: Run the warmup scripts from the below attachment.

Step 14: Please set the below parameters in the DB and

match them to FC recommended.

Parameter FC Recommended
DB_WRITER_PROCESSES 16
CURSOR_SHARING Force
DB_CACHE_ADVICE OFF (Should be ON while Performance Monitoring)
FAST_START_MTTR_TARGET 300
FILESYSTEM_IO_OPTIONS SETALL
JOB_QUEUE_PROCESSES 1000
NLS_DATE_FORMAT DD-MON-RRRR
OPEN_CURSORS 5000
OPTIMIZER_DYNAMIC_SAMPLING 2
OPTIMIZER_INDEX_CACHING 90
OPTIMIZER_INDEX_COST_ADJ 50
PLSQL_CODE_TYPE NATIVE
PLSQL_OPTIMIZE_LEVEL 2
PROCESSES 5500
QUERY_REWRITE_ENABLED FALSE
REMOTE_DEPENDENCIES_MODE SIGNATURE
RESULT_CACHE_MAX_SIZE 0.5% of SGA
CLIENT_RESULT_CACHE LAG 10,800,000(3 hours)
CLIENT_RESULT_CACHE_SIZE 32K
SESSION_CACHED_CURSORS 400
SKIP_UNUSABLE_INDEXES FALSE
UNDO_RETENTION 1800
UTL_FILE_DIR *
LOG_BUFFER Recommended Value: 100M
_ALLOW_LEVEL_WITHOUT_CONNECT_BY TRUE
PGA_AGGREGATE_LIMIT 0
 optimizer_adaptive_features FALSE
SGA_TARGET 600G
SGA_MAX_SIZE 600G
PGA_AGGREGATE_TARGET
_lm_drm_disable 0
shared_pool_size 30G
db_cache_size 90G
gcs_server_processes 8
parallel_max_servers 1600
parallel_min_servers 160
parallel_servers_target 1600
vm.nr_hugepages(sysctl.conf) 320000
db_securefile PREFERRED

Step 15: Verify if any indexes are in UNUSABLE status and take

necessary action.

Step 16: Please change the sequence cache size 20 of all the

FC schema sequences except the sequences like below.

TRSQ_103_PROCESS

TRSQ_104_PROCESS

TRSQ_105_PROCESS

TRSQ_106_PROCESS

Step 17: Disable logon trigger for DIFACE.

Step 18: Change ACTB_DAILY_LOG PK Index from Reverse to HASH

with 256 partitions.

1) ALTER INDEX PK01_ACTB_DAILY_LOG RENAME TO PK01_ACTB_DAILY_LOG_OLD;

2) ALTER INDEX PK01_ACTB_DAILY_LOG_OLD INVISIBLE;

3) CREATE UNIQUE INDEX PK01_ACTB_DAILY_LOG ON ACTB_DAILY_LOG (AC_ENTRY_SR_NO)

GLOBAL PARTITION BY HASH (AC_ENTRY_SR_NO) PARTITIONS 256 INITRANS 20 MAXTRANS
255 COMPUTE STATISTICS STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE FCCINDXMED
ONLINE;

4) ALTER TABLE ACTB_DAILY_LOG MODIFY CONSTRAINT PK01_ACTB_DAILY_LOG USING

INDEX PK01_ACTB_DAILY_LOG;

Step 19: Apply the below patches to both the instances.

Step 20: Make all the datafiles autoextend to 1G.
ALTER DATABASE DATAFILE '+DATA/FCXUAT/fcbpelprod_ums.dbf' AUTOEXTEND ON NEXT
1G;

Step 21: Gather stats for FC schema and Fixed objects using

the below command.

EXEC DBMS_STATS.gather_schema_stats(OWNNAME=>'FCCHOST',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,degree => 48,
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1', cascade =>
TRUE ,GRANULARITY => 'GLOBAL AND PARTITION');

exec dbms_stats.gather_dictionary_stats;

exec dbms_stats.gather_fixed_objects_stats;

exec dbms_stats.gather_system_stats('EXADATA');