Monday, 29 April 2019

R12.2 ADOP Patching Phases Finalize and Cutover

Dear DBA-mates… Topic: R12.2 ADOP Phases in R12.2
As per our last discussion and promises R12.2 ADOP Phases in R12.2, we would like to share the next phases Finalize and Cutover phase which is very important and useful here to all here.

Here, we would like to share the details as how Finalize phase and cutover phase works internally which is very common for any interviewers.

Finalize Phase:
This phase is the 3rd phase in R12.2 adop phase. Please find the below details:

1. It compile invalid objects

2. It generate derived objects

3. We can run Finalize phase in ‘quick’ mode or ‘full’ mode where ‘quick’ mode is the default mode.

4. It is recommended to be done explicitly in order to reduce cutover (downtime) phase time

5. In this phase adop patching cycle can be pause as long as required, because after this finalize phase we need to run cutover where downtime required.

Syntax:
$adop phase=finalize [ finalize_mode=full ]

Note: In full mode, It collect data dictionary statistics.

Cutover phase:

This phase is the 4rd phase in R12.2 adop phase. Please find the below details how it works internally.

1. First it performs the ADOP validations on all of the application nodes

2. When we run the Cutover Phase all the users will be logged off the system including the third party applications.

3. First it will bring down the Application Services and then will start up the Application Services.

4. After the start up the Patch file system and Run file system will be switched over like patch file will be Run fs and Run fs will be Patch fs.

5. Even the Patch database edition will be promoted to be the Run database edition

6. It will terminates ‘ADZDPATCH’ concurrent program which was started in prepare phase.

Syntax:
$adop phase=cutover

Some more useful links:



Regards,

Monday, 22 April 2019

Concurrent Manager Performance Tuning Tips in Oracle EBS R12

Hi DBA-Mates, Topic: Concurrent Manager Performance Tuning Tips in Oracle EBS R12

Hope you all are doing in your DBA life… I know it is not easy but not hard also heheh… It is our task like that we have to available 24/7.

By the way, let’s move to topic Concurrent Manager Performance Tuning Tips in r12 Oracle EBS.

Concurrent Managers in EBS R12 Performance Generic Tips:

1. Cache Size:  It means the Number of Request cached. And Cache size value should be the twice of the number of Target Processes.

For example: If a Concurrent Manager’s target value is 2, then cache size should be like 4. It means CM will read 4 request at a time and try to run these 4 requests before reading any new request.
i.e. Cache Size = 2*no. of Target Process.

2. Sleep Seconds: It is the number of seconds when Concurrent Manager waits to check the list of pending Concurrent Requests.

For Example: If the Sleep Seconds value is 2 Min (max) then CM will check Pending Concurrent Requests every 2 Min.  For peak time we can set it to 30 seconds.

3. We should not enable maximum number of Custom Managers if not required. It will degrade the performance for Queue Table.

4. We should keep less sleep time for CRM manager in peak hours.

5. Reports.log files should be always truncate from Log file location $APPLCCSF/APPLLOG. Some times its sizes increases more than 2 GB which impacts performance, so we should truncate manually.

6. More number of records in FND_Concurrent Table can slow the application performance. So, we should run the Purge Concurrent Request FNDCPPUR at regular interval.

7. We should always keep eyes on below tables
  • FND_CONCURRENT_REQUESTS
  • FND_CONCURRENT_PROCESSES
  • FND_CRM_HISTORY
  • FND_ENV_CONTEXT
  • FND_TEMP_FILES
  • FND_CONFLICTS_DOMAIN
These above tables should be Defragment periodically, while CM should be down.

8. We should always check for FND_Stats for the tables.

For example: 

exec fnd_stats.gather_table_stats ('APPLSYS','FND_CONCURRENT_REQUESTS',PERCENT=>99);

Hope this may useful and helpful here.

Some more useful links:




(Ref. 1057802.1)
Regards,

Friday, 19 April 2019

TKPROF Concepts with Syntax and Real time example

Hey DBA-Mates, Topic: Tkprof Syntax with Example
We would like to share one more dba stuff Tkprof here as it is very easy and useful also… It is a very useful for any DBAs.

Tkprof is the basic performance diagnostic tool that help us to monitor and tune applications running against the oracle server.

It will be help us to know the accurate access the efficiency of the SQL statements.

TKPROF is used to format the content of the trace file and put output into readable format file.
It is also used for Determine the execution plan of SQL statements.

Below is the real time example for tkprof:

Syntax: tkprof <trace_filename> <output_format_filename> explain=apps/<apps_pwd> sort=exeela,prsela,fchela sys=yes

There are many more option but usually we use these options. It depends as per requester.

Here,
Exeela: Elapsed time spent Execution
Fchela: Elapsed time spent Fetching.
Prsela: Elapsed time spent Parsing.
SYS = Yes/No: it is used for enable or disable the list of SQL statements issued by the SYS user.

$ pwd
/pp/oracle/ORA-DATA/920/admin/ORA-DATA_hostname/udump

$ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989.trc
-rw-r--r--   1 sku   dba        278986564 Aug 12 23:17 ORA-DATA_ora_27722_ORADATA_CR695975989.trc

Command:

$tkprof <ORA-DATA_ora_27722_ORADATA_CR695975989.trc> <ORA-DATA_ora_27722_ORADATA_CR695975989.txt>  explain=apps/apps  sort=exeela,prsela,fchela sys=yes

TKPROF: Release 9.2.0.8.0 - Production on Thu Aug 13 15:39:13 2015
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

$ pwd
/pp/oracle/ORA-DATA/920/admin/ORA-DATA_hostname/udump
$ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989.txt
-rw-r--r--   1 sku   dba         162819 Aug 13 15:39 ORA-DATA_ora_27722_ORADATA_CR695975989.txt

$ $ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989*
ksh: $:  not found

$ ls -ltr ORA-DATA_ora_27722_ORADATA_CR695975989*
-rw-r--r--   1 sku   dba        278986564 Aug 12 23:17 ORA-DATA_ora_27722_ORADATA_CR695975989.trc
-rw-r--r--   1 sku   dba         162819 Aug 13 15:39 ORA-DATA_ora_27722_ORADATA_CR695975989.txt

$ pwd
/pp/oracle/ORA-DATA/920/admin/ORA-DATA_hostname/udump
$ exit
logout
$ exit

Hope this may useful and helpful here.

Some more useful links:



Regards,

Monday, 15 April 2019

Concurrent Manager Status System Hold, Fix Manager Solved

Hi DBA_Mates,
We are back again. We would like to share “Concurrent Manager status “System Hold, Fix Manager” one of the most important issue related to Concurrent Manager.

As we know how Concurrent Manager is important in our Apps DBA’s life.

Recently, we faced such an issue that suddenly Concurrent Manager was going down on Node-2 as we have PCP. The status of Concurrent Manager Shows as “System Hold, Fix Manager” and we didn’t find useful in manager’s log also.

As per our earlier this below post, we got some ideas:

Concurrent Managers are down error insert_fcp failed due to ORA-01403 no data found,ORA-06512

As per these concepts we checked the Node name in front-end and it was in small letter for the Concurrent Managers who was going down.

The Concurrent Manager on Node-1 was working fine as Node name was in CAPS entry but in Node-2 Node name was in small and it was going down suddenly.

When we bounced the application it was showing up but after some time again it was going down.

This issue starts after purging the FND tables. So, from now we should remember one thing always as whenever we do the FND table’s purge, we should run Twice Autoconfig on all nodes.

i.e. EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

EXIT;

After this we need to run AutoConfig, twice on all tiers like first DB nodes then Apps Tiers. In this also, primary node should be first and last.

Here can see the below changes if we ran once autoconfig:

Before:

Host and domain values are missing in FND_NODES for MT nodes. Please run autoconfig in all nodes to fix the issue and then check.

NODE_NAME            SERVER_ADDRESS                 HOST                 DOMAIN                                   CP  WEB DB  VIRTUAL_IP

-------------------- ------------------------------ -------------------- ---------------------------------------- --- --- --- ----------------------------------------

AUTHENTICATION       *                                                                                            N   N   N

HOSTNAME         IP:198.162.11.1                                            N   Y   N

HOSTNAME IP:198.162.11.1                                                   N   Y   N

HOSTNAME   IP:198.162.11.1                                               Y   Y   N

HOSTNAME IP:198.162.11.1                                                    Y   Y   N

HOSTNAME        hostname                            N   N   Y   hostname-vip

HOSTNAME                                     hostname   N   N   Y   hostname-vip

HOSTNAME   IP:198.162.11.1                                                   N   Y   N


After running Autoconfig 2 times on all MT nodes:

SQL> select NODE_NAME,SERVER_ADDRESS,HOST,DOMAIN,SUPPORT_CP CP,SUPPORT_WEB WEB,SUPPORT_DB DB,VIRTUAL_IP from fnd_nodes;

NODE_NAME                      SERVER_ADDRESS                 HOST                 DOMAIN                         C W D VIRTUAL_IP

------------------------------ ------------------------------ -------------------- ------------------------------ - - - -------------------------

AUTHENTICATION                 *                                                                                  N N N

HOSTNAME                 IP:198.162.11.1

                 hostname         hostname          N Y N

HOSTNAME                   IP:198.162.11.1                 hostname          N Y N

HOSTNAME               IP:198.162.11.1                 hostname          Y Y N

HOSTNAME                IP:198.162.11.1              hostname          Y Y N

HOSTNAME                                        hostname          N N Y hostname-vip

HOSTNAME                  hostname        N N Y hostname-vip

HOSTNAME                   IP:198.162.11.1           hostname          N Y N

 

8 rows selected.

 

Result: Since FND_CONC_CLONE.SETUP_CLEAN we ran, we need to run 2 cycles of autoconfig on all nodes to get the fnd_nodes populated.

When we ran the autoconfig in all MT nodes, we get populated host and domain values in FND_NODES table.

Hope this may useful and helpful.

Some more useful links:

Thursday, 11 April 2019

Why We are not Rich

Dear Friends,
I would like to share my personal experience which may be good and useful here.

We all want to do something in their life but we won’t able to do but it is not like that we can’t do it. If a person want to do anything in their life then can do it.

I will tell you one thing, why we are not able to do because we think as others but the difference is, they take actions towards their Aim or Thinking way, but we do only plan and thinking ideas.

Even I am also planning to open a company but the problem is I am only thinking… there is no action towards that aim… So, it is not possible at all…

One day just I was coming in cab from office and was thinking how I can make more money in my life… Then suddenly very simple thing strikes in my mind that “What I am doing for earning extra money?”

Then the answer was nothing… It means I can’t get any extra money. I am coming office and working so I can get that much only as regular my job money.

Result: If you want to make extra money or want to be rich like others you have to work extra and need to take steps towards to be rich…

Believe me being rich is not a big deal but the thing is you have to work towards that your ideas and thinking…
I know people may thinnking that why I am not rich because dude's I am also one of you guys only... "Mungary lal ke sapne" but believe me i have done manything which was impossible for me and have good life experiences...

It is correct if you are going to start any new things you will face a lots of challenges but once you cross that challenges you will be leader and an experienced hero...

Result: Be Ready for new challenges and try to solve it because believe me every issues have definitely Solutions 100% . . .

Hope this may useful and helpful in life.
Some more useful links:
How to Study and Prepare for Exams
How to Enable or Disable Archive log mode in oracle database 11g
ADOP Phasesin R12.2
fs_clonefailed :[UNEXPECTED] Error occurred running .pl files
Real Time Oracle Apps DBA Interview Questions 
Regards,