How to create test user in test database from production database with impdp

Some time ago i had a need to create identical user on a test database every hour. To solve that i used impdp ability to use transfer over database link.
I have two databases . Production "db_prod" and test database "db_test". User that i am transferring has to preserve its name for easier testing.

1. Create public database link on db_test as SYSTEM:
 create a public database link DB_PROD connect to system identified by xxxxx using ‘DB_PROD_ALIAS’;

2. Insert data (create users and all) from db_prod to db_test with impdp:
impdp system/xxxxxx@db_test schemas=user_prod network_link=db_prod_alias

This way there is no need to use remap_schema since both user names are the same. There is no need to use data_pump_dir because all transfer is done by database link.
There is some more job to be done. I ran script by hand at first to see what objects are giving me warning (Roles, references to another user) and to create a script to prepare test database for fresh injection of a test user. That means usually to drop existing user from db_test, create roles if needed (impdp will fill them with appropriate grants).

Install Oracle Developer 6i on Windows 7 x64

I have a need to install Oracle Forms and Reports Developer suite 6i  with patch 14 on much newer WIndows 7 Professional 64-bit. At first attempt i tried and succeeded to install it all in 64 bit environment using Compatibility options but when i started to work with the tools i encountered many problems.
Next step was to install it in Windows Virtual PC )XP Mode). Steps to do so are:

1. Install Windows XP mode and Virtual PC as in instructions: http://windows.microsoft.com/en-us/windows7/install-and-use-windows-xp-mode-in-windows-7
2. Download and install XP mode : http://www.microsoft.com/en-us/download/details.aspx?id=8002
3. Download and install VirtualPC : http://www.microsoft.com/en-us/download/details.aspx?id=3702
4. Create default XP machine and edit settings (i usually get RAM to 1GB):

After that i started XP mode, set up initial password for XPMode (this is required. Windows XP mode remembers this password, but in later actions it can ask for it. Sometimes when that password is blank, XPmode throws an error. On this site there is a manual how to reset: http://www.mydigitallife.info/resetand-fix-incorrect-or-wrong-password-for-windows-xp-mode-xpmuser/) and install Developer 6i. After that i put valid tnsnames.ora and sqlpnet.ora at %ORACLE_HOME%\network\admin directory.

Now Developer tools could be run directly in XPmode machine (some people find it easier) or from Windows 7 Start menu:

FRM-92095 and Oracle Forms 10G Builder

Recently i was doing a test to move files from Application server to File server and than to get them using webutil. But in meantime i installed Windows7 x64 and reinstall forms 10g builder on my new PC.
When i try to run form locally with OC4j i got FRM-92095 although i had installed 32 bit versions of multiple 1.3.x.x. Jinitiators.
The problem was that only JRE and JDK installed on PC was JDK7u21 (x64) and when i try to run form i got following:

After i got that error i installed JRE 1.6.u45 from :

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jre-6u45-oth-JPR

The next step was either:

• Set up JRE 1.6u45 as default Java either in System and User enviroment through Control Panel
• or uninstall 64-bit Java version

Setting new Font in BI Publisher 10.1.3.4.1

From time to time there is a need to set up a new font for RTF,HTML or PDF reports in BI Publisher. Fonts could be inserted system wide or per a report.

To insert font for only one report to get it in all those types of reports:

--> Copy font (*.ttf file) to the directory : C:\ORACLE\BIPUBLISHER\jdk\lib\fonts where c:\oracle\bipublisher is where BI Publisher is installed

-->  edit xdo.cfg file of report that is found in c:\ORACLE\BIPUBLISHER\xmlp\XMLP\Reports\"Report Folder"\"Report Name":

<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
    <!-- Properties -->
    <properties>
        <!-- System level properties -->
        <!-- PLEASE SELECT A VALID TEMPFILE DIRECTORY!!! -->
        <property name="system-temp-dir">c:/Temp</property>
        <property name="rtf-circlefull-glyph">Wingdings 2;002;154</property>
        <property name="rtf-circleempty-glyph">Wingdings 2;002;152</property>
        <property name="rtf-checkbox-glyph">Wingdings 2;152;153</property>
 <!-- Font setting -->
    <fonts>
      <!-- Windows Wingdings font -->
      <font family="WingDings 2" style="normal" weight="normal">
       <truetype path="wingdng2.ttf"/> 
      </font
    </fonts>

</config>

Note: in this case WingDings2 font is used. Best case is to use lowecase in scripts and file name on OS side.

--> Edit or check configuration of report. If there is no value put this:

How to reset Apex internal password with a script.

This is the script i use to reset Apex password in Apex version 4.x.

• Login to db as sysdba
• Check what verison is APEX user (mine case is 4.2 or APEX_040200)
• Execute Script

Script:

set define '&'

set verify off

alter session set current_schema = APEX_040200;

prompt ...changing password for ADMIN

begin

  wwv_flow_security.g_security_group_id := 10;

  wwv_flow_security.g_user := 'ADMIN';

  wwv_flow_security.g_import_in_progress := true;

  for c1 in (select user_id from wwv_flow_fnd_user

      where security_group_id = wwv_flow_security.g_security_group_id

      and user_name = wwv_flow_security.g_user) loop

     wwv_flow_fnd_user_api.edit_fnd_user(

       p_user_id => c1.user_id,

       p_user_name => wwv_flow_security.g_user,

       p_web_password => '&1',

       p_new_password => '&1');

  end loop;

  wwv_flow_security.g_import_in_progress := false;

end;

/

commit;

/

Working with Oracle Forms Developer 10G on Windows 7 Pro x64

First problem is to install Forms 10G on Windows 7 (either 32 bit or 64 bit). Steps to achieve this are :

• Change Windows 7 virtual memory from automatic to manually since OUI do not recognize automatic virtual memory ( set it from 6000M to 12000M on 4GB Ram machine)
• In setup.exe change compatibility mode to "Windows XP SP2 (or 3)" and set "Run As Administrator"
• Ignore PSAPI.DLL errors for now. I did not come to any problems for now ignoring this errors.

Now you can run Forms, load new form and so on. But the problem is when you start OC4J Instace to run form locally (with default Forms Jinitiator) in IE8 or IE9 nothing happens. So i changed runtime to run froms with default JRE installed on system. To do so i changed in formsweb.cfg:

• baseHTML=base.htm --> baseHTML=basejpi.htm
• baseHTMLjinitiator=basejini.htm --> baseHTMLjinitiator=basejpi.htm
• jpi_mimetype=application/x-java-applet;jpi-version=1.4.2_06 --> jpi_mimetype=application/x-java-applet

With mimetype i ommit the version number so there is no need to instal JRE 1.4.2_06. Instead forms runtime process is using any JRE installed on system (in my case it is 1.7.0_21)

I read that on Firefox 12 version number is a must but did not tested that.

How to Convert Oracle Reports to UTF-8

One of the problems i faced recently is how to convert CP1250 (EE8MSWIN1250) codepage to UTF-8 codepage in Oracle Reports. Problem is  that Forms and Reports server is installed with NLS_LANG set to EE8MSWIN1250 with 5 applications consist of hundreds forms and reports. One smaller app need to execute reports with RUN_REPORT_OBJECT but in UTF-8 codepage.

One solution was to install separate Report Server with NLS_LANG set to UTF-8 but that requires more resources and licences.
My solution is to use GNU software called ICONV (http://www.gnu.org/software/libiconv/) which converts files to another code page.
First task is to create report (.XML) on report server with RUN_REPORT_OBJECT to some folder.
Than with host comand execute next batch script:

set PATH=D:\oracle\FRHome\jdk\bin;C:\Iconv\bin -- i use to copy all windows PATH to this variable

iconv -f CP1250 -t UTF-8 %1 > %2 -- %1 is source where CP1250 report is

                                                           -- %2 is where we put UTF8 report to

exit

After that i call Web.Show_document to show UTF-8 file in IE9.

Oracle IAS (FORMS) memory mapped file I/O

When running forms on application server as a production everyone is setting FORMS_MMAP=TRUE or leave it unassignet to relieve memory demands on a server. The rule is to put new or updated forms to server at designeted time (I am doing that at midnight when restarting Forms services). But what happened when programmer need to update some forms immediately during peek time.
Suppose that we have multiple forms (100+) from different enviroments. The problem is how to copy requested forms on form server.
One idea is to put FORMS_MMAP to FALSE but that could be too heavy on server memory demands for Forms services. Another idea is to stop OC4J_BI_FORMS through enterprise management but than we hace to disconnect all users, not only that use particular form.
I made a solution in form of a VBS script which create batch file (Windows) to kill only those processes that uses enviroment of a form i need to replace:


'First input name of Forms server (could use InputBox if more than one server)  
strComputer = "server_name"

'Define arguments
Dim arg
Dim pass
Dim argpass

'Define passwords
pass="******"

'Get user to write a password 
argpass=InputBox("Unesite Password:")

'Define output file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("output.bat", True)

'Set WmiService
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 

'Get frmwebprocesses
Set colItems = objWMIService.ExecQuery("Select * from Win32_Process where name='frmweb.exe'")

If pass = argpass Then  

'Get the name of env file
arg = InputBox("What application you need to stop?")
arg=LCase(arg)

'Fill the output.bat file
For Each objItem in colItems
        proc=objItem.ProcessID
     'Get command line 
                     '(actually you get webfile=HTTP-0,0,1,name_of_app_from_env,ip_address)
                     line = objItem.CommandLine
    linesplit=Split(line,",",5) 'Extract the name of aplication from command line
    app=linesplit(3)
    app=LCase(app)
    If arg=app Then     
    objFile.WriteLine "pskill.exe " & proc 'Write the output.bat file
    End if
Next
End if

Output of a VBS script is a batch file output.bat which consists:
pskill.exe 1345
pskill.exe 1328
pskill.exe 1479
pskill.exe 1511

Pskill can be downloaded from: http://technet.microsoft.com/en-us/sysinternals/bb896683.aspx

The remote session was disconnected because there are no Terminal Server client licenses available for this computer" in Windows XP

Windows XP and Windows  7 users sometimes encounter the error:

"The remote session was disconnected because there are no Terminal Server client licenses available for this computer" in Windows XP"

To work around this problem, follow these steps.

Click Start –> Run

Type regedit and press Enter

Browse to the following registry key:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSLicensing

close registry editor.

Installation OC4J standalone

To configure Apex FOP printing first step is installation of OC4J Standalone (or using BIP Publisher).
To install OC4J Standalone you need:

• Oracle OC4J Standalone Version 10.1.3.5.0  (http://www.oracle.com/technetwork/middleware/ias/downloads/utilsoft-090603.html)
• Java SDK 6u38 (http://www.oracle.com/technetwork/java/javase/downloads/jdk6u38-downloads-1877406.html)

Define two system variables

• ORACLE_HOME = C:\ORACLE\OC4J
• JAVA_HOME=C:\Program Files\Java\jdk1.6.0_38

Unpack OC4J standalone zip to directory c:\oracle\oc4j:

To start OC4J manually go to %ORACLE_HOME%\bin\oc4j -start

When you start first time it will ask you for OC4J password

To stop OC4J manually go to %ORACLE_HOME%\bin\oc4j -shutdown -port "xxxxx" -password "pass"

where:

         "pass" is OC4J password

         "port" is 23791 and is found in: C:\ORACLE\OC4J\j2ee\home\config\rmi.xml (port="23791")

To start OC4j automatically define variable J2EE_HOME=C:\ORACLE\OC4J\j2ee\home

         cd %J2EE_HOME%

         java -jar oc4j.jar

         or with custom configuration script: java -jar oc4j.jar -config /mypath/server.xml

When you are done you should get response on web page:

http://%server_name%:8888 (or any other port you configured)

and OC4J Enterprise Manager at:

http://%server_name%:8888/em

Oracle Forms and Reports Services 10.1.2.0.2 - FRM-92101 - addendum

After some more investigation i found Oracle note about this problem.

"FRM-92101, FRM-92050, FRM-93552 and FRM-93000 Error When Running Forms After a Relatively Small Number (50 - 80) of Concurrent Connections and No New Connection Possible [ID 187455.1]"

Oracle indicates that this solution with ticking "Allow to interact with Desktop" is just for a testing purposes and Microsoft and Oracle did not recommend it as a permanent solution.

Permanent solution will be to edit Windows registry key:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems

Double click on the Windows node value in the right frame.  Increase the  appropriate SharedSection parameter which will be the third one.

Example Setting ->  SharedSection= 1024,3072,512
Solution on my server is SharedSection= 1024,3072,2048

I have problems when third value is greater than second one. Also there is a Microsoft note which says that sum of second and third value could not be more than 48MB on windows 2003 server.