EXADATA -- EBS on EXADATA, migration, 2 tips about parallelization and the listeners + 1 fix for the scan listener registration problem

Exadata is poweful , it is said to be the World's fastest database machine and it is stable.
However, it is an like instrument (Oracle database is also like an instrument) and you need to know how to play it.

I have been doing Exadata migrations since 2012, and I was the one who first implemented EBS R12 on Exadata in Turkey. Since then, I hve migrated several EBS environment to Exadata X2, X3, X4 and X5. The migrations have become my routine. Altough I have a team, consisting of 5 people, I have always wanted to do these migrations by myself, as it was an always ivolving routine and in any new migration, there was something to learn and some optimizations to be done.
So here I am and want yo to give 2 tips regarding EBS on Exadata implementation.
Recently migrated an EBS 11i to Exadata X6. The EBS in question was a critical production system, which have lots of consumers around.
The POC and TEST was perfectly done, no issues were there at all.
Everything was running as expected and everything was configured as requested by the customer.
However, during the PROD migration, the customer wanted 2 new things.
1) They wanted to have a more optimized parallelization configuration in the EBS PROD database which reside on Exadata
2) They said that , they want Exadata to be a consolidated environment , where they will host lots of EBS databases including Test, Dev, Uat and more.

PARALLELIZATION:

So, regarding the parallelization, I have implemented auto dop. The Exadata was an X6 1/8 and the RDBMS software on it was Oracle Database 12c. So it was the time to trust Oracle a little bit so , I have implemented auto degree policy and let Oracle decide the parallel degrees. Still, I needed to limit the parallelism as I didn't want the server resource to be consumed suddenly.

So, I configured the parallel parameters for this job as follows;
--Remember it is an Exadata X6 1/8 and these setting may be changed according to your environments and needs.

SQL> show parameter parallel

parallel_degree_limit                string      CPU
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     TRUE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     480
parallel_min_percent                 integer     0
parallel_min_servers                 integer     32
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     320
parallel_threads_per_cpu             integer     1

So , in brief what I have configured and say Oracle  is,
you can have maximum 480 parallel servers. You should not allocate lots of parallel servers for a single query and you should decide the parallel degrees automatically(auto dop). Also,  you should run the parallel statements in the same node.(dont scatter the parallel servers for a query accross nodes-- this is an EBS requirement),
Also, I say; you activate parallel statement queuing feature when parallel server count become 320.
So what I actually say is , if 320 of your parallel servers are allocated, and if a new query requires 200 parallel servers, then you queue it till the 200 parallel servers are available. (320+200=520 exceeds the parallel_max_servers)

Let's explain how auto dop works a little bit, as it is the most important thing in this parallelization configuration;

When Oracle estimates the time for a query to be less than the PARALLEL_MIN_TIME_THRESHOLD, it runs the query serially.
If the estimated time is higher than the PARALEL_MIN_TIME_THRESHOLD, then the Oracle (12c) looks to CPU and IO costs and decide the parallel degree accordingly.

At this time, to prevent a single query to allocate lots of parallel processes, we set the PARALLEL_DEGREE_LIMIT.

PARALLEL_DEGREE_LIMIT by default is set to CPU. This setting means; "maximum DOP is limited by the DEFAULT DOP"

DEFAULT DOP is calculated as follows;
PARALLEL_THREADS_PER_CPU * SUM(CPU_COUNT across all cluster nodes) . For ex: 1*44=44

To get te optimized value, Oracle uses this ACTUAL DOP = MIN(IDEAL DOP, PARALLEL_DEGREE_LIMIT)

So when Oracle decides the IDEAL DOP for AUTO DOP, it uses the formula and method above, but it also uses the values stored in resource_io_calibrate$ .

Higher value for the MAX_PMBPS (maximum megabytes per second) in this table -> lower value for IDEAL DOP.

For example: the following delete-insert makes Oracle to decide a lower IDEAL DOP.
Note: wer se Max_PMBPS as 200 MBs. Because of this the IDEAL OP will be lower  and as ACTUAL DOP=  MIN(IDEAL DOP, PARALLEL_DEGREE_LIMIT), Oracle will decide on low Parallel degrees. 

delete from resource_io_calibrate$;

insert into resource_io_calibrate$ values(current_timestamp, current_timestamp, 0, 0, 200, 0, 0);

commit;

However, if we set the MAX_PMBS to a lower value, like 20 MB shown in below example, Oracle will decided a higher IDEAL DOP.

delete from resource_io_calibrate$;

insert into resource_io_calibrate$

values(current_timestamp, current_timestamp, 0, 0, 20, 0, 0);

commit;

Well, the parameters that I gave above can be changed according to your environment. I only write this blog post for acknowledgement. But you get the idea right?
The idea is ; use Auto dop, set the parallel parameters in an optimized way and limit the unnecessary parallel server allocation.
What about the MAX_PMBPS in resource_io_calibrate$? I deleted the line , as recommended Oracle, and the things continued perfectly fine. But I knew that I can have the control from resource_io_calibrate$ in case there is a need.

LISTENERS:

Regarding the consolidation, the listener configuration comes in to play. Normally, Oracle recommends having EBS listeners running from the GRID Home. However; what if I will have several EBS database in Exadata right?  Several EBS databases in the same cluster, I mean. Will I give lots of IFILE pointers from the the tnsnames,sqlnet and listener files stored in the GRID Home to the different TNS_ADMIN directories of different EBS database homes? This seems dirty right?

Here , the following approach comes to our help;

What we do is;
We create the EBS local listeners from the oracle OS user in the EBS Oracle Homes.
We execute srvctl by the oracle OS user to do that. (this is important)
Then we set the TNS_ADMIN environment variables for these listeners and their associated databases using srvctl again. We set TNS_ADMIN to the $ORACLE_HOME/network/admin directory which is associated with the related listeners.
We give IFILE pointer from those default network admin to the EBS TNS_ADMIN and we are ready to go.
This approach isolates the listeners and the tnsnames and sqlnet files.
It is hard to explain but the following output will give you a better picture about what I m talking about;

Here is what it looks like when I configured them;
Note: Please , concantrate on the listener named LISTENER_PROD ;

[root@exadb01 ~]# ps -ef|grep inh
oracle   102244      1  0 Sep11 ?        00:00:00 /u01/app/oracle/product/12.1.0.2/dbhome_2/bin/tnslsnr LISTENER_TEST70 -no_crs_notify -inherit
oracle   160117      1  0 Sep11 ?        00:02:20 /u01/app/oracle/product/12.1.0.2/dbhome_prod/bin/tnslsnr LISTENER_PROD -no_crs_notify -inherit
grid     160740      1  0 Sep11 ?        00:01:35 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
grid     160977      1  0 Sep11 ?        00:01:19 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
root     282658 280422  0 13:03 pts/1    00:00:00 grep inh
grid     339575      1  0 Sep01 ?        00:00:14 /u01/app/12.1.0.2/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit

[grid@exadb01 ~]$ srvctl config listener -l LISTENER_PROD

Name: LISTENER_PROD
Type: Database Listener
Network: 1, Owner: oracle
Home: /u01/app/oracle/product/12.1.0.2/dbhome_prod
End points: TCP:1523
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

tnsnames.ora: (also, sqlnet.ora and listener.ora have these kind of IFILES)
IFILE=/u01/app/oracle/product/12.1.0.2/dbhome_prod/network/admin/PROD1_exadb01/tnsnames.ora

[grid@exadb01 ~]$ srvctl getenv database -d PROD
PROD:
ORA_NLS10=/u01/app/oracle/product/12.1.0.2/dbhome_prod/nls/data/9idata
TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/dbhome_prod/network/admin

[grid@exadb01 ~]$ srvctl getenv listener -l LISTENER_PROD
LISTENER_PROD:
TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/dbhome_prod/network/admin

So with this configuration, every EBS listener will be running its own Oracle Home and every EBS listener will see only its own tnsnames,sqlnet and listener files.

One last thing; (it is important)

If you see your EBS database is not registering itself with the scan listener, check your tnsnames.ora stored in the EBS TNS_ADMIN directory. If your EBS is an 11i, you will probably see a TNS name entry in the form of the scan name and port (scan_name:port)... You know what... That entry is not correct. It should not be there. It is created by autoconfig, but it is a bug. So delete that record and use alter system register to make your database register itself with the scan listener. You will see that it will work.

Well, we have reached the end of this blog post. It will be a complicated one I know, but still I find it quite useful. I hope you have enjoyed reading.