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

Advertisements

Oracle Applications R12 | error while loading shared libraries: libig.so.0…

December 11, 2016

 

applprod@erp-prodbak.my.home:/u01/applprod/PROD/apps/apps_st/appl/au/12.0.0/forms/US>frmcmp_batch module=$AU_TOP/forms/US/XXITMDTLS.fmb userid=apps/apps output_file=$ONT_TOP/forms/US/XXITMDTLS.fmx Module_Type=FORM compile_all=Special
frmcmp_batch: error while loading shared libraries: libig.so.0: cannot open shared object file: No such file or directory
applprod@erp-prodbak.my.home:/u01/applprod/PROD/apps/apps_st/appl/au/12.0.0/forms/US>

Recently I cloned our Production instance to check the feasibility of OEL 6.7 & as usual securely copied most of the bash profile for the Oracle and Application manager users from the production server.

I came across the “cannot open shared object file: No such file or directory” error while trying to compile an old form module & hadn’t have a clue for how to deal with this error, that happened to me first time in last 8 years with Oracle applications.

I crosschecked the folder permissions against production instance and everything was fine. All I could remember which could have gone wrong was narrowed down to the bash profile for the application user and after validating I realized that I was sourcing a wrong environment file!

Instead of sourcing the “/apps/apps_st/appl/APPSPRODBAK_hostname.env” file, I was sourcing “/apps/apps_st/appl/PRODBAK_hostname.env”. I realized it while trying to echo the $ORACLE_HOME variable as application manager user.

So, if you come across “error while loading shared libraries” error while trying to compile, before start dismantling your instance, try to see whether you have sourced the environment variables correctly.

regards,

rajesh


Configure Oracle database for Virdi fingerprint machines

May 9, 2016

Hi guys

Recently we changed our age old SAGEM finger print machines with cheaper and affordable VIRDI devices & bit the bullet when it was about configuring UNIS Remote Manager software to our Oracle ERP database server.

Virdi openly states they prefer MSACCESS database and other databases (SQL and Oracle)are “supported”, you read it, supported by the CUSTOMER

The FAQ available with Virdi website must be the work of a GENIUS, I have never seen such a structured FAQ with any other products & a wonderful close button that is a page big itself!

1

Oracle database setup is done for Virdi UNIS Remote Manager 3.2.3.6

Make sure your UNIS software matches the above version or later.

Default installation source includes a folder called “Database”

image

and within the “Database” folder you will find Oracle, SQL Server folders

image

Inside the Oracle folder you should see another folder “SQL Script”, within which you will find the SQL file for creating Oracle database elements for the UNIS software. Virdi wants a DBA to carefully analyze the script, execute sets one after another…

I’ve split the SQL into two portions which you can download from here. Please execute the scripts like following

(Please note the attached SQL script for creating the user is not updated, if you are going to use the downloaded scripts, please update the script using the one provided below)

As System or a user with DBA

“UNIS_User.sql”

create tablespace unis_db
datafile 'unis_db.dbf'
size 10M
autoextend on;

CREATE USER unisuser IDENTIFIED BY unisamho
 DEFAULT TABLESPACE "UNIS_DB"
 QUOTA UNLIMITED ON "UNIS_DB";

GRANT "CONNECT" TO unisuser;
GRANT "DBA" TO unisuser ;
GRANT "RESOURCE" TO unisuser ;
ALTER USER unisuser DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";

Please remove DBA privileges once after the initial data migration etc are over! (Useful when you are migrating from SQL to Oracle to carry out different DBA activities ;) )

Once the unisuser account created. Run the

“UNIS_Tables.sql” as unisuser

CREATE TABLE iUserFinger(
    L_UID int NOT NULL,
    L_IsWideChar int NULL,
    B_TextFIR blob NULL
);

ALTER TABLE iUserFinger ADD PRIMARY KEY ( L_UID    ) ;

CREATE TABLE tClientLog(
    C_EventTime char(14) NULL,
    L_LogonID int NULL,
    L_Type int NULL,
    L_Result int NULL
);

CREATE TABLE tTerminalLog(
    C_EventTime char(14) NULL,
    L_TID int NULL,
    L_Type int NULL
) ;
/*
CREATE TABLE tEnter(
    C_Date char(8) NOT NULL,
    C_Time char(6) NOT NULL,
    L_TID int NOT NULL,
    L_UID int NOT NULL,
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Card varchar(24) NULL,
    L_UserType int NULL,
    L_Mode int NULL,
    L_MatchingType int NULL,
    L_Result int NULL,
    L_IsPicture int NULL
);
*/
--Altered tEnter table script
--Rajesh
CREATE TABLE UNISUSER.TENTER
(
  C_Date          CHAR(8 BYTE)                NOT NULL,
  C_Time          CHAR(6 BYTE)                NOT NULL,
  L_TID             INTEGER                     NOT NULL,
  L_UID             INTEGER                     NOT NULL,
  C_Name          VARCHAR2(30 BYTE),
 C_Unique        VARCHAR2(20 BYTE),
  C_Office        VARCHAR2(30 BYTE),
  C_Post          VARCHAR2(30 BYTE),
  C_Card          VARCHAR2(24 BYTE),
  L_UserType      INTEGER,
  L_Mode          INTEGER,
  L_MatchingType  INTEGER,
  L_Result        INTEGER,
  L_IsPicture     INTEGER,
  L_Device        INTEGER,
  L_OverCount     INTEGER,
  C_Property      VARCHAR2(8 BYTE),
  L_JobCode       INTEGER,
  L_Etc           INTEGER,
  L_Trans         INTEGER,
  L_NvrChannel1   INTEGER,
  L_NvrChannel2   INTEGER,
  L_NvrChannel3   INTEGER,
  L_NvrChannel4   INTEGER
);

ALTER TABLE tEnter ADD PRIMARY KEY
    (
        C_Date,
        C_Time,
        L_TID,
        L_UID
    )  ;

CREATE TABLE tAuditTerminal(
    C_EventTime char(14) NOT NULL,
    L_TID int NOT NULL,
    L_AdminID int NOT NULL,
    C_AdminName varchar(30) NULL,
    L_Type int NOT NULL,
    L_UserID int NOT NULL,
    C_UserName varchar(30) NULL
) ;

ALTER TABLE tAuditTerminal ADD PRIMARY KEY
    (
        C_EventTime,
        L_TID,
        L_AdminID,
        L_Type,
        L_UserID
    )  ;

CREATE TABLE tAuditServer(
    C_EventTime char(14) NOT NULL,
    L_LogonID int NOT NULL,
    L_Section int NULL,
    C_Target varchar(30) NULL,
    L_Process int NULL,
    L_Detail int NULL
) ;

CREATE TABLE cHoliday(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cHoliday ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cOffice(
    c_code varchar(30) NOT NULL,
    c_name varchar(30) NULL
) ;

ALTER TABLE cOffice ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cPost(
    c_code varchar(30) NOT NULL,
    c_name varchar(30) NULL
) ;

ALTER TABLE cPost ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cStaff(
    C_Code varchar(30) NOT NULL,
    C_Name varchar(30) NULL
) ;

ALTER TABLE cStaff ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iCantTerminal(
    L_UID int NULL,
    L_TID int NULL
) ;

CREATE TABLE iHoliday(
    C_Code char(4) NOT NULL,
    C_Holiday char(4) NULL,
    C_DayName varchar(30) NULL
) ;

CREATE TABLE iTerminalAdmin(
    L_TID int NOT NULL,
    L_UID int NOT NULL
) ;

ALTER TABLE iTerminalAdmin ADD PRIMARY KEY
    (
        L_TID,
        L_UID
    )  ;

CREATE TABLE tCmdDown(
    C_RegTime char(14) NOT NULL,
    L_TID int NOT NULL,
    L_UID int NOT NULL,
    C_Time char(14) NULL,
    B_Data blob NULL,
    L_Retry int NULL
) ;

ALTER TABLE tCmdDown ADD PRIMARY KEY
    (
        C_RegTime,
        L_TID,
        L_UID
    )  ;

CREATE TABLE tMealType(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL
) ;

ALTER TABLE tMealType ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE tMoney(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Unit int NULL,
    L_Early int NULL,
    L_Basic int NULL,
    L_Over int NULL,
    L_Night int NULL,
    L_Holi int NULL
) ;

ALTER TABLE tMoney ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE tWorkType(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_BasicDay char(8) NULL,
    L_Spin int NULL,
    C_WorkCode varchar(60) NULL,
    L_InoutMode int NULL,
    L_AutoIn int NULL,
    L_AutoOut int NULL,
    L_LateMargin int NULL,
    L_LackMargin int NULL,
    C_HoliCode char(4) NULL
) ;

ALTER TABLE tWorkType ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iUserPicture(
    L_UID int NOT NULL,
    B_Picture blob NULL
) ;

ALTER TABLE iUserPicture ADD PRIMARY KEY
    (
        L_UID
    )  ;

CREATE TABLE tConfig(
    C_MasterPwd varchar(30) NULL,
    L_UniqueType int NULL,
    L_AutoDown int NULL,
    C_DownTime char(4) NULL,
    L_AutoUp int NULL,
    L_RegSameFp int NULL,
    L_FpNum int NULL,
    L_UidCipher int NULL,
    L_TidCipher int NULL,
    L_UniqueCipher int NULL,
    L_MinVID int NULL,
    L_MaxVID int NULL,
    L_tSockPort int NULL,
    L_PollTime int NULL,
    L_SaveMode int NULL,
        C_PicturePath varchar(255)
) ;

CREATE TABLE iTimezone(
    C_Code char(4) NOT NULL,
    C_Timezone char(8) NOT NULL
) ;

CREATE TABLE cTimezone(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NOT NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cTimezone ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cAccessTime(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Holiday char(4) NULL,
    C_Sun char(4) NULL,
    C_Mon char(4) NULL,
    C_The char(4) NULL,
    C_Wed char(4) NULL,
    C_Thu char(4) NULL,
    C_Fri char(4) NULL,
    C_Sat char(4) NULL,
    C_Hol char(4) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessTime ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iAccessArea(
    C_Code char(4) NOT NULL,
    L_TID int NOT NULL
)
;
ALTER TABLE iAccessArea ADD PRIMARY KEY
    (
        C_Code,
        L_TID
    )  ;

CREATE TABLE cAccessArea(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessArea ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cAccessGroup(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessGroup ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iAccessGroup(
    C_Code char(4) NOT NULL,
    L_Type int NOT NULL,
    C_AccessCode char(4) NOT NULL
) ;

CREATE TABLE tUser(
    L_ID int NOT NULL,
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    L_Type int NULL,
    C_RegDate char(14) NULL,
    L_OptDateLimit int NULL,
    C_DateLimit char(16) NULL,
    L_AccessType int NULL,
    C_Password varchar(8) NULL,
    L_Identify int NULL,
    L_VerifyLevel int NULL,
    C_AccessGroup char(4) NULL,
    C_PassbackStatus char(4) NULL
) ;

ALTER TABLE tUser ADD PRIMARY KEY
    (
        L_ID
    )
;
CREATE TABLE tEmploye(
    L_UID int NOT NULL,
    C_IncludeDate char(8) NULL,
    C_RetiredDate char(8) NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Staff varchar(30) NULL,
    C_Authority char(4) NULL,
    C_Work char(4) NULL,
    C_Money char(4) NULL,
    C_Meal char(4) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tEmploye ADD PRIMARY KEY
    (
        L_UID
    )  ;

CREATE TABLE tVisitor(
    L_UID int NOT NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Target varchar(30) NULL,
    C_Goal varchar(255) NULL,
    C_Company varchar(30) NULL,
    C_Info varchar(255) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tVisitor ADD PRIMARY KEY
    (
        L_UID
    )  ;

CREATE TABLE tVisited(
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    C_RegDate char(14) NULL,
    C_LastDate char(8) NULL,
    C_Company varchar(30) NULL,
    C_Info varchar(255) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

CREATE TABLE tTerminal(
    L_ID int NOT NULL,
    C_Name varchar(30) NULL,
    L_FnWork int NULL,
    L_FnMeal int NULL,
    L_FnSchool int NULL,
    C_Office varchar(30) NULL,
    C_Place varchar(255) NULL,
    C_RegDate char(14) NULL,
    L_CommType int NULL,
    C_IPAddr varchar(255) NULL,
    L_IPPort int NULL,
    L_ComPort int NULL,
    L_Baudrate int NULL,
    L_Passback int NULL,
    C_AreaIn char(4) NULL,
    C_AreaOut char(4) NULL,
    C_lastup char(14) NULL,
    C_Version varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tTerminal ADD PRIMARY KEY
    (
        L_ID
    )  ;

CREATE TABLE iCardInfo(
    L_CardSize int NULL,
    L_CardType int NULL,
    L_ReadType int NULL,
    L_TemplateSize int NULL,
    L_TemplateCount int NULL
) ;

CREATE TABLE iCardLayout(
    L_Index int NULL,
    L_Sector int NULL,
    L_Block int NULL,
    L_Start int NULL,
    L_Length int NULL,
    L_KeyType int NULL,
    C_KeyValue char(12) NULL
)
;
CREATE TABLE iUserCard(
    C_CardNum varchar(24) NOT NULL,
    L_UID int NULL
) ;

ALTER TABLE iUserCard ADD PRIMARY KEY
    (
        C_CardNum
    )
;
CREATE TABLE cAuthority(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_SetLocal int NULL,
    L_RegInfo int NULL,
    L_DataBackup int NULL,
    L_MgrTerminal int NULL,
    L_RegControl int NULL,
    L_SetControl int NULL,
    L_RegEmploye int NULL,
    L_ModEmploye int NULL,
    L_OutEmploye int NULL,
    L_RegVisitor int NULL,
    L_OutVisitor int NULL,
    L_RegMoney int NULL,
    L_RegWork int NULL,
    L_SetWork int NULL,
    L_ModWork int NULL,
    L_RegMeal int NULL,
    L_SetMeal int NULL,
    L_ModMeal int NULL,
    L_DelResult int NULL,
    L_DelWork int NULL,
    L_DelMeal int NULL,
    L_MgrScope int NULL
) ;

ALTER TABLE cAuthority ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cPassback(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_Remark varchar(255) NULL
);

ALTER TABLE cPassback ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE tMapImage(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_FileName varchar(255) NULL,
    L_FileSize int NULL,
    B_FileData blob NULL
)
;
ALTER TABLE tMapImage ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iMapDrawing(
    C_MapCode char(4),
    L_PosX int NULL,
    L_PosY int NULL
);

CREATE TABLE iMapTerminal(
    C_MapCode char(4),
    L_TID int NULL,
    L_Type int NULL,
    L_PosX int NULL,
    L_PosY int NULL
);

Insert into tconfig values ('1',1,0,'0000',1,1,3,4,4,20,7000,9999,9870,10,0,'');
Insert into cAuthority values ('****', 'User', 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
Insert into cAuthority values ('1000', 'Department Admin', 0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0);
Insert into cAuthority values ('2000', 'Terminal Admin', 0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1);
Insert into cAuthority values ('3000', 'Branch Admin', 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
Insert into cAuthority values ('4000', 'All Admin', 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2);
Insert into cOffice values ('****', 'Unassigned');
Insert into cPost values ('****', 'Unassigned');
Insert into cStaff values ('****', 'Unassigned');
Insert into cAccessGroup values ('****', 'Unassigned',0,'');
Insert into cPassback values ('****', 'Unassigned','');
Insert into cTimezone values ('****', 'Unassigned', 1, '');
Insert into cHoliday values ('****', 'Unassigned', '');
Insert into tMoney values ('****', 'Unassigned', 0,0,0,0,0,0);
Insert into tMealType values ('****', 'Unassigned');
Insert into tWorkType values ('****', 'Unassigned', '', 0, '', 0,0,0,0,0,'');

If you use the Virdi provided Oracle SQL script, be sure you will not able to use the log monitoring screen as the tenter table misses many vital columns.

Sequence of Oracle software installation

Scenario #1

  1. 64Bit Windows Server 2008 r2 or later with ONE STATIC IP address
  2. Oracle 10g R2 64Bit (You can try 11g or 12c databases,provided you are pretty sure aware of the password complexity & expiry parts)
  3. Oracle 11g 32Bit client, full administrator installation (UNIS Remote manager is strictly 32Bit software & if you don’t have Oracle client 32bit version installed, won’t able to communicate with the 64bit database). A hacked installation of Oracle 10g 31bit on Windows 10 will list the drivers under 32bit ODBC, however I strongly discourage using the driver (2006) as it is not at all compatible with Windows 10. If your business strictly needs to use Oracle 10g database, please make sure that you have either 11g or 12c 32bit client installed for the System DSN part.

As I clearly mentioned, the scripts I have provided are for UNIS remote manager version 3.2.3.6. In addition to it, I have modified the table creation script for tEnter table, which holds the attendance details as per the SQL table structure so that the log monitor part of UNIS remote manager could work. I don’t guarantee or answerable for any data loss that caused by slightly hacked scripts. If you are truly concerned about data safety and reliability, please contact Virdi for support (as if they care!)

(I have tried the same against a 12c 64bit installation, unfortunately, there are some errors which fail to fetch tables after the database opened. I need reconfirm it is nothing related to the case sensitive password or complex password related mess-ups)

Windows 10 has two entries for odbc with the control panel, while Windows 2008 R2 you have to startup the 32bit odbc from the following place (Assuming Windows is installed in default location)

C:\Windows\SysWOW64\odbcad32.exe

image

image

image

image

Restart the services (Incase if you setup the Oracle part once after the UNIS software was installed)

UNIS Software instalation

Install the software using setup & and no specific settings changes are necessary to make the software communicate with the Oracle database. Said, there are no settings available with the UNIS Remote Manager to setup the database. UNIS software looks for a system DSN with the names UNIS and UNIS_Temp and if connections could be initiated, works against the available database through the DSN(s)

 

image

Try to start UNIS Remote Manager software and if your configurations were correct in the place, should start without giving any errors

image

(Check the Master Logon, that will put “0000” in Admin ID and the default password for Admin account “0000” is “1”)

image

I have populated the Oracle table tenter with data extracted from SQL table in order to make sure that UNIS is communicating with Oracle database properly.

image


Java runtime 1.8.0.91 | Oracle EBS R12 (12.0.6)

May 2, 2016

Hi guys

You could run into a situation after the latest Java client runtime update, your R12 instance failing to load forms, complaining about the lower version of JRE. This is mainly due to the fact that, if your Windows OS is 64Bit, the latest java runtime update installs both 64Bit & 32Bit and most of the R12 environments run against 32Bit JRE.

Internet explorer (the only one browser that runs the JRE properly for EBS R12) loads the 64Bit java runtime by default from a 64Bit Windows OS & eventually the forms will fail to load.

At present the only possible solution to address this situation is to uninstall the 64bit version of java runtime, using “Programs and features” or “Add remove programs” based on the version of Windows you are using.

Hope this helps few out there :)

regards,

rajesh

 

 


It’s been long time!

April 19, 2016

Hello guys!

I know, there were NOT much to post throughout last few months about Oracle as I am nose down into one of the development platforms that I hardly desired for developing business applications.

For me, it was always Oracle forms and reports. The easiest, the most robust development tools for BUSINESS applications which is supported by the ease of SQL, PL/SQL

I am doing loads of research with C# & SQL database, which will be the main technologies behind our next proposed ERP suite. After spending almost 15 years with Forms and reports, I don’t see Oracle is too keen about modernizing their most loved development tools & the latest release lacks loads of features those would have helped to reclaim the desktop based business applications.

Wondering whether there is still room for desktop applications, especially for businesses? dude! there is, and there will be!

 

So stay tuned, I will start posting my “findings” about C#, who knows may be some sample applications using Windows forms or WPF & Oracle database in near future!

regards,

rajesh

 


Oracle E-Business Suite R12.0 | Automating clone process

February 16, 2016

Hi guys

A clone is the exact replica of a production instance, against what you do all tests, custom development and patch deployments to insure that your attempts are NOT going to break the PRODUCTION instance once such are moved over to.

How often consultants & users may request for a fresh clone (with latest data) depends upon many factors. During the implementation time, a DBA could be bombarded with requests for cloning almost once in couple of days. Although I am “not a dba”, I have been doing cloning to learn & understand the technology from last couple of years time & trust me, it is NOT at all fun. Especially once after you are familiar with the tasks.

Throughout last few months I was trying to “fully automate” the entire cloning process and made significant advancements with the process. I will share my experiences with you today

Scenario:

We have a cron job initiated by “root” user, starting by 2:30 PM every Friday, that shuts down the application and database after running pre-cloning. The same script makes tar balls for both the database and application nodes in separate files and then copies the tar balls to our TEST instance server.

Logically once the tar balls are copied to TARGET (TEST) server, following activities are expected from the DBA

  1. Stop the application & database instances those are online in the TEST server
  2. Extract the tar balls copied from PRODUCTION instance to relevant folders
  3. Clone database tier, followed by application tier
  4. Tune the database with TEST server specific SGA/job queue processes etc parameters

What if I am too lazy & a scripting junkey who wants to automate the entire activities using shell scripts? The following demonstrates such an attempt

Why not a cron job? ;)

The first step will be, creating auto response files for both database and application nodes. I have already detailed a how to here

ebsclone.sh | This shell script calls a number of other shell scripts to facilitate the entire cloning process

Please note, my Oracle database user is “oraprod” and application manager is “applprod”. If you are planning to copy the below script(s), make sure you change the physical paths, user details according to your specific environment.

Both the database and application manager user accounts are enhanced with .bash_profile values. Hence most of my scripts will not populate the environment variables prior executing other scripts.

I am using “expect”, that YOU must install, if not already installed in order to automate the cloning process. If you are using Oracle linux, you can install expect by issuing the following command:

yum install expect -y
#!/bin/bash
# As a precaution to make sure the port pool is available during automated
# Cloning, we will kill all orphaned processes those were not closed during
# DB, APPS stop

# Kill all processes for both oraprod & applprod users
echo "Killing all processes for Oracle user"
pkill -u oraprod
echo "Killing all processes for Application user"
pkill -u applprod
echo "All processes for both Oracle and Application users were killed"

sleep 30

echo "$(date)"

#Remove the existing physical folder for database files
cd /u01
find oraprod -delete
echo "finished deleting Oracle top at $(date)"
#Extract files for database top from the cold backup archive
echo "Extract database backup file at $(date)"
time tar -zxf /u02/backup/PROD_DAILY_BACKUP_db.tar.gz
echo "Finished extracting database backup file at $(date)"

#Remove the existing physical folder for application files

cd /u03
find applprod -delete
echo "finished deleting Application top at $(date)"

#Extract files for application top from the cold backup archive
echo "Extract application backup file at $(date)"
time tar -zxf /u02/backup/PROD_DAILY_BACKUP_apps.tar.gz u06/applprod/PROD/apps
echo "Finished extracting application backup file at $(date)"

#Move the files around based on your configuration files (db.xml & apps.xml)

mv /u01/u05/oraprod /u01
mv /u03/u06/applprod /u03

#Change the ownership of the folders, so that corresponding users could read & execute files

chown -R oraprod:oinstall /u01/oraprod
chown -R applprod:oinstall /u03/applprod

########################################
#Start the cloning
########################################

echo "Database cloning phase starts now, $(date)"

#su - oraprod -c "perl /u01/oraprod/PROD/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTier /u01/clonescripts/db.xml"

/root/scripts/dbclone.sh

sleep 30

echo "Application cloning phase starts now, $(date)"

# su - applprod -c "perl /u03/applprod/PROD/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier /u01/clonescripts/apps.xml"

/root/scripts/appsclone.sh

echo "EBS Cloning completed, $(date)"
######################################
#Optional steps for changing database SGA parameters,
#startup configuration files to spfile etc
######################################
echo "Changing database parameters, $(date)"

/root/scripts/dbfix.sh

echo "Done! Application online with changed database parameters, $(date)"

Now I will copy the code for each script called within the ebsclone.sh script
dbclone.sh | script enabled with expect which will not ask for the apps password

#!/usr/bin/expect -f
set force_conservative 0  ;

# set to 1 to force conservative mode even if
# script wasn't run conservatively originally

if {$force_conservative} {
        set send_slow {1 .1}
        proc send {ignore arg} {
                sleep .1
                exp_send -s -- $arg
        }
}

set timeout -1

spawn su - oraprod -c "perl /u01/oraprod/PROD/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTier /u01/clonescripts/db.xml"

match_max 100000

expect -exact "\r
Enter the APPS password : "
send -- "apps\r"

expect eof

appsclone.sh | script enabled with expect which will not ask for the apps password

#!/usr/bin/expect -f
set force_conservative 0  ;

# set to 1 to force conservative mode even if
# script wasn't run conservatively originally

if {$force_conservative} {
        set send_slow {1 .1}
        proc send {ignore arg} {
                sleep .1
                exp_send -s -- $arg
        }
}

set timeout -1

spawn su - applprod -c "perl /u03/applprod/PROD/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier /u01/clonescripts/apps.xml"

match_max 100000

expect -exact "\r
Enter the APPS password : "
send -- "apps\r"

expect eof

dbfix.sh | Changing database parameters like SGA, job queue processes etc

#!/bin/bash
su - applprod -c "/u03/applprod/PROD/inst/apps/PRODBAK_erp-prodbak/admin/scripts/adstpall.sh apps/apps"
su - oraprod -c /root/scripts/dbalter.sh
su - applprod -c "/u03/applprod/PROD/inst/apps/PRODBAK_erp-prodbak/admin/scripts/adstrtal.sh apps/apps"

Finally dbalter.sh | All database alter commands are included within this file

#!/bin/bash
export ORACLE_HOME=/u01/oraprod/PROD/db/tech_st/10.2.0
export ORACLE_SID=PRODBAK

#http://www.cyberciti.biz/faq/unix-linux-test-existence-of-file-in-bash/
#Check whether spfile already exist
file="/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/spfilePRODBAK.ora"
if [ -f "$file" ]
then
	echo "$file found. Aborting database configuration now"
exit;
else
	echo "$file not found."
fi

sqlplus "/ as sysdba" <<EOF
create spfile from pfile;
shutdown immediate;
startup;
alter system set sga_max_size=8G scope=spfile;
alter system set sga_target=8G scope=spfile;
alter system set job_queue_processes=10 scope=both;
shutdown immediate;
! cp /u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora /u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora.original
! >/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora
! echo "spfile=/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/spfilePRODBAK.ora" >>/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora
startup;
exit;
EOF

The above five scripts should do what they are meant to. Just copy the files to same folder
Change the execute mode of ebsclone.sh

chmod +x ebsclone.sh

and execute the ebsclone.sh as “root” (attempts made with other users will fail the cloning)

#./ebsclone.sh

Prior attempting, please make sure all the above scripts are modified with absolute paths, referring to your existing partitions & other

Download the scripts here

References:

Sample expect script

https://community.oracle.com/thread/2558592?start=0&tstart=0

Linux: Find whether a file already exist

http://www.cyberciti.biz/faq/unix-linux-test-existence-of-file-in-bash/

Happy cloning!


Virtualize your desktop/laptop computer

January 3, 2016

Hello guys

This is my first post for the year 2016. Actually I have more drafts than posts this time with me and I truly hope that I will able to finish all of them in few weeks time. I’m sure you are interested about Oracle 12c products on Windows platform. Stay tuned, am on it

Coming back to the topic, I have a decent desktop computer that could be called a “half server” with the following configurations

  1. i7 processor 2nd generation
  2. 16GB DDR3 memory
  3. Around 4.5-5 terabyte of storage
  4. windows 10 64Bit Professional

I came across VMWare always 8 years back, liked it, and used it until they started charging for everything. Then Oracle VirtualBox (Earlier JAVA VirtualBox) started giving tough fight and it has become quite famous among developer communities where NOT many were truly able to afford VMWare’s paid software.

VMWare is altogether a different standard made for sophisticated environments, hence we will never compare both the products here. Our intention is to point you towards the advantages of using Virtual Machines that could less clutter your rig, give you better exposure to different technologies and a bit of networking etc

So our product of interest for this post is Oracle VirtualBox and we will see how we can utilize our existing desktops/laptops to run multiple virtual machines at the same time and thus utilize the available hardware to the maximum extends

So prior attempting to virtualize your existing computer, you need to know what kind of hardware you have and whether your computer meets minimum requirements to support virtualization.

So today is 3rd January 2016 & if your computer is 4 years old or less,  99.9% chances that your rig sure supports virtualization. Most of BIOS comes with the virtualization enabled by default.

Yet we should make sure that our computers support virtualization. Read the instructions provided here to find out whether your current processor supports virtualization OR

Just install Oracle VirtualBox and try to create a VM. You will immediately come to realize whether your rig really supports virtualization ;)

Well, that’s the brute force way of doing stuffs, adapt the one that defines you. If you are using Windows 10, I’ve noticed that many default installations enable Hyper-V by default. You need to disable it from add remove windows features console, so that you can create 64Bit VMs using Oracle VirtualBox. This post explains how to enable it, just do the opposite to disable it

Before anything else, you need to identify your processor, it’s capabilities. A nice comparison is available here for i3, i5 & i7 processors and definitely, the author favors i7 processors. Please spend few minutes to read about the differences between these three different processors.

A ripoff from http://www.pcadvisor.co.uk

What the difference between Core i3, i5, i7: Hyper-Threading

A thread in computing terms is a sequence of programmed instructions that the CPU has to process. If a CPU has one core, it can process only one thread at once, so can only do one thing at once (as before, it’s actually more complex than this, but the aim here is to keep it simple and understandable).

Hence, a dual-core CPU can process two threads at once, a quad-core four threads at once. That’s twice or four times the work in the same amount of time.

Hyper-Threading is a clever way to let a single core handle multiple threads.

A Core i3 with Hyper-Threading can process two threads per core which means a total of four threads can run simultaneously. The current Core i5 range doesn’t have Hyper-Threading so can also only process four cores. i7 processors do have it, so can process eight threads at once. Combine that with 8MB of cache and Turbo Boost Technology, and you can see why it’s good to choose a Core i7 over an i5 or i3.

Now, you should know how much physical memory you have. More, the merrier. Starting with Windows 7, computers started shipping with a minimum of 4GB as a standard. So, 4GB is enough for your OS and proposed virtualization? It’s going to be a tight fit. I will suggest an additional 4GB minimum, making the total physical memory 8GB so that you won’t have to sacrifice performance.

Finally the storage. Most of the branded PCs and laptops are coming with 500GB HDD as standard & extending the storage of a laptop is complex than of a desktop computer. For the later, all you need is another HDD which you can plug to one of the available SATA ports and configure. With a laptop, you may need to replace the HDD with a higher capacity one or use an external HDD for your additional storage requirements.

My current Virtualizations are mostly for Oracle technologies. I am a forms and reports developer, doing a certain level of .NET development & manage Oracle EBS R12 instances (“NOT a DBA”). Further I try almost all the database, weblogic versions & currently learning ASMM & RMAN. I have never attempted other areas of Oracle technologies, so my VMs run

  1. Oracle EBS R12 cloned instances
  2. Oracle database(s)
  3. Oracle weblogic server(s) with forms and reports (supported)

My EBS R12 VM has the following configuations

8GB memory out of 16GB physical, 4 processor out of 8 logical processors & almost 850GB of storage out of 4.5 terabyte total storage. Our instance has 400GB database size, 175GB application instance & the VM responds to requests instantaneously once after scheduled jobs are completed after a restart of the application. Usually I find the application responding better after 3 hours of settling down & the performance is assured throughout days and weeks until a restart.

My Weblogic, database VMs have the following configurations

4GB memory out of 16GB physical, 4 processor out of 8 logical processors & almost 250 GB of storage out of 4.5 terabyte total storage. I have my weblogic VM running 11gR2 64bit database as well. I get instant responses from both the Weblogic server and Oracle database 11gR2 from a client system, whenever accessed

Recommendations

Even though you can run multiple VMs at the same time, I would suggest, based on your hardware, limit them. Example

When I run my EBS R12 VM (8GB memory, 4 processors) my HOST computer is left with only 8GB free memory and 4 logical processors. If I start a Windows XP VM (2GB memory, 2 processors) to check the application performance, I feel my rig start slowing down and couple of times my computer shutdown with a high thermal point.

So make sure that you do tweaking to your VMs in order to make sure that your HOST doesn’t breakdown due to overload.

I always make sure that my HOST always has half of the hardware resources reserved for it, ie, 8GB memory, 4 processors regardless how many VMs I run at the same time! This is by using the VirtualBox console to alter the parameters of VMs before they are started

1

All the settings for the VMs could be altered using the settings, like increasing or decreasing the memory, processors, adding and removing storage devices etc.

2

Below you watch how fast my VM running EBS r12 responds to requests from another VM running Windows XP

Advantages of using Virtualization

The most important advantage for me is: I’ve a less cluttered HOST, said, I am NOT installing all the technologies to one OS, breaking it with conflicting versions of services and libraries and processing load.

Other advantages

  1. I can backup (copy) my entire “machine” and restore it during a total mess up or loss of data, rather than rebuilding the entire computer
  2. I get a sand-boxed environment & without fearing my attempts will break my main OS, continue the experiments
  3. I can make a VM, for example, running Oracle Enterprise Linux, copy and keep the OS installed disk somewhere and copy it to new VMs whenever required! Say, you install the OS only once and whenever you need to create a new VM with same OS, just duplicate the disk that has the OS! (Make sure you make a backup of the OS disk prior installing and configuring additional software into it)

Finally my suggestions for you, in case if you are considering to build your 1st VM using Oracle VirtualBox

  1. Majority of the Oracle geeks prefer Linux against Windows for database, weblogic deployments. So if you are NOT familiar with Linux, I suggest you start learning, regardless whether you FEEL very comfortable with it or NOT. You may be joining a firm that has reservations towards Windows OS running Oracle products, especially Oracle DBAs who have valid points like block corruptions, difficulties to recover from a crash are complex in the case of Windows OS.
  2. Install OS in a separate disk. 40GB dynamic size should be more than enough for any recent Linux distros. Avoid Linux 7 if you are truly new to Linux. Oracle Linux 6.7 should be your friend.
  3. Install 64Bit OS, so that you can take the advantage of your 64Bit processor and physical memory
  4. Install Oracle supported Linux distros (RHEL, OEL & certain versions of Debian. CentOS is not at all supported)
  5. Install the complete desktop, you are hardly going to complete the installations on pure CLI mode.
  6. Add SCSI interface to your VM and for Oracle database etc, use SCSI disks. I had a huge argument with VirtualBox guys about the performance difference between SATA VDI disks and SCSI VDI disks. I found the SCSI VDI disks performing better, however I was dismissed saying as far both the types are created on the same HDD, it must be more psychological :O
  7. Use 1TB 7200 RPM disks in the place of 2TB 5400 RPM disks. Later ones are best for data storage, when the previous ones give you better I/O. Create fixed size VDI for databases & applications, that means you will NOT able to increase the size of the disk once after created, however, it gives you faster I/O and better performance.
  8. Update your OS. As soon as the VM is built, update your OS prior installing database, weblogic etc. RHEL will NOT allow you to update the packages without subscription, hence Oracle Enterprise Linux should be your best choice of Linux distro. Please note, you shouldn’t use Oracle Linux in a production environment without acquiring sufficient licenses. Whatever I suggest here are limited for study/evaluation purposes and I don’t encourage any kind of illegal usage of software!
  9. Use Oracle’s pre-install packages to install database, EBS etc prerequisites rather than trying to download individual components from different download sites.
  10. Use shared folders between HOST and Guest (VM) so that you don’t have to sacrifice storage. Not just that, when you want to keep the backup of some files from the VM, the shared folders will make it as easy as possible
  11. Use bridged network, with Promiscuous Mode “Allow All” so that you can communicate with the VM from network
  12. 3
  13. Disable IPV6, firewall, SELinux on your Linux VM
  14. If you creating a Windows VM, I’m sure you better know how to configure your guest so that you can access it from a network.

Finally recommendations for a DESKTOP computer to try virtualization

  1. i7 processor + good quality heat sink. Your HOST and VMs are going to create loads of heat!
  2. 16GB Memory (DDR4 is the new standard, do not ignore it)
  3. 1×2 TB HDD

Few years back, such a configuration looked impossible for me, well, I saved bit by bit and made my dream computer. I’m sure you can also do it :) & trust me, a good computer opens a new world for you.

All the best and wish you all a very successful year ahead!

for Windows7bugs

rajesh