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;