EBS -- Playing with Upload Limit top-down the stack - Upload File Size Limit, LimitRequestBody & SecRequestBodyLimit

In one of my Advanced Customer Support site visits, I came across an interesting issue related with the EBS file uploads.

Quickly check the Upload File Size Limit profile, as it is the only EBS side limit to the file size of an attachment. I have increased it up to a logical maximum ( A Quick lesson: Using lots of digits like 9999999 breaks the code it seems:), and retried. It just didn't help. The file upload screen recognized the new value, but the HTTP Server failed again while we were trying to upload the file. (file that I have used in my test was a PDF, and it was 350 MB in size)

It was clear that the issue was caused by some other limits configured in one (or two) technological components placed higher (or a little further on the front:) in the stack. So, I decided the check the Apache/Oracle Http Server configuration.

Two configuration parameter were visited. 

The first one was LimitRequestBody and actually it was managed with the Autoconfig (s_limitrequestbody) .. Checked the context file and saw that it was already set to unlimited (LimitRequestBody - from 0 (meaning unlimited) to 2147483647 (2GB)). It was a good try but no luck.. (as you may guess)

The second one was SecRequestBodyLimit (checked the see if it was set) set in the file named security2.conf placed in the following directory, $IAS_ORACLE_HOME/instances/EBS*/config/OHS/EBS*

security2.conf is the configuration file of the ModSecurity and it inspects the requests sent to the Web Server, which is Oracle HTTP Server in this case.

Added the following parameter in security2.conf;

SecRequestBodyLimit 1047527424

-- Note that, This parameter sets the maximum request body size ModSecurity will accept. This parameter is closely related with the file upload and it is set in bytes. (We have also SecRequestBodyNoFilesLimit parameter to play for tightening up the security , but it is another story -- not related with this case actually)

Anyways, after setting parameter, I restarted the Oracle HTTP Server, and tried to upload that big file again. This time it succeeded. The issue resolved!

Okay.. This was today's tip and it was for EBS community, because we don't find anything other than  Upload File Size Limit profile in the knowledge base. So I think this blog post may help to someone suffering from a same kind of issue.

Just a little extra info (something nice to have) -> Low heap sizes configured for Weblogic Managed Servers (especially for oacore) may also prevent us for doing these excessive stuff.. So increasing the heap size of Oacore Managed Server may be a good move -- not necessary but just in case)

RDBMS / EBS 12.2 -- too many records in OBJ$ & other dictionary objects

In one of my ACS (Advanced Custom Support) site visits; I have dealt with a performance issue. The system was an EBS 12.2 running on multiple Sparc (&Solaris) nodes , and the performance problem was faced in the database management and developer related tasks that were done using GUI tools like TOAD.

Developers were complaining due to the slow response of Toad Code Road Map, and they named the problem as "Toad Code Road Map performance issue".. However; when I analyzed it, I saw that it was a general issue related with the internal data objects, especially the OBJ$.

Following SQL (which was executed by TOAD) alone was a good representation of the problem;

SELECT DISTINCT OWNER SCHEMA1, REFERENCED_OWNER SCHEMA2 FROM SYS.DBA_DEPENDENCIES UNION SELECT DISTINCT OWNER SCHEMA1, R_OWNER SCHEMA2 FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R'

So, I started my analysis by focusing on this SQL and first tried to improve its performance without touching any data.

Tried to following;

• Checked the execution plan, saw the Full table scans there; checked all the execution plans(including the historical ones) and tried to find a better one. No luck. 
• Executed SQL Advisor for that SQL. SQL Advisor recommended a SQL Profile, but again no luck, no gains.
• Tried with parallel hint (and several other hints aligned with the context).. 4 parallel, 8 parallel, 16 parallel.. Just didn't improve the performance.
• Considered creating a Materialized View and making use of Query Rewrite capability of Oracle, but again no luck due to the related tables being internal objects.. (can't use query rewrite with the internal ones)

After all these, continued my analysis with the data and concluded that the solution was there in the data. I checked the data in OBJ$, and found several similar objects that belong to different database editions.. (More than 150000 objects multiplexed 13 times) 

Remember this was an EBS 12.2, and we had online patching and that's why we had Edition Based Redefinition (EBR) there.

It seemed that excessive patching was done in this environment and no full cleanup (& no actualize all) was executed since the beginning of those patching activities. It was a big deal, because the multiplexed objects were the results of that.

Well.. Finishing my post with the action plan that I provided to customer;

Run the following;

adop phase=prepare

adop phase=actualize_all

adop phase=finalize finalize_mode=full

adop phase=cutover

adop phase=cleanup cleanup_mode=full

And then gather the dictionary and fixed object stats. (cause the number of the rows in the related object will change)

exec dbms_stats.create_stat_table('APPS','FIXED_STATS_BCK','USERS_TBS');

exec dbms_stats.export_fixed_objects_stats(stattab=>'FIXED_STATS_BCK',statown=>'APPS');

exec dbms_stats.create_stat_table('APPS','DICT_STATS_BCK','USERS_TBS');

exec dbms_stats.EXPORT_DICTIONARY_STATS(stattab=>'DICT_STATS_BCK',statown=>'APPS');

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

That's all for today. I hope you will find it useful.

Natural Selection & Converged Databases (Oracle finds good solutions :) -- JSON & In-Memory

This is an Oracle Blog in the first place, but! I do intellectual gymnastics as much as I can and I develop or change my content in parallel with the output of these gymnastics.. There will still be pure technical posts from time to time, but most of the time; I will try write technological articles by making analogies with science. I think it will be more fun that way. 

Today's post will be about Natural Selection and Oracle Converged Database (especially about JSON and in-memory capabilities). These two topics seem so unrelated, don't they? 

Remember -> http://ermanarslan.blogspot.com/2022/08/same-thing-from-different-angles.html

The link that I shared above is the link to one of my previous blog posts and its title was "Looking at the same thing from different angles under different interpretations concluding different results -> Entropy & Information | + Cloud & On-Prem | + A little Farming" .  I think I could manage to make the connection there, and now I will try to do the same in this article.

I will start with the communication, the emergence of the optimal communication method and finding itself a solid space in our world. (reference: Arik Kershenbaum & Sean Carroll - What Aliens Will be Like) I will talk about the fact that; It is the requirements (and the environment) that makes that communication method be selected, and then I will quickly make a connection by changing the context and try to bring the same notions, same logic to the database world, and give my comments about why we should consider Oracle Converged Database and the related features of it for meeting our modern needs. So part1 will be about natural selection, part 2 will be the converged database and part 3 will be making the analogy. It is a hard one, but we will see in the execution.. We will see if I will be successful in conveying my thoughts.

Okay.. With that; lets go!

Communication is everywhere. All animals, plants communicate. Sight, hearing.. These are the products of sensing and all the communication evolves from sensing. And we speak in words because we can hear it. So, again sensing.. Sensing is what drives this process and it is the cause of the final product in a sense. In other words, we don't communicate, share our thoughts or ideas by means other than the known senses. (we don't use telepathy, we didn't.. It didn't just evolve) These are directly related with the needs actually.. What evolves, what doesn't evolve.. These are all related with the needs. For instance, we've used vision, because we used light to find food. 

Anyways, these communication channels based on sensory also depend on the physical environment. Electric field sensing is rare but it may be effective.. (we can transfer lots of information between long distances but it is expensive!) -- see -> https://en.wikipedia.org/wiki/Electric_fish -- note that; using the electric field for sensory is rare, but using it for communication is much rarer, and this fish does both of that.

Light can also be used for communication and it is a very very old ability, but! it is good for the environments where there is a transparent medium. If something is in the way, it is useless.. Imagine a wall between you and me, you used a flashlight and sent me a Morse code. Of course I wouldn't be able to receive it. Light can not go really around things at all (because of the wave length of light) and why would you just generate that Morse code? -- if I was not able see anything. (this can also be applied to the under water life, the oceans where there is no light, why would you bother generating it.)  Note that, that short wave length also makes light be very difficult to detect.

Now, sound.. Sound has a long enough wave length and it can go around the objects. It can also travel long distances and lots of information can be put in it. So, we used it to speak because of good reasons. It is a very good way for communicating in this environment.. (in the environment we live in, the world But it should also be a common way of communicating in the similar worlds where there is a dense gas atmosphere. 

Going fast, but we should be reaching the Oracle side of things really fast, right?

Well.. So far so good... We kind of made an entrance by implicitly touching the evolution.. So far; the bottom line is "natural selection finds good solutions". (remember the story of sound -- being the optimal communication method)

Okay, let's take a look at the Database World. In the last few years, we see a dramatic change in the database technologies as well.. NoSQL databases, and databases for specific purposes started to be used in the industry.. Environments and databases suitable for Machine Learning, Blockchain, Graph, Spatial, Editions, Microservices and IoT Streaming.. 

We are talking about software and data stores that enable us querying and writing high amounts of non-relational data faster. Powered with the fast caches for decreasing the latency while operating with the new and fast data interchange methods.

These good, beneficial and leading innovations brought a lot of new technologies, processes and software into our lives. For instance we deploy software to operate faster on JSON data, we cache the data separately , but we still have a big Oracle databases, Exadata platforms , Oracle RAC clusters and so forth in the backend.

This means we usually have a big package of separate software components and data stores to address different data storage needs, data manipulation and access requirements.. We also usually have an integration and data flow between these components. 

At the end of the day, we do our work in modernized way, but we also find ourselves managing those components, paying the support and consultancy for each of them and looking for skilled resources/administrators/developers who can understand the related technology and solutions, and add value to our enterprises. Not to mention that, it is a complicated architecture that comes with its own non-technical challenges.

It is still worth mentioning that, these all increase management complexity (that is already deducible from the previous paragraph)

But! remember, we usually have Oracle at the database tier right? and also some of us believe that Oracle finds good solutions. (like the natural selection does)

So why don't we just check what we have in Oracle for addressing all those modernized needs?

At this point; I will call the notion of "Converged Database" for help.

Oracle is a converged database and we have started to feel it more with the release 19C.

Of course it a big topic consisting of the following:

• PLUGGABLE Databases, that let micro services to store their data in a logically separate data containers,
• API Driven Architecture, that automatically generates REST API on top of SQLs or stored procedures,
• APEX, for generating simply low code applications,
• SHARDING for having a distributed data architecture,
• Edition Based Redefinition (EBR), Online Table Redefinition, native JSON and XML for simplifying continuous delivery that enables application enhancements,
• Automatic Machine Learning (AutoML) for enabling Machine Learning for non-experts,
• In-Memory and In-Memory Columnar formats for increased OLTP and Data Analytics performance,
• Simplified Graph Analytics, Simplified Use of Document Data (JSON, XML), Spatial Data, Block Chain Data and so forth.

So, these all are provided with the Oracle Database under the umbrella of the Converged Database notion and these just simplify the developer's work and administrator's work due to having a single converged database rather than implementing multiple fragmented databases and using distributed execution and data movement across them..

I think you already guessed how these things and the natural selection is related. But before that, I want to focus on 2 specific capabilities in these context. Storing & Modifying "JSON" data and "advanced but still in-database caching capabilities" that we have in Oracle Database, or should I say Converged Oracle Database?

Let's start with JSON.. (Note that, we even have Autonomous JSON Database (AJD) --in addition to ADW and ATP-- in the family of Autonomous Database Services in the Oracle Cloud)

We just basically insert JSON data, load JSON Document and query JSON data using SQLs in Oracle Databases. We have built-in functions to update specific parts of JSON documents and we also have built-in functions to query specific records in the JSON data that is stored in the Oracle Database. 

We have standard functions to map JSON content in a columnar view and we have all the SQL power to operate on that JSON that is exposed as columns. We can index our JSON data in the usual ways and we can even move a table with a JSON column data to the in memory column store for improving the performance of queries that are interested in that JSON data. 

We can partition a table with JSON columns in regard to the JSON value. Lots of performance enhancements for SQL/JSON processing and these are all integrated with the Oracle's in-database optimizer and that's why we can expect results in significant performance speed up for queries..

These things I just mentioned are mostly based on On-Prem deployments, but they also apply to cloud.

As we said JSON, some people may already start to think about MongoDB right?

Following is a graph that quickly represents the benchmark results that are gathered in a study that is made in-house at Oracle in August 2020. It is for Autonomous JSON Database, but it unleashes the JSON performance of Oracle Database and it does this by comparing Oracle with MongoDb Atlas.

Here is the link for the related whitepaper. -->

https://www.oracle.com/a/ocom/docs/autonomous-json-database-whitepaper.pdf

Above graph (+ whitepaper) reveals that Oracle provides 2-3 times better throughput than MongoDb can provide.

Of course, people are skeptical about such benchmarks. I respect them and sometimes I can understand them. But, what we see in the document and the graphs shared above is that, it works.. Oracle database is quite good at JSON.

Okay.. Let's take a look at the In-Memory side..

Oracle Database In-Memory (Database In-Memory) is based on a suite of features that greatly improves performance for real-time analytics and mixed workloads. The In-Memory Column Store (IM column store) is the key feature of Database In-Memory. 

(See -> https://docs.oracle.com/en/database/oracle/oracle-database/21/inmem/intro-to-in-memory-column-store.html)

The document that shared its link below; exposes the benchmark results, which realize the benefits of Oracle Database In-Memory for SAP Applications. 

Here is the link -> https://www.oracle.com/technetwork/database/in-memory/overview/benefits-of-dbim-for-sap-apps-2672504.html

Following is the conclusion for the guys who don't like to read relatively large documents.

Using the SAP BW-EML benchmark we have proved Oracle Database 12c with the Oracle Database In-Memory option outperforms SAP HANA by a factor of 2X on the same Intel processor for SAP workloads. Oracle Database 12c also shows near linear scalability as the number of database servers is increased and achieved more navigation steps per hour than SAP HANA on half the CPUs.

Well... We are talking about scalability in addition to the performance now. In this context we see that, Oracle Database In-Memory expands the scalability horizon and efficiency of Oracle Databases significantly.

A quick chart from the document above -- just for showing you the difference.

What I want to underline here is not how Exadata with In-memory can beat HANA, because these comparisons are relative and they may be falsified from some points of views.. But! my intention is to show that In-Memory option works and does what it promises.

JSON & In-Memory. These were the subjects of today's post, but these are just two important abilities/features of many, that are presented to us with the Oracle Converged Database..

As I finish my article, I would like to make a reference to the analogy used in the title of this post. Let's say "reminder of analogies". That is, natural selection is the engine that drives evolution, and in this physical world that we all live in , all the things evolve through natural selection.  

As the title of this article implicitly states, natural selection looks for good solutions, and it finds them. So, it is not hard to make a connection to our enterprise data environments and conclude that, in our enterprise environments where Oracle databases exist at the heart of all the things and processes related to data, many problems are solved with the capabilities of these databases and if that's the case it is similarly tempting to evaluate the good solutions that Oracle finds through its evolution.

EBS -- Java deployment -- Custom Page Fails -> No data found for region (xxcustom/oracle/apps/../...../......./CustomRN)

This is a quick tip emerged from a solution implemented in a real life case.

It is about customizations and I think it's worth sharing with you, because you can't get support from Oracle when your issue is related with your custom objects.

This is for EBS customers (including EBS 12.2 customers) and now I'm getting to the point quickly;

If our custom page fails to render fully or if it can't be populated with data; we usually check our deployment, and ensure it was done properly.. At first glance; the custom class deployment, or xml related stuff (like Region, blablaRN.xml) that usually come within that deployment may be looking guilty. Fair enough.. This is one of the reasons for these kinds of cases, but there are other reasons as well, and here they are;

*Deployment may be wrong.. (+customall.jar needs to be rebuilt, oacore servers need to be restarted..)

*MDS repository may be the cause.. Most of the time this is related with a bug, of course if that 's the case.. So we use jdr_util to check the custom code by executing jdr_util.printdocument and if it is there, we remove it using jdr_utils.deletedocument and then import it again..

*Those xml files may be corrupted. We check the size and contents of them. If needed, we recopy them to the apps file system (to the proper place) and import them to the MDS repository again.

**the XML file references may be wrong.. This one was the case in the recent issue that I 've dealt with.. It looks simple but it can be overlooked. In one of the recent cases; It was a missing "/" character that made our customer fail. I mean; missing the leading "/" character makes you end up with "No data found for region".. That is ; xxcustom/oracle/apps/ak/livereports/webui/CustomRN makes you fail, but /xxcustom/oracle/apps/ak/livereports/webui/CustomRN brings you success :) --> note that; your developer should fix it...

End of the story (tip of the day).. I hope you find it useful.

Oracle Database -- In-Depth Security (Preventive, Detective and Administrative)

Just a little joke before we start -> Database Vault - Don't worry, it is not your vault. :)

While thinking about database security, we need consider all the aspects.  Preventing access to data is the key, but detecting unexpected accesses and managing our secure environment should also be in our focus.

Today's post will be about Oracle Database Security.. We'll look at the Oracle products that we use in order to prevent the unauthorized access, monitor and report the access, realize our vulnerabilities (and fix them) and secure the database both in place -physically (at the backend directly on the disk + before queries ever reaching the database) and on-the-fly when queried -- logically. 

Of course, we'll shed a light on the administrative part as well, on the tools let us manage the secure environment.

Actually following picture just by itself sums up the subject quite well:), but  I will still try to explain it all. (of course briefly, but without omitting the important points..)

Let's check what we have..

Well...In the preventive side; we have Advanced Security Option, Data Masking & Subsetting and Label Security.

Audit Vault, Database Firewall, Oracle Database Vault and Oracle Database Auditing.. These are on the detective side.

And, we have Oracle Database Lifecycle Management, Oracle Key Vault and Oracle Database Vault(again) for the administrative side of things..

Okay. Let's take a closer look at each of these products to understand what we mean by Database Security, the purposes that make us place these products in customer environments, and the value we provide by implementing database security projects using these products.

Preventive

Advanced Security Option: First things first; all the functionalities we will about the visit; are engineered to work seamlessly with Oracle Exadata, Oracle Real Application Clusters, Oracle Data Guard, Oracle Golden Gate, and more.

Using advanced security option, we can encrypt data in place, in the database layer. This provides a physical security mechanism that prevents attackers from getting information even if they can bypass the database security layer and reach the bits.. Even in these cases, even if attackers get the data, they will not get any information out of it, because we encrypt it physically, and we have the ability to encrypt the data in columns, in tables or in entire tablespaces. We even have the ability to encrypt things in database exports and database backups. This is called Transparent Data Encryption(TDE) bytheway.

In addition to TDE, we have the ability to redact the data before it reaches the users. It is like blacken the sensitive data on documents. Being able to scribble the data while displaying it, is very important in terms of privacy and security. That is; we can change the data before it arrives to the clients; we change it according to the policies we define, I mean we can do it according to the things like “username”, “IP” and other session information.. 2 more things for this one -> This will be transparent to the application & this brings just a minimal negative impact on workload.

We also have the ability to encrypt all the data transit.. From and to the Oracle Database. For instance; we can encrypt the Data Guard traffic using this option. ( In those old days; I was doing it with SSL tunnels , but now no need for that.. Advanced Security brings us the opportunity to encrypt data guard traffic (redo transport traffic) in a managed way, stable and transparent to the applications.

We have a few more things under this Advanced Security Option. But the things above are the main ones, and we better get going.. We have a long way to go :) 

Data Masking and Subsetting: Using Oracle Data Masking and Subsetting, we basically discover and protect the data by masking it and subsetting it :) 

We mostly use these functionalities in the Test, Development environments. We integrate to the Development and Test environment creation processes and remove sensitive data from those instances. We have a powerful library to change the data in the  Test, Dev environments and we do it without breaking the application integrity there. We mask all the data or part of it.. All can be done according to the data security policies of the environment. There are also ready to use templates for data masking for Oracle Products such as EBS. In the data subsetting side of the things; we can create smaller data sets to save time and storage costs. We have the ability to implement a goal based subsetting.. Such as ; a goal like having a smaller table size in TEST environment. Also we have the condition based subsetting option.. A condition can be for instance; having only Europe based data in the new TEST environment.

Note that, these are in database operations; so they permanently change the data in the database. 

Label Security:

This one is based on Virtual Private Database (VPD) technology. But it requires less effort . 

Label security is easy to enable, and it work on row level. We basically display the data according to the policies. A user (or app) with rights to access sensitive data, sees sensitive data stored in a table, another user who doesn't have that; doesn't see that data, but he/or she may still see the unsensitive part of the data if we like.

Oracle Database Vault:

Oracle Database Vault provides access control even  for the privileged users, even for the administrators of the database. It can block and report those failed attempts (queries from users trying to break security) for reaching the sensitive data. It has a alert mechanism as well.. 

We have lots of solutions for the unauthorized users but what about the authorized users, right? Here, Database Vault comes into play.

Your DBA credentials is stolen? You re still safe (not that safe actually:) if Database Vault is in place. We have a very fine grained access control here.. That is; we can control the access for every single SQL.

We can also prevent unauthorized and unplanned production changes.. (for instance, a developer accidentally making changes to production).. In this context, we can also prevent the execution of DDL commands like (ALTER SYSTEM, ALTER SESSION, ALTER TABLE and so on) outside maintenance windows.

We also implement a true separation of duties in the database. Security administrators manage the security, DBAs manage backup, tuning and etc..

Detective: --> Trust but verify!

Audit Vault & Database Firewall: 

These two are generally deployed together, and that's why I've put them under the same title. But, there is one thing mention, and that is; Audit Vault & Database Firewall can be considered as detective tools, but Database Firewall can also be considered as a preventive tool.

Let's start with Audit Vault;

We may trust ourselves about our database access.. Our database may be secure enough to make us think our data is only accessed by authorized ones, however; we still need to verify this.

So, we capture all the events happened in the database and save them in a secure and centralized repository (a best practice) and we do it almost a fully managed manner.. We then get our reports about these events along with the access information if we like and take our corrective actions.

This is also a quite handy tool for collecting and analyzing of audit data.. Besides we have our audit data in a secure center. This is also a one-stop reporting approach of audit data collected from different sources. We have the opportunity to use ready-made reports populated with our audit records, and these can be created according to our wishes and we may use them for analytic purposes.

Using Audit vault, we can get audit data from non-Oracle sources as well.. (such as MS SQL, SAP Sybase, IBM DB2 for LUW and MYSQL) We can get audit data from these different sources and consolidate them all in our centralized audit repository.

Using Database Firewall, we can block, permit or substitute a SQL before it reaches the database server. It is the first line of defense and that's why we call it a Database Firewall :)

Database Firewall parses the SQL before it reaches the database, and according to the policies, it decides whether it should log, substitute or block the request. We have black lists and white lists as you may guess. There are different deployment options/modes for this one. In-line, Out-of-band, Proxy Mode & Host Monitor.. The behavior and capabilities of the firewall changes according to the deployment mode. I will not get into the details of these modes, if you are curious; just check the Oracle documentation. But I must say, Database Firewall really listens and gets the database network traffic, that's for sure :)

Administrative:

Oracle Database Lifecycle Management: In addition to the convenience it provides in things like patching and provisioning; Oracle Database Lifecycle Management evaluates the alignment of our database environments with the security standards, and checks our systems to see how close they are to the best practices. ( configuration, security, and storage best practices)

Using the functionalities delivered with Oracle Database Lifecyle Management; we can also identify, secure, manage, and track sensitive data in our data centers. Databases can be scanned periodically inspecting either object metadata or the data itself for items matching Oracle or user provided criteria and patterns. An example usage maybe: Use Database Lifecycle Management to identify sensitive data, flag them and then mask them in Test, Dev etc.. environments.. Note that; what we mean by sensitive data is; standard things like credit card numbers, social security numbers, phone number, etc. . But we can also create custom–sensitive column types using regular expressions.

Oracle Database Vault (Again!:)

Oracle Database Vault is back again, in the administrative part also :) In this category, it provides Authorization monitoring feature. It also enables us determine the privileges and roles that are actually used in the database. It also lets us easily identify unnecessary privileges (privilege analysis) (and thus we can revoke them) 

It basically enables us to implement the minimum privilege principle and minimize the security risks based on having unnecessary privileges all over the place. At the end of the day, we have secure applications and database schemas freed from unnecessary access privileges.

Oracle Key Vault (OKV):

It is our Key management solution, closely related with TDE ( Transparent Data Encryption). It provides a centralized key management mechanism which is compatible with the Industry standard. It is for us to use with Oracle Products (such as Oracle Database, Oracle RAC, GoldenGate, RMAN Backups and so on.) Key Vault stores and delivers passwords, tokens, SSH keys, certificates, wallets, and other secrets to authorized users and servers. It can be deployed as a cluster and provide high availability that way.

Set a strong password , don't remember it, just upload it to OKV. So, when you want to connect to the database using that password, get it from OKV programmatically. Don't remember those passwords, don't write them to anywhere.. You see the value of it, right?

Finally, the big picture, Oracle's Maximum Security Architecture.. We see all these instruments in orchestration in harmony ->

Okay, here is where I will stop:) Of course this is a big topic, but I tried to give you the general information that can be used in day 1 of your database In-depth security projects. It is important to mention that, there are also things that can be done in the database without using any of these.. But! these are the best practices and we need implement both in-database security hardening plus these ones.

If you are wondering about what I meant by in-database securing actions, please see the following blog post.. It is for EBS , but you will get idea. ->

 

https://ermanarslan.blogspot.com/2014/07/ebs-122-securing-ebs.html --Securing EBS

Oracle ACE Pro -- Announcement

News: Now we call it ACE Pro ! :)

This is my 6th year. Proud to be part of this family. 

Profile Link: 

https://apexapps.oracle.com/pls/apex/r/ace_program/oracle-aces/ace?ace_id=616

If you need help or guidance or support for Oracle Database, Exadata, ODA, Weblogic, PCA , RAC, OCI, E-Business Suite, Oracle HTTP Server, FMW Products , Oracle Linux, KVM, OVM and so on; don't hesitate to contact me.. You can also use my forum to ask your questions. Please subscribe and be a part of the community  ->

http://ermanarslan.blogspot.com/p/forum.html (Erman Arslan's Oracle Forum)

Looking at the same thing from different angles under different interpretations concluding different results -> Entropy & Information | + Cloud & On-Prem | + A little Farming

Entropy, Information, Cloud & On-Prem, and farming (They look unrelated to each other, don't they? :)

Same thing from different angles, different field motivations, different associations and different thoughts that come to mind... 

In today's blog post, I will try to share some interesting inference, that I made by attending some of Sean Carroll's (my favorite  theoretical physicist and philosopher ) online tutorials about Entropy.. This is what I try, right?.. Trying to get the input from different fields, and use it in different contexts. Since 2013, I have tried to generate unique content, and I hope this blog post too will be in a similar wave length.

This blog post is about an analogy to of the things that I get from  my physics research. (I 'm a physics fan -- for those who don't know it :) An analogy that concentrates on our industry. Actually, my main purpose in this post was to draw attention to the approach of different subfields to information and entropy.  But I can't help myself from thinking that the inferences that I made on this way, may also apply to our Industry( Technology, IT...) as well.. In short; what I want to highlight is; different approaches to the same notion (due to different disciplines)  may bring us a mismatch in associating two subjects together.. 

At the end of this post, I will also try make an analogy to the farming, exactly to the beekeeping :) This agricultural subject came up in our conversations with my friend and brother Barış Saltık, who is a highly respected senior computer engineer.. 

Okay, let's see what I mean by that.. Let's get into physics and communication theory quickly...

Everyone agrees; there's a close relationship between entropy and information. 

However; different people from different sub fields may think the relationship goes in opposite ways.. 

For a physicist; high entropy is associated with the low information. That is to say -> if the entropy is high; we almost don't know anything about that thing (or that system).  In a low entropy configuration, however; we say; we know a lot about that system.

This means; physicists tend to associate high entropy with low information content. (Note that; entropy is a distribution on phase space. (and information is where are you in phase space -- remember Boltzman Entropy ) --)

But! a communication theorist has a completely different notion. Actually it is all mathematically, formally the same, but  for a communication theorist (like Claude Shannon), low entropy corresponds to low information & high entropy corresponds to high information.

See my posts on entropy for getting some background knowledge on this.. (background knowledge for our context) ->

https://ermanarslan.blogspot.com/2020/05/entropy-linux-kernel-csprngs-devurandom.html https://ermanarslan.blogspot.com/2022/04/ebs-poor-performance-in-autoconfig-due.html

Well.. As you see different people from different sub fields think the relationship (between information & entropy) goes in the opposite ways. 

Thanks to Sean Carroll, we are aware of these interesting things :) -- I got all the information about what I mentioned in the paragraph above from Sean Carroll's publications.

As Sean Carroll mentions; they are both perfectly sensible ways of thinking but they are different ways of thinking depending on what you're thinking about..

Well.. I have to thing to add actually.. I know that physicists and communication theorists don't have the same reference points.. . But still yes! For example, when someone says high entropy and when we think of the first thing that comes to mind, we see that difference...

Let's check our world, I mean IT world.. Databases, Systems, Middleware, management, costs, efforts, stuff like that.. What about Cloud & On-premise. Here we are; finding a similar different thing ( or  let's say trying to find a similar thing :).. Remember analogies are always dangerous, but they are also handy :)

Anyways; think about on-prem environment. (cloud ready environment, cloud-like modern on-prem environments are exceptions to this.) If we own the data center, and if we operate in a classical way and if we manage all of the components; we almost always prefer to build (& have) uncomplicated, plain, system architecture. Because; we think it is easy to manage such an architecture, it is easy doing diagnostics there. Moreover, it is not easy to get that knowledge to implement complex systems.. That is; for instance, we have a database and we put all the files (all the files that we read during the day) there as well :) We could position a Hadoop cluster for that task right? or an object storage or a cluster filesystem and do the integration, but it requires extra know-how, effort, and cost (at least for management & ownership). 

So having a complex architecture makes someone with an on-prem point of view (again there are exceptions ->  cloud-ready environments, public cloud-like on-prem environments) think that it is not efficient and preferable. However; for a cloud architect or someone looking from the perspective of the cloud with a cloud-ready or a cloud-native mode; having a complex architecture may be preferable.

It is similar right? In this case, classical on-prem folks may associate that complexity with a not preferred architecture, and as we say, cloud folks, on the other hand; may associate complexity with a preferred architecture..

Lets focus a little more on this to help you understand what I mean. I don't want you to think I am making all these things up :)

In modern Cloud environments (like Oracle Cloud Infrastructure, or Google Cloud Platform), we have the capability to use all the components we want. This really applies all the components, software components, virtual machines, storage servers , load balancers, firewalls, docker containers, Hadoop clusters, managed ETL/ELT processes etc.. you name it.. We consume the products of the capabilities of these components and we do it in various ways. I mean, we can have dedicated resources, or we can have as-a-service solutions. Most of the time, all of these components are managed by the cloud vendors, so we don't need to care the know-how required to keep these components up and running (and up-to-date). Support, diagnostics, patching, building DR environments (and lots of other things) ... We don't need to think about these things at all, because these are just one click away -- of course once you plan your cloud architecture and deploy your cloud resources accordingly.

As you may already know, having these amenities and possibilities save us time and we use that time for improving the technical things that affect the business side of things ( talking about a a good effect of course). 

Well.. Those technical things may be; having a faster deployment process (a quicker deployment) or having a distributed infrastructure that serves users connecting from different continents with the resources in the relevant continent or a true scalable environment right? An auto-scale environment that can scale-in and scale-out automatically for instance.. -- Just think about implementing one of these ones in an on-prem environment.. Complex and hard right? However; in Cloud environments, it is easy (of course if you know what you are doing).

Of course there are other things that I mentioned before.. Loading the files in to the database all the time, really? What about managing them, what about making fast file I/O on a system that is built for database I/O? What about the data life cycle? What about using the expensive database resources for storing files you never read. What about building hadoop clusters? Kubernetes environments? Micro services? Sounds cool but hard right? What about the monitoring of all these stuff? The alert mechanisms? The high-end security? The data flow? ...

I think you get the idea.. So these are not a problem in cloud environments and that's why they are preferred there. 

Actually it is a fact that; the complex architecture that can be built by putting all these stuff (modern stuff) at work occupied by a proper orchestration (I'm talking about something close to being fully managed) is preferred (universally:)! However; the difficulty of implementing and maintaining them in pre-prem environments prevents the preference of such complex architectures in on-prem environments. 

But! as I just mentioned; the situation is different for the cloud. The benefits of complex architectures in Cloud environments can find space to show themselves there.. Complex environments where we position specialized components for exactly what is relevant, are naturally preferred because in the cloud; implementing and maintaining such complex architectures does not bring as much difficulty as on-prem. 

Of course, even in the cloud, complex architectures may bring some difficulties, but these difficulties doesn't affect the decision that much, because their benefits overwrite them...

At the end; we say the almost same thing right?  In the beginning; we said -> "Same thing from different angles, different field motivations, different associations and different thoughts that come to mind... " and at the end; we say -> "Same thing from different environments full of different realities, (again) different motivations and (again) different associations" :)

Just a note before I finish;

Being cloud ready eases the cloud migrations.. I mean, in cloud migration projects, we generally migrate the environment as-is.. After the migration, there are phases to make the environment cloud ready ( and maybe cloud-native).. These things are related with the SW stack mostly, but there are things that need to be done in the infra layer as well and all these things can be deduced from what I wrote above :) Anyways; if it is not a modernized arch, it will not get full benefit from being on cloud, because its cloud footprint won't be in that complex (and modernized) mode at day1.. 

Maybe you already noticed , maybe you didn't.. That 's I would want to point out that; this text seems impartial , but in fact, it tells you be cloud-ready and modernize your whole stack.  It whispers to you; "turn your concentration this way and start getting the benefits".. Sooner or later it will be your choice, anyways..

Before I finish, I want to make one last analogy :) It is in the context of farming - agriculture, - beekeeping.

While bees collect nectar and pollen, they always go to the same flowers at certain times. They have to develop a strategy according to the flower type in their focus. That is; they calibrate their tongue for a particular flower type. So it is difficult for them to collect pollen and nectar from other type of flowers in that process. On the other hand; another hive focuses on another flower type, and for that hive, the flower that the bees I mentioned earlier focus on, seems difficult and impractical, until that hive has to change their flower type. In fact, correct flower (easier ones) type is determined by the habits of the bees. 

However; the future of the hive is not about focusing on easy flowers in the short term. It is about being ready for adaptation according to the amount of nectarine. Of course the speed of that adaptation is also important. Good hives are those which can adapt quickly. + At the end of the day, you produce new hives from successful hives, not from the other ones :)

OCI -- Running Core Banking on Cloud (+ Weblogic Cluster-aware Auto-scale configuration)

It is already up in the cloud! We are running Core Banking applications on Oracle Cloud Infrastructure! 

Our solution is almost fully managed.  It is Weblogic cluster-aware + it provides a fully automated scale-out mechanism for both having optimized compute resources and distributing the Weblogic & Http Server-related load across the nodes (with increased fault tolerance as as result of those automatic horizontal compute node expansions). 

Well.. With this solution; the cloud infra (including the Weblogic apps servers (WLS) and Http Servers) of the core banking applications is able to scale-out and scale-in according to the load of the cloud nodes (IAS compute nodes).

In this blog post , I will try to shed a light on the enablement process; the purpose; the motivation, the challenges and the final picture of such an architecture to make you think the things that can be done on Cloud, the benefits of having a cloud-based architecture and the ability to run mission critical applications on Cloud. This is especially for Oracle Cloud.. (We are also a Google Partner and have the ability to build a similar architecture on GCP as well!)

Just for intro; the name of our Core Banking application is Symphony. 

Our purpose -> Having a high available and auto-scale Core Banking environment on Oracle Cloud Infrastructure."

Our motivation-> Leveraging the advance features of Oracle Cloud to build a managed and auto-scale banking environment that is cloud ready and easy to manage.

SCOPE OF THE WORK:

Configure OCI compute and storage resources to support the Core Banking application needs.

Migrate the whole Core Banking environment from on-PREM to OCI (IAS Compute + WLS).

Do the development that is needed for auto scale-out operations.

Do the development that is need for auto scale-in operations.

Do the functional and stability tests.

SOLUTION:

Environment is completely on OCI.

Load Balancers in the forefront.

A 2-node RAC database is in the database tier. (will be replaced by the Oracle Autonomous database soon..)

Application servers are in an instance pool that we defined and configured.

A template image is built for adding a new machine to the managed group when a scale-out operation is triggered (when the load increases, OCI trigger the scale-out, custom code does the rest)

Scale-in operations are also triggered by OCI. However, custom code and a Cloud Function run to remove the configuration of the removed node from the Weblogic Cluster.

Python and Bash scripts do the Weblogic-side of things in the case of  both scale-out and scale-in operations. (Configuring the node manager and the cluster)

ARCHITECTURE:

Having all the stack on OCI Frankfurt Region. ( I can't share our core banking architecture here due to privacy concerns, if you are interested please contact me -> erman.arslan@gtech.com.tr)

By looking at the value of the metrics (such as the CPU utilization) , OCI decides to trigger the scale-out and scale-in events and this process can be tuned according to the needs. Metric can be modified + all these operations can be monitored.

Notification mechanism is also placed for notifying the Cloud admins when a scale-out/in operation is triggered.

The auto-scale solution is fast. For instance, when a scale-out operation is triggered; a node can be added to the Weblogic configuration in almost 5 minutes.

FLOW CHART:

In the following diagram we see the general flow, the flow that enables the solution make automated decisions ( these are made by the auto-scale mechanism) for starting scale out and scale in operations. We also see the built-in monitoring mechanism of OCI that listens for the load of the nodes in the instance pool.. We see, the alarming system that is kicked in when the load level is increased or decreased.

In the left pane, we see the custom code, that adds a new machine, configures the Weblogic and starts (removes when we are scaling in) the managed servers automatically.

The Load Balancer is also integrated and automatically gets the new node to its configuration (to its backend) when we add a node into the cluster. Similarly, load balancer removes the node from its backend when we remove a node due to a scale-in operation.

AUTO-SCALE IN ACTION:

Following output shows the auto-scale side of things. 

Here, we started with 2 nodes around 7 am and then auto-scale mechanism kicked in.

As we had lack of resources, auto-scale mechanism added 2 more nodes to the config at around 11 am.

And then, around 13:15, the load decreased, so the auto-scale mechanism removed a node this time.

Note that, our custom code is integrated to this mechanism, so all the WLS configuration is altered automatically during these things happen.

ROOMS FOR IMPROVEMENT:

Wait for the active application sessions before removing the node from the configuration. (during a SCALE OUT operation)

Optimize the local max JVM count in case we may be using all the JVM processes even though we didn’t have any CPU utilization or load on the server.. (This improvement should be done by the Application Developer Team.. There are also things that should be done by Oracle Development to provide the ability to make the code wait when a scale-in or out is kicked in. )

Replace the database layer with the Oracle Autonomous Database. ( This is already on its way, coming!)

EPM -- SSL/TLS configurations -- java.io.IOException: No self-signed cert in the chain

 You may get  java.io.IOException: No self-signed cert during a proper looking SSL/TLS configuration in EPM environment.. Actually you can get this error in any FMW environment, of course if you do the same mistake, which is to use the wrong versioned orapki tool :)

As you see the error above, using orapki from the wrong directory makes you end up with the exception. In other words; if you use orapki 11.1.1.7 in a 12.* EPM environment, you end up with these types of results.

As an example, I share the following with you.. You may see the correct location of orapki (as an example), deduce its version and understand the sequence of actions (just the wallet ops actually) for a SSL enabled task , by just looking at the following...

/u01/app/oracle/product/12.2.1/oracle_common/bin/orapki wallet create -wallet /home/oracle/ERM/ -pwd complex2022 -auto_login

/u01/app/oracle/product/12.2.1/oracle_common/bin/orapki wallet import_pkcs12 -wallet /home/oracle/ERM -pkcs12file /home/oracle/ERM/SSL/erm.pfx -pkcs12pwd ermpass

/u01/app/oracle/product/12.2.1/oracle_common/bin/orapki wallet add -wallet /home/oracle/ERM/ -trusted_cert -cert "/home/oracle/ERM/SSL/Root-ERM.crt" -pwd "ermpass"

/u01/app/oracle/product/12.2.1/oracle_common/bin/orapki wallet add -wallet /home/oracle/ERM/ -trusted_cert -cert "/home/oracle/ERM/SSL/RSA_ERM_SSL.crt" -pwd "ermpass"

/u01/app/oracle/product/12.2.1/oracle_common/bin/orapki wallet display -wallet /home/oracle/ERM/

Core Banking & Oracle Products

In this post, I will try to give all the Oracle infrastructure components and products that can be positioned for fulfilling the needs in a Core Banking environment.

In a banking environment, the core banking applications (as a software suite) provide all the core banking features including all the banking products, services (loans, trade finance, treasury, payments etc..) and reporting(for basic operations and regulatory requirements)

In this kind of an application environment, which needs high performance and reliability in all the tiers;  the quick look already tells us the core infra can be addressed with Weblogic and Oracle Database.

The application can leverage Weblogic JVMs (Managed Servers) with all the sophisticated functionalities that come with them. This includes, clustering, scale out mechanism and even the auto-scale mechanism (considering the Cloud Platforms)

The database layer should be Oracle. The application can take advantages of the key abilities of Oracle Database and its products/options (Real Application Clusters/RAC, Partitioning, Diagnostics Pack, Tuning Pack, and Database Lifecycle Management) to provide an efficient and high-performance scale-out platform, that is also completely fault tolerant, traceable, fully monitored, and diagnosable. (at all levels)

The Oracle technologies and their areas of use in the context of a Core Banking Software Suite are as follows. 

Database Enterprise Edition: Applications can leverage core Enterprise Edition functionalities and database options of Oracle Database to minimize the downtime and deliver superior performance in the database layer.

Partitioning: In the database layer, partitioning can be used for increasing query performance and easing the data management operations.

Diagnostic Pack: Oracle Database Diagnostic pack can used for having automatic performance diagnostics and monitoring functionalities in the database layer. ADDM and AWR can be used for shedding lights on the complicated performance issues in complex Core Banking database environments. In conjunction with the use of Enterprise Manager; these functionalities delivered with the diagnostic pack can help addressing database performance issues and will let the database administrators discover the causal sequences to many potential causes and effects.

Tuning Pack: Oracle Database Tuning pack can used for ensuring high database query performance. Using real time SQL monitoring feature, SQL Access Advisor and SQL Tuning advisor; SQL layer can be tuned continuously as it will require less effort.. Having a optimized SQL execution makes administrators ensure they have smooth running of their core banking applications.

Real Application Clusters: Having active-active database clusters is vital for ensuring continuity in the database and being aligned with the tight SLAs of Core Banking applications. Real Application Clusters a.k.a RAC option of Oracle Database provides multi-node active-active database clusters. By using RAC option, fault tolerance(along with load balancing) is ensured. RAC is a crucial component for a having maximum availability architecture in the database layer of the Core Banking Applications.

Advanced Compression: Advanced database compression capabilities of Oracle Database can be used for improving database performance and reducing storage cost in the database layer of Symphony. Especially fast-growing database environments can leverage these advanced compression capabilities delivered with this option, to speed up their large database I/Os and minimizing their database footprint.

Database Lifecycle Management: Oracle Database Lifecycle Management pack helps Core Banking Database Administrators to provision and patch multiple Oracle Database easily. Administrators of Core Banking environments with several databases (including PROD, Reporting, TEST, UAT, DEV etc.) can take the advantage of having a managed database lifecycle to eliminate the time-consuming tasks related to patching, configuration management and ongoing change management of Oracle Database environments.

Advanced Security: Core Banking environments are highly regulated environments and most of these regulations are related with the data protection. Using advanced security features (like Transparent Data Encryption and Data Redaction) of Oracle Database, out-of-band access to Core Banking data and proliferation of the sensitive data can be prevented directly.

Database Vault: There are lots of user profiles and personas using Core Banking applications and databases. (Analysts, administrators, developers, operators and so on) Using Oracle Database Vault can be used for implementing separation of duties in the core banking database layer.. It provides a unique way for restrict access to application data by privileged users.

Weblogic Server Enterprise Edition: Core Banking environment can utilize Weblogic Server Enterprise Edition in its middleware. Weblogic server is positioned as the application server, that supports fully clustered java virtual machines on it unified and extensible platform. Weblogic supports developing, deploying, and running enterprise applications, such as Java, for on-premises and cloud.

As mentioned in the intro; Core Banking applications can leverage Weblogic JVMs(Managed Servers) with all the sophisticated functionalities that come with it. This includes, clustering, scale out mechanism and even the auto-scale mechanism (considering the Cloud Platforms)

Data Integrator Enterprise Edition: Oracle Database Integrator a.k.a ODI can be used as the ETL (Extract, Transform Load) tool for providing the data integration processes around (and in) the core banking database environments. Using ODI and its sophisticated graphical interface, all those complex processes can be built easily. Monitoring of the ETL tasks can be done at all levels, change management in ETL process can be done faster and ETL itself can be done faster by leveraging the power of the target databases. ODI provides several connectors for integrating different database and Big Data environments. If needed, it can also expose its services via APIs.

Analytics Publisher: Oracle Analytics Publisher is the reporting tool that can be used within Core Banking Applications. All the reporting activities can be done using this tool. It provides both Desktop and Web interfaces. Oracle Analytics Publisher can used for generating all kinds of reports against almost any data source.

Real Application Testing: Oracle Real Application Testing a.k.a RAT is used for ensuring database performance in the cases of a new (and big) database code deployment, data migrations, database migrations, structural change in the database, database replatforming, database upgrades (and so on.) in  Core Banking environments. It is an Oracle Database product that helps the organizations foresee the effects of those kinds of changes.. To be able to see the performance effect of such a change, all the database workload is captured from the source and then replayed in the target using RAT.. Using the sophisticated reports of the tool, all the necessary analysis can be made, and the necessary improvements can be done before the production iteration.

Golden Gate: Oracle Golden Gate a replication tool, that can replicate, filter, and transform data between databases.. It operates near real time by leveraging its unique CDC features and it is clearly the best way to replicate data between Oracle databases. Golden Gate is used in various scenarios around(and in) Core Banking environments. These scenarios include cross platform data migrations, bi-directional data replications, zero downtime database migrations, zero downtime database upgrades and so on.

Golden Gate can also be positioned in the ETL processes in the relevant contexts when real time data from the source is needed and when there are various data resources to be visited.