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…