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

RMAN BACKUP

weekly incremental backup
run
{
backup as compressed backupset
incremental level=0 cumulative
device type disk
tag "weekly_cum_database"
format '+backup/weekly_cum_database_%d_t%t_c%c_s%s_p%p'
database;
backup as backupset
device type disk
tag "ARCHIVELOGS"
format '+backup/daily_archivelog_%d_t%t_c%c_s%s_p%p'
archivelog all;
}


daily incremental backup
run
{
backup as compressed   backupset
incremental level=1 cumulative
device type disk
tag "daily_cum_database"
format '+backup/daily_cum_database_%d_t%t_c%c_s%s_p%p'
database;
backup as backupset
device type disk
tag "ARCHIVELOGS"
format '+backup/daily_archivelog_%d_t%t_c%c_s%s_p%p'
archivelog all;
}

RMAN Performance

Monitoring RMAN Job Progress

select sid, serial#, sofar, totalwork, opname,
round(sofar/totalwork*100,2) "% Complete"
from v$session_longops
 where opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
 and totalwork != 0
and sofar <> totalwork;

 set lines 300 
set operation format a10 
alter session set optimizer_mode=RULE; 
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss'; 
select distinct p.tag,r.object_type,r.SESSION_RECID,r.START_TIME,r.END_TIME,(r.END_TIME-r.START_TIME)*24 "ET(Hr)", 
r.OUTPUT_BYTES/1048576/1024 "SIZE (GB)",r.status,r.OUTPUT_DEVICE_TYPE,r.input_bytes/1048576 "input (MB)",INPUT_BYTES_PER_SEC/1048576 "read (MB/sec)",r.output_bytes/1048576 "output (MB)",OUTPUT_BYTES_PER_SEC/1048576 "output (MB/sec)" 
from v$backup_piece p,v$rman_status r, v$rman_backup_job_details d 
where p.RMAN_STATUS_RECID=r.RECID and p.RMAN_STATUS_STAMP=r.STAMP 
and r.status like '%COMPLETED%' and r.OPERATION like '%BACKUP%' 
and r.object_type like 'DB%' 
and d.SESSION_RECID=r.SESSION_RECID;



SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
select sid, totalwork, sofar, (sofar/totalwork) * 100 per_done,start_time, sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and lower(opname) not like ‘%aggregate%’
and lower(opname) like ‘rman%’;
For Export:

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;

SQL>
select sid, totalwork, sofar, round((sofar/totalwork) * 100) done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like ‘%EXPORT%’;

For Import:
SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;

SQL>
select sid, totalwork, sofar, round((sofar/totalwork) * 100) per_done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like ‘%IMPORT%’;

scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.

Login as sysdba -

This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99

select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -
col STATUS format a9
col hrs format 999.99

select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

vi Editor on AIX

VI Editor

A: append at the end of line.

I: insert at begin of line.

i: insert at cursor.

a: at next cursor.

G: end of the file.

1G: Go to first line.

nG: goes to nth line.

G: end of file.

M: Middle of the screen.

H: HOME, ie, top left of the current screen.

nG: go to line n.

To search a word :?wordname

h: move cursor left one character

j: move cursor down one line

k: move cursor up one line

l: move cursor right one character

vi -r filename: recover filename that was being edited when system crashed.

o: open and put text in a new line below current line

O: open and put text in a new line above current line

r: replace single character under cursor

R: replace characters, starting with current cursor position

C: change (replace) the characters in the current line

cc: change (replace) the entire current line

dgg: delete above lines

dG: delete below lines

Command mode

Editing mode

Insert mode

Four ways of insert

i at the same cursor position

Capital A

To the end of the line.

Capital I

Begin of the line.
Small a

Next to the cursor position

To copy a single line
yy

5yy

for five lines copy

nyy
n number of lines can copy.

1G
Goes to first line

10G
Goes to tenth line

11G
Goes to eleventh line

nG
Goes to the nth position

1H
Goes up to the current screen

Capital G
Goes to the end of the page

yw
To copy a word

nyw
n number of words can be copied

x
One character delete

dw
One word delete

dd
Delete a line

ndd
n number of lines can be deleted

To replace a character

Type small r and then type character.

To replace many characters from the cursor position Type capital R

Clear cache in oracle R12

Unable to authenticate session. --Oracle apps R12
Bring down services of application then complile jsps

cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 2

Run autoconfig on both db and apps tiers

Bring up the services and test login
Cleared internet cache(browser cache)..