Refresh Database Content without reinstalling the database or Kernel for ABAP systems
Classic “Refresh or Move DB Instance” vs. New “Refresh DB Content”
Customer Situation:
Customers may need to setup a new system with existing DB content. This can be necessary when you want to create a test system or you want to refresh a system that has been corrupted with an existing proper backup.
The Software Provision Manager offers two slightly different ways to do this. In this blog I want to introduce those two functionalities and to show the differences.
Introduction:
It is possible to use the system copy procedure to export / import the DB content but this will install a new DB instance using the installation media. This option is called: “Refresh or Move Database Instance”
In many cases, it is better to refresh a system without having to install a new DB instance but only replace the DB content in an existing ABAP schema.
This new option is called “Refresh Database Content”
Background / Motivation
Customers should be able to refresh the DB content of an existing system as fast as possible.
The new process is faster, since the new DB instance does not need to be installed every time the DB content gets refreshed. It is also faster because the Kernel media and RDBMS media is not needed for that scenario. TCO is reduced, due to a minimal downtime of the system.
However, moving the refreshed system to another host is not possible with the new functionality. For this scenario, the classic option is still available.
Description: Classic “Refresh or Move DB Instance”
- Using the DB content on the same host it is called “Refresh”
- Using a different host to refresh the DB content it is called “Move”
-> Installation media is required and DB backup or migration export
-> New Kernel is needed (from media): download new Kernel media = time consuming
-> Database media (RDBMS server and client software) is needed = time consuming
-> New DB instance is installed = time consuming
Result: System refresh with existing DB content on the same or different host and new DB Instance
Description: New “Refresh DB Content”
- Refresh the database content on the same host only
-> No installation media is required, only DB backup or migration export
-> No new DB instance is installed, only DB content is replaced, this is faster as option “Refresh or Move DB Instance”
Result: System refresh with existing DB content on the same host on the existing DB Instance
For additional Information please see also: http://support.sap.com/sltoolset -> System Provisioning -> System Copy Option -> Guide for Systems Based on SAP NetWeaver 7.1 & Higher
Section “Copying Single Instances Only -> Copying the Database Only – Refresh Database Content”:
Couple of questions regarding this.
-Yogesh
Hello Yogesh,
thank you for the questions.
In fact this functionality is available for all DBs except MaxDB (I had a wrong screenshot in the Blog).
Yes, it will refresh the complete Database schema including transaction and master data.
Best Regards
Stefan
Hello Stefan,
this looks like a good development, however I do have a quick thing: it seems when using this function (or even when using the original): it seems it requires a file based backup. This basically makes both options irrelevant for a lot of customers executing point-in-time refreshes.
When executing either the "Refresh database content" or the "Refresh or Move database instance" I get at a certain point the following screen:
As far as I can see there is no way around this which would make restores from backup/restores tools impossible.
Did I miss something?
Kind regards,
Bart
Hello Bart,
in fact, if you want to create a complete system refresh, you need a consistent status of the system at a certain point in time. For the Software Provisioning Manager, this is done by a full DB backup or a migration export.
However, other tools from 3rd Party vendors may have their own solution to create a system refresh.
Best Regards
Stefan Jakobi, Product Management CLM
Great article. I was reading the system copy guide. We want to refresh our QAS system with PRO and I was thinking to do it with the new option "Refresh Database Content" as we only need to refresh our ASE database on our distributed system. Is this option going to work? Can we change the schema from pro to qas with this?
Hello Miguel,
thanks for the question.
There are two options with the "Refresh Database Content" scenario.
Option 1 is Backup/Restore based, Option 2 is Load based. When using option 2 (load based) you can change the schema from pro to qas.
Best Regards
Stefan
Hi,
really desperate trying for hours this new option.(It seems that i don't have available in the menu the "old" option) I keep getting the attached error, and didn't found anything online about this.
What do you think?
Hi Paul, I am getting exact error message. Did you manage to resolve this?
Hello Paul,
sorry that you face this problem.
For that case please open an SAP Support ticket so we can analyze the error and solve the issue or transfer the issue to the right team inside SAP.
Thanks and best regards
Stefan Jakobi
Product Management CLM
Hi Stefen ,
Thanks for wonderful blog,
Our customer want to refresh data of ERP Sandbox system from ERP Production. Production SID (PXX) is different from Sandbox SID (SBX)
In system copy guide Chapter "6.41. Copying the Database Only - Refresh Database Content on MS SQL Server", there are three options as mentioned below.
Please advise which option will be suitable for my requirement.
1. Using the SAP Standard Method. (Export and Refresh DB Content)
2. Procedure Using Database Backups
3. Procedure With Copying the Database Device Files of the SAP database
Please Refer page 116-117 from system copy guide
https://help.sap.com/doc/b600445be5d24a4db946e16287e849f7/CURRENT_VERSION/en-US/SystemCopy_71X_win_abap.pdf
Your agile response is highly appreciated.
Thanks
Abuzar Ehteshamuddin
Hello Stefan Jakobi
I already have a copy environment of ECC production, last refresh was made in 2016 called BUBBLE.
I only wish to realize refresh of the database, copy of the productive environment base.
my question is, in this option of "refresh or move database instance" it will reinstall my sap system already installed losing the sidadm etc ...
sidadm and sidDB bubble - BSE
sid ECC production - EEP
thank you so much
Hello Mr. Piati,
with the option “Refresh DB Content” your system environment of the "Bubble" system will remain the same, only the DB content will be replaced.
If you want to reset the complete system, you can use the classic option: “Refresh or Move DB Instance”.
Hi, Stefan
Previously, export/import procedure is used by database team to redistribute data into datafiles. New datafiles may be added due to some needs.
I want to know is it ok to use “Refresh DB Content” to do it?--export the database, and import again to the same place, just with some new datafiles added.
If the database is already existing, could SWPM ask for the datafile locations and datafile numbers? If no, should I do as belows?
1, firstly export.
2, delete the database,
3, refresh the DB content by import and define how many datafiles will be created and where are their location.
Best regards,
Cherry.
Hello Cherry,
>>I want to know is it ok to use “Refresh DB Content” to do it?–export the database, and import again to the same place, just with some new datafiles added.
It depends on the database that you want to refresh but in general that should be possible.
If it is not possible from SWPM you could create and distribute the datafiles after the refresh with DB tools.
As for the second part, for SAP standard method R3load (to be more precise):
With the Option: “Refresh Database Content”, the DB Instance itself is not newly installed. But the target DB is dropped and re-created and the content from the R3Load-export is imported.If you delete the database, you cannot import anymore.
Hi Stefan,
Thanks for this helpful blog. I have some questions on this process. When using the SAP method (export/import) to refresh the 'database content only', would it allow to use the export dump from source (using R3load) in the target system when using Generic option> Refresh database content only? It seems like in the target system, we must use the database backup from the source system when using this option correct? If so, can you please provide some steps/document on handling database backup for SWPM in target system.
Thanks,
Dipendra
Hello Dipendra,
The database you import when using the “refresh DB content” expects basically the export from the same system source.
Example: You made a system copy from a DEV system to a Test System. From time to time you want to refresh the content in the Test system and make a “refresh DB content” with the data from the DEV system. This will work because both systems have the same source.
If you have other systems in your landscape with totally different origin, you cannot refresh the Test System with "refresh content only" because the source is different. In that case you need to use the classic “move database instance” option.
Please use the documentation mentioned in the Blog. Also, the procedure in SWPM is a step by step approach and should be self explaining.
Hi Stefan, can you clarify this a bit. What do you mean by 'same source' . Generally all SAP persistence is in database (yes there is kernel on Filesystem). So a 'refresh DB content' should work for any database to any database right? Ofcourse a customer would not like to do this on a ECC test system with a backup from a BW prod system. The customer has to do this logical check but theoretically, you could use refresh DB content do do any database to any database refresh (as long as the target DB is already setup).. Only if you want to 'install or setup' a new db instance for a new SAP system, the classic approach would be necessary. Please confirm this understanding
Hello,
yes, system refresh will also work across db, as long as source and target system are of the same origin.
Strictly speaking "same origin" means that the target system was created from a system copy. That was the initial idea when offering this option.
Many other potential scenarios as you describe might work as well but were not tested and can therefor only be done at your own risk.
Hi Stefan
Thanks for this helpful blog. We are going to migrate an ERP 6.07 environment with oracle 11g to new server with oracle 12c. We have already done the first system copy successfully with all the necessary configurations, now, to reduce time during the final migration it is possible to perform a "Refresh Database Content"?
Thank you
Best Regards
Hello Willmer,
as the new system comes from a system copy, it should be possible to refresh this system with a "Refresh Database Content" from the original system.
Hi Stefan
The doubt that I have is that in the origin server has oracle 11g and the server destination oracle 12c and when using the "refresh database content" the implicit oracle upgrade to 12c is maintained (using export/import)
Thank you
Best Regards
Hello Willmer,
if you do a Oracle update from 11g to 12c, only db files are updated, the content of the SAP system inside the db does not change.
So an unload of the db content from SAP with R3load in the 11g system should fit into the new db with version 12c.
Hello Stefan,
The source system is oracle rac asm and the target system is oracle single instance file system.
Is it possible to refresh the backup of the source system to the target system by The Software Provision Manager?
If not , is there any other way?
Hello Yu,
sorry for the late reply.
I have a question to your comment: Do you have one system running on Oracle RAC ASM and you want to migrate it to Oracle single instance?
Or do you have two systems and you want to refresh one system with the content from the other system?
In the second case this should be possible. You can refresh your system with the corresponding Backup based option in Software Provisioning Manager. Important information on how to restore the backup is described in SAP note 1844380 subsection 18 with BR*TOOLS .
Please note that you need some Oracle knowledge and make sure that you test this prior to use it on your productive system.
Hellow Stefan ,
Is it possible to use "Refresh Database Content" for target system on MSFC of production system?
Thank you
Best Regards
Hello Nakato,
I think this should work.
The Microsoft Failover Cluster should not have an impact on the loading of the data in the database.
Best Regards
Hello Stefan,
Thanks for the article. We nneed to performa a full export/import of our production system to get rid of problems described under 2137109 - Data Object ID limit
As per your blog, I think we can use the option "refresh database content" as we will take export on the same host. Is my understanding correct?
Thanks in advance!
Hi Stefan,
Blog is very useful. Refresh DB content can be done for MCOD database ( oracle12). I have solution manager system ( ABAP and JAVA Stack already split). i am able to perform the refresh DB content for ABAP stack successfully via export/import method.
For JAVA stack I am getting below error .
An error occurred while processing option Generic Options > Oracle > Refresh Database Content(Last error reported by the step: SQL statement or script failed. DETAILS: Error message: ORA-01501: CREATE DATABASE failed ORA-01101: database being created currently mounted by some other instance Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01101: database being created currently mounted by some other instance
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
I am thinking does DB refresh content support MCOD oracle DB. I am not able to find any info on the system copy guide as well. It would be great help if you can suggest some hints or solution how to proceed.
Thanks
Poornima
Hello Poornima,
there is a chapter on MCOD in the installation guide:
https://help.sap.com/viewer/DRAFT/08baebca0fef470389e4a4ebcb46b879/CURRENT_VERSION/en-US/925d23f8451c4598aa7778f83e4e1cca.html
However, MCOD is no longer recommended, see SAP note 2146542
In case of that error that you mention, please open an incident ticket with the SAP Support.
Best Regards
Hello Stefan,
thanks for your blog post - helpful! I am using the refresh DB option quite often for system copies.
I do have a question about this option concerning MaxDB when SWPM does the restore for me: As far as I know I have to hand over a DB backup in one(!) single file to the SWPM. Is this the only way or is SWPM able to restore the backup out of a, let's say, 4-file-backup? And if so, how? 🙂
Usually the customers running backups on parallel streams (2 or 4 files). So for the SWPM I have to create a separate single files backup which also taktes its time.
Hope you can enlighten me here. Looking forward to your answer.
Thanks in advance
Daniel
Hello Daniel,
SWPM refresh via backup recovery on MaxDB was developed only for single backup file. Initially it was only thought to support the setup of small test systems, in the meantime customers refresh systems from the backup of bigger production systems etc.
Also MaxDB supports many type of backups: tapes, files, single, parallel, third party backup etc. To support them all in an automated process, would mean a big development effort which is currently not planned.
However, there is a workaround which you might want to use: SWPM provides an option for manual recovery. It runs until the step where the target DB is configured. You could then apply the backup manually and after that continue with SWPM.
Thanks and best Regards
Hello Stefan,
Thanks for the quick and clarifying answer. Indeed I have seen this manual restore option but never tried it so far. It is quite comfortable for us to drop a backup file into SWPM and it does most of the work.
Maybe I will set up a scenario to test this method.
Regards
Daniel
Hello Stefan
I’d like to ask another question. I was always using the Refresh DB Content Option for SWPM > 7.0 systems. This time I have to work with 70SWPM10SP28_0-20009707.sar since the System is a NW 7.01.
Unfortunately the SWPM seems to behave differently in this version. Since I have a backup in a single file I chose “Homogenous System Copy…” and later on “Recreate Instance”. Normally the SWPM let me chose to “Keep the DB configuration”. In this 70SWPM this option does not show up. I have to go through configuration for the logs and enter all of the data volume locations, and so on.
This does not feel quite right. Am I doing something wrong? Is there a possibility to use the other SWPM version for this Refresh scenario?
Update #1: I’d like to add some more analysis information. By checking the file “inifile.params” on a SWPM > 7.0 I found out that there is a section looking like this:
…
# Flag to re-create existing SAP MaxDB instance
SdbPreInstanceDialogs.UseRecreateInstance = force_recreate
# Possible values: recreateFromScratch, keepParameter
SdbPreInstanceDialogs.recreate_option = keepParameter
# Password of database administrator ‘superdba’
Sdb_DBUser.dbaPassword = …
…
On my 70SWPM it looks like:
…
SdbPreInstanceDialogs.UseRecreateInstance = force_recreate
# Password of Database System Administrator ‘superdba’
Sdb_DBUser.dbaPassword = …
…
The Line with the “keepParameter” is missing.
Update #2: When using the SWPM > 7.0 the dialog appears. Dont know if I can refresh with this SWPM tough.
Thanks and regards
Daniel
Hello Daniel,
thanks for the feedback.
We do not recommend to use the SWPM 7.1 version for 7.0 systems.
As for the missing parameter, the colleagues are investigating into this issue.
Additionally I would like to ask you to open an incident on this problem, so that we can analyse and communicate better.
Hello Stefan,
Thanky you for helping me out here!
Unfortunately due to time pressure I hat do continue on my on. I can say that SWPM 7.1 worked for Refreshing the DB content in my case. There were no (at least) obvious errors in the procedure.
Since I’d like to help you as best as I can, is there any way for me to do so? I believe opening an incident wont help anymore?
Regards
Daniel
Hi Daniel,
good that this procedure with the 7.1 worked out.
There are many things that work out, but SAP can not recommend them because theses where not officially tested and approved.
No need to open another call, we follow up on the issue anyway.
Hi Stefan,
How is this “Refresh” differ from doing database backup+restore from source to target system?
E.g to copy DB content from PRD to QA in DB2, one can just do a backup from PRD and do redirect restore to QA, without touching SWPM.
The same for HANA DB.
When would I need SWPM?
Hello Bobby,
you can also do a DB backup/restore and there are other tools out there that will do the same or similar as SWPM.
We as SAP can not know all features and post restore activities of these tools and therefor can only comment and recommend the official SWPM tool.
Hello Stefan,
Thank you for the reason.
Regards,
Stefan,
Can we skip the step, Database postload activities (around 86%) in this Refresh Database Content task? As this SWPM is performed on DB instance, and customer environment only allows DB port connection between ASCS/PAS and DB instance, thus, while SWPM tries to start the ASCS instance, which is already running on ASCS/PAS server, the step never knows ASCS is running.
The log also shows Connection Refused in the process, sapcontrol -nr <ASCS instance #> -host <ASCS host> -function GetInstanceProperties.
Suppose we can skip the step, do you suggest we use Step State Editor?
Also, can we make up the step Database postload activities manually outside SWPM?
Regards,
Dwight
Hello Dwight,
the step "Database postload activities" can not be skipped as it does more than just start the DB instance.
If for some reason this customers network environment does not allow this step to finish correctly, please open an SAP Support Incident for advice what to do in such a case.
Stefan,
Thanks for the comment.
Regards,
Dwight
Hi Stefan
we want to make a system copy from PROD to TEST.
We have decided to the method "Copying the Database Only - Refresh Database Content" Using the SAP Standard Method.
So if I understand correctly, "SAP standard method" - is export / import method, right?
We exported the DATABASE with "Database Instance Export".
During the system copy on the target host we get this error:
The copy guide says the following:
"If you receive a message that the schema already exists, you must delete the existing database schema. You can do this in one of the following ways:
- Automatically while running the Refresh Database Content option by choosing to drop the schema on screen IBM Db2 for Linux, UNIX, and Windows - Drop Existing Schemas"
However, we do not get this message (drop existing schemes or a message that the schema already exists).
And why is the new DB software package required?
Many thanks
Best Regards
Hello Tatjana,
from your comment I see that you use two different version of SWPM. Picture 1 shows a screenshot of an old SWPM and screenshot 3 shows a much newer version.
DB refresh has to be done with the same version of the SWPM.
Also, from the error-message screenshot we can not identify exactly where the error comes from. Therefor we would need to analyse the logfiles.
In a case of such an error, it is best to open an SAP Support Incident and attache the respective logfiles. The colleagues from support can analyse the logfiles and, in case the problem can not be solved immediately, distribute the incident directly to the right development team within SAP.
Hi Stefan,
First many thanks.
Must both servers have the same disk structure for the backup / recovery method (when I copy from PRO to TEST)?
Thank you
Hi Stefan,
I am planning to do the above procedure for moving our Solman DB from one host to another which is running on DB6 wherein ABAP and JAVA is on same DB separated by scheam SAPR3 (ABAP) and SAPPSSDB (JAVA). In this case, we are planning to move DB option from SWPM using backup/restore method will this work for moving both ABAP & JAVA ssytems? Please advise?
Regards,
Ananth
Hello Ananth,
we support Systemcopy ABAP, Java and Dual Stack with SAP tools and with backup / restore.
For Solman 7.2 see the following section of the documentation
Copying the Database Only – Move Database Instance
It should be possible to move the db separately.
Hi Stefan,
Thanks for the excellent blog
Please will you clarify below
We are going to migrate an ERP 6 EHP6 (6.06) with SQL Server 2008 R2 to new server with SQL server 2017 with different Hostname.
We have already done the first system copy using export /Import, Now for cutover and to capture delta changes from source system to Target, We are planning to use the “refresh database content” option. Since the source Database version is SQL Server 2008 R2 and Target Database version will be SQL Server 2017 is this supported.
Thank you
Regards,
Prakash
Hello Stefan,
Can we use this option "Refresh DB Content" for migrating/copying content from Big Endian HANA 1.0 DB to Little Endian HANA 2.0 system that is already running SAP? Thank you
Regards,
-Kalyan
Hello Kalyan,
Simply put, HANA 1.0 on Power backups can only be recovered to HANA 1.0 on Power.
So to move out of HANA 1.0 on Power (Big Endian) to any other Little Endian platform, a migration is necessary.
https://launchpad.support.sap.com/#/notes/2537080
Hi Stephan
planning to do Db refresh HANA 2 .0 from power le (QA)to red hat Linux (DEV)
both on Hana db but different OS , is this option will work out or need to go for export import method every time?
Hello Ranjit,
If you are referring to backup compatibility, as long as both HANAs are 2.0, the backups are compatible
https://launchpad.support.sap.com/#/notes/1642148
Backups created with SAP HANA 2.0 are compatible with both supported hardware platforms (Intel and IBM Power). You can recover SAP HANA 2.0 using data backups and log backups created with SAP HANA 2.0 on either an Intel-based system or an IBM Power-based system.
Compatibility of Backups of SAP HANA 1.0 for Recovery to SAP HANA 2.0 (IBM Power and Intel)
(Data Backups and Log Backups)
(IBM Power)
(Intel)
Hope that answers your question.
Hi Stefan,
Need your inputs.
We are in process of building N+1 system using the SWPM Export/import method. After the build we want to upgrade the system.
Source : ECC - EHP5 7.02 OS - RHEL5 DB - Oracle 11.2.03
Target : ECC - EHP 5 - 7.02 - OS - RHEL 8 DB Oracle 19C after the import we want to upgrade to EHP8.
Oracle 19 has new features like BIGFILE table space and Advance compression and we want to use this features during the import DB installation phase.
We know that SWPM doesn't support BIGFILE table space but we want to check with you is there any other methods using which we can use the BIGFILE table space and Advance compression during the DB import phase.
Thanks in advance,
Rajanikanth.
Hello Stefan,
Can I have some questions? Because I confusing with System migration methods. Could you please advise me of my requirements?
Our consulting team consults on our System. We want to copy that system into customer DEV System ( OS and DB are same, APP product release same 1909 but some SP Stack different ). How can I continue which migration method? We also want to retain import queries in the target system.
Thanks in advance,
KLZ
Hello Lin Zaw,
if I understand correctly, you want to copy a system and reuse as DEV system and the only difference is the SP stack.
So I would suggest you simply copy the system and add the SP stacks afterwards.
About the import queues I am not 100% sure. Probably you need to rebuild them after setting up the copied system.
Best Regards
Hi Stefan,
Thank you so much for your reply. Can I use the database refresh method in this seturation? SID is different.
Thanks and Best Regards,
KLZ
Hello Lin Zaw,
Classic “Move DB Instance” would work as well.
You will then get a System refresh with existing DB content on new DB Instance.
Best Regards
Hello Stefan,
If i install a completely new SAP system (new and empty database instance), then can i use "Refresh Database content option" from latest full backup of source system?
Will it work?
Thanks and Best Regards
RY
Hello Yadav,
if the backup was taken from the same release version as the new installed system, that should not be a problem.
Best Regards
Hello Stefan,
We are using Refresh Database Content option.
Our goal is declustering using export database with declustering option and import content to the same database.
Environment:
SAP is on Windows and Oracle (RAC) is on Linux.
Additional I installed Gateway for Database because lack of SAP on database host.
Questions:
Thank You
Best Regards
Hello Karol,
Your question1:
“Refresh DB Content”:
-> No new DB instance is installed, only DB content is replaced
Existing DB content on the same host on the existing DB Instance
Your question2: No, only if you have specific requirement.
Best Regards
Hello Stefan,
I have a question about one screen about Oracle database parameters:
I have two options :
1.Install MCOD (Multiple Components in one Database)
2.Install database or recreate an existing one
I selected 1 option but I have error " An existing database cannot be selected"
Do you know how is the reason ?
Thank You
Best Regards
Hello Karol,
from the screenshot I can not judge what the concrete problem is.
In such a case it is better to create an SAP support ticket and attach the necessary logfiles. Only then can the SAP experts analyze the problem and give the correct instructions.
Thanks and best regards
Hello Stefan,
Good day!
Just a quick question about refreshing DB content.
After a successful refresh, is it needed to do the follow-up activity or is that only for the copy/move DB?
Thank you!
Russell