Monday 5 June 2017

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…

64 comments:

  1. I simply couldn't leave your web site prior to suggesting that I actually enjoyed the standard
    info an individual supply to your guests? Is gonna be again steadily to check out new posts

    ReplyDelete
  2. Wow, marvelous blog layout! How lpng have you been blogging for?
    you made blogging look easy. The overall
    look of your website is fantastic, let alobe
    the content!

    ReplyDelete
    Replies

    1. Dear Users,

      Thank you all for your feedback.
      Now a days busy with work as Upgrade, so not able to post.

      But I will post some more very soon.

      Regards,
      ora-data Team

      Delete
  3. Steps looks to be good and simple. Is there any Metalink note available for this?
    grant_privs.sql is spool of 2nd SQL in Step 4 ?
    create_synonyms.sql is spool of 1st SQL in Step 4 ?
    Do we need to replace appsro with ORA-DATA? in 2nd SQL in Step 4
    Can you provide easy steps to grant readonly for new objects created after this actionplan performed?
    Can you provide steps for EBS 12.1.3 ? because i see many blogs with different sql's
    Thanks in advance.

    ReplyDelete
    Replies

    1. Dear Anil,

      Sorry for delay in update.
      Thank you very much for correcting. We have updated it, Please check and let us know for any concerns.


      Regards,
      ora-data Team

      Delete
  4. When someone writes an post he/she maintains the thought of a user in his/her brain that how a user can know it.
    So that's why this paragraph is amazing. Thanks!

    ReplyDelete
  5. Usually I do not learn post on blogs, however I wish to say that this write-up very forced me to take a
    look at and do it! Your writing style has been amazed me. Thanks, very nice post.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you very much, I really was hoping this comment from someone.
      I really trying to provide learning in easy and different ways.

      Regards,
      ora-data Team

      Delete
  6. You really make it appear so easy along with your presentation but I find this topic to be actually something that I feel I might by no means understand.
    It kind of feels too complex and extremely wide for me.
    I am looking ahead for your next publish, I will attempt to get the grasp of it!

    ReplyDelete
  7. It's appropriate time to make some plans for the future and it's time
    to be happy. I have learn this put up and if I may I desire to suggest you few fascinating things or suggestions.
    Maybe you could write next articles referring to this article.

    I desire to read more things about it!

    ReplyDelete
  8. Do you mind if I quote a couple of your posts as long as I
    provide credit and sources back to your webpage? My website is in the exact same area of interest
    as yours and my visitors would really benefit from some of the
    information you present here. Please let me know if this alright with
    you. Cheers!

    ReplyDelete
  9. Hey I know this is off topic but I was wondering if you
    knew of any widgets I could add to my blog that automatically tweet my newest twitter updates.

    I've been looking for a plug-in like this for quite some time and was
    hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

    ReplyDelete
  10. I'm truly enjoying the design and layout of your site.
    It's a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often.
    Did you hire out a developer to create your theme? Fantastic work!

    ReplyDelete
    Replies

    1. Dear User,

      Sorry, I didn't hired any developer.
      Thank you for your comment.

      Regards,
      Sunil

      Delete
  11. Good post. I will be facing some of these issues as well..

    ReplyDelete
  12. Thanks very interesting blog!

    ReplyDelete
  13. I couldn't resist commenting. Very well written!

    ReplyDelete
  14. Yes! Finally something about cover letters.

    ReplyDelete
  15. I do not even know how I ended up here, but I thought this post was good.
    I do not know who you are but certainly you're going to a famous blogger if you are not already
    ;) Cheers!

    ReplyDelete
    Replies

    1. Dear User,

      Thanks a lots for your feedback and appreciations. We will try our best.

      Regards,
      ora-data Team

      Delete
  16. Its like you read my mind! You seem to know a lot about this,
    like you wrote the book in it or something. I think that you
    can do with a few pics to drive the message home a little bit, but instead of that, this is wonderful blog.
    A great read. I will definitely be back.

    ReplyDelete
  17. Nice response in return of this query with solid arguments and describing
    everything about that.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you for your appreciation.

      Regards,
      ora-data Team

      Delete
  18. Informative article, exactly what I needed.

    ReplyDelete
    Replies

    1. Dear All,

      Thank you all for your comments...
      Yes, I will make some more improvement here...

      Regards,
      ora-data Team

      Delete
  19. Hi there mates, how is the whole thing, and what
    you want to say on the topic of this post, in my view its actually
    awesome for me.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you for your comment and appreciation.

      Regards,
      ora-data Team

      Delete
  20. Very interesting subject, thanks for putting up.

    ReplyDelete
  21. Very descriptive post, I loved that a lot. Will there be a part 2?

    ReplyDelete
    Replies

    1. Dear User,

      Very soon we will make a post for R12.2 patching details which is the best but waiting for free time.
      We are damn sure your all will like that.

      Regards,
      ora-data Team

      Delete
  22. I have been exploring for a little for any
    high quality articles or weblog posts on this sort of area .
    Exploring in Yahoo I at last stumbled upon this web site.

    Reading this information So i'm satisfied to express that I have an incredibly good uncanny feeling I came upon just what I needed.
    I most undoubtedly will make sure to do not
    overlook this website and provides it a look regularly.

    ReplyDelete

  23. Dear User,

    Thank you for your feedback. We will always maintain the stuff quality.

    Regards,
    ora-data Team

    ReplyDelete
  24. I'm impressed, I must say. Rarely do I come across a blog that's both educative and entertaining, and without a doubt,
    you have hit the nail on the head. The issue is something that too few folks are speaking intelligently about.
    I'm very happy I found this during my search for something
    concerning this.

    ReplyDelete
  25. This piece of writing will help thee internet viewers for creating new webpage or even a weblog from start to end.

    ReplyDelete
  26. But unlike other SEO companies London or elsewhere, we forge long term partnerships
    for long-lasting success. Typically, it's good
    practice to maintain about a 5% usage for
    your keywords or phrases. Whether you promote yourself
    as a solo or business brand (< learn 10 ways to improve your SEO with Google+) Google+ is now a necessity.

    ReplyDelete
  27. You will draw more loyal readers to your site and keep your readers coming
    back because you are sharing the best with them. By using the data from Google Adwords, SEO company London experts can match
    keywords that they want to target and are specific. By using a service that
    can provide you will all of the various choices, you will save time and money getting online and noticed.

    ReplyDelete
  28. This gives a website web presence and it gives you a major advantage over your competition. There
    is absolutely no point in finding one of the great London Accountants only to find that they are actually too far away from your
    place of business to be accessible. Taking a look at other company's websites within a particular niche field can clue
    you in to what's being actively searched for, and what's not.

    ReplyDelete
  29. They mainly put their efforts in making space at the
    page by designing locations of everything, including
    navigation bars, search box and all. Typically, it's
    good practice to maintain about a 5% usage for your keywords or phrases.
    Many organisations are finding that they have a better outcome when they employ a service to perform
    their London web design as well as Facebook development and social media UK options.

    ReplyDelete
  30. You will draw more loyal readers to your site and keep your readers coming back because you are sharing the best with them.
    By using the data from Google Adwords, SEO company London experts can match keywords that they want to target
    and are specific. Whether you promote yourself as a solo or business brand (< learn 10 ways to improve your SEO with Google+) Google+ is now a necessity.

    ReplyDelete
  31. Some SEO companies may only offer basic professional SEO services.
    By using the data from Google Adwords, SEO company London experts
    can match keywords that they want to target and are specific.
    While it may take a little while for changes in design culture to filter out into
    the more remote regions of the UK, London because of its position as the capital city gets
    to see new concepts first, thanks to a plethora of exhibitions and shows dedicated to web design.

    ReplyDelete
  32. He forms the enjoyable and informative articles related to Web Design Company and SEO
    Company in the UK. There are always product demonstrations and distinctive displays unfold through Hamleys seven dynamic floors.
    While it may take a little while for changes in design culture to filter out into the more remote regions of the UK, London because of its position as the capital city gets to
    see new concepts first, thanks to a plethora of exhibitions and shows dedicated to web design.

    ReplyDelete
  33. Hello would you mind letting me know whixh hostring company you're using?
    I've loadded your blog in 3 different browsers and I must say
    this blog loads a lot quicker then most. Can you suggest a good internet hosting provider at a reasonable price?

    Cheers, I appeciate it!

    ReplyDelete
  34. Some SEO companies may only offer basic professional SEO services.
    There are always product demonstrations and distinctive displays unfold through Hamleys seven dynamic floors.

    You can get the contact details of the various seo companies by surfing around the globally web and create
    sure that you go through details of the training organization successfully
    before you be a part of your name for having a bright profession ahead.

    ReplyDelete
  35. Hi there, After reading this article, it
    made me look at how far I've come with regards to my degree of understanding of things.
    I get better each day, when I see great things like this.
    Keep up the good work. It's much needed. I really appreciated your thoughts.


    Keep up the great work... Enjoy!

    ReplyDelete
  36. There are lots of software companies all around the world, one of which
    is London web agency. There are always product demonstrations
    and distinctive displays unfold through Hamleys seven dynamic floors.

    While it may take a little while for changes in design culture
    to filter out into the more remote regions
    of the UK, London because of its position as the capital city gets to see
    new concepts first, thanks to a plethora of exhibitions and shows dedicated to web design.

    ReplyDelete
  37. So sites with excellent quality content become valuable to search engines.
    There is absolutely no point in finding one of the great
    London Accountants only to find that they are actually too far away from your place of business to
    be accessible. This means that all of the content that your users can see, including
    buttons, menus and clickable links will be index-able.

    ReplyDelete
  38. bookmarked!!, I really like your blog!

    ReplyDelete
  39. Some SEO companies may only offer basic professional SEO services.
    Furthermore, a keyword should never feel like it
    is forced awkwardly to fit into content. Whether you promote yourself as a solo
    or business brand (< learn 10 ways to improve your SEO with Google+) Google+ is now a necessity.

    ReplyDelete
  40. They mainly put their efforts in making space at the
    page by designing locations of everything, including
    navigation bars, search box and all. Furthermore, a keyword should never feel like it is forced awkwardly to fit
    into content. While it may take a little while for changes in design culture to filter out into the more remote regions of the UK, London because of its position as the capital
    city gets to see new concepts first, thanks
    to a plethora of exhibitions and shows dedicated to web
    design.

    ReplyDelete
  41. You will draw more loyal readers to your site and keep
    your readers coming back because you are sharing the best with
    them. It provides flexible operating here we are at the employees and one can have pretty large wage along with the appropriate spare
    time. asia expert in providing website designing,
    website development, seo services, website hosting, digital marketing services and offering professional readymade scripts without any hesitation work and these Yourneeds.

    ReplyDelete
  42. This gives a website web presence and it gives you a
    major advantage over your competition. The company can provide online businesses with the means to reach goals and achieve greatly.
    asia expert in providing website designing, website development, seo services, website hosting, digital marketing services and offering professional readymade scripts without any hesitation work and these Yourneeds.

    ReplyDelete
  43. So, additional efforts have to be undertaken to develop
    the visiting page of your website. They do their level best to make a site attractive and beautiful.
    You can get the contact details of the various seo companies by surfing around
    the globally web and create sure that you go through details of
    the training organization successfully before you be a part of your name for having a bright profession ahead.

    ReplyDelete
  44. There are lots of software companies all around the world,
    one of which is London web agency. Each of the images on your web site should really have proper detailed titles and alt tags too.
    You can get the contact details of the various seo companies by surfing around the globally web
    and create sure that you go through details of the training organization successfully before
    you be a part of your name for having a bright profession ahead.

    ReplyDelete
  45. Aw, this was an incredibly nice post. Taking a few minutes and actual effort to
    make a great article? but what can I say? I hesitate a lot and don't
    seem to get nearly anything done.

    ReplyDelete
  46. It's amazing in support of me to have a website, which is good designed for my knowledge.
    thanks admin

    ReplyDelete
  47. I'm honored to obtain a call coming from a friend as soon as
    he observed the important guidelines shared in your site.

    Studying your blog publication is a real wonderful
    experience. Many thanks for taking into consideration readers like
    me, and I wish you the best of success for a professional
    in this field.

    ReplyDelete
  48. I in addition to my buddies were actually studying the good tricks found
    on your web site and then quickly developed a horrible feeling
    I had not expressed respect to the website owner for those strategies.
    All the guys had been excited to see all of them and have without a doubt been tapping into those things.
    Many thanks for getting indeed kind and for having this sort of superb
    information most people are really wanting to be informed
    on. Our own sincere regret for not expressing gratitude to sooner.

    ReplyDelete
  49. They mainly put their efforts in making space at the page by designing locations of everything, including navigation bars, search box and all.
    Furthermore, a keyword should never feel like it is
    forced awkwardly to fit into content. You need to check that they are qualified accountants as
    many will not be.

    ReplyDelete
  50. What's up everybody, here every person is sharing these kinds of
    experience, therefore it's good to read this weblog, and I used to pay a visit this webpage everyday.

    ReplyDelete
  51. My relatives all the time say that I am killing my time here at
    web, except I know I am getting knowledge everyday by reading such nice content.

    ReplyDelete
  52. We are a gaggle of volunteers and opening a new scheme in our community.
    Your website offered us with helpful info to paintings on. You have performed a
    formidable job and our whole neighborhood might be grateful to you.

    ReplyDelete
  53. So sites with excellent quality content become valuable to search
    engines. By using the data from Google Adwords, SEO company London experts can match keywords that they want to target and are specific.

    Taking a look at other company's websites within a particular niche field can clue you
    in to what's being actively searched for, and what's not.

    ReplyDelete
  54. They mainly put their efforts in making space at the
    page by designing locations of everything, including navigation bars, search box and all.
    It provides flexible operating here we are at the
    employees and one can have pretty large wage along with the appropriate spare time.
    Whether you promote yourself as a solo or business brand (< learn 10 ways to improve your SEO with Google+) Google+ is now a necessity.

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.