Oracle| Altering table structures through PL/SQL block

February 21, 2018

Hi guys

Prior moving to Oracle EBS (R12) we were using a mini ERP system, that was as robust as possible, however lacked many features those were required for modern business environments. While the company that distributed the software in the market over a decade finally dissolved, we were also provided a full copy of the source codes & the same was in my custody for whole these years.

Recently I wanted to revive it, after WebLogic 12c & seeing the possibility of “JAVA WEBSTART” in small & middle scale industries.

One of the major hurdles I came across were with the columns those saved document numbers. This software which was designed during late 90s only had maximum 6 digits document numbers across modules, totaling 496 tables!

It was not a very feasible idea to go through each table & to modify them using GUI tools like Oracle SQL developer. Instead I planned for a PL/SQL procedure.

& here comes the PL/SQL block


SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

V_EXECUTE_MODIFY BOOLEAN := FALSE; --'TRUE' FOR TABLE MODIFY

BEGIN
FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
--T_YR_MTH
IF (V_EXECUTE_MODIFY) THEN
IF (C.COLUMN_NAME 'T_YR_MTH') THEN
/*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
END IF;

END IF;
END IF;

END LOOP;

END LOOP;
END;

So what does this block do?


CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

This block fetches all table names from the DBA_TABLES for the owner that is passed in as a parameter, in this specific case ‘OPMAN’


CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

The above cursor takes in two parameters, OWNER name and the table name, that comes from the 1st cursor


FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
    FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
    IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

        DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
        --T_YR_MTH
        IF (V_EXECUTE_MODIFY) THEN
        IF (C.COLUMN_NAME 'T_YR_MTH') THEN
        /*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
        END IF;

        END IF;
        END IF;

        END LOOP;

END LOOP;

Let us analyze this part of the block now.

The first cursor passes the table name to 2nd cursor and the loop goes through all columns matching data type, precision and scale. I’ve checked for “Number”, precision 6 & scale 0 which are common for all columns those were defined for document numbers. However there were few instances where YearMonth (YYYYMM) values captured for month end processes, which shouldn’t be checked. As our mini ERP is a standard business application, it was designed following same nomenclature across tables while columns defined. Thus it become easier for me to isolate the columns whose were matching same data type, precision and scale & to limit the alter commands modifying only those columns which were defined for document numbers.

The above example could be further developed to easily alter tables while the entire application is modernized and to easily modify columns with bigger sizes.

Interesting? Why don’t you give it a try and post your comments?

regards,

rajesh

Advertisements

Oracle Application R12(12.0.x) | JRE | UNKNOWN Publisher

January 19, 2018

Hi guys

This time I am going to post a solution towards one of the nagging annoyances with Oracle application R12 (12.0.6 in our specific case) & JRE securities those were implemented by JRE 7 (1.7.x)

Our instance was upgraded to use JRE 6 almost 5 years back during 12.0.4 to 12.0.6 upgrade and ever since there were dozens of JRE updates & the current JRE 1.8.0_161 (AS on 19th January 2018) & with each update, whatever the runtime parameters we add specifically for Oracle Application used to get removed, forcing us to renter it for each user.

I’ve been searching for a proper solution, not only towards the runtime parameter fix, for the security prompts those started annoying our end users whenever they tried to launch the forms, once after we forcefully updated many users who were sticking to 1.6 JRE

So after referring many documents, using some of the past experiences dealing with security certificates, I believe, have figured out how to get rid of those annoyances, minimum for a while.

Solution 1

Runtime parameters being removed after each JRE update

jre

Add a new environment JAVA_TOOL_OPTIONS with a value -Djava.vendor=”Sun Microsystems Inc.”

You may add it under your specific user or system wide, which I usually prefer so that I don’t have to worry about the next user who will be using the same system (for some reasons)

ENV

Solution 2

Addressing JAVA security prompts

Trying to open the forms with latest JRE without adding exceptions will produce the following JAVA security error

1.png

Now we need to add the application URL with complete port details to the security tab of JRE

2

Now, we will be presented with the “toughest” situation to resolve, which will be explained later.

3

Clicking the More Information, JAVA presents more details about the digital certificate issues, stating the publisher of the digital certificate as UNKNOWN, hence this warning appears for the end user each time whenever the user tries to open a Forms based module.

4

You may require Domain Administrator/Administrator privileges to continue the below fixes

1st we have to retrieve the certificate from Oracle application server. Login as application manager user to LINUX environment (We’re setting up the entire stuffs for R12 application that is running on 64Bit Linux)

download $APPL_TOP/admin/appltop.cer file to local computer (If your implementation team has cloned the instances across, the appltop.cer will remain the same across different servers, so the single certificate integration with the user computer should deal with all different instances of the Oracle application (Test, Development & Production)

Once the certificate is downloaded or copied to the local computer, you need to install it. We’ll install the certificate twice, in two different locations.

Follow the images below for more details

5

We will install the certificate for the “Local Machine”, so that all users using the same computer will get benefitted

6

For the 1st instance, We MUST install this certificate to “Trusted Root Certification Authorities” Container.

For 78

 

9

Once the certificate is imported, We should install the certificate once again, Select “Local System” and follow the prompts. We don’t have to specify the container this time.

10

Now, trying to open a forms based module will present you another warning

11

Checking “Do not show this again for apps from the publisher and location above” will suppress this dialog until the certificate remains the same for the application server.

Hope this helps few out there!

regards,

rajesh

 

 


Oracle Application R12 | HTML Page load issues with Internet Explorer 11 | Windows 10

January 7, 2018

Hi guys

Our Oracle application R12 (12.0.6) instance has JDK 6 environment & We don’t have any plans to migrate to any recent versions of Oracle Application those are supported by later Java environments.

Almost all users those use Oracle applications are using JRE for many other engineering programs (Calculations programs by Vendors) & frequent updates of the JRE happens automatically at the user ends. We have a significant crowd that is still using JRE 1.6_20 as base JRE, who seldom have the following issue(s) discussed below.

Over Windows 10, Internet Explorer 11 fails to load HTML pages properly & many times after the logon to the application, the HTML menu page is loaded in the explorer, leaving a 4-5 white space lines. Refer the image below

halfpage

Basically this issue is observed with boxes those have latest JRE (x86) installed and used with parameter -Djava.vendor=”Sun Microsystems Inc.”

image

Oracle blogs strictly says, Compatibility view for Oracle applications R12 shouldn’t be enabled for Internet Explorer 11. I have tried enabling the Compatibility view for the Oracle applications, without any positive changes.

As a last resort, I reset the Internet Explorer 11

image

and after restarting the computer, Oracle Application HTML pages started loading correctly, without leaving white spaces between the IE Window and application top frame.

Hope this helps few folks out there!

rajesh


Oracle EBS R12(12.0.x) | Disable concurrent programs executing immediately after a clone

November 28, 2017

Hi guys

One of the annoyances suffered by business is the alerts and concurrent programs those start sending highly confusing messages to users immediately after a cloning completion. Application tier cloning finishes by trying to start all related services after a successful cloning. Hence the best method to stop the concurrent manager/alerts from firing up is NOT to let the cloning process start application tier level services, so that you can disable alerts/concurrent programs those are NOT required by the cloned instance.

First step is to alter the $COMMON_TOP/clone/bin/adcfgclone.pl (perl script, please refer the image)

image

Change the portion in the highlighted block as shown below. This will stop the clone script from kick starting the application services after a successful clone.

elsif (!($PLATFORM eq "win32")) {
print "\n Not Starting application Services for $s_dbSid:\n";
#bugfix:5838814 subgovin
#runPipedCmd("$s_config_home/admin/scripts/adstrtal.$scrExt -nopromptmsg", "$s_apps_user", "$gConfig{'appspass'}");
}

Now, you may proceed to disable all or selected alerts/concurrent programs using backend before starting the Application services.

Please note, this workaround is ONLY tested on 12.0.x releases of Oracle Applications. Please adjust to your environments.

References:

http://onlineappsdba.blogspot.com/2008/06/prevent-concurrent-requests-executing.html

http://oracle-latest-technology.blogspot.com/2013/11/how-to-disable-all-scheduled-concurrent.html

regards,

rajesh


Oracle Backup to Google Drive?

June 6, 2017

Hi guys

This is a follow up post to my previous post about using a simple batch script for creating a dump export file on regular basis for Oracle database.

Backup. The most essential, however many times highly ignored element of the digital world even today as many small scale industries find the investments made to this particular mechanism hardly comes in effect, unless a disaster arises. My personal experiences with convincing the management to go for sophisticated backup solutions were always the toughest, until we had a HUGE disaster.

As a thumb rule, the first thing I always did for an Oracle database was to setup a dump export every night (if the database is truly small in size), after the normal working hours, in addition to RMAN backups. These export files are kept in a different partition & regularly monitored and purged by the beginning of a new month, keeping the last day backup for the previous month, which is deleted by the beginning of a new year.

Keeping the backup in the same hardware could prove fatal when the hardware fails, and almost all the servers are configured to use RAID, using different levels. In such scenarios even if the drives are intact, retrieving the data from raided volumes will be a professional job, costing investment and time.

For small databases, like the one I have mentioned with my previous post we can design multiple options like mapping a network folder & copying the files automatically once after a new dump file is created as the part of a backup script.

I have devised two methods for my party, and they were

  1. FTP the compressed latest dump file to another machine hosting FTP server
  2. Using google drive (Free 15GB), upload the latest compressed dump file

The first method was already explained here so I will go to 2nd method in which Google drive sync is used to insure that the party has a valid backup stored somewhere in the cloud

  • Database dump export size: 300MB approximately
  • Zipped dump file size: 50MB approximately

Install google drive on your Windows 2008 x, Windows 2012 server machine. You may need to install corresponding Visual C++ Redistributable packages in order to come across python related errors. Please read more here for solutions.

Once the google drive starts working fine, you can use the following script, which will create a dump file first, then create a zip file against the latest dump file created and then copy the zip file to google drive for cloud synching.

Please note, I have moved the google drive folder from the default location to somewhere else, like E:\Google_Drive to make sure that my batch file has shortest path entry for the copying. If you plan the same, you can change the default location for google drive by exiting the application first, then pointing google drive to your folder of choice when google drive complains about missing default location

Windows batch file for Creating, zipping & copying the files to Google Drive

@echo off
FOR /F "tokens=2-4 delims=/ " %%a IN ('date/t') DO exp system/password@connectionstring full = y file=d:\Orabackup\exp_%%b%%a%%c.dmp 

SETLOCAL
::Get the latest dump file name, generated using exp command
::Switch to the folder where the dump (.dmp) files are stored
CD D:\Orabackup\
:: D:\Orabackup is the folder where everyday dump files are stored.
for /f "tokens=*" %%a in ('dir *.dmp /o:-d /b') do set NEWEST=%%a&& goto :next
:next
REM echo The most recently created file is %NEWEST%
::http://stackoverflow.com/questions/15567809/batch-extract-path-and-filename-from-a-variable
FOR %%i IN ("%NEWEST%") DO (
REM ECHO filedrive=%%~di
REM ECHO filepath=%%~pi
SET ZIPNAME=%%~ni
REM ECHO fileextension=%%~xi
)

SET ZIPNAME=%ZIPNAME%.zip
::You can use built-in zip or 7-Zip to create archives
zip %ZIPNAME% %NEWEST%
::E:\Google_Drive is the folder used by the google drive in my setup
COPY %ZIPNAME% E:\Google_Drive

del %ZIPNAME%

::Exit

While this method looks pretty awesome for small size databases, please be noted that, may not be at all feasible for larger ones. I will OPT this method for a backup dump file that could be compressed to a size of 400-500MB maximum, including the possibilities of corrupt compressed files.

Whatever, as far the party has a reliable internet connection with decent bandwidth, based on the size of compressed file, will always have access to a recent backup dump file, stored free in the cloud!

Does it look decent? ;)

Tip: Running Google drive sync as Windows Service

regards,

rajesh


Windows | ORA-12560: TNS:protocol adapter error

February 25, 2017

Hi guys

Not many DBAs prefer Windows for their Oracle databases. Linux is most preferred by most of them & most of the DBAs I know setup the bash profile under Oracle user to setup the environment during each logon to the server.

Our legacy business application database runs on Windows 2003 & trust me, we never had a single database crash (Other than the physical hardware failure that forced us to recover the database once). Depending upon how huge the database and application, the choices for hosting the Oracle database differ from one business to other.

We decided to upgrade our Oracle 10g 10.1.x.x 32Bit database to 11g R2 & as usual I have replicated the environment using my home semi-server class desktop, before the Production environment at work.

Installed 10g 32Bit, created the database using dump export file (The total size of the database is less than 7GB, hence I avoided the hectic RMAN backup and restore part)

  1. Configured RMAN against the new database & made full backup for archive logs and database.
  2. Installed 11g 11.2.0.4 64Bit database (Software Only installation)
  3. Created a new Windows Service using oradim
  4. Restored the database from RMAN backups & upgraded the database to 11g

So far so good. I had to restart the computer & after rechecking the database was up and running, tried to access the instance using sqlplus & was presented with

ORA-12560: TNS:protocol adapter error

REG_SID_MISSING

I setup ORACLE_SID=SID at the CMD window & sqlplus was happy after that.

Usually, Windows doesn’t need environment variables set exclusively for the database as Windows registry takes care of it. This is very efficient when the box has only one database running. If you have more than one database or multiple Oracle homes, the scenario changes.

In addition to, Oracle always looks for the executable based on the PATH information it reads. For example my box has 10g,11g,12c database software installed without any databases created during the installation time.

Let us consider the scenario like I didn’t re-order the PATH entries after the latest installation of 12c & try to open SQL or RMAN. The call will find the executable from 12c path entry BIN as default, and a beginner could have enough confusions due to it.

In my case, I needed my 10g instance first, hence I moved the 10g folder as the 1st entry for Oracle products, and once I finished with 10g moved 11g home folder to the 1st position.

SID_Missing

Anyway, after confirming the path settings, my immediate attention was towards registry, as Oracle services completely depend upon the registry values for each service registered.

To my utter surprise, found the 11g Service entry didn’t have ORACLE_SID string created during the instance creation using ORADIM.exe

REG_SID_MISSING

Oracle 11g 11.2.0.4 has a huge bug list and interim patches those should be applied before moving to Production instance. I really don’t know whether the missing ORACLE_SID string entry was due to one of such bugs.

So I stopped the Oracle service, added ORACLE_SID string entry with the value for my database

REG_SID_ADD

Restarted the service & sqlplus connected to the instance happily without setting up the environment variable like set ORACLE_SID=SIDNAME

REG_SID_ADDED

While the easiest solution is to setup both ORACLE_HOME, ORACLE_SID when someone wants to use the sqlplus or RMAN exclusively as a part of the database access, the above method is a definite way to deal with “ORA-12560: TNS:protocol adapter error”

regards,

rajesh


ORA-12546: TNS: permission denied

February 13, 2017

Update: 16th Feb 2017

Sorry folks, I’ve given upon 10.2.0.4 & moved to 10.2.0.5 patch upgrade that resolves the intermittent service startup issues of Oracle on Windows OS (Uncertified OS like Windows 10/2008R2)

I’m sure, if at all needed an upgrade, your DBA will choose a later version of Oracle database, when a client server architecture may not be flexible with Oracle 12c. Please make sure you try the upgrade using a virtual machine, spend some time analyzing logs and if you feel, it is worth taking a risk for the production, do the production patching using 10.2.0.5 patch. You may have to drop and recreate the Enterprise Manager repository (if you are using it at all)

Update: 14th Feb 2017

A right day to present something that’s kinda disappointing? :P Well, Even though I managed to get stuffs running, after a reboot over night, realized that the “net start Oracle serviceName” in consequent attempts brings back the dreaded “ORA-12546: TNS: permission denied” error message logged in oradim.log

1

forco

the only one logic I could reach to this intermittent issues with starting service is, read & execute permissions on Oracle executables are not being triggered always. Finish!

My further digging using “Dependency walker”  for both Oracle.exe & TNSLSNR.exe has exposed that both the executable were making reference to Microsoft APIs those are depreciated!

2

Finally: I’m doing a hack install of 10.2.0.3 on Windows 10. Updating the instance using 10.2.0.4 patch & expecting a smooth sail. I’m totally wrong, I can test it, hope it works (not always) & I shouldn’t ever attempt the same in a Production environment.

Positive thing: Once the bin folder has correct permissions & setting up the Oracle services to start automatically, the service starts without any issues and shuts down gracefully when Windows is shut down.

 

Hi guys

I’m in the midst of compiling a huge post about RMAN backup & restore for NOT DBAs & at the same time trying to fix “ORA-12546: TNS: permission denied” errors I was getting from my 10.2.0.3 upgraded to 10.2.0.4 (32Bit) on a Windows 10 virtual machine using Oracle VirtulBox (My RMAN backup was restored from 10.2.0.3 Production to this upgraded instance)

My scenario:

Windows Service for Oracle Service is set as manual. After a reboot, I try to start the service manually. Sometimes it starts, some other time I have start/stop/start/stop a number of times until the database gets started. I asked google to give me tip & found a post answered by Tom (AskTom) bombarded with questions about “ORA-12546: TNS: permission denied” after machine restarts & there were no real solutions. Few of them even claimed that they managed to start the Oracle service by turning off the firewall(?)

Then I asked google another question “fix ORA-12546: TNS: permission denied” & landed on http://www.dba-oracle.com/t_ora_12546_tns_permission_denied.htm & from that page to http://www.dba-oracle.com/security/removing_permissions.htm

Basically the 2nd page is purely for Linux, and if you are familiar with file permissions, easy to translate for Windows

I browsed the bin folder & realized that, even though the SERVER\Administrator account has full access to the oracle.exe executable, somehow my user “rajesh” who is the default administrator was not inheriting the execute right on the same.

So

I gave myself full rights on the executable and tried to restart the service & without making any complaints the Oracle database Service started and the database was opened. I was having a look at both alert log and oradim log files to insure that “ORA-12546: TNS: permission denied” not being appearing once again.

1

Then I gave my user account rajesh full rights on the bin folder (Over reaction) to make sure that I am not going to get hammered with the dreaded, without much explanations available “ORA-12546: TNS: permission denied” error once again.

2

I think during the upgrade process much of the file permissions were changed as part of the patching process & eventually the permissions were not reset to the actuals. This issue could happen with any Oracle upgrade processes. So if you are that another unlucky one, please give the above hack a try.

 

regards,

rajesh