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

Oracle 11 DataPump, and connecting to a shutdown database.

Goal:
To use DataPump to Export and Import a Oracle database. 



Problem:
I used to use the 'imp' and 'exp' command. These commands are no longer, by default, available from Oracle version 10g and above. So it was time to start using the EXPDP and IMPDP commands.


Solution:


Step 1 - Create a Oracle Directory

First I want to do a Export of my database. I use a Windows Server 2012, Maximo 7.5.0.5, with Oracle 11gR2 database.

When using the exp command I could specify a directory where the dump file should be stored. The EXPDP command uses a Oracle parameter called 'directory'. This parameter specifies where to store the dump file. In my example I use a local directory placed on the server. In a production environment you could create a shared folder on the server so you can manage your dumps from an administrative workstation.

I created the following directory by executing the script in SQL Developer as  SYS user;


CREATE OR REPLACE DIRECTORY DIR01 AS 'E:\DataPump';

GRANT READ, WRITE ON DIRECTORY DIR01 TO system;

COMMIT;


In this script we grant the system user the permissions on the directory. You should always use the system user to do your exporting and importing. 

Now we have a directory. Oracle also comes with a default Datapump directory. If you want to list all the configured directories, you can query the ALL_DIRECTORIES view.

SELECT * FROM ALL_DIRECTORIES;

On my test server this will give the following result:

Step 2 - Export

Now we are ready for the Export. I created a export.cmd file in the same directory as the output from the directory. 

The content of the cmd:

expdp system/sys schemas=MAXIMO directory=DIR01 dumpfile=ctginst1.dmp logfile=Export.log reuse_dumpfiles=yes

Here we see the system account with password sys, the schema name we want to export, the directory where the export should be stored, the name of the dump file, the name of the logfile and the option to overwrite the dumpfile.

In order to run the Export I open a dosbox as Administrator and navigate to the location of the export.cmd.

Then I type in export.cmd and press enter. The export start to run.

After a short while I got an error message. Might not be occuring at your configuration but I got the error shown below: 

This is the error:

ORA-04301 error
ORA-39097: Data Pump job encountered unexpected error -4301

I managed to solve it with information from this page:
http://arjudba.blogspot.nl/2008/12/expdp-fails-with-ora-39125-ora-04031.html

In short, a Oracle memory problem.

To fix it, connect to the databse as sys and run:

show parameter sga_t;
show parameter sga_max;

If they are low (below 300M I gues)  you can in crease these by running:

alter system set sga_max_size=300M scope=spfile;
alter system set sga_target=300M scope=spfile;
commit;

As setting of SGA_TARGET is depend on SGA_MAX_SIZE and to change SGA_MAX_SIZE we need to bounce the database (restart) in order to effect.

I use SQL Plus to login to the database as sys and then execute:

startup force;  (see below for an update on the startup force, this will most likely not work, you have to follow the steps below)

Now I run the export again, and huuray! it works

Update 11 september 2014
After working with this configuration for a while the problem came back! I Checked the parameters, but they still where at 300M.
First I stopped the MXServer and then I bounced the database. While bouncing the database with the startup force; the database wouldn't startup anymore and I couldn't connect. I got the error below:

ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
SQL> startup mount
ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

I decided to reboot the server. After the reboot, the database was back online, so I decided to try the export again.
This time the export was succesfull. So no changes made, just a reboot...

To prevent the error with a startup force; I used the information from this site: 
http://stackoverflow.com/questions/12470893/oracle-10g-issue-with-startup-mount-command-ora-24324-ora-01041

So it seems that I first have to shutdown the database and then use startup force; 
The test:

I login to SQL Plus as sys, then execute:
shutdown immediate;

Then I close SQL Plus and start it up again. But when I try to login I get a ORA-12514 again.
This seems normal, because the database is down, but is there a way to bring it back to life with a startup command then?

With Google I found this:
https://community.oracle.com/thread/2275156?tstart=0

quote:
assuming that after you shut down the database, you want to log in to the database through the listener
by default the database uses dynamic registration and is available through the listener only in MOUNT or OPEN state
to be able to "log in" to a not running, or a NOMOUNT database (as SYSDBA), you have to register the database instance manually in your listener.ora

Ok, makes sense, let's do that.
I go to my 'listener.ora' located on my machine  in 'E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN' and i added the SID for my database to the SID_LIST_LISTENER.

(SID_DESC =

      (SID_NAME = CTGINST1)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)

     )

Next I restarted the TNS Listener Service

Now I opened a SQL Plus and logged in as sysdba:

Next I execute startup force; 

and whoohooo :) succes!

I also encountered a ORA-00333 error at one point when trying to startup the database on my VM. I came across this great post that fixed it:
http://shaharear.blogspot.nl/2013/06/recover-database-from-ora-00333-redo.html 

Step 3 - Import

So now we have a backup, lets see if it is a good backup by dropping the Maximo database and restoring it with the created dump from the previous step.

First, drop the database by connecting to the database as sys and execute the following command (Dropping the Maximo user and recreating it is standard IBM procedure, so I will not post any screenshots etc. For more information about this check:  http://pic.dhe.ibm.com/infocenter/tivihelp/v23r1/index.jsp?topic=%2Fcom.ibm.mam.inswas.doc%2Finstall%2Ft_manconfigdboracle11.html

Now we have an empty Maximo schema in our database.

Next I created import.cmd in the same directory as the dump file and put the following line in it:

impdp system/sys@ctginst1 directory=DIR01 dumpfile=CTGINST1.DMP logfile=import.log

NOTE:
If you want to import to another schema (for example you load a dump from a production environment to a Test environment you need to use the remap_schema=MAXIMO:MAXTEMP parameter in your import.cmd.
So if the production is MAXIMO and the target environment is MAXTEMP the complete cmd file would be:
impdp system/sys@maxtemp directory=DIR01 dumpfile=CTGINST1.DMP remap_schema=MAXIMO:MAXTEMP logfile=importCTGINST1ToMAXTEMP.log


Again I run it from a dosbox and surprise surprise, a error pops-up... :(

ORA-39083: object type VIEW failed to create with error:
ORA-00928: missing SELECT keyword

Again Google was my friend, and I found this page with a solution:
http://sunkupuli.com/2013/11/20/datapump-import-impdp-reports-ora-928ora-923-ora-39083-when-importing-views-into-al32utf8/

According to the site the issue only occurs on databases of version 11.2.0.2 with multibyte character set.

So in order to fix it you could install patch 11.2.0.3 for the Oracel Database, or, like I did, use this workaround;

Connect to the database as sys and execute the following:

alter system set events '31156 trace name context forever, level 0x400';
COMMIT;

Now I try the import again:

And ......   done. :) 
I start the websphere server again to check if Maximo works again:

Update:
I created extra database for this exercise, but to save resources I set the Oracle Service for that database to Manual so it won't start when rebooting. I want to manually start it when I need it.

But now when I do the DataPump the following error occurs:

UDE-12560: operation generated ORACLE error 12560
ORA-12560: TNS:protocol adapter error

To solve this I need to specify the SID. To do this you simply start a dosbox and type:
set ORACLE_SID=orcl

Even better is to put it in the Export.cmd.
So the complete export.cmd will be:

set ORACLE_SID=ctginst1

expdp system/sys schemas=MAXIMO directory=DIR01 dumpfile=ctginst1.dmp logfile=Export.log reuse_dumpfiles=yes

Now I don't know if it occurs because I stopped the one of the Oracle Services or if I now have two SIDs. ut no matte what, this will fix it :)

GNZ.

Install and Enable a second language in Maximo 7.5.0.5

Goal:
Install a second language in Maximo 7.5.0.5 on a Windows 2012 server.

Problem:
I installed my Maximo 7.5.0.5

with the English language. Now I want to install Dutch as the second language.

When I login i want to have the option to select my language. Now there is no option for that:

Solution:

Step 1 - check if language if installed

Sometimes the language is installed but not enabled. Go to the installation directory and check if the language folder is located in tools/maximo. In this example I check my installation directory E:\ibm\SMP\maximo\tools\maximo

Check inside the folder for a folder named xliff and check if the folder contains data. For some languages the folders are there, but there only is a a_mif folder.

When you want to create a custom language you could edit the xliff files. Also if you want to check if the languae is available, you could open the files. Because if you open an xliff file you can see that it is a sort of xml type file you can read and edit

Step 2 - Install a language (optional)
  
If the language folder isn't available you can add it by running the Maximo installer. In this example I use a Windows 2012 server, so I use the method for starting the installed as described in a other post on this blog (http://sometimesiliketopretend.blogspot.com/2013/12/maximo-7505-on-windows-server-2012.html) So for more details why I run the installtion this way, refer to this post.

Start the Maximo installation:

Now just click through all the screens, as they will just tell you what is installed

It will tell me a newer version is installed since i run the 7.5.0.0 installer. Click [Next]

Then after a short while the screen with languages pops up.
It will show what languages are installed and you can check the languages to install. As you can see I also had the Spanish, German, French and Dutch language installed. Now I add the Danish language for the purpuse of testing this, and click [Next]

There already is a 'da' folder in the E:\ibm\SMP\maximo\tools\maximo location. But it doesn't contain any data (except the a_mif)

I check both options and click [Next]

In the summary I click [Next]

Then I click [Install]

Wait untill it is finished

When finished a dialog pops up with the notification that warnings are reported in the log.

First I click [Done]  this way the logs will be unlocked and updated.
Then i navigate to E:\IBM\SMP\logs and open the CCMDB_install.log

I search for the warning and found it on line 561

Apparently the license could not be copied.

When i browse to E:\ibm\SMP\ there is no license folder.

There is however a E:\ibm\SMP\licensetemp\V7.5.0.0\Base_Services folder. In there .txt files are stored with the license agreement. So nothing to worry about I gues. Maybe another thing related to the installation in Windows Server 2012?

I decide to run the taskrunner. Open a Dosbox as Administrator and go to E:\IBM\SMP\scripts and run
taskRunner.bat CONTINUE STOPONERROR

So now we have the xliff files for the 'da' language in E:\ibm\SMP\maximo\tools\maximo.


Step 3 - Enable a language

Next we need to to tell Maximo to use the xliff files. I will enable the nl language

First we stop the MXServer.

Note: I tried to login to Websphere but got a 'This page can't be displayed'

Solved it by starting the ctgCellManager01 Service via the Windows Services manager.

Now i can login to Websphere and stop the MXServer

Now open a dosbox as Administrator and go to E:\IBM\SMP\maximo\tools\maximo and run TDToolkit -addlangnl

Wait until the task is finished.

Now we start the MXServer and go to the login screen of Maximo

And the enabled Language is now selectable:

GNZ