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…

33 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
    Replies

    1. Dear User,

      Thank you...

      Regards,
      ora-data Team

      Delete
  12. Awesome blog! Is your theme custom made or did you
    download it from somewhere? A theme like yours
    with a few simple adjustements would really make my blog shine.
    Please let me know where you got your design. With thanks

    ReplyDelete
  13. Thanks very interesting blog!

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

    ReplyDelete
  15. Yes! Finally something about cover letters.

    ReplyDelete
  16. 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
  17. 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
  18. 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
  19. Howdy just wanted to give you a brief heads up and let you know a few of
    the pictures aren't loading correctly. I'm
    not sure why but I think its a linking issue. I've tried it in two different browsers and both show the same outcome.

    ReplyDelete
  20. 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
  21. 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
  22. Very interesting subject, thanks for putting up.

    ReplyDelete
  23. 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

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