Migrating SAP ECC on Oracle to IBM Power Virtual Server

Discovery Process

This document assumes that a detailed discovery has been previously collected:

• Business, technical, functional, nonfunctional requirements of the Oracle database being migrated (including access and availability requirements)
• Installation configuration, HW/SW inventory, and sizing information related to the source database and the underlying platform.
  • Collect relevant metrics related to sizing, change rate, service level requirements, tolerance for migration downtime, capacity of networks to support data transfer from source to target, and so on.
  • The target environment has been evaluated and deployed consistent with source system capacity/performance/availability as found in the discovery process.

Migration Options Covered By this Document.

The following flowchart illustrates the two options that are currently covered by this document:

• Option 1: Consistent RMAN backup from source, with generated files to be used by the RMAN Duplicate Database procedure to restore to IBM PowerVS.
• Option 2: Inconsistent RMAN backup + archived redo logs (or level 0 + incremental backups) from source, with backup files to be used by the RMAN Restore/Recover Database procedure to restore to IBM PowerVS.
• Option 3: Leveraging Oracle Data Guard, currently being researched and will be the recommended method if it is the same platform – create a mirror and as soon as it is in sync you can switchover with just minutes of database downtime. The online documentation will be updated as soon as the testing and assessment phases have been completed.

Selected Considerations

Customer requirements and technical conditions affect the decision to select from the presented options.

1. Service Level Requirements, particularly when it comes to Restore Point Objectives and Restore Time Objectives.
   • Option 1: As presented assumes that the Customer is comfortable with a considerable service delay (RTO = "Days") before the source database is migrated to the target database. This expectation is often the case for nonproduction workloads but rarely for production databases.
   • Option 2: As presented assumes that the Customer is comfortable with a service interruption in terms of "Hours". This expectation is often the case for nonproduction workloads and some production databases.
   • Option 3: (Not yet presented in this document), incorporating Oracle Data Guard database synchronization, features a potential cutover of service from source to target in minutes.
2. Database Size, Network Throughput and Reliability.

   • Transporting backups of a large database across a network takes time. Consider the following transfer example of 600 GB of backups across an end-to-end network connection where available protocols and bandwidth support 80 megabytes/second throughput. At this rate, it will take 125 minutes to transfer the data. If the database backups are much bigger or if the network connection throughput is less, more transfer time will be required. Unreliable networks can disrupt the transfer, requiring you to restart the transfer process, incurring delay.

     • Customers can benefit from specialized transfer tools that compress data before/during transfer, use high-throughput protocols and decompress data after transfer.
     • Access to IBM’s Aspera, which can greatly accelerate data transfers from on-premise to IBM PowerVS locations is advantageous.
3. Availability of Skills to Execute.
   • Options provided in this document require experienced Database Administrator skills and the ability to work with infrastructure teams to migrate database content to a new target database on Power Virtual Server within an IBM PowerVS workspace.

Any migration procedures a Customer elects to use should be executed in the context of a detailed, well-rehearsed transfer and cutover plan.

Assumptions

This document assumes that :

1. If the source database is a production system it likely won’t be shut down for a full backup, in which case RMAN incremental backups will be used.
2. No changes to the source database (schema and configuration) are expected by executing this procedure.
3. Database will be migrated from source to target using Oracle RMAN options: Duplicate database, or backup/restore/recover database.
4. The target environment has Oracle homes pre-installed with a version matching the source instance being migrated.
5. RMAN duplicate, restore/recovery provide the option to modify the database data file location, but this option is not covered in this document. The assumption is made that the location of database files, either AIX JFS2 file systems or Oracle ASM disk groups, is identical between the source and the target environment.
6. The target OS will be either :
   • An exact copy (an OVA or “Open Virtual Appliance” archive file) of the source operating system generated by using an mksysb procedure, or
   • A freshly deployed version of AIX that is supported by IBM and certified by Oracle to support the version of Oracle to be instantiated.

Documentation of a mksysb procedure might be found at:

• Restoring AIX

Documentation of the current Source Database Sizing

Document current sizing and performance metrics related to compute, IOPS, and storage, of one or more source Oracle instances.

• The target IBM PowerVS Oracle instance should be constructed to at least match the sizing of the existing system.
• Very important on the target system to select disk tier and disk capacity to meet IOPS requirements.
• This discovery process should include execution of a performance test tool against the source database to capture metrics for future reference and comparison to target system deployment.

Confirm a Recent, Successful Backup Exists

Confirm, by using your standard backup tools, that a recent, successful, full backup of the source Oracle system exists. You must be able to completely restore the source system.

• If you cannot confirm it, the migration/discovery team should review RMAN backup procedures.
• In addition, the discovery team should work with customer DBA's or SMEs to validate database integrity and/or identify any existing corrupt blocks or schema problems.

Document Your Source Database

Document the location of source database instances and known credentials for DBA administration access.

Document RMAN Configuration

Document the current Oracle Recovery Manager (RMAN) configuration that is used to perform backups, per instance.

A simple way to obtain this information is by performing the following steps:

Use ssh to connect to the Oracle server.

ssh oracle@<hostname>

Check the variable $ORACLE_SID has been set.

echo $ORACLE_SID

Usually the SID value is shown. In case no value is shown, set one manually with this command:

setenv ORACLE_SID <SID value>

Please replace <SID value> with your SID value.

Connect to the source database using the rman command:

rman target /

A typical output will look like this :

Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 2 13:24:05 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: Exx (DBID=xxxxxxxx)

Display the rman configuration settings using:

RMAN> SHOW ALL;

Store and/or document output.

Exit the RMAN session by typing:

RMAN > exit;

Just for your reference, a typical SHOW ALL output will look like this:

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name EXX are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET; # 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 ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/EXX/19/dbs/snapcf_EXX.f'; # default

Verify Disk Space

Confirm that enough disk space is available, formatted, and mounted to accept the compressed migration database backup sets.

• Best performance is obtained if the disk space is local to the instance being backed up.
• Backing up database directly to Cloud Object Storage can introduce delays in backup execution due to comparative slowness of the network.
• This procedure will perform a backup, applying medium compression with the following considerations:
  • Compression results in backup file storage that takes approximately 25% of the space consumed by the Oracle database.
  • Local file system capacity should be sufficient to store the entire backup set (or sets) required to bring the target database to the restore point desired.
  • Target system requires access to the backup sets, via one of the following options:
    • A local filesystem to which the backup sets will be copied.
    • Installation of Aspera Agent on the On-Premise landscape,
    • An NFS direct mount to the Cloud Object Storage device or if using a Seagate Lyve Mobile transfer device. This does not yield the best performance and there have been other observations in the past.

Depending on network/access speed, it can be desirable to copy files from NFS mount to local storage.

File System for Backup Sets on Source

A dedicated local file system e. g. /backup/rman for the migration backup sets is referenced. As alternative option, a direct restore/recovery from the NFS-mounted Seagate Lyve Mobile device was also successfully tested using 10GbE and 100 GbE.

Perform the following commands to confirm available space for backups:

df -g /backup/rman

File System for Backup Sets on Target

The database duplication or restore/recover procedures require access to the set of backup files, via one of the following options:

• A local filesystem to which the backup sets will be copied.
• An NFS mounted to a Seagate Lyve Mobile transfer device, or
• An NFS mounted to IBM Cloud Object Storage to which the backup files have already been copied from the Seagate device.

Application and Database Shutdown

Use standard operational procedures to shut down the SAP system and the Oracle database before performing the backup procedure.

When using the multitenant architecture, you must connect to the root container database (CDB) and the backups include the pluggable databases (PDBs).

More information can be found in:

• About Performing Operations on CDBs and PDBs.

Ensure that RMAN Configuration is Documented

As described in the preparation section Document RMAN Configuration, be sure to record current RMAN configuration and associated parameters.

After completing special backups for migration, you want to ensure that RMAN configuration is left as before so normally scheduled backup operations continue.

Backup Option 1 - Create Target Directory

Login as the oracle user and execute the following commands to create the target backup directory to match the backup script that is described in the next section.

If the directory does not exist the rman script fails. Also if using an NFS mount, you need to make sure that the oracle user has the correct permissions in the definition file exports on the NFS server. The oracle user requires read and write (rw) permissions. Preferably using NFS is not advisable for the RMAN backup process. Using JFS2 or a locally mounted filesystem achieve better results.

The following two commands create the backup target location and set ORACLE_SID to the SID, please replace the term <SID> with the correct value for your system:

mkdir -p /backup/rman/<sid>_option1
setenv ORACLE_SID <SID>

You can of course use any target location as long as the oracle user can write to it and you modify the backup script from the next step to use your target directory.

Backup Option 1 - Backup Script

The RMAN script is used to perform the backup option 1. The commands used in this script are discussed after the script. Copy and paste the script and make the necessary adjustments for your environment.

option1_backup.rman script

connect target /
SHUTDOWN IMMEDIATE
STARTUP MOUNT
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/<sid>_option1/option1_cmp_%d_%U';
CONFIGURE DEVICE TYPE DISK PARALLELISM 60;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/<sid>_option1/option1_cf_%F';
CONFIGURE COMPRESSION ALGORITHM 'HIGH';
CONFIGURE ENCRYPTION FOR DATABASE ON ;
CONFIGURE ENCRYPTION ALGORITHM 'AES256' ;
SET ENCRYPTION ON IDENTIFIED BY passw0rd ONLY ;
BACKUP AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE TAG <YOUR TAG ID HERE> include current controlfile ;
alter database open;
quit;

Execute this script from the command line by using the oracle user.

rman @option1_backup.rman

RMAN backs up data to the configured default device for the type of backup requested. By default, RMAN creates backups on disk. If a fast recovery area is enabled, and if you do not specify the FORMAT parameter, then RMAN creates backups in the recovery area and automatically gives them unique names. This is the reason for modifying FORMAT in the parameters before this and is repeated here for emphasis.

The following are the main components of the rman script used:

Database Shutdown and Start in Mount Mode The database must be cleanly shut down and then started in “mount mode” for the offline backup option 1. To ensure that the backup is consistent, the database must not be open. Database shutdown + mount mode is accomplished by these two lines:

SHUTDOWN IMMEDIATE
STARTUP MOUNT

Backup Control File The database control file contains the RMAN catalog that is required to restore the backup pieces into a functioning database.

This command includes the control file into the backup:

CONFIGURE CONTROLFILE AUTOBACKUP ON
BACKUP AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE TAG EXX_100K_INITV3 include current controlfile ;

Note that the CONFIGURE CHANNEL DEVICE TYPE DISK and CONFIGURE CONTROLFILE AUTOBACKUP FORMAT for DEVICE TYPE DISK commands in the previous mentioned script include the backup file system location!

Backup Parallelism Set disk device parallelism - likely to speed up the backup and to reduce the backup window. The optimal parallelism is dependent on several factors:

• Availability of CPU resources to run that many concurrent backup processes. With the selected compression and encryption each RMAN process typically uses all CPU cycles of a logical processor, assuming the storage subsystem can provide the data fast enough.
• Capability of storage subsystem to support the RMAN data file read and the write to backup location I/O throughput.
• Amount of free physical memory to support the backup processes to read, compress, encrypt the data.
• Parallelism 60 was used in our testing as shown in the rman script mentioned previously, but a parallelism of 8, as shown now, maybe a good starting point to find an optimal level during discovery:

CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;

Backup Compression Set backup file compression. However HIGH has been tested in the script, however MEDIUM is advised. HIGH could be an option under certain circumstances. The use of 'MEDIUM' and 'HIGH' requires the Oracle Advanced Compression license!

• Tested CONFIGURE COMPRESSION ALGORITHM 'HIGH' ;
• Advised preventing license cost increase CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' ;

Backup encryption Set backup encryption on and modify the algorithm if desired (the default is AES128) and specify the encryption password to be used. Unless you are working with a TDE-encrypted database, every RMAN session requires the setting of the encryption and decryption password; otherwise, the session fails with a "wallet not open" error:

CONFIGURE ENCRYPTION FOR DATABASE ON ;
CONFIGURE ENCRYPTION ALGORITHM 'AES256' ;
SET ENCRYPTION ON IDENTIFIED BY passw0rd ONLY ;

Backup The final command then triggers the actual backup of the database to disk. The backup uses the RMAN-specific backup sets. Each RMAN process reads up to 6000M of consecutive data from a data file as a backup piece, compress and encrypt that data and then write it to the destination file system.

Of course the last action is to alter the database to open. It is advisable to create a specific tag (including the ORACLE_SID <SID> just for identification purposes). The goal of the section size is to limit the size of a backup piece so that a potentially required retransmit of a failed file transfer to the destination environment is manageable. The use of a TAG is recommended as it simplifies the management of multiple backups in an RMAN catalog:

BACKUP AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE TAG <SID>_100K_INITV3 include current controlfile ;
alter database open;

Backup Validation and Cross Check Backup Validation and Cross Check When backup is complete, still within RMAN, you can use some of the commands listed after this to validate and cross-check results.

On the RMAN prompt, use these commands:

RMAN> REPORT SCHEMA;
RMAN> LIST BACKUP SUMMARY;
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
RMAN> VALIDATE DATAFILE 10;
RMAN> VALIDATE BACKUPSET 3;
RMAN> CROSSCHECK BACKUP;

Explanation of commands:

• REPORT SCHEMA ; - Lists and displays information about the database files, tablespaces and so on.
• LIST BACKUP SUMMARY ; - Lists all existing backups. A SUMMARY option can be used.
• LIST BACKUP <TAG ID> ; - List a specific backup by TAG ID.
• BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;- Validate the contents of backup files.
• VALIDATE DATAFILE 10; - Validate a specific datafile.
• VALIDATE BACKUPSET 3; - Validate a specific backupset.
• CROSSCHECK BACKUP; - Synchronize the physical reality of backups and copies with their logical records in the RMAN repository.

Create PFILE Create a plain text database parameter file (PFILE) from the binary server parameter file (SPFILE) as follows. The database remains in mounted mode. Remember to use your <SID> in the command.

As the oracle user execute:

export ORACLE_SID=<SID>
sqlplus "/ as sysdba";

Inside SQL*Plus create the parameter file:

SQL> create pfile='/backup/rman/init<SID>.ora' from spfile;

Be sure to restore all RMAN parameters back to original discovery settings when done.

For this Option 1 we have created ONE backup to be transferred to and restored on the target system. At this point, to be consistent with a migration scenario, the source database should be shut down and not used any longer.

Check Database Size

Remember to make sure that you have enough space that is allocated on the filesystem to be able to back up the database. Use the following SQL statement to determine the current database size:

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

Ensure that RMAN Configuration is Documented

As described in the preparation section Document RMAN Configuration, be sure to record current RMAN configuration and associated parameters. After completing special backups for migration, you want to ensure RMAN configuration is left as before so normally scheduled backup operations continue.

Backup Option 2 - Create Target Directories

Execute the following commands as the oracle user.

The first two commands create the target backup directories for full and incremental backups. If you use a different directory, please adapt the two backup scripts in the next section. The third command sets the environment variable ORACLE_SID, replace the term <SID> with the appropriate SID value of your system:

mkdir -p /backup/rman/<sid>_option2
mkdir -p /backup/rman/<sid>_option2_inc1

setenv ORACLE_SID <SID>

Backup Option 2 - Backup Scripts

connect target /
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/<sid>_option2/lev0_%d_%U';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 60;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/<sid>_option2/lev0_cf_%F';
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
CONFIGURE ENCRYPTION FOR DATABASE ON ;
CONFIGURE ENCRYPTION ALGORITHM 'AES256' ;
SET ENCRYPTION ON IDENTIFIED BY passw0rd ONLY ;
BACKUP  tag '<Your TAG Here>' incremental level 0 AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;
quit;

rman @option2_backup_level0.rman

Recovery Manager complete.

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

BACKUP AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE PLUS ARCHIVELOG TAG ECOM_option1 include current controlfile;

CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;

CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' ;

CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
SET ENCRYPTION ON IDENTIFIED BY passw0rd ONLY ;

BACKUP  tag '<Your TAG here>' incremental level 0 AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG TAG <SID>_LEV0;

connect target /
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/<sid>_option2_inc1/inc1_%d_%U';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 60;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/<sid>_option2_inc1/inc1_cf_%F';
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
CONFIGURE ENCRYPTION FOR DATABASE ON ;
CONFIGURE ENCRYPTION ALGORITHM 'AES256' ;
SET ENCRYPTION ON IDENTIFIED BY passw0rd ONLY ;
BACKUP TAG `<SID>`_INC1 incremental level 1 AS compressed BACKUPSET section size 6000M DEVICE TYPE DISK DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;
quit;

setenv ORACLE_SID <SID>

rman @option2_backup_inc1.rman

Inspect Server Time and Date

Connect to the database as sysdba and execute the commands, as shown instructed. Note that the database is only put into mount mode and not opened.

Setting the NLS Date format is used as the format model to implicitly cast from date-to-string or string-to-dates, this is important if you choose to perform a Point in Time recovery.

The second SQL command seen in the following image is forcing Oracle to write to a new redolog, the command ran after shows the date stamp of when the file was created and provides you with a timestamp to roll the database forward to by applying the applicable redologs.

Execute the final incremantal backup via RMAN.

RMAN> REPORT SCHEMA;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUPSET <TAG ID> ;
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
RMAN> VALIDATE DATAFILE 10;
RMAN> VALIDATE BACKUPSET 3;
RMAN> CROSSCHECK BACKUP;

setenv ORACLE_SID <SID>
sqlplus "/ as sysdba";

SQL> create pfile='/backup/rman/init<SID>.ora' from spfile;

Check PFILE

We will now review the Oracle parameter file, or PFILE, stored within as init<SID>.ora.

Review database parameters that are contained within the saved PFILE and examine those with file name specifications. The parameter audit_file_dest= references a directory location that does not yet exist on the target server. The other file destinations reference ASM disk groups that should have already been created.

If the file locations in the target server do not match the source environment additional configuration changes are required in the rman scripts, so it is important that when cloning your Oracle server in IBM Cloud that you compare the configurations that are mentioned in the init<SID>.ora file and ensure that the locations exist on your target system.

Check Audit Directory

Check and confirm that the audit file directory is present on the target server and as listed in the PFILE and give the proper ownership and mode.

Check ASM Disk Groups and other Directory Definitions

Confirm that ASM disk groups that are referenced in the PFILE exist on target and have sufficient free space. As an oracle grid user, run:

asmcmd lsdg

If ASM groups are not used, review the PFILE and verify that referenced directories exist. At this point, you are ready to proceed with one of the following restore options.

Why are nofilenamecheck and noredo Options Used With duplicate database

nofilenamecheck

The option nofilenamecheck will stop RMAN pre-checking the directory and file location of the target system by comparing the information that is contained with the controlfiles of the duplicate backup. This is necessary if you want to restore a 1-2-1 copy of your source database to target using identical filesystem locations, SID and so on. If the option is not present then, you could receive a warning and the restore stops stating that there are discovered conflicts where target file locations where the datafiles will be restored to match source file locations. If this occurs add the nofilenamecheck to the restore command and retry, then the restore works.

noredo

The noredo option has to be added to the duplicate database command in the rman_duplicate.cmd script as shown earlier.

This is informing RMAN that you want to restore the full offline backup that is taken and do not check for any redologs. Oracle DBA's already know the issues when trying to create duplicate backups with the PLUS ARCHIVELOG mentioned in the backup command.

Start Target Database in NOMOUNT Mode

The following steps should be executed as the user oracle. As always please replace the term <SID> with the SID value of your system:

setenv ORACLE_SID <SID>

Start the Oracle instance in NOMOUNT mode, directly specifying the pfile to be used. Note that in our environment we are using Oracle in an SAP Environment so our ORACLE_HOME was /oracle/EC9/19.0.0 your environment may differ, so please pay attention with copy & paste and remember to substitute your own Oracle <SID> in the commands mentioned after this.

sqlplus "/ as sysdba"

SQL> startup nomount pfile='/backup/rman/ora/initEC6.ora';
SQL> create spfile='/oracle/EC6/19.0.0/dbs/spfileEC6.ora' from pfile='/backup/rman/ora/initEC6.ora';
SQL> shutdown immediate;
SQL> startup nomount;
SQL> show parameter spfile;

The output showing the parameter typically looks like:

NAME                 TYPE            VALUE
---------          -------------    ---------------------------------
spfile               string          /oracle/EC6/19/dbs/spfileEC6.ora

Determine Configuration File Path in Backup File Set

The control file (cf) had been included in the full backup (level 0). Determine the full path of the control file in the backup option2 level0 file set.

Example:

find /backup/rman/ec6_option2 -name "lev0_cf_*"

The find command prints all filenames including path that match the name pattern:

/backup/rman/ec6_option2/lev0_cf_c-2252531432-20240529-01

If no output is given, refer to the backup you created with the option 2 level 0 procedure for the control file location and check if and where the backup file set is on the target server.

Explanation of Restore Script

Substitute the correct locations for archive, redologs, and controlfiles that are relevant to your Oracle Environment/Installation.

The backup files are encrypted and RMAN requires the encryption password to be able to restore the database files.

SET DECRYPTION IDENTIFIED BY passw0rd;

We restore the database control file from the level 0 backup that contains the RMAN catalog we need to map backup pieces to data files as well as the list of data files and their expected locations.

restore controlfile from '/backup/rman/ec6_option2/lev0_cf_c-2252531432-20240529-01';

We then alter the database to mount mode which is required for the next step.

alter database mount;

Using the TAG we assigned to the level 0 backup that we instruct RMAN to restore the database from that specific backup.

restore database from tag IBMECC02_EC6_LEV0;

As a final step we apply any in level 0 backup included archive logs to the restored database. Note that this does not open the database so that we can apply future incremental backups and/or archive logs to the database.

recover database;

Execute Restore Database Level0 Script

Ensure that the database instance was started with startup nomount

As user oracle execute the restore script:

rman @restore_option2_lev0.rman

Sample output is extensive and not listed here.

Restore Incremental Backups (level 1) - All Except the Last One

Catalog the remainder of the incremental level 1 backupsets and archived logs in the target directory. If multiple incremental backups are provided, this is an iterative process.

For all incremental backups, EXCEPT the last, you can simply execute a

recover database ;

With this step(s) the incremental backup set(s) are cataloged.

Catalog Final Incremental Backup (level 1)

The final incremental backup requires that the recovery is only until a specific time to be able to open the database!

The target database can remain in MOUNT state and have incremental level 1 and archived REDO logs applied continuously, as needed. Catalog new backup pieces and archived logs and recover until ready for final cutover.

The following command assumes that all incremental backups are stored in directories under /backup/rman.

Still in SQL*Plus, execute these SQL commands:

RMAN> catalog start with '/backup/rman';

Sample output, just showing backup files from the first incremental level 1 backup:

searching for all files that match the pattern /backup/rman

List of Files Unknown to the Database
=====================================
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dk2s0rg6_1460_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dl2s0rgc_1461_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dl2s0rgc_1461_2_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dm2s0rgc_1462_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dn2s0rgc_1463_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_do2s0rgc_1464_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dp2s0rgc_1465_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dq2s0rge_1466_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_dr2s0rgh_1467_1_1
File Name: /backup/rman/ec6_option2_inc1/inc1_EC6_ds2s0rgn_1468_1_1
        ...Lines Omitted ...
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_EC6_eo2s0rlq_1496_1_1
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_EC6_ep2s0rlu_1497_1_1
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_EC6_eq2s0rm3_1498_1_1
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_EC6_er2s0rm6_1499_1_1
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_EC6_es2s0rma_1500_1_1
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_EC6_eu2s0rmi_1502_1_1
File Name: /backup/rman/ibmecc02/ec6_option2_inc1/inc1_cf_c-2252531432-20240529-02

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

Restore Final Incremental Backup (level 1) to Point in Time

For all incremental backups, EXCEPT the final, you can then execute in RMAN:

Remember because the backup was created with encryption you need to update the configuration in RMAN to decrypt the backup that uses the password, otherwise you encounter errors such as ORA-19913: unable to decrypt backup and ORA-28365: wallet is not open

RMAN> SET ENCRYPTION ALGORITHM 'AES256' ;
RMAN> SET DECRYPTION IDENTIFIED BY passw0rd;
RMAN> recover database ;

After cataloging the FINAL incremental backup pieces the recovery needs to be up to the date/time determined in the backup

After this final recovery action no further changes are expected to be applied to the database from a recovery perspective. Set the time and date format with:

RMAN> alter session set nls_date_format='DD-MM-YYYY HH24:mi:SS' ;

You will find an example date range here:

RMAN> recover database until time '13-01-2024 11:52:12';