HP Data Protector Software HP Data Protector and Oracle 10g Database: Oracle Database migration to a remote Data Protector cell—Best practices Table of Contents Executive summary............................................................................................................................... 2 Target audience .................................................................................................................................. 2 Overview ..............................................
Executive summary This paper provides in-depth information on how to migrate an Oracle Database from one Data Protector cell to a different one. Target audience This paper is mainly aimed at system integrators and anyone trying to migrate an Oracle Database using Data Protector. Good understanding of Oracle Recovery Manager (RMAN) and Data Protector is recommended to follow this white paper. Overview The Data Protector Architecture organizes clients in cells.
Environment Configuration The environment used for our testing contains two Data Protector cells, (cell 1 and cell 2), each on a different location (data center A and data center B). Each cell has its own cell Manager and one Oracle Database server: Cell 1 (data center A): • Ita017: – (HP-UX) Data Protector cell Manager in cell1. – Oracle Server 10.2.0.3 – Recovery Catalog RCVCAT. • Ita022: – (HP-UX) Oracle Server running 10.2.0.3. – Oracle Database DB10g. – Data Protector File Library.
Figure 1. Test Environment As mentioned in the opening summary, there maybe different reasons why we need to move a database to a different data center, different cell, and different location. We mention below the two key ones: • Data center consolidation • Disaster Recovery Plan (DRP) In a data center consolidation scenario, normally downtime and migration time has been agreed ahead. In case of a disaster, all preparation steps for the DRP need to be done ahead.
Create the needed directories and password file Since we are not installing the database on the target server but performing a migration, we have to create the required directories manually before we mount the database.
Figure 3. Media labels and medium ID b. Data Protector CLI: i.
List File ---1 Handle: DB10g_online_FL.dbf Media: of Datafiles in backup set 43877 LV Type Ckp SCN Ckp Time Name -- ---- ---------- ---------- ---0 Incr 8387509 2008-05-26 /opt/oracle/oradata/DB10g/system01.dbf RMAN> list backupset 43877 ; (#repeat for every backupset you need#) List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------43877 Incr 0 369.
Figure 4. Jukebox configuration on hpu015 iii. Add a device type Jukebox on the target system. iv. ADD the files in the repository window 4. Import the media into the target cell. An import will re-read all the backup session information in the media into the internal database. a. Scan the devices and import all media b. Verify after the import that you can see the session imported in the Internal Database.
As the Control File autobackup is stored in a well-known format and path, it can be restored with the following command: RMAN> restore controlfile from autobackup; While Oracle’s recommendation is to have autobackup Control File set to ON, the default is currently set to OFF.
2. the Recovery Catalog (if available); connect to the recovery catalog database with the RMAN user: oracle@ita022[DB10g]:/home/oracle$ sqlplus rman/xxx@rcvcat SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 21 14:28:20 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.
Recovery Catalog export As for the Oracle Control File, Data Protector performs a backup of the Oracle Recovery Catalog at the end of every Oracle backup session by default. This functionality can be disabled in the Application Specific Options window, inside the Oracle backup specification. First, the Oracle Recovery Catalog is exported to the file system and then, by means of a file system backup, copied to the target device. For restore purposes, the DP GUI and the CLI can be used.
5. Startup nomount force: at this point, we want to have the instance running with the previously restored SPFILE. If you wish, you could also specify the SPFILE location in the startup command. 6. Restore the control files using RMAN 7. Mount the database: using the previously restored control files. 8. Query the control files for available backup information; decide on the Point In Time Recovery 9. Restore and Recover the database until the Point in Time (PITR) 10.
I. Restore with Control File autobackup ON and using Recovery Catalog In this case, when the backup was taken, Control File autobackup was enabled, and the Recovery Catalog was used. First, we start RMAN and connect to the RCAT recovery catalog on cell 2. Note that we do not connect to target yet. Next, we set the database DBID (which is known in the recovery catalog) and then we can connect to the target. oracle@hpu015[DB10g]:/opt/oracle$ rman catalog rman/xxx@rcat Recovery Manager: Release 10.2.0.3.
[Normal] From: OB2BAR@hpu015.deu.hp.com "" Time: 05/07/08 09:43:00 Completed OB2BAR Restore: ita022:c-38758237-20080506-01 "Oracle8" channel ORA_SBT_TAPE_1: SPFILE restore from autobackup complete Finished restore at 2008-05-07 Once the SPFILE is restored, we decide to restart the instance with the restored SPFILE and not leave it running with the default one.
At this point, we have a mounted database. To move forward first we need to decide, from the available backups, which backup to use for our restore. In case the number of backups is large, we can just list the backups between two specified dates. Below we can see the different backup sets available between today and yesterday.
RMAN> run { set until sequence = 98 thread 1; restore database; recover database; alter database open resetlogs; } Once the recovery is finished, the Database will be opened and ready to be used. II. Restore without Control File autobackup and using Recovery Catalog In this case, when the backup was taken, the Control File autobackup was disabled but the Recovery Catalog was used. While it is very similar to the previous session, no restore from autobackup is possible.
channel ORA_SBT_TAPE_1: restored backup piece 1 piece handle=DB10g_online_FL.
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------40354 Incr 0 191.50M SBT_TAPE 00:00:43 2008-05-07 BP Key: 40361 Status: AVAILABLE Compressed: NO Tag: TAG20080507T155104 Handle: DB10g_online_FL.dbf Media: List of Datafiles in backup set 40354 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ---------- ---2 0 Incr 7463627 2008-05-07 /opt/oracle/oradata/DB10g/undotbs01.
As the last available archived log is the one with sequence 102, we perform our incomplete recovery until sequence 103: RMAN> run { set until sequence = 103 ; restore database ; recover database ; alter database open resetlogs; } III. Restore without Control File autobackup and No Recovery Catalog In this case, we do not have a recovery catalog or Control File autobackup specified. This means, RMAN has no information about where the Control File backup is located.
Once we have the imported session, we can get a list of the objects it contains using the following command: omnidb –session “Session_ID” hpu015:[/.root]# omnidb -session "2008/05/08-3" Object Name Object Type Object Status CopyID =============================================================================== ita022:DB10g_online_FL.dbf Oracle8 Completed ita022:DB10g_online_FL.dbf Oracle8 Completed ita022:DB10g_online_FL.
As in the preceding case, we have to provide the right object that contains the Control File backup. Again, below the session with the restore of the Control File: RMAN> run { allocate channel dev_0 type sbt_tape; restore controlfile from 'ita022:DB10g_online_FL.dbf' ; } allocated channel: dev_0 channel dev_0: sid=101 devtype=SBT_TAPE channel dev_0: Data Protector A.06.
Recommendations This is a summary of the recommendations described along this paper: • Verify and document your Recovery Plan regularly • Always enable CONTROL FILE AUTOBACKUP • Use a Recovery Catalog in your environment • Plan a RESTORE VALIDATE on a regular basis.
Appendix A: Session Reports Session 1: Control File autobackup ON and Recovery Catalog oracle@hpu015[DB10g]:/opt/oracle/product/10.2.0/db_1/dbs$ rman catalog rman/xxx@rcat Recovery Manager: Release 10.2.0.3.0 - Production on Wed May 7 08:27:15 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Fixed Size Variable Size Database Buffers Redo Buffers 2057944 100665640 209715200 2134016 bytes bytes bytes bytes RMAN> run { 2> restore controlfile from autobackup; 3> } Starting restore at 2008-05-07 allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: sid=101 devtype=SBT_TAPE channel ORA_SBT_TAPE_1: Data Protector A.06.
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------38977 Incr 0 369.50M SBT_TAPE 00:00:38 2008-05-06 BP Key: 38981 Status: AVAILABLE Compressed: NO Tag: TAG20080506T145513 Handle: DB10g_online_FL.dbf Media: List of Datafiles in backup set 38977 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ---------- ---1 0 Incr 7375106 2008-05-06 /opt/oracle/oradata/DB10g/system01.
Completed OB2BAR Restore: ita022:DB10g_online_FL.dbf "Oracle8" channel ORA_SBT_TAPE_1: restored backup piece 1 piece handle=DB10g_online_FL.dbf tag=TAG20080506T145513 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:35 channel ORA_SBT_TAPE_1: starting datafile backupset restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /opt/oracle/oradata/DB10g/system01.
Session 2: Control File autobackup OFF and Recovery Catalog oracle@hpu015[DB10g]:/home/oracle$ rman catalog rman/xxx@rcat Recovery Manager: Release 10.2.0.3.0 - Production on Wed May 7 13:18:58 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved.
channel ORA_SBT_TAPE_1: sid=101 devtype=SBT_TAPE channel ORA_SBT_TAPE_1: Data Protector A.06.00/PHSS_37147/PHSS_37148/DPSOL_00306/DPLNX_ allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=100 devtype=DISK channel ORA_SBT_TAPE_1: starting datafile backupset restore channel ORA_SBT_TAPE_1: restoring control file channel ORA_SBT_TAPE_1: reading from backup piece DB10g_online_FL.dbf [Normal] From: OB2BAR_Oracle8@hpu015.deu.hp.
List File ---3 4 Handle: DB10g_online_FL.dbf Media: of Datafiles in backup set 40356 LV Type Ckp SCN Ckp Time Name -- ---- ---------- ---------- ---0 Incr 7463626 2008-05-07 /opt/oracle/oradata/DB10g/sysaux01.dbf 0 Incr 7463626 2008-05-07 /opt/oracle/oradata/DB10g/users01.
[Normal] From: OB2BAR_Oracle8@hpu015.deu.hp.com "DB10g" Time: 05/07/08 13:43:49 Completed OB2BAR Restore: ita022:DB10g_online_FL.dbf "Oracle8" channel ORA_SBT_TAPE_1: restored backup piece 1 piece handle=DB10g_online_FL.
Session 3: Control File autobackup OFF and NO Recovery Catalog oracle@hpu015[DB10g]:/home/oracle$ omnidb -session "2008/05/08-3" Object Name Object Type Object Status CopyID =============================================================================== ita022:DB10g_online_FL.dbf Oracle8 Completed ita022:DB10g_online_FL.dbf Oracle8 Completed ita022:DB10g_online_FL.dbf Oracle8 Completed ita022:DB10g_online_FL.
[Normal] From: OB2BAR_Oracle8@hpu015.deu.hp.com "" Time: 05/08/08 11:33:32 Completed OB2BAR Restore: ita022:DB10g_online_FL.dbf "Oracle8" channel dev_0: restore complete, elapsed time: 00:00:46 output filename=/opt/oracle/oradata/DB10g/control01.ctl output filename=/opt/oracle/oradata/DB10g/control02.ctl output filename=/opt/oracle/oradata/DB10g/control03.
Completed OB2BAR Restore: ita022:DB10g_online_FL.dbf "Oracle8" channel ORA_SBT_TAPE_1: restored backup piece 1 piece handle=DB10g_online_FL.dbf tag=TAG20080508T083907 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:15 channel ORA_SBT_TAPE_1: starting datafile backupset restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /opt/oracle/oradata/DB10g/sysaux01.
Appendix B: How to restore the DP Managed Control File root@ita022:/.root# export OB2APPNAME=DB10g root@ita022:/.root# /opt/omni/lbin/ob2rman.pl -restore_controlfile -session 2008/03/19-1 [Normal] From: ob2rman@ita022 "DB10g" Time: 03/19/08 16:26:37 Starting restore of target database. Net service name: DB10g. Instance status: OPEN. Instance name: DB10g. Database DBID = 38758237. Database control file type: CURRENT. Database log mode: ARCHIVELOG.
Appendix C: Sample RMAN script to duplicate the Database In this case, we assume the backup contains a Control File autobackup and we are using a Recovery Catalog: • Set the DBID of your database • Set the until clause (either time or sequence) rman catalog rman/xxx@RCVCAT << EOF set dbid = 38758237 ; connect target / ; startup nomount force; run { allocate channel dev_0 type sbt_tape; restore spfile from autobackup; release channel dev_0; } startup nomount force; run { allocate channel dev_0 type sbt_tape;
Appendix D: How to import the Recovery Catalog in the new cell In this example, we export the Recovery Catalog on ita017 and import it into hpu015. We assume no Recovery Catalog backup was taken previously with Data Protector, or this backup is not available. On the source system (ita017), export the recovery catalog owned by the user RMAN and copy it to the target system: oracle@ita017[RCVCAT]:/home/oracle$ exp rman/xxx@rcvcat file=rcvcat.dmp owner=rman Export: Release 10.2.0.3.
CTXSYS ANONYMOUS MDDATA TSMSYS DIP SCOTT SYSAUX SYSAUX USERS USERS USERS USERS SQL> create user rmani identified by xxx default tablespace RCVCAT; User created. SQL> grant RECOVERY_CATALOG_OWNER TO rmani; Grant succeeded. SQL> alter user rmani quota unlimited on RCVCAT; User altered. SQL> grant connect,resource to rmani; Grant succeeded. SQL> connect rmani/xxx Connected.
Appendix E: Script to restore the SPFILE and Control File providing the Backupset. These scripts may help you to perform the restore of the SPFILE and Control File from within a Data Protector Session, by providing the different objects to the RMAN restore command. It can be used in the case neither recovery catalog nor Control File autobackup is available. Assume the session we want to restore from is ‘2008/05/08-3’. It many need to be changed based on your requirements and environment.
Appendix F: Restore scenario: RAC Database to a Single instance Assume a disaster scenario where we need to restore our RAC database from cell 1 to cell2. However, in cell 2 only one host is available. In this case, we will restore a RAC database which was running on 4 nodes into a single instance running on a different node (in a different Data Protector cell).
starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 159383552 bytes Fixed Size Variable Size Database Buffers Redo Buffers 2056752 67112400 88080384 2134016 bytes bytes bytes bytes RMAN> restore spfile from autobackup; Starting restore at 2008-07-02 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=40 devtype=DISK allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: sid=35 devtype=SBT_TAPE channel ORA_SBT_TAPE_1: Data Protect
If you are not able to create a PFILE from the original RAC database (not available), you may want to create a file system copy of the restored SPFILE (binary file) and remove with your ASCII editor the binary characters (officially unsupported) RMAN> startup mount force pfile='/opt/oracle/product/10.2.0/db_1/dbs/initRAC1.
For more information • HP Data Protector Software http://www.hp.com/go/dataprotector © Copyright 2008 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty.