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

In-Database Archiving

Companies may need to keep data for longer time for compliance purposes even there is not use these data for companies. In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.

Continue reading

Temporal Validity in 12c

Temporal Validity enables you to track time periods for real world validity. Valid times can be set by users and applications for data, and data can be selected by a specified valid time, or a valid time range.

Concepts that are integral to valid time temporal modeling include:

  • Valid time: This is a user-defined representation of time. Examples of a valid time include project start and finish dates, and employee hire and termination dates.
  • Tables with valid-time semantics: These tables have one or more dimensions of user-defined time, each of which has a start and an end.
  • Valid-time flashback queries: This is the ability to do as-of and versions queries using a valid-time dimension.
Continue reading

Clone Virtual Machine

This is always good to have a golden copy of your system. Then you don’t need to spend time to build your environment. For example, you can clone a VM after installed OS and configure network and prerequisites. Within couple of minutes you VM will be ready for any kind of R&D.

Continue reading

Oracle Data Pump (expdp, impdp) in Oracle Database

The Data Pump feature had been introduced from Oracle 10g. Until Oracle 9i, There were a tradition export and import (exp, imp) tools for logical backup. Nowadays Data Pump (expdp & impdp) utilities are mostly use by oracle DBA.

Data Pump is very useful tools to migrate database in difference version of Oracle databases or partial refresh (full database backup / restore also is the option) from prod to non-prod. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Continue reading