Blogs on Oracle DBA / Temenos T24 - Deepak Kumar

  ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using I recently configured a Heterogenous connectivity from our Oracle dev database to a MSSQL dev database. I used a custom name for the gateway sid name. I have installed Oracle 19c gateways for Microsoft SQL Server in the Oracle DB server. I have followed the Oracle Gateways 19c documentation and cross verified with the Oracle support notes.  Every things at the configuration level seems to be fine but the test failed with above error. I enabled the dg4msql debug and got "NCR-1003 Read error", searching this error on Oracle support or on the internet yielded nothing. I have sieved through a lot of Oracle support notes but nothing helped. I have tried to redo everything from scratch and thus arrived at the solution for the issue. I used a SID name that was 10 char long. I have used a smaller name and the issue is solved. The error was very s...

User creation, Roles and privileges in 12c. User creation and role & privilege management changes slightly on Oracle 12c. Now we have two types of user and roles & privileges such as Local and Common. * Local User * Common User * Local roles and privileges * Common roles and privileges. Below are some points about this topic. A CDB can contain only common User. By default any user created in a CBD is a common user. Let’s create a Common User. <pre class ="brush: csharp" SQL> l 1* select username,user_id,account_status,profile,common,oracle_maintained from dba_users where oracle_maintained='N' SQL> / USERNAME    USER_ID ACCOUNT_STATUS    PROFILE COM O --------------- ---------- -------------------- ---------- --- - C##TESTUSER1    105 OPEN DEFAULT YES N C##CDB_USER    102 OPEN DEFAULT YES N C##TEST1    104 OPEN DEFAULT YES N SQL> </pre> - A common user is...

A nice video by Arup Nandha on Performance tuning. I found it very informative, sharing the link here. I hope it will benefit you too.

Download Oracle database software. You can download Oracle database software from the link http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html ·          You will need an OTN account to download this software. (It’s a free signup) ·          You can create an account with the link https://login.oracle.com/mysso/signon.jsp ·          You can use this software free for personal educational purpose. ·          You can only download current release (which is 12c) and the previous release (which is 11g) ·          If you require older version you will need Oracle Support account (which is paid account). Note : You can download various other software from Oracle free for educational purpose.

·       *    In a server if there is a listener running, then an Oracle database can dynamically register it in the listener to provide its service. ·        *  This dynamic registration is automatic if the listener is running in the Oracle default listening port 1521 ·        *   Incase if you want to allow dynamic database registration only to certain databases in the server. You need to run the listener in non-default port. (For example: 1522 or 1530 etc) ·        *   To allow dynamic registration of database services in the listener with non-default port we need to modify few parameters in database level. No changes needed in listener.ora file. Below are the parameters o    In case if the listener and database are in same server change the parameter LOCAL_LISTENER For example: Alter system set local_listener=”orclnew” scope=both; Where listener2 should be r...

Archivelog mode is a must for a production database as it ensures high recoverability of the database and provides greater flexibility to backup. Its very simple to turn on archiveing of Redo logs in your database. First step is check the log mode of the database. There are two ways to check this. First way is to query the column LOG_MODE from v$database. sys@ORCL> select log_mode from v$database; LOG_MODE ------------ NOARCHIVELOG sys@ORCL> sys@ORCL> Second way is to use the command ARCHIVE LOG LIST sys@ORCL> archive log list Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     51 Current log sequence        ...

      ORA-01092 & ORA-30040 ·          Mount the database. ·          Create a pfile from spfile to a desired location. For example CREATE PFILE = ‘f:\temp\test\initorcl.ora’ from spfile; ·          Edit the pfile and give an online tablespace *.undo_tablespace='UNDOTBS3' already existing. ·          Start the database with this new Pfile, for example STARTUP PFILE=’F:\TEMP\TEST\initorcl.ora’ ·          Create spfile from the new pfile settings loaded using the command “CREATE SPFILE FROM PFILE;” ·          Bounce the database.

SP2-1503 Error Oracle 11g. SP2-1503 : Unable to initialize Oracle call interface. SP2-0152: ORACLE may not be functioning properly. This error is due to version mismatch between the binaries of the sqlplus called and the listener, instance running in the server. Action taken : The system contained 2 Oracle homes as shown below The environment variable “Path” contained the bin path of both the installation. Note that we try to start 10g, but the bin path of 11g comes first in the “Path” variable. As a result of this, version mismatch happened during the call. The sqlplus called is 10g and the listener called is in 11g. Due to this version mismatch problem, we faced the error SP-1503. Modify the path variable and remove the bin path of 11g and try again. Or you can change the order of bin path. Lsnrctl of 10g is now started correctly. The instance will start correctly now.

The flow is * First you need to create a cluster. * Then create tables that need to be clustered by using the newly created cluster. The idea of clustered table is to minimize space used by a group of tables by clustering column that occur commonly in these tables. Suppose there are 3 tables namely T1, T2, T3 and they all have a column named Emp_ID with the same values, then clustering these table using this column Emp_ID reduce space occupied by the column. This column will be stored only one time in a cluster instead of storing in 3 tables. Here is the command to create a cluster create cluster test_cluster (Emp_ID varchar2(32)); Create the tables that need to clustered by using the above created cluster. create table T1 (Col1 varchar2(100), Emp_ID varchar2(32)) cluster test_cluster(Emp_ID); create table T2 (Col1 varchar2(100), Emp_ID varchar2(32)) cluster test_cluster(Emp_ID); etc. Make sure you match the type of the cluster column a...

I am recently getting some knowledge in T24 performance tuning. I will share some of my experience from my work while creating reports for APAP * Never put a select blindly on heavily populated tables like ACCOUNT, CUSTOMER etc * Even a medium sized bank have nearly 1 million records in the ACCOUNT table (including the internal accounts.). Blind selects on tables like these will never return. * Index highly queried columns of the most common tables like CUSTOMER, ACCOUNT, CARD/ISSUE etc.. * Make sure you index only the necessary fields only. If you index large number of fields, it will inturn kill the server, as every commit on the table need to update all the indexes. * Its better to use more robust databases like Oracle DB or SQL Server DB as the backend database instead of JBase as they have better performance. * If you are writing a nofile enquiry never do a SSELECT if you need sorted output. Instead finish the processing of the output array and sort the array using the SOR...

The query Language of Jbase is really restrictive. Data analysis and comparison between two table is not possible without the use of spreadsheet software. I recently came to know that Jbase is providing a command called SQLSELECT through which you can actually issue SQL query command directly on Jbase tables. A sample would be like SQLSELECT * FROM FBNK.ACCOUNT WHERE SHORT_NAME LIKE '%PETER%' AND CATEGORY = 6601 Limitations: The command though have serious limitations such as  The table you query should not have dot charecter for example you cannot query FBNK.CUSTOMER.ACCOUNT with this command. But there is a workaround for the above drawback. Make a copy of the VOC record FBNK.CUSTOMER.ACCOUNT into a new one without ID in its ID. For Example: COPY FROM VOC FBNK.CUSTOMER.ACCOUNT,FBNK.CUSACC SQLSELECT * FROM FBNK.CUSACC; will work. The field name should not have dot character. You can overcome this by changing the dot character to underscore. For...

The purpose of this T24 API is to increase the amount of a LIMIT. This can be either in the Online or during COB. The syntax of the API is given below LIMIT.PROCESS.INCREASE (LIMIT.ID,LIM.REC,INCREASE.TYPE,INCREASE.CCY,INCREASE.AMOUNT,AMT.TYPE) Input Parameters : ---------------- LIMIT.ID      - ID of the Limit for which the increase has to be done. LIM.REC       - Entire Limit record for which increase is done.                 Optional argument. INCREASE.TYPE - It can be either FULL/SINGLE.                 FULL - Increase is done for the entire Limit structure.                 SINGLE - IncreasINCREAthat Limit. INCREASE.CCY    - Increase amount currency. INCREASE.AMOUNT - Amount to be ...

You can jbase and linux command into a single command and execute. This is really powerful option. For example if you want to list only COB services from the file F.TSA.STATUS you can do it like below. prompt --> SH LIST F.TSA.STATUS | grep COB This will do. The command SH is a jbase command which will execute linux commands in Jbase. This will work on mw42 also prompt -->SH mw42 | grep tSA prompt -->SH mw42| grep tSA    10    r10 1409350      6 (5)    5    1    25     6   18 5.25M  0.00  2 E SH mw42| grep tSA (SH,48)    41    r10 1511704     47 (8)    4    1 12045  1382  295 20.5M  1.81  2 SLEEP tSA 29 (BATCH.JOB.CONTROL,943)    43    r10 2470242     47 (8)    4  ...

You can select all the failed OFS transactions using this query SELECT F.OFS.RESPONSE.QUEUE WITH F1 LIKE -... This will select all the records from the file F.OFS.RESPONSE.QUEUE which are failed due to any reason. If you want to select only the failures due to error then use this query SELECT F.OFS.RESPONSE.QUEUE WITH F1 LIKE -1... If you want to select only the failures due to overrides then use this query SELECT F.OFS.RESPONSE.QUEUE WITH F1 LIKE -2...

The following query can be used in a Jbase server to list all the User login VOCs created in that environment. LIST VOC WITH TYPE EQ PA AND F3 EQ EX In this query the Variable F3 denotes the position of the command EX in the VOC entry. In the below entry EX is in 3rd line hence the query uses the variable F3. If it is in line 2 then we need to use the query as LIST VOC WITH TYPE EQ PA AND F2 EQ EX An example VOC record is given below File VOC , Record 'A1' Command-> 0001 PA 0002 ETS 0003 EX 0004 DATA INPUTT 0005 DATA 123456 0006 DATA Y

There is a core routine that will return all the accounting entries for an account it is EB.ACCT.ENTRY.LIST Syntax: EB.ACCT.ENTRY.LIST(ACCOUNT.NUMBER,FROM.DATE,END.DATE,YID.LIST,OPENING.BAL,ER) Passed Parameters. ACCOUNT.NUMBER :- Account for which balance & entries is to be returned. FROM.DATE :- Start date for opening balance and entries. END.DATE :- The last date to be considered. Outgoing : YID.LIST :- List of statement entry ids. OPENING.BAL :- Opening balance on the startt date. ER :- Any errors found

Description: We need to generate different drop downs on a version field based on another version field. We tried to populate the T array of the target field by making it as a Hot field Validation field, but it did not help. So to resolve this we created a Nofile enquiry and attached it to the target field. As this is a Nofile enquiry we can manage what to display in the field. Below are the details: There are 2 associated multi value fields and are, 1) THIRD.PARTY - YES or NO Field 2) APPL.TRANS - Normal Field Version Validation: IF THIRD.PARTY = NO THEN ONLY 8 TRANSACTION TYPES SHOULD COME IN DROP DOWN IN THE TRANSACTION.TYPE FIELD END ELSE ONLY 3 TRANSACTION TYPES SHOULD COME IN DROP DOWN IN THE TRANSACTION.TYPE FIELD END To achieve the code is written as below and attached with the field THIRD.PARTY: THIRD.PARTY = COMI IF THIRD.PARTY EQ 'YES' THEN T (QTC.CBD.APPL.TRANS) <2> = "Withdrawal_Earning _Distribution_Loan Advances...

CURRENT VARIABLE Introduction In addition to the system common variables that can be used in ENQUIRY and VERSION there is a feature where the user can populate a variable of their own and use it later. There are now a series of new system variables and a much wider option for users to create and use their own which are: CURRENT.CUSTOMER CURRENT.ARRANGEMENT CURRENT.EXTERNAL.USER These may be set on login by an external user and cleared when they logout. This means that ENQUIRY & VERSION can be tailored to accept the content of the variable instead of forcing a user to enter their own customer number or arrangement etc. Similarly in VERSION you can now default a field content by using the !CURRENT.CUSTOMER to populate a customer field with the current content of the variable. So the variable could be set in one Enquiry and used in either another Enquiry linked to it; or via VERSION to populate fields in an application triggered from the Enquiry. Note: ...