TechExuberance: 2018

Configuring VMWARE snapshot backups for Oracle databases.

Oracle databases require 'begin backup mode' to freeze datafile headers and consistency of backups.
VEEAM is backup, replication and restore solution for Virtual machines.
We will consider understanding steps involved in running veeam backups for live Oracle database vm machines.

We will start with VMWARE tools installation steps (if not already installed). This will be done on vmware host machine.

Prepare your virtual machine to install VMware Tools.
Choose VM > Install VMware Tools.

Logon to Linux as root -
[root@clone ~]# mount /dev/cdrom /mnt/cdrom
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@clone ~]# cd /tmp
[root@clone tmp]# tar zxpf /mnt/cdrom/VMwareTools-9.0.0-782409.tar.gz
[root@clone tmp]#
[root@clone tmp]#

Install vmware tools on virtual machine -

[root@clone tmp]# cd vmware-tools-distrib
[root@clone vmware-tools-distrib]# ls -ltr
total 272
drwxr-xr-x 14 root root   4096 Jul 14 2012 lib
drwxr-xr-x 2 root root   4096 Jul 14 2012 installer
drwxr-xr-x 2 root root   4096 Jul 14 2012 bin
drwxr-xr-x 4 root root   4096 Jul 14 2012 etc
drwxr-xr-x 2 root root   4096 Jul 14 2012 doc
-rw-r--r-- 1 root root 251898 Jul 14 2012 FILES
lrwxrwxrwx 1 root root     31 Oct 29 14:27 vmware-install.pl -> ./bin/vmware-uninstall-tools.pl
lrwxrwxrwx 1 root root     13 Oct 29 14:27 INSTALL -> ./doc/INSTALL
[root@clone vmware-tools-distrib]# ./vmware-install.pl

Creating scripts to run before and after snapshots -
create /usr/sbin/pre-freeze-script
#!/bin/sh
if [ "$(id -u)" -eq "0" ]; then
exec su oraprod -c /home/oraprod/maaz/monitor/scripts/pre-freeze.sql
fi

create /usr/sbin/post-thaw-script
#!/bin/sh

if [ "$(id -u)" -eq "0" ]; then
exec su oraprod -c /home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
fi

Switch to oraprod/oracle user and create custom shell scripts scripts -

Script 1-
/home/oraprod/maaz/monitor/scripts/pre-freeze.sql

. /data/prod/apps12/db/tech_st/db/12.1.0.2/clone_clone.env

$ORACLE_HOME/bin/sqlplus "sys/sysdcf321@CLONE as sysdba" <<EOF
spool /home/oraprod/maaz/monitor/scripts/pre-freeze.log;
select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;
alter database begin backup;
spool off;
EOF

Script 2-
/home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
. /data/prod/apps12/db/tech_st/db/12.1.0.2/clone_clone.env

$ORACLE_HOME/bin/sqlplus "sys/sysdcf321@CLONE as sysdba" <<EOF
spool /home/oraprod/maaz/monitor/scripts/post-thaw.log;
select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;
alter database end backup;
spool off;
EOF

Check permissions for files with root user -

[root@clone vmware-tools-distrib]# ls -ltr /usr/sbin/pre-freeze-script
-rw-r--r-- 1 root root 122 Oct 29 14:24 /usr/sbin/pre-freeze-script
[root@clone vmware-tools-distrib]# chmod 700 /usr/sbin/pre-freeze-script
[root@clone vmware-tools-distrib]# ls -ltr /usr/sbin/pre-freeze-script
-rwx------ 1 root root 122 Oct 29 14:24 /usr/sbin/pre-freeze-script

[root@clone bin]# chown root.root /usr/sbin/post-thaw-script
[root@clone bin]# chmod 700 /usr/sbin/post-thaw-script
[root@clone bin]# ls -ltr /usr/sbin/post-thaw-script
-rwx------ 1 root root 122 Oct 29 14:27 /usr/sbin/post-thaw-script

Files with oraprod user -

[oraprod@clone scripts]$ ls -ltr /home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
-rw-r--r-- 1 oraprod proddba 282 Oct 29 14:20 /home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
[oraprod@clone scripts]$ chmod 755 /home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
[oraprod@clone scripts]$ ls -ltr /home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
-rwxr-xr-x 1 oraprod proddba 282 Oct 29 14:20 /home/oraprod/maaz/monitor/scripts/post-thaw-script.sql
[oraprod@clone scripts]$ ls -ltr /home/oraprod/maaz/monitor/scripts/pre-freeze.sql
-rw-r--r-- 1 oraprod proddba 283 Oct 29 14:19 /home/oraprod/maaz/monitor/scripts/pre-freeze.sql
[oraprod@clone scripts]$ chmod 755 /home/oraprod/maaz/monitor/scripts/pre-freeze.sql
[oraprod@clone scripts]$ ls -ltr /home/oraprod/maaz/monitor/scripts/pre-freeze.sql
-rwxr-xr-x 1 oraprod proddba 283 Oct 29 14:19 /home/oraprod/maaz/monitor/scripts/pre-freeze.sql
[oraprod@clone scripts]$

Schedule VEEAM BAckup now and check if database was put in begin backup before snapshot.
Make sure you use -quiesce option when scheduling backup in VEEAM.

Query to fetch Function assigned to which responsibility

1. Check for which function needs to be assigned to which responsibility.
2. Check for responsibility's menu
sytem administrator-> Secutiry -> responsibilty -> Define
3. Search for responsiblity (% Inventory User)
4. Get default Menu name and search for that menu
System Administrator -> Application->Menu
5.Once you get Function name, go for function short name as follows -
System Administrator -> Application->Function

Enter Function Short code for below query

SELECT frtl.responsibility_name,
       fr.responsibility_key,
       fm.menu_id,
       fm.menu_name,
       menu.function_id,
       menu.prompt,
       menu.grant_flag,
       fffv.user_function_name,
       fffv.function_name,
       fffv.TYPE
FROM (SELECT connect_by_root fmet.menu_id top_menu_id,
               fmet.menu_id                 menu_id,
               fmet.sub_menu_id,
               fmet.function_id,
               fmet.prompt ,
               fmet.grant_flag
          FROM fnd_menu_entries_vl fmet
        CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id
                         AND PRIOR fmet.prompt IS NOT NULL
                         ) menu,
       fnd_responsibility fr,
       fnd_responsibility_tl frtl,
       fnd_menus fm,
       fnd_form_functions_vl fffv
WHERE fr.menu_id = menu.top_menu_id
   AND fffv.function_id = menu.function_id
   AND fffv.TYPE <> 'SUBFUNCTION'
   AND menu.function_id IS NOT NULL
   AND menu.prompt IS NOT NULL
   AND fm.menu_id = menu.menu_id
      AND frtl.responsibility_id = fr.responsibility_id
--   AND frtl.responsibility_name LIKE '%Inventory Manager%'
   AND menu.function_id NOT IN (SELECT ffvl.function_id
                                  FROM apps.fnd_resp_functions frf,
                                       applsys.fnd_responsibility_tl frt,
                                       apps.fnd_form_functions_vl ffvl
                                 WHERE
       frf.responsibility_id = frt.responsibility_id
                                   AND frf.action_id = ffvl.function_id
                                   AND frf.rule_type = 'F'
                                   AND
           frt.responsibility_name = frtl.responsibility_name)
   AND menu.menu_id NOT IN (SELECT fmv.menu_id
                              FROM apps.fnd_resp_functions frf,
                                   applsys.fnd_responsibility_tl frt,
                                   apps.fnd_menus_vl fmv
                             WHERE
       frf.responsibility_id = frt.responsibility_id
                               AND frf.action_id = fmv.menu_id
                               AND frf.rule_type = 'M'
                               AND
       frt.responsibility_name = frtl.responsibility_name)
       and fffv.function_name like '&Function_short_code'
       and menu.grant_flag='Y'
ORDER BY frtl.responsibility_name;

NTP client configuration for Oracle Linux Servers

Maaz Khan

October 24, 2018

Version 1.0

Status:

Document Control

Revision History

Revision Number	Date	Author	Change Summary
1.0	October 24, 2018	Maaz Khan

Reviewers

Name	Role	Document Version	Date Reviewed	Comments

Approvers

Name	Role	Document Version	Date Reviewed	Comments

Contributors

Name	Role	Document Version	Comments Incorporated

About this Document

Background

This document will cover use of ntp service to synchronize time with ntp servers.

Intended Audience

This document is primarily meant for database administrators, system administrators, Team Leaders, Technical Managers, etc.

References

https://docs.oracle.com/cd/E37670_01/E41138/html/section_m5p_j1h_pp.html

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/deployment_guide/s1-checking_the_status_of_ntp

https://www.ntppool.org/zone/om

https://www.howtogeek.com/tips/how-to-sync-your-linux-server-time-with-network-time-servers-ntp/

https://www.akadia.com/services/ntp_synchronize.html

Attachments

Table of Contents

Summary-

Choosing NTP Server for DCF –

Steps to configure ntp in DCF (tested in Clone)

Shutting down services-

NTP Configuration -

Synchronizing time every day using cron scheduler –

Summary-

NTP stands for Network Time Protocol. NTP is organized in a hierarchical client-server model. Top hierarchy contains reference clocks. A reference clock also known as stratum 0 is cesium clock or a Global Positioning System (GPS) that receives time from satellites.

Attached to these machines there are the so-called stratum 1 servers (that is, stratum 0 clients), which are the top level time servers available to the Internet, that is, they are the best NTP servers available.

Following this hierarchy, the next level in the structure are the stratum 2 servers which in turn are the clients for stratum 1 servers.

The lowest level of the hierarchy is made up by stratum 16 servers.

Every server that is synchronized with a stratum n server is termed as being at stratum n+1 level.

Choosing NTP Server for DCF –

As we do not have our own NTP server, we will be using ntp server to check time on periodic basis over internet for on-prem servers which are ntp clients in this case –

pool.ntp.org project is a big virtual cluster of timeservers providing reliable easy to use NTP service for millions of clients. From list of servers across globe, this open source project has suggested below ntp server list for Oman –

server 0.asia.pool.ntp.org

server 1.asia.pool.ntp.org

server 2.asia.pool.ntp.org

server 3.asia.pool.ntp.org

These servers are in Asia zone

Steps to configure ntp (tested in Clone)

Shutting down services-

1. Shut down apps services

adstpall apps/appspwd

Manually check and make sure if all application services are down.

ps -ef | grep applmgr

2. Shut down database and listener

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Shut down ASM

[oraprod@clone ~]$ source /home/oraprod/asm.env

[oraprod@clone ~]$ crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'clone'

CRS-2673: Attempting to stop 'ora.CLONE.lsnr' on 'clone'

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'clone'

CRS-2677: Stop of 'ora.CLONE.lsnr' on 'clone' succeeded

CRS-2677: Stop of 'ora.DATA.dg' on 'clone' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'clone'

CRS-2677: Stop of 'ora.asm' on 'clone' succeeded

CRS-2673: Attempting to stop 'ora.evmd' on 'clone'

CRS-2677: Stop of 'ora.evmd' on 'clone' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'clone'

CRS-2677: Stop of 'ora.cssd' on 'clone' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'clone' has completed

CRS-4133: Oracle High Availability Services has been stopped.

[oraprod@clone ~]$

NTP Configuration -

1. Update NTP File –

vi /etc/ntp.conf

We will be using asia ntp servers to synchronize them. This is because there are 0 servers assigned for Oman and we did not find any server for UAE either.

Refer - https://www.ntppool.org/zone/om

Backup file - /etc/ntp.conf

Comment below lines -

#server 0.rhel.pool.ntp.org

#server 1.rhel.pool.ntp.org

#server 2.rhel.pool.ntp.org

Add below lines -

#### new lines for Oman ntp servers

server 0.asia.pool.ntp.org

server 1.asia.pool.ntp.org

server 2.asia.pool.ntp.org

server 3.asia.pool.ntp.org

2. Start ntpd service on Clone –

Service ntpd start

3. Stop ntpd service to synchronize time

Service ntpd stop

4. Update ntpd using ntpdate

/usr/sbin/ntpdate asia.pool.ntp.org

Synchronizing time every day using cron scheduler –

For security reasons, we have disabled internet to our server and hence real time synchronization is not possible. In such case, we will be scheduling synchronization after mid night 2 am using a script.

The script will –

1. Enable internet by adding gateway

2. Stop ntpd service

3. Run ntpdate command

4. Start ntpd service

5. Disable internet by deleting gateway

Script Details –

[root@prod3 scripts]# cat ntp_sync.sh

#!/bin/bash

route add default gw 195.1.1.8 eth0

service ntpd stop

/usr/sbin/ntpdate asia.pool.ntp.org

service ntpd start

route del default gw 195.1.1.8 eth0

exit

Post steps –

Startup services-

1. Startup asm

[oraprod@clone ~]$ crsctl start has

CRS-4123: Oracle High Availability Services has been started.

[oraprod@clone ~]$ crsctl stat res -t

----------------------------------------------------------------------------

Name Target State Server State details

----------------------------------------------------------------------------

Local Resources

----------------------------------------------------------------------------

ora.CLONE.lsnr

ONLINE OFFLINE clone STABLE

ora.DATA.dg

ONLINE ONLINE clone STABLE

ora.asm

ONLINE ONLINE clone Started,STABLE

ora.ons

OFFLINE OFFLINE clone STABLE

----------------------------------------------------------------------------

Cluster Resources

----------------------------------------------------------------------------

ora.clone.db

1 OFFLINE OFFLINE Instance Shutdown,ST

ABLE

ora.cssd

1 ONLINE ONLINE clone STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE clone STABLE

----------------------------------------------------------------------------

Manually check and make sure if all application services are down.

ps -ef | grep applmgr

2. Start database and listener

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2.1475E+10 bytes

Fixed Size 3722144 bytes

Variable Size 1543504992 bytes

Database Buffers 1.9864E+10 bytes

Redo Buffers 63385600 bytes

Database mounted.

Database opened.

[oraprod@clone ~]$ lsnrctl start CLONE

3. Start apps node

[applprod@clone ~]$ adstrtal.sh apps/appspwd

Validation-

1. Logon to Oracle Applications and validate –

2. Logon to Backend server and validate –

Issue faced -

[root@clone ~]# /usr/sbin/ntpdate pool.ntp.org

24 Oct 10:26:16 ntpdate[730]: no server suitable for synchronization found

Solution -

Make sure server is accessible to internet.