The Easy Button: Fixing Security Issues When Migrating EPBCS to Production
Author: Dave Shay | | September 12, 2017
When we migrate EPBCS/PBCS from the Test instance to the Production instance, security doesn’t migrate correctly because Oracle uses different naming conventions between Test & Prod for the security groups.
planning-test Service Administrator planning Service Administrator
planning-test PowerUser planning PowerUser
and so on…
When you import your Test snapshot into Production, you do not get any kind of notification that security provisioning was rejected. For example, if you do not want “planning Viewer” to have access to the Workforce accounts, that access assignment is rejected because it was imported as “planning-test Viewer” and we are none the wiser.
Traditionally, we’d either manually redo the security setup directly in Production, and hope we remembered everything we did in Test (Seriously, don’t do this). Or, we would crawl through the snapshot and edit the various .csv and .xml files, and certain files would also have to be renamed. Been there, done that.
I proudly present…. The Easy Button.
Unzip it and save it in a place you can find it later when you need it. To ensure you’re paying attention, this download link expires in 30 days.
1. One time only, copy and paste this command to a command prompt on your computer (it doesn’t matter what folder you’re in) and press ENTER:
powershell.exe Set-ExecutionPolicy Unrestricted
2. When it is time to migrate, download the snapshot from the Source instance to your computer and unzip it using 7-zip from http://7-zip.org. Regular Windows unzip will bark about the long paths inside Reporting & Analysis. Unzip the snapshot to a path and folder name that does not contain any spaces anywhere within the fully-qualified path.
3. Copy the contents of the zip file you downloaded from my link above into the topmost folder of the extracted snapshot, like this:
4. If you are migrating from Test to Prod, double-click TestToProd.bat. If you are migrating from Prod to Test, double-click ProdToTest.bat. The script completes in less than 1 minute.
5.Use 7-zip to zip up your snapshot. Upload it to the target instance, and import it according to then-current best practices.
What the three highlighted files above do is search for and replace the instance-specific roles and also rename the instance-specific group files. You now have a repeatable process.
If we have a similar migration issue with FCCS, ARCS, and other Cloud products that have different group names between Test & Prod, this process could be adapted for them. Inspect the two .bat files and you should be able to figure it out. The .ps1 file should not need to be changed.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.