Switchover from primary to standby

1. Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_STANDBY” as result.
Otherwise

2. SQL>alter system switch logfile;

3. Switch primary database to standby database.

SQL>alter database commit to switchover to physical standby with session shutdown;

 SQL>shutdown immediate;

 SQL>startup nomount;

4.SQL>alter database mount standby database;

Defer for archive log apply. Because I didn’t set my standby database as primary yet.

5. SQL>alter system set log_archive_dest_state_2=defer;

Switch standby database to primary. Check switchover status before switching database

Upgrade of Oracle Applications Version R12.1.1 to R12.1.3 on AIX

Upgrade of Oracle Applications Version R12.1.1 to R12.1.3 on AIX

Required patches:

p9239089_R12.AD.B_R12_LINUX.zip
p10349415_R12.AD.B_R12_GENERIC.zip (Optional) (leave it)

·                    p9239090_R12_AIX64-5L_1of6.zip

·                    p9239090_R12_AIX64-5L_2of6.zip

·                    p9239090_R12_AIX64-5L_3of6.zip

·                    p9239090_R12_AIX64-5L_4of6.zip

·                    p9239090_R12_AIX64-5L_5of6.zip

·                    p9239090_R12_AIX64-5L_6of6.zip

p9239095_R12_GENERIC.zip

p9822544_R12.MSC.B_R12_GENERIC.zip (Optional) (leave it)

Post update patches are:

p9966055_R12.FND.B_R12_GENERIC.zip
p9817770_R12.ATG_PF.B_R12_AIX64-5L.zip

The following information will provide up gradation process for Ooracle Applications R12.1.1 to R12.1.3.

Step1: Run adadmin and put Maintenance Mode

Step2: Apply Prerequisite R12.AD.B.DELTA.3 Patch 9239089
Do the following Tasks before going to apply the Patch R12.AD.B.DELTA.3 Patch 9239089

Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

Usage:
1.Create $ORACLE_HOME/appsutil/admin on the database server.

2. Copy adgrants.sql (UNIX) from this patch directory to $ORACLE_HOME/appsutil/admin.Or, copy adgrants_nt.sql (Windows) from this patch directory to %ORACLE_HOME%\appsutil\admin.

3. Set the environment to point to ORACLE_HOME on the database server.

4. Use SQL*Plus to run the script:

UNIX:
$ sqlplus /nolog
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>

Step3: Apply R12.AD.B_R12_GENERIC.zip Patch 10349415(leave it)

Step4: Apply Oracle E-Business Suite Release 12.1.3 Patch 9239090

Step5:. Apply Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack patch 9239095 (leave it)

Step6:.Apply Patch 9822544 for Advanced Supply Chain Planning. (Optional).This patch fixes the issue of collecting resource requirements of OPM batches WIP warehouse does not belong to resource warehouse. (Leave it)

Step7: Apply post Update Patches:

–Apply mandatory Patch 9817770 (9817770:R12.ATG_PF.B [POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATEDPATCH].)

–Apply mandatory Patch 9966055 (9966055:R12.FND.B [TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED].)

Notice

After you have upgraded to Oracle E-Business Suite Release 12.1.3, perform the following post-update steps to update all database tier nodes with the code level, provided by Oracle E-Business Suite Release 12.1.3

Step8: Run Application and Database Environment files.

@Application Tier:

·        Run AutoConfig on the APPL_TOP.

·        Run the admkappsutil.pl utility to create the file appsutil.zip in the <INST_TOP>/admin/out directory.

Perl <AD_TOP>/bin/admkappsutil.pl

If scripts directory is not exist in oracle_home/appsutil

Perl adbldxml.pl template=/d01/oracle/GREENTC/db/tech_st/11.1.0/appsutil/template/adxdbctx.tmp out=/d01/oracle/GREENTC/db/tech_st/11.1.0/appsutil/greentc_sfxd5.xml

adconfig.sh contextfile=/d01/oracle/GREENTC/db/tech_st/11.1.0/appsutil/greentc_sfxd5.xml appspass=greenapps

@Database Tier:

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>. Uncompress appsutil.zip under the <RDBMS ORACLE_HOME>.

·        cd <ORACLE_HOME>

·        unzip -o appsutil.zip

·        Run AutoConfig on the <RDBMS ORACLE_HOME>.

Step9:. Run adpreclone.pl on the database tier and the application tier

·        perl adpreclone.pl dbTier

·        perl adpreclone.pl appsTier

Step10: Disable Maintenance Mode using adadmin and check the version using following sql statement.

SQL> select release_name from fnd_product_groups;

RELEASE_NAME

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

12.1.3

After up gradation of R12.1.1 and open the form then below error is occurs--


APP-FND-01926:THE CUSTOM event WHEN-RESPONSIBILITY-CHANGED raised unhandled exception ORA-200000 ORA-01722 INVALID NUMBER

The above Error solve by following query

There is no need to run this query where multi org are setup, then direct run the 2^nd query.

Query 1

declare

var boolean;

begin

var :=  fnd_profile.SAVE('ORG_ID','102','SITE',NULL,NULL,NULL);

--from dual;

--return var;

end;

if the error is not solve then apply following query
Query 2

declare

var boolean;

begin

var :=  fnd_profile.SAVE('FND_MO_INIT_CI_DEBUG','N','SITE',NULL,NULL,NULL);

--from dual;

--return var;

end;

or

DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('FND_MO_INIT_CI_DEBUG',' ','SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
end;

Refresh clone R12 Database from Production

UPDATE CLONE FROM PROD
use prod pfile with
change path according to clone and add log_file_name_convert,db_file_name_convert parameter

1- sqlplus>startup nomount pfile='/d01/oracle/FIN/db/tech_st/11.1.0/dbs/refresh.ora';
2-rman target /
rman>restore controlfile from '/sw/oracle/cont.bkp';
rman>alter database mount;
rman>catalog backuppiece '/backup/oracle/ and 'database backup' and 'archivelog backup'
or
rman>CATALOG START WITH '/sw/oracle';
RMAN>CATALOG START WITH '/backup/oracle' NOPROMPT;

3-run rman restore script;
run
{
SET UNTIL SEQUENCE  260 THREAD 1;
set newname for datafile 1 to '/d01/oracle/oradata/system01.dbf';
set newname for datafile 2 to '/d01/oracle/oradata/system02.dbf';
set newname for datafile 3 to '/d01/oracle/oradata/system03.dbf';
set newname for datafile 4 to '/d01/oracle/oradata/system04.dbf';
set newname for datafile 5 to '/d01/oracle/oradata/system05.dbf';
set newname for datafile 6 to '/d01/oracle/oradata/system06.dbf';
set newname for datafile 7 to '/d01/oracle/oradata/system07.dbf';
set newname for datafile 8 to '/d01/oracle/oradata/system08.dbf';
set newname for datafile 9 to '/d01/oracle/oradata/system09.dbf';
set newname for datafile 10 to '/d01/oracle/oradata/system10.dbf';
set newname for datafile 11 to '/d01/oracle/oradata/system11.dbf';
set newname for datafile 12 to '/d01/oracle/oradata/undo01.dbf';
set newname for datafile 13 to '/d01/oracle/oradata/a_archive01.dbf';
set newname for datafile 14 to '/d01/oracle/oradata/a_int01.dbf';
set newname for datafile 15 to '/d01/oracle/oradata/a_media01.dbf';
set newname for datafile 16 to '/d01/oracle/oradata/a_nolog01.dbf';
set newname for datafile 17 to '/d01/oracle/oradata/a_queue01.dbf';
set newname for datafile 18 to '/d01/oracle/oradata/a_queue02.dbf';
set newname for datafile 19 to '/d01/oracle/oradata/a_ref01.dbf';
set newname for datafile 20 to '/d01/oracle/oradata/a_ref02.dbf';
set newname for datafile 21 to '/d01/oracle/oradata/a_summ01.dbf';
set newname for datafile 22 to '/d01/oracle/oradata/a_txn_data01.dbf';
set newname for datafile 23 to '/d01/oracle/oradata/a_txn_data02.dbf';
set newname for datafile 24 to '/d01/oracle/oradata/a_txn_data03.dbf';
set newname for datafile 25 to '/d01/oracle/oradata/a_txn_ind01.dbf';
set newname for datafile 26 to '/d01/oracle/oradata/a_txn_ind02.dbf';
set newname for datafile 27 to '/d01/oracle/oradata/a_txn_ind03.dbf';
set newname for datafile 28 to '/d01/oracle/oradata/a_txn_ind04.dbf';
set newname for datafile 29 to '/d01/oracle/oradata/a_txn_ind05.dbf';
set newname for datafile 30 to '/d01/oracle/oradata/ctxd01.dbf';
set newname for datafile 31 to '/d01/oracle/oradata/odm.dbf';
set newname for datafile 32 to '/d01/oracle/oradata/olap.dbf';
set newname for datafile 33 to '/d01/oracle/oradata/owad01.dbf';
set newname for datafile 34 to '/d01/oracle/oradata/portal01.dbf';
set newname for datafile 35 to '/d01/oracle/oradata/sysaux01.dbf';
set newname for datafile 36 to '/d01/oracle/oradata/apps_ts_tools01.dbf';
set newname for datafile 37 to '/d01/oracle/oradata/a_txn_data4.dbf';
set newname for datafile 38 to '/d01/oracle/oradata/sfx.dbf';
restore database;
switch datafile all;
recover database;
}
4-alter redolog file rename
alter database rename file  '/d01/oracle/PROD/apps_st/data/log04a.dbf' to '/d01/oracle/oradata/log04a.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log04b.dbf' to '/d01/oracle/oradata/log04b.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log03a.dbf' to '/d01/oracle/oradata/log03a.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log03b.dbf' to '/d01/oracle/oradata/log03b.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log02a.dbf' to '/d01/oracle/oradata/log02a.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log02b.dbf' to '/d01/oracle/oradata/log02b.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log01a.dbf' to '/d01/oracle/oradata/log01a.dbf';
alter database rename file  '/d01/oracle/PROD/apps_st/data/log01b.dbf' to '/d01/oracle/oradata/log01b.dbf';

5-alter database open resetlogs;

6-change database name
1-shutdown immediate
2-delete controlfile
3-startup nomount pfile='/d01/oracle/FIN/db/tech_st/11.1.0/dbs/initsfxfin.ora';
4-craete controlfile
5-alter database open resetlogs;
 
7-create tablespace
set path /u01/oracle/DEV/inst/apps/fintest_report/admin/scripts
6- FNDCPASS apps/mobileforu 0 Y system/manager SYSTEM APPLSYS greeenapps
7- AUTOCONFIGs

Oracle Apps R12 cloning online hotbackup/rman

Cloning Oracle R12 using RMAN backup

Here are the steps:

1.Execute preclone on all tiers of the source system. This includes both the database and application tiers. (For this example, TEST is my source system.)

For the database execute: $ORACLE_HOME/appsutil/scripts/<context>/adpreclone.pl dbTier
Where context name is of the format <sid>_<hostname>

For the application tier: $ADMIN_SCRIPTS_HOME/adpreclone.pl appsTier

2.Prepare the files needed for the clone and copy them to the target server.
Take a FULL rman backup and copy the files to the target server and place them in the identical path. ie. if your rman backups go to /sw/oracle on the source server, place them in /sw/oracle on the destination server. To be safe, you may want to copy some of the archive files generated while the database was being backed up. Place them in an identical path on the target server as well.
Application Tier: tar up the application files and copy them to the destination server. The cloning document referenced above ask you to take a copy of the $APPL_TOP, $COMMON_TOP, $IAS_ORACLE_HOME and $ORACLE_HOME. Normally I just tar up the System Base Directory, which is the root directory for your application files.
Database Tier: tar up the database $ORACLE_HOME.

ex. from a single tier system. The first tar file contains the application files and the second is the database $ORACLE_HOME

[oratest@myserver TEST]$ pwd
/u01/TEST
[oratest@myserver TEST]$ ls
apps db inst
[oratest@myserver TEST]$ tar cvfzp TEST_apps_inst_myserver.tar.gz apps inst
.
.
[oratest@myserver TEST]$ tar cvfzp TEST_dbhome_myserver.tar.gz db/tech_st
Notice for the database $ORACLE_HOME I only added the db/tech_st directory to the archive. The reason is that the database files are under db/apps_st and we don't need those.
Copy the tar files to the destination server, create a directory for your new environment, for example /u01/DEV. (For the purpose of this article I will be using /u01/DEV as the system base for the target envrionment we are building and myserver is the server name.)
Extract each of the tar files with the command tar xvfzp

Ex. tar xvfzp TEST_apps_inst_myserver.tar.gz

3.Configure the target system.

On the database tier execute adcfgclone.pl with the dbTechStack parameter.

For example. /u01/DEV/db/tech_st/10.2.0/appsutil/clone/bin/
perl adcfgclone.pl dbTechStack

By passing the dbTechStack parameter we are tell the script to configure only the necessary $ORACLE_HOME files such as the init file for the new environment, listener.ora, database environment settings file, etc. It will also start the listener.

You will be prompted the standard post cloning questions such as the SID of the new environment, number of DATA_TOPS, Oracle Home location, port settings, etc.

Once this is complete goto /u01/DEV/db/tech_st/10.2.0 and execute the environment settings file to make sure your environment is set correctly.

[oradev@myserver 10.2.0] . ./DEV_myserver.env

4.Duplicate the source database to the target.
In order to duplicate the source database you'll need to know the scn value to recover to. There are two wasy to do this. The first is to login to your rman catalog, find the Chk SCN of the files in the last backupset of your rman backup and add 1 to it.


Ex. Output from a rman> List backups
.
.
List of Datafiles in backup set 55729
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 1 Incr 5965309363843 15-JUN-09 /u02/TEST/db/apps_st/data/owad01.dbf
.
.
So in this case the SCN we would be recovery to is 5965309363843 + 1 = 5965309363844.

The other method is to login to the rman catalog via sqlplus and execute the following query:

select max(absolute_fuzzy_change#)+1,
max(checkpoint_change#)+1
from rc_backup_datafile;

Use which ever value is greater.
Modify the db_file_name_convert and log_file_name convert parameters in the target init file. Example:

db_file_name_convert=('/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
'/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')

log_file_name_convert=(/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
'/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')
Verify you can connect to source system from the target as sysdba. You will need to add a tns entry to the $TNS_ADMIN/tnsnames.ora file for the source system.
Duplicate the database. Before we use rman to duplicate the source database we need to start the target database in nomount mode.

Start rman:

rman target sys/<syspass>@TEST catalog rman/rman@RMAN auxiliary /

If there are no connection errors duplicate the database with the following script:

run {
set until scn 5965309363844;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
duplicate target database to DEV }

The most common errors at this point are connection errors to the source database and rman catalog. As well, if the log_file_name_convert and db_file_name_convert parameters are not set properly you will see errors. Fix the problems, login with rman again and re-execute the script.

When the rman duplicate has finished the database will be open and ready to proceed with the next steps.
Execute the library update script:

cd $ORACLE_HOME/appsutil/install/DEV_myserver where DEV_myserver is the <context_name> of the new environment.

sqlplus "/ as sysdba"@adupdlib.sql so

If your on linux replace with so, HPUX with sl and for windows servers leave blank.
Configure the target database

cd $ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl dbconfig

Where is $ORACLE_HOME/appsutil/DEV_myserver.xml
$ORACLE_HOME/appsutil/clone/bin/ adcfgclone.pl dbconfig /d01/oracle/CLONE/db/tech_st/11.1.0/appsutil/cloner_sfxprodapp1.xml
　
5.Configure the application tier.

cd /u01/DEV/apps/apps_st/comn/clone/bin
perl adcfgclone.pl appsTier

You will be prompted the standard cloning questions consisting of the system base directories, which services you want enabled, port pool, etc. Make sure you choose the same port pool as you did when configuring the database tier in step 3.

Once that is finished, initialize your environment by executing

. /u01/DEV/apps/apps_st/appl/APPSDEV_myserver.env

6.Shutdown the application tier.

cd $ADMIN_SCRIPTS_HOME
./adstpall.sh apps/<source apps pass>

7.Login as apps to the database and execute:

exec fnd_conc_clone.setup_clean;

I don't believe this step is necessary but if you don't do this you will see references to your source environment in the FND_% tables. Every time you execute this procedure you need to run autoconfig on each of the tiers (db and application). We will get to that in a second.

8. Change the apps password. Chances are you don't want to have the same apps password as the source database, so its best to change it now while the environment is down.
With the apps tier environment initialized:

FNDCPASS apps/<source apps pass> 0 Y system/<source system pass>> SYSTEM APPLSYS <new apps pass>

9.Run autoconfig on both the db tier and application tier.

db tier:
cd $ORACLE_HOME/appsutil/scripts/DEV_myserver
./adautocfg.sh

Application Tier
cd $ADMIN_SCRIPTS_HOME
./adautocfg.sh

10. If there are no errors with autoconfig start the application. Your already in the $ADMIN_SCRIPTS_HOME so just execute:

./adstrtal.sh apps/<new apps pass>

11. Login to the application and perform any post cloning activities. You may want to override the work flow email address so that notifications goto a test/dev mailbox instead of users. We always change the colors and site_name profile options, etc. More details can be found in Section 3: Finishing tasks of the R12 cloning document referenced earlier
Reference 　
http://suryakanta-sahu.blogspot.in/2011/07/frm-92101-when-launching-forms-on-ibm.html

Refresh Clone of Oracle R12

UPDATE CLONE FROM PROD
prod pfile with
change path according to clone and add log_file_name_convert,db_file_name_convert parameter

1- sqlplus>startup nomount pfile='/d01/oracle/PROD/tech_st/11.1.0/dbs/initfinprodrefresh.ora';
2-rman target /
rman>restore controlfile from '/backup/oracle/.......';
rman>alter database mount;
rman>catalog backuppiece '/backup/oracle/              and 'database backup' and 'archivelog backup'
or
rman>CATALOG START WITH '/backup/oracle ';
RMAN>CATALOG START WITH '/backup/oracle' NOPROMPT;
3-run rman restore script;
4-alter redolog file rename
5-alter database open resetlogs;
6-change database name
shutdow immediate
delete controlfile
startup nomount pfile='/d01/oracle/CLONE/db/tech_st/11.1.0/dbs/cloner_sfxprodapp1_ifile.ora';
create controlfile
alter database open resetlogs
7-create tablespace
6- FNDCPASS apps/mobileform 0 Y system/manager SYSTEM APPLSYS safex321
7- AUTOCONFIG