RMAN Recovery Catalog Configuration

 Consider the recovery catalog Dbname is cln and tns entry is cln. Kindly follow the below steps in recovery catalog database.

Tablespace name              =>   RMAN

Temporary tablespace name    =>   TEMP

User name                    =>   rman

 1.Create tablespace.

   SQL> create tablespace RMAN datafile '/test/oracle/uatdata/rman1.dbf' size 2048M;

Tablespace created.

2.Create user.

   SQL> create user rman identified by rman

  2  default tablespace RMAN

  3  temporary tablespace TEMP

  4  quota unlimited on RMAN;

User created.

3.Granting Privilege.

   SQL> Grant connect,resource,recovery_catalog_owner to rman;

Grant succeeded.

4.Recovery catalog Creation

           Please check the recovery catalog IP address in production whether pinging is happening or not. Then put the tns entry of recovery in production database. Follow the steps in production.

[oracln@test dbs]$ rman catalog=rman/rman@cln

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to recovery catalog database

recovery catalog is not installed

RMAN> create catalog;

recovery catalog created

5. Register the Target Database with Recovery Catalog

[oracln@test dbs]$ rman catalog=rman/rman@cln target /

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: CLN (DBID=4233744721)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

6. Final Step

 Now the recovery catalog will successfully created and try to connect with recovery catalog in production.

[oracln@test dbs]$ rman target / catalog=rman/rman@cln

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: CLN (DBID=4233744721)

connected to recovery catalog database

RMAN>

Now we can change all the RMAN parameters as per our database requirement using show all command.

Rman>show all;

After the successful Catalog configuration we can schedule the RMAN backup in crontab as per the backup location if required.

I hope the above mentioned steps will assist you to configure the Recovery Catalog easily.

Regards,

Vijay

Workflow Notification mailer issue

Issue :

Workflow Differed Job got filled so the workflow notification mailer couldn't send mail.

Solution :

I saw the particular user mail status in that all status was shows expired.

So I submitted "Move Messages from Exception to Normal Queue of Workflow 

Agent" concurrent request. It will ask parameters then we need to give 

WF component name in my case I was given WF_NOTIFICATION_OUT.

Reference:

1. Howto Move Notifications From Exception Queue To Normal Queue?ID472204.1

2. FNDWF_MOVE_MSGS_EXCEP2NORMAL "The agent is not found"[ID 760983.1]

3. Error APP-FND-1030 Is Displayed When Trying To Submit "Move messages 

from Exception to Normal Queue of Workflow Agent" FNDWF_MOVE_MSGS

_EXCEP2NORMAL [ID 1451820.1]

For Enabling De-Queue we need run below procedure,

exec dbms_aqadm.start_queue(

queue_name=>'APPLSYS.AQ$_WF_NOTIFICATION_OUT_E',dequeue=>TRUE,

enqueue=>FALSE)

Regards,

Vijay.

Steps For Cloning Process

Cloning process:

Ans :

1. Run adpreclone as applmgr and oracle user on source Perl adpreclone.pl dbTier as oracle user Perl adpreclone.pl appsTier as applmgr user

2. Take the cold/hotbackup of source database

3. Copy the five directories appl,comn,ora , db,data to target

4. Rename the directories, and change the permisssion

5. Set the inventory in oraInst.loc

6. Run perl adcfgclone.pl dbTier as oracle user,if the backup type is cold

7. If the backup type is hotbackup then Perl adcfgclone.pl dbTechStack.
   Create the control file on target from the control script trace file from source Recover the database   Alter database open resetlogs

8. Run autoconfig with the ports changed as per requirement in xml.

9. Run perl adcfgclone.pl appsTier as applmgr

10. Run autoconfig with the ports changed as per requirement in xml.

Location of adpreclone.pl for oracle user:

Ans : RDBMS_ORACLE_HOME/appsutil/scripts/

Location of adpreclone.pl for applmgr user:

Ans : $COMMON_TOP/admin/scripts/

Location of adcfgclone.pl for oracle user:

Ans : $RDBMS_ORACLE_HOME/appsutil/clone/bin

Location of adcfgclone.pl for applmgr user:

Ans : $COMMON_TOP/clone/bin


Reference : http://appsdba.info/index.php?module=pagemaster&PAGE_user_op=view_page&PAGE_id=58&MMN_position=53:53

Step by Step Procedure To Add a Swap File Under Linux

In Linux, as in most other Unix-like operating systems, it is common to use a whole partition of a hard disk for swapping. However, with the 2.6 Linux kernel, swap files are just as fast as swap partitions, although I recommends using a swap partition. The administrative flexibility of swap files outweighs that of partitions; since modern high capacity hard drives can remap physical sectors, no partition is guaranteed to be contiguous. You can add swap file as a dedicated partition or use following instructions to create a swap file.

Procedure To Add a Swap File Under Linux

You need to use the dd command to create swap file. The mkswap command is used to set up a Linux swap area on a device or in a file.

Step #1: Login as the root User

Open a terminal window (select Applications > Accessories > Terminal) or login to remote server using the ssh client.  Switch to the root user by typing su - and entering the root password, when prompted

Step #2: Create Storage File

Type the following command to create 512MB swap file (1024 * 512MB = 524288 block size):
# dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
Where,

1. if=/dev/zero : Read from /dev/zero file. /dev/zero is a special file in that provides as many null characters to build storage file called /swapfile1.
2. of=/swapfile1 : Read from /dev/zero write stoage file to /swapfile1.
3. bs=1024 : Read and write 1024 BYTES bytes at a time.
4. count=524288 : Copy only 523288 BLOCKS input blocks.

Step #3: Set Up a Linux Swap Area

Type the following command to set up a Linux swap area in a file:

# mkswap /swapfile1

Setup correct file permission for security reasons, enter:

# chown root:root /swapfile1
# chmod 0600 /swapfile1

A world-readable swap file is a huge local vulnerability. The above command make sure only root user can read/write to the file. Finally, activate /swapfile1 swap space immediately, enter:

# swapon /swapfile1

To activate /swapfile1 after Linux system reboot, add entry to /etc/fstab file. Open this file using a text editor such as vi:

# vi /etc/fstab

Append the following line:

/swapfile1 swap swap defaults 0 0

Save and close the file. Next time Linux comes up after reboot, it enables the new swap file for you automatically.

How do I Verify Swap is Activated or Not?

Simply use the free command to Check swap size Changes. 

$ free -m

Regards,

Vijay.

Compatible Initialization Parameter and Upgrade/Downgrade

Compatibility: 

The COMPATIBLE initialization parameter controls the compatibility level of your database.

This parameter controls the database behavior for example whether a feature will work for a database. For example if database version is 10.2.0.4 but compatible parameter is set to 9.2 then certain feature like RMAN compression will not work. 

When you no longer need the ability to downgrade your database back to its original version, set the COMPATIBLE initialization parameter based on the compatibility level you want for your new database.

InCompatible :

When you upgrade to a new release of Oracle Database, certain new features might make your database incompatible with your previous release.

Conditions for Incompatible:

Your upgraded database becomes incompatible with your previous release under the following conditions: 

A new feature stores any data on disk (including data dictionary changes) that cannot be processed with your previous release. 

An existing feature behaves differently in the new environment as compared to the old environment. This type of incompatibility is classified as a language incompatibility.  

Purpose:

The COMPATIBLE initialization parameter enables or disables the use of features in the database that affect file format or disk and also you prevents from downgrading to your previous release.

After upgrading to Oracle Database new release, you can set the COMPATIBLE initialization parameter to match the release number of the new release.

COMPATIBLE Initialization Parameter and upgrade to 10g and 11g :

Oracle Database Release	Default Value	Minimum Value	Maximum Value
Oracle Database 9i Release 2 (9.2)	8.1.0	8.1.0.0.0	9.2.0.n.n
Oracle Database 10g Release 1 (10.1)	10.0.0	9.2.0.0.0	10.1.0.n.n
Oracle Database 10g Release 2 (10.2)	10.2.0	9.2.0.0.0	10.2.0.n.n
Oracle Database 11g Release 1 (11.1) Oracle Database 11g Release 2 (11.2)	11.0.0 11.2.0	10.0.0.0.0 10.0.0.0.0	11.1.0.n.n 11.2.0.n.n

You have to set the Minimum compatible value to the current database while upgrading to new release.

The COMPATIBLE Initialization Parameter and downgrade from 11g:

If, after upgrading, you want to downgrade, and you have never set the COMPATIBLE value to 11.0.0 or higher, then the COMPATIBLE initialization parameter must be left (not changed) as follows after the upgrade: 

        1. Set to 10.1.0 if you upgraded from Oracle Database 10g Release 1 (10.1) 
        2. Set to 10.2.0 or earlier if you upgraded from Oracle Database 10g Release 2 (10.2)

Checking the Current Value of the COMPATIBLE Initialization Parameter:

You can  check the current value of the COMPATIBLE initialization parameter, enter the following SQL statement: 

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

SQL> show parameter compatible;

When to Set the COMPATIBLE Initialization Parameter:

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for Oracle Database 11g Release 1 (11.1).

However, after you do this, the database cannot subsequently be downgraded

Setting the COMPATIBLE Initialization Parameter

Complete the following steps to set the COMPATIBLE initialization parameter to a higher value: 

      1) Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional). 

Raising the COMPATIBLE initialization parameter might cause your database to become incompatible with earlier releases of Oracle Database, and a backup ensures that you can return to the earlier release if necessary by restoring the backup. 

     2) If you are using a server parameter file, then complete the following steps:

           i)  Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter.  

For example, to set the COMPATIBLE initialization parameter to 11.0.0, enter the following statement:
              SQL> ALTER SYSTEM SET COMPATIBLE = '11.0.0' SCOPE=SPFILE; 

          ii) Shut down and restart the instance. 

   3) If you are using an initialization parameter file, then complete the following steps: 

          i) Shut down the instance if it is running:                 

               SQL> SHUTDOWN IMMEDIATE 

         ii)Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter. 

For example, to set the COMPATIBLE initialization parameter to 11.0.0, enter the following in the initialization parameter file: 
           COMPATIBLE = 11.0.0 

        iii) Start the instance using STARTUP.

Checking the Current Value of the COMPATIBLE Initialization Parameter:

You can  check the current value of the COMPATIBLE initialization parameter, enter the following SQL statement: 

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

SQL> show parameter compatible;

Downgrade the Oracle Database :

Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section.

Complete the following steps to downgrade your release 10.1 database to the previous Oracle Database release:

Log in to the system as the owner of the release 10.1 Oracle home directory.
 

1. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
2. Start SQL*Plus.
3. Connect to the database instance as a user with SYSDBA privileges.
4. Start up the instance in DOWNGRADE mode:

SQL> STARTUP DOWNGRADE

You may need to use the PFILE option to specify the location of your initialization parameter file.

Set the system to spool results to a log file for later verification of success:

SQL> SPOOL downgrade.log

Run dold_release.sql, where old_release refers to the release to which you are downgrading. See Table 7-1 to choose the correct script. Each script provides a direct downgrade to the release specified in the "Downgrading To" column.

To run a script, enter the following:

SQL> @dold_release.sql

Reference :

1.      COMPATIBLE Initialization Parameter and Upgrade/Downgrade in 11g R1 or 11gR2 (Doc ID 444709.1)

2.      11g Compatible Initialization Parameter Setting When Upgrading Agile (Doc ID 1324734.1)

3.      How To Change The COMPATIBLE Parameter And What Is The Significance? (Doc ID 733987.1)

4.      How to Downgrade from Oracle RDBMS 10gR2? (Doc ID 398372.1)

Regards, 

Vijay.

Find Application User Password in Oracle E-Business Suite 11i & R12

This scripts allows to view the passwords which are stored encrypted in fnd_user table.

We can use different sqls to find apps password and application user passwords.

Connect as an Apps User to do the following,

Create Package

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2;

END XXARTO_GET_PWD;

/

 Package created.

Create Package Body
 

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2

AS

LANGUAGE JAVA

NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END XXARTO_GET_PWD;

/

Package body created.

Find apps user password in 11i

SELECT (SELECT XXARTO_GET_PWD.Decrypt (

UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))

FROM DUAL)),

Usertable.Encrypted_Foundation_Password)

FROM DUAL)

AS Apps_Password

FROM applsys.Fnd_User Usertable

WHERE Usertable.User_Name LIKE

UPPER( (SELECT SUBSTR (

Fnd_Profile.VALUE ('Guest_User_Pwd'),

1,

INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')

- 1)

FROM DUAL));

APPS_PASSWORD
--------------------------------------------------------------------------------
APPS

Find other application user password in 11i

Select Usertable.User_Name , (Select XXARTO_GET_PWD.Decrypt (Upper ((Select
(Select XXARTO_GET_PWD.Decrypt (Upper ((Select Upper (Fnd_Profile.Value ('Guest_User_Pwd'))
From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password
From Fnd_User Usertable
Where Usertable.User_Name Like Upper ((Select Substr (Fnd_Profile.Value ('Guest_User_Pwd'),
1 , Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual)))) ,Usertable.Encrypted_User_Password)
From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username');

Enter value for username: vijay
old   7:        From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username')
new   7:        From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('vijay')

USER_NAME                  ENCRYPTED_USER_PASSWORD
-------------------------------------------------------------
VIJAY                               ORACLE

Find other application user password in R12

ALTER SESSION SET current_schema = apps;

Select Usr.User_Name, Usr.Description,

       Get_User_Pwd.Decrypt((Select (Select Get_User_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';

Find  application user password (for example SYSADMIN) in 11i
 

SELECT Usertable.User_Name,
(SELECT XXARTO_GET_PWD.Decrypt (
UPPER( (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))
FROM DUAL)),
Usertable.Encrypted_Foundation_Password)
FROM DUAL)
AS Apps_Password
FROM applsys.Fnd_User Usertable
WHERE Usertable.User_Name LIKE
UPPER( (SELECT SUBSTR (
Fnd_Profile.VALUE (
'Guest_User_Pwd'),1,
INSTR (
Fnd_Profile.VALUE (
'Guest_User_Pwd'),
'/')
- 1)
FROM DUAL)))),
Usertable.Encrypted_User_Password)
FROM DUAL)
AS Encrypted_User_Password
FROM Applsys.Fnd_User Usertable
WHERE Usertable.User_Name LIKE UPPER ('&Username');

Enter value for username: sysadmin
old  24: WHERE Usertable.User_Name LIKE UPPER ('&Username')
new  24: WHERE Usertable.User_Name LIKE UPPER ('sysadmin')

USER_NAME                     ENCRYPTED_USER_PASSWORD
--------------------------------------------------------------------------------
SYSADMIN                              SYSADMIN

Regards,
Vijay.