Performance Management

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

• Configure the Resource Manager
• Tune SQL statements
• Use real application testing
• Manage SQL Plan baselines
• Capture performance statistics
• Tune an instance
• Configure and manage result cache
• Manage extended statistics
• Create and manage partitioned indexes
• Control CPU use for Oracle Instances
• Configure and manage “In Memory” features

Go back to OCM 12c Preparation Project 

Thank you for visiting this blog 🙂

Advertisement

Data Management

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

• Manage additional buffer cache
• Optimize space usage for the LOB data
• Manage an encrypted tablespace
• Manage schema data
• Manage partitioned tables
• Set up Fine-Grained Auditing
• Configure the database to retrieve all previous versions of the table rows

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.

• Manage database links
• Manage a fast refreshable materialized view
• Create a plug-in tablespace by using the transportable tablespace feature
• Optimize star queries
• Configure parallel execution
• Apply a Patch
• Configure Automatic Data Optimization, In-Row Archiving, and Temporal Validity
• Manage external tables

Go back to OCM 12c Preparation Project 

Thank you for visiting this blog 🙂

Manage Database Availability

Manage Database Availability

• Install the EM Cloud Control agent
• Configure recovery catalog
• Configure RMAN
• Perform a full database backup
• Configure and monitor Flashback Database

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

• Create and manage pluggable databases
• Administer users, roles, and privileges
• Configure the network environment to allow connections to multiple databases
• Administer database configuration files
• Configure shared server
• Manage network file directories

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:)

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

— hiteshgondalia (@hiteshgondalia) November 11, 2016

@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

Set PASSWORD UNLIMITED for PDB Application Schema

SQL> SELECT dba_users.USERNAME,
dba_users.ACCOUNT_STATUS,
dba_users.EXPIRY_DATE
FROM dba_users
WHERE ACCOUNT_STATUS=’OPEN’;
2 3 4 5
USERNAME ACCOUNT_STATUS EXPIRY_DA
——————————————————————————————————————————– ——————————– ———
NETVERTEXBLR OPE 17-APR-17
PDBADMIN OPE 17-MAR-17
ELITELDAPBLR OPE 21-MAR-17
ELITEPMRO OPE 13-APR-17
ELITEPROXYBLR OPE 17-APR-17
SYSTEM OPE 17-MAR-17
SYS OPE 17-MAR-17

7 rows selected.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT dba_users.USERNAME,
dba_users.ACCOUNT_STATUS,
dba_users.EXPIRY_DATE
FROM dba_users
WHERE ACCOUNT_STATUS=’OPEN’; 2 3 4 5

USERNAME ACCOUNT_STATUS EXPIRY_DA
——————————————————————————————————————————– ——————————– ———
NETVERTEXBLR OPEN
PDBADMIN OPEN
ELITELDAPBLR OPEN
ELITEPMRO OPEN
ELITEPROXYBLR OPEN
SYSTEM OPEN
SYS OPEN

7 rows selected.

SQL>

If you want to remove password expiry date for your schema then you have to use “ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;”

If you want to remove password expiry date for your schema then you have to use "ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;"

— hiteshgondalia (@hiteshgondalia) November 11, 2016

Production Experience with 12.1.0.2.0 Active Data guard

Oracle Community Thread/Issues

12.1.0.2.0 RAC to RAC Dataguard broker Switchover back is failed with ORA-01017 https://community.oracle.com/message/1401658

DGMGRL – FSFO Configuration with Maximum Performance Mode https://community.oracle.com/thread/3982304

Failover Test cases of FSFO – Observer configuration with Dataguard https://community.oracle.com/thread/3903016

Data Gaurd Sync Issue after creation of datafile in 12.1.0.2   https://community.oracle.com/thread/3980984

Oracle Database 12.1.0.2 Service’s Unexpected Behaviour   https://community.oracle.com/thread/3985260

 

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Fixing it on primary database

SQL> alter system set log_archive_dest_2=” scope=both;

System altered.

## Fixing this on our standby terminal

SQL>  alter system set log_archive_dest_2=” scope=both;

System altered.

http://karandba.blogspot.in/2014/10/far-sync-in-oracle-12c-database.html