Oracle 19c database with ASM

[ Oracle Complete Scenario ] GI 19c [ASM] DB 19c on [ Oracle Linux 8.3 ] + RMAN

In this Document we will start with oracle linux 8.3 installation, then move to apply the prerequisites for both GI and DB, after that we going to proceed to Oracle Grid Infrastructure 12.2 installation with ASM then Oracle 19.3 Database software only installation, Once we finish the installation, we will create database, then using RMAN we’ll simulate the weekly full backup and daily archivelog backup from the database, then create objects during this process, after finish all of this, we will drop the database, and we going to try to rebuild it by restoring the RMAN backup taken from dropped one

Requirements:

Oracle Linux 8.3.
Oracle 19.3 Grid Infrastructure (GI)
Oracle 19.3 Database (DB)
VMWare or VBox
Vitrual Machine Resource allocation:
- s8 GB RAM
- 4 Cores [CPU]
- 60 GB Virtual Disk for OS
- 30 GB Virtual Disk for ASM
- 5 GB Virtual Disk for RMAN Backup

The steps that we are going to follow are:

Prepare the OS for Grid and DB installation
Installing oracle grid infrastructure 19.3 software
Create Data and FRA disk groups
Installing Oracle DB 19.3 software only
Create database using dbca
RMAN backup strategy
Configure RMAN and Perform a weekly Full backup
Create Database Objects
Simulate the archivelog daily backup
Drop/Delete DB
Restore DB from Backup
Add DB service

Prepare the OS for the Installation [Prerequisites]

You need for oracle ASMLib and you can download it here ASMLib8 ASMLib7 ASMSupport

check the Internet connectivity using ping :

# check internet connectivity 
 ping www.google.com

check cache to download the metadata from online repo:

# check the cache
 dnf makecache

Install prerequisites and ASM required packages:

# Install oracle prereqisites  
 dnf install oracle-database-preinstall-19c -y

this step is optional if you want to update the system:

# Optional 
dnf check-update # list the packages that need for update  
dnf update 
dnf clean all

Create OS groups for asm administration and operation:

# Create ASM groups 
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin

Add asmdba as secondary group to oracle user:

# add asmdba group to oracle user
 usermod -a -G asmadmin,asmdba oracle
 id oracle

Create Grid user:

# create grid user 
 useradd -u 54331 -g oinstall -G dba,asmdba,asmadmin,asmoper,racdba grid

Change the password for Oracle and Grid user:

# create grid oracle user passwords 
 passwd oracle
 passwd grid

Create the Directories for the Oracle Grid installation

mkdir -p /u01/19c/oracle_base
mkdir -p /u01/19c/oracle_base/oracle/db_home
chown -R oracle:oinstall /u01

Create the Directories for the Oracle Database installation

mkdir -p /u01/19c/grid_base
mkdir -p /u01/19c/grid_home
chown -R grid:oinstall /u01/19c/grid_base /u01/19c/grid_home
chmod -R 775 /u01

Switch to the grid user and edit the Grid .bash_profile, before edit the file I will take backup for it first

su - grid
cd /home/grid
cp .bash_profile .bash_profile.bkp

Copy and paste this to grid home directory

cat > /home/grid/.grid19c_env <<EOF
# User specific environment and startup programs
ORACLE_SID=+ASM; export ORACLE_SID
ORACLE_BASE=/u01/19c/grid_base; export ORACLE_BASE
ORACLE_HOME=/u01/19c/grid_home; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
JAVA_HOME=/usr/bin/java; export JAVA_HOME
TNS_ADMIN=\\$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:\\${JAVA_HOME}/bin:\\${PATH}:\\$HOME/bin:\\$ORACLE_HOME/bin
PATH=\\${PATH}:/usr/bin:/bin:/usr/local/bin
export PATH
umask 022
EOF

apply the profile for the current session and check the environment variables:

echo "source ~/.grid19c_env" >> ~/.bash_profile
source .bash_profile
env | grep -i "tns\\|oracle"
exit

Switch to oracle user and backup the .bash_profile :

su - oracle 
cp .bash_profile .bash_profile.bkp

create new bash profile file copy the below script to your terminal and press enter:

cat > /home/oracle/.db19c_env <<EOF
# specific environment and startup programs
ORACLE_HOSTNAME=\\$HOSTNAME; export ORACLE_HOSTNAME
ORACLE_SID=prod; export ORACLE_SID
ORACLE_UNQNAME=prod; export ORACLE_UNQNAME
ORACLE_BASE=/u01/19c/oracle_base; export ORACLE_BASE
ORACLE_HOME=\\$ORACLE_BASE/oracle/db_home; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
JAVA_HOME=/usr/bin/java; export JAVA_HOME
TNS_ADMIN=\\$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:\\${JAVA_HOME}/bin:\\${PATH}:\\$HOME/bin:\\$ORACLE_HOME/bin
PATH=\\${PATH}:/usr/bin:/bin:/usr/local/bin
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
TNS_ADMIN=\\$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:\\${JAVA_HOME}/bin:\\${PATH}:\\$HOME/bin:\\$ORACLE_HOME/bin
PATH=\\${PATH}:/usr/bin:/bin:/usr/local/bin
TEMP=/tmp ;export TMP
TMPDIR=\\$tmp ; export TMPDIR
export PATH
umask 022
EOF

apply the profile

echo "source ~/.db19c_env" >> ~/.bash_profile
source /home/oracle/.bash_profile
env | grep ORACLE
exit

Check the NTP service

systemctl status chronyd

set secure linux to permissive

# change SELINUX=enforcing to SELINUX=permissive
sed -i s/SELINUX=enforcing/SELINUX=permissive/g /etc/selinux/config
sed -i s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config
cat /etc/selinux/config
# Forse the change  
setenforce Permissive

# create limitation in security forlder for grid user 
cp /etc/security/limits.d/oracle-database-preinstall-19c.conf /etc/security/limits.d/grid-database-preinstall-19c.conf

# rename oracle with grid in this file grid-database-preinstall-19c.conf
# use vim 
vim /etc/security/limits.d/grid-database-preinstall-19c.conf
:%s/oracle/grid/g
:x

allow the port 1521 port in the Linux firewall ref-link

# allow the traffic for the port 1521
firewall-cmd  --permanent --add-port=1521/tcp
firewall-cmd --list-ports
# OR Stop the firewall and disable it 
systemctl stop firewalld
systemctl disable firewalld

Configure the Oracle ASM

# Add to the vmx file for the VM
disk.EnableUUID = "TRUE"# detect the Vmware disks without reboot the machine 
echo "- - -" >> /sys/class/scsi_host/host1/scan

# format the disks on both nodes
 export HDISK="/dev/sdb"echo -e "d\\nn\\n\\n\\n\\n+5G\\np\\nn\\np\\n\\n\\n+15G\\np\\nn\\np\\n\\n\\n\\n\\np\\nw" | fdisk $HDISK
 fdisk -l 

udevadm info --query=property --name /dev/sdb1
udevadm info --query=property --name /dev/sdb2
udevadm info --query=property --name /dev/sdb3
/lib/udev/scsi_id -gud /dev/
vim /etc/udev/rules.d/99-asm-disks.rules

36000c29d381cc4882ee074a9333f9ee2
# paste below to the file 

KERNEL=="sd*",ENV{DEVTYPE}=="partition",ATTR{partition}=="1",PROGRAM=="/lib/udev/scsi_id -gud /dev/$name",RESULT=="36000c2901abcc9d26317288407432092",SYMLINK+="oracleasm/OCR_ASM_1", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*",ENV{DEVTYPE}=="partition",ATTR{partition}=="2",PROGRAM=="/lib/udev/scsi_id -gud /dev/$name",RESULT=="36000c2901abcc9d26317288407432092",SYMLINK+="oracleasm/DAT_ASM_1", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*",ENV{DEVTYPE}=="partition",ATTR{partition}=="3",PROGRAM=="/lib/udev/scsi_id -gud /dev/$name",RESULT=="36000c2901abcc9d26317288407432092",SYMLINK+="oracleasm/FRA_ASM_1", OWNER="grid", GROUP="asmadmin", MODE="0660"

fsck -N /dev/sdb?

# Load updated block device partition table
partx -u /dev/sdb1
partx -u /dev/sdb2
partx -u /dev/sdb3

# test the rules are working as expected 
udevadm test /block/sdb/sdb1
udevadm test /block/sdb/sdb2
udevadm test /block/sdb/sdb3

# Reload the UDEV rules 
udevadm control --reload-rules && udevadm trigger --action=add

Nếu dùng san thì config như sau

# Sửa địa chỉ san
vi /etc/iscsi/initiatorname.iscsi
# Start các service liên quan
service multipathd start
chkconfig multipathd on
service iscsi start
service iscsid start
chkconfig iscsi on
chkconfig iscsid on
iscsiadm -m discovery --type sendtargets -p 192.168.2.160
service iscsi restart
iscsiadm -m session
# Format ổ
lsblk or fdisk -l trước khi format 
[root@testbox ~]# fdisk /dev/sdd

Welcome to fdisk (util-linux 2.32.1).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table.
Created a new DOS disklabel with disk identifier 0x8c782d71.

Command (m for help): n
Partition type
   p   primary (0 primary, 0 extended, 4 free)
   e   extended (container for logical partitions)
Select (default p):

Using default response p.
Partition number (1-4, default 1):
First sector (2048-209715199, default 2048):
Last sector, +sectors or +size{K,M,G,T,P} (2048-209715199, default 209715199):

Created a new partition 1 of type 'Linux' and of size 100 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.
# Load updated block device partition tables
# For Linux 5,6 and 7
/sbin/partprobe /dev/sdd1
/sbin/partprobe /dev/sde1
# For Linux8
/sbin/partx -u /dev/sdd1
/sbin/partx -u /dev/sde1
#Find SCSI ID
/usr/lib/udev/scsi_id -g -u -d /dev/sdd
/usr/lib/udev/scsi_id -g -u -d /dev/sde
# Cấu hình udev
vi /etc/udev/rules.d/99-oracle-asmdevices.rules
Thêm vào file nội dung như sau:
KERNEL=="sdd1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c45527636564b4b372d426e53722d57797571", SYMLINK+="oracleasm/OCR_ASM_1", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sdb1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c45525357544f72322d367931652d71713331", SYMLINK+="oracleasm/DAT_ASM_1", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sdc1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c4552507a334979682d64634e682d727a4a67", SYMLINK+="oracleasm/FRA_ASM_1", OWNER="grid", GROUP="asmadmin", MODE="0660"
# Reload the udev rules
/sbin/udevadm control --reload-rules
/sbin/udevadm trigger
# Check list
ls -ld /dev/sd*1
ls -ltra /dev/oracleasm*

Installing oracle grid infrastructure 19c software

Download the Oracle grid 12.2 software from edelivary.oracle.com then use your preferred way to move them to the server using scp , ftp, sftp then unzip the files:

unzip LINUX.X64_193000_grid_home.zip

if you working remotely you need to configure the display for remote connection x11 forwarding:

export DISPLAY=10.10.20.1:0.0
xhost +
# to test the x11 forwarding run below command 
xev
export CV_ASSUME_DISTID=OEL7.6

then we need to run the setup script to start the installation.

./gridSetup.sh

The installation process will start here

It will ask you to run the two scripts as root.

fist script ran from script

the second script ran from root and it will take time to finished.

Once the both scripts finished successfully you can Press OK on the dialog and continue with the installation:

close the windows

lets check the grid services

# from Grid user 
crsctl stat res -t
sqlplus -s / as sysdba
<<EOFselect instance_name from v\\$instance;EOF

Create Data and FRA disk groups

Then We need to create the DATA and FRA disk groups to install oracle database on them.

asmca

this windows will pop up

Using mouse right click on the DIsk Groups then new windows will pop up

Create Data Disk group

Create FRA Disk Group

At the end you should have like below screenshoot

check the cluster resource if the disk groups have the cluster services using below command:

crsctl stat res -t
# the restult should be 
# ora.CRS.dg    ONLINE  ONLINE       oracle                   STABLE
# ora.DATA.dg   ONLINE  ONLINE       oracle                   STABLE
# ora.FRA.dg    ONLINE  ONLINE       oracle                   STABLE

Installing Oracle DB 19c software Only

unzip the oracle database 19c zipped files in the home dir location(/u01/19c/oracle_base/oracle/db_home) :

unzip LINUX.X64_193000_db_home.zip

Go to database directory and run below command

export CV_ASSUME_DISTID=OEL7.6

./runInstaller

click install software only option then next

Single instance database installation

Enterprise Edition

check the oracle base and oracle home then click next

Check the OS groups then next

Click press Ignore then next if there is any warnings otherwise Check the sammary and Click Install

The Installation begin

ask us to run the root.sh as root user

Run the root.sh in the terminal as user

Press OK and then close the window

Create database using dbca

To create database we need to run the below command as oracle userdbca

select create new database

Select Advance Mode then next

Select General Purpose or Transaction Processing

Fill the textboxes with the required data based on your need:

Use ASM as database storage

Enable the FRA and select ASM as FRA storage

Click next

click next

Fill the data and see below screenshoot:

Deselect EM and click next:

Fill the Password and click next

Create database and next

Check the summary and Finish

once finished we can close the windows

RMAN backup strategy

Note: in this scenario I’m using the control file instead of RMAN catalog database and the RMAN control file autobackup should be enabled.

FULL Backup

I will take RMAN full backup pluse archivelog files once a week and delete the archivelog files from the disk once the successful backup finished

Archivelog Backup

I will incrementally backup the archivelog

Configure RMAN and Perform a weekly Full backup

Using oracle on Linux OS to create a backup Directory as shown below:

mkdir -p /u02/backup

Check the RMAN configuration

# This will show the default configuration show all
# check if autobackup for control file are enabled 
# We need to modify some 
configuration CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/backup/snapcf_PROD.f';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/ctr_%F';
configure channel device type disk format '/u02/backup/bkp_%U.bkp';

Need to run below script once a week use cronjob to make it weekly bases job on your Linux OS: and to backup the Database as full backup run below script:

rman target / nocatalog <<RMANrun {
allocate channel c1 device type disk format '/u02/backup/bkp1_%U';
allocate channel c2 device type disk format '/u02/backup/bkp2_%U'; 
show all;crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;delete noprompt obsolete;
sql "alter system checkpoint";
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
backup archivelog all  tag="ARC_BACKUP_20210324"  MAXSETSIZE 50M;BACKUP FILESPERSET 1 FULL DATABASE SKIP READONLY TAG='FULL_BACKUP_20210324' PLUS ARCHIVELOG NOT BACKED UP 1 TIMES TAG='ARC_BACKUP_20210324' format='/u02/backup/ARC_%T_%p.bkp';
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK;
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
}
RMAN

Simulate the archivelog daily backup

Note this should run as daily bases using cronjob on your Linux os.

Tag here for the next day after the backup taken TAG='ARC_BACKUP_20210325'

rman target / nocatalog <<RMANrun {allocate channel c1 device type disk format '/u02/backup/ARC1_%U';SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES TAG='ARC_BACKUP_20210325'  MAXSETSIZE 50M;DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK;}RMAN

-add more records @3--archive the log file @arc-- insert recods @4--archive the log file @arc

run backup for archive log files and change the tag TAG='ARC_BACKUP_20210326' for the next day

rman target / nocatalog <<RMANrun {allocate channel c1 device type disk format '/u02/backup/ARC1_%U';SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES TAG='ARC_BACKUP_20210326'  MAXSETSIZE 50M;DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK;}RMAN

using sqlplus again and make sure that you are on this location /home/oracle/Documents :

-add more records @5--archive the log file @arc-- insert recods @6--archive the log file @arc

run backup for archive log files and change the tag TAG='ARC_BACKUP_20210327' for the next day

rman target / nocatalog <<RMANrun {allocate channel c1 device type disk format '/u02/backup/ARC1_%U';SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES TAG='ARC_BACKUP_20210327'  MAXSETSIZE 50M;DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK;}RMAN

Drop/Delete DB

Using dbca we can delete the database from the system:

Restore DB from Backup

Create a dummy init file.
Startup nomount usning that dummy init file.
login to rman and restore spfile from pfile
copy the spfile to ASM
edit the pfile to point to the spfile in ASM
startup force nomount the instance to take the new spfile
login to rman again
restore the control file that contains the full backup
mount the database
restore database
recover database
startup force nomount
restore control file from last backup
mount database
recover database

restore archivelog all;
crosscheck archivelog all;
switch database to copy;
catalog start with '+DATA';
list backup of archivelog all;r
eport schema

Using grid user you need to create the below directory

alter diskgroup DATA add directory '+DATA/PROD';
alter diskgroup DATA add directory '+DATA/PROD/DATAFILE';
alter diskgroup DATA add directory '+DATA/PROD/BE20CD2F85936486E0530100007F91C9';
alter diskgroup DATA add directory '+DATA/PROD/FD9AC20F64D244D7E043B6A9E80A2F2F';
alter diskgroup DATA add directory '+DATA/PROD/BE20CD2F85936486E0530100007F91C9/DATAFILE';
alter diskgroup DATA add directory '+DATA/PROD/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE';
alter diskgroup DATA add directory '+DATA/PROD/PARAMETERFILE';
alter diskgroup DATA add directory '+DATA/PROD/TEMPFILE';
alter diskgroup DATA add directory '+DATA/PROD/BE20B9BE42B92155E0530100007FCCB4';
alter diskgroup DATA add directory '+DATA/PROD/CONTROLFILE';
alter diskgroup DATA add directory '+DATA/PROD/ONLINELOG';
alter diskgroup FRA add directory '+FRA/PROD';
alter diskgroup FRA add directory '+FRA/PROD/CONTROLFILE';
alter diskgroup FRA add directory '+FRA/PROD/ONLINELOG';
alter diskgroup FRA add directory '+FRA/PROD/ARCHIVELOG';

Add DB service

Once we finished we need to add database service to the cluster ware that will be managed through it:

srvctl add database -d PROD -a DATA,FRA -o $ORACLE_HOME -p +DATA/PROD/spfilePROD.ora
srvctl start database -d prod 
srvctl status database -d prod
# Listener status and services
lsnrctl status
lsnrctl services
# inside the sqlplus we need to open the pluggable database 
sqlplus -s / as sysdba
<<SQLFalter pluggable database prod1pdb open;SQLF

Netca

create service name on the server to be able to connect to the prod1pdb pluggable database

https://github.com/icyb3r-code/DBAdmin/tree/master/Oracle/Contents/Oracle19c_with_ASM