Performance Management

I would like to share my learning experience of Oracle Database Performance Management. This article also covered skillset5 for OCM12c Certification Exam.

Go back to OCM 12c Preparation Project 

Thank you for visiting this blog 🙂

Data Management

I would like to share my learning experience of Oracle Data Management . This article also covered skillset4 for OCM12c Certification Exam.

Go back to OCM 12c Preparation Project 

Thank you for visiting this blog 🙂

Data Warehouse Management

I would like to share my learning experience of how to manage the Data Warehouse. This article also covered  skillset3 for OCM12c Certification Exam.

Go back to OCM 12c Preparation Project 

Thank you for visiting this blog 🙂

OCM 12c Preparation for General Database and Network Administration

General Database and Network Administration
Go back to OCM 12c Preparation Project 

Thank you for visiting this blog 🙂

Disabling Transparent HugePages in RHEL7.x

Environment
[root@lab-12cr2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)

Runtime Disable THP
Append below lines in /etc/rc.local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
Make sure that rc.local is running on reboot
chmod +x /etc/rc.d/rc.local
systemctl enable rc-local
systemctl status rc-local

Check the behavior on post reboot the operating system

[root@lab-12cr2 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@lab-12cr2 ~]#
[root@lab-12cr2 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
[root@lab-12cr2 ~]#
[root@lab-12cr2 ~]# grep -i HugePages_Total /proc/meminfo
HugePages_Total: 0
[root@lab-12cr2 ~]# cat /proc/sys/vm/nr_hugepages
0
[root@lab-12cr2 ~]# grep -i AnonHugePages /proc/meminfo
AnonHugePages: 2048 kB

Permanent Disable THP
Oracle Linux 7 is similar, but uses GRUB2 so you need to edit the “/boot/grub2/grub.cfg” file using the grubby command.
[root@lab-12cr2 ~]# grubby –default-kernel
/boot/vmlinuz-3.10.0-123.el7.x86_64
[root@lab-12cr2 ~]# grubby –args=”transparent_hugepage=never” –update-kernel /boot/vmlinuz-3.10.0-123.el7.x86_64
[root@lab-12cr2 ~]# grubby –info /boot/vmlinuz-3.10.0-123.el7.x86_64
index=0
kernel=/boot/vmlinuz-3.10.0-123.el7.x86_64
args=”ro rd.lvm.lv=rhel/root crashkernel=auto rd.lvm.lv=rhel/swap vconsole.font=latarcyrheb-sun16 vconsole.keymap=us rhgb quiet LANG=en_US.UTF-8 transparent_hugepage=never”
root=UUID=8190d97a-a2e9-44cd-9810-804def1d6f89
initrd=/boot/initramfs-3.10.0-123.el7.x86_64.img
title=Red Hat Enterprise Linux Server, with Linux 3.10.0-123.el7.x86_64
[root@lab-12cr2 ~]#

[root@lab-12cr2 ~]# grep -i “transparent_hugepage=never” /boot/grub2/grub.cfg
linux16 /vmlinuz-3.10.0-123.el7.x86_64 root=UUID=8190d97a-a2e9-44cd-9810-804def1d6f89 ro rd.lvm.lv=rhel/root crashkernel=auto rd.lvm.lv=rhel/swap vconsole.font=latarcyrheb-sun16 vconsole.keymap=us rhgb quiet LANG=en_US.UTF-8 transparent_hugepage=never
[root@lab-12cr2 ~]# grep -i AnonHugePages /proc/meminfo
AnonHugePages: 0 kB
[root@lab-12cr2 ~]#
[root@lab-12cr2 ~]# grep -i HugePages_Total /proc/meminfo
HugePages_Total: 0
[root@lab-12cr2 ~]# cat /proc/sys/vm/nr_hugepages
0

Reference
Oracle MetaLink ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels (Doc ID 1557478.1)

https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64#disabling-transparent-hugepages

http://docs.oracle.com/database/122/LADBI/disabling-transparent-hugepages.htm#LADBI-GUID-02E9147D-D565-4AF8-B12A-8E6E9F74BEEA

Remove Extra member from standby redo group

Tips , Reset below parameters before create standby redo group
db_create_online_log_dest_1=+DATA1 and db_create_online_log_dest_2=+DATA2

If you have set these parameters and you create standby redo log then it will be multiplexed in +DATA1 and +DATA2, Not recommended in Prod:)

@Primary
SQL> show parameter standby_file_management=MANUAL;
SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_5.319.922465363’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_6.310.922465367’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_7.311.922465371’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_8.299.922465375’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_9.872.922465379’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_10.874.922465383’;

Database altered.

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_2.305.922465353
+ORADATA/BLRDB/ONLINELOG/group_2.622.922465353
+ORADATA/BLRDB/ONLINELOG/group_1.307.922465349
+ORADATA/BLRDB/ONLINELOG/group_1.306.922465351
+ORADATA/BLRDB/ONLINELOG/group_3.620.922465355
+ORADATA/BLRDB/ONLINELOG/group_3.323.922465357
+ORADATA/BLRDB/ONLINELOG/group_4.322.922465359
+ORADATA/BLRDB/ONLINELOG/group_4.320.922465361
+ORADATA/BLRDB/ONLINELOG/group_5.313.922465365
+ORADATA/BLRDB/ONLINELOG/group_6.301.922465369
+ORADATA/BLRDB/ONLINELOG/group_7.302.922465373

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_8.300.922465377
+ORADATA/BLRDB/ONLINELOG/group_9.873.922465381
+ORADATA/BLRDB/ONLINELOG/group_10.875.922465385
+ORADATA/BLRDB/ONLINELOG/group_11.1351.927649007

15 rows selected.

SQL> show parameter standby_file_management=AUTO scope=both;
SQL> ALTER SYSTEM SET standby_file_management=AUTO scope=both;

System altered.

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_2.305.922465353
+ORADATA/BLRDB/ONLINELOG/group_2.622.922465353
+ORADATA/BLRDB/ONLINELOG/group_1.307.922465349
+ORADATA/BLRDB/ONLINELOG/group_1.306.922465351
+ORADATA/BLRDB/ONLINELOG/group_3.620.922465355
+ORADATA/BLRDB/ONLINELOG/group_3.323.922465357
+ORADATA/BLRDB/ONLINELOG/group_4.322.922465359
+ORADATA/BLRDB/ONLINELOG/group_4.320.922465361
+ORADATA/BLRDB/ONLINELOG/group_5.313.922465365
+ORADATA/BLRDB/ONLINELOG/group_6.301.922465369
+ORADATA/BLRDB/ONLINELOG/group_7.302.922465373

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_8.300.922465377
+ORADATA/BLRDB/ONLINELOG/group_9.873.922465381
+ORADATA/BLRDB/ONLINELOG/group_10.875.922465385
+ORADATA/BLRDB/ONLINELOG/group_11.1351.927649007

15 rows selected.

SQL>
SQL> alter system switch logfile;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1

@DR

alter system set standby_file_management=MANUAL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_5.319.922465363’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_6.310.922465367’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_7.311.922465371’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_8.299.922465375’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_9.872.922465379’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_10.874.922465383′;

Database altered.

Standby redo logs without multiplexting:-

SQL> SELECT MEMBER FROM V$LOGFILE WHERE TYPE=’STANDBY’;

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_5.313.922465365
+ORADATA/BLRDB/ONLINELOG/group_6.301.922465369
+ORADATA/BLRDB/ONLINELOG/group_7.302.922465373
+ORADATA/BLRDB/ONLINELOG/group_8.300.922465377
+ORADATA/BLRDB/ONLINELOG/group_9.873.922465381
+ORADATA/BLRDB/ONLINELOG/group_10.875.922465385

SQL> SELECT GROUP#,THREAD#,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# STATUS
———- ———- ———-
5 1 UNASSIGNED
6 1 UNASSIGNED
7 1 UNASSIGNED
8 2 UNASSIGNED
9 2 UNASSIGNED
10 2 UNASSIGNED