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');
