Skip to content

Posts from the ‘rman’ Category

TSM: ORA-27211: Failed to load Media Management Library

SYMPTOMS

 

When attempting backup after a new install or upgrade to Oracle9i you see the error:RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 11/11/2004 22:36:02
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 8
Recovery Manager complete.

CAUSE

The problem is an environment problem. When the LD_LIBRARY_PATH is set and the vendors libobk is a part of that path when a channel is allocated within RMAN the library should dynamically load from the channels initialization call. This is not always the case and is known to Tivoli.

Tivoli has provided the following solution to this issue.

SOLUTION

This example is for an AIX platform.

To resolve this:

1) Link the TDP library file directly to the Oracle directory. The link would look like:
$ORACLE_HOME/lib/libobk.a -> /usr/tivoli/tsm/client/oracle/bin64/libobk64.a

2) Change the LIBPATH environment variable to include $ORACLE_HOME/lib before /usr/lib. If you have a LD_LIBRARY_PATH, ensure that this has the $ORACLE_HOME/lib before /usr/lib.

3) Ensure the SBT_LIBRARY parameter is not set

4) The Oracle instance may need to be restarted as it has been seen where the LIBPATH value was held in memory.

RMAN Cross Platform Tablespace Transport Over Network

12.1 introduced cross platform data transport using backup-sets. We could perform database/tablespace backups using FOR TRANSPORT / TO PLATFORM clause and restore the same to a different OS Platform. This typically involved 3 steps: create backup on source, copy backup-piece to destination and perform restoration on destination.

Starting from 12.2, we can perform cross platform transport of datafiles directly over the network using the FROM SERVICE clause along with the RESTORE FOREIGN DATAFILE command. This command internally performs a cross platform backup of the requested datafiles on source, the backup-piece chunks are transferred to destination over network and restoration is performed on destination.

SOLUTION

Below steps demonstrate a cross platform transport of tablespace EXAMPLE from source platform AIX (Big Endian) to destination Linux (Little Endian)

1. On source, check the datafiles which belong to tablespace EXAMPLE

SQL> select FILE_ID from DBA_DATA_FILES where TABLESPACE_NAME=’EXAMPLE’;

FILE_ID
———-
2

2. On destination, perform a cross platform restore of datafiles belonging to source tablespace EXAMPLE using FROM SERVICE clause:

Note: Start RMAN and connect to the destination database (not the source database) as TARGET.

rman target sys/<password>@<destination_tns>

RMAN> restore foreign datafile 2
2> format ‘/<destination_path>/example01.dbf’
3> from service <source_service_name>;

We can perform the migration using consistent or inconsistent backups. If using consistent backup, put the tablespace EXAMPLE in READ ONLY mode before running above restore command. If using inconsistent backups, the tablespace can remain in READ WRITE mode while performing initial restore.

3. If above restoration was done with tablespace in READ ONLY mode, then recovery is not required. You can skip to next step i.e. step# 4. However, if restoration was done with tablespace in READ WRITE mode, then we need to perform recovery. This can also be done directly over the network. Multiple recovery attempts can be performed while the source tablespace is in READ WRITE mode to sync it with latest changes being done on source.

Start RMAN and connect to the destination database (not the source database) as TARGET and execute RMAN recover command.

rman target sys/<password>@<destination_tns>

RMAN> recover foreign datafilecopy ‘/<destination_path>/example01.dbf’
2> from service <source_service_name>;

Before, performing final recovery, put source tablepace in READ ONLY modeSQL> alter tablespace EXAMPLE read only;

And, perform a final recovery on destination

rman target sys/<password>@<destination_tns>

RMAN> recover foreign datafilecopy ‘/<destination_path>/example01.dbf’
2> from service <source_service_name>;

4. We now have a consistent datafile on destination but it’s not yet plugged into the destination database. For this, we need to perform metadata export on source and metadata import in destination. We can also combine these 2 steps using network_link option of impdp. For this, create a database link in the destination database pointing to source database:SQL> create public database link <dblink> connect to system identified by oracle using ‘<source_service_name>’;

And then, perform the metadata import (user system is used in this example)impdp system/<password> network_link=<dblink> transport_tablespaces=EXAMPLE transport_datafiles=’/<destination_path>/example01.dbf’

12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN Backup Sets

GOAL

This Document explains the new 12c feature:  Cross-Platform Data transport Using rman Backup Sets across different Endian Platform.

RMAN can transport databases, data files, and tablespaces across platforms using backup sets. Performing cross-platform data transport with backup sets enables you to use block compression to reduce the size of backups. This improves backup performance and reduces the time taken to transport backups over the network.
A cross-platform backup is an RMAN backup that can be restored on a destination platform that is different from the source platform

SOLUTION

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Source database: AIX-Based Systems (64-bit) (Big Endian format)
Destination database: Linux x86 64-bit (Little Endian format)
User RMAN_TEST is granted SYSBACKUP privilege in both source and destination database
Tablespace been transported is TEST1
Source database name is SOURCE
Destination database name is DEST

*******************


Pre-requisite

  • To perform cross-platform data transport using backup sets, the version of the destination database must be Oracle Database 12c Release 1 (12.1) or later.
  • The COMPATIBLE parameter in the server parameter file of the source database and the destination database must be set to 12.0.0 or higher.
  • The tablespaces to be transported are in read-only mode, unless the ALLOW INCONSISTENT clause is used in the BACKUP command.
  • The tablespaces to be transported are self-contained.
  • Execute the DBMS_TTS.TRANSPORT_SET_CHECK procedure to check for dependencies. If the TRANSPORT_SET_VIOLATIONS view contains rows corresponding to the specified tablespaces, then you must resolve the dependencies before creating the cross-platform backup.

Terms you need to be aware of

Foreign Data File

Data files that do not belong to the destination database are called foreign data files. These data files are being plugged in to the destination database as part of a data transfer to the destination database. In the source database, this data file is identified by its original data file number.

Foreign Tablespace

A foreign tablespace is a set of foreign data files that comprise a tablespace in the source database. These foreign data files do not belong to the destination database, but are being transported into the destination database and are identified by the original tablespace name in the source database.

Foreign Data File Copy

A foreign data file copy is a data file that was restored from a cross-platform backup. It cannot be directly plugged in to the destination database because it is inconsistent. You must apply a cross-platform incremental backup to this data file and recover it before you can plug it in to the destination database.

Data Pump Destination

A Data Pump destination is a location on the disk of the server host of the destination database on which the Data Pump export dump file and the Data Pump log files are stored.

 

NOTE:  If the tablespace which is part of TTS/migration contains an object owned by a schema which does not exist in the destination database, the following error may occur:

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role ‘DMSTEAM’ does not exist
Failing sql is:GRANT DELETE ON “ADVTVS”.”FT_ENQUIRY_PLAN” TO “DMSTEAM”
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role ‘DMSTEAM’ does not exist

To avoid this issue, identify all the schema(s) on the source and create them on the destination server, if necessary, before executing the plugin process. The following query may be of help:

select distinct OWNER from dba_segments where tablespace_name in (‘<tablespace name which is part of TTS/migration>’);

Steps to Transport Read-Only Tablespaces to a Different Platform Using Backup Sets

Step 1: Connect to the source database (AIX) from which you need to transport tablespaces as TARGET.

$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Thu May 28 14:38:06 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN> Connect target “rman_test@source AS SYSBACKUP”;

Enter the password for the RMAN_TEST user in source when prompted.

Step 2:  Place the tablespaces to be transported in read-only mode.

RMAN> alter tablespace test1 read only;

using target database control file instead of recovery catalog
Statement processed

 

Step 3:  Back up the tablespace on the source database using the BACKUP command with the TO PLATFORM or FOR TRANSPORT clause.

Use the DATAPUMP clause to indicate that an export dump file for the tablespaces must be created

RMAN> BACKUP
TO PLATFORM ‘Linux x86 64-bit’
FORMAT ‘/tmp/xplat_backups/trans_ts.bck’
DATAPUMP FORMAT ‘/tmp/xplat_backups/trans_ts_dmp.bck’
TABLESPACE test1;

This command results in the following output:

RMAN>
Starting backup at 22-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=184 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces…
EXPDP> Starting “SYSBACKUP”.”TRANSPORT_EXP_SOURCE_asbF”:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table “SYSBACKUP”.”TRANSPORT_EXP_SOURCE_asbF” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_SOURCE_asbF is:
EXPDP>   /<oracle_home path>/dbs/backup_tts_SOURCE_93693.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST1:
EXPDP>   /<path>/oradata/SOURCE/test01.dbf
EXPDP> Job “SYSBACKUP”.”TRANSPORT_EXP_SOURCE_asbF” successfully completed at Fri May 22 11:54:53 2015 elapsed 0 00:02:06
Export completed

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/<path>/oradata/SOURCE/test01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=/<path>/oradata/trans_ts.bck tag=TAG20150522T115158 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
input Data Pump dump file=/<oracle_home path>/dbs/backup_tts_SOURCE_93693.dmp
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=/<path>/oradata/trans_ts_dmp.bck tag=TAG20150522T115158 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-MAY-15

When the 'TO PLATFORM' clause is used, conversion to the endian format of the destination database is performed on the source database.

Exit this Rman session connected to the source.

Please note :- For Step 3 the export file is created in $ORACLE_HOME/dbs until it completes and then it is moved to the location specified by DATAPUMP FORMAT.

Ensure this location has enough space.

Enhancement request 25909942 – RMAN’S BACKUP TO PLATFORM SHOULD NOT CREATE FILE IN $ORACLE_HOME/DBS has been logged for this issue so that file is written directly to DATAPUMP FORMAT location.
Currently its still in Initial review status

Step 4: Move the backupsets to Destination server


Move the backup sets created by the BACKUP command and the Data Pump export dump file to the destination host. An operating system utility is used to move
the backup sets from the source host to the destination host.

Step 5: Connect to destination database (Linux x86 64-bit)

Connect to the destination database, in which the tablespaces must be transported, as TARGET. The destination database is open in read write mode.

In this example, a TEST1 user is granted the SYSBACKUP privilege within the destination database:

$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Thu May 28 14:38:06 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target “rman_test@dest AS SYSBACKUP”;

Enter the password for the RMAN_TEST user in destination when prompted.

 

Step 6: Restore the tablespace from backupset.

Restore the datafiles from the backup sets transported from the source database using the RESTORE command. Use the DUMP FILE clause to import the export dump file containing the tablespace metadata and plug the tablespaces in to the destination database.

In this example the backuppiece and dump file were copied to /u01/app/oracle/oradata/test/.

If db_create_file_dest set in the destination database execute:

RMAN> RESTORE
FOREIGN TABLESPACE TEST1 to NEW
FROM BACKUPSET ‘/u01/app/oracle/oradata/test/trans_ts.bck’
DUMP FILE FROM BACKUPSET ‘/u01/app/oracle/oradata/test/trans_ts_dmp.bck’;

NOTE: The use of “to NEW” restores the datafiles in the db_create_file_dest specification.

If using multiple backupsets, specify each like:

RMAN> RESTORE
ALL FOREIGN DATAFILES TO NEW
FROM BACKUPSET ‘/mnt/staging_clt_target/tts/all_3tbs_uhrdm8f5_1_1.bck’ BACKUPSET ‘/mnt/staging_clt_target/tts/all_3tbs_uirdm8f6_1_1.bck’
DUMP FILE FROM BACKUPSET ‘/mnt/staging_clt_target/tts/all_3tbs_uhrdm8f5_1_1.bck’ ;

If using multiple backup pieces for each backupset, specify the backup pieces names within each backupset like:

RMAN> RESTORE
ALL FOREIGN DATAFILES TO NEW
FROM BACKUPSET ‘/mnt/staging_clt_target/tts/RTODP-a0r8utgu_1_1′,’/mnt/staging_clt_target/tts/RTODP-a0r8utgu_2_1’,
BACKUPSET ‘/mnt/staging_clt_target/tts/RTODP-a1r8utgu_1_1′,’/mnt/staging_clt_target/tts/RTODP-a1r8utgu_2_1’
DUMP FILE FROM BACKUPSET ‘/mnt/staging_clt_target/tts/RTODP_metadata_1.bck’;

If db_create_file_dest is not set you can specify the location of the datafile using:

RMAN> RESTORE
FOREIGN TABLESPACE TEST1 format ‘/u01/app/oracle/oradata/%U’
FROM BACKUPSET ‘/u01/app/oracle/oradata/dest/trans_ts.bck’
DUMP FILE FROM BACKUPSET ‘/u01/app/oracle/oradata/dest/trmp.bck’;

The command results in the following output:

Starting restore at 22-MAY-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring all files in foreign tablespace TEST1
channel ORA_DISK_1: reading from backup piece/u01/app/oracle/oradata/dest/trans_ts.bck
channel ORA_DISK_1: restoring foreign file 2 to/u01/app/oracle/oradata/dest/data_D-dest_I-2390668590_TS-TEST1_FNO-2_jvq7is5b
channel ORA_DISK_1: foreign piece handle=/u01/app/oracle/oradata/dest/trans_ts.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring Data Pump dump file to/u01/app/oracle/product/c12101/dbs/backup_tts_dest_31167.dmp
channel ORA_DISK_1: reading from backup piece/u01/app/oracle/oradata/dest/trans_ts_dmp.bck
channel ORA_DISK_1: foreign piece handle=/u01/app/oracle/oradata/dest/trans_ts_dmp.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Performing import of metadata…
IMPDP> Master table “SYSBACKUP”.”TSPITR_IMP_dest_jykD” successfully loaded/unloaded
IMPDP> import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export done in US7ASCII character set and AL16UTF16 NCHAR character set
IMPDP> Starting “SYSBACKUP”.”TSPITR_IMP_dest_jykD”:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job “SYSBACKUP”.”TSPITR_IMP_dest_jykD” successfully completed at Fri May 22 12:19:08 2015 elapsed 0 00:00:05
Import completed

Finished restore at 22-MAY-15

Exit this Rman session connected to the destination.

Step 7: Alter the tablespace to read write

SQL> select status,tablespace_name from dba_tablespaces;

STATUS TABLESPACE_NAME
——— ——————————
ONLINE SYSTEM
ONLINE SYSAUX
ONLINE UNDOTBS1
ONLINE TEMP
ONLINE USERS
READ ONLY TEST1

SQL> Alter tablespace test1 read write ;

Tablespace altered.

SQL> select status,tablespace_name from dba_tablespaces;

STATUS TABLESPACE_NAME
——— ——————————
ONLINE SYSTEM
ONLINE SYSAUX
ONLINE UNDOTBS1
ONLINE TEMP
ONLINE USERS
ONLINE TEST1

 

Additional information (Difference between TO PLATFORM and FOR Transport)

You can use the BACKUP command to create backup sets for cross-platform data transportation. To indicate that a backup is for cross-platform transportation,
use either the FOR TRANSPORT or TO PLATFORM clauses.

When you use TO PLATFORM, the endian format conversion is performed on the source database.
Therefore, you can restore this cross-platform backup only on the specified platform. If you omit TO PLATFORM, you can restore this cross-platform backup on any supported platform.The target platform specified by the TO PLATFORM clause must be a supported platform for cross-platform transport. The V$TRANSPORTABLE_PLATFORM view
contains the list of supported platforms.

When you use FOR TRANSPORT, the backup set that is created can be transported to any destination database. If the destination database uses an endian format that is
different from that of the source database, then the required endian format conversion is performed on the destination database (This is applicable for tablespace level transport).You can transport a entire database only if the source and destination use the same endian format.. The benefit of this method is that the processing
overhead of the conversion operation is offloaded to the destination database.

RMAN restore validate and preview

Here are steps to follow for restore validate and preview.

I did practice to restore database 30 days ago and it was useful before performing actual steps.

i found for one date data file and archive log were available but not control file so i need to forward date until i find control file. so it was very helpful before performing real time restore so we do waste time in critical moment.

rman
connect target “c##rman/a123@abc as sysbackup”

— restore preview

run
{
set until time “to_date(‘2019-30-06:23:00:00′,’yyyy-dd-mm:hh24:mi:ss’)”;
restore database preview;
}

— restore validate

run
{
set until time “to_date(‘2019-30-06:23:00:00′,’yyyy-dd-mm:hh24:mi:ss’)”;
restore database validate;
}

— from restore preview result get sequence and validate

run
{
restore archivelog from sequence 1246 until sequence 1248 thread 1 validate;
restore archivelog from sequence 1036 until sequence 1037 thread 2 validate;
}

— control file and spfile validate

run
{
set until time “to_date(‘2019-30-06:23:00:00′,’yyyy-dd-mm:hh24:mi:ss’)”;
restore controlfile validate;
restore spfile validate;
}

note: all of the above steps should be successful in order to perform real time restore.

 

 

RMAN Commands

Delete from TSM 

tdpo syncDB

List backup before 45 days

list backup completed before “sysdate-45”;

List of obsolete backup

report obsolete;

script to do crosscheck and delete obsolete

RUN
{
crosscheck copy of controlfile;
delete noprompt obsolete;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
}

script to delete for orphan backup from catalog

list backup;
run
{
set dbid=2874538101;
delete noprompt backup;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
crosscheck archivelog all;
}