Thursday, October 12, 2023

Erman Arslan's Oracle Forum / AUG 25 - OCT 12, 2023 - "Q & A Series"

Remember, you can ask questions and get remote support using my forum. Just click on the link named "Erman Arslan's Oracle Forum is available now". Click here to ask a question", which is available on the main page of Erman Arslan's Oracle Blog. More than 2000 questions were asked, almost 10000 comments have been made and here is the links to the latest questions from the last period.


-- or just use the direct link:


Supporting the Oracle users around the world. Let's check what we have in the last few weeks..

Need Advice on job queue processes for upgrading to 12.2.10 by prabhunoule

autostart grid by Roshan

Pending OPP issue with custom program by VinodN

Unable to Login EBS R12.2.10 in Windows 7 32bit by prabhunoule

Workflow Java Mailer Analyzer by big

enterprise manager wallet by Roshan

Login Issue with users by prabhunoule

restore data pump by Roshan

mariadb locks by Roshan

undo_retention in 19c database by raiq1

Query on interoperability patches from 11.2.0.4 to 19C by prabhunoule

restart one node by Roshan

Discoverer 10g by Samia

restore data pump by Roshan

Oracle Database 12.1.0.2 to 19c non-CDB upgrade by Hassan141

refresh DB by Roshan

need ebs online instance by motoky

XLAACCPB: Create Accounting issue by Samia

Clone failed 19c database. by satish

Prepare phase failed ORA-00904: "TABTYPE": invalid identifier by satish

 

Wednesday, October 11, 2023

RDBMS -- Oracle 12.1 - Problem accessing Web Service due to SNI Extension (Web Services failing with ORA-29263, Ora-29259 errors due to SNI Extension)

Intro:

Oracle databases have the ability to integrate with many applications and systems today. Web services are frequently used during these integrations. However, these integrations do not always occur smoothly. The problem we encountered occurred when calling a web service API from the Oracle database via PL/SQL code. This web service had a critical role in the application, so we had to solve this problem.

The issue appeared when tried to access a web service using the HTTPS (TLS.1.2) protocol. The client (Oracle database in this case) encountered an unexpected error message..

Oracle Database 12.1 - Problem accessing Web Service due to SNI Extension:

Analysis, Solution Suggestions and Workaround:

Web service call using the example code of which you can see below, resulted in ORA-29253 and ORA-29259 errors.

select utl_http.request('https://testerman.example.com/WebService/ErmService.asmx', null, 'file:/oracle/PROD/db/tech_st/12.1.0/admin/PROD/wallet', 'wallet_password') from dual;

 ORA-29263: HTTP protocol error

ORA-29259: end-of-input reached

---

These error messages, at first glance, indicate that the call to the web service failed and the expected response was not received. But what was the real reason behind these errors? We had to do an in-depth analysis to find the answer to this question. I will share with you our findings regarding that in-depth analysis and the solutions we implemented using the output of the analysis.

While performing the analysis, we went through the following document to identify SSL/TLS issues between the database and the web server: MOS note "How To Investigate And Troubleshoot SSL/TLS Issues on the Database And Client SQL*Net Layer (Doc ID 2238096.1)" 

Based on the MOS document above, we performed many checks and enabled different types of traces (tcpdump, sqlnet.ora, listener). We examined the trace files in detail. We checked the wallet permissions and made sure that they are correct. Likewise, we examined the certificates of the relevant HTTPS web server and concluded that there was no problem there either. As a result of these investigations, we agreed that the problem was not related to the wallet or certificates.

Considering the SSL/TLS version we were using (TLS 1.2), we checked the PSU level of the database.. The PSU version was not the highest one, but it was high enough and anything higher would not have added anything to us.. However, in addition to all these checks, the network traces we received with tcpdump revealed some great details. 

The command used for getting the relevant trace using the tcpdump was as follows;

/usr/sbin/tcpdump -i <interface or any> -s0 -w <output filename>

Example command: /usr/sbin/tcpdump -i eth0 -s0 -w ssl_dump.cap

Note that we analyzed the tcpdump traces with Wireshark...

In parallel to that, we also continued to review other Oracle documentation and MOS notes, specifically when we were investigating the source of  "ORA-29259: end-of-input reached" and "ORA-29263: HTTP protocol error".

These errors could also be related to wallet access permissions. However, the changes we made in this direction did not solve the problem (ACE or ACL permissions didn't help, the cause was not related with those things.) -- a reference : MOS note : "ORA-29259: end-of-input reached" or "ORA-29263: HTTP protocol error" when using HTTPS with a Wallet File (Doc ID 2303905.1)")

We also checked the ciphers as there might be a missing cipher in the database.. However, we saw that the patches correcting the potential missing ciphers were already applied to the client side (the Oracle database 12.1 in this case) -- a reference: MOS Note: "UTL_HTTP access to secured website fails with ORA-29259 in 12c database (Doc ID 2402276.1)" (Note that, we also used openssl to check the ciphers requested by the server)

The document above given as the reference contained the following statement: The issue is due to missing cipher suits in 12c. 

TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256  and ECDHE_RSA_WITH_AES_128_GCM_SHA256 were in question, but we had them in the database TLS layer, so it wasn't the cause.

We even explicitly defined the ciphers that we wanted to use in sqlnet.ora and listener.ora, but this action (as expected) didn't solve the problem either.

SSL_CIPHER_SUITES = (SSL_DH_anon_WITH_DES_CBC_SHA, SSL_DH_anon_WITH_3DES_EDE_CBC_SHA,SL_DH_anon_WITH_RC4_128_MD5,SSL_RSA_WITH_AES_256_CBC_SHA)"

We checked lots of things at many different areas and the known issues along with the possible bugs  related with TLS 1.2 and Oracle Database 12.1 carefully. 

After all these diagnostics and solution attempts, we turned back our focus to the findings we had with tcpdump.

The most important finding there, was the proof of SNI (Server Name Indication) extension usage during this problematic TLS 1.2 Web Service call.

Handshake Protocol: Client Hello

       Handshake Type: Client Hello (1)

       Length: 290

       Version: TLS 1.2 (0x0303)

       Random

       Session ID Length: 0

       Cipher Suites Length: 128

       Cipher Suites (64 suites)

       Compression Methods Length: 1

       Compression Methods (1 method)

       Extensions Length: 121

       Extension: server_name

             Type: server_name (0x0000)

             Length: 33

             Server Name Indication extension!!!!!! --  HERE IT IS!

                  Server Name list length: 31

                  Server Name Type: host_name (0)

                  Server Name length: 28

                  Server Name: test.server.com

        Extension: ec_point_formats

The SNI feature was not supported in Oracle Database version 12.1 and it was the cause of the problems.. In order to use SNI , the database version should be at least 12.2. Additionally, this SNI feature could not be backported to version 12.1.

Server Name Indicator (SNI) is an extension of the TLS protocol. At the beginning of the handshake process when establishing a secure SSL connection, it verifies the digital certificate of the server name (hostname) mentioned in the request. SNI technology allows multiple secure websites to be accessed via the same IP address and TCP port. "Since it performs the verification process for SSL, it eliminates the fixed IP requirement that was previously required to use SSL." source: https://tr.wikipedia.org/wiki/Server_Name_Indication

Even the latest database PSU release for 12.1 wouldn't solve the problem, since there was no backport..

We shared our findings about this SNI feature with the website administrator and asked whether this feature could be disabled on the web server side. They said they could disable the SNI only in their TEST environment, and we made them do so. We tired the web service call after the web server administrator disabled the SNI in the TEST environment , and the problem disappeared. 

On the other hand, the web server administrator (and the company that owns the web service) stated that they cannot disable SNI for the PROD environment.

This situation led us to find different solutions or workarounds.

Upgrading the version of the database (12.1 -> 12.2 or 19C upgrade) would solve this problem, but this kind of an upgrade would require an intensive testing process and it did not seem possible at that moment, considering the customer have no resources and motivation for such a task.

Well.. DB upgrade was not an option.. There was no solution for 12.1 Oracle Database ( No backports for SNI capability due to the inadequacy of the DB TLS code of 12.1)

Therefore, we started looking for alternative solutions. 

 Ability to look from differences perspectives, and produce flexible solutions to overcome the challenges that our customer face; let us implement a method of accessing the web service by calling a function through a "Database" that acts as an intermediate proxy.

This was actually a workaround.. It required less efforts for the implementation and did not directly affect the existing system. 

The solution we planned was to access the web service through a database link (DB Link) residing in a 12.2 Oracle database.

In this solution, the database in the middle (Oracle Database 12.2) provides access to a specific websi service with a function. However, what is interesting here is that it transmits this data directly to the 12.1 database via db link. However, the fact that DB Link does not support the CLOB data type appeared as an obstacle at the beginning. To get around this problem, we wrote a custom CLOB parser function and deployed it to the 12.1 database. Thanks to this function, we could receive the HTML response from the website without any problems.

-- 12.1 Database (The source)

CREATE OR REPLACE FUNCTION APPS.fetch_https_content_via_link(p_url IN VARCHAR2) RETURN CLOB IS

    l_clob clob;

    l_tmp long;

    l_offset number := 1;  

begin

    loop

      select dbms_lob.substr@https_link(dblink.fetch_https_content@https_link(p_url), 4000, l_offset)

      into l_tmp

      from dual@https_link;

   exit when l_tmp is null;

   l_clob := l_clob || l_tmp;

   l_offset := l_offset + length(l_tmp);

  end loop;

      return l_clob;

END fetch_https_content_via_link;

/

-- 12.2 Database -- the intermediate / proxy environment

CREATE OR REPLACE FUNCTION dblink.fetch_https_content(p_url IN VARCHAR2) RETURN CLOB IS

    l_http_request  UTL_HTTP.req;

    l_http_response UTL_HTTP.resp;

    l_content       CLOB;

BEGIN

    UTL_HTTP.SET_WALLET ('file:/orahome/LINK/db_122/product/12.2.0.1/db_1/wallet','wallet_password');

    -- Make the HTTP request

    l_http_request := UTL_HTTP.begin_request(p_url);

    l_http_response := UTL_HTTP.get_response(l_http_request);   

    -- Read the response content

    UTL_HTTP.read_text(l_http_response, l_content);

    -- Close the HTTP request and response

    UTL_HTTP.end_response(l_http_response);

    RETURN l_content;

EXCEPTION

    WHEN UTL_HTTP.end_of_body THEN

        UTL_HTTP.end_response(l_http_response);

        RETURN l_content;

    WHEN OTHERS THEN

        RETURN 'Error fetching content';

END fetch_https_content;

/

Thursday, September 21, 2023

EBS 12.2 -- Problem with AD_ZD package resulting unexpected 'component must be declared' errors

After applying CLE patches ( 34546768:R12.CLE.C , 28828799:R12.CLE.C) to our TEST instance, we noticed lots of invalid database objects owned by APPS user. Our EBS instance has become unusable due to these invalid objects. 

Tried to compile some of those invalid objects for diagnostics purposes, and noticed that almost all of them were complaining about missing GET_RUN_EDITION function, which was supposed to be there in the AD_ZD package.. It was a foundational deficiency.. Then, we checked the version of the AD_ZD package and we saw that its version was way older than the other EBS instances in the environment.. These were clone environments, cloned from one golden copy (let's say) and that's why it was impossible to have a difference in version (impossible to have an older version of a Standard Package.. Let's put it that way:) of a standard package in any of these instances.

It was obvious that one of these CLE patches recreated the AD_ZD package in this environment, but the AD_ZD code delivered with that patch was probably an old version.. So, there was a mismatch, a patch-environment compatibility problem.. I saw problematic CLE patches earlier, but I have never seen such an obvious failure due to patching.

Following the action below; we could fix the problem;

  • Stop Apps Services.
  • Run adgrants sql.
  • Copy the package (ADZDXS.pls) and package body (ADZDXB.pls) from a problem-free environment to the $AD_TOP/patch/115/sql directory of the problematic environment. (backup the old versioned files, just in case..)
  • Compile the package spec and bod codes residing in the ADZDXS.pls and ADZDXB.pls files, by connecting to the database using sqlplus
  • Compile APPS using adadmin.
  • Check invalid objects and ensure that they are validated.
  • Start Apps Services.
Tip: Be careful with CLE patches :)

Tuesday, September 12, 2023

An interview about my profession, my company (GTech) and me

From an interview I recently gave for a TV program... Q&A. You know we love this :) 

Question & Answers:

1) Can you briefly tell us how you started your career? Why did you choose this sector and how did it progress?

While I was completing my computer science education at Işık University Istanbul, I was reviewing the business lines and sectors in our field in 2006.

During my university years, I took a  comprehensive database course in addition to the software and other engineering courses.

The field of relational databases in general ( Oracle especially) was a promising field at that time. (Let's say Java in the software and Oracle in the database tier)

As soon as I completed my education, I started to do an internship in the software field, but at the same time, I decided to take the Oracle specialist training.

Within a period of approximately 2-3 months, I came across a Junior Oracle DBA job opportunity.. They were looking to find a junior to be trained in the HQ of Acıbadem Healthcare Group.

I considered this and applied for the job. 

The first 4 years were quite challenging but enjoyable.  During this period; Databases, Operating Systems and ERP Systems became my passion. In parallel to my work, I was also doing MBA in Bahçeşehir University, Istanbul.

I read more books than I did in college. I need to admid that, these first 4 years became the source of my motivation for blogging, establishing Forums and writing Books.

In Acıbadem, I added a lot of things to myself both as technical and soft skills. My managers were my luck. They trusted me.

Then, when I returned after the military service that lasted for 6 months (there I was also dealing with the software problems and developed 2 applications), I stepped into the consultancy sector.

I even received an offer from a consulting firm while I was in the military.

I have been working for 16 years in this field, and I have been a consultant for 12-13 years. 

I have been a manager in the field of Consulting for the last 8-9 years.

I have been an Oracle ACE Pro for 7 years.

I have been at GTech for the last 6 years. Here I am the Senior Director of the System Database and Middleware department.

We have a team of 15 consultant.. All of them are experienced and knowledgeable engineers. We are a real team and we have good relations with each other.

With this team, we offer technical consultancy services not only in Database technologies, but also in the field of Middleware, Integrated and Engineered Systems, Cloud and ERP (Oracle E-Business Suite).

We follow trends, anticipate future trends, and align in advance in new areas where we see the future.

Of course, in this process, we have completed many successful projects together with my team and we have lots of success stories in several business sectors.

2) Could you tell us about the services you provide?

In fact, as GTech, we provide solutions and consultancy in several fields. But! Analytical and financial technologies have been always the key of our success.

With our 22 years of knowledge and experience, we offer end-to-end services to our customers from all sectors, from software to hardware, from projects to 24/7 maintenance activities and managed services.

We not only meet their needs, but also help them meet today's standards, become more competitive in their fields with our modern solutions, and create more value more easily by getting rid of the background work. Thus, our customers find more time for focusing on their core business.

We provide services in the fields of Big Data and Advanced Analytics, Data Warehouse and Business Intelligence, Financial Services including Core Banking (which is our own software), System and Database management, Risk Management and Corporate Performance Management.

We have sub-services such as turnkey projects, consultancy, maintenance, outsource and training in the related fields.

We have many products besides our services. Symphony Core Banking, Symphony Sense, Our Legal Reporting application, Smart Bank and Profit 4.0 are some of them.

In GTech, where we serve with 300 employees, we develop these products in our own R&D centers. We have 5 offices, 1 of which is in Dubai. Our Maslak Office (our HQ) is also an on-site R&D center.

3) Detailed explanation of our services.

Our department touches the work of all departments in the company. We're at a key point -- especially in terms of troubleshooting and continuity.

As GTech, we can say that our Financial Services team, which has been growing rapidly recently, especially with Core Banking, is establishing banks. I mean, they establish the banking system of the banks (of newly established banks or of customers in the banking sector, that wants to change their banking software).

Of course, this is a complex task. Complex and fun.

In the background of this business, of course, there are databases, systems, middleware servers, message queues, load balancers, ETL and ELT, DWH and many additional software solutions and products.

We take part in this big picture by supporting all these solutions at all layers. From the sizing process to implementing best practices, from architectural design to installation & configuration. We support the solutions both during the project and after taking them to live.

We also work with Risk Management solutions, especially on the Moody's side.. There are also those studies we carry out together with our other departments like Data Warehouse, Big Data and Business Intelligence. We cooperate with other departments in cloud modernization solutions and related projects as well.. These cloud-type projects have been started in recent years, and we help the companies during their cloud transformation journeys by playing the role of Cloud Architect.

But, I want to emphasize that we are mainly on the system, database and middleware side of the business. We carry out critical work in our field such as database maintenance activities, Oracle ERP application administration, Middleware administration and operating system management and similar consultancy activities.

In these areas of the work, there is no tolerance for any error and the expected service is 24/7. In order to handle these critical tasks, you need to have an expert team, technical operators and a sophisticated software supported monitoring system like we have.

Customer satisfaction is very important for us.. We measure it. We act proactively in service quality through satisfaction surveys and after-sales interview processes. Monthly and weekly reports are also sent to customers.

We (as Systems, Databases and Middleware) also have our own software solutions and routines for simplifying and accelerating the monitoring process, and for handling some of the admin tasks automatically. Those routines also help minimizing the human errors.

In our field, bringing system accessibility to 99.999% (5x9), making the systems stable and robust, ensuring that the customers benefit from their hardware and software investments, adding value to the data management processes and being reliable advisors are the most important factors for the real success. Without compromising these factors, we look ahead (especially for exporting our services and expanding abroad) and we grow in a controlled and utilizing manner..  Achieving a world-wide success in these areas is among our most important goals.

4)What distinguishes you from your competitors?

First of all, as GTech we are very experienced in the field.

All the tech consultant and managers working in our teams are highly qualified and are involved in highly advanced technical tasks. Managers and directors are escalation nodes for the hard problems.

All the employees have the passion for their respective fields. (In my opinion, being passionate and disciplined are important features of successful and trusted consultants)

Since we have many business lines; the ability of transferring information and collaborating across different areas give us an opportunity for taking a step forward. Over time, this enables a team of experts in their field to approach the problems of different fields from the right perspective. Being able to measure the business impact before taking a technical action is another benefit of this capability.

The "consultant profile" is very common in our company. In this context, in addition to technical knowledge and experience, things such as customer relations, respecting the business ethics, minimizing the risks and staying on the safe side come to the fore.

Newcomers who are transferred to GTech or juniors who are just starting out quickly come to a point where they differentiate themselves from their competitors by internalizing this company culture and getting the benefits of it.

We pave the way for young people through the Gtech Summer School where we offer to new graduates every year.  After the summer school, we recruit suitable candidates. In this way, the knowledge of experienced consultants sheds light on young dynamic engineers, and we preserve dynamism as well as the knowledge.

As a company, we have very critical business partners. Oracle, Moody's, Microsoft are some technology giants that are examples of these.

Our consultants are certified in the related areas of expertise. Our company is also certified in most of these areas and is engaged with business partners in the background.

Professional and strong staff provide services professionally in all processes from sales to marketing , from consultancy and delivery to after-sales.

Successful projects, critical references, customer satisfaction, broad range of product and services, a good reputation and strong reliability are also among our highlights.

5) Can you tell us about your vision or your company's vision for the future?

As GTech, where we want to go is growth and globalization with quality. We are aimed to innovative, and increase our customer satisfaction by providing our services as promised.

Of course, while doing this, we focus on strategies such as making it profitable, sustainable and efficient (in terms of operations), developing the skills of our experts and employees continuously, and expanding towards to the new and right business partnership ecosystem.

My own vision is actually parallel to that of the company. If I need to personalize it a little, I can say that, my main goal has always been "being valuable". (rather than being important)

I think I have taken steps towards this goal with my day job and with the additional things that I have made so far. Instead of adapting to the changing world and living a relatively easier professional life with my past knowledge and skills, I face open problems. And I will continue on my way by challenging future technologies and doing more than what my job requires.

For this reason, I follow the developments in my own personal time and try to improve myself on the subjects that interest me.  I focus on subjects that can add value to us, our services and customers.

Physics and Quantum Computing are among the things I have been interested in lately.

6) Who should choose this profession and what qualifications should they have to do it?

I actually discovered my predisposition after started working. That is, my passion developed after I started working.

I would like to abstract the passion here a little more. In our field, a passion for problem solving makes things easier and allows you to make a difference in the short term.

I think the key to success in this business is researching, wondering, dealing with those tiny details (there are a lot of information in those tiny details), approaching problems causally, and doing all these with the motivation to learn new things at the end of the work.

Of course, discipline is required, a little perfectionism and order... Actually these are what engineering requires.

In addition to these, especially for consultancy; being passionate to learn new things, being okay with a dynamic business life and having the desire for working hard and being up-to-date and being resilience are the prominent features.

7) Finally, what would you like to say to the audience?

I wish everyone to do something they are passionate about. Sometimes you can't discover this on the first day or before, but I know from my own story that sometimes when you go into details or sometimes on the contrary, when you isolate the subject, the work you do can reach a point where you can constantly feed yourself with passion.

No matter what sector or field, continuous learning makes you successful both in social and business lives. These paths are long, but shortened by passion and knowledge, and they leave us legacies that we can extrapolate towards different topics in the future.

Technical ability, command of jargon, experience and diligence are very important and indispensable. However, I think that it is necessary to constantly improve communication techniques and social skills, as well.

For the tech side; I recommend that we should learn the current problems very well and then focus on open problems in the relevant fields. 

I don't recommend refraining from sharing information. Knowledge really increases as it is shared.

And finally, I can say that in order to succeed in the long term, one should walk the path, in which is full of experiences to be gained and knowledge to be gained. One should look around while walking that path. (I hope the analogy here is understandable.) 

One must be result-oriented, but also should achieve the results by doing what is necessary and by learning along the way.

In any case, it should be a win win. Your company will win, you will win, maybe your nation will win with your successes, but in order for this to happen, one must continue to search for answers to the question of why, in addition to the question of what. Time to time, I say all these to myself as well.

Thursday, September 7, 2023

A fun departure from the norm : I'm going on a TV show!

 The TV show we shot today is on the air in a week. Actually, it was a little different and interesting than I thought it would be, but! it was fun! A spontaneous conversation led us to interesting topics. 

Couple of days earlier from the show, I just thought a little about what I could say and what questions might be asked.  I'm concentrated (and working) on the Infrastructure, Databases, Operating Systems, Engineered System, Cloud and ERP as you may already know and that's why I thought that all the questions would probably be on these domains. 

However, the questions asked were on broader range of topics. We even talked about deep topics that are still open to interpretation (in my opinion). 

We talked about AI (Large Language Models etc..) and Quantum Computing in that short period of time. Although these aren't in my areas of expertise, I didn't leave those questions unanswered and tried to shed some lights on the topics like AI safety and Quantum Cryptography...  I tried to convey my ideas on these topics.

I understand the concerns in the questions and I hope that I relieved the concern of some people by saying AI safety against the fear of advanced artificial intelligence and I put Quantum Cryptography forward, against the fear that quantum computing will cause security vulnerabilities for all of us in the future.

Of course, time was short and we were way out of my domain. I tried to answer these with what I learned in my own practical studies and intellectual gymnastics. I enjoyed chatting about these ideas.

Sharing the announcement and the banner of the talk, and leaving some other photos from today for the memories..




Friday, August 25, 2023

Oracle ACE PRO Announcement (2023-2024) -- Expertise : Application Technology (& EBS), Database, Engineered Systems & Cloud

My continuous expedition and dissemination of knowledge and experience about Oracle, now found recognition for the 7th time, as an Oracle Ace Pro. 

My kindest gratitude's to Oracle and to Ace Community.

Erman Arslan's Oracle Forum / MAY 29 - AUG 25, 2023 - "Q & A Series"

Things are a little slow in the summer months, but we continue to answer questions as intensely as possible.

Remember, you can ask questions and get remote support using my forum. Just click on the link named "Erman Arslan's Oracle Forum is available now". Click here to ask a question", which is available on the main page of Erman Arslan's Oracle Blog. More than 2000 questions were asked, almost 10000 comments have been made and here is the links to the latest questions from the last period.

-- or just use the direct link:


Supporting the Oracle users around the world. Let's check what we have in the last few months..



OEM views by Roshan

Prepare phase failed ORA-00904: "TABTYPE": invalid identifier by satish

Cannot launche FORMS by big

4 node Rac with 4 node Ebs by syed

Telugu data is not displaying and working properly in all oaf pages by prabhunoule

cells down by Roshan

disks KVM by Roshan

FS_clone should be run two times by big

SMS notification by Roshan

locks by Roshan

Control file - concurrent program by satish

Cloning of AdminServer/Weblogic along with managed servers(WL_FORMS&WL_REPORTS) by kvmishra

Upgrade to R12.2 by satish

Redirect forms url by kvmishra

remove disk ASM by Roshan

ATG PF Upgrade by prabhunoule

dataguard on RAC by Roshan

ACFS filesystem extension by Roshan

Adaptive plans in EBS database performance by satish

Facing an issue in concurrent program after upgrade from 12.1.3 to 12.2.10 by prabhunoule

Frequente Archive Log Generation by big

disk group error by Roshan

open zfs by Roshan

Getting error while configuring node2 in ebs 12.2 by Mohammed Hamed

Bi publisher ( EBS) integration with apex by satish

monitor exadata by Roshan

Run patch analyze for secone time by big

stop start Workflow mailer by script by big

R12.2 Patch Analyze by big

ACFS filesystem hangs by Roshan

Error with host concurrent program 19c by satish


Tuesday, August 1, 2023

EBS 12.2 -- ADOP Phase=Cleanup Error -- Unable to get APPS_DDL package version from db

Today's issue is from Oracle EBS domain. I will be fast and clean and give you the error, the cause and  the solution without dancing around the subject. Okay... Let's go!

->

PROBLEM:

While performing ADOP cleanup phase, we failed with the AutoPatch error seen below..

AutoPatch error:

Unable to get APPS_DDL package version from db <XX_CUSTOM_SCHEMA> <APPS_DDL> <PACKAGE>

AutoPatch error:

adpmrp: Error while installing apps_ddl packages.

It was due to an EBS-side config of a custom schema and the the cause of the issue could be identified using the query below;

select ORACLE_USERNAME, READ_ONLY_FLAG, ENABLED_FLAG, ORACLE_ID, INSTALL_GROUP_NUM, last_update_date from FND_ORACLE_USERID where ORACLE_USERNAME in ('XX_CUSTOM_SCHEMA');

--ENABLED_FLAG and READ_ONLY_FLAG were suspicious, and in our case the problem was related with the value of the READ_ONLY_FLAG.

SOLUTION:

*Connect to the database using sqlplus and APPS user

*Take a back up of the table fnd_oracle_userid

create table fnd_oracle_userid_bkp2 as select * from fnd_oracle_userid;

*Check the values before changing for reference.

select ORACLE_USERNAME, READ_ONLY_FLAG, ENABLED_FLAG, ORACLE_ID, last_update_date from FND_ORACLE_USERID where ORACLE_USERNAME in ('XX_CUSTOM_SCHEMA');

*Use the following update, and fix the issue.

update fnd_oracle_userid set read_only_flag='A' where ORACLE_username in ('XX_CUSTOM_SCHEMA');

commit;

--In our case READ_ONLY_FLAG was set to U, and that was causing the issue.

Note the description of the values of READ_ONLY_FLAG column;

C - Category 5 - APPLSYSPUB
U - Category 5 - APPS
E - Category 5 - APPLSYS and APPS_NE
U - Category 5 - APPS variations for the obsolete Multiple Reporting Currencies feature
A - Category 6 - Oracle E-Business Suite Base Product schemas
X - Category 4 - Non-Oracle E-Business Suite schemas

*Check if the values are corrected as expected.

select ORACLE_USERNAME, READ_ONLY_FLAG, ENABLED_FLAG, ORACLE_ID, last_update_date from FND_ORACLE_USERID where ORACLE_USERNAME in ('XENP');

Re-run adop cleanup phase, and you will see the issue will be disappeared.

adop phase=cleanup

Monday, May 29, 2023

Erman Arslan's Oracle Forum / APR 29 - MAY 29, 2023 - "Q & A Series"

Remember, you can ask questions and get remote support using my forum. Just click on the link named "Erman Arslan's Oracle Forum is available now". Click here to ask a question", which is available on the main page of Erman Arslan's Oracle Blog. A total of 2165 questions were asked, almost 10000 comments have been made and here is the links to the latest questions from the last period.

-- or just use the direct link:


 Supporting the Oracle users around the world. Let's check what we have in the last month..




Apex schema in ebs database by satish

Authentication error while accessing FNDWRR.exe from browser. by Mohammed Hamed

Apache exiting with 152 by satish

SQL ID in procedure by satish

1530 ORA-24263: Certificate of the remote server does not match the target address by satish

locks onDB by Roshan

exadata monitoring tool by Roshan

prepare phase hangs by satish

Storage IOPS by satish

BI publisher in EBS by satish

Patch conflict by big

EBS R122: Context File shows apps_jdbc_connect_descriptor with VIP instead of SCAN by NubeAppsDba

mount acfs filesystem by Roshan

locks DB 19c by Roshan

patch for weblogic by big

CPU PATCH FOR APR 2023 by big

how to create a dblink from oracle to sql server by baig

Wrong version after upgrading to R12.2.10 by satish

Database monitoring by satish

Invalids after upgrade to R12.2.10 by satish

host issue performance by Roshan

Applying RUP patch 30399999 R12.2 upgrade by satish

Upgrade EBS R12.1.3 to R12.2---->Package FND_CONCURRENT has errors by satish

survey exadata by Roshan

KVM by Roshan

Upgrade EBS R12.1.3 to R12.2.10 by satish

ebs_patch or <INSTANCE>_ebs_patch services by Harin


OBIEE 12C / Weblogic -- Authentication for user weblogic denied , The specified user failed to log in. javax.security.auth.login.FailedLoginException

This is about an OBIEE 12C issue that was escalated to me today. OBIEE admin was trying to implement a custom auth method for OBIEE, and due to that, she restarted the Weblogic Services of OBIEE multiple times.. Suddenly, Admin Server started to fail. It wasn't about weblogic username and password, it wasn't about boot.properties, it wasn't about any lock files or something like that.. But! it was about the corrupted weblogic user pass information in a file (under the ldap directory of the Admin Server) located under the OBIEE Weblogic domain.

After a lot of diagnostic work, and after lots of tries, the solution for us was the following;

*Backup & rename/move/remove the ldap directory located under the directory named "/home/erm/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/servers/AdminServer/data/"

*set the domain env :
cd /home/erm/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/bin
. ./setDomainEnv.sh

*change(actually rechange) the Weblogic user's password to have a fresh and proper record in the related files:
cd /home/gtech/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/security
java weblogic.security.utils.AdminAccount weblogic weblogic123 .
--note that , "." needs to be there at the end.

*Recreate the boot.properties file

*Start the Weblogic Managed Server and that's it.

I shared the call stack, the error stack below for the record.. I mean if you feel like you are hitting the same issue, you can take a look at the following call stack and check whether the solution I have provided is suitable for you.

A MultiException has 6 exceptions.  They are:

weblogic.security.SecurityInitializationException: Authentication for user weblogic denied.
java.lang.IllegalStateException: Unable to perform operation: post construct on weblogic.security.SecurityService
java.lang.IllegalArgumentException: While attempting to resolve the dependencies of weblogic.jndi.internal.RemoteNamingService errors were found
java.lang.IllegalStateException: Unable to perform operation: resolve on weblogic.jndi.internal.RemoteNamingService
java.lang.IllegalArgumentException: While attempting to resolve the dependencies of weblogic.connector.common.ConnectorServiceActivator errors were found
java.lang.IllegalStateException: Unable to perform operation: resolve on weblogic.connector.common.ConnectorServiceActivator

at org.jvnet.hk2.internal.Collector.throwIfErrors(Collector.java:89)
at org.jvnet.hk2.internal.ClazzCreator.resolveAllDependencies(ClazzCreator.java:250)
at org.jvnet.hk2.internal.ClazzCreator.create(ClazzCreator.java:358)
at org.jvnet.hk2.internal.SystemDescriptor.create(SystemDescriptor.java:487)
at org.glassfish.hk2.runlevel.internal.AsyncRunLevelContext.findOrCreate(AsyncRunLevelContext.java:305)
Truncated. see log file for complete stacktrace
Caused By: weblogic.security.SecurityInitializationException: Authentication for user weblogic denied.
at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.doBootAuthorization(CommonSecurityServiceManagerDelegateImpl.java:1158)
at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.postInitialize(CommonSecurityServiceManagerDelegateImpl.java:1272)
at weblogic.security.service.SecurityServiceManager.postInitialize(SecurityServiceManager.java:586)
at weblogic.security.SecurityService.start(SecurityService.java:130)
at weblogic.server.AbstractServerService.postConstruct(AbstractServerService.java:76)
Truncated. see log file for complete stacktrace
Caused By: javax.security.auth.login.FailedLoginException: [Security:090938]Authentication failure: The specified user failed to log in. javax.security.auth.login.FailedLoginException: [Secu
rity:090302]Authentication Failed: User specified user denied
at com.bea.common.security.utils.ExceptionHandler.throwFailedLoginException(ExceptionHandler.java:62)
at weblogic.security.providers.authentication.LDAPAtnLoginModuleImpl.login(LDAPAtnLoginModuleImpl.java:380)
at com.bea.common.security.internal.service.LoginModuleWrapper$1.run(LoginModuleWrapper.java:117)
at java.security.AccessController.doPrivileged(Native Method)
at com.bea.common.security.internal.service.LoginModuleWrapper.login(LoginModuleWrapper.java:114
)