Oracle Database Migration Guide (Cross Platform Migration) Part 1
22 min readDec 27, 2021
1. Overview
This document is the part of DB Cross Platform Migration series.
And in this technical document I will explain the preparation of the target database with Oracle 11.2.0.4 DB installation and configuration with ASM on Oracle Linux Cloud environment. This document will include the following topics with screenshots.
· Product prerequisite for installation.
· Oracle Grid and 11g R2 installation procedure.
· Oracle 11.2.0.4 DB configuration procedure.
2. Introduction
2.1 Purpose
Detailed steps to install and configure Oracle Database as per the best Practices and standards.
2.2 Assumptions
Source server: AIX (OS )
Server name — myhostonprem
Database version : 11.2.0.4
Target Server : OEL 6.9 (OS)
Server name — myhostoci
Database version : 11.2.0.42.3 Technical Infrastructure Topology
· Single instance with ASM storage
· Oracle Database & ASM 11.2.0.4 version
· One Disk group (+POC_MYDISKGRP)
· Consists PSU PATCHES as source server
2.4 Directory Structure
Below are the directory structure created on WMS DB server.
· /wmsdb· /wmsdb/oracle/product (Oracle base)· /wmsdb/oracle/product/11.2.0.4/db/ (oracle home)· /rmsrdbms/oracle/product/11.2.0.4/grid (grid home)· +POC_MYDISKGRP (Diskgroups name)· + POC_MYDISKGRP/POCDB/DATAFILE (datafile location)· + POC_MYDISKGRP/POCDB/ONLINELOG (redologs location)· + POC_MYDISKGRP/POCDB/CONTROLFILE (control file location)· + POC_MYDISKGRP/POCDB/TEMPFILE (temp file location)
3. Product Prerequisites for Installation
3.1 Creating OS users and Groups:
Create oinstall, dba, retails OS groups and assign this groups to oracle, grid, oretail, appworx users.
[root@myhostoci ~]# id appworx
uid=4005(appworx) gid=5003(retail) groups=5003(retail)[root@myhostoci ~]# id oracle
uid=4002(oracle) gid=5001(oinstall) groups=5001(oinstall),5002(dba),5003(retail),5004(staff)[root@myhostoci ~]# id oretail
uid=4003(oretail) gid=5003(retail) groups=5003(retail)[root@myhostoci ~]# id appworx
uid=4005(appworx) gid=5003(retail) groups=5003(retail)
3.2 Granting Cronjob permission:
Provide cornjob permission to the users to create any crontab entry.
3.3 CPU count/Physical Memory/Swap Size Details:
Verify Target DB allocated CPU count, Memory and Swap size details.
[root@myhostoci ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0–3
Thread(s) per core: 2
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Platinum 8167M CPU @ 2.00GHz
Stepping: 4
CPU MHz: 1995.240
BogoMIPS: 3990.61
Virtualization: VT-x
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 4096K
L3 cache: 16384K
NUMA node0 CPU(s): 0–3
[root@myhostoci ~]# free -m
total used free shared buffers cached
Mem: 29897 28753 1143 8203 398 26828
-/+ buffers/cache: 1526 28370
Swap: 8191 123 80683.4 Ensure OS Users password never expires:
Ensure that the OS Users password never expires and never locks down on invalid login attempts.
[root@myhostoci ~]# chage — list oracle
Last password change : Feb 11, 2019
Password expires : never
Password inactive : never
Account expires : never
Minimum number of days between password change : 0
Maximum number of days between password change : 99999
Number of days of warning before password expires : 7[root@myhostoci ~]# chage — list oretailLast password change : Dec 27, 2018
Password expires : never
Password inactive : never
Account expires : never
Minimum number of days between password change : 0
Maximum number of days between password change : 99999
Number of days of warning before password expires : 7[root@myhostoci ~]# chage — list gridLast password change : Dec 27, 2018
Password expires : never
Password inactive : never
Account expires : never
Minimum number of days between password change : 0
Maximum number of days between password change : 99999
Number of days of warning before password expires : 7
3.5 Set OS kernel Parameters :
Set OS kernel parameters in /etc/sysctl.conf file on Linux Machine.
[root@myhostoci ~]# cat /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
#
# Use '/sbin/sysctl -a' to list all possible parameters.
# Controls IP packet forwarding
net.ipv4.ip_forward=0
# Controls source route verification
# Do not accept source routing
net.ipv4.conf.default.accept_source_route=0
# Controls the System Request debugging functionality of the kernel
kernel.sysrq=0
# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid=1
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies=1
# Controls the default maxmimum size of a mesage queue
kernel.msgmnb=65536
# Controls the maximum size of a message, in bytes
kernel.msgmax=65536
# Controls the maximum shared segment size, in bytes
# Controls the maximum number of shared memory segments, in pages
# The following lines were added per Accenture Security
net.ipv4.conf.default.secure_redirects=0
net.ipv4.conf.all.secure_redirects=0
net.ipv4.icmp_echo_ignore_broadcasts=1
net.ipv4.conf.all.accept_redirects=0
net.ipv4.conf.default.accept_redirects=0
net.ipv4.tcp_max_syn_backlog=4096
net.ipv4.conf.all.accept_source_route=0
net.ipv4.conf.all.send_redirects=0
net.ipv4.conf.default.send_redirects=0
net.ipv4.icmp_ignore_bogus_error_responses=1
#Addded as per the best practices
net.ipv6.conf.all.disable_ipv6=1
net.ipv6.conf.default.disable_ipv6=1
net.ipv6.conf.lo.disable_ipv6=1
# oracle-rdbms-server-11gR2-preinstall setting for fs.file-max is 6815744
fs.file-max=6815744
# oracle-rdbms-server-11gR2-preinstall setting for kernel.sem is '250 32000 100 128'
kernel.sem=250 32000 100 128
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmni is 4096
kernel.shmmni=4096
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86_64 and 2097152 on i386
kernel.shmall=4294967296
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64 and 4294967295 on i386
kernel.shmmax=4398046511104
# oracle-rdbms-server-11gR2-preinstall setting for kernel.panic_on_oops is 1
kernel.panic_on_oops=1
# oracle-rdbms-server-11gR2-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default=262144
# oracle-rdbms-server-11gR2-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max=4194304
# oracle-rdbms-server-11gR2-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default=262144
# oracle-rdbms-server-11gR2-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max=1048576
# oracle-rdbms-server-11gR2-preinstall setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter=1
# oracle-rdbms-server-11gR2-preinstall setting for net.ipv4.conf.default.rp_filter is 2
net.ipv4.conf.default.rp_filter=1
# oracle-rdbms-server-11gR2-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr=1048576
# oracle-rdbms-server-11gR2-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range=9000 65500
fs.suid_dumpable=0
kernel.exec-shield=1
kernel.randomize_va_space=2
net.ipv4.conf.all.log_martians=1
net.ipv4.conf.default.log_martians=1
net.ipv6.conf.all.accept_ra=0
net.ipv6.conf.default.accept_ra=0
net.ipv6.conf.all.accept_redirects=0
net.ipv6.conf.default.accept_redirects=03.6 Set UDP and TCP Kernel Parameters:
Add UDP and TCO network kernel parameters in /etc/sysctl.conf file as per the standards.
[root@myhostoci ~]# cat /etc/sysctl.conf | grep ip_local_port_range# oracle-rdbms-server-11gR2-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500net.ipv4.ip_local_port_range=9000 65500
3.7 Disable Transparent Hugepages:
Below showing enable transparent hugepages value as “never”.
[root@myhostoci ~]# cat /sys/kernel/mm/transparent_hugepage/enabledalways madvise [never]
3.8 Binary Filesystem Type Details:
Showing file sytems name and type as per our standards.
[root@myhostoci ~]# grep ^[^#] /etc/fstabUUID=466d5bab-d28f-4d03-b333-c9e640c60004 / ext4 defaults,_netdev,_rnetdev 1 1UUID=663A-F813 /boot/efi vfat defaults,uid=0,gid=0,umask=0077,shortname=winnt 0 0UUID=912605c2–0a0b-4dc1-a761-ea15dd2a5cd5 swap swap defaults 0 0none /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
UUID=d6747b42–0821–4c64–8b22–37f66f712ec8 /wmsdb ext4 auto,defaults,_netdev,_rnetdev 1 1
3.9 Installed RPM’s details on DB VM server:
Install all the Oracle and ASM related RPM’s to configure ASM and Oracle DB as per Oracle Standard.
[root@myhostoci ~]# rpm -qa | grep oracle
oracle-rdbms-server-11gR2-preinstall-1.0–14.el6.x86_64
oracle-logos-60.0.14–1.0.5.el6.noarch
oracleasm-support-2.1.11–2.el6.x86_64
kmod-oracleasm-2.0.8–15.el6_9.x86_64
oracleasmlib-2.0.12–1.el6.x86_64
oraclelinux-release-notes-6Server-21.el6.x86_64
oraclelinux-release-6Server-9.0.3.x86_643.10 Adding ASM storage to the OS VM:
Create disk partition from allocated disk on server level and add it to Diskgroup.
[root@myhostoci ~]# fdisk -l /dev/sd*
WARNING: GPT (GUID Partition Table) detected on '/dev/sda'! The util fdisk doesn't support GPT. Use GNU Parted.
Disk /dev/sda: 161.1 GB, 161061273600 bytes
256 heads, 63 sectors/track, 19504 cylinders
Units = cylinders of 16128 * 512 = 8257536 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0x00000000
Device Boot Start End Blocks Id System
/dev/sda1 1 19505 157286399+ ee GPT
Partition 1 does not start on physical sector boundary.
Disk /dev/sda1: 536 MB, 536870912 bytes
255 heads, 63 sectors/track, 65 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0x00000000
Device Boot Start End Blocks Id System
Disk /dev/sda2: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0x00000000
Disk /dev/sda3: 151.9 GB, 151933402624 bytes
255 heads, 63 sectors/track, 18471 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0x00000000
Disk /dev/sdb: 1099.5 GB, 1099511627776 bytes
255 heads, 63 sectors/track, 133674 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0xc659e90c
Device Boot Start End Blocks Id System
/dev/sdb1 1 133675 1073740800 83 Linux
Disk /dev/sdb1: 1099.5 GB, 1099510579200 bytes
255 heads, 63 sectors/track, 133674 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0x00000000
Disk /dev/sdc: 751.6 GB, 751619276800 bytes
255 heads, 63 sectors/track, 91379 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disk identifier: 0x314d3203
Device Boot Start End Blocks Id System
/dev/sdc1 1 91379 734001786 83 Linux
Partition 1 does not start on physical sector boundary.
Disk /dev/sdc1: 751.6 GB, 751617828864 bytes
255 heads, 63 sectors/track, 91378 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Alignment offset: 512 bytes
Disk identifier: 0x000000003.11 Copy $HOME of all OS users to cloud:
Copy the contents of $HOME from source server and place it in target DB server.
3.12 Check Database Alias Details:
Check and verify DB Alias details in /etc/hosts file.
[opc@myhostoci ~]$ cat /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#DB Server
10.180.66.43 myhostoci.com.local myhostoci#RIB Server
10.180.66.44 myhostocirib.com.local myhostocirib#WMS APP Server
10.180.66.46 myhostociwmsapp.com.local myhostociwmsapp
4. ORACLE GRID AND 11GR2 DB Product Installation Procedure
4.1 Verify ASM RPM’s in the OS server:
Verify if all the ASM related rpm’s installed before configure ASM.
[opc@myhostoci ~]$ rpm -qa | grep oracleasmoracleasm-support-2.1.11–2.el6.x86_64
kmod-oracleasm-2.0.8–15.el6_9.x86_64
oracleasmlib-2.0.12–1.el6.x86_64
4.2 Configure the ASM kernel module:
Once rpm’s installed , configure Oracle ASM libaray driver as root user.
root@myhostoci ~]# /etc/init.d/oracleasm configure -iConfiguring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets (‘[]’). Hitting <ENTER> without typing an answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: grid
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module “oracleasm”: [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]4.3 Initialize the ASMLib Driver:
Once ASM library configuration done, start the driver as root user.
Example:
4.4 Creating ASM Disks and change it to grid owner:
Below example screenshot is showing create disk partition.
4.5 Check the list of newly created ASM Disks:
Once ASM disks are created at OS level , verify whether the newly created disks available on server level.
[oracle@myhostoci ~]$ oracleasm listdisksDATA_PRY
4.6 Create ORACLE_HOME, GRID_HOME paths:
Create Oracle Home and Gird Home directories to install binaries
mkdir –p /wmsdb/product/oracle/11.2.0.4/dbmkdir –p /wmsdb/product/oracle/11.2.0.4/grid
Database Oracle home to be owned by “oracle” user and Grid home to be owned by “grid” user.
4.7 Check the DB File system Layout and Permissions:
Before installing Oracle binaries, crosscheck the file system layout and respective directories permissions.
[oracle@myhostoci 11.2.0.4]$ ls -ltrtotal 14605680drwxr-x — -+ 72 grid oinstall 4096 Jan 30 08:31 grid
drwxr-xr-x 79 oracle oinstall 4096 Feb 6 17:05 db
4.8 Unzip the GRID software and install ASM instance as GRID user:
Once all pre-requsites completed, unzip the grid 11.2.0.4 software and run the ./runInstaller as grid user. Given below are the screen shots of ASM instance installation on Linux Cloud Machine.
[oracle@myhostoci grid_old]$ pwd
/wmsdb/stage/DB/grid_old[oracle@myhostoci grid_old]$ ls -ltr
total 68
drwxr-xr-x. 2 grid oinstall 4096 Aug 26 2013 sshsetup
-rwxr-xr-x. 1 grid oinstall 3268 Aug 26 2013 runInstaller
-rwxr-xr-x. 1 grid oinstall 4878 Aug 26 2013 runcluvfy.sh
drwxr-xr-x. 2 grid oinstall 4096 Aug 26 2013 rpm
drwxr-xr-x. 2 grid oinstall 4096 Aug 26 2013 response
drwxr-xr-x. 4 grid oinstall 4096 Aug 26 2013 install
drwxr-xr-x. 14 grid oinstall 4096 Aug 26 2013 stage
-rw-r — r — . 1 grid oinstall 30016 Aug 27 2013 readme.html
-rw-r — r — . 1 grid oinstall 500 Aug 27 2013 welcome.html
4.9 Post installation checks:
After ASM instance created, perform the post installation checks in ASM level as given below.
[grid@myhostoci ~]$ cd $ORACLE_HOME[grid@myhostoci grid]$ lsassistants clone cv eons hs jdk md ohasd oraInst.loc perl relnotes srvm xdk
auth crf dbs evm install jlib mdns ologgerd ord plsql root.sh suptools
bin crs dc_ocm gipc instantclient JRE mesg OPatch osysmond precomp rootupgrade.sh sysman
ccr csmig deinstall gnsd inventory ldap network OPatch_old oui psu scheduler usm
cdata css demo gpnp javavm lib nls opmn owm racg slax utl
cfgtoollogs ctss diagnostics has jdbc log oc4j oracore patch rdbms sqlplus wwg[grid@myhostoci grid]$ ps -ef | grep pmon
grid 10305 10283 0 11:53 pts/0 00:00:00 grep pmon
grid 26384 1 0 Jan30 ? 00:02:22 asm_pmon_+ASM[grid@myhostoci grid]$[grid@myhostoci grid]$ asmcmdASMCMD> lsdgState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED EXTERN N 4096 4096 4194304 1048572 22648 0 22648 0 N POC_MYDISKGRP/
4.10 Install required RDBMS rpm packages:
Given below screenshot is showing installed the Oracle rpms.
[opc@myhostoci ~]$ rpm -qa | grep oracleoracle-rdbms-server-11gR2-preinstall-1.0–14.el6.x86_64
oracle-logos-60.0.14–1.0.5.el6.noarch
oracleasm-support-2.1.11–2.el6.x86_64
kmod-oracleasm-2.0.8–15.el6_9.x86_64
oracleasmlib-2.0.12–1.el6.x86_64
oraclelinux-release-notes-6Server-21.el6.x86_64
oraclelinux-release-6Server-9.0.3.x86_64
4.11 Invoke RUNINSTALLER to install Oracle Software:
Login as oracle user, invoke the ./runInstaller scripts to install Oracle 11.2.0.4 instance.
[oracle@myhostoci database]$ pwd/wmsdb/stage/DB/database[oracle@myhostoci database]$ ls -ltrtotal 60drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 2013 sshsetup
-rwxr-xr-x. 1 oracle oinstall 3267 Aug 27 2013 runInstaller
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 2013 rpm
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 2013 response
-rw-r — r — . 1 oracle oinstall 30016 Aug 27 2013 readme.html
drwxr-xr-x. 14 oracle oinstall 4096 Aug 27 2013 stage
-rw-r — r — . 1 oracle oinstall 500 Aug 27 2013 welcome.html
drwxr-xr-x. 4 oracle oinstall 4096 Aug 27 2013 install
4.12 Create DB as per the standards:
Once oracle 11.2.0.4 binaries installed, run the DBCA command under /$ORACLE_HOME/bin folder to create DB. Below are the complete screenshots of DB.
[oracle@myhostoci ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 12 12:17:03 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,Data Mining and Real Application Testing optionsSQL> select name, open_mode from v$database;NAME OPEN_MODE
------- ------------------
POCDB READ WRITE
5. oracle 11.2.0.4 db Configuration Procedure
5.1 Configure Listener on Target DB:
Configure the listener with port number 1771
[oracle@myhostoci admin]$ pwd/wmsdb/product/oracle/11.2.0.4/db/network/admin[oracle@myhostoci admin]$ cat listener.oraADR_BASE_LISTENER_POCDB = /wmsdb/product/oracleLISTENER_POCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1771))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhostoci.com.local)(PORT = 1771))
))SID_LIST_LISTENER_POCDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = POCDB)
(ORACLE_HOME = /wmsdb/product/oracle/11.2.0.4/db)
(SID_NAME = POCDB))(SID_DESC =
(GLOBAL_DBNAME = POCDBDR_DGMGRL)
(ORACLE_HOME = /wmsdb/product/oracle/11.2.0.4/db)
(SID_NAME = POCDB)
(SERVICE_NAME = POCDB)
))
5.2 Modify TNSNAMES:
Modify the TNS entry as shown below.
[oracle@myhostoci admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /wmsdb/product/oracle/11.2.0.4/db/network/admin/tnsnames.ora# Generated by Oracle configuration tools.POCDB =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.180.66.43)(PORT = 1771))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = POCDB)
))
5.3 Apply PSU Patch On the ORACLE_HOME:
Apply DB PSU patch on the Oracle Home and Gird home as per the source.
[oracle@myhostoci db]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation. All rights reserved.Oracle Home : /wmsdb/product/oracle/11.2.0.4/db
Central Inventory : /wmsdb/product/oraInventory
from : /wmsdb/product/oracle/11.2.0.4/db/oraInst.loc
OPatch version : 11.2.0.3.20
OUI version : 11.2.0.4.0
Log file location : /wmsdb/product/oracle/11.2.0.4/db/cfgtoollogs/opatch/opatch2019-02-12_12-24-12PM_1.logLsinventory Output file location : /wmsdb/product/oracle/11.2.0.4/db/cfgtoollogs/opatch/lsinv/lsinventory2019-02-12_12-24-12PM.txt--------------------------------------------------------------------------------
Local Machine Information::
Hostname: myhostoci.com.local
ARU platform id: 226
ARU platform description:: Linux x86-64Installed Top-level Products (1):Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.Interim patches (11) :Patch 18747342 : applied on Wed Feb 06 17:05:16 GST 2019
Unique Patch ID: 19136880
Created on 13 Jul 2015, 16:50:13 hrs PST8PDT
Bugs fixed:
18747342Patch 17551261 : applied on Wed Feb 06 17:00:08 GST 2019
Unique Patch ID: 17274505
Created on 9 Feb 2014, 12:44:44 hrs PST8PDT
Bugs fixed:
17551261Patch 16346715 : applied on Wed Feb 06 16:59:20 GST 2019
Unique Patch ID: 18200614
Created on 16 Dec 2014, 17:41:13 hrs PST8PDT
Bugs fixed:
16346715Patch 16188701 : applied on Wed Feb 06 16:58:23 GST 2019
Unique Patch ID: 17274046
Created on 9 Feb 2014, 12:19:48 hrs PST8PDT
Bugs fixed:
16188701Patch 21765124 : applied on Wed Feb 06 16:57:10 GST 2019
Unique Patch ID: 19301250
Created on 1 Sep 2015, 00:34:11 hrs
Bugs fixed:
19855835, 18255105
This patch overlays patches:
19121551
This patch needs patches:
19121551
as prerequisitesPatch 21463894 : applied on Wed Feb 06 16:56:14 GST 2019
Unique Patch ID: 19210826
Created on 6 Aug 2015, 00:09:50 hrs PST8PDT
Bugs fixed:
21463894
This patch overlays patches:
20299013
This patch needs patches:
20299013
as prerequisitesPatch 21075138 : applied on Wed Feb 06 16:55:13 GST 2019
Unique Patch ID: 19237530
Created on 12 Aug 2015, 21:27:19 hrs
Bugs fixed:
21075138
This patch overlays patches:
19769489
This patch needs patches:
19769489
as prerequisitesPatch 20879889 : applied on Wed Feb 06 16:54:00 GST 2019
Unique Patch ID: 19192643
Created on 11 Aug 2015, 19:48:57 hrs
Bugs fixed:
20879889
This patch overlays patches:
18031668
This patch needs patches:
18031668
as prerequisitesPatch 18807988 : applied on Sun Feb 03 20:09:34 GST 2019
Unique Patch ID: 18628399
Created on 26 Feb 2015, 01:38:29 hrs PST8PDT
Bugs fixed:
18807988Patch 18665660 : applied on Sun Feb 03 20:04:26 GST 2019
Unique Patch ID: 19934886
Created on 2 Mar 2016, 08:29:18 hrs PST8PDT
Bugs fixed:
18665660
This patch overlays patches:
21948347
This patch needs patches:
21948347
as prerequisitesPatch 21948347 : applied on Wed Jan 30 19:09:24 GST 2019
Unique Patch ID: 19564435
Patch description: "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Created on 14 Dec 2015, 03:31:48 hrs PST8PDT
Sub-patch 21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch 20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch 20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17184721, 21538558, 16091637, 18092127, 17381384, 15979965, 18441944
13837378, 16314254, 16731148, 17835048, 13558557, 17201159, 17853498
17246576, 18356166, 18440047, 18681862, 16875449, 19788842, 17296856
21330264, 14010183, 17648596, 17551063, 17025461, 17267114, 17912217
17889583, 18202441, 17040764, 16524926, 17478145, 19358317, 18747196
18641419, 17036973, 17811789, 14285317, 16542886, 18009564, 8322815
16618694, 16692232, 18247991, 17570240, 17848897, 17441661, 14034426
17465741, 21343897, 20506706, 17437634, 21453153, 18339044, 22321741
17951233, 18430495, 21787056, 20506715, 17811429, 18230522, 19554106
19458377, 17612828, 22092979, 22321756, 17040527, 17811438, 18641461
14657740, 13364795, 17588480, 17346671, 18235390, 17889549, 19309466
16472716, 18331850, 18641451, 17344412, 21179898, 17546761, 18203835
18964939, 18203838, 22195457, 17313525, 18203837, 18139690, 14106803
16837842, 17842825, 21352646, 20657441, 16360112, 22195441, 17389192
14565184, 17205719, 22195448, 14354737, 14764829, 13944971, 16571443
17186905, 18673342, 17080436, 17027426, 19972569, 19972568, 19972566
17282229, 19972564, 16870214, 19615136, 17390431, 18762750, 16613964
18098207, 17957017, 18471685, 19730508, 18264060, 21538485, 17754782
17323222, 17600719, 18317531, 17852463, 17596908, 17655634, 20074391
16228604, 19972570, 18996843, 16042673, 19854503, 17835627, 20334344
20861693, 18000422, 17393683, 17551709, 20506699, 19006849, 18456514
18277454, 17258090, 17174582, 17242746, 16399083, 17824637, 17762296
17397545, 16450169, 12364061, 20067212, 18856999, 19211724, 19463893
21343775, 19463897, 17853456, 18673304, 20004021, 21668627, 16194160
17477958, 16538760, 12982566, 20296213, 18293054, 17610798, 19699191
18135678, 17311728, 10136473, 16785708, 17786518, 18315328, 18334586
12747740, 19032867, 18096714, 17390160, 17232014, 16422541, 18673325
18155762, 19827973, 14015842, 17726838, 18554871, 18051556, 20803583
21972320, 18282562, 17922254, 15990359, 16855292, 16668584, 21343838
20299015, 17446237, 17694209, 17288409, 17274537, 13955826, 16934803
17634921, 17501491, 16315398, 17006183, 13829543, 18191164, 17655240
18384391, 19393542, 21538567, 16198143, 21847223, 17892268, 20142975
19584068, 17165204, 21756699, 18508861, 18554763, 16901385, 18189036
17936109, 14829250, 17385178, 17443671, 20925795, 17478514, 16850630
13951456, 16595641, 15861775, 14054676, 16912439, 17299889, 17297939
16833527, 18619917, 17798953, 17816865, 18607546, 17571306, 17341326
17851160, 20558005, 17586955, 19049453, 21051840, 17587063, 16956380
18328509, 14133975, 18061914, 21051833, 18522509, 18765602, 18199537
17332800, 13609098, 18384537, 14338435, 17945983, 21067387, 16392068
17752995, 21051862, 17237521, 16863422, 18244962, 19544839, 17156148
18973907, 17449815, 17877323, 18180390, 17088068, 17037130, 20004087
19466309, 11733603, 21051858, 18084625, 18674024, 21051852, 18091059
18306996, 16306373, 18193833, 19915271, 17787259, 20631274, 16344544
14692762, 18614015, 17346091, 18228645, 17721717, 18436307, 11883252
17891943, 16384983, 19121551, 12816846, 17982555, 17761775, 17265217
17071721, 16721594, 18262334, 15913355, 17891946, 17672719, 17602269
17239687, 17042658, 17238511, 17811456, 17284817, 17752121, 17394950
16579084, 17011832, 22195465, 14602788, 18325460, 12611721, 16903536
17006570, 18783224, 16043574, 16494615, 21526048, 19197175, 16069901
17811447, 17308789, 22195477, 17865671, 19013183, 17343514, 18316692
17325413, 16180763, 17348614, 14368995, 17393915, 16285691, 20331945
17883081, 17705023, 17614227, 22195485, 14084247, 13645875, 16777840
19727057, 14852021, 18744139, 18674047, 17716305, 18482502, 19289642
17622427, 22195492, 14458214, 18723434, 17767676, 17786278, 17082983
21351877, 13498382, 18331812, 16065166, 18031668, 16943711, 21517440
17649265, 18094246, 13866822, 14245531, 17783588, 17082359, 20448824
18280813, 16268425, 17302277, 18018515, 17215560, 19271443, 20777150
17016369, 20441797, 19769489, 17545847, 18260550, 13853126, 17227277
9756271, 18868646, 17614134, 19680952, 18704244, 17546973, 18273830
18828868, 17050888, 17360606, 16992075, 17375354, 12905058, 18362222
17571039, 17468141, 18436647, 17235750, 21168487, 16220077, 16929165--------------------------------------------------------------------------------OPatch succeeded.
On Grid Home:
[grid@myhostoci ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation. All rights reserved.Oracle Home : /wmsdb/product/oracle/11.2.0.4/grid
Central Inventory : /wmsdb/product/oraInventory
from : /wmsdb/product/oracle/11.2.0.4/grid/oraInst.loc
OPatch version : 11.2.0.3.20
OUI version : 11.2.0.4.0
Log file location : /wmsdb/product/oracle/11.2.0.4/grid/cfgtoollogs/opatch/opatch2019-02-12_12-26-03PM_1.logLsinventory Output file location : /wmsdb/product/oracle/11.2.0.4/grid/cfgtoollogs/opatch/lsinv/lsinventory2019-02-12_12-26-03PM.txt--------------------------------------------------------------------------------
Local Machine Information::
Hostname: myhostoci.com.local
ARU platform id: 226
ARU platform description:: Linux x86-64Installed Top-level Products (1):Oracle Grid Infrastructure 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.Interim patches (1) :Patch 21948347 : applied on Wed Jan 30 08:31:42 GST 2019
Unique Patch ID: 19564435
Patch description: "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Created on 14 Dec 2015, 03:31:48 hrs PST8PDT
Sub-patch 21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch 20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch 20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17184721, 21538558, 16091637, 18092127, 17381384, 15979965, 18441944
13837378, 16314254, 16731148, 17835048, 13558557, 17201159, 17853498
17246576, 18356166, 18440047, 18681862, 16875449, 19788842, 17296856
21330264, 14010183, 17648596, 17551063, 17025461, 17267114, 17912217
17889583, 18202441, 17040764, 16524926, 17478145, 19358317, 18747196
18641419, 17036973, 17811789, 14285317, 16542886, 18009564, 8322815
16618694, 16692232, 18247991, 17570240, 17848897, 17441661, 14034426
17465741, 21343897, 20506706, 17437634, 21453153, 18339044, 22321741
17951233, 18430495, 21787056, 20506715, 17811429, 18230522, 19554106
19458377, 17612828, 22092979, 22321756, 17040527, 17811438, 18641461
14657740, 13364795, 17588480, 17346671, 18235390, 17889549, 19309466
16472716, 18331850, 18641451, 17344412, 21179898, 17546761, 18203835
18964939, 18203838, 22195457, 17313525, 18203837, 18139690, 14106803
16837842, 17842825, 21352646, 20657441, 16360112, 22195441, 17389192
14565184, 17205719, 22195448, 14354737, 14764829, 13944971, 16571443
17186905, 18673342, 17080436, 17027426, 19972569, 19972568, 19972566
17282229, 19972564, 16870214, 19615136, 17390431, 18762750, 16613964
18098207, 17957017, 18471685, 19730508, 18264060, 21538485, 17754782
17323222, 17600719, 18317531, 17852463, 17596908, 17655634, 20074391
16228604, 19972570, 18996843, 16042673, 19854503, 17835627, 20334344
20861693, 18000422, 17393683, 17551709, 20506699, 19006849, 18456514
18277454, 17258090, 17174582, 17242746, 16399083, 17824637, 17762296
17397545, 16450169, 12364061, 20067212, 18856999, 19211724, 19463893
21343775, 19463897, 17853456, 18673304, 20004021, 21668627, 16194160
17477958, 16538760, 12982566, 20296213, 18293054, 17610798, 19699191
18135678, 17311728, 10136473, 16785708, 17786518, 18315328, 18334586
12747740, 19032867, 18096714, 17390160, 17232014, 16422541, 18673325
18155762, 19827973, 14015842, 17726838, 18554871, 18051556, 20803583
21972320, 18282562, 17922254, 15990359, 16855292, 16668584, 21343838
20299015, 17446237, 17694209, 17288409, 17274537, 13955826, 16934803
17634921, 17501491, 16315398, 17006183, 13829543, 18191164, 17655240
18384391, 19393542, 21538567, 16198143, 21847223, 17892268, 20142975
19584068, 17165204, 21756699, 18508861, 18554763, 16901385, 18189036
17936109, 14829250, 17385178, 17443671, 20925795, 17478514, 16850630
13951456, 16595641, 15861775, 14054676, 16912439, 17299889, 17297939
16833527, 18619917, 17798953, 17816865, 18607546, 17571306, 17341326
17851160, 20558005, 17586955, 19049453, 21051840, 17587063, 16956380
18328509, 14133975, 18061914, 21051833, 18522509, 18765602, 18199537
17332800, 13609098, 18384537, 14338435, 17945983, 21067387, 16392068
17752995, 21051862, 17237521, 16863422, 18244962, 19544839, 17156148
18973907, 17449815, 17877323, 18180390, 17088068, 17037130, 20004087
19466309, 11733603, 21051858, 18084625, 18674024, 21051852, 18091059
18306996, 16306373, 18193833, 19915271, 17787259, 20631274, 16344544
14692762, 18614015, 17346091, 18228645, 17721717, 18436307, 11883252
17891943, 16384983, 19121551, 12816846, 17982555, 17761775, 17265217
17071721, 16721594, 18262334, 15913355, 17891946, 17672719, 17602269
17239687, 17042658, 17238511, 17811456, 17284817, 17752121, 17394950
16579084, 17011832, 22195465, 14602788, 18325460, 12611721, 16903536
17006570, 18783224, 16043574, 16494615, 21526048, 19197175, 16069901
17811447, 17308789, 22195477, 17865671, 19013183, 17343514, 18316692
17325413, 16180763, 17348614, 14368995, 17393915, 16285691, 20331945
17883081, 17705023, 17614227, 22195485, 14084247, 13645875, 16777840
19727057, 14852021, 18744139, 18674047, 17716305, 18482502, 19289642
17622427, 22195492, 14458214, 18723434, 17767676, 17786278, 17082983
21351877, 13498382, 18331812, 16065166, 18031668, 16943711, 21517440
17649265, 18094246, 13866822, 14245531, 17783588, 17082359, 20448824
18280813, 16268425, 17302277, 18018515, 17215560, 19271443, 20777150
17016369, 20441797, 19769489, 17545847, 18260550, 13853126, 17227277
9756271, 18868646, 17614134, 19680952, 18704244, 17546973, 18273830
18828868, 17050888, 17360606, 16992075, 17375354, 12905058, 18362222
17571039, 17468141, 18436647, 17235750, 21168487, 16220077, 16929165--------------------------------------------------------------------------------OPatch succeeded.
5.4 Configure DB Initialization Parameters as per the Source system:
We have configured the initPOCDB.ora in target as per the source.
[oracle@myhostoci dbs]$ cat initPOCDB.ora
*.db_unique_name='POCDB'
*.audit_file_dest='/wmsdb/product/oracle/admin/POCDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+POC_MYDISKGRP/pocdb/controlfile/current.278.996489185'
*.db_block_size=8192
*.db_create_file_dest='+POC_MYDISKGRP'
*.db_domain=''
*.db_name='POCDB'
*.diagnostic_dest='/wmsdb/product/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=POCDBXDB)'
*.open_cursors=300
*.pga_aggregate_target=3134193664
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=9403629568
*.undo_tablespace='UNDOTBS1'5.5 Turn OFF Auto Extend :
Turn auto extend OFF for all the data files as per our standards and verify using below query.
select tablespace_name,file_name,autoextensible from dba_data_files where autoextensible = ‘YES’;5.6 Ensure filesystemio_options=SETALL in DB level:
As per Oracle best practices, set the filesystemio_options as SETALL.
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONSNAME TYPE VALUE
--------------------- ----------- ----------------------
filesystemio_options string SETALL
5.7 Redo log group creation / resizing:
As per our standards, create 5 redo log groups with 2 members count and each member size 1G.
Note : But in our case we are having only one member due to one disk group availability.
select * from v$logfile;GROUP# STATUS TYPE MEMBER3 ONLINE +POC_MYDISKGRP/pocdb/onlinelog/group_3.279.9964891892 ONLINE +POC_MYDISKGRP/pocdb/onlinelog/group_2.280.9964891871 ONLINE +POC_MYDISKGRP/pocdb/onlinelog/group_1.281.996489187
Add new groups
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 (‘+POC_MYDISKGRP’) SIZE 1G;Database altered.SQL> ALTER DATABASE ADD LOGFILE GROUP 5 (‘+POC_MYDISKGRP’) SIZE 1G;Database altered.
Once group 4 becomes active
SQL> alter database drop logfile group 1;Database altered.SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘+POC_MYDISKGRP’) SIZE 1G;Database altered.SQL> alter database drop logfile group 2;Database altered.SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘+POC_MYDISKGRP’) SIZE 1G;Database altered.SQL> alter database drop logfile group 3;Database altered.SQL> ALTER DATABASE ADD LOGFILE GROUP 3 (‘+POC_MYDISKGRP’) SIZE 1G;Database altered.
5.8 TEMP and UNDO tablespace Size:
Allocate size of temp and undo tablespaces as per the source.
5.9 Configure Memory_target=3G for ASM:
As per our standers, set memory target value as 3G for ASM.
Since it is POC environment we will keep as it is.
SQL> show parameter memory;NAME TYPE VALUE
------------------ ------------------------------ ---------
memory_max_target big integer 1076M
memory_target big integer 1076M
5.10 Ensure SPFILE should be kept in ASM Diskgroup:
Ensure both Oracle and ASM is running with spfile in ASM diskgroup.
SQL> show parameter spfile;NAME TYPE VALUE
------ ------ ------------------------------------------
spfile string /wmsdb/product/oracle/11.2.0.4/db/db/spfilePOCDB.oraSQL> show parameter pfile;NAME TYPE VALUE
------ ------ ------------------------------------------
spfile string +POC_MYDISKGRP/asm/asmparameterfile /registry.253.995301235
5.11 Enable Crontab Sync-up:
Cronjobs to be enable as per the Source DB.
[oracle@myhostoci integration]$ crontab -l5.12 Configure AWR Retention (6 months):
Configure AWR retention period as 6 months in target DB as per our standards.
SQL> execute dbms_workload_repository.modify_snapshot_settings (interval => 60, retention => 263520);PL/SQL procedure successfully completed.SQL> select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 01:00:00.0
+00183 00:00:00.0
5.13 Update environment settings to oracle user profile:
Create the environment setting profiles as per the source DB. Below are the screenshots.
[oracle@myhostoci ~]$ cat db.env
# Oracle user DB FOR WMS database profile
export TRACE=/wmsdb/product/oracle/diag/rdbms/pocdb/POCDB/trace
export ORACLE_BASE=/wmsdb/product/oracle
export ORACLE_HOME=/wmsdb/product/oracle/11.2.0.4/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_SID=POCDB
export NLS_LANG=AMERICAN_AMERICA.UTF8[grid@myhostoci ~]$ cat wmsasm.env
# Oracle user ASM FOR WMS database profileexport ORACLE_BASE=/wmsdb/product/oracle
export ORACLE_HOME=$ORACLE_BASE/11.2.0.4/grid
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/vac/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_SID=+ASM
export NLS_LANG=AMERICAN_AMERICA.UTF8
export ALERT=$ORACLE_BASE/diag/asm/+asm/+ASM/trace
5.14 Importing DB Dump from source server to target server
[oracle@myhostoci Dumps]$ pwd
/wmsdb/stage/Dumps
[oracle@myhostoci Dumps]$ ls -ltr
total 48787088
-rw-r--r-- 1 oracle oinstall 87798 Dec 24 13:49 MyDB_03-Jun-2018.log
-rwxrwxrwx 1 oracle oinstall 11680186368 Dec 24 13:55 MyDB_03-Jun-2018_01.dmp
-rwxrwxrwx 1 oracle oinstall 15341146112 Dec 24 14:03 MyDB_03-Jun-2018_02.dmp
-rwxrwxrwx 1 oracle oinstall 12644749312 Dec 24 14:08 MyDB_03-Jun-2018_04.dmp
-rwxrwxrwx 1 oracle oinstall 9946480640 Dec 24 14:13 MyDB_03-Jun-2018_03.dmp
-rw------- 1 oracle oinstall 281594 Jan 2 17:01 nohup.out
-rw-r--r-- 1 oracle oinstall 5975948 Jan 3 11:05 MyDB_03-Jan-2019.logcreate or replace directory DATA_PUMP as '/wmsdb/stage/Dumps';Verfiy the Dump Directory
SELECT directory_name, directory_path FROM dba_directories;nohup impdp "'/ as sysdba'" directory=DATA_PUMP full=y TABLE_EXISTS_ACTION=REPLACE dumpfile=MyDB_03-Jun-2018_%U.dmp logfile=MyDB_03-Jan-2019.log &