Oracle Data Migration Techniques

Migration is moving data from source to target.

Oracle Migration Techniques are

1) Data Pump(export/import):

2) Transportable Tablespaces (TTS)

3) RMAN

4) Data Guard

5) GoldenGate:

6) Oracle Streams

7) Insert/Select or Create Table as Select (CTAS) across database links

8) Custom procedural approaches (PL/SQL, etc.

9) build standby

DATAPUMP :

Link

Oracle 19c Installation (Single Node) With ASM

 

Installation Instructions for installing oracle and asm binaries:

This document will guide you steps by step with command and instructions on how to install Oracle 19c ASM and database binaries without configuring ASM, diskgroups and database.

This document will provide step by step instructions on how to install Oracle database 19c on Single node VM.

Pre-Requisite and downloads:

Target Audience:

1. Oracle DBA with basic knowledge of Installations

Scenario for installation:

1. Single Node installation
2. Storage should be on ASM

Pre-Requisites:

1. Server is ready and updated for installation
2. Disk are avialble and dicoverable using fdisk command
3. Putty/terminal can be accessible

Software Versions:

1. OS Version: Oracle Linux 8.8
2. Database: Oracle 19c (19.19.0.0.0) with Grid Infrastructure (Oracle Restart)

Certification Matrix:

Before Go ahead and perform installation, do check the certification matrix available on Oracle Support :

Certified on Oracle Linux 7 & 8.x 

About 8.x Version 

Software Needed:

We can download these from official Oracle website (https://edelivery.oracle.com/osdc/faces/SoftwareDelivery_ ).

1. Grid Infra (Including cvuqdisk utility)
2. Oracle Database 19c
3. Oracle database and grid RU patches from Oracle Support Patch download section ( https://support.oracle.com/knowledge/Oracle Cloud/19202304_9.html )

Downloading Grid Infra (Oracle Restart) & Oracle Database 19.3.0.0 version from edelivery location as shown in picture here.

Note : edelivery has WGET options as well to download softwares, in case if WGET is a viable option to download.

Note : You should not download Oracle HTTP Server 12.2.1.3.0 from list.

• Create a folder

• Copy WGET script from download page onto the server in that folder as WGET_1.sh

• Change script permission to 755

  chmod 755 WGET_1.SH
  

• WGET will download all files into one folder.

Downloaded files are listed here:

• V982063-01.zip - Oracle Database
• V982068-01.zip - Grid Infra (ASM, Oracle Restart)

Installation of ASM Binaries:

Installation Steps:

We need to update system first:

sudo su -
yum update

Installing Pre-Instllation requirements using YUM:

sudo su -
yum install oracle-database-preinstall-19c

Setting up Oracle User and its group

Since already we have executed pre-installation package, these users and group would already be there.

Verify, if not created:

id oracle

It should return like :

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)

Switch you login to Root:

su -

## Incase if pre-install has not worked and oracle user has not created:
groupadd oinstall
groupadd dba
groupadd oper
groupadd backupdba
groupadd dgdba
groupadd kmdba

useradd -m -g oinstall -d /home/oracle oracle
usermod -a -G dba, oper, backupdba, dgdba, kmdba  oracle


## These groups to be created:
groupadd asmadmin
groupadd asmdba
groupadd asmoper
groupadd osoper


usermod -a -G asmdba,osoper oracle
useradd -u 54323 -g oinstall -G asmadmin,asmdba,asmoper grid

Create OFA folders for installations:

## For Oracle user
mkdir -p /u01/app/oracle/product/19.0.0/db_1

## For Grid user
mkdir -p /u01/app/grid
mkdir -p /u01/app/19.0.0/grid

chown -R grid:oinstall /u01
chown -R oracle:oinstall /u01/app/oracle

chmod -R 775 /u01

Login to oracle to set environment Oracle Database

• su - oracle

backing up existing .bash_profile

• mv ~/.bash_profile ~/.bash_profile_org

Use vi editor with .bash_profile to create below lines:

vi .bash_profile

 if [ -f ~/.bashrc ]; then
  . ~/.bashrc
  fi
  ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
  ORACLE_SID=cdbdplpp; export ORACLE_SID
  ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1; export ORACLE_HOME
  # NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
  TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

  PATH=$PATH:$HOME/.local/bin:$HOME/bin
  PATH=${PATH}:/usr/bin:/bin:/usr/local/bin
  PATH=.:${PATH}:$ORACLE_HOME/bin
  export PATH

  LD_LIBRARY_PATH=$ORACLE_HOME/lib
  LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
  LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
  export LD_LIBRARY_PATH

  CLASSPATH=$ORACLE_HOME/JRE
  CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
  CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
  CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
  export CLASSPATH

  export TEMP=/tmp
  export TMPDIR=/tmp
  umask 022

Login to grid User to set environment for ASM

• su - grid

backing up existing .bash_profile

• mv ~/.bash_profile ~/.bash_profile_org

Use vi editor with .bash_profile to create below lines:

vi .bash_profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

ORACLE_SID=+ASM; export ORACLE_SID

ORACLE_BASE=/u01/app/grid; export ORACLE_BASE
ORACLE_HOME=/u01/app/19.0.0/grid; export ORACLE_HOME


TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN    

PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/local/bin    
export PATH    
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

Unzipping downloaded files of database using oracle user:

Unzipping downloaded files of Grid Infra using grid user:

• Grid Infra

  su - grid
  unzip V982068-01.zip -d /u01/app/19.0.0/grid
  

• Database Unzip

  unzip V982063-01.zip -d /u01/app/oracle/product/19.0.0/db_1

Swtich login to Root:

• su -

Install Oracle ASMLib Package

yum install oracleasm-support
yum install kmod-redhat-oracleasm
yum install oracleasmlib-2.0.17-1.el8.x86_64.rpm

In case yum install is not working for oracleasm-support, download it from Oracle Linux 8 Yum repository .

On this page, search for "oracleasm-support"

You can download ASMLib 2.0 from Oracle ASMLib page :

Once the installation done, proceed to it's configuration:

• oracleasm configure -i

We need to answer few questions asked by this configurator.

  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
  Scan for Oracle ASM disks on boot (y/n) [y]: y

Switch back to root user:

Need to perform some task which is required Root user's permission.

su - 

Updating Kernel Parameters

Note: If pre-install package did not work, this work to be done manuall.

Kernel parameters are to be updated as per Oracle documentations. Below parameters value to be set. We have two ways to update this. Create another file in folder system.d starting with any number for example:

99-oracle-database-sysctl.conf

Open the file and update values:

 fs.aio-max-nr = 1048576
 fs.file-max = 6815744
 kernel.shmall = 2097152
 kernel.shmmax = 4294967295
 kernel.shmmni = 4096
 kernel.sem = 250 32000 100 128
 net.ipv4.ip_local_port_range = 9000 65500
 net.core.rmem_default = 262144
 net.core.rmem_max = 4194304
 net.core.wmem_default = 262144
 net.core.wmem_max = 1048576

To apply changes in kernel parameters :

 sysctl -p

Note: A reboot may require if the changes does not refelect

Other packages required for this installation

Two more packages to be installed.

 yum install ksh
 yum install libaio-devel.x86_64

Oracle Grid infrastructure Software (Oracle Restart) Installation

Switch to root user

su -

Install cvuqdisk using root user

cd /u01/app/19.0.0/grid/cv/rpm/
CVUQDISK_GRP=oinstall; export CVUQDISK_GRP
rpm -iv cvuqdisk-1.0.10-1.rpm

Note: File name of cvuqdisk-1.0.10-1.rpm to be updated while doing actual installations

Switch to grid user

su - grid

vi $ORACLE_HOME/cv/admin/cvu_config

 Search for "CV_ASSUME_DISTID="
 update this with :
 CV_ASSUME_DISTID=OLE8.8

Switch to root user

sudo su -
vi /etc/hosts

##add lines with host and ip:
10.76.62.202  pvv-svlp-cmtst1.int.aurobay.com        pvv-svlp-cmtst1

Switch to grid user

su - grid

cd $ORACLE_HOME

Note: Download ResponseFileGridSetup.txt and rename it to ResponseFileGridSetup.rsp

ResponseFileGridSetup.txt

Execute either of the statment:

1. Silent installation, without diskgroup and without display (XTERM):

 Download Attached response file "ResponseFileGridSetup.txt"
 Before proceeding, response file to be renamed to "ResponseFileGridSetup.rsp"

 ./gridSetup.sh -silent -ignoreInternalDriverError -responseFile/ home/grid/orclsoft/ResponseFileGridSetup.rsp INVENTORY_LOCATION=/u01/app/oraInventory   SELECTED_LANGUAGES=en oracle.install.asm.SYSASMPassword=<password> oracle.install.asm.monitorPassword=<password> 	

Note : installation of binaries for ASM is installed. This installation does not have any disk attached or ASM configured.

Installation of Oracle Binaries

Switch to oracle user

su - oracle

vi $ORACLE_HOME/cv/admin/cvu_config

 Search for "CV_ASSUME_DISTID="
 update this with :
 CV_ASSUME_DISTID=OLE8.8

The environment is ready for installation.

Download and rename the response file: db_install.txt

1. Silent installation of oracle binaries without display (XTERM):

 Download Attached response file "db_install.txt" and rename the response file to
 db_install.rsp 


 ./runInstaller -silent -debug -force -responseFile /home/oracle/orasoft/db_install.rsp DECLINE_SECURITY_UPDATES=true

Oracle Data Migration options!!

ACFS!!

1. What is ADVM and ACFS?
2. Can I mount ACFS filesystem on server which is not part of RAC cluster nodes?
3. Can we increase or decrease the ACFS filesystem size dynamically? And what is the impact?
4. Is ACFS file system follows the Disk group redundancy? (External, Normal and High)

Performance Tuning!!

Restore Point Vs Guarantee Restore Point

 Restore Point Vs Guarantee Restore Point

1. To create GRP, is Flashback Database must be enabled?
2. To enable Flashback does DB nees to be stopped?
3. How many GRP I can create on DB?
4. How many times I can flashback my DB?
5. After flashback database does latest stats of table will be reverted or still the stats are persistent?
6. After changing compatibility parameter can I flashback database?
7. After opening database with resetlogs, Can I still flashback DB?
8. Can I flashback database on SQL command prompt or RMAN command prompt?
9. What are the differences between flashback logs and archive logs?
10. What is the location of Flashbacks logs?
11. Can set Flashback log location to some custom location?
12. When the flashback logs will be generated?
13. What are the use of flashback logs in database?
14. Can we create Restore point inside PDB?
15. Can we flashback PDB to a restore point in 12cR1?
Can we flashback PDB to a restore point in 12cR2?