Oracle expdp tablespace mode

In my first expdp article, I have explained how to use expdp as sysdba and also other options such as nohup, excluding schemas, compression etc.  In this article, I will give you an example of expdp in tablespace mode. Traditionally, when one wants to take a schema backup, one uses expdp always as it is a logical backup.  For tablespace backups, most of the times DBAs take physical backups of tablespaces instead of using expdp.

However expdp too provides the tablespace option.  Infact, several tablespaces can be backed up at once using expdp. Also one can combine other options such as parallel,compression etc.  The below command is one such example.

expdp TABLESPACES=FINTS_DATA,HRTS_DATA dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp LOGFILE=DATA_PUMP_DIR:ts_dumps_log.log    parallel=2    filesize=20G   compression=ALL

In the above example, expdp takes a backup of the contents of FINTS_DATA and HRTS_DATA. The expdp also runs quickly because of the parallel=2 option (provided there are more CPUs available in the database server). 

Furthermore, the filesize option above specifies that each dumpfile generated should not be more than 20G in size.  Oracle will create a second dumpfile if the first dumpfile's size already reaches the 20G threshold.  The %U in the dumpfile name guarantees uniqueness in the name.  

The compression option is a new feature in Oracle 11g. It compresses the output dumpfile by almost a ratio of 1:10 approximately. This is a great feature if the tablespaces have bigger sizes and one wants to save space in the DATA_PUMP_DIR directory for their exported dumpfiles.  Also, during a subsequent impdp process, Oracle automatically decompresses these dumpfiles during the data load, requiring no additional impdp option.  

RMAN Uncatalog using DBMS_BACKUP_RESTORE

Introduction

Most of the Oracle DBAs just rely on the command line RMAN interface ( $ORACLE_HOME/bin/rman ) to perform the rman backup and recovery commands. Every rman backup file (backup piece) generated is also called a backup piece handle. Some of the rman operations can be performed via the little documented DBMS_BACKUP_RESTORE package also and it turns out to be a quite useful in some cases such as when one wants  to uncatalog many RMAN backup pieces at once.

Problem

The other day I was transferring my RMAN backup pieces from one server to another server to create a clone database using rman restore and recovery commands. Due to some transfer issue, all the backup pieces (files) generated on a given day were corrupt during the transfer. Because Oracle Flash Recovery Area (FRA) was used, all the backup pieces were transferred to the FRA area. Some of you already know that FRA has a nicely laid out directory structure with one of the subdirectories containing the date the backup piece was generated.

For e.g /backup/flash_recovery_area/APPSDB/backupset/2011_02_15/o1_mf****_.bkp. Note the date 2011_02_15 in the directory path.

I already cataloged these backup pieces together using the catalog start with '/backup/flash_recovery_area/APPSDB/backupset/2011_02_15/' noprompt command.

Because these files were corrupted during the transfer process, the rman recovery failed. I fixed the problem using the below approach.

1) spool uncatalog_files.sql

2) set lines 200

3)

select 

'exec sys.dbms_backup_restore.deleteBackupPiece('||recid||','||stamp||','''||handle||''','||set_stamp||','||set_count||','||piece#||');'
from v$backup_piece 

where handle like '%2011_02_15%';

4) exit

5) Now run the uncatalog_file.sql file

The above dbms_backup_restore.deleteBackupPiece procedure removes the backup metadata about these corrupted files and also physically removes these files from the disk. Now transfer the backup pieces again from the source server to the destination server, catalog them again and retry your recovery. This time it must be successful.

Conclusion

One can also use the CHANGE backuppiece '#backup piece file name#' UNCATALOG command. But if there are dozens of backup pieces to be uncataloged all at once, the above dynamic generation of the uncatalog commands using dbms_backup_restore.deleteBackupPiece and v$backup_piece can be used.

Oracle ASM disk failure - Part 2

Introduction

In Part 1, I wrote about a scenario when ASM detects READ_ERRS/WRITE_ERRS and updates these columns in v$asm_disk for the ASM disk. The DBA has to explicitly drop the disk in ASM. This article is about a different scenario when ASM instance itself performs the 'drop disk' operation.

This post assumes that you are using ASM redundancy (Normal or High) and that you are not using ASMLib program. The commands and syntax could be different if you are using ASMLib.

Scenario

In this scenario, ASM drops the disk automatically. Furthermore, the READ_ERRS/WRITE_ERRS in v$asm_disk could be showing a value of NULL (instead of an actual count of READ or WRITE errors noticed).

How to identify the failed disk

Unlike scenario 1 discussed in Part 1 of the ASM series, ASM instance can initiate the 'drop disk' by itself in some situations. Let the failed disk be '/dev/sds1'.

select path
from v$asm_disk
where read_errs is NULL;

/dev/sds1

select path
from v$asm_disk
where write_errs is NULL

/dev/sds1

Additionally, the HEADER_STATUS in v$asm_disk returns a value of UNKNOWN.

select mount_status,header_status,mode_status,state
from v$asm_disk
where path = '/dev/sds1';

CLOSED UNKNOWN ONLINE NORMAL

Compare this scenario with that of the scenario mentioned in Part 1, when the HEADER_STATUS is still shown as MEMBER and the READ_ERRS/WRITE_ERRS has a value > 0.

The following are the errors mentioned in the +ASM alert log file when the failure was first noticed.

WARNING: initiating offline of disk
NOTE: cache closing disk
WARNING: PST-initiated drop disk

ORA-27061: waiting for async I/Os failed
WARNING: IO Failed. subsys:System dg:0, diskname:/dev/sds1


No "drop disk" command required by DBA

The disk is already dropped by ASM instance. There is no need of an "alter diskgroup ...drop disk" command again. Instead the DBA has to work with the system administrator and physically locate the failed disk in the disk enclosure and remove it.Add the replacement disk

1)Get the replacement/new device name, partition it and change ownership to the database owner. For example let the disk path after partitioning be /dev/sdk12)select distinct header_status from v$asm_disk where name = '/dev/sdk1'; (Must show as CANDIDATE)

3)alter diskgroup #name# add disk '/dev/sdk1';

4)ASM starts the re-balancing operation due to the above disk add command. One can monitor the progress of the re-balance operation by checking v$asm_operation.

select state,power,group_number,EST_MINUTES
from v$asm_operation;

After a few min/hours the above gets completed (no rows returned)

5)The disk add operation is now considered complete.
How to decrease the ASM re-balance operation time

While the above ASM re-balancing operation is in progress, the DBA can let it complete quickly by changing 'ASM power' by running the below command for example.

alter diskgroup #name# rebalance power 8;

The default power is 1 (i.e ASM starts one re-balance background process to handle the re-balancing work, called ARB process). The above command dynamically starts 8 ARB processes (ARB0 to ARB7), which can dramatically decrease the time to re-balance. The maximum power limit in 11g R1 is 11 (upto 11 ARB processes can be started).

Conclusion

I am not exactly sure why ASM shows the status of a failed disk in different ways, but these are two scenarios that I aware of so far.

None of the above maintenance operations (faile disk removal from the disk enclosure, new disk add) causes a downtime to the end user and therefore can be completed during normal business hours. The re-balance operation can cause slight degradation of performance and hence increase the power limit to let it complete quickly.

Oracle ASM disk failure - Part 1

Introduction

Oracle Automatic Storage Management (ASM) was introduced in Oracle 10g. ASM provides advance storage management features such as DISK I/O re-balancing, volume management and easy database file name management. It also can provide MIRRORING of data for high availability and redundancy in the event of a disk failure (Mirroring is optional). ASM guarantees that data extents (table,index row data etc.) in one disk are mirrored in another disk (normal redundancy) and in two disks (high redundancy).

A few times I have faced ASM disk failures when redundancy (mirroring) was enabled and none of them resulted in an issue for an end user. ASM automatically detects the disk failure and services Oracle SQL requests by retrieving information from the mirrored (other) disk. Such a failure is handled gracefully and entirely managed by Oracle. I am very impressed by the fault tolerance capability in ASM.

But soon the Oracle DBA must work with the system administrator to replaced the failed disk. If the mirrored disk also fails before the replacement, then Oracle SQL by end users will error because both the primary and mirrored disks have failed.

This post assumes that you are using ASM redundancy (Normal or High) and that you are not using ASMLib program. The commands and syntax could be different if you are using ASMLib.

How to identify a failed disk

An ASM disk failure as noted below is transparent to end users and one can be caught unaware if one is not proactive in database monitoring. The DBA can write a program that constantly checks the database alert logfile or a SQL script that checks for any read/write errors.

If either of the below queries return rows, then it is confirmed there are one or more ASM disks that have failed.

select path,name,mount_status,header_status
from v$asm_disk
where WRITE_ERRS > 0

select path,name,mount_status,header_status
from v$asm_disk
where READ_ERRS > 0;

But despite the read/write errors, the header_status column value may still be shown as "MEMBER".

Drop the failed disk

1) alter diskgroup #name# drop disk #disk name#;

Caution: Do NOT physically remove the failed disk YET from the disk enclosure of the server. The above command is executed immediately, but ASM also starts a lengthy re-balance operation. The disk should be physically removed only after the header_status for the failed disk becomes FORMER. This status is set after the re-balance operation is completed. One can monitor the progress of the re-balance operation by checking v$asm_operation.

state,power,group_number,EST_MINUTES
from v$asm_operation;

After a few min/hours the above operation will get completed (no rows returned). Then verify that the header_status is now FORMER and then request the System Administrator to physically remove the disk from the disk enclosure. The LED light for the failed disk should get turned off and this indicates the physical location of the failed disk in the enclosure.

Add the replacement disk

1) Get the replacement device name, partition it and change ownership to the database owner. For example let the disk path after partitioning be /dev/sdk1
2) select distinct header_status from v$asm_disk where name = '/dev/sdk1'; (Must show as CANDIDATE)

3) alter diskgroup #name# add disk '/dev/sdk1';
4) ASM starts the re-balancing operation due to the above disk add command. One can monitor the progress of the re-balance operation by checking v$asm_operation.

select state,power,group_number,EST_MINUTES
from v$asm_operation;

After a few min/hours the above gets completed (no rows returned)

5) The disk add operation is now considered complete.

How to decrease the ASM re-balance operation time

While the above ASM re-balancing operation is in progress, the DBA can let it complete quickly by changing 'ASM power' by running the below command for example.

alter diskgroup #name# rebalance power 8;

The default power is 1 (i.e ASM starts one re-balance background process to handle the re-balancing work, called ARB process). The above command dynamically starts 8 ARB processes (ARB0 to ARB7), which can dramatically decrease the time to re-balance. The maximum power limit in 11g R1 is 11 (upto 11 ARB processes can be started).

Conclusion

None of the above maintenance operations (disk drop, disk add) causes a downtime to the end user and therefore can be completed during normal business hours. The re-balance operation can cause slight degradation of performance and hence increase the power limit to let it complete quickly.

Database Administrator : Top Ten Best Jobs in America

CNN.com has released the BEST JOBS in AMERICA report for 2011. Database Administrator again stands in the TOP TEN. It is currently placed at No.8

Here is the LINK for more information

http://money.cnn.com/magazines/moneymag/best-jobs/2011/fast-growing-jobs/8.html

This is the 3rd time in a row that Database Administrator has been among the TOP TEN in DEMAND jobs in USA.