Backup and Recovery « Oracle DBA – Tips and Techniques

Oracle 11g Cross platform Active Standby - Windows Primary database and Linux Active Standby

This note describes the procedure of configuring a cross platform using the 11g RMAN Active Duplicate as well as an Active Standby Database setup over a Windows and Linux platform.

The Metalink note Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1] describes the supported cross platform combinations between a primary and standby database.

In Oracle 10g, we could have a 32 bit and 64 bit Primary/Standby combination on some supported platforms and in 11g this has been extended to cover heterogenous platforms for the Primary and Standby database.

The environment used is as follows:

Primary

Windows 7 64 bit
11g Release 2
DB_UNIQUE_NAME=orcl

Active Standby

Oracle Enterprise Linux 5.7 64 bit
11g Release 2
DB_UNIUE_NAME=orcl_dr

Read the rest of the note Oracle 11g Cross platform Active Standby – Windows Primary database and Linux Active Standby

Recovery through RESETLOGS and reset of the incarnation of the database

Prior to Oracle 10g, the redo log files generated after opening the database with RESETLOGS could not be used with the backups taken before the RESETLOGS was performed.

Therefore, whenever a resetlogs was done, it was important to take an immediate full database backup, since all previous backups became invalid.

Just to recap, a RESETLOGS needs to be performed when we

1) Do a point in time recovery
2) Recover a database using a backup of the control file

What does a RESETLOGS do?

• Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1.
• Creates the online redo log files if they do not currently exist.
• Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp

The Recovery through Resetlogs feature provides the following benefits:

* There is no need to

Continue reading Recovery through RESETLOGS and reset of the incarnation of the database

11g RMAN Duplicate from Active Database and ORA-01017

Recently while performing an 11g Release 2 Active Database duplication, I encountered an error which one would normally associate with password files and the fact that the SYS password is not the same on the source and target databases.

RMAN-03002: failure of Duplicate Db command at 09/01/2010 09:17:39
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/01/2010 09:17:39
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Apparently, if we are performing an active database duplication via RMAN, we cannot connect to the target database using OS authentication like ‘ target /’.

We need to fully qualify the connection to the target database with the SYS user and password like ‘target sys/syspassword’ and not just ‘target /’.

I found a few links which discuss the same topic and thanks for the solution which I am sharing with

Continue reading 11g RMAN Duplicate from Active Database and ORA-01017

Some more RMAN Recovery Scenarios

I have added a few more RMAN recovery scenarios which I have come across recently and may be faced by others – so thought I’d share those with the community.

The first relates to a case where we need to create a replica of a production database on a test or development environment and we are not able to use the RMAN DUPLICATE DATABASE command because there is no network connectivity between the production and the test servers. This is very possible in cases where due to security restrictions production is on an isolated network from test or dev or there may be firewall rules in place which prevent the RMAN auxiliary channel connections (required for the duplicate) from working.

Further, we may be faced with a situation where for some kind of testing, we need the target database to be in sync with the source production database to a point

Continue reading Some more RMAN Recovery Scenarios

RMAN KEEP FOREVER, KEEP UNTIL TIME and FORCE commands

The KEEP FOREVER option in 11g has been improved over what was available in Oracle 10g from the point of view that in 11g, only the archivelogs which are required to keep the online backup consistent are retained.

In Oracle 10g we could roll forward this backup taken with the KEEP FOREVER option allowing point in time recovery to a time after the backup was taken. In Oracle 11g, the KEEP option will only retain enough archive logs to keep the backup consistent – so we can only use this backup to recover to the point where the backup was taken.

Archivelogs generated after this backup is completed need not be retained and can be candidates for the DELETE OBSOLETE command. In Oracle 10g, ALL archivelog backups taken after the KEEP FOREVER backup was taken had to be retained.

The KEEP FOREVER requires the use of an RMAN Catalog. The KEEP

Continue reading RMAN KEEP FOREVER, KEEP UNTIL TIME and FORCE commands

Duplicate from active database – ASM Primary to ASM Standby

In my earlier post 11g Standby Database Creation Without Any RMAN Backups I had illustrated how to create a standby database using the 11g Active Duplication feature which enabled us to create a standby database without any backup taken of the primary database. In this case it was a ASM Primary and a non ASM Standby database.

This example now illustrates how the same procedure can be extended to create an ASM Standby database from an ASM Primary database using the same active duplication method.

After following all the steps explained in the previous example, run the following command after launching RMAN and connecting to the auxiliary instance.

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
PARAMETER_VALUE_CONVERT
‘/u01/oracle/admin/test_fc’,’/u01/oracle/admin/test_js’
SET DB_UNIQUE_NAME=”test_js”
SET LOG_ARCHIVE_DEST_2=”service=test_fc LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”test_fc”
SET FAL_CLIENT=”test_js”

Continue reading Duplicate from active database – ASM Primary to ASM Standby

11g Release 2 RMAN Backup Compression

Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well.

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.

To use this option, we can run the following RMAN commands

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

followed by ..

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;

Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.

Compression Level ‘HIGH’

backupset size: 226.18M
time: 00:02:21

Compression Level ‘Medium’

backupset size: 293.80M
time: 00:00:30

Compression Level ‘Low’

backupset size: 352.59M
time: 00:00:20

Compression Level ‘Basic’

backupset size:

Continue reading 11g Release 2 RMAN Backup Compression

11g RMAN Virtual Private Catalog

In Oracle 11g, we can grant restricted access to the RMAN catalog to some users so that they can only access a limited set of databases that are registered in the RMAN catalog.

This is done by creating a Virtual Private Catalog which in turn will grant a particular user read/write access to only that user’s RMAN metadata. We can in this way create a number of multiple recovery catalog users each seeing only having access to a limited set of databases while the base recovery catalog owner has access to the entire metadata.

For example, in the RMAN catalog owned by user RMAN11D, there a a number of databases registered, but we would like to restrict access to the APEX database to a single user – RMAN_APEX.

So we need to first create a user in the database which houses the base RMAN catalog, grant that user the RECOVERY_CATALOG_OWNER role and then

Continue reading 11g RMAN Virtual Private Catalog

11g Release 2 Tablespace Point In Time Recovery – recover from dropped tablespace

One of the good new features in 11g Release 2 is that it enables us to recover from a case of a dropped tablespace. TSPITR (tablespace point in time recovery) has been around in earlier releases, but we could not recover a dropped tablespace.

What 11gR2 does is that it peforms a fully automated RMAN managed TSPITR. It creates and starts the auxiliary instance and restores just the datafiles it requires – SYSTEM,SYSAUX,UNDO and the files pertaining to the dropped tablespace – in this case datafiles 1,2,3 and 7- in the location which we specify as the ‘Auxiliary Destination’. It will first perform a recovery of the tablespace on the auxiliary instance and then use Data Pump and Transportable Tablespace technology to extract and import the tablespace meta data into the original source database.

To illustrate this example we create a new tablespace MYEXAMPLE and create two tables MYSALES and MYCOSTS

Continue reading 11g Release 2 Tablespace Point In Time Recovery – recover from dropped tablespace

RMAN Restore Validate Examples

We can use the RMAN RESTORE VALIDATE command to check and verify the integrity of the backups which are stored on tapes or disk as well.

A point to keep in mind is that the RESTORE DATABASE VALIDATE command will only check for the datafile backups and will not check for either the archivelog of controlfile backups. We need to issue additional RESTORE ARCHIVELOG VALIDATE as well as RESTORE CONTROLFILE VALIDATE commands.

We can also issue the RESTORE SPFILE VALIDATE command to check the backup of the server parameter file. By default, RMAN only checks for physical corruption while validating. We can also instruct RMAN to check for logical corruption via the VALIDATE CHECK LOGICAL command.

The RESTORE DATABASE VALIDATE command will check for the last level 0 or FULL tape or disk based backup, but the RESTORE ARCHIVELOG ALL command will check for all the archivelog files catalogued based on the retention

Continue reading RMAN Restore Validate Examples

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration