Webinar: Why Oracle Data Guard and its Benefits

Do you want to know Oracle Data Guard Architecture and its benefits and how you can protect your database from Disaster Recovery? Join the webinar

Date and time: Monday, Aug 15th 2011 8:00am - 9:00am PST

To register for this event send an email to SatishbabuGunukula@gmail.com

Please find the presentation link
http://www.slideshare.net/satishbabugunukula/data-guard-architecture-setup

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

How to recover Undo or Rollback Tablespace

While handing with undo tablespace you need to be extra cautious due to active transactions in the undo segments. You need to follow different approach depend upon scenario.

Scenario 1: Undo/rollback datafile damaged or dropped accidently when database is up
In this scenario the lost or damaged datafile may contain the active transactions and you cannot able to offline or drop the undo/rollback datafile.

• Startup mount
SQL> STARTUP MOUNT

• Check the status of the datfile
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
If the datafile is offline you must bring the datafile online before you recover

• Restore and Recover the datafile
$ rman TARGET / CATALOG rman/*****@rman
RMAN> Restore datafile 'fullpath_and_filename'
RMAN> Recover datafile 'fullpath_and_filename'

• Open the database.
SQL> ALTER DATABASE OPEN;

Scenario 2: Undo/rollback datafile damaged or dropped accidently when database is down

• If using automatic UNDO_MANAGMENT then comment out the parameter in init.ora file. If using rollback segments then comment out ROLLBACK_SEMGNETS parameter

• Mount the database in restricted mode
SQL> STARTUP RESTRICT MOUNT

• Offline the undo or rollback datafile and drop the file
SQL> ALTER DATABASE DATAFILE 'fullpath_and_filename' OFFLINE DROP;

• Open the database and drop the UNDO tablespace or the tablespace which contains the rollback segments
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

• Recreate the undo tablespace. If you are using rollback segments, recreate the rollback segment tablespace and rollback segments. Make sure to bring the rollback segments online.

If using Undo tablespace
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '/oradata/undotbs2_01.dbf' SIZE 100M REUSE AUTOEXTEND ON;

If using rollback segment tablespace
SQL> CREATE TABLESPACE rollback_tbs DATAFILE '/oradata/rollback_tbs01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL;
SQL> CREATE ROLLBACK SEGMENT rollback1 TABLESPACE rollback_tbs;
SQL> ALTER ROLLBACK SEGMENT rollback1 ONLINE;

• Modify the parameter file settings
If using UNDO tablespace
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=new_undo_tablespace_name

If you are using the rollback segment tablespace, include the rollback segments that you created in previous step in ROLLBACK_SEMGNETS parameter in init.ora file

• Take the database out of restricted mode.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle 11g introduced a new feature called RMAN UNDO Bypass. The RMAN backup command no longer backs up the UNDO data that is not needed for recovery. Prior to Oracle 11g, all UNDO transactions that were already committed also backed up. This backup undo optimization minimizes the backup time and storage.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Understand Oracle RMAN Reporting

For any company the most important asset is data and the most challenging job is to recovery the database with less downtime with out any data loss, in the event of database failure. In many situations users end up with incomplete recovery of the database with out knowing which data files backed and which data files need to backup. You should ensure that your database is backed up efficiently and should restore successfully when needed. The RMAN reporting provides effective and easy way to determine database backup for a successful recovery.

In general, a catalog view contains the metadata of all databases and you need to write a complex query to extract usable backup information, but with RMAN reporting you can obtain the same information with LIST and REPORT commands. RMAN Reporting has been enhanced from version to version and now you can determine which database files have not been backed up and you can preview the backups required for successful restore...etc.

I have seen numerous postings on Metalink and Oracle forums regarding database restore and recovery issues as they don’t know what they have backed up and what backups they need for a successfully recovery. Using RMAN reporting effectively you can prevent such situations and ending up with incomplete recovery.
As part of your backup and recovery strategy, you should periodically run the reports that indicate

1) What you have backed up.
2) Which datafiles need backups or which files have not been backed up recently.
3) Which backups RMAN would need to restore if a problem occurs.
4) Historical information about RMAN jobs

What you have backed up?

To know what you have backed up, use LIST command to get the lists of backups, copies and other objects relating to backup and recovery (for example, backups that expired, restricted)

For example, you can list backups of all files in database
RMAN> LIST BACKUP OF DATABASE;

You can list copy of specified datafile
RMAN> LIST COPY OF DATAFILE ‘/oradata/users01.dbf';

Besides backups and copies, the RMAN can list other types of data using LIST command and it supports a number of options that enables you to control how output is displayed.

For example, you can list restore points know to repository.
RMAN> LIST RESTORE POINT;

You can list the names of recovery catalog scripts created with the CREATE SCRIPT or REPLACE SCRIPT command
RMAN> LIST SCRIPT NAMES;

List all of the expired backup sets
RMAN> LIST EXPIRED BACKUP SET;

Which datafiles need backups?

To know which files need backup, Use REPORT command that analyzes the available backups in the repository and return results about which files need a backup, which files have had unrecoverable operations on them, which files are obsolete etc.

Use the REPORT NEED BACKUP command to determine which database files need backup under a specific retention policy.

For example, display objects requiring backup to satisfy a recovery window-based retention policy.
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS;

Use REPORT UNRECOVERABLE command to determine which database files need backup when a database file has been affected by unrecoverable operation, such as insert, direct-path insert, normal media recovery cannot be used to recover the file, because an unrecoverable operation does not generate redo. You must perform either a full or incremental backup of affected datafiles after such operations, to ensure that data blocks affected by the unrecoverable operation can be recovered using RMAN.

For example, identify the datafiles affected by an unrecoverable operation.
RMAN > REPORT UNRECOVERABLE;

You should also know which backups are obsolete and can therefore delete. Because disk full can create performance problems or can cause the database to halt. Use CROSSCHECK command to update the status of backups in the RMAN repository compared to their backup status on disk or tape and run REPORT OBSOLETE to identify which backups are obsolete and are no longer needed for recovery.

For example, crosschecks all backups and copies on the disk
RMAN>CROSSCHECK BACKUP DEVICE TYPE DISK;
RMAN> CROSSCHECK BACKUP;

Identify which backups are obsolete and no longer needed for recovery
RMAN> REPORT OBSOLETE;

Delete obsolete backup information from RMAN repository.
RMAN> DELETE OBSOLETE;

This way you can effectively manage the backups and space.

Which backups RMAN would need to restore if a problem occurs?

Use RESTORE... PREVIEW to know which backups would need to restore if a problem occurs. The RESTORE…PREVIEW command does not actually restore, but identifies the backups required to complete a given restore operation based on the information in the repository. This command ensures that all required backups are available or identify the backups required for successful recovery. In case any backup is unavailable, use the CHANGE…UNAVAILABLE command to mark the backup status to UNAVAILABLE and re-run the RESTORE…PREVIEW to see the backups required to perform a restore operation with out using the unavailable backup.

You can use RESTORE…VALIDATE HEADER as an alternative to RESTORE…PREVIEW. In addition to listing of the files needed for restore and recovery, this command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository.

For example, identify the backups required to complete a database restore.
RMAN> RESTORE DATABASE PREVIEW

Use REVIEW...SUMMARY command to summarize the lengthy output.
RMAN> RESTORE DATABASE PREVIEW SUMMARY;

The RESTORE... PREVIEW RECALL can be used, in cases a restore fails due to a needed backup being stored remotely. Using this RECALL mode with media manager which supports vaulted backups simplifies the RECALL operation, by recalling the backups which are needed for restore from remote storage.

For example, to recall backups stored offsite
RMAN>RESTORE DATABASE PREVIEW RECALL;

You can also use RMAN validation to be sure they can use used for a restore (for ex, check for block corruption and missing backup sets). The RESTORE…VALIDATE command tests weather you can restore from backup and users can validate the restore of the backup control file, SYSTEM tablespace, and all archived logs.

For example, to validate RMAN backups
RMAN> RESTORE DATABASE VALIDATE;

By default VALIDATE does not check for logical corruption but to identify logical corruption use CHECK LOGICAL clause with RESTORE…VALIDATE command.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Find historical information about Oracle RMAN Jobs

In some cases, the V$ views supply information that is not available through use of the LIST and REPORT commands. You should periodically run the reports to obtain historical information about RMAN jobs. For example, you may want to know how many backup jobs have been issued, the status of each backup job (for example, whether it failed or completed), when a job started and finished, and what type of backup was performed.

The views V$RMAN_BACKUP_JOB_DETAILS and V$RMAN_BACKUP_SUBJOB_DETAILS provide details about RMAN backup jobs.

RMAN Reporting has been enhanced through V$VIEWS from version to version. Backup operations are more transparent and now DBA’s can easily embed queries to these views in SQL scripts, and generate historical reports. These reports help to analyze the issues and provide corrective action plan to prevent further failures.

The V$RMAN_OUTPUT view is an in-memory view and shows the output of all currently running and recently completed RMAN jobs.

The following query shows the output of a RMAN job

SQL> Select OUTPUT from V$RMAN_OUTPUT where SESSION_STAMP='698172913';

OUTPUT
-----------------------------------------------------------------
connected to target database: PINDBTS (DBID=3952868985)

executing command: SET SNAPSHOT
using target database control file instead of recovery catalog
snapshot control file name set to: /backup/dvtestrac01/rman/pindbts1/snapcf.f
new RMAN configuration parameters are successfully stored

allocated channel: d1
channel d1: sid=311 instance=pindbts1 devtype=DISK

sql statement: alter system archive log current

Starting backup at 21-SEP-09
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 21-SEP-09

---- (Some detail removed for brevity) ----

Finished backup at 21-SEP-09
released channel: d1

95 rows selected.

The V$RMAN_STATUS view shows the status of completed and running RMAN jobs. The running RMAN job information is stored in memory and once the job is completed the information is stored in the control file.

The following query shows the status of completed, completed with errors, failed and running RMAN jobs.

SQL> select OPERATION, START_TIME, END_TIME, OBJECT_TYPE, STATUS from v$RMAN_STATUS order by START_TIME;

OPERATION START_TIME END_TIME OBJECT_TYPE STATUS
---------------- ----------------------------- ----------------------------- ----- -------------- ----- -----------------------
RMAN 09/20/2009 20:00:06 09/20/2009 20:01:21 COMPLETED
BACKUP 09/20/2009 20:00:09 09/20/2009 20:01:06 DB FULL COMPLETED
BACKUP 09/20/2009 20:01:11 09/20/2009 20:01:21 ARCHIVELOG COMPLETED
RMAN 09/20/2009 20:05:03 09/20/2009 20:05:28 COMPLETED WITH ERRORS
BACKUP 09/20/2009 20:05:16 09/20/2009 20:05:28 ARCHIVELOG FAILED
RMAN 09/20/2009 20:10:15 09/20/2009 20:10:47 RUNNING
BACKUP 09/20/2009 20:10:39 09/20/2009 20:10:47 ARCHIVELOG RUNNING

The V$BACKUP_FILES view simulates the LIST BACKUP and LIST COPY RMAN commands. Also this view provides the information used as the basis of the REPORT OBSOLETE command. Now you can get the exact physical size of the backup set from BS_BYTES column, as the BYTES column shows the size of the physical file.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

If you notice any one of these errors in Alert.log during startup then the issue is related to “aio-max-nr" kernel limit. You will see this error in 10.2.0.4

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

ORA-27090: UNABLE TO RESERVE KERNEL RESOURCES FOR ASYNCHRONOUS DISK I/O

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call

You will encounter this issue when the "aio-max-nr" kernel limit is too low.

Here is how you can check the value
#/sbin/sysctl -a |grep aio

Solution : Change the "aio-max-nr" kernel limit as per Oracle recommendation
fs.aio-max-nr= 3145728

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com