Monday, July 28, 2014

Restore a xml file from a database dump, create new database for this

Goal:
To restore a Maximo application (front end, xml) from a database backup. Also I do not want to take down the production database.
I use Maximo 7.5.0.5 with an Oracle 11g R2 database.

Problem:
I modified an application in Maximo through the Application Designer. I moved some fields and saved it. Now it doesn't meet my requirements and I cannot get it back the way it was because the sections are really hard to get back again
.
I didn't export my application to XML before I did the changes (please do so in the future...) but I do have a database dump from the last day in which the application was the way I want to be.

I could drop my database and load up the old database but that way all the changes and all the data is overwritten and restored to the point when the database dump was made. I only want to restore one front end of an application (in this case the Preventive Maintenance application (PM))


Solution:
Since applications are stored in XML and this XML is stored in the database I can retreive the old XML from the database dump. Since I do not want to take the original database down I will have to load up the backup database next to the original database.


Step one - Connect the backup database dump.

I will create a new database here, but most of you will most likely have a test environment in where the database can be loaded. If available, you can skip this step and just loadup the dump in your test environment.

Create a database to load the dump in. 

Start up Database Configuration Assistant. (You can find it in you start menu or on the Windows Metro style start menu)



Click [Next] in the opening screen.




Choose 'Create a Database' and click [Next]



 Since this is just for restoring a file from the database and to run Maximo from I will choose 'General Purpose or Transaction Processing' and click [Next]



I enter 'MAXTEMP' as the 'Global Database Name' and as the 'SID'



In Step 4 I uncheck the 'Configure Enterprise Manager' on the 'Enterprise Manager' tab.



On the 'Automatic Maintenance Tasks' tab I uncheck the 'Enable automatic maintenance tasks' and I click [Next]


I set the password to 'sys' for all accounts and click [Next]



In the pop-up I click [Yes] 



The next screens I leave as-is






On the last step make sure 'Create Database' is checked and click [Finish]



In the confirmation screen click [OK]



The following progress screen comes up:



When finished, the following screen will pop-up, where I click [Exit] 



Next I create a new connection in the Oracle SQL Developer.


I the conenction is not available, i could be that the connection needs to be added to the tnsnames.ora. Mine is located at: E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN



If the following lines are not available, you should add them and try to connect again in SQL Developer.

MAXTEMP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MAXTEMP)
    )

  )


Now I create a MAXTEMP schema and load the dump. For details on how to use the Oracle DataPump check my other post:
http://sometimesiliketopretend.blogspot.nl/2014/07/oracle-11-datapump.html

Also check out this post about configuring a database:
http://pic.dhe.ibm.com/infocenter/tivihelp/v23r1/index.jsp?topic=%2Fcom.ibm.mam.inswas.doc%2Finstall%2Ft_manconfigdboracle11.html

In this case I needed to create the tablespaces before I could create the user and load the database.

Create tablespace maxdata datafile 
'E:\app\Administrator\oradata\MAXTEMP\maxdata.dbf'

size 1000M autoextend on;

create temporary tablespace maxtemp tempfile  
'E:\app\Administrator\oradata\MAXTEMP\maxtemp.dbf'

size 1000M autoextend on maxsize unlimited;


So now the database dump is loaded and we can query it from SQL Developer.




Step two - Retreive the XML and load it.
Now we have the database we can get the XML from it. XML files for applications are stored in the MAXPRESENTATION table. So if we query the table like so:

select presentation from maxpresentation
where app = 'PM';

We would get the XML for the PM application.
In the 'Query Result' right click on the result and choose 'Export'



Now in the export screen modify it as follows;
Format: XML
Encoding: UTF-8
I also changed the name to match the exported application.



Click [Next] and then [Finish]

Now open up the file in Notepad++ and first delete the section as marked in the screenshot (lines 2, 3 and 4)




Then scroll down and delete the last rows
(the last 3 lines)



Now save the file and open Maximo. Go to the Application Designer and click on the 'Import Application Definition' icon
Choose the saved file and click [OK]




Now go to the Preventive Maintenance application to check if the application works.



GNZ