SAP Oracle 19c System Refresh Guide on Azure VMs using Azure NetApp Files Snapshots with AzAcSnap

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Table of Contents

Version

Authors

Overview

Disclaimer

Assumptions

Preparation

Lab System Overview Source System GR1

Mount points GR1

fstab entries GR1

Oracle Control File Locations GR1 (default locations)

Oracle pfile and spfile locations GR1 (default locations)

Oracle trace file location GR1 (default location)

Lab System Overview Target System XR1

Mount points XR1

fstab entries XR1

Oracle Control File Locations XR1 (default locations)

Oracle pfile and spfile locations XR1 (default locations)

Oracle trace file location XR1 (default location)

AzAcSnap

AzAcSnap command to orchestrate the online Oracle backup on gr1ora:

AzAcSnap Configuration (azacsnap.json)

SAP NetWeaver/Oracle System Refresh

Backup GR1 source system with AzAcSnap

Backup the GR1 source database configuration to trace file

Modify the backup controlfile for database recovery on XR1 (xr1ora target system)

Prepare XR1 target system for a database refresh from GR1

Recreate/Recover the XR1 database

Start SAP Basis Post Processing

Summary

Additional Information

 

Version

This article leveraged the SAP/Oracle 19c using Microsoft Azure NetApp Files and AzAcSnap documentation as a guide to building the lab systems.

 

Authors

Scott McCullough, SAP Solutions Architect, NetApp

Nils Bauer, SAP Technical Marketing Engineer, NetApp

 

Overview

A typical SAP customer environment today consists of different SAP HANA and SAP NetWeaver components. To be able to test application patches, run performance and data integrity tests, or provide simple user training environments, copies of SAP components are required. A typical SAP customer needs about 10 copies of different SAP components. These copies must be refreshed, often on a monthly or quarterly basis.

 

Rapid provisioning of test or QAS systems allows SAP customers to run more test or project systems and refresh those systems more often. Doing so enables project teams to reduce project cycles by running parallel testing and improves quality of testing and training with more data from production.  

 

Time Requirements

 

The time required to create an SAP system copy can be subdivided into four parts:

 

  • Time to create a backup of the source system
  • Time to restore the backup to the target system
  • Time to perform OS and database-specific postprocessing
  • Time to perform SAP application postprocessing

 

(!) Note

 

The SAP postprocessing time depends on the customer’s SAP environment. Some customers can complete postprocessing in a few hours, while other customers need several days.

 

In a conventional system copy process, the data is backed up to disk or blob and then restored, which takes a great deal of time. If an online backup is used, there is no downtime for the source system; however, performance will be affected on the source system during the backup. Because of the large number of logs that potentially need to be applied, the time required to recover the database and make it consistent is greatly increased, possibly adding hours to the system copy process. If an offline backup is used, the source system is shut down, resulting in a loss of productivity.

 

Figure 1 and Figure 2 illustrate the differences between the amount of time spent creating an SAP system copy using a standard approach versus the time spent using AzAcSnap with Azure NetApp Files and restore to new volume approach.

 

Figure 1) SAP system copy: standard approach.

GeertVanTeylingen_0-1672833095564.png

 

Figure 2) AzAcSnap with Azure NetApp Files and restore to new volume approach

 

GeertVanTeylingen_1-1672833095570.png

A key requirement to successfully manage an SAP environment is the ability to create copies of production data to use in testing, quality assurance, or training. Azure NetApp Files snapshot technologies and AzAcSnap allow fast creation of SAP systems.

 

AzAcSnap with Azure NetApp Files snapshot technology can be used to create online database backups within minutes. Because a snapshot does not move any physical data blocks on the storage platform, the time needed to create a snapshot is independent of the size of the database. The use of snapshot technology also has no performance impact on the live SAP system. That is because the Azure NetApp Files snapshots do not move or copy data blocks when the snapshot is created or when data in the active file system is changed. Therefore, the creation of snapshots can be scheduled without having to consider peak dialog or batch activity periods.  For SAP system refresh considerations customers can perform adhoc backups of the source system anytime of the day without impacting performance of the source system. 

 

This article describes how to leverage an SAP/Oracle database (source system) backup when using Azure NetApp Files with AzAcSnap orchestration tool to protect the database with a redirected restore and SID change to a target system.  The scenario that is covered is:

 

  • Backup the source system using AzAcSnap with a redirected restore and SID change to a target system

 

Disclaimer

As with anything especially in the IT field, there are multiple ways to accomplish a task.  This article is by no means the only way to accomplish this task, it should be used as a guide and can be adjusted per individual circumstances.  In addition, most if not all the following can be automated.  However, the purpose of this article is to outline all the steps being performed manually. 

 

Assumptions

The setup of AzAcSnap is not covered within this article.  Setup instructions for AzAcSnap with Oracle can be found here.

 

The management of the Oracle archives contained within /oracle/SID/saparch:oraarch is not covered here.  Multiple solutions can be used to manage the archives via BRTools, RMAN or 3rd-party (backint) solutions. 

 

(i) Important

 

It is critical the Oracle archives that are created when AzAcSnap has the source Oracle database in hot backup-mode, are available during the recovery on the target system.

 

To leverage Azure NetApp Files ability to quickly create a volume from a snapshot, the target VM must be either:

  • In the same VNET as the source system

 GeertVanTeylingen_2-1672833095588.png

 

  • Have a VNET peering with the source system

GeertVanTeylingen_3-1672833095599.png

 

(i) Note

 

To address security concerns, Azure NetApp Files provides a mechanism to lock down access to a volume to a (sub)network, or single IP address. In the above example, Azure NetApp Files leveraging export policies can restrict data access to the target (xr1ora) IP address. Only that IP address would have access to the sapdata_clone volume regardless if it’s in the same VNET or VNET peered.

 

In this article a refresh of an SAP NetWeaver Oracle system is being performed with two unique SAP SIDs.

 

Preparation

 

Lab System Overview Source System GR1

Host: gr1ora (source system)

SAP SID: GR1

Host OS: Oracle Linux Server 8.6

Oracle version: 19.13.0.0

SAP NW: 7.5

 

GeertVanTeylingen_0-1672839279441.png

 

Mount points GR1

Azure NetApp Files (ANF) NFSv3 volumes.

 

ANF Volume

ANF Sub-Directory

File System

gr1ora-orashared

oracle

/oracle

gr1ora-orashared

usr_sap

/usr/sap

gr1ora-orashared

sapmnt

/sapmnt

gr1ora-orashared

oracle_GR1

/oracle/GR1

gr1ora-sapdata

sapdata1

/oracle/GR1/sapdata1

gr1ora-sapdata

sapdata2

/oracle/GR1/sapdata2

gr1ora-sapdata

sapdata3

/oracle/GR1/sapdata3

gr1ora-sapdata

sapdata4

/oracle/GR1/sapdata4

gr1ora-oraarch

GR1

/oracle/GR1/oraarch

gr1ora-origlog

origlogA

/oracle/GR1/origlogA

gr1ora-origlog

origlogB

/oracle/GR1/origlogB

gr1ora-mirrlog

mirrlogA

/oracle/GR1/mirrlogA

gr1ora-mirrlog

mirrlogB

/oracle/GR1/mirrlogB

 

fstab entries GR1

 

/etc/fstab

 

# Oracle SAP Mount points
10.1.9.6:/gr1ora-orashared/sapmnt  /sapmnt nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-orashared/usr_sap  /usr/sap nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/gr1ora-orashared/oracle  /oracle nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-orashared/oracle_GR1  /oracle/GR1 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/gr1ora-oraarch/GR1  /oracle/GR1/oraarch nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/gr1ora-mirrlog/mirrlogA  /oracle/GR1/mirrlogA nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-mirrlog/mirrlogB  /oracle/GR1/mirrlogB nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/gr1ora-origlog/origlogA /oracle/GR1/origlogA nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-origlog/origlogB  /oracle/GR1/origlogB nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/gr1ora-sapdata/sapdata1  /oracle/GR1/sapdata1 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-sapdata/sapdata2  /oracle/GR1/sapdata2 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-sapdata/sapdata3  /oracle/GR1/sapdata3 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-sapdata/sapdata4  /oracle/GR1/sapdata4 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

 

Oracle Control File Locations GR1 (default locations)

 

/oracle/GR1/sapdata1/cntrl

cntrlGR1.dbf

/oracle/GR1/origlogA/cntrl

cntrlGR1.dbf

/oracle/GR1/origlogB/cntrl

cntrlGR1.dbf

 

Oracle pfile and spfile locations GR1 (default locations)

 

spfile

/oracle/GR1/19.0.0/dbs

spfileGR1.ora

pfile

/oracle/GR1/19.0.0/dbs

initGR1.ora

 

Oracle trace file location GR1 (default location)

 

alert_GR1.log/*.trc

/oracle/GR1/saptrace/diag/rdbms/gr1/GR1/trace

 

Lab System Overview Target System XR1

Host: xr1ora (target system)

SAP SID: XR1

Host OS: Oracle Linux Server 8.6

Oracle version: 19.13.0.0

SAP NW: 7.5

GeertVanTeylingen_1-1672841258679.png

 

Mount points XR1

 

Azure NetApp Files (ANF) NFSv3 volumes.

ANF Volume

ANF Sub-Directory

File System

xr1ora-orashared

oracle

/oracle

xr1ora-orashared

usr_sap

/usr/sap

xr1ora-orashared

sapmnt

/sapmnt

xr1ora-orashared

oracle_XR1

/oracle/XR1

gr1ora-clone-sapdata

sapdata1

/oracle/XR1/sapdata1

gr1ora-clone-sapdata

sapdata2

/oracle/XR1/sapdata2

gr1ora-clone-sapdata

sapdata3

/oracle/XR1/sapdata3

gr1ora-clone-sapdata

sapdata4

/oracle/XR1/sapdata4

xr1ora-oraarch

XR1

/oracle/XR1/oraarch

xr1ora-origlog

origlogA

/oracle/XR1/origlogA

xr1ora-origlog

origlogB

/oracle/XR1/origlogB

xr1ora-mirrlog

mirrlogA

/oracle/XR1/mirrlogA

xr1ora-mirrlog

mirrlogB

/oracle/XR1/mirrlogB

 

fstab entries XR1

           

/etc/fstab

 

# Oracle SAP Mount points
10.1.9.6:/xr1ora-orashared/sapmnt /sapmnt nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/xr1ora-orashared/usr_sap /usr/sap nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/xr1ora-orashared/oracle /oracle nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/xr1ora-orashared/oracle_XR1 /oracle/XR1 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/xr1ora-oraarch/XR1 /oracle/XR1/oraarch nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

10.1.9.6:/xr1ora-mirrlog/mirrlogA /oracle/XR1/mirrlogA nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/xr1ora-mirrlog/mirrlogB /oracle/XR1/mirrlogB nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/xr1ora-origlog/origlogA /oracle/XR1/origlogA nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/xr1ora-origlog/origlogB /oracle/XR1/origlogB nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

# CLONE SAP DATA MOUNTS
10.1.9.6:/gr1ora-clone-sapdata/sapdata1 /oracle/XR1/sapdata1 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-clone-sapdata/sapdata2 /oracle/XR1/sapdata2 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-clone-sapdata/sapdata3 /oracle/XR1/sapdata3 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0
10.1.9.6:/gr1ora-clone-sapdata/sapdata4 /oracle/XR1/sapdata4 nfs rw,vers=3,hard,timeo=600,rsize=262144,wsize=262144,bg,noatime,nointr,lock 0 0

 

Oracle Control File Locations XR1 (default locations)

 

/oracle/XR1/sapdata1/cntrl

cntrlXR1.dbf

/oracle/XR1/origlogA/cntrl

cntrlXR1.dbf

/oracle/XR1/origlogB/cntrl

cntrlXR1.dbf

 

Oracle pfile and spfile locations XR1 (default locations)

 

spfile

/oracle/XR1/19.0.0/dbs

spfileXR1.ora

pfile

/oracle/XR1/19.0.0/dbs

initXR1.ora

 

Oracle trace file location XR1 (default location)

 

alert_XR1.log/*.trc

/oracle/

XR1/saptrace/diag/rdbms/xr1/XR1/trace

 

AzAcSnap

 

AzAcSnap command to orchestrate the online Oracle backup on gr1ora:

 

azacsnap -c backup --volume data --prefix GR1_clone --retention 1

 

 

AzAcSnap Configuration (azacsnap.json)

 

This Azure NetApp Files volume will be captured within an Azure NetApp Files storage snapshot while Oracle is in hot backup mode.  You must ensure all Azure NetApp Files volumes containing sapdata1-X are included.

 

dataVolume

gr1ora-sapdata

 

azacsnap.json

 

{
  "version": "7",
  "logPath": "./logs",
  "securityPath": "./security",
  "comments": [
    "GR1"
  ],
  "database": [
    {
      "hana": null,
      "oracle": {
        "serverAddress": "gr1ora",
        "sid": "GR1",
        "connectString": "/@AZACSNAP",
        "backupAbortWaitSeconds": -1,
        "hliStorage": [],
        "anfStorage": [
          {
            "anfBackup": "none",
            "dataVolume": [
              {
                "resourceId": "/subscriptions/XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXXXX/resourceGroups/rg-mcscott/providers/Microsoft                    .NetApp/netAppAccounts/SAP-EastUS/capacityPools/sap-premium-mqos/volumes/gr1ora-sapdata",
                "authFile": "azureauth.json",
                "subscription": "XXXXXXXXXXX-9847-4eb16109e870",
                "resourceGroupName": "rg-mcscott",
                "accountName": "SAP-EastUS",
                "poolName": "sap-premium-mqos",
                "volume": "gr1ora-sapdata"
              }
            ],
            "otherVolume": []
          }
        ],
        "amdStorage": []
      },
      "db2": null
    }
  ]

 

SAP NetWeaver/Oracle System Refresh

The following example will refresh the SAP Oracle database XR1(target) with data from SAP Oracle database GR1 (source).  The example will use printer entries within SAP NW transaction SPAD to highlight refresh operations.

 

Current list of output devices on GR1 source (9 entries)

GeertVanTeylingen_2-1672842047444.png

 

Current list of output devices on XR1 target (5 entries) 

GeertVanTeylingen_3-1672842055235.png

 

Backup GR1 source system with AzAcSnap

Take note of the Oracle archive log(s) generated during the backup.  They must be available on the target system (xr1ora) for a successful recovery.  Backup of the source system regardless of database size will take only minutes.

 

[azacsnap@gr1ora ~]$ azacsnap -c backup --volume data --prefix GR1_clone --retention 1 -vv
INFO : --------------------------------------------------------------------------------
INFO :  azacsnap 7 (Build: 1A8FDFF) is 17 days old.
INFO : --------------------------------------------------------------------------------
INFO : Executing: azacsnap -c backup --volume data --prefix GR1_clone --retention 1 -vv
INFO : Program version: 7 (Build: 1A8FDFF)
INFO : Build age: 17 days old
INFO : Starting backup process for 1 database(s)
INFO : Processing Oracle database SID = GR1
INFO : Getting Oracle database version
INFO : Getting Oracle database version
INFO : Setting the minimum wait period before automatically aborting an active backup to -1 secs
INFO : Setting the maximum wait period before aborting a SQL command to 300 secs
INFO : Enable backup mode for Oracle with connect string = AZACSNAP on gr1ora
INFO : Enable backup mode for Oracle database version 1913000000
INFO : Output of following command:
SELECT V\$TABLESPACE.name, V\$DATAFILE.file#, V\$BACKUP.status, V\$DATAFILE.name FROM V\$DATAFILE, V\$TABLESPACE, V\$BACKUP WHERE V\$DATAFILE.TS#=V\$TABLESPACE.TS# AND V\$BACKUP.FILE#=V\$DATAFILE.FILE# AND V\$BACKUP.STATUS='ACTIVE';
sent to file './logs/azacsnap-backup-azacsnap.protected-tables'
INFO : Snapshot starting for ALL 'data' volume(s) for SID = 'GR1'.
INFO : [5] Initiate Snapshot on ANF Storage Volume with azureauth.json@/subscriptions/xxxxxxxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-mcscott/providers/Microsoft.NetApp/netAppAccounts/SAP-EastUS/capacityPools/sap-premium-mqos/volumes/gr1ora-sapdata, attempt 1 of 5
INFO : Instantiating an AzureNetAppFilesManagementClient object...
INFO : [5] Attempting to get Service Principal from file='azureauth.json'.
INFO : [5] Service Principal authenticated against 'https://login.microsoftonline.com' ok
INFO : [5] Testing connectivity to 'https://management.azure.com/' (will wait up to 30 seconds for response)
INFO : [5] Connection to 'https://management.azure.com/' ok
INFO : [5] Create Storage Snapshot on resource '/subscriptions/xxxxxxxxxxxx-xxxx-xxxxxxxxx/resourceGroups/rg-mcscott/providers/Microsoft.NetApp/netAppAccounts/SAP-EastUS/capacityPools/sap-premium-mqos/volumes/gr1ora-sapdata'.
INFO : Trying to create snapshot for volume 'gr1ora-sapdata'.
INFO : Snapshot successfully created with resource id: /subscriptions/xxxxxxxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-mcscott/providers/Microsoft.NetApp/netAppAccounts/SAP-EastUS/capacityPools/sap-premium-mqos/volumes/gr1ora-sapdata/snapshots/GR1_clone__F365291B91B
INFO : [5] Retaining maximum of '1' snapshots on volume 'gr1ora-sapdata'.
INFO : [5] Snapshot tasks for volume 'gr1ora-sapdata' complete.
INFO : Storage snapshots completed successfully for all 'data' Volumes
INFO : Snapshot complete for ALL 'data' volume(s) for SID = 'GR1'.
INFO : Disable backup mode for Oracle with connect string = AZACSNAP on gr1ora
INFO : Disable backup mode for Oracle database version 1913000000
INFO : Finished backup process successfully, created snapshot 'GR1_clone__F365291B91B' for 'data' volumes.
[azacsnap@gr1ora ~]$

 

AzAcSnap issues an “alter system archive log current” after it brings the Oracle database out of hot backup mode.  This is a good time to copy the archive log(s) to a location where the target system xr1ora has access to them.  A simple script like the following could be setup to do this as an AzAcSnap post operation.

 

ls -tr /oracle/"$SID"/oraarch/*arch*dbf | tail -"$NUM_OF_LOGFILES" | while read LOG
   do
      echo "Copying archive log $LOG to "$DIR" ....."
      write2log "Copying archive log $LOG to "$DIR" ....."
      cp ${LOG} $DIR
      RET=$?
      if [ $RET -gt 0 ]
      then
         ERROR=1
      fi
   done 

 

Within the Azure portal you can see the newly created Azure NetApp Files snapshot within the Azure NetApp Files volume gr1ora-sapdata.

 

(!) Note

 

You can have multiple snapshot schedules/names for the same system.  In this way you can maintain a production backup schedule (GR1_hourly….) and use an adhoc snapshot for a system refresh.

 

GeertVanTeylingen_1-1672844018226.png

 

Backup the GR1 source database configuration to trace file

This file is critical to the system recovery on the target system.  The file will be created on the source system (oragr1) within the Oracle trace file location.  The file should be created shortly after the AzAcSnap backup operation. Copy the file to a location that the target oraSID (xr1ora) has access to edit and run the script (oraxr1 home directory can be used).

 

gr1ora:oragr1 130> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 1 14:02:20 2023
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> alter database backup controlfile to trace;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
gr1ora:oragr1 131>

 

Trace file has been created.

 

gr1ora:oragr1 134> pwd
/oracle/GR1/saptrace/diag/rdbms/gr1/GR1/trace
gr1ora:oragr1 135> ls -ltr GR1_ora_35260.trc
-rw-r----- 1 oragr1 dba 7997 Jan  1 14:03 GR1_ora_35260.trc
gr1ora:oragr1 136>

 

Modify the backup controlfile for database recovery on XR1 (xr1ora target system) 

Copy the trace file to  XR1_clone.sql in a location on xr1ora (target system) that oraxr1 has access to modify the file.

 

xr1ora:oraxr1 53> cp GR1_ora_35260.trc XR1_clone.sql

 

The XR1_clone.sql file contains instructions for Oracle to recreate the database.  There are two sections in the sql script.  We will only be using the second set of instructions.

 

Delete all lines down to:

 

       --     Set #2. RESETLOGS case

 

Replace all occurrences of “GR1” with “XR1” (source to target).  A simple way to do this within the vi editor is using:

 

            :%s/GR1/XR1

 

Locate the “CREATE” line

 

CREATE CONTROLFILE REUSE DATABASE "XR1" RESETLOGS  ARCHIVELOG

 

Change the line to

 

CREATE CONTROLFILE SET DATABASE "XR1" RESETLOGS  ARCHIVELOG

 

Locate the “RECOVER” line

 

RECOVER DATABASE USING BACKUP CONTROLFILE

 

Delete this line to the end of file.  We will manually perform the recovery and add the temp tablespace.  The following is the  XR1_clone.sql that will recreate the database from GR1 to XR1.

 

xr1ora:oraxr1 55> cat XR1_clone.sql
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "XR1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 1000
    MAXINSTANCES 50
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 (
    '/oracle/XR1/origlogA/log_g11m1.dbf',
    '/oracle/XR1/mirrlogA/log_g11m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/XR1/origlogB/log_g12m1.dbf',
    '/oracle/XR1/mirrlogB/log_g12m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/XR1/origlogA/log_g13m1.dbf',
    '/oracle/XR1/mirrlogA/log_g13m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 4 (
    '/oracle/XR1/origlogB/log_g14m1.dbf',
    '/oracle/XR1/mirrlogB/log_g14m2.dbf'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/XR1/sapdata1/system_1/system.data1',
  '/oracle/XR1/sapdata1/sysaux_1/sysaux.data1',
  '/oracle/XR1/sapdata1/undo_1/undo.data1',
  '/oracle/XR1/sapdata2/sr3_1/sr3.data1',
  '/oracle/XR1/sapdata2/sr3_2/sr3.data2',
  '/oracle/XR1/sapdata2/sr3_3/sr3.data3',
  '/oracle/XR1/sapdata2/sr3_4/sr3.data4',
  '/oracle/XR1/sapdata2/sr3_5/sr3.data5',
  '/oracle/XR1/sapdata2/sr3_6/sr3.data6',
  '/oracle/XR1/sapdata3/sr3750_1/sr3750.data1',
  '/oracle/XR1/sapdata3/sr3750_2/sr3750.data2',
  '/oracle/XR1/sapdata3/sr3750_3/sr3750.data3',
  '/oracle/XR1/sapdata3/sr3750_4/sr3750.data4',
  '/oracle/XR1/sapdata3/sr3750_5/sr3750.data5',
  '/oracle/XR1/sapdata4/sr3usr_1/sr3usr.data1'
CHARACTER SET UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/XR1/oraarch/XR1arch1_1_1124574837.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
--
xr1ora:oraxr1 56> 

 

Prepare XR1 target system for a database refresh from GR1

Stop the XR1 SAP and Oracle instance on xr1ora

           

(i) Important

 

The following operations are destructive as we will be removing the existing Azure NetApp Files SAP XR1 data volume (gr1ora-clone-sapdata) and recreating it from the AzAcSnap snapshot performed previously.

 

Unmount the XR1 sapdata file systems.

 

[root@xr1ora ~]# umount /oracle/XR1/sapdata1
[root@xr1ora ~]# umount /oracle/XR1/sapdata2
[root@xr1ora ~]# umount /oracle/XR1/sapdata3
[root@xr1ora ~]# umount /oracle/XR1/sapdata4

 

Delete the Azure NetApp Files gr1ora-clone-sapdata volume via the Azure portal.

 

Recreate the Azure NetApp Files gr1ora-clone-sapdata volume using the snapshot AzAcSnap created in the previous step within Azure NetApp Files volume gr1ora-sapdata

 

GeertVanTeylingen_2-1672844338268.png

 

 

GeertVanTeylingen_3-1672844371236.png

Regardless of volume size, the newly created gr1ora-clone-sapdata volume will be available to mount within minutes.

 

Optionally, you can restrict access to this volume to specific IP addresses.  The other parameters will be inherited by the source volume (gr1ora-sapdata).

 

Mount the newly created gr1ora-clone-sapdata volume on xr1ora.

 

[root@xr1ora ~]# mount /oracle/XR1/sapdata1
[root@xr1ora ~]# mount /oracle/XR1/sapdata2
[root@xr1ora ~]# mount /oracle/XR1/sapdata3
[root@xr1ora ~]# mount /oracle/XR1/sapdata4
[root@xr1ora ~]# df -h | grep sapdata
10.1.9.6:/gr1ora-clone-sapdata/sapdata1  100G   24G   77G  24% /oracle/XR1/sapdata1
10.1.9.6:/gr1ora-clone-sapdata/sapdata2  100G   24G   77G  24% /oracle/XR1/sapdata2
10.1.9.6:/gr1ora-clone-sapdata/sapdata3  100G   24G   77G  24% /oracle/XR1/sapdata3
10.1.9.6:/gr1ora-clone-sapdata/sapdata4  100G   24G   77G  24% /oracle/XR1/sapdata4

 

Adjust the file permissions on the sapdata directories.  We need to do this since the volume was created from the source system gr1ora with oragr1 as the owner of the files.  Ignore the “Read-only file system” message.  This is the Azure NetApp Files .snapshot directory and contains the source snapshot from gr1ora-sapdata that was used to create this volume.  This is a read-only file system.

 

[root@xr1ora XR1]# pwd
/oracle/XR1
[root@xr1ora XR1]# chown -R oraxr1 sapdata*
chown: changing ownership of 'sapdata1/.snapshot/GR1_clone__F365291B91B/sysaux_1/sysaux.data1': Read-only file system
chown: changing ownership of 'sapdata1/.snapshot/GR1_clone__F365291B91B/sysaux_1': Read-only file system
chown: changing ownership of 'sapdata1/.snapshot/GR1_clone__F365291B91B/undo_1/undo.data1': Read-only file system
chown: changing ownership of 'sapdata1/.snapshot/GR1_clone__F365291B91B/undo_1': Read-only file system
chown: changing ownership of 'sapdata1/.snapshot/GR1_clone__F365291B91B/temp_1/temp.data1': Read-only file system
chown: changing ownership of 'sapdata1/.snapshot/GR1_clone__F365291B91B/temp_1': Read-only file system

 

Delete the existing Oracle XR1 control files.  These will be recreated when the XR1_clone.sql script is executed.

 

xr1ora:oraxr1 51> rm /oracle/XR1/origlogA/cntrl/cntrlXR1.dbf
xr1ora:oraxr1 52> rm /oracle/XR1/origlogB/cntrl/cntrlXR1.dbf
xr1ora:oraxr1 61> rm /oracle/XR1/sapdata1/cntrl/cntrlGR1.dbf

      

(!) Note

 

GR1 source system has a control file in sapdata1. These control files will be recreated as cntrlXR1.dbf after the sql script is performed.

 

Recreate/Recover the XR1 database

The following will recreate the database as XR1.

 

As oraxr1 recreate the control files by running the XR1_clone.sql script.  You must be in the directory where you have the XR1_clone.sql file.

 

xr1ora:oraxr1 68> sqlplus / as sysdba @XR1_clone.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 1 15:13:15 2023
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 4563399440 bytes
Fixed Size                  8905488 bytes
Variable Size            2281701376 bytes
Database Buffers         2264924160 bytes
Redo Buffers                7868416 bytes

Control file created.

SQL>

 

The following will recover the XR1 database. 

 

You must ensure you have the Oracle archive log(s) that were generated when AzAcSnap had the source GR1 database in backup mode.  The archive log(s) must be placed in the /oracle/XR1/oraarch file system.  You must also rename the archive log(s) from “GR1” to “XR1”.  If you did not take note of the archive log(s) generated while the source GR1 database was in backup mode, Oracle will display the log(s) needed when you execute the recovery.  Take this time to ensure the log file(s) from source system GR1 archive log GR1arch1_90_1124574837.dbf is copied to /oracle/XR1/oraarch/XR1arch1_90_1124574837.dbf

 

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 3121925 generated at 01/01/2023 13:46:35 needed for thread 1
ORA-00289: suggestion : /oracle/XR1/oraarch/XR1arch1_90_1124574837.dbf
ORA-00280: change 3121925 for thread 1 is in sequence #90

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 3121972 generated at 01/01/2023 13:47:43 needed for thread 1
ORA-00289: suggestion : /oracle/XR1/oraarch/XR1arch1_91_1124574837.dbf
ORA-00280: change 3121972 for thread 1 is in sequence #91
ORA-00278: log file '/oracle/XR1/oraarch/XR1arch1_90_1124574837.dbf' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL>

 

XR1 database has been successfully renamed and recovered.  Add the temporary table space.  These commands can be found within the original trace file at the end of file and can now be executed on the SQL prompt.

 

SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/XR1/sapdata1/temp_1/temp.data1'
     SIZE 367001600  REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;  2

Tablespace altered.

SQL> exit

 

Start SAP Basis Post Processing

Every customer performing an SAP system refresh needs to execute post processing on the newly refreshed system.  Start SAP and begin Basis post-processing….

 

If you need to update OPS$ password here is a guided document provided by SAP.  This would happen if the source and target systems have different passwords.

 

GeertVanTeylingen_4-1672845278993.png

 

Demonstrating that the copy process has been successful display the spool entries.  SAP system XR1 now contains the same output devices as source system GR1 had at the time AzAcSnap performed the backup.

 

Current list of XR1 output devices

 

GeertVanTeylingen_5-1672845307783.png

 

Spool server is set to the source system GR1.  This would be considered a Basis post-processing step by updating spool server.

 

GeertVanTeylingen_6-1672845333112.png

 

GeertVanTeylingen_8-1672845356959.png

 

GeertVanTeylingen_9-1672845366377.png

 

Summary 

Running and administrating an SAP NW Oracle system is challenging.  Leveraging Azure’s unique capabilities including powerful M-series VMs and exclusive Azure NetApp Files storage solutions coupled with Microsoft’s AzAcSnap can make life cycle management tasks much easier.  Regardless of database size from 5TB to 120+TB SAP Oracle instances, AzAcSnap can capture an application consistent picture of the Oracle database with zero load on the system for fast recovery or to provide a baseline for a fast system refresh.  Every SAP shop must provide quality systems for testing and training purposes.  In addition, SAP shops must at times react to production issues.  No matter how much time and effort are spent on being proactive, there will be times during an SAP lifecycle that SAP Basis teams must react.  How fast and efficiently those SAP Basis teams can react can make all the difference.  Leveraging MS Azure NetApp Files with AzAcSnap provides a unique insurance policy that is exclusive in the industry.

 

Special thanks to Eric Fitzpatrick.  Still one of the best SAP Basis consultants to this day… and rocks the sweater.

 

Additional Information

  1. https://azure.microsoft.com/solutions/sap/#overview
  2. https://azure.microsoft.com/products/netapp/
  3. https://learn.microsoft.com/azure/azure-netapp-files/azure-netapp-files-solution-architectures#sap-anydb
  4. https://learn.microsoft.com/azure/azure-netapp-files/azacsnap-introduction

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.