How to restart a failed patch in r12

Hi Friends,

Some of my friends asked questions related to adpatch. We have some scenario for adpatch failed. Please find the below details:

Suppose, our adpatch fails due to pre-requisite Patch or some other reasons. But we have already spent lots of time on patching.

So, here we have two options either apply the patch again or apply the patch from where adpatch patch is failed.

Off course, everybody likes to save time. So let’s start the below steps to save time and start the patching from where it was failed.

1. We need to shutdown the workers by below command.
$adctrl

Then select option 3 “Tell worker to shutdown/quit”

2. Now, take the backup of the FND_INSTALL_PROCESSES table

Steps:

Connect as applsys because FND_INSTALL_PROCESS is owned by applsys schema.

 

$Sqlplus applsys/<password>

create table fnd_Install_processes_back
as select * from fnd_Install_processes;

Check the both tables should have the same number of rows.

SQL>select count(*) from fnd_Install_processes_back;

1919 rows selected

SQL>select count(*) from fnd_Install_processes;
1919 rows selected

Same way take the backup of the AD_DEFERRED_JOBS table.

$sqlplus applsys/<password>

SQL>create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;

Check the both tables should have same number of rows as we checked in above commands.

Now backup the .rf9 files located at below path directory. $APPL_TOP/admin/ORA-DATA/restart directory.

Here adpatch session should have ended and the cursor should be back at the UNIX prompt.

$echo $ORACLE_SID

ORA-DATA

$ cd $APPL_TOP/admin/ORA-DATA
$ mv restart restart_back
$ mkdir restart

$ chmod 777 restart

Now we are ready to DROP the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.

$sqlplus applsys/<password>
$drop table FND_INSTALL_PROCESSES;
$drop table AD_DEFERRED_JOBS;

Now, we are ready to apply the new patch or say pre-req patch in case if you are missing the pre-req patch.
After Pre-req Patch is applied successfully.

Then restore the .rf9 files located at below location as shown above command

$ $APPL_TOP/admin/ORA-DATA/restart_back
directory.
$cd $APPL_TOP/admin/ORA-DATA
$mv restart restart_<patchnumber>
$mv restart_back restart

Again restore the both FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table owned by the APPLSYS
schema.
$sqlplus applsys/<password>

$create table fnd_Install_processes
as select * from fnd_Install_processes_back;

$create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;

Just cross-check the number of rows for both tables which should be equal from above commands.

And now Re-create the synonyms

$sqlplus apps/apps

$create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;

$create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

Now, we are ready to Start adpatch and it will resume from where it was stopped in previous sessions.

Note: If you ask me frankly, I would like to go through re-apply the patch.

Patch log file location: /u01/oracle/VIS/apps/apps_st/appl/admin/VIS/log

Some more useful links:

R12.2 Custom SchemaCreation Steps

Life from PHPDeveloper to Oracle DBA

How to compile JSPManually in R12

Important SQL Scripts

Blocking SessionScenario and Action

Hope this may useful and helpful. Please let us know for any concerns or suggestion in either comment box or contact us @ora-data.blogspot.com

Thanks,

Sharing Experiences: From PHP Developer to Oracle DBA

Hi Friends,

Today I would like to share my 1^st Company real experiences as I promised with you all in my previous post.

I am sharing these experiences because I have learnt many things and I want you people to learn from these experiences. At that time, no one was to tell or guide me. So, I don’t want you people to suffer.

When I came to Bangalore here nobody was to guide me. And there was only one thing in my mind that: I have to get job any how: Here any how means without giving money to anyone either to broker or any consultancy. Because we all know, our parents already have expenses money a lot on study and again we can’t ask money for job also.

In my earlier post: How to get job and what we should do: I have shared my experiences about getting a job.

Today also I remembered, 19^th September 2012 at evening time, I was just sitting with my friends and chatting then I got a call that I am selected. After that call, I was very Happy, hope you can feel that moment and feeling.

This same thing happened with me, and then I decided to join company.

Now what happened in company:  In starting they told as there are many projects and they will allow me on project after 2-3 months. But later on I came to know there is nothing much work. For some times, I worked for php and sql/plsql but later on no work. They start to assign work like data entry and advertisements work. It was very irritating. Usually I used to be there from 9:00 am to 9, 10 or 11:00 pm just because for good impression, but it was foolish, please and please never do like this. It is very bad things in life.

Later on I met a new my PG mate, he did not know any languages like C, C++, java, .Net etc... He only knew Oracle technology. And he was working in MNC. He explained me about Oracle; he gave me many documents for oracle. 

And then I started to learn. He guided me and helped me out that time.

Now my mind opened and I decided to learn Oracle technology, which I was inspired by my ex- company MD already because he was an oracle architect. But he told me he don’t have time to teach me. So I decided to resign from company.

Later on I joined a consultancy for Oracle learning after quitting 1st job. I learned oracle database and application in 6 month. Then I worked next 6 month on project in very little salary. But I did that project because there was learning for me. It’s true that company earned lots from me :D.

After that I joined another company with a good salary and started my carrier as Oracle Apps DBA. After 1 year again I got new opportunity in Oracle Company, which was my dream.

One thing remembers, We all everyone require good adviser from experienced person. I’m telling this because when I was in college and joined a Training institutes, they told there is no carrier in DBAs in the market because almost companies have 1 or 2 DBAs. But it was not true.

Remember: If you don’t have any Dream other people will use you to complete their dream.

Thank you Every body for joining us, Please keep in touch and like us for real time and real life experiences. And also join with us to share your real time/life experiences which useful and learning for every one of us.

Some useful links:

Job Tips

Career Tips

Oracle Database

Oracle Apps DBA

Realtime or real life Experiences

Thanks,

How to create oracle Custom Schema in R12.2

Dear Friends,

Sorry for delay in posting as I promised to post yesterday but due to some production issues, I didn’t get time and was not able to concentrate. There was a CPU Patches and our team faces issues, believe me there was almost 48 Hrs on call meeting with customer. Working DBAs can understand my situation…

For freshers all the best and welcome to our DBA’s world…

Please find the below steps for creating a custom apps schema:

Below steps are explained and applied only on EBS R12.2 and the higher version of the oracle EBS.

These are the below steps to create APPS read only custom schema for example or schema name is ORA-DATA:

Before going through this we must know the Patching Cycle in R12.2 which I have mentioned in my previous posts.

As we know there are below 5 Phases of ADOP (online patching):
1) PREPARE
2) APPLY
3) FINALIZE
4) CUTOVER
5) CLEANUP

So, let’s start and follow the below steps:
$ adop phase=prepare

2. Now connect to the patch edition and to connect it, we need to source the patch env, because by default we will be in RUN env.

$. $HOME/EBSapps.env patch

3. Now, we will connect as sysdba and create the database user to be used for apps read only schema.

For Example: ORA-DATA

$ sqlplus "/ as sysdba"
SQL > create user ORA-DATA identified by ora-data default tablespace APPS_tablespace_name;

SQL> grant connect, resource to ORA-DATA;
SQL> grant create synonym to ORA-DATA;
SQL> exit;

4. Then connect as APPS user and run the below sql commands as shown:
$ sqlplus apps/<apps_password>

SQL> spool create_synonyms.sql
SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
SQL> spool off 

SQL> spool grant_privs.sql
SQL> select 'exec AD_ZD.grant_privs select on '|| OWNER ||'.' ||OBJECT_NAME || ' to ORA-DATA;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
SQL> spool off
SQL> exit;

5. Now, run the below command grant privs script as shown below:
$ sqlplus "/as sysdba"

SQL> @grant_privs.sql
SQL> exit;

Here, we need to check two things as mentioned below:

1. If there is online patching enablement is not yet enabled then please execute below command:

SQL>exec FND_ORACLE_USER_PKG.LOAD_ROW('CUSTOM_SCHEMA', 'CUSTOM', 'INVALID', NULL, 'N', 'B');

2. And if online patching enablement has already been enabled then connect as SYSTEM and follow below command:


SQL>show user;
SYSTEM
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('ORA-DATA');

6. Now, we need to create the synonyms for ORA-DATA user as shown below.
$ sqlplus ORA-DATA /password

SQL> @create_synonyms.sql
SQL> exit;

7. Now, please complete the patching cycle as finalize, cutover and cleanup which is described in my previous posts.

Some more useful links:

R12.2 Architecture and Concepts

12c database issue and solution

How to create Database Context file

Dataguard Concepts and Setup

Adpatch patching steps in r12 with example

Job Tips

Please let us know for any concerns or suggestions, we really appreciate your comments. Put your comments either in comments box or contact us @ ora-data.blogspot.com

Thanks…