Whatever topic has been discussed on this blog is my own finding and views, not necessary match with others. I strongly recommend you to do a test before you implement the piece of advice given at my blog.
Expert Oracle RAC
7.09.2011
RMAN best practices for RAC presentation
Since the manageability of RMAN backup, restore and recovery operations are nearly identical in non-clustered and clustered databases, the objective of the presentation was to summarize the options using which an RMAN can be best utilized for a RAC database to speed up the RMAN operations. The presentation also explains in-detail about instance,crash recovery operations for RAC database in contract to a stand-alone database instance recovery.
If you have missed the opportunity to attend or listen the presentation, I am uploading the slides here at my blog for your reference.
7.03.2011
RMAN Snapshot Controlfile for RAC database in 11gR2
According to MOS [ID 1263621.1], it is an expected behavior with 11gR2 or later in contrast to earlier Oracle versions. Since all instances of the RAC database shares the same snapshot controlfile, you must ensure that the SCF for a RAC database is configured on a shared device plus all instances of the RAC database must have access to the file.
The error in the context is likely to appear if all instances of RAC database can't access the snapshot controlfile. Therefore, the following RMAN configuration across all instances (on each node) should avoid the situation:
Syntax:
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO
References:
RMAN Snapshot Controlfile Must Reside on Shared Device for RAC database in 11g [ID 1263621.1]
Happy reading,
Jaffar
5.06.2011
Yet another RMAN quiz: What do you do in this situation?
A few days ago, I was trapped into a very tricky RMAN restore situation for one of our Oracle 10g (10.2.0.4) RAC databases and would like to get your perspective over the issue. Al right, here is the QUIZ for you.
Took an RMAN offline backup to tape with 5 channels. (DB running in noarchive log mode)
The database has around 22 datafiles.
dropped the database, including the controlfiles.
Restore the controlfile from the backupset followed by full database restore.
Do you think the restore is going to finish without any issues?
What sort of issues do you anticipate with the above situation?
By the way, no controlfile auto backup was set on the RMAN.
Updates on 8/May/2011
A lot of curiosity about the quiz has been generated in the Oracle community and many DBAs indeed tried their hands giving various solutions here and at linkedIn discussions.
Just to add more spice to the curiosity, let me tell you guys that I restored the controlfile from the backuppiece and followed by complete database restore. However, when I restored the entire database, to my surprise, only 4 data files were restored from the last backup and 18 datafiles were restored last but one backup sets. When I query the rman backup, it only showed 4 datafiles backup information for the last backup, of course there were older backups info too. What has astonished me is, despite the complete successful backup operations, the controlfile got only 4 datafiles backup info for that date.
Any wild guess why ?????
Regards,
Jaffar
10.06.2010
A mini RMAN recovery quiz
At that time, I was advised to rollback the database using the last night ONLINE RMAN backup and recovery (forward) it to a particular point in time. I was able to restore the database successfully using the last night backup. However, while doing the recovery, RMAN was requesting archived logs that were generated almost a week back. I was surprised a little bit about this behavior as I was in a impression that RMAN looks for the archived logs that were generated after the online backup. Though I have restore and recover the database successfully to a PIT, I thought of conducting a mini quiz on this behavior to get cross your perspective on this, before I reveal the secrets and blog about it. So, here goes your quiz:
Under which circumstances does an RMAN recovery needs archive logs generated before the backup sets that are used in the restore operation?
Best of luck and awaits for your response....
Jaffar
12.21.2008
How to boost RMAN performance on AIX5L systems
1. set AIXTHREAD to S on /etc/environment.
2. " ioo -o maxpgahead=256 " to set maxpgahead parameter
Initial settings were : Min/Maxpgahead 2 16
3. " vmo -o minfree=360 -o maxfree=1128 " to set minfree and maxfree...
Initial settings were : Min/Maxfree 240 256
7.23.2007
Presenting RMAN 1 day seminar in Sweden and Denmark
The course index is as follows:
Backup & Recovery Concepts
- What is backup and why backups are important?
- What is recovery?
Backups Methods
- Logical Backups (Export/Import and Data Pumps)
- Cold Backups Hot Backups (Consistent & Inconsistent Backups)
Recovery Manager (RMAN) Architecture & Concepts
- RMAN Introduction and Architecture
- Performing Backups using RMAN Recovery Catalog Maintenance
Different levels of Backups
- Mechanism of Incremental Backups
- Block Change Tracking Concepts
Flash Recovery Area (FRA)
- Introduction FRA Concepts and Benefits
Recover Database
- Recovering from different scenarios Instance/Crash Recovery
- Media Recovery
- Complete Recovery
- Incomplete Recovery
Block Recovery RMAN Advanced Topics
- Automatic Point-in-Time Recovery (TSPITR)
- Cloning Database
- Creating Standby Database/Rolling Upgrade
- Transportable Tablespaces from RMAN Backups Cross- Platform
- Transportable Tablespaces Cross Platform Database
Anyone wants to register for Denmark seminar, use the following url to register for the seminar.
Those who are in Sweden and willing to attend this seminar, can use the following url and register for the seminar.
Since this is my ever first international seminar, I am bit nervous and at the same time excited about the opportunity.
Happy Reading,
Jaffar
6.08.2007
STANDBY DATABASE + Error is 16191.
After wasting an hour time, finally had a look at primary database alert log and found the following error messages:
====
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
It may be necessary to define the DB_ALLOWED_LOGON_VERSION
initialization parameter to the value "10". Check the
manual for information on this initialization parameter.
------------------------------------------------------------
Thu Jun 07 01:40:44 2007
Errors in file d:\oradata\ocm\bdump\ocm_arc0_876.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC0]: Heartbeat failed to connect to standby 'SDB'. Error is 16191.
Errors in file d:\oradata\ocm\bdump\ocm_arc0_876.trc:
ORA-01031: insufficient privileges
====
When searching in the metalink about 'DB_ALLOWED_LOGON_VERSION' found the following
Note: Bug 2981553, which is implemented in 10.1.0.2, removes the parameter db_allowed_logon_version. This is replaced by the sqlnet.ora parameter called sqlnet_allowed_logon_version.
As per note, setting SQLNET_ALLOWED_LOGON_VERSION doesn't solve the issues.
Looking more close of the error message, I then, realized that I gave the different password to standby database password file than the primary password file.
After recreating the password file of standby database with the similar password of primary database password file, automatically log shipping started transmitting from primary to standby.
Well, I don't realized that just having different passwords to primary and standby parameter files will leads to this problem.
The other change I noted in the 10g Standby versus 9i is that, in 9i, when you say simply say startup Oracle throws an error saying the the controlfile is standby, where as in 10g, upon using startup, it automatically opens the standby database in READ ONLY state. Umm...
Happy reading,
Jaffar
--
6.04.2007
Mystry resolved (RMAN/VERITAS +ORA-19511)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch09 channel at 06/01/2007 13:15:15ORA-19502: write error on file "OFDMP_Dly_BACKUP_DBFFri010607krij6c1h_1_1", blockno 10731521 (blocksize=1024)ORA-27030: skgfwrt: sbtwrite2 returned errorORA-19511: Error received from media manager layer, error text:VxBSASendData: Failed with error:Server Status: Communication with the server has not been iniatated or the server status has not been retrieved fromthe server.
Looking into the above error message it is clearly understood that the problem is definitely relates to Media Manager as the communication between the channels from the client side to the netbackup server side loosing the communication after sometime.
We reported this problem with Veritas (vendor) and their support staff were here to run the back in trace mode and took those trace file for further investigations.
Meanwhile, I have also opened a p1 TAR with Oracle Support, they simply said that the problem is moreover related to Veritas and we should contact Veritas to resolve the issue.
It was like not having backups for more than 1 month (luckily we have DRC in sync with the primary).
Surprisingly, backing up a single, set of datafiles or tablespace was working fine, the problem is only when backing up entire database. Once it finishes 1 hr of time, backup was failing with the above stated error message.
Upon Veritas recommendation we gave look at NET_BUFFER_SZ value in the Netbackup Server and the Client side(the database server configured as netbackup client, since there is no server edition available on HP Superdom, we were using Netbackup client) we found out that the client and the server has different values in the NET_BUFFER_SIZE file.
We changed the value of this file in the client side to match with the server side file and backup started working fine.
As per general recommendation, NET_BUFFER_SZ file value on the client should be <= to the value on the server side.
Now the backup is running without any issues. No one has clue how this file got changed the values.
Happy Reading,
Jaffar
5.23.2007
Transport Tablespace from RMAN backupsets
Transportable Tablespace from RMAN backupsets:
In general, tablespaces must be kept in read only mode before being transported. It will be difficult task to put bigger size tablespaces and 24x7 accessed high availability tablespaces in the read only mode for longer time. This could prove a potential problem with the high availability databases.
Starting with version 10g Release 2, Transportable tablespace can be created from RMAN backupsets. In contrast, when RMAN creates transportable tablespace set from backupsets, it doesn’t touch the live datafiles from the tablespace to be transported and this elements the need of putting the tablespace in read only mode. Hence, it improves the availability.
Depending on the backup retention policy, transportable tablespace cal also be enables until point in time, SCN or restore points.
Step-by-step procedure for creating Transportable Tablespace from RMAN backup
RMAN uses three following steps to create Transpor Tablespace:
1. RMAN constructs an auxiliary instance.- Creates an initialization parameter file.
- Brings the instance in NOMOUNT state and then restores production database controlfile from RMAN backup to server auxiliary instance.
- Then, mounts the database.
2. RMAN restores auxiliary and transportable set of datafiles and Perform Point-in-Time Recovery.
- Restore system related tablespaces(system,sysaux,undo & temp)
- Restore transportable set of datafiles to the specified auxiliary destination.
- Performs SWITCH operations at auxiliary instance.
- Performs point-in-time recovery at the auxiliary instance.
- Archived logs will be applied on the demand and will be deleted upon apply.
- Upon completion of recovery, RMAN performs OPEN RESETLOGS on the auxiliary instance.
3. Auxiliary tablespace are put into read only mode, and Data Pump export mode invokes to construct the dump file with set of transportable tablespace.
tablespace destination ‘d:\tts_rman’
auxiliary destination ‘d:\temp_dest’;
Description:
TRANSPORT TABLESPACE is the command used to create transportable tablespace from RMAN backup.
AUXILIARY DESTINATION : is the temporary location where RMAN restores system, sysaux, undo and temp tablespace from the source database, and also redo logs. These files will be deleted automatically upon successfully completion of transport tablespace operation.
It is possible to divert the Data Pump files (import script, dump file and logfile) generated by RMAN to the Data Pump directory using the following syntax:
RMAN> transport tablespace amicus
Tablespace destination ‘d:\tts_rman’
Auxiliary destination ‘d:\temp_dest’
Datapump directory dump_dir
Dump file ‘amicus.dmp’
Import script ‘amicus_tbs.sql’
Export log ‘amicus_tbs.log’;
RMAN Transport Tablespace also can be used with SCN, UNTIL and RESTORE POINTS options.
Examples:
Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL SCN 11379;
Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL RESTORE POINT 'before_change';
Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL TIME 'SYSDATE-1';
Auxiliary Instance Parameter:
Following are the initialization parameters are used to construct the auxiliary instance.
Db_name
Compatible
Db_unique_name
Db_block_size
Db_files
Shared_pool_size – Set to 110M
Large_pool_size – set to 1M
Customizing Auxiliary File Location:
Its also possible to relocate the auxiliary instance files to a specific location for various reasons. Using SET NEWNAME FOR DATAFILE is used in the RUN block to achieve the above.
Common Errors triggers during RMAN transportable tablespace process:
Failing due to the insufficient value for shared_pool_size.
Filename conflict.
Limitations:
There are limitation applies specific to this feature.
There must be valid backup of all the datafiles required for this operations. In case there is no backup of the tablespace used for this operation, following error will be thrown on the RMAN prompt:
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================
RMAN-03002: failure of tranport tablespace command at 05/22/2007 21:59:01
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "JAFFAR"
Automatic auxiliary instance be created on the same host as the source instance.
Dropped and renamed tablespace can’t be used later for RMAN Transport Tablespace.
Tablespace must be self contained.
3.29.2007
Fully Automated TSPITR
Couple of days ago, I have tested Automatic TSPITR feature which is introduced with version 10g. Maybe, many experts might have already discussed about this and might have written articles about this feature। However, I just thought of sharing my experience which could help to the DBA who is not yet aware of this feature or never tested this feature.
This process requires the following two manual steps:
1. Specify the auxiliary location for RMAN to restore the auxiliary datafile/datasets
2. Configure the required channels on Target Instance.
Manual TSPITR is required the following procedure:
The basic procedure for performing user-managed TSPITR is as follows:
Take the tablespaces requiring TSPITR offline.
- Plan the setup of the auxiliary database.
- Create the auxiliary database and recover it to the desired point in time.
- Drop the tablespaces requiring TSPITR from the primary database.
- Use the transportable tablespace*** feature to transport the set of tablespaces from the auxiliary database to the primary database.
To perform full automated TSPITR, you just need to add AUXILIARY DESTINATION in the RMAN along with recover tablespace.
Assuming that a table has been dropped from tablespace DATATS:
{ RECOVER TABLESPACE datats
UNTIL logseq thread 1 -- thread 1 indicates single instance
AUXILIARY DESTINATION '/u10/temp'; --Auxiliary Location
}
When the above script runs, RMAN then carries out the following steps:
Takes the tablespaces to be recovered offline in the target database.
Restores a backup control file from a point in time before the target time to the auxiliary instance.
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance.
Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE).
Recovers the restored datafiles in the auxiliary instance to the specified time.
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database.
Shuts down the auxiliary instance.
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.
What I like about this feature is that, given the enough space to the auxiliary location, RMAN will do everything which required for TSPITR and once the process is done it will automatically obliterate the auxiliary instance, including files which restored during this process.
Its really worth to have a test about this feature.
References:
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit003.htm#sthref778
Metalink Note : Getting Started with Transportable Tablespaces - Note:100693.1
Happy reading,
Jaffar
3.18.2007
ALTER DATABASE BEGIN BACKUP
I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.
Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:
ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP; -- to exit from the backup mode.
In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.
-- The following has done with Oracle 10gR1
SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
-- All the datafiles are in backup mode now
SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
-- All the datafiles are out of backup mode now.
Happy reading,
3.03.2007
Known RMAN Performance Problems - Metalink Note : 247611.1
The note has workaround for the following bugs:
Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Note 342999.1 First Resync on Production Host After Running RMAN on
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles
Happy reading,
Jaffar
1.28.2007
CONTROLFILE AUTOBACKUP should be TURNED ON??
RMAN was first introduced with Version 8 through which database server database can be backup. Since then, Oracle has enhanced RMAN features drastically by every release/version. I am not going to discuss neither the RMAN history nor the backup.
I would like to mention here, how enabling few default parameters of RMAN can save the life of dba significantly.
RMAN has following default parameters and its default values:
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /app/oracle/dbs/snapcf_EHEALTH.f'; # default
I would be talking the benefits of CONTROLFILE AUTOBACKUP.
By default CONTROLFILE AUTOBACKUP is OFF. I would strongly recommend enabling CONTROLFILE AUTOBACKUP ON.
I remember in my early days of DBA job, a lot of notes/articles and many people use to suggest backup the controlfile immediately after any maintenance on the tablespaces, which was a good suggestion.
RMAN just takes away this head ache of backing controlfile after any maintenance on tablespace, when you turn ON CONTROLFILE AUTOBACKUP feature.
Benefits:
With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.
A control file autobackup lets you restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file autobackup.
Whenever you add a datafile/s, resize, increase/decrease the size of datafile/s or etc, controlfile is automatically backed up.
If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up.
Conclusion:
You can turn the autobackup feature on or off by running the following commands through RMAN utility:CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP OFF;
Reference:
Oracle documentation – backup and recovery concepts.
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1006.htm#sthref261
1.20.2007
Bug 4200702 - BLOCK CHANGE TRACKING WHEN USING DIRECT LOAD
Possible Symptoms
~~~~~~~~~~~~~~~~~
RMAN-571: ==========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-571: ========================================================== RMAN-3009: failure of backup command on ORA_DISK_1 channel at 02/14/2005 ORA-19694: some changed blocks were not found in the change tracking file
Workaround would be disabling change tracking feature.
According to the note, this is fixed in a patchset 10.1.0.5 and one-off patches are available for 10.1.0.4 and 10.1.0.3.
I guess, who are on 10gR2 and enabled the BCT feature doesn’t have this bug.
Happy reading,
Jaffar