Category: database

Oracle Recovery Manager Features: BACKUP AS COPY

What is the advantage backup as copy feature in RMAN?

RMAN(Recovery Manager) utility creates backup in the form of backupsets or backup pieces by default.RMAN utility also allows other method which is similar to user managed hot backup. It can be acheived using ‘BACKUP AS COPY’ command.Using “Backup as Copy” RMAN creates backup in the form of image copies. However, we do not need to put the database in begin backup/end backup mode as in Conventional Hot Backup.

Syntax for Datafile Backup using Backup as copy:

RMAN> backup as copy datafile 1 format ‘/backup/data/datafile01.dbf’ ;



Another advantage of this feature is that it allows to create image copy of control file.

 RMAN> backup as copy current controlfile format ‘F:CntrlFile_Bkp.ctl’;  



How do we identify backups taken  as Image copies using RMAN?


Use “LIST COPY” Command. “LIST BACKUP” only gives a list of backup sets which were created using default RMAN backup Methos.

Syntax:

RMAN> LIST COPY;

How to Configure RMAN Backups into Multiple Locations?

If we need  RMAN to distribute backup sets among multiple disks on different mountpoints. We can use the below syntax to configure backup in multiple locations.

Using Allocate Channel:

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT ‘/u01/oracle/data1’; 
ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT ‘/u02/oracle/data2’; 
}

Using Configure Channel:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/%U’, ‘/u02/%U’;


Oracle RMAN (Recovery Manager) Concepts

What is the difference between obsolete and expired Backup in RMAN?

Obsolete Backup: Backup pieces or backup sets which are not needed for recovery.
We can use the REPORT OBSOLETE command to list the obsolete backups and DELETE OBSOLETE command to delete the obsolete backup.

Expired Backup:When the CROSSCHECK command is used to determine whether backups recorded in the repository still exist on disk or tape, if RMAN cannot locate the backups, then it updates their records in the RMAN repository to EXPIRED status.We can then use the DELETE EXPIRED command to remove records of expired backups from the RMAN repository.


Oracle DBA Concepts: Background Processes in Oracle ASM (Automatic storage Management)

The following background processes are an integral part of Automatic Storage Management:


ARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.


ASMB runs in a database instance that is using an ASM disk group. ASMB communicates with the ASM instance, managing storage and providing statistics. ASMB can also run in the ASM instance. ASMB runs in ASM instances when the ASMCMD cp command runs or when the database instance first starts if the SPFILE is stored in ASM.


GMON maintains disk membership in ASM disk groups.


MARK marks ASM allocation units as stale following a missed write to an offline disk. This essentially tracks which extents require resync for offline disks.


RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.


How to change Rebalance Power in ASM Instances?

Whenever a new diskgroup is added or dropped, ASM automatically performs REBALANCING operation.Power used by rebalacing defaults to value specified for asm_power_limit initialization parameter.We can check the status of rebalancing from v$asm_operation view.

Increasing the value of ASM_POWER_LIMIT, reduces the estimated time for completion of Rebalance Operation.

ASM power limit can be increased using the below command

alter system set asm_power_limit =11;

To know more about values for this parameter, check this link:

http://www.appsdbadiaries.com/2017/06/asm-features-rebalancing.html


But this does not increase the rebalancing power of current operation. 

To increase the rebalancing power of ongoing operation, use the below command

 alter diskgroup dg1 rebalance power 11;

We can also specify rebalance power at the time of adding or dropping disks.

alter diskgroup dg1 add disk d01 rebalance power 11;


When to use asm_power_limit 0?

Whenever there is a need to add/drop several disks (like migrations), setting the power limit to 0 is highly beneficial.We can avoid the waiting time for rebalance operation to complete,before the next disk is rebalanced.In such cases, set the asm_power_limit to 0, add/drop all the disks and set the value of asm_power_limit to a non zero value.
Rebalance operation will be parallelized and completes faster.


Oracle Database ASM Features: REBALANCING

ASM has the ability to rebalance data across the disks whenever a disk is added,dropped or replaced.
The performance of rebalance operation is controlled by initialization parameter ASM_POWER_LIMIT.


ASM_POWER_LIMIT Parameter


Default value 1


Range of Values 0 to 11 (Prior to 11gR2)


Range of Values 0 to 1024(From 11gR2)


For 10g Databases:


SQL> alter diskgroup DG1 rebalance power 12;
alter diskgroup DG1 rebalance power 12
                                              *
ERROR at line 1:
ORA-15102: invalid POWER expression


SQL> alter diskgroup DG1 rebalance power 11;


Diskgroup altered.




For 11g AND 12c Databases


SQL> alter diskgroup DG1 rebalance power 1025;
alter diskgroup DG1 rebalance power 1025
                                      *
ERROR at line 1:
ORA-15102: invalid POWER expression


SQL> alter diskgroup DG1 rebalance power 1024;


Diskgroup altered.


The higher the value of rebalancing power, faster would be rebalancing operation.

Initialization Parameters Required for Oracle ASM (Automatic Storage Management)

The below parameters are required for ASM instance.


1. INSTANCE_TYPE 


INSTANCE_TYPE specifies whether the instance is a database instance or an Automatic Storage Management instance.


Values:


RDBMS


The instance is a database instance.


ASM


The instance is an Automatic Storage Management instance.




2. DB_UNIQUE_NAME 


DB_UNIQUE_NAME specifies a globally unique name for the database


Default value
Database instances: the value of DB_NAME
Automatic Storage Management instances: +ASM


3. ASM_POWER_LIMIT 


ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.


Default value 1


Range of Values 0 to 11 (Prior to 11gR2)

Range of Values 0 to 1024(From 11gR2)






4. ASM_DISKGROUPS 


ASM_DISKGROUPS specifies a list of names of disk groups to be mounted by an Automatic Storage Management instance at instance startup or when an ALTER DISKGROUP ALL MOUNT statement is issued.


Automatic Storage Management (ASM) automatically adds a disk group to this parameter when the disk group is successfully created or mounted, and automatically removes a disk group from this parameter when the disk group is dropped or dismounted.


Issuing the ALTER DISKGROUP…ALL MOUNT or ALTER DISKGROUP…ALL DISMOUNT command does not affect the value of this parameter.


5.ASM_DISKSTRING 


ASM_DISKSTRING specifies an operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. When a new disk is added to a disk group, each Automatic Storage Management instance that has the disk group mounted must be able to discover the new disk using the value of ASM_DISKSTRING.


6. ASM_PREFERRED_READ_FAILURE_GROUPS 


ASM_PREFERRED_READ_FAILURE_GROUPS specifies the failure groups that contain preferred read disks. Preferred disks are instance specific. This parameter is only valid in ASM instances.


SQL Profile and SQL Plan Baseline

What is SQL Profile?


A SQL profile contains corrections for poor optimizer estimates discovered by the SQL Tuning advisor.A  SQL profile is to a SQL statement what statistics are to a table or index.
Implementing sql profiles allows us to replace bad sql execution plan with good execution plan.





What is SQL Plan Baseline?


A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved.