Thursday, July 31, 2014

There is a problem with this website's security certificate

Goal:
To be able to navigate to websites with certificates of 512 bits or lower in Internet Explorer 10


Problem:
When navigating to, for example, the Oracle Database Manager or the Websphere Integrated Solutions Console the following page will show:






There is a problem with this website's security certificate. I usually click on 'Continue to this website (not recommended).' and I can go to the website.

But depending on the host and browser version/brand you can also encounter the following (Sometimes after clicking on the 'Continue' link)


In the first example you can ignore the warning and continue to the site, in the second example you cannot even enter the site.

My configuration was Windows Server 2012 with Internet Explorer 10. I couldn't go to the Required website anymore.


Solution:

I started searching and found the following site:

http://social.technet.microsoft.com/Forums/ie/en-US/5fdf4b07-30e6-4a0b-bb08-9adc45d42d54/ie10-there-is-a-problem-with-this-websites-security-certificate?forum=ieitprocurrentver

On this site it is pointed out that there is a policy in place that required a website's certificate to have a key with a minimun bit length of 1024 bits. So when you navigate to a site with a certicate not meeting this requirement the message will come up.

The fix I used is also on that site.

First run a dosbox as Administrator.


Then enter:
certutil -setreg chain\minRSAPubKeyBitLength 512



Now I get the option again to 'Continue to the website' :)



More information from Microsoft on this:
http://support.microsoft.com/kb/2661254

GNZ

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

Thursday, July 24, 2014

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.