Personalization for Separate Email for Oracle user ID and Password

Open User Define forms for personalization:

For Oracle User ID:

Following the below mention navigation

30 Send mail to user uopn change Oracle ID “Function” “Check In”

='begin

MAIL_FILE(''Oracle Application User ID'',''Tino-Oracle@Tinopak.com'', '${item.user.email_address.value}', null,''furqan@Tinopak.com'', ''This is to inform you that Oracle ID ('||${item.user.user_name.value}||'||chr(13)|| Password must be at least 8 character long, having alpha-numeric characters'',null,''N'', ''TEST'');

end'

Message Text:  Oracle User ID send to concern

Trigger Event: WHEN-VALIDATE-RECORD

Trigger Object: USER

Condition:  :USER.USER_PASSWORD1 IS NOT NULL AND :USER.EMAIL_ADDRESS IS NOT NULL

For Oracle Password:

40 Send mail to user uopn change Oracle Password “ Functon” “Check In”

='begin

MAIL_FILE(''Oracle Application User Password'',''Tino-Oracle@Tinopak.com'', '${item.user.email_address.value}',null,''furqan@Tinopak.com'', ''Your Oracle Application Password is '||${item.user.user_password1.value}||'.Change'',NULL, ''N'', ''TEST'');

end'

Message Text: Oracle Password send to concern

Trigger Event: WHEN-VALIDATE-RECORD

Tigger Object: USER

Condition: :USER.USER_PASSWORD1 IS NOT NULL AND :USER.EMAIL_ADDRESS IS NOT NULL

ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

Solution:

Check the page size:

#getconf PAGE_SIZE

4096

Calculate proper value for shmall:

The value of shmall should be:

Shmall=total size of the SGAs on the system/page size.

Let’s assume the size of the SGA is 16GB in the system then it would be 1024 * 1024 * 1024 * 16 / 4096 = 4194304

Change shmall in /etc/sysctl.conf

vi /etc/sysctl.conf  

kernel.shmall = 4194304

Apply the changes:

# sysctl -p 

check shmall value after change

# sysctl -A | grep shmall

Start the database

# su - oracle

# sqlplus sys as sysdba

SQL> startup

LMPH DATABASE AGEING

CREATE TABLE LMPH_DB_AGEING

(

DT                                           VARCHAR2 (40),

IP                                            VARCHAR2(20),

DBNAME                             VARCHAR2 (10),

DB_SIZE                               VARCHAR2 (20),

USE_SPC                              VARCHAR2 (20),

USE_SPCP                           VARCHAR2(20),

FREE_SPC                            VARCHAR2 (20),

FREE_SPCP                         VARCHAR2 (20),

GRO_DAY                            VARCHAR2(20),

GRO_DAYP                         VARCHAR2(20),

GRO_WEEK                        VARCHAR2(20),

GRO_WEEKP                      VARCHAR2 (20)

);

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

#=============== ANONYMOUS BLOCK DATA FATCHING ================#

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

DECLARE

V_DT                                     VARCHAR2(40);

V_IP                                      VARCHAR2(20);

V_DBNAME                        VARCHAR2(10);

V_DB_SIZE                          VARCHAR2(20);

V_USE_SPC                        VARCHAR2(20);

V_USE_SPCP                     VARCHAR2(20);

V_FREE_SPC                      VARCHAR2(20);

V_FREE_SPCP                    VARCHAR2(20);

V_GRO_DAY                      VARCHAR2(20);

V_GRO_DAYP                   VARCHAR2(20);

V_GRO_WEEK                   VARCHAR2(20);

V_GRO_WEEKP                VARCHAR2(20);

BEGIN

SELECT

----SYSTIMESTAMP INTO V_DT

SYSDATE INTO V_DT FROM DUAL;

SELECT UTL_INADDR.get_host_address INTO V_IP FROM DUAL;

SELECT

(select name from v$database) ,

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' ,

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' ,

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' ,

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB'

INTO

V_DBNAME,

V_DB_SIZE,

V_USE_SPC,

V_USE_SPCP,

V_FREE_SPC,

V_FREE_SPCP,

V_GRO_DAY,

V_GRO_DAYP,

V_GRO_WEEK,

V_GRO_WEEKP

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

DBMS_OUTPUT.PUT_LINE('==================================='||CHR(10));

DBMS_OUTPUT.PUT_LINE('DATE AND TIME........ '||V_DT);

DBMS_OUTPUT.PUT_LINE('SYSTEM IP ADD........ '||V_IP);

DBMS_OUTPUT.PUT_LINE('DATABASE NAME........ '||V_DBNAME);

DBMS_OUTPUT.PUT_LINE('DATABASE SIZE........ '||V_DB_SIZE);

DBMS_OUTPUT.PUT_LINE('USE SPACE............ '||V_USE_SPC);

DBMS_OUTPUT.PUT_LINE('USE SPACE IN %....... '||V_USE_SPCP);

DBMS_OUTPUT.PUT_LINE('FREE SPACE........... '||V_FREE_SPC);

DBMS_OUTPUT.PUT_LINE('FREE SPACE IN %...... '||V_FREE_SPCP);

DBMS_OUTPUT.PUT_LINE('GROWTH PER DAY....... '||V_GRO_DAY);

DBMS_OUTPUT.PUT_LINE('GROWH PER DAY IN %... '||V_GRO_DAYP);

DBMS_OUTPUT.PUT_LINE('GROWTH PER WEEK...... '||V_GRO_WEEK);

DBMS_OUTPUT.PUT_LINE('GROWTH PER WEEK IN %. '||V_GRO_WEEKP);

DBMS_OUTPUT.PUT_LINE('==================================='||CHR(10));

END;

/

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

 #============== ANONYMOUS BLOCK DATA INSERTION ================#

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

DECLARE

V_DT                                     VARCHAR2(40);

V_IP                                      VARCHAR2(20);

V_DBNAME                        VARCHAR2(10);

V_DB_SIZE                          VARCHAR2(20);

V_USE_SPC                        VARCHAR2(20);

V_USE_SPCP                     VARCHAR2(20);

V_FREE_SPC                      VARCHAR2(20);

V_FREE_SPCP                    VARCHAR2(20);

V_GRO_DAY                      VARCHAR2(20);

V_GRO_DAYP                   VARCHAR2(20);

V_GRO_WEEK                   VARCHAR2(20);

V_GRO_WEEKP                 VARCHAR2(20);

BEGIN

SELECT

----SYSTIMESTAMP INTO V_DT

SYSDATE INTO V_DT FROM DUAL;

SELECT UTL_INADDR.get_host_address INTO V_IP FROM DUAL;

SELECT

(select name from v$database) ,

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' ,

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' ,

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' ,

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB'

INTO V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

INSERT INTO LMPH_DB_AGEING

VALUES(V_DT,V_IP,V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP);

COMMIT;

END;

/

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

#=============== CREATE PROCEDURE FOR DATA INSERTION ==============#

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

CREATE OR REPLACE PROCEDURE LMPH_DB_AGEING_P AS

V_DT                                     VARCHAR2(40);

V_IP                                      VARCHAR2(20);

V_DBNAME                        VARCHAR2(10);

V_DB_SIZE                          VARCHAR2(20);

V_USE_SPC                        VARCHAR2(20);

V_USE_SPCP                     VARCHAR2(20);

V_FREE_SPC                      VARCHAR2(20);

V_FREE_SPCP                    VARCHAR2(20);

V_GRO_DAY                      VARCHAR2(20);

V_GRO_DAYP                   VARCHAR2(20);

V_GRO_WEEK                   VARCHAR2(20);

V_GRO_WEEKP                                VARCHAR2(20);

BEGIN

SELECT

----SYSTIMESTAMP INTO V_DT

SYSDATE INTO V_DT FROM DUAL;

SELECT UTL_INADDR.get_host_address INTO V_IP FROM DUAL;

SELECT

(select name from v$database) ,

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' ,

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' ,

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' ,

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' ,

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB'

INTO V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

INSERT INTO LMPH_DB_AGEING

VALUES(V_DT,V_IP,V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP);

COMMIT;

END;

/

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

  #================= CREATE JOB SCHEDULER FOR INSERTION =============#

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

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

  job_name                  => 'LMPH_DB_AGEING_SCH',

  job_type                  => 'STORED_PROCEDURE',

  job_action                => 'LMPH_DB_AGEING_P',

  start_date                => '06-OCT-2017 09:15:00 PM',

  repeat_interval           => 'FREQ=DAILY',

  enabled                   => TRUE

  );

  END;

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

#================== CREATE JOB RUN FOR TESTING =====================#

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

begin

  dbms_scheduler.run_job (job_name => 'LMPH_DB_AGEING_SCH');

 end;

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

#========================= END ===========================#

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

adgendbc.sh INSTE8_SETUP 1 ( R12 12.1.3 Cloning Issue )

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

                   ADX Database Utility

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

getConnectionUsingAppsJDBCConnector() -->

    APPS_JDBC_URL=''

    Trying to get connection using SID based connect descriptor

getConnection() -->

    sDbHost    : devc

    sDbDomain  : hinopak.com

    sDbPort    : 1522

    sDbSid     : DEVC

    sDbUser    : APPS

    Trying to connect using SID...

getConnectionUsingSID() -->

    JDBC URL: jdbc:oracle:thin:@devc.hinopak.com:1522:DEVC

    Exception occurred: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-01882: timezone region not found

    Trying to connect using SID as ServiceName

getConnectionUsingServiceName() -->

    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devc.hinopak.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=DEVC)))

    Exception occurred: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-01882: timezone region not found

    Trying to connect using SID as ServiceName.DomainName

getConnectionUsingServiceName() -->

    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devc.hinopak.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=DEVC.hinopak.com)))

    Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

    Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------

        Verifying connection to the Database   : Could not be stablished

        No Restore Profile file created.

Restore Profile utility ran successfully

===========================================================================

adcvmlog.xml renamed to /u01/applmgr/prodappl/inst/apps/DEVC_devc/admin/log/04051526/adcvmlog.xml.04051530

[AutoConfig Error Report]

The following report lists errors AutoConfig encountered during each

phase of its execution.  Errors are grouped by directory and phase.

The report format is:

      <filename>  <phase>  <return code where appropriate>

  [SETUP PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/applmgr/prodappl/inst/apps/DEVC_devc/admin/install

      adgendbc.sh             INSTE8_SETUP       1

AutoConfig is exiting with status 1

AutoConfig execution completed on Wed Apr  5 15:30:27 2017

Time taken for AutoConfig execution to complete : 4 mins  4 secs

Solution:

0   1-   Set the time zone parameter in user .bash_profile

 [oracle@devc bin]$ cat /home/oracle/.bash_profile

 # .bash_profile

 # Get the aliases and functions

 if [ -f ~/.bashrc ]; then

 . ~/.bashrc

 fi

 # User specific environment and startup programs

 PATH=$PATH:$HOME/bin

 export TZ=/usr/share/zoneinfo/Asia/Karachi

 export PATH

 [oracle@devc bin]$

02-   Run Adconfig.sh on both first Database and second Application

 For Database:

 [oracle@devc bin]$ cd /u02/oracle/proddb/11.2.0/appsutil/bin/

 [oracle@devc bin]$ pwd

 /u02/oracle/proddb/11.2.0/appsutil/bin

 [oracle@devc bin]$

 [oracle@devc bin]$ ./adconfig.sh

 Enter the full path to the Context file:

 /u02/oracle/proddb/11.2.0/appsutil/DEVC_devc.xml

 Enter the APPS user password:

 For Application:

 [oracle@devc bin]$ pwd

 /u01/applmgr/prodappl/apps/apps_st/appl/ad/12.0.0/bin

 [oracle@devc bin]$

 [oracle@devc bin]$ ./adconfig.sh 

 Enter the full path to the Context file:   /u01/applmgr/prodappl/inst/apps/DEVC_devc/appl/admin/DEVC_devc.xml

 Enter the APPS user password:

How to Change the Default Logo for R12 (12.1.x) ?

Step – 1: Create custom image gif/jpeg

Step – 2: Copy custom logo abc.gif to $OA_MEDIA

Step – 3: Check & update Profile Option name “FND_CORPORATE_BRANDING_IMAGE” to point to aaa.gif

Step – 4: Backup existing image files under $OA_MEDIA
mv FNDSSCORP.gif FNDSSCORP.gif.old

Step – 5: Replace the default logo with custom image
cd $OA_MEDIA
cp abc.gif FNDSSCORP.gif

Step – 6: Access the login page, you should be able to see the custom image on top left.

After Copy of Custom Logo through Backend

UPDATE FND_PROFILE_OPTION_VALUES set profile_option_value='abc.gif'

where profile_option_id=9532 and level_id=10001;

How to find Oracle EBS R12 file version in Linux/Unix

[applmgr@drapp sql]$ pwd
/u01/applmgr/prodappl/apps/apps_st/appl/mrp/12.0.0/sql
[applmgr@drapp sql]$ ls
MRPAUREL.sql  MRPNLINS.sql  mrppgupd.sql  MRPSPMRP.sql
[applmgr@drapp sql]$ strings -a mrppgupd.sql | grep '$Header'
REM $Header: mrppgupd.sql 120.0 2005/05/25 03:39:47 appldev noship $
[applmgr@drapp sql]$

Getting The DDL in Oracle EBS R12 through Query

SQL> select object_type,count(*) from dba_objects group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              25
INDEX PARTITION           2343
TABLE SUBPARTITION         956
EDITION                      1
SEQUENCE                 10882
QUEUE                      180
TABLE PARTITION           2983
JAVA DATA                  323
RULE                        26
SCHEDULE                     3
PROCEDURE                  161
OPERATOR                    46
LOB PARTITION               52
WINDOW                       9
SCHEDULER GROUP              4
DESTINATION                  2
DATABASE LINK                6
LOB                       2345
PACKAGE                  47758
PACKAGE BODY             46664
LIBRARY                    204
RULE SET                   110
PROGRAM                     28
INDEX SUBPARTITION         924
JAVA RESOURCE             1019
TYPE BODY                  321
CONTEXT                     34
XML SCHEMA                  97
TRIGGER                   4186
MATERIALIZED VIEW          723
DIRECTORY                   12
JOB CLASS                   15
UNDEFINED                   11
INDEX                    51197
TABLE                    29661
SYNONYM                  78008
VIEW                     31007
FUNCTION                   364
JAVA CLASS               30005
INDEXTYPE                    9
JAVA SOURCE                  2
CLUSTER                     10
TYPE                      4713
RESOURCE PLAN               11
EVALUATION CONTEXT          44
JOB                         14
LOB SUBPARTITION            96

47 rows selected.

Step-01 ( Script for Procedure )

PROCEDURE
--------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='PROCEDURE'
order by owner;

Step-02 ( Script For Function)

FUNCTION
---------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='FUNCTION'
order by owner;

select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='PACKAGE BODY'
order by owner;

Step-03 ( Script For Package)

PACKAGE
---------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='PACKAGE'
order by owner;

Step-04 ( Script For Package Body)

PACKAGE BODY
---------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='PACKAGE BODY'
order by owner;

Step-04 ( Script For Trigger)

TRIGGER
---------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='TRIGGER'
order by owner;

Step-04 ( Script For Table)

TABLE
---------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='TABLE'
order by owner;

Step-04 ( Script For TABLE SUBPARTITION)

TABLE SUBPARTITION
---------------------
select 'select dbms_metadata.get_ddl('||''''||object_type||''''||','||''''||object_name||''''||','||''''||owner||''''||')'||' from dual ;'
from dba_objects where object_type='TABLE SUBPARTITION'
order by owner;


-----------------------------------------------------------------------------------
          ================= :Example: ===============
-----------------------------------------------------------------------------------

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;

How to Get Oracle EBS R12 Release

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

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

12.1.3

SQL>

Oracle EBS R12 Password decryption Query

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

Indexes Rebuild in 10g/11g/12C

SQL> select index_name from dba_indexes where owner=upper('hr');

INDEX_NAME
------------------------------
LOC_CITY_IX
LOC_ID_PK
REG_ID_PK
COUNTRY_C_ID_PK
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

19 rows selected.


Use this following script to generate Index Rebuild script for your Schema.


SQL> select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'||chr(10)||
from dba_indexes where owner=upper('hr');

---- user_indexes
----  dba_indexes
----  all_indexes
---- set autotrace on explain ( Execution plan )

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINENOLOGGING;'
---------------------------------------------------------------------------------------------------
alter index HR.LOC_CITY_IX rebuild online nologging;
alter index HR.LOC_ID_PK rebuild online nologging;
alter index HR.REG_ID_PK rebuild online nologging;
alter index HR.COUNTRY_C_ID_PK rebuild online nologging;
alter index HR.LOC_STATE_PROVINCE_IX rebuild online nologging;
alter index HR.LOC_COUNTRY_IX rebuild online nologging;
alter index HR.DEPT_ID_PK rebuild online nologging;
alter index HR.DEPT_LOCATION_IX rebuild online nologging;
alter index HR.JOB_ID_PK rebuild online nologging;
alter index HR.EMP_EMAIL_UK rebuild online nologging;
alter index HR.EMP_EMP_ID_PK rebuild online nologging;
alter index HR.EMP_DEPARTMENT_IX rebuild online nologging;
alter index HR.EMP_JOB_IX rebuild online nologging;
alter index HR.EMP_MANAGER_IX rebuild online nologging;
alter index HR.EMP_NAME_IX rebuild online nologging;
alter index HR.JHIST_EMP_ID_ST_DATE_PK rebuild online nologging;
alter index HR.JHIST_JOB_IX rebuild online nologging;
alter index HR.JHIST_EMPLOYEE_IX rebuild online nologging;
alter index HR.JHIST_DEPARTMENT_IX rebuild online nologging;

19 rows selected.

Finally, execute above output to rebuild your all Indexes.


SQL> alter index HR.LOC_CITY_IX rebuild online nologging;

Index altered.

SQL> alter index HR.LOC_ID_PK rebuild online nologging;

Index altered.

SQL> alter index HR.REG_ID_PK rebuild online nologging;

Index altered.

How To Restore RMAN Disk backups of RAC to RAC Database. And configuration Application on Multitier

RAC to RAC Cloning

Terminologies used

Source database:

Database whose clone will be created (AWGPROD).

Target database:

Database on which clone of source will be made (QTA).

Requirement on source database:

Before take Cold Rman backup make sure adpreclone.pl has been run successfully on apps and db tier as

$ perl adpreclone.pl dbTier 

$ perl adpreclone.pl appsTier

Appspasswd

apps768prelive

               

There must be a latest cold backup of source database (preferably) of level-0 including “controlfile”.

Backup directory path set to: /PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13

·         Down both apps tier services cleanly on source as below

Sh adstpall.sh apps/***

·         Down both cluster db services on source as below

Srvctl stop database –d erpdb

It is assumed that source database is running in archive log mode and we will take cold backup to avoid db recovery after restoration of database.

Note: We will take normal backup instead of encrypted backup to avoid wallet/encryption related issues while restoration on target database.

 

###########RMAN Backup Script for database ERPDB  ################################

ORA_ENVFILE="/d01/app/erpdb/product/11.2.0/dbhome_1/erpdb1_AWGdb.env"

. $ORA_ENVFILE

rman target / log=/PRELIV EBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/rmanbkplog_`date +%d%m%Y`.log <<EOF

configure retention policy to redundancy 2;

CONFIGURE ENCRYPTION FOR DATABASE OFF;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/%d%F.ctl';

configure device type disk parallelism 32 backup type to compressed backupset;

configure channel device type disk format '/PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/rmancold_bkp_%T_%d_%s_%U.bak';

sql "create pfile=''/PRELIVEBKP/PRELIVEBKP/RMANPRELIVEBKP/rmancoldbkp29apr13/pfile`date +%d%m%Y`.ora'' from spfile";

backup as compressed backupset database;

exit

EOF

 

Now we will move backup from source to target machine (on 1^st db node where we will perform restoration) as below.

Scp –r rmancoldbkp29apr13 oracle@101.73.65.117: /backupebsdb/

After successfully backup moved, start apps and db services on source application for user.

Requirement on target database

The target database machine must have sufficient disk space for the restoration of the backup sets being used.

Steps involved

Restore backup

The backup of source will be restored on target machine. This backup includes all datafiles and controlfiles.

Perform the following steps in the given sequence:

Step 1

Before doing anything we will take backup of current pfile on db1, create pfile from spfile and remove controlfile, onlineredologs, tempfiles, datafile from ‘+EBSDATA/AWGprod’ as follows

bash-3.2$ mv initAWGprod1.ora initAWGprod1.ora.bkp.30apr2013

bash-3.2$ mv initAWGprod.ora initAWGprod.ora.bkp.30apr2013

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 30 14:41:13 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

SQL> create pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora' from spfile='+EBSDATA/spfileAWGprod.ora’;

File created.

SQL> shut immediate

Set GRID HOME env to remove files from ASM diskgroup.

bash-3.2$ export ORACLE_HOME=/d01/app/11.2.0/grid

bash-3.2$ export ORACLE_SID=+ASM1

bash-3.2$ export PATH=$PATH:$ORACLE_HOME/bin

ASMCMD> pwd

+EBSDATA/AWGPROD

ASMCMD> ls

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

ASMCMD> rm –f DATAFILE/

ASMCMD> rm –f CONTROLFILE /

ASMCMD> rm –f ONLINELOG /

ASMCMD> rm –f TEMPFILE /

Remove all datafiles,controlfile,tempfiles,onlinelog from above mentioned folders to avoid and overwrite or space issue while restoration.

Edit newly created pfile (from spfile) and change db_name parameter from ‘AWGprod’ to ‘erpdb’ as we haveto restore backup of erpdb.

bash-3.2$ vi initAWGprod1.ora

From

*.db_name=’erpdb’

To

*.db_name=’AWGprod’

Step 2

Restore control file on target machine.

Open an RMAN prompt:

Start database in nomount state

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 30 14:41:13 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora';

$ rman target / nocatalog

RMAN> restore controlfile from '/backupebsdb/rmancoldbkp29apr13/ERPDBc-3075940697-20130429-07.ctl';

Starting restore at 30-APR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2647 instance=AWGprod1 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+EBSDATA/erpdb/controlfile/current.322.814120777

Finished restore at 30-APR-13

Note: new controlfile create in +EBSDATA/erpdb/controlfile with their db_name.

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> exit

Now database is in mount mode.

Step 3

Register the backup piece(s) in control file according to target machine locations.

RMAN> catalog start with  '/backupebsdb/rmancoldbkp29apr13';

using target database control file instead of recovery catalog

searching for all files that match the pattern /backupebsdb/rmancoldbkp29apr13

Step 4

Perform restoration with below script.

$ORACLE_HOME/bin/rman target / nocatalog <<EOF

run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

set newname for datafile 1 to '+EBSDATA/erpdb/datafile/system01.dbf';

set newname for datafile 2 to '+EBSDATA/erpdb/datafile/system02.dbf';

set newname for datafile 3 to '+EBSDATA/erpdb/datafile/system03.dbf';

set newname for datafile 4 to '+EBSDATA/erpdb/datafile/system04.dbf';

set newname for datafile 5 to '+EBSDATA/erpdb/datafile/system05.dbf';

set newname for datafile 6 to '+EBSDATA/erpdb/datafile/system06.dbf';

set newname for datafile 7 to '+EBSDATA/erpdb/datafile/system07.dbf';

set newname for datafile 8 to '+EBSDATA/erpdb/datafile/system08.dbf';

set newname for datafile 9 to '+EBSDATA/erpdb/datafile/system09.dbf';

set newname for datafile 10 to '+EBSDATA/erpdb/datafile/system10.dbf';

set newname for datafile 11 to '+EBSDATA/erpdb/datafile/system11.dbf';

set newname for datafile 12 to '+EBSDATA/erpdb/datafile/undo01.dbf';

set newname for datafile 13 to '+EBSDATA/erpdb/datafile/a_archive01.dbf';

set newname for datafile 14 to '+EBSDATA/erpdb/datafile/a_int01.dbf';

set newname for datafile 15 to '+EBSDATA/erpdb/datafile/a_media01.dbf';

set newname for datafile 16 to '+EBSDATA/erpdb/datafile/a_nolog01.dbf';

set newname for datafile 17 to '+EBSDATA/erpdb/datafile/a_queue01.dbf';

set newname for datafile 18 to '+EBSDATA/erpdb/datafile/a_queue02.dbf';

set newname for datafile 19 to '+EBSDATA/erpdb/datafile/a_ref01.dbf';

set newname for datafile 20 to '+EBSDATA/erpdb/datafile/a_ref02.dbf';

set newname for datafile 21 to '+EBSDATA/erpdb/datafile/a_summ01.dbf';

set newname for datafile 22 to '+EBSDATA/erpdb/datafile/a_txn_data01.dbf';

set newname for datafile 23 to '+EBSDATA/erpdb/datafile/a_txn_data02.dbf';

set newname for datafile 24 to '+EBSDATA/erpdb/datafile/a_txn_data03.dbf';

set newname for datafile 25 to '+EBSDATA/erpdb/datafile/a_txn_ind01.dbf';

set newname for datafile 26 to '+EBSDATA/erpdb/datafile/a_txn_ind02.dbf';

set newname for datafile 27 to '+EBSDATA/erpdb/datafile/a_txn_ind03.dbf';

set newname for datafile 28 to '+EBSDATA/erpdb/datafile/a_txn_ind04.dbf';

set newname for datafile 29 to '+EBSDATA/erpdb/datafile/a_txn_ind05.dbf';

set newname for datafile 30 to '+EBSDATA/erpdb/datafile/ctxd01.dbf';

set newname for datafile 31 to '+EBSDATA/erpdb/datafile/odm.dbf';

set newname for datafile 32 to '+EBSDATA/erpdb/datafile/olap.dbf';

set newname for datafile 33 to '+EBSDATA/erpdb/datafile/owad01.dbf';

set newname for datafile 34 to '+EBSDATA/erpdb/datafile/portal01.dbf';

set newname for datafile 35 to '+EBSDATA/erpdb/datafile/sysaux01.dbf';

set newname for datafile 36 to '+EBSDATA/erpdb/datafile/apps_ts_tools01.dbf';

set newname for datafile 37 to '+EBSDATA/erpdb/datafile/interim.dbf';

set newname for datafile 38 to '+EBSDATA/erpdb/datafile/a_txn_data4.dbf';

set newname for datafile 39 to '+EBSDATA/erpdb/datafile/a_txn_data05.dbf';

set newname for datafile 40 to '+EBSDATA/erpdb/datafile/undo02.dbf';

set newname for datafile 41 to '+EBSDATA/erpdb/datafile/c_xxca1.dbf';

set newname for datafile 42 to '+EBSDATA/erpdb/datafile/sysaux02.dbf';

set newname for datafile 43 to '+EBSDATA/erpdb/datafile/a_txn_data06.dbf';

set newname for datafile 44 to '+EBSDATA/erpdb/datafile/undo03.dbf';

set newname for datafile 45 to '+EBSDATA/erpdb/datafile/undo04.dbf';

set newname for datafile 46 to '+EBSDATA/erpdb/datafile/a_ref03.dbf';

set newname for datafile 47 to '+EBSDATA/erpdb/datafile/a_summ02.dbf';

set newname for datafile 48 to '+EBSDATA/erpdb/datafile/system12.dbf';

set newname for datafile 49 to '+EBSDATA/erpdb/datafile/c_xxca2.dbf';

set newname for datafile 50 to '+EBSDATA/erpdb/datafile/a_txn_data07.dbf';

set newname for datafile 51 to '+EBSDATA/erpdb/datafile/undo05.dbf';

set newname for datafile 52 to '+EBSDATA/erpdb/datafile/a_ref04.dbf';

set newname for datafile 53 to '+EBSDATA/erpdb/datafile/a_txn_data08.dbf';

set newname for datafile 54 to '+EBSDATA/erpdb/datafile/a_txn_data09.dbf';

set newname for datafile 55 to '+EBSDATA/erpdb/datafile/a_txn_data10.dbf';

set newname for datafile 56 to '+EBSDATA/erpdb/datafile/a_txn_ind06.dbf';

set newname for datafile 57 to '+EBSDATA/erpdb/datafile/o1_mf_undotbs2_83kyylyp_.dbf';

set newname for datafile 58 to '+EBSDATA/erpdb/datafile/sysaux03.dbf';

set newname for datafile 59 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85gcky4s_.dbf';

set newname for datafile 60 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85j1mhyw_.dbf';

set newname for datafile 61 to '+EBSDATA/erpdb/datafile/o1_mf_sysaux_85ob61g6_.dbf';

set newname for datafile 62 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb62r7_.dbf';

set newname for datafile 63 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb99r0_.dbf';

set newname for datafile 64 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__884bxzjg_.dbf';

set newname for datafile 65 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88dj97b0_.dbf';

set newname for datafile 66 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djh3nr_.dbf';

set newname for datafile 67 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djl1bx_.dbf';

set newname for datafile 68 to '+EBSDATA/erpdb/datafile/o1_mf_system_88djqht8_.dbf';

set newname for datafile 69 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6xklr_.dbf';

set newname for datafile 70 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6ywnw_.dbf';

set newname for datafile 71 to '+EBSDATA/erpdb/datafile/o1_mf_system_88h70cwk_.dbf';

set newname for datafile 72 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h72gqb_.dbf';

set newname for datafile 73 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h74wfs_.dbf';

set newname for datafile 74 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wc4jy8_.dbf';

set newname for datafile 75 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89ws999m_.dbf';

set newname for datafile 76 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wsckx1_.dbf';

set newname for datafile 77 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byhjwgf_.dbf';

set newname for datafile 78 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byho4lm_.dbf';

set newname for datafile 79 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy4xs9p_.dbf';

set newname for datafile 80 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy51r6s_.dbf';

set newname for datafile 81 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy56y3y_.dbf';

set newname for datafile 82 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5b79or_.dbf';

set newname for datafile 83 to '+EBSDATA/erpdb/datafile/o1_mf_custom_x_8f5b9ys7_.dbf';

set newname for datafile 84 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5bd39h_.dbf';

set newname for datafile 85 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61ggor_.dbf';

set newname for datafile 86 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61klp7_.dbf';

set newname for datafile 87 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dojpl_.dbf';

set newname for datafile 88 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dv8j0_.dbf';

set newname for datafile 89 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f1mr2_.dbf';

set newname for datafile 90 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f5ljy_.dbf';

set newname for datafile 91 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fbo9s_.dbf';

set newname for datafile 92 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fghfv_.dbf';

set newname for datafile 93 to '+EBSDATA/erpdb/datafile/o1_mf_system_8h7fm0kn_.dbf';

set newname for datafile 94 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf72xfo_.dbf';

set newname for datafile 95 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf74fgk_.dbf';

set newname for datafile 96 to '+EBSDATA/erpdb/datafile/undo06.dbf';

set newname for datafile 97 to '+EBSDATA/erpdb/datafile/o1_mf_sysaux_8mm63h3m_.dbf';

set newname for datafile 98 to '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8nhs6461_.dbf';

set newname for tempfile 1 to '+EBSDATA/erpdb/datafile/o1_mf_temp1_83mqxz18_.tmp';

set newname for tempfile 2 to '+EBSDATA/erpdb/datafile/o1_mf_temp2_83pbmk77_.tmp';

set newname for tempfile 3 to '+EBSDATA/erpdb/datafile/temp01.dbf';

set newname for tempfile 4 to '+EBSDATA/erpdb/datafile/temp02.dbf';

set newname for tempfile 5 to '+EBSDATA/erpdb/datafile/o1_mf_temp2_840mj9oj_.tmp';

set newname for tempfile 6 to '+EBSDATA/erpdb/datafile/o1_mf_temp1_85ftf43q_.tmp';

set newname for tempfile 7 to '+EBSDATA/erpdb/datafile/o1_mf_temp2_85ftg5mh_.tmp';

restore database;

switch datafile all;

switch tempfile all;

release channel c1;

release channel c2;

release channel c3;

}

EOF

Step 6

SQL> alter database backup controlfile to trace as ‘/d01/app/oracle/cntrol.sql’;

Database altered.

Edit cntrol.sql file as the following with adding online redo logs/groups. 

CREATE CONTROLFILE SET DATABASE "AWGPROD" RESETLOGS
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 20157
LOGFILE
GROUP 1 '+EBSDATA/erpdb/onlinelog/group_1.318.792864661'  SIZE 1024M BLOCKSIZE 512,
GROUP 2 '+EBSDATA/erpdb/onlinelog/group_2.319.792864669'  SIZE 1024M BLOCKSIZE 512,
GROUP 3 '+EBSDATA/erpdb/onlinelog/group_3.316.792864679'  SIZE 1024M BLOCKSIZE 512,
GROUP 4 '+EBSDATA/erpdb/onlinelog/group_4.317.792864687'  SIZE 1024M BLOCKSIZE 512,
GROUP 5 '+EBSDATA/erpdb/onlinelog/group_5.324.793822901'  SIZE 1024M BLOCKSIZE 512,
GROUP 6 '+EBSDATA/erpdb/onlinelog/group_6.325.793822949'  SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+EBSDATA/erpdb/datafile/system01.dbf',
  '+EBSDATA/erpdb/datafile/system02.dbf',
  '+EBSDATA/erpdb/datafile/system03.dbf',
  '+EBSDATA/erpdb/datafile/system04.dbf',
  '+EBSDATA/erpdb/datafile/system05.dbf',
  '+EBSDATA/erpdb/datafile/system06.dbf',
  '+EBSDATA/erpdb/datafile/system07.dbf',
  '+EBSDATA/erpdb/datafile/system08.dbf',
  '+EBSDATA/erpdb/datafile/system09.dbf',
  '+EBSDATA/erpdb/datafile/system10.dbf',
  '+EBSDATA/erpdb/datafile/system11.dbf',
  '+EBSDATA/erpdb/datafile/undo01.dbf',
  '+EBSDATA/erpdb/datafile/a_archive01.dbf',
  '+EBSDATA/erpdb/datafile/a_int01.dbf',
  '+EBSDATA/erpdb/datafile/a_media01.dbf',
  '+EBSDATA/erpdb/datafile/a_nolog01.dbf',
  '+EBSDATA/erpdb/datafile/a_queue01.dbf',
  '+EBSDATA/erpdb/datafile/a_queue02.dbf',
  '+EBSDATA/erpdb/datafile/a_ref01.dbf',
  '+EBSDATA/erpdb/datafile/a_ref02.dbf',
  '+EBSDATA/erpdb/datafile/a_summ01.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data01.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data02.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data03.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind01.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind02.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind03.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind04.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind05.dbf',
  '+EBSDATA/erpdb/datafile/ctxd01.dbf',
  '+EBSDATA/erpdb/datafile/odm.dbf',
  '+EBSDATA/erpdb/datafile/olap.dbf',
  '+EBSDATA/erpdb/datafile/owad01.dbf',
  '+EBSDATA/erpdb/datafile/portal01.dbf',
  '+EBSDATA/erpdb/datafile/sysaux01.dbf',
  '+EBSDATA/erpdb/datafile/apps_ts_tools01.dbf',
  '+EBSDATA/erpdb/datafile/interim.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data4.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data05.dbf',
  '+EBSDATA/erpdb/datafile/undo02.dbf',
  '+EBSDATA/erpdb/datafile/c_xxca1.dbf',
  '+EBSDATA/erpdb/datafile/sysaux02.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data06.dbf',
  '+EBSDATA/erpdb/datafile/undo03.dbf',
  '+EBSDATA/erpdb/datafile/undo04.dbf',
  '+EBSDATA/erpdb/datafile/a_ref03.dbf',
  '+EBSDATA/erpdb/datafile/a_summ02.dbf',
  '+EBSDATA/erpdb/datafile/system12.dbf',
  '+EBSDATA/erpdb/datafile/c_xxca2.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data07.dbf',
  '+EBSDATA/erpdb/datafile/undo05.dbf',
  '+EBSDATA/erpdb/datafile/a_ref04.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data08.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data09.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_data10.dbf',
  '+EBSDATA/erpdb/datafile/a_txn_ind06.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_undotbs2_83kyylyp_.dbf',
  '+EBSDATA/erpdb/datafile/sysaux03.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85gcky4s_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__85j1mhyw_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_sysaux_85ob61g6_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb62r7_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__86cb99r0_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__884bxzjg_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88dj97b0_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djh3nr_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88djl1bx_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_system_88djqht8_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6xklr_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h6ywnw_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_system_88h70cwk_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h72gqb_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__88h74wfs_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wc4jy8_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89ws999m_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__89wsckx1_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byhjwgf_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8byho4lm_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy4xs9p_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy51r6s_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8cy56y3y_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5b79or_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_custom_x_8f5b9ys7_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f5bd39h_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61ggor_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8f61klp7_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dojpl_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7dv8j0_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f1mr2_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7f5ljy_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fbo9s_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8h7fghfv_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_system_8h7fm0kn_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf72xfo_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8kf74fgk_.dbf',
  '+EBSDATA/erpdb/datafile/undo06.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_sysaux_8mm63h3m_.dbf',
  '+EBSDATA/erpdb/datafile/o1_mf_apps_ts__8nhs6461_.dbf'
CHARACTER SET US7ASCII
;

SQL> @/d01/app/oracle/cntrol.sql;

CREATE CONTROLFILE SET DATABASE "AWGPROD" RESETLOGS

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-12720: operation requires database is in EXCLUSIVE mode

SOLUTION

After analyzing the issue, We found that cluster_database parameter needs to be set as FALSE. So proceeding with the change.

bash-3.2$ vi initAWGprod1.ora

*.cluster_database=FALSE

then

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora.pfile';

ORACLE instance started.

Before create new control file comment on pfile old controlfile destination after creation copy new location and define it:

SQL> @/d01/app/oracle/cntrol.sql;

Control file created

Open the database with resetting log file headers.

RMAN> alter database open resetlogs;

Database altered.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +EBSDATA/AWGprod/controlfile/c
                                                 urrent.369.814133507
control_management_pack_access       string      DIAGNOSTIC+TUNING

After open database, create spfile from current pfile to shared location (+EBSDATA).

SQL> create spfile='+EBSDATA/spfileAWGprod.ora' from pfile='/d01/app/oracle/product/11.2.0/db_1/dbs/initAWGprod1.ora.pfile';

File created.

SQL> shut immediate

ORACLE instance shut down.

SQL> exit
bash-3.2$ sqlplus / as sysdba


Connected to an idle instance.

SQL> startup
ORACLE instance started.

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +EBSDATA/spfileAWGprod.ora

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

bash-3.2$ srvctl start database -d AWGprod
PRCR-1079 : Failed to start resource ora.AWGprod.db
CRS-5017: The resource action "ora.AWGprod.db start" encountered the following error:
ORA-01618: redo thread 2 is not enabled - cannot mount


CRS-2674: Start of 'ora.AWGprod.db' on 'AWGebsdb2' failed
CRS-2632: There are no more servers to try to place resource 'ora.AWGprod.db' on that would satisfy its placement policy


On the other end when we checked on 2nd db node its giving below error:

SQL> alter database mount
  2  ;
alter database mount
*
ERROR at line 1:
ORA-01618: redo thread 2 is not enabled - cannot mount

SOLUTION

After analyzing the issue, Start db on 1st node and run the following command to add group/logfiles in thread 2 and then enable thread2.


SQL> alter database add logfile thread 2 group 7 ('+EBSDATA/erpdb/onlinelog/group_7_01.dbf')  SIZE 1024M,
  2  group 8 ('+EBSDATA/erpdb/onlinelog/group_8_01.dbf')  SIZE 1024M,
  3  group 9 ('+EBSDATA/erpdb/onlinelog/group_9_01.dbf')  SIZE 1024M,
  4  group 10 ('+EBSDATA/erpdb/onlinelog/group_10_01.dbf')  SIZE 1024M,
  5  group 11 ('+EBSDATA/erpdb/onlinelog/group_11_01.dbf')  SIZE 1024M,
  6  group 12 ('+EBSDATA/erpdb/onlinelog/group_12_01.dbf')  SIZE 1024M;

Database altered.

SQL> alter database enable public thread 2;

Database altered.

Now we are able to start and stop db services with srvctl as below:

$ srvctl start database –d AWGprod

$ srvctl status database –d AWGprod

Db running on both nodes.

SQL> select * from v$tempfile;

no rows selected

SQL> alter tablespace TEMP1 add tempfile size 1024m;

Tablespace altered.

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1       7863567269 01-MAY-13          2          1 ONLINE  READ WRITE
1073741824     131072   1073741824       8192
+EBSDATA/AWGprod/tempfile/temp1.376.814285421



SQL> create temporary tablespace TEMP3 tempfile size 1024m;

Tablespace created.

SQL> alter database default temporary tablespace TEMP3;

Database altered.

SQL> select * from v$tablespace where name like '%TEMP%';

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         2 TEMP1                          NO  NO  YES
        20 TEMP2                          NO  NO  YES
        26 TEMP3                          NO  NO  YES

SQL> exit

SQL> show parameter db_name;

 

NAME                                 TYPE        VALUE

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

db_name                              string      AWGPROD

 

$cd $ORACLE_HOME/appsutil/install/AWGPROD_AWGebsdb1

$ sqlplus "/ as sysdba" @adupdlib.sql so

 

PL/SQL procedure successfully completed.

 

SQL>exit

bash-3.2$ hostname

AWGebsdb1.earms.army.gov

 

bash-3.2$ sqlplus / as sysdba

SQL> select * from v$option where parameter='Oracle Database Vault';

PARAMETER                            VALUE

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

Oracle Database Vault               TRUE

Enable archive log mode with database

bash-3.2$ hostname

AWGebsdb1.earms.army.gov

bash-3.2$ sqlplus / as sysdba

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /d01/ebsacfs/archive

Oldest online log sequence     1

Current log sequence           1

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE

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

log_archive_dest                     string

log_archive_dest_1                   string      LOCATION=/d01/ebsacfs/archive

 

$srvctl stop database –d AWGprod

$srvctl start database –d AWGprod –o mount;

$sqlplus / as sysdba

 

SQL> Alter database archivelog;

SQL> Alter database open;

SQL> Alter system switch logfile;

$srvctl stop database –d AWGprod

$srvctl start database –d AWGprod

#cd /d01/ebsacfs/archive

#ls –ltr        [check archive logs are generating]

Step 7

At last Run autoconfig on both db tiers.

bash-3.2$ sh adautocfg.sh

Enter the APPS user password:

The log file for this session is located at: /d01/app/oracle/product/11.2.0/db_1/appsutil/log/AWGprod1_AWGebsdb1/05011430/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

        Using ORACLE_HOME location : /d01/app/oracle/product/11.2.0/db_1

        Classpath                   : :/d01/app/oracle/product/11.2.0/db_1/jdbc/lib/ojdbc5.jar:/d01/app/oracle/product/11.2.0/db_1/appsutil/java/xmlparserv2.jar:/d01/app/oracle/product/11.2.0/db_1/appsutil/java:/d01/app/oracle/product/11.2.0/db_1/jlib/netcfg.jar:/d01/app/oracle/product/11.2.0/db_1/jlib/ldapjclnt11.jar

        Using Context file          : /d01/app/oracle/product/11.2.0/db_1/appsutil/AWGprod1_AWGebsdb1.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112

Updating rdbms type in Context file to 64 bits

Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

Application tier preparation:

AWGEBS1:                                                                 

Now we will move only (COMMON_TOP and APPL_TOP) backup from source to target machine.

Scp –r BKPAPPS_AWGEBS_04272013.tar applmgr@101.73.65.137: /d01/prodapp

Check again below entries of AWGprod_AWGebs1.xml

Implement load balancing for the Oracle Applications database connections:

1.        Edit the Context file($CONTEXT_FILE) and set the value of "Tools OH TWO_TASK" (s_tools_twotask), "iAS OH TWO_TASK" (s_weboh_twotask) and "Apps JDBC Connect Alias" (s_apps_jdbc_connect_alias).

2.        To load balance the forms based applications database connections, set the value of "Tools OH TWO_TASK" to point to the <database_name>_balance alias generated in the tnsnames.ora file.

Change from “AWGprod” to “AWGprod _balance”

3.        To load balance the self-service applications database connections, set the value of "iAS OH TWO_TASK" (s_weboh_twotask) and "Apps JDBC Connect Alias"(s_apps_jdbc_connect_alias) to point to the <database_name>_balance alias generated in the tnsnames.ora file.

Change from “AWGprod” to “AWGprod _balance”

4.        Execute AutoConfig by running the command:

$ $AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/AWGprod_AWGebs1.xml

5.        Restart the Applications processes, using the new scripts generated by AutoConfig.

6.        Ensure that value of the profile option "Application Database ID" is set to dbc file name generated in $FND_SECURE.

7.        Make sure all DB node names and scan names are resolvable (e-g HOST file entries) on appstier node if DNS is not used.

Note: If you are adding a new node to the application tier, repeat the above steps 1-6 for setting up load balancing on the new application tier node.

bash-3.2$ sh adautocfg.sh

Enter the APPS user password:

The log file for this session is located at: /d01/prodapp/inst/apps/AWGprod_AWGebs1/admin/log/05021502/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.

        Using CONFIG_HOME location     : /d01/prodapp/inst/apps/AWGprod_AWGebs1

        Classpath                   : /u1/prodapp/apps/apps_st/comn/java/lib/appsborg2.zip:/u1/prodapp/apps/apps_st/comn/java/classes

        Using Context file          : /d01/prodapp/inst/apps/AWGprod_AWGebs1/appl/admin/AWGprod_AWGebs1.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...

        Configuring AD_TOP........COMPLETED

        Configuring FND_TOP.......COMPLETED

        Configuring ICX_TOP.......COMPLETED

        Configuring MSC_TOP.......COMPLETED

        Configuring IEO_TOP.......COMPLETED

        Configuring BIS_TOP.......COMPLETED

        Configuring AMS_TOP.......COMPLETED

        Configuring CCT_TOP.......COMPLETED

        Configuring WSH_TOP.......COMPLETED

        Configuring CLN_TOP.......COMPLETED

        Configuring OKE_TOP.......COMPLETED

        Configuring OKL_TOP.......COMPLETED

        Configuring OKS_TOP.......COMPLETED

        Configuring CSF_TOP.......COMPLETED

        Configuring IGS_TOP.......COMPLETED

        Configuring IBY_TOP.......COMPLETED

        Configuring JTF_TOP.......COMPLETED

        Configuring MWA_TOP.......COMPLETED

        Configuring CN_TOP........COMPLETED

        Configuring CSI_TOP.......COMPLETED

        Configuring WIP_TOP.......COMPLETED

        Configuring CSE_TOP.......COMPLETED

        Configuring EAM_TOP.......COMPLETED

        Configuring FTE_TOP.......COMPLETED

        Configuring ONT_TOP.......COMPLETED

        Configuring AR_TOP........COMPLETED

        Configuring AHL_TOP.......COMPLETED

        Configuring OZF_TOP.......COMPLETED

        Configuring IES_TOP.......COMPLETED

        Configuring CSD_TOP.......COMPLETED

        Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.

bash-3.2$ sh adstrtal.sh apps/apps768prelive

AWGEBS2:

Setup the inst_top as AWGebs1 and run autoconfig.

cd $ADMIN_SCRIPTS_HOME

sh adstrtal.sh apps/appspassword

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.

Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

Configure Enterprise Manager

AWGEBSDB1:

bash-3.2$ emca -config dbcontrol db -repos create -cluster

STARTED EMCA at May 3, 2013 8:21:41 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:

Database unique name: AWGprod

Service name: AWGprod

Listener port number: 1521

Listener ORACLE_HOME [ /d01/app/11.2.0/grid ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Cluster name: AWGebsdb

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

ASM ORACLE_HOME [ /d01/app/11.2.0/grid ]:

ASM port [ 1521 ]:

ASM username [ ASMSNMP ]:

ASM user password:  ra

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

You have specified the following settings

Database ORACLE_HOME ................ /d01/app/oracle/product/11.2.0/db_1

Database instance hostname ................ Listener ORACLE_HOME ................ /d01/app/11.2.0/grid

Listener port number ................ 1521

Cluster name ................ AWGebsdb

Database unique name ................ AWGprod

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

ASM ORACLE_HOME ................ /d01/app/11.2.0/grid

ASM port ................ 1521

ASM user role ................ SYSDBA

ASM username ................ ASMSNMP

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

Do you wish to continue? [yes(Y)/no(N)]: Y

INFO:

****************  Current Configuration  ****************

 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST

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

AWGprod           AWGebsdb1           AWGebsdb1.earms.army.gov

AWGprod           AWGebsdb2           AWGebsdb1.earms.army.gov

Enterprise Manager configuration completed successfully

FINISHED EMCA at May 3, 2013 8:50:02 PM

bash-3.2$

AWGEBSDB2:

bash-3.2$ emca -reconfig dbcontrol -cluster -EM_NODE AWGebsdb2 -EM_SID_LIST AWGprod2

STARTED EMCA at May 3, 2013 8:58:28 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:

Database unique name: AWGprod

Service name: AWGprod

Agent Node list [comma separated] (optional):

Do you wish to continue? [yes(Y)/no(N)]: Y

May 3, 2013 8:58:42 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /d01/app/oracle/cfgtoollogs/emca/AWGprod/emca_2013_05_03_20_58_28.log.

May 3, 2013 8:58:47 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

May 3, 2013 8:59:58 PM oracle.sysman.emcp.EMAgentConfig performDbcReconfiguration

INFO: Propagating /d01/app/oracle/product/11.2.0/db_1/AWGebsdb1_AWGprod/sysman/config/emd.properties,/d01/app/oracle/product/11.2.0/db_1/AWGebsdb2_AWGprod/sysman/config/emd.properties to remote nodes ...

May 3, 2013 8:59:59 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

May 3, 2013 9:00:45 PM oracle.sysman.emcp.EMDBPostConfig performDbcReconfiguration

INFO: Database Control started successfully

May 3, 2013 9:00:45 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage

INFO:

****************  Current Configuration  ****************

 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST

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

AWGprod           AWGebsdb1           AWGebsdb2.earms.army.gov

AWGprod           AWGebsdb2           AWGebsdb2.earms.army.gov

Enterprise Manager configuration completed successfully

RMAN Backup Scrips

########################RMAN Backup Script for PRELIVE  ################

echo "#################################################################################################"

echo "Start RMAN Database Full Backup on Date";date

ORACLE_HOME=/d01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=AWGprod1; export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin; export PATH

echo "Current database status";date

srvctl status database -d AWGprod

echo "Start Full Database Backup on";date

$ORACLE_HOME/bin/rman target / nocatalog <<EOF

configure encryption for database on;

set encryption on identified by 'earmsprod123rman';

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/backupebsdb/AWGPROD_RMANBKP/%F';

run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup as compressed backupset format '/backupebsdb/AWGPROD_RMANBKP/AWGprod_df_%t_%s_%p.bak'(database);

sql 'alter system archive log current';

backup format '/backupebsdb/AWGPROD_RMANBKP/archive_%t_%s_%p.bak' archivelog all;

copy current controlfile to '/backupebsdb/AWGPROD_RMANBKP/AWGprod.ctl';

delete noprompt obsolete;

release channel c1;

release channel c2;

}

exit

EOF

echo "RMAN Backup Finished on";date

srvctl status database -d AWGprod