Configure Oracle 12c RAC to RAC Data Guard (DR) on Linux

We will discuss how to configure RAC to RAC Data Guard on Linux. If you follow all below steps you will able to configure the two node (or multi node) DR.

Overview:

Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.

Continue reading

Change Archive Log Mode in Oracle Database

In this topic, I will show how to change Archive Log Mode in Oracle Database.

Enable Archive Log Mode in RAC:

Archive Log Status:

Continue reading

Install Oracle Software in Silent Mode

All kind of installations and configurations for Oracle RAC or Database, can be performed either using GUI or Silent mode. GUI mode is more easier as it shows all option (with details) step by step but it may not possible for all the cases or may not support on environment or may need to install bulk number of databases. So we can use all oracle provided tools/utility with silent mode which is more faster and you will able to do by using putty only.

Benefits to use oracle tool/utility in silent mode:

  • Extra Software is not required to run with GUI mode
  • Faster and minimal user interaction
  • Run as script by nohup or cron job so no Internet issue between server and client
Continue reading

Creating a new file system for an existing Linux virtual machine

This article provides steps to create a new file system for an existing Linux virtual machine.

Create a partition on new disk:

Continue reading

Database Backup Script using RMAN

A backup is a copy of data. This copy can include important parts of the database, such as the control file and datafiles. A backup is a safeguard against unexpected data loss and application errors. If you lose the original data, then you can reconstruct it by using a backup. So taking database backup is one of the most important task for DBA.

Continue reading

Automatic Storage Management (ASM)

In this topic, I will discuss how to manage ASM in Oracle including following items.

  • Important queries to manage or monitor ASM.
  • Create ASM DISKGROUP
  • Manage Existing Diskgroup (add / drop disk)
  • Tuning I/O Performance in Oracle ASM
  • Mapping ASM Disk with Physical DISK

ASM REDUNDANCY Level:

  • NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
  • HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups.
  • EXTERNAL REDUNDANCY – In this configuration there are only primary extents and no mirrored extents.
Continue reading

Oracle Database 12c R1 (12.1) RAC installation

Oracle RAC (Real Application Cluster) is used for complex application to increase database performance, high availability and scalability.

Below high level steps will perform to configure / install Oracle RAC:

  • Configure 2 Virtual Machine (VM) for 2 Nodes RAC
  • Install Oracle Linux 5.7
  • Install GI (Grid Infrastructure)
  • Configure ASM
  • Install Oracle Database Binary
  • Create Database on RAC
Continue reading

Create and Manage Partition Table in Oracle

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. 

Continue reading

USABLE_FILE_MB is showing negative values in V$ASM_DISK View

[grid@ocmnode1 ~]$ asmcmd lsdg -g OCR
Inst_ID State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
2 MOUNTED NORMAL N 512 4096 1048576 12282 3143 3614 -471 0 N OCR/
1 MOUNTED NORMAL N 512 4096 1048576 12282 3143 3614 -471 0 N OCR/
SQL> select name, os_mb, failgroup from v$asm_disk where name like 'OCR%';

NAME                                OS_MB FAILGROUP
------------------------------ ---------- ------------------------------
OCRDISK1                             4094 OCRDISK1
OCRDISK2                             4094 OCRDISK2
OCRDISK3                             4094 OCRDISK3

REQUIRED_MIRROR_FREE_MB value in V$ASM_DISKGROUP indicates how much free space is required in an ASM disk group to restore redundancy after failure of an ASM disk or ASM failure group.

USABLE_FILE_MB value indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

We need 3614 MB space if a disk of Diskgroup fails. But available free space is 3143 / 3 = 1047 MB (Normal Redundancy for OCR) .
We have shortage around 3143 – 3614 = -471 MB which means Normal Redundancy can not maintained for this Diskgroup after ASM Failgroup failure.

Reason:
– 3 Disks each 4 GB results that we can use for Normal redundancy : (3 x 4 GB ) / 3 = 4 GB
– if we have a disk failure we need 4.1 Gbyte space but we only have 3.53 GM available to maintain 3-way mirror.
For this reason, Usable_file_MB is showing a negative value.

Solution:
Add disk to the Diskgroup.

ASM Calculation:
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / [2|3]

Configure Resource Manager on Container Database

The following issues DBAs may face in oracle databases but we can overcome these issues by using Resource Manager:

  • Excessive overhead: Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
  • Inefficient scheduling: The operating system deschedules database servers while they hold latches, which is inefficient.
  • Inappropriate allocation of resources: The operating system distributes resources equally among all active processes and cannot prioritize one task over another.
  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Resource Plan: In CDB or Non-CDB Database, Resource plan controls allocation of CPU and parallel execution servers. In a PDB, it is used to specify how the resources are to be distributed among the consumer groups in that specific PDB. Several resource plans might be created, but only one will be active at a time.

Consumer Group: It is a set of sessions that are grouped based on resource requirements. When a session is created in the database, it is automatically mapped to a consumer group. There are two default consumer groups in the database: SYS_GROUP and OTHER_GROUPS.

Continue reading