While you are working in a very complex environments, it may become mandatory as a domain admin to disable the “Admin Share” from your specific computer (How to disable Admin Share) in order to protect some contents from other “Admins”. Well, it happens :)
While disabling the “Admin Shares” gives you a kind of invisibility, it has major disadvantages also. One of them being, asset collection software failing to read information about your computer in the network. This list could be long like the one provided by this Microsoft article. Though the article was specifically written for Windows XP, most of listed issues are applicable to all later versions of client OS (Haven’t tried with Server OS yet)
&
Oracle 12c installation will fail, and provide you very confusing error messages about unable to read temp location and more. If you come across such an error, do not wait, read our previous article about disabling Admin Share on Windows 7, browse to the registry “HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters” & check the value for key “AutoShareWks” Type: DWORD entry and confirm it is not set as “0”. If yes, change the value to “1”, reboot your box and the installation must go ahead without giving errors associated with reading details about “TEMP” space.
Please note, this is just one of the issues & you may be subjected to other issues while installing the Oracle software, especially on a uncertified platform like Windows 10.
Google drive have security requirements changed & you have to log on to your google account to download the file attached with the post. I’ve edited the code sections, so that you can copy the scripts from the post without downloading.
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!
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”
and within the “Database” folder you will find Oracle, SQL Server folders
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
temporary tablespace temp;
GRANT connect, resource to unisuser;
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
);
*/
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
64Bit Windows Server 2008 r2 or later with ONE STATIC IP address
Oracle 10g R2 64Bit (You can try 11g or 12c databases,provided you are pretty sure aware of the password complexity & expiry parts)
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
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)
Try to start UNIS Remote Manager software and if your configurations were correct in the place, should start without giving any errors
(Check the Master Logon, that will put “0000” in Admin ID and the default password for Admin account “0000” is “1”)
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.
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.
