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

My points of attention when upgrading from 7.1.1.7 to 7.5.0.5

Goal:
Points of attention when upgrading from Maximo 7.1.1.7 to Maximo 7.5.0.5.

Problem:
Not really a problem, but after an upgrade from 7.1.1.7 to 7.5.0.5 I encountered some (relatively easy to fix) issues. It would have been nice if these issues where solved when upgrading.

Solution:
Just a little list of found issues when upgrading from 7.1.1.7 to 7.5.0.5.



Issue:
Communication templates with default name are overwritten

Solution:
Copy-pasted the description from an backup Maximo to the new Maximo.



Issue:
Refferential Jobplan doclinks not showing on workorder. Note that I use 'Jobplan revisions' in 7.5.0.5.

Solution:

In 7.5.0.5 with Jobplan Revision, an extra relationship is created on workorder (pluscdoclink) the default relationship (doclink) is also modified with the revision statement in the jobplan section of the where. (Bold and red in the example below)

((ownertable='WORKORDER' and ownerid=:workorderid)
or (ownertable='WOACTIVITY' and ownerid=:workorderid and :istask=1))
or (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid))
or (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid))
or (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid) and pluscrevnum =:pluscjprevnum) )
or (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid))
or (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid))
or (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid))
or (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid))
or (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable in (select value from synonymdomain where domainid='WOCLASS')))
or (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum))

The field pluscjprevnum is a new field on the workorder table. This contains all null values.
The pluscrevnum however is filled with 0. So the comparison isn't working here.
So we just need to fill the revisionnumber on the WORKORDER table.

select count(wonum)

from workorder
where pluscjprevnum is null
and jpnum is not null
and istask = 0
and historyflag = 0;

update workorder
set pluscjprevnum = 0
where pluscjprevnum is null
and jpnum is not null
and istask = 0
and historyflag = 0;

select w.wonum, w.jpnum, w.pluscjprevnum, j.pluscrevnum
from workorder w
 inner join jobplan j
 on j.jpnum = w.jpnum
where w.jpnum is not null;


select w.wonum, w.jpnum, w.pluscjprevnum, j.pluscrevnum
from workorder w
 inner join jobplan j
 on j.jpnum = w.jpnum
where w.jpnum is not null
and w.pluscjprevnum = '0';


Issue:
When selecting a Jobplan that has at least one revision on a PM in the 'Job Plan Sequence' tab the following error pops up:

Error we received: BMXAA4082E - The WHERE clause is either empty or returning more than one row for the crossover domain specified for {0} object. Use the Domains application to correct the WHERE clause.

Also when selecting a Jobplan with at least one revision on a Workorder not all the correct information of the jobplan was not copied to the workorder.

Solution:
We use two crossover domains to get jobplan information on pm and on workorders. These crossover domains had the following where clause:

jpnum=:jpnum

These needed to modified to take in account the active jobplan:

jpnum=:jpnum and status in (select value from synonymdomain where domainid='JOBPLANSTATUS' and maxvalue ='ACTIVE')


Issue:
Error message BMXAA4042 was reset to default.

Solution:
Copy pasted the Error message from a backup Maximo 7.1.1.7 installation.
You can find these message in  Database Configurion -> Select Action -> Messages



Issue:
Maximo is showing up all messed up. Icons are placed below each other and it is impossible to work with.

Solution:
We used the same DNS name for Maximo, so the screen needed a hard refresh in order to replace the cached pictures and stylesheets. 
First go back to the startcenter then press CTRL + F5 on the keyboard.



Issue:
Problem with the startcenters. Applications that are related are now also shown on the startcenter, even if the user does not have access to them. This was not the case in Maximo 7.1

Solution:
We need to build different startcenters for the some groups. In 7.1 we just used the same startcenter with a lot of applications on them. Application to which users where not authorized where not shown.


Issue:
There was a problem with the saved queries. The schema name was registered in some queries

Solution:
Fixed with query below.
Please check with you own environment, and test it first as always!

--checks
select * from query
where lower(clause) like '%maxprod%';

select clause, replace(clause, 'maxprod', 'maxprod75')
from query
where lower(clause) like '%maxprod%';

--the actual update
update query set clause = replace(clause, 'maxprod', 'maxprod75')
where lower(clause) like '%maxprod%';

--checks
select clause
from query
where lower(clause) like '%maxprod%';

select *
from query
where lower(clause) like '%maxprod75%';

Issue:
Now the queries works but the user still the following error on some queries if they want to edit them:

BMXAA7024E - The query contains an invalid condition. Modify the query and try again. (maxadmin also gets this same error)

Solution:

IBM introduced an SQL Injection system property (mxe.db.sqlinjection) in later 7.1 versions and 7.5 versions. Guess it wasn't there yet in 7.1.1.7 :)

http://www-01.ibm.com/support/docview.wss?uid=swg21657644


GNZ

Automatically Refresh the Start Center for each user

Goal:
Bulk refresh a Start Center for all the users in Maximo 7.5.


Problem:
I updated the Start Center a lot of users use. Problem is that every user has to click on the ' Update Start Center' link in order to see the updates.

Solution:
Run a script that forces an update of the Start Center after a user logs in.
This can be done by deleting the layout associated with the user's Start Center and then deleting the Start Center config.
This solution requires that you can login on the Database with the maximo user.


First off, the start situation, a Start Center with three applications for the user GZOTEST;

Next I login as MAXADMIN to modify the Start Center template so that it contains a Bulletin Board;

I login as GZOTEST and the startcenter is not updated;

I need to click on 'Update Start Center' in the top right corner to see the Bulletin Board;

The changes are shown to the user. This is the manual method, and each user has to click on the 'Update Start Center' link in order to see our changes.


Now in order to show the changes automatically to the user.

First I wil change the Start Center again as MAXADMIN.

I added 'Inbox/Assignments' to the left column.

When I login as GZOTEST the changes are not shown:

Log out again and open a DB tool (I use SQL Developer)
Run the query: 

select * from scconfig;

For each user a record exists in this table. We can see the SCTEMPLATEID the user has assigned.

now run: 

select * from layout;

This shows all the elements assigned to the different users. When the user clicks on the 'Update Start Center' link this table is checked and updated. 

Next we want to delete all the records in the layout table and scconfig table matching the Start Center we want to update. In our example this is '2'
Run the two queries below to delete these records.

delete from layout
where scconfigid in
(
 select scconfigid
 from scconfig
 where sctemplateid = '2'
);

delete from scconfig
where sctemplateid = '2';


Do not forget to commit the changes to the database if you use an Oracle DB, like in my example.

Next I login to Maximo as GZOTEST again.

et voila, the Start Center shows the lastest addition, the 'Inbox / Assignments'


GNZ.

IE browser shows blank screen after loggin in to Maximo

Goal:
Enable the use of Maximo 7.5 in the Internet Explorer browser on a Windows Server when logged in on the Server through a Remote Desktop Session.

Problem:
The browser shows a blank screen after loggin in to Maximo on the server. 

Consultants often log in on application servers to (remotely) do Maximo stuff. They often want to test their modifications but Maximo is not always available publicly. So they need to test locally on the server they are logged in to. This is not possible when the Internet Explorer browser shows a blank screen after loggin in. Often they are are not permitted to change security settings on the server and installing a different browser is not something you want to do on a server.

Solution:
The latest versions of Internet Explorer browser (IE10 and up) on modern servers (Windows 2008 and up) are very thigtly secured when viewing pages from address outside the local server environment. You constantly have to add mark pages as secured to be able to view them. This is the reason Maximo often shows a blank page after loggin in. 


Option 1 - Adding the domain to the trusted sites in IE:

When navigating to Maximo often the servername is used:

After entering the credentials and loggin in the following message is displayed:

Click [Add] to add the page to the trusted sites. 

This way the security policies applied are different and Maximo will display correctly.

If for some reason the dialog above is not shown you can go to the Internet Options of the browser to add the site:

Click on the 'Security' tab and then click the [Sites] button

Type in the first part of the URL and click [Add]

Option 2 - Use localhost

It is also possible you do not have the option to add addresses to the trusted sites. Maybe because a policy is in place that prevents users from changing the Internet Options.

There is a solution for that. Change the servername in the address to localhost.

So the url would go from:

http://win2k12-01/maximo/

to

http://localhost/maximo/

This way the address is recognised by the browser as a local address and different security policies apply.

GNZ.