Oracle DBA and APPS DBA Things

shell script to check if the Workflow mailer is down in APPS R12

2011-11-17T16:52:00.005+03:00

Introduction:

We can use the following shell script and can schedule it with crontab. It is very handy, it will check the workflow mailer and if it is down it will send email to APPS DBA.

Cookbook:

Following is the script.

#!/bin/sh
## ORACLE_HOME=`cat /etc/var/opt/oratab | grep ^TESTDB: | cut -f2 -d':'`
ORACLE_HOME=/erptest/TST/db/tech_st/11.1.0
export ORACLE_HOME
ORACLE_SID=ERPTEST
export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin:.
export PATH
SCRIPT_DIR=/erptestdb/home/scripts
export SCRIPT_DIR
# check_ts.log
$ORACLE_HOME/bin/sqlplus -s test_user/test_user <spool /erptestdb/home/scripts/check_wf_mailer.log

set lines 1000
set head off
# column tablespace_name format a50
select COMPONENT_STATUS
from apps.FND_SVC_COMPONENTS
where component_type='WF_MAILER'
/

spool off
exit
!
#SCRIPT_DIR=/test/appl/homes/oratest/scripts

if [ -s $SCRIPT_DIR/check_wf_mailer.log ]; then
#COUNT=`cat check_ts.log | wc -l `
COUNT=0
STATUS=`cat $SCRIPT_DIR/check_wf_mailer.log | awk '{print $1}'`
VSTATUS='RUNNING'
echo "v status is: " $VSTATUS
echo "file is present"
echo "Script Dir: " $SCRIPT_DIR
echo "status is " $STATUS
if [ $STATUS != $VSTATUS ] ; then
echo "status is in the if stmt " $STATUS
mailx -s "Oracle Workflow Mailer is down in APPS R12, please check " appsdba@test.com < /erptestdb/home/scripts/check_wf_mailer.log

fi
fi

APPS R12.1.1 post cloning for Database Tier error - ORA-12547: TNS:lost contact

2011-10-13T14:45:00.002+03:00

Introduction:

On post cloning of database tier of APPS R12.1.1 , it ended with errors "ORA-12547: TNS:lost contact"

Resolution:

Following were the errors which we got in ApplyDbTier.
===============
LD_LIBRARY_PATH : /erptst/TST/db/tech_st/11.1.0/lib:/usr/dt/lib:/usr/openwin/lib:/erptst/TST/db/tech_st/11.1.0/ctx/lib:/erptst/TST/db/tech_st/11.1.0/lib32

Executable : /erptst/TST/db/tech_st/11.1.0/bin/sqlplus

The log information will be written to
/erptst/TST/db/tech_st/11.1.0/appsutil/log/TST_testmachine/adcrdb_TST.txt

Creating the control file for TST_testm1 database ...

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Oct 10 16:00:59 2011

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

ERROR:
ORA-12547: TNS:lost contact

ORA-12547: TNS:lost contact
ERROR:
ORA-12547: TNS:lost contact

ORA-12547: TNS:lost contact
SP2-0640: Not connected
==============================

We tried to connect from sqlplus but after few seconds of wait it again gave the same error.

We checked the inventory location and permission and it was all ok.

We tried again the post clone, but this time we choose the different SID as CLN and gave the different port number, previously it was 01 and this time we chose 79. And this time the post clone went successful without any errors, this is so weird, but this is just what happened!

Oracle Application APEX admin user password reset by script

2011-07-09T13:00:00.002+03:00

Introduction:
If you dont know the password for admin user of Oracle Application APEX, and if version of APEX is 3 or higer then the password of admin can be reset by a script.

CookBook:

There is script in the directory of apex softwae and the script name is apxchpwd.sql, this can be run with sys user on sqlplus and it will ask the new password for admin, just type the new password and use it.

Oracle Application APEX admin user

2011-07-09T01:27:00.002+03:00

Introduction:
How to create a second amdin user for Oracle Application APEX.

CookBook:

1- unlock the account FLOWS_030000 and login with FLOWS_030000 to Database where APEX was installed.

2- run the following procedure

begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);
wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'admin2',
p_email_address => 'admin2@test.com',
p_web_password => 'admin2') ;
end;
/

3- test the new admin account from the url

http:/appserver.test.com:7778/pls/apex/apex_admin

it will ask the new admin username and password, in our case the username is "admin2" and password is "admin2", once logged in, the APEX will ask to change the old password.

4- in case you dont know the password for FLOWS_030000, you can change the password and once run the procedure in point 2, the FLOWS_030000 should be locked.

To find the sid and serial# of a Concurrent Request in Oracle Financials R 11i and R12

2011-06-30T23:18:00.003+03:00

Objective:
Some times we need to kill a hang concurrent request, and to kill it, we need to find the sid and serial# of that concurrent request.

Cookbook:

Following SQL query can be run with APPS user on APPS Database , it will need to provide the concurrent request id, and then it will give the sid and serial# of that concurrent requests, more information can also be obtained from v$session and v$sqlarea views.

Following is the SQL query

----------
SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

---------

SQL Server 2008 error: The SQL Server failed to initialize VIA support library QLVipl.dll

2011-06-19T20:06:00.003+03:00

Introduction:
The Network protocol VIA was enabled, and after that SQL Server 2008 didnt startup and on checking the event log file, the following error was in the log file.

SQL Server The SQL Server failed to initialize VIA support library QLVipl.dll

CookBook:
On disabling the network protocol VIA, when SQL Server 2008 was started from SQL Server Configuration Manager , it started successfully.

Oracle APPS R12 cloning issue

2011-06-11T14:58:00.002+03:00

Introduction:
Having successfully cloned the db Tier, when we tried to clone the APPS Tier, it gave the following errors.

Uploading Metadata file /APPS/TST/apps/apps_st/appl/ad/12.0.0/admin/template/adctxinf.tmp
Metadata file /APPS/TST/apps/apps_st/appl/ad/12.0.0/admin/template/adctxinf.tmp upload failed.
Check log file created by FNDCPUCF program.
ERRORCODE = 137 ERRORCODE_END
.end std out.
ld.so.1: FNDCPUCF: fatal: /APPS/TST/apps/tech_st/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
Killed
ld.so.1: FNDCPUCF: fatal: /APPS/TST/apps/tech_st/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
Killed

CookBook:

When we checked the logs and error messages , we came to know that os user of the apps tier i.e. appltst doesnt have the permissions on the oraInventory location. Having made a new directory of oraInventory when we again ran the "perl adcfgclone.pl appsTier" it was all successful.

incomplete recovery of Oracle 11g R2 database with RMAN on secondary host

2011-05-11T14:27:00.006+03:00

Introduction:
In this post we will see how to recover the Oracle 11g database past in time (incomplete recovery) on the secondary host.

CookBook:

Following things were done to achieve the objective.

1- Installation of the Oracle 11g R2 on secondary host.

2- There should be connectivity between the secondary host, RMAN server and Target database.

3- Target database name is ORA11G, Recovery Manager database is RMAN, and the recovered database would be called as AUX database.

4- The target database hot backup was made by using RMAN and the destination was a mounted file system on the target database server.

Following is the rman script which was used to recover the database past in time (incomplete recovery).

run {
set until time "to_date('08-MAY-2011 18:00:00','DD-MON-YYYY HH24:MI:SS')";
allocate auxiliary channel ch2 device type disk;
allocate auxiliary channel ch1 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
set newname for tempfile 1 to '/FS_restore/oracle/database/oradata/AUX/temp01.dbf';
set newname for tempfile 2 to '/FS_restore/oracle/database/oradata/AUX/temp02.dbf';
set newname for tempfile 3 to '/FS_restore/oracle/database/oradata/AUX/temp03.dbf';
set newname for datafile 4 to '/FS_restore/oracle/database/oradata/AUX/users01.dbf';
set newname for datafile 3 to '/FS_restore/oracle/database/oradata/AUX/undotbs01.dbf';
set newname for datafile 2 to '/FS_restore/oracle/database/oradata/AUX/sysaux01.dbf';
set newname for datafile 1 to '/FS_restore/oracle/database/oradata/AUX/system01.dbf';
set newname for datafile 5 to '/FS_restore/oracle/database/oradata/AUX/EAN_T_1.dbf';
set newname for datafile 6 to '/FS_restore/oracle/database/oradata/AUX/EAN_T_2.dbf';
set newname for datafile 7 to '/FS_restore/oracle/database/oradata/AUX/PAST_T_1.dbf';
set newname for datafile 8 to '/FS_restore/oracle/database/oradata/AUX/PAST_T_2.dbf';
set newname for datafile 9 to '/FS_restore/oracle/database/oradata/AUX/ON_T_1.dbf';
set newname for datafile 10 to '/FS_restore/oracle/database/oradata/AUX/ON_T_2.dbf';
set newname for datafile 11 to '/FS_restore/oracle/database/oradata/AUX/NFE_T_1.dbf';
set newname for datafile 12 to '/FS_restore/oracle/database/oradata/AUX/NFE_T_2.dbf';
set newname for datafile 13 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf';
set newname for datafile 14 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf';
set newname for datafile 15 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_1.dbf';
set newname for datafile 16 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_2.dbf';
set newname for datafile 17 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf';
set newname for datafile 18 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf';
set newname for datafile 19 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf';
set newname for datafile 20 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf';
set newname for datafile 21 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_3.dbf';
set newname for datafile 22 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_4.dbf';
set newname for datafile 23 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_5.dbf';
set newname for datafile 24 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf';
set newname for datafile 25 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_6.dbf';
set newname for datafile 26 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf';
set newname for datafile 27 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf';
set newname for datafile 28 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf';
set newname for datafile 29 to '/FS_restore/oracle/database/oradata/AUX/undotbs02.dbf';
set newname for datafile 30 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf';
set newname for datafile 31 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_12.dbf';
set newname for datafile 32 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_13.dbf';
set newname for datafile 33 to '/FS_restore/oracle/database/oradata/AUX/others01.dbf';
set newname for datafile 34 to '/FS_restore/oracle/database/oradata/AUX/REPORTING_7.dbf';
set newname for datafile 35 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf';
set newname for datafile 36 to '/FS_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf';
DUPLICATE TARGET DATABASE TO AUX
LOGFILE GROUP 1 ('/FS_restore/oracle/database/oradata/AUX/auxredo01a.log','/FS_restore/oracle/database/oradata/AUX/auxredo01b.log') SIZE 500M REUSE,
GROUP 2 ('/FS_restore/oracle/database/oradata/AUX/auxredo02a.log', '/FS_restore/oracle/database/oradata/AUX/auxredo02b.log') SIZE 500M REUSE,
GROUP 3 ('/FS_restore/oracle/database/oradata/AUX/aux1redo03a.log','/FS_restore/oracle/database/oradata/AUX/aux1redo03b.log') SIZE 500M REUSE;
release channel ch2;
release channel ch1;
release channel ch3;
release channel ch4;
}

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

Here are the few scenarios which we discovered while doing the incomplete recovery.

Following is the error messages which we got.

channel ch2: starting datafile backup set restore
channel ch2: restoring control file
channel ch2: reading from backup piece /appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_02_04/o1_mf_s_742230501_6nqv1p5j_.bkp
channel ch2: ORA-19870: error while restoring backup piece /appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_02_04/o1_mf_s_742230501_6nqv1p5j_.bkp
ORA-19505: failed to identify file "/appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_02_04/o1_mf_s_742230501_6nqv1p5j_.bkp"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

failover to previous backup

released channel: ch2
released channel: ch1
released channel: ch3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/12/2011 21:10:29
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

Recovery Manager complete.

-------
When we studied the above messages we came to know that RMAN is not able to find the control file backup, as the file system it is showing in the error messages was the file system on the Target database, we made the same file system on the Secondary database server (AUX database) and copied the same directories which RMAN was trying to search and having done this we again run the same script and this time it didnt show the above errors.

after mounting the file system we again ran the rman script and this time it was able to restore the control file and mounted the database, following is the informatio from log file.
----------

Starting restore at 08-MAY-11

channel ch2: starting datafile backup set restore
channel ch2: restoring control file
channel ch2: reading from backup piece /appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_04_08/o1_mf_s_747924939_6sxpwvx2_.bkp
channel ch2: piece handle=/appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_04_08/o1_mf_s_747924939_6sxpwvx2_.bkp tag=TAG20110408T125539
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:03
output file name=/fs_restore/oracle/database/oradata/AUX/control01.ctl
output file name=/fs_restore/oracle/database/oradata/AUX/control02.ctl
Finished restore at 12-APR-11

database mounted
released channel: ch2
released channel: ch1
released channel: ch3

-----------

And following is the complete log of rman script.

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

Recovery Manager: Release 11.2.0.1.0 - Production on Wed MAY O8 05:10:55 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: REPORTING (DBID=418O888985)
connected to recovery catalog database
connected to auxiliary database: AUX (not mounted)

RMAN> run {
2> set until time "to_date('08-MAY-2011 18:00:00','DD-MON-YYYY HH24:MI:SS')";
3> allocate auxiliary channel ch2 device type disk;
4> allocate auxiliary channel ch1 device type disk;
5> allocate auxiliary channel ch3 device type disk;
6> allocate auxiliary channel ch4 device type disk;
7> set newname for tempfile 1 to '/fs_restore/oracle/database/oradata/AUX/temp01.dbf';
8> set newname for tempfile 2 to '/fs_restore/oracle/database/oradata/AUX/temp02.dbf';
9> set newname for tempfile 3 to '/fs_restore/oracle/database/oradata/AUX/temp03.dbf';
10> set newname for datafile 4 to '/fs_restore/oracle/database/oradata/AUX/users01.dbf';
11> set newname for datafile 3 to '/fs_restore/oracle/database/oradata/AUX/undotbs01.dbf';
O8> set newname for datafile 2 to '/fs_restore/oracle/database/oradata/AUX/sysaux01.dbf';
O8> set newname for datafile 1 to '/fs_restore/oracle/database/oradata/AUX/system01.dbf';
14> set newname for datafile 5 to '/fs_restore/oracle/database/oradata/AUX/EAN_T_1.dbf';
15> set newname for datafile 6 to '/fs_restore/oracle/database/oradata/AUX/EAN_T_2.dbf';
16> set newname for datafile 7 to '/fs_restore/oracle/database/oradata/AUX/PAST_1.dbf';
17> set newname for datafile 8 to '/fs_restore/oracle/database/oradata/AUX/PAST_T_2.dbf';
18> set newname for datafile 9 to '/fs_restore/oracle/database/oradata/AUX/ON_T_1.dbf';
19> set newname for datafile 10 to '/fs_restore/oracle/database/oradata/AUX/ON_T_2.dbf';
20> set newname for datafile 11 to '/fs_restore/oracle/database/oradata/AUX/NFE_T_1.dbf';
21> set newname for datafile O8 to '/fs_restore/oracle/database/oradata/AUX/NFE_T_2.dbf';
22> set newname for datafile O8 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf';
23> set newname for datafile 14 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf';
24> set newname for datafile 15 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_1.dbf';
25> set newname for datafile 16 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_2.dbf';
26> set newname for datafile 17 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf';
27> set newname for datafile 18 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf';
28> set newname for datafile 19 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf';
29> set newname for datafile 20 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf';
30> set newname for datafile 21 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_3.dbf';
31> set newname for datafile 22 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_4.dbf';
32> set newname for datafile 23 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_5.dbf';
33> set newname for datafile 24 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf';
34> set newname for datafile 25 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_6.dbf';
35> set newname for datafile 26 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf';
36> set newname for datafile 27 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf';
37> set newname for datafile 28 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf';
38> set newname for datafile 29 to '/fs_restore/oracle/database/oradata/AUX/undotbs02.dbf';
39> set newname for datafile 30 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf';
40> set newname for datafile 31 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf';
41> set newname for datafile 32 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf';
42> set newname for datafile 33 to '/fs_restore/oracle/database/oradata/AUX/others01.dbf';
43> set newname for datafile 34 to '/fs_restore/oracle/database/oradata/AUX/REPORTING_7.dbf';
44> set newname for datafile 35 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf';
45> set newname for datafile 36 to '/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf';
46> DUPLICATE TARGET DATABASE TO AUX
47> LOGFILE GROUP 1 ('/fs_restore/oracle/database/oradata/AUX/auxredo01a.log','/fs_restore/oracle/database/oradata/AUX/auxredo01b.log') SIZE 500M REUSE,
48> GROUP 2 ('/fs_restore/oracle/database/oradata/AUX/auxredo02a.log', '/fs_restore/oracle/database/oradata/AUX/auxredo02b.log') SIZE 500M REUSE,
49> GROUP 3 ('/fs_restore/oracle/database/oradata/AUX/aux1redo03a.log','/fs_restore/oracle/database/oradata/AUX/aux1redo03b.log') SIZE 500M REUSE;
50> release channel ch2;
51> release channel ch1;
52> release channel ch3;
53> release channel ch4;
54> }
55>
56>
executing command: SET until clause

allocated channel: ch2
channel ch2: SID=817 device type=DISK

allocated channel: ch1
channel ch1: SID=851 device type=DISK

allocated channel: ch3
channel ch3: SID=885 device type=DISK

allocated channel: ch4
channel ch4: SID=919 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at O8-MAY-11

contents of Memory Script:
{
set until scn 7345323564358;
sql clone "alter system set db_name =
''REPORTING'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone imNFEiate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set db_name = ''REPORTING'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 8554O87360 bytes

Fixed Size 2165208 bytes
Variable Size 822089256 bytes
Database Buffers 7700742144 bytes
Redo Buffers 29O80752 bytes
allocated channel: ch2
channel ch2: SID=817 device type=DISK
allocated channel: ch1
channel ch1: SID=851 device type=DISK
allocated channel: ch3
channel ch3: SID=885 device type=DISK
allocated channel: ch4
channel ch4: SID=919 device type=DISK

Starting restore at O8-MAY-11

channel ch2: starting datafile backup set restore
channel ch2: restoring control file
channel ch2: reading from backup piece /fs/appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_04_08/o1_mf_s_747924939_6sxpwvx2_.bkp
channel ch2: piece handle=/fs/appl/oracle/flash_recovery_area/REPORTING/autobackup/2011_04_08/o1_mf_s_747924939_6sxpwvx2_.bkp tag=TAG20110408TO85539
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:03
output file name=/fs_restore/oracle/database/oradata/AUX/control01.ctl
output file name=/fs_restore/oracle/database/oradata/AUX/control02.ctl
Finished restore at O8-MAY-11

database mounted
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/system01.dbf for datafile 1 with checkpoint SCN of 7344671914096
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/sysaux01.dbf for datafile 2 with checkpoint SCN of 7344688377360
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/undotbs01.dbf for datafile 3 with checkpoint SCN of 73446719142O8
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/users01.dbf for datafile 4 with checkpoint SCN of 7344688377360
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ean_T_1.dbf for datafile 5 with checkpoint SCN of 7344687623519
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ean_T_2.dbf for datafile 6 with checkpoint SCN of 7344687623519
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/PAST_T_1.dbf for datafile 7 with checkpoint SCN of 7344688214802
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/PAST_T_2.dbf for datafile 8 with checkpoint SCN of 7344688214802
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ON_T_1.dbf for datafile 9 with checkpoint SCN of 7344688214802
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ON_T_2.dbf for datafile 10 with checkpoint SCN of 7344688377360
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/NFE_T_1.dbf for datafile 11 with checkpoint SCN of 7344687333584
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/NFE_T_2.dbf for datafile O8 with checkpoint SCN of 7344687333584
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf for datafile O8 with checkpoint SCN of 73446719142O8
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf for datafile 14 with checkpoint SCN of 7344671914096
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_1.dbf for datafile 15 with checkpoint SCN of 73446719142O8
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_2.dbf for datafile 16 with checkpoint SCN of 7344671914096
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf for datafile 17 with checkpoint SCN of 7344687333584
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf for datafile 18 with checkpoint SCN of 7344687623519
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf for datafile 19 with checkpoint SCN of 7344688214802
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf for datafile 20 with checkpoint SCN of 7344688377360
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_3.dbf for datafile 21 with checkpoint SCN of 7344687333584
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_4.dbf for datafile 22 with checkpoint SCN of 7344687623519
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_5.dbf for datafile 23 with checkpoint SCN of 7344688214802
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf for datafile 24 with checkpoint SCN of 7344671914325
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_6.dbf for datafile 25 with checkpoint SCN of 7344688377360
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf for datafile 26 with checkpoint SCN of 7344671914325
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf for datafile 27 with checkpoint SCN of 7344687672604
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf for datafile 28 with checkpoint SCN of 7344687672604
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/undotbs02.dbf for datafile 29 with checkpoint SCN of 7344671914096
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf for datafile 30 with checkpoint SCN of 7344687672604
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf for datafile 31 with checkpoint SCN of 73446719142O8
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf for datafile 32 with checkpoint SCN of 7344671914096
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/others01.dbf for datafile 33 with checkpoint SCN of 73446719142O8
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/REPORTING_7.dbf for datafile 34 with checkpoint SCN of 7344671914325
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf for datafile 35 with checkpoint SCN of 7344687333584
Using previous duplicated file /fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf for datafile 36 with checkpoint SCN of 7344687623519

contents of Memory Script:
{
catalog clone datafilecopy "/fs_restore/oracle/database/oradata/AUX/system01.dbf",
"/fs_restore/oracle/database/oradata/AUX/sysaux01.dbf",
"/fs_restore/oracle/database/oradata/AUX/undotbs01.dbf",
"/fs_restore/oracle/database/oradata/AUX/users01.dbf",
"/fs_restore/oracle/database/oradata/AUX/ean_T_1.dbf",
"/fs_restore/oracle/database/oradata/AUX/ean_T_2.dbf",
"/fs_restore/oracle/database/oradata/AUX/PAST_T_1.dbf",
"/fs_restore/oracle/database/oradata/AUX/PAST_T_2.dbf",
"/fs_restore/oracle/database/oradata/AUX/ON_T_1.dbf",
"/fs_restore/oracle/database/oradata/AUX/ON_T_2.dbf",
"/fs_restore/oracle/database/oradata/AUX/NFE_T_1.dbf",
"/fs_restore/oracle/database/oradata/AUX/NFE_T_2.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_1.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_2.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_3.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_4.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_5.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_6.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf",
"/fs_restore/oracle/database/oradata/AUX/undotbs02.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf",
"/fs_restore/oracle/database/oradata/AUX/others01.dbf",
"/fs_restore/oracle/database/oradata/AUX/REPORTING_7.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf",
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf";
switch clone datafile 1 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/system01.dbf";
switch clone datafile 2 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/sysaux01.dbf";
switch clone datafile 3 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/undotbs01.dbf";
switch clone datafile 4 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/users01.dbf";
switch clone datafile 5 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ean_T_1.dbf";
switch clone datafile 6 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ean_T_2.dbf";
switch clone datafile 7 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/PAST_T_1.dbf";
switch clone datafile 8 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/PAST_T_2.dbf";
switch clone datafile 9 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ON_T_1.dbf";
switch clone datafile 10 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ON_T_2.dbf";
switch clone datafile 11 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/NFE_T_1.dbf";
switch clone datafile O8 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/NFE_T_2.dbf";
switch clone datafile O8 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf";
switch clone datafile 14 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf";
switch clone datafile 15 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_1.dbf";
switch clone datafile 16 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_2.dbf";
switch clone datafile 17 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf";
switch clone datafile 18 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf";
switch clone datafile 19 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf";
switch clone datafile 20 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf";
switch clone datafile 21 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_3.dbf";
switch clone datafile 22 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_4.dbf";
switch clone datafile 23 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_5.dbf";
switch clone datafile 24 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf";
switch clone datafile 25 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_6.dbf";
switch clone datafile 26 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf";
switch clone datafile 27 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf";
switch clone datafile 28 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf";
switch clone datafile 29 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/undotbs02.dbf";
switch clone datafile 30 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf";
switch clone datafile 31 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf";
switch clone datafile 32 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf";
switch clone datafile 33 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/others01.dbf";
switch clone datafile 34 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/REPORTING_7.dbf";
switch clone datafile 35 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf";
switch clone datafile 36 to datafilecopy
"/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/system01.dbf RECID=1 STAMP=748329314
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/sysaux01.dbf RECID=2 STAMP=748329314
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/undotbs01.dbf RECID=3 STAMP=748329315
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/users01.dbf RECID=4 STAMP=748329315
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ean_T_1.dbf RECID=5 STAMP=748329316
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ean_T_2.dbf RECID=6 STAMP=748329316
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/PAST_T_1.dbf RECID=7 STAMP=748329317
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/PAST_T_2.dbf RECID=8 STAMP=748329317
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ON_T_1.dbf RECID=9 STAMP=748329318
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ON_T_2.dbf RECID=10 STAMP=748329318
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/NFE_T_1.dbf RECID=11 STAMP=748329319
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/NFE_T_2.dbf RECID=O8 STAMP=748329320
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf RECID=O8 STAMP=748329320
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf RECID=14 STAMP=748329321
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_1.dbf RECID=15 STAMP=748329321
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_2.dbf RECID=16 STAMP=748329322
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf RECID=17 STAMP=748329322
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf RECID=18 STAMP=748329323
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf RECID=19 STAMP=748329324
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf RECID=20 STAMP=748329324
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_3.dbf RECID=21 STAMP=748329325
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_4.dbf RECID=22 STAMP=748329325
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_5.dbf RECID=23 STAMP=748329326
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf RECID=24 STAMP=748329326
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_6.dbf RECID=25 STAMP=748329327
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf RECID=26 STAMP=748329328
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf RECID=27 STAMP=748329328
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf RECID=28 STAMP=748329329
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/undotbs02.dbf RECID=29 STAMP=748329329
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf RECID=30 STAMP=748329330
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf RECID=31 STAMP=748329330
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf RECID=32 STAMP=748329331
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/others01.dbf RECID=33 STAMP=748329331
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_7.dbf RECID=34 STAMP=748329332
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf RECID=35 STAMP=748329332
cataloged datafile copy
datafile copy file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf RECID=36 STAMP=748329333

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=748329314 file name=/fs_restore/oracle/database/oradata/AUX/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=748329314 file name=/fs_restore/oracle/database/oradata/AUX/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=748329315 file name=/fs_restore/oracle/database/oradata/AUX/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=748329315 file name=/fs_restore/oracle/database/oradata/AUX/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=748329316 file name=/fs_restore/oracle/database/oradata/AUX/ean_T_1.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=748329316 file name=/fs_restore/oracle/database/oradata/AUX/ean_T_2.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=748329317 file name=/fs_restore/oracle/database/oradata/AUX/PAST_T_1.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=748329317 file name=/fs_restore/oracle/database/oradata/AUX/PAST_T_2.dbf

datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=748329318 file name=/fs_restore/oracle/database/oradata/AUX/ON_T_1.dbf

datafile 10 switched to datafile copy
input datafile copy RECID=10 STAMP=748329318 file name=/fs_restore/oracle/database/oradata/AUX/ON_T_2.dbf

datafile 11 switched to datafile copy
input datafile copy RECID=11 STAMP=748329319 file name=/fs_restore/oracle/database/oradata/AUX/NFE_T_1.dbf

datafile O8 switched to datafile copy
input datafile copy RECID=O8 STAMP=748329320 file name=/fs_restore/oracle/database/oradata/AUX/NFE_T_2.dbf

datafile O8 switched to datafile copy
input datafile copy RECID=O8 STAMP=748329320 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_1.dbf

datafile 14 switched to datafile copy
input datafile copy RECID=14 STAMP=748329321 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_2.dbf

datafile 15 switched to datafile copy
input datafile copy RECID=15 STAMP=748329321 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_1.dbf

datafile 16 switched to datafile copy
input datafile copy RECID=16 STAMP=748329322 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_2.dbf

datafile 17 switched to datafile copy
input datafile copy RECID=17 STAMP=748329322 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_3.dbf

datafile 18 switched to datafile copy
input datafile copy RECID=18 STAMP=748329323 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_4.dbf

datafile 19 switched to datafile copy
input datafile copy RECID=19 STAMP=748329324 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_5.dbf

datafile 20 switched to datafile copy
input datafile copy RECID=20 STAMP=748329324 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_6.dbf

datafile 21 switched to datafile copy
input datafile copy RECID=21 STAMP=748329325 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_3.dbf

datafile 22 switched to datafile copy
input datafile copy RECID=22 STAMP=748329325 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_4.dbf

datafile 23 switched to datafile copy
input datafile copy RECID=23 STAMP=748329326 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_5.dbf

datafile 24 switched to datafile copy
input datafile copy RECID=24 STAMP=748329326 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_7.dbf

datafile 25 switched to datafile copy
input datafile copy RECID=25 STAMP=748329327 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_6.dbf

datafile 26 switched to datafile copy
input datafile copy RECID=26 STAMP=748329328 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_8.dbf

datafile 27 switched to datafile copy
input datafile copy RECID=27 STAMP=748329328 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_9.dbf

datafile 28 switched to datafile copy
input datafile copy RECID=28 STAMP=748329329 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_10.dbf

datafile 29 switched to datafile copy
input datafile copy RECID=29 STAMP=748329329 file name=/fs_restore/oracle/database/oradata/AUX/undotbs02.dbf

datafile 30 switched to datafile copy
input datafile copy RECID=30 STAMP=748329330 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_11.dbf

datafile 31 switched to datafile copy
input datafile copy RECID=31 STAMP=748329330 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf

datafile 32 switched to datafile copy
input datafile copy RECID=32 STAMP=748329331 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_O8.dbf

datafile 33 switched to datafile copy
input datafile copy RECID=33 STAMP=748329331 file name=/fs_restore/oracle/database/oradata/AUX/others01.dbf

datafile 34 switched to datafile copy
input datafile copy RECID=34 STAMP=748329332 file name=/fs_restore/oracle/database/oradata/AUX/REPORTING_7.dbf

datafile 35 switched to datafile copy
input datafile copy RECID=35 STAMP=748329332 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_14.dbf

datafile 36 switched to datafile copy
input datafile copy RECID=36 STAMP=748329333 file name=/fs_restore/oracle/database/oradata/AUX/ORA11G_DATA_15.dbf

contents of Memory Script:
{
set until time "to_date('08-MAY-2011 18:00:00','DD-MON-YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at O8-MAY-11

starting NFEia recovery

channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
archived log thread=1 sequence=O86447
channel ch2: restoring archived log
archived log thread=1 sequence=O86448
channel ch2: restoring archived log
archived log thread=1 sequence=O86449
channel ch2: restoring archived log
archived log thread=1 sequence=O86450
channel ch2: restoring archived log
archived log thread=1 sequence=O86451
channel ch2: restoring archived log
archived log thread=1 sequence=O86452
channel ch2: restoring archived log
archived log thread=1 sequence=O86453
channel ch2: restoring archived log
archived log thread=1 sequence=O86454
channel ch2: restoring archived log
archived log thread=1 sequence=O86455
channel ch2: restoring archived log
archived log thread=1 sequence=O86456
channel ch2: reading from backup piece /fs_backup/arch_bkp/REPORTING_arch_O874_1_747665115
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=O86457
channel ch1: restoring archived log
archived log thread=1 sequence=O86458
channel ch1: restoring archived log
archived log thread=1 sequence=O86459
channel ch1: restoring archived log
archived log thread=1 sequence=O86460
channel ch1: restoring archived log
archived log thread=1 sequence=O86461
channel ch1: restoring archived log
archived log thread=1 sequence=O86462
channel ch1: restoring archived log
archived log thread=1 sequence=O86463
channel ch1: restoring archived log
archived log thread=1 sequence=O86464
channel ch1: restoring archived log
archived log thread=1 sequence=O86465
channel ch1: restoring archived log
archived log thread=1 sequence=O86466
channel ch1: restoring archived log
archived log thread=1 sequence=O86467
channel ch1: restoring archived log
archived log thread=1 sequence=O86468
channel ch1: restoring archived log
archived log thread=1 sequence=O86469
channel ch1: restoring archived log
archived log thread=1 sequence=O86470
channel ch1: restoring archived log
archived log thread=1 sequence=O86471
channel ch1: restoring archived log
archived log thread=1 sequence=O86472
channel ch1: restoring archived log
archived log thread=1 sequence=O86473
channel ch1: restoring archived log
archived log thread=1 sequence=O86474
channel ch1: restoring archived log
archived log thread=1 sequence=O86475
channel ch1: restoring archived log
archived log thread=1 sequence=O86476
channel ch1: restoring archived log
archived log thread=1 sequence=O86477
channel ch1: restoring archived log
archived log thread=1 sequence=O86478
channel ch1: restoring archived log
archived log thread=1 sequence=O86479
channel ch1: restoring archived log
archived log thread=1 sequence=O86480
channel ch1: restoring archived log
archived log thread=1 sequence=O86481
channel ch1: restoring archived log
archived log thread=1 sequence=O86482
channel ch1: restoring archived log
archived log thread=1 sequence=O86689
channel ch3: reading from backup piece /fs_backup/arch_bkp/REPORTINGrpt_arch_O881_1_747837919
channel ch2: piece handle=/fs_backup/arch_bkp/REPORTINGrpt_arch_O874_1_747665115 tag=TAG20110405TO845O8
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 01:28:05
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86447_727790523.arc thread=1 sequence=O86447
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86447_727790523.arc RECID=40862 STAMP=748334746
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86448_727790523.arc thread=1 sequence=O86448
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86448_727790523.arc RECID=40825 STAMP=748333359
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86449_727790523.arc thread=1 sequence=O86449
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86449_727790523.arc RECID=40863 STAMP=748334748
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86450_727790523.arc thread=1 sequence=O86450
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86450_727790523.arc RECID=40826 STAMP=748333360
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86451_727790523.arc thread=1 sequence=O86451
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86451_727790523.arc RECID=40749 STAMP=748331040
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86452_727790523.arc thread=1 sequence=O86452
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86452_727790523.arc RECID=40776 STAMP=748331782
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86453_727790523.arc thread=1 sequence=O86453
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86453_727790523.arc RECID=40827 STAMP=748333362
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86454_727790523.arc thread=1 sequence=O86454
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86454_727790523.arc RECID=40750 STAMP=748331042
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86455_727790523.arc thread=1 sequence=O86455
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86455_727790523.arc RECID=40724 STAMP=748330244
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86456_727790523.arc thread=1 sequence=O86456
channel clone_default: deleting archived log(s)
archived log file name=/fs_backup/archv/AUX/REPORTINGRPT_1_O86456_727790523.arc RECID=40775 STAMP=748331781
channel ch2: starting archived log restore to default destination
channel ch2: restoring archived log
archived log thread=1 sequence=O86690
channel ch2: restoring archived log
archived log thread=1 sequence=O86691
channel ch2: restoring archived log
archived log thread=1 sequence=O86692
channel ch2: restoring archived log
archived log thread=1 sequence=O86693
channel ch2: restoring archived log
archived log thread=1 sequence=O86694
channel ch2: restoring archived log
archived log thread=1 sequence=O86695
channel ch2: restoring archived log
archived log thread=1 sequence=O86696
channel ch2: restoring archived log
archived log thread=1 sequence=O86697
channel ch2: restoring archived log
archived log thread=1 sequence=O86698
channel ch2: restoring archived log
archived log thread=1 sequence=O86699
channel ch2: restoring archived log
archived log thread=1 sequence=O86700
channel ch2: restoring archived log
archived log thread=1 sequence=O86701
channel ch2: restoring archived log
archived log thread=1 sequence=O86702
NFEia recovery complete, elapsed time: 00:54:14
Finished recover at 08-MAY-11

contents of Memory Script:
{
shutdown clone imNFEiate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone imNFEiate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 8554O87360 bytes

Fixed Size 2165208 bytes
Variable Size 838866472 bytes
Database Buffers 7683964928 bytes
Redo Buffers 29O80752 bytes
allocated channel: ch2
channel ch2: SID=817 device type=DISK
allocated channel: ch1
channel ch1: SID=851 device type=DISK
allocated channel: ch3
channel ch3: SID=885 device type=DISK
allocated channel: ch4
channel ch4: SID=919 device type=DISK

sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 8554O87360 bytes

Fixed Size 2165208 bytes
Variable Size 838866472 bytes
Database Buffers 7683964928 bytes
Redo Buffers 29O80752 bytes
allocated channel: ch2
channel ch2: SID=817 device type=DISK
allocated channel: ch1
channel ch1: SID=851 device type=DISK
allocated channel: ch3
channel ch3: SID=885 device type=DISK
allocated channel: ch4
channel ch4: SID=919 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGPASTORY 18688
LOGFILE
GROUP 1 ( '/FS_restore/oracle/database/oradata/AUX/auxredo01a.log', '/FS_restore/oracle/database/oradata/AUX/auxredo01b.log' ) SIZE 500 M REUSE,
GROUP 2 ( '/FS_restore/oracle/database/oradata/AUX/auxredo02a.log', '/FS_restore/oracle/database/oradata/AUX/auxredo02b.log' ) SIZE 500 M REUSE,
GROUP 3 ( '/FS_restore/oracle/database/oradata/AUX/aux1redo03a.log', '/FS_restore/oracle/database/oradata/AUX/aux1redo03b.log' ) SIZE 500 M REUSE
DATAFILE
'/FS_restore/oracle/database/oradata/AUX/system01.dbf'
CHARACTER SET AR8ISO8859P6

contents of Memory Script:
{
set newname for tempfile 1 to
"/FS_restore/oracle/database/oradata/AUX/temp01.dbf";
set newname for tempfile 2 to
"/FS_restore/oracle/database/oradata/AUX/temp02.dbf";
set newname for tempfile 3 to
"/FS_restore/oracle/database/oradata/AUX/temp03.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/FS_restore/oracle/database/oradata/AUX/sysaux01.dbf",
"/FS_restore/oracle/database/oradata/AUX/undotbs01.dbf",
"/FS_restore/oracle/database/oradata/AUX/users01.dbf",
"/FS_restore/oracle/database/oradata/AUX/EAN_T_1.dbf",
"/FS_restore/oracle/database/oradata/AUX/EAN_T_2.dbf",
"/FS_restore/oracle/database/oradata/AUX/PAST_T_1.dbf",
"/FS_restore/oracle/database/oradata/AUX/PAST_T_2.dbf",
"/FS_restore/oracle/database/oradata/AUX/ON_T_1.dbf",
"/FS_restore/oracle/database/oradata/AUX/ON_T_2.dbf",
"/FS_restore/oracle/database/oradata/AUX/NEF_T_1.dbf",
"/FS_restore/oracle/database/oradata/AUX/NEF_T_2.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_1.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_2.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_1.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_2.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_3.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_4.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_5.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_6.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_3.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_4.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_5.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_7.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_6.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_8.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_9.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_10.dbf",
"/FS_restore/oracle/database/oradata/AUX/undotbs02.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_11.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf",
"/FS_restore/oracle/database/oradata/AUX/others01.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_7.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_14.dbf",
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_15.dbf";
switch clone datafile all;
switch clone datafile 2 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/sysaux01.dbf";
switch clone datafile 3 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/undotbs01.dbf";
switch clone datafile 4 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/users01.dbf";
switch clone datafile 5 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/EAN_T_1.dbf";
switch clone datafile 6 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/EAN_T_2.dbf";
switch clone datafile 7 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/PAST_T_1.dbf";
switch clone datafile 8 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/PAST_T_2.dbf";
switch clone datafile 9 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/ON_T_1.dbf";
switch clone datafile 10 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/ON_T_2.dbf";
switch clone datafile 11 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/NFE_T_1.dbf";
switch clone datafile O8 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/NFE_T_2.dbf";
switch clone datafile O8 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_1.dbf";
switch clone datafile 14 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_2.dbf";
switch clone datafile 15 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_1.dbf";
switch clone datafile 16 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_2.dbf";
switch clone datafile 17 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_3.dbf";
switch clone datafile 18 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_4.dbf";
switch clone datafile 19 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_5.dbf";
switch clone datafile 20 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_6.dbf";
switch clone datafile 21 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_3.dbf";
switch clone datafile 22 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_4.dbf";
switch clone datafile 23 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_5.dbf";
switch clone datafile 24 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_7.dbf";
switch clone datafile 25 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_6.dbf";
switch clone datafile 26 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_8.dbf";
switch clone datafile 27 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_9.dbf";
switch clone datafile 28 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_10.dbf";
switch clone datafile 29 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/undotbs02.dbf";
switch clone datafile 30 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_11.dbf";
switch clone datafile 31 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf";
switch clone datafile 32 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf";
switch clone datafile 33 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/others01.dbf";
switch clone datafile 34 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_7.dbf";
switch clone datafile 35 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_14.dbf";
switch clone datafile 36 to datafilecopy
"/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_15.dbf";
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renaNFE tempfile 1 to /FS_restore/oracle/database/oradata/AUX/temp01.dbf in control file
renaNFE tempfile 2 to /FS_restore/oracle/database/oradata/AUX/temp02.dbf in control file
renaNFE tempfile 3 to /FS_restore/oracle/database/oradata/AUX/temp03.dbf in control file

cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/sysaux01.dbf RECID=1 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/undotbs01.dbf RECID=2 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/users01.dbf RECID=3 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/EAN_T_1.dbf RECID=4 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/EAN_T_2.dbf RECID=5 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/PAST_T_1.dbf RECID=6 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/PAST_T_2.dbf RECID=7 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/ON_T_1.dbf RECID=8 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/ON_T_2.dbf RECID=9 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/NFE_T_1.dbf RECID=10 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/NFE_T_2.dbf RECID=11 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_1.dbf RECID=O8 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_2.dbf RECID=O8 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_1.dbf RECID=14 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_2.dbf RECID=15 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_3.dbf RECID=16 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_4.dbf RECID=17 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_5.dbf RECID=18 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_6.dbf RECID=19 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_3.dbf RECID=20 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_4.dbf RECID=21 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_5.dbf RECID=22 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_7.dbf RECID=23 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_6.dbf RECID=24 STAMP=748392203
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_8.dbf RECID=25 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_9.dbf RECID=26 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_10.dbf RECID=27 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/undotbs02.dbf RECID=28 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_11.dbf RECID=29 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf RECID=30 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf RECID=31 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/others01.dbf RECID=32 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_7.dbf RECID=33 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_14.dbf RECID=34 STAMP=748392204
cataloged datafile copy
datafile copy file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_15.dbf RECID=35 STAMP=748392204

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/EAN_T_1.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/EAN_T_2.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/PAST_T_1.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/PAST_T_2.dbf

datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/ON_T_1.dbf

datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/ON_T_2.dbf

datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/NFE_T_1.dbf

datafile O8 switched to datafile copy
input datafile copy RECID=11 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/NFE_T_2.dbf

datafile O8 switched to datafile copy
input datafile copy RECID=O8 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_1.dbf

datafile 14 switched to datafile copy
input datafile copy RECID=O8 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_2.dbf

datafile 15 switched to datafile copy
input datafile copy RECID=14 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_1.dbf

datafile 16 switched to datafile copy
input datafile copy RECID=15 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_2.dbf

datafile 17 switched to datafile copy
input datafile copy RECID=16 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_3.dbf

datafile 18 switched to datafile copy
input datafile copy RECID=17 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_4.dbf

datafile 19 switched to datafile copy
input datafile copy RECID=18 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_5.dbf

datafile 20 switched to datafile copy
input datafile copy RECID=19 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_6.dbf

datafile 21 switched to datafile copy
input datafile copy RECID=20 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_3.dbf

datafile 22 switched to datafile copy
input datafile copy RECID=21 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_4.dbf

datafile 23 switched to datafile copy
input datafile copy RECID=22 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_5.dbf

datafile 24 switched to datafile copy
input datafile copy RECID=23 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_7.dbf

datafile 25 switched to datafile copy
input datafile copy RECID=24 STAMP=748392203 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_6.dbf

datafile 26 switched to datafile copy
input datafile copy RECID=25 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_8.dbf

datafile 27 switched to datafile copy
input datafile copy RECID=26 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_9.dbf

datafile 28 switched to datafile copy
input datafile copy RECID=27 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_10.dbf

datafile 29 switched to datafile copy
input datafile copy RECID=28 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/undotbs02.dbf

datafile 30 switched to datafile copy
input datafile copy RECID=29 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_11.dbf

datafile 31 switched to datafile copy
input datafile copy RECID=30 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf

datafile 32 switched to datafile copy
input datafile copy RECID=31 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_O8.dbf

datafile 33 switched to datafile copy
input datafile copy RECID=32 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/others01.dbf

datafile 34 switched to datafile copy
input datafile copy RECID=33 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTINGRPT_7.dbf

datafile 35 switched to datafile copy
input datafile copy RECID=34 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_14.dbf

datafile 36 switched to datafile copy
input datafile copy RECID=35 STAMP=748392204 file name=/FS_restore/oracle/database/oradata/AUX/REPORTING_DATA_15.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 08-MAY-11

released channel: ch2

released channel: ch1

released channel: ch3

released channel: ch4

Recovery Manager complete.

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

login with sysdba ORA-01031: insufficient privileges

2011-02-25T17:39:00.002+03:00

Introduction:

If you try to connect with sysdba from client machine, it might give you the following error

ORA-01031: insufficient privileges

Cookbook:

You need to create the password for in $ORACLE_HOME/dbs location, and following is an example to create the password file.

$ orapwd file=$ORACLE_HOME/dbs/orapwSID password=type_password entries

once you run this command on the Oracle Server, then again try to login from the client with sysdba, it will work.

RMAN-20002: target database already registered in recovery catalog

2011-01-31T17:07:00.001+03:00

Introduction:
We moved one the Oracle 10g database from one server to other server, and database was opened with "resetlogs" option. And when we tried to do the register the database it gave the following error
"RMAN-20002: target database already registered in recovery catalog"

Cookbook:

To resolve the issue we issued the following command on rman prompt and then every thing was fine. Following are the errors and command which we ran

----------
RMAN> register database;

starting full resync of recovery catalog
full resync complete
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of register command on default channel at 01/29/2011 22:00:48
RMAN-20002: target database already registered in recovery catalog

RMAN> reset database;

database incarnation already registered
----------

Oracle 10g AS R2 Webcache error - Permission denied when setting group ID

2010-10-02T04:36:00.002+03:00

Objective:
On startup of the Oracle 10g AS R2 middle tier, we got the following errors.

Error
--> Process (pid=9185)
failed to start a managed process after the maximum retry limit
Log:
/u04/ora9i/ora10gAS/mtier/opmn/logs/WebCache~WebCache~1

And when we checked from the Enterprise Manager and opmn status command, it was showing that Portal and WebCache failed to start.

CookBook:

When we checked the event.log file under $ORACLE_HOME/webcache/logs directory it was showing the following messages.

[02/Oct/2010:04:15:21 +0300] [notification 9612] [ecid: -] OracleAS Web Cache 10g (10.1.2), Build 10.1.2.0.2 050802
[02/Oct/2010:04:15:21 +0300] [notification 9403] [ecid: -] Maximum number of file/socket descriptors set to 900.
[02/Oct/2010:04:15:21 +0300] [notification 13002] [ecid: -] Maximum allowed incoming connections are 700
[02/Oct/2010:04:15:21 +0300] [alert 9526] [ecid: -] Permission denied when setting group ID ( staff ).
[02/Oct/2010:04:15:21 +0300] [alert 13003] [ecid: -] Configuration error. Stopping admin or cache server.
[02/Oct/2010:04:15:21 +0300] [alert 9000] [ecid: -] Process 8259 exit(1) at 589:xmlcfglsnr.c [Build 10.1.2.0.2 050802]

We found the error "Permission denied when setting group ID ( staff )".

and on checking the logged in user, we found that user has the group other than "staff".

we reset the group to staff, and restarted the application and every thing was fine.

Oracle APPS R12 Post Cloning issue - Form not launching

2010-09-22T22:47:00.003+03:00

Introduction:
We faced one of the issue, after successfull cloning of APPS R12.1.1, when we tried to open the form, it was not launching.

CookBook:

We did the following steps, and form started coming up.

1- Stopped the APPS Tier services.

2- rename the directory "tldcache" under following directories.

a- /binaries/INST_NAME/inst/apps/INST_NAME_MACHINE_NAME/ora/10.1.3/j2ee/oafm

b- /binaries/INST_NAME/inst/apps/INST_NAME_MACHINE_NAME/ora/10.1.3/j2ee/oacore

c- /binaries/INST_NAME/inst/apps/INST_NAME_MACHINE_NAME/ora/10.1.3/j2ee/forms

3- create the emplty directory with the name "tldcache" under the above directories.

4- restart the APPS Tier services.

5- test the issue.

how to restart the failed APPS R12 installation

2010-09-14T16:50:00.003+03:00

Objective:
In this post we will show how can we restart the failed APPS R12 installation.

Cookbook:
The installation can fail becuase of any reason, e.g. system hang, system restart etc. In any of the case we can restart the installation from the same point where it got failed.

Following are the points.

1- start the rapidwiz with the option "-restart"

./rapidwiz -restart

2- it will open the install screen and will as the saved configuration file. Here is the catch, the saved configuration file can be found on two places.

a- /tmp/MMDDHHMI/conf_SID.txt

b- $ORACLE_HOME/appsutil/conf_SID.txt

3- once you choose the right saved configuration file, the installer will start from the same point where it got failed.

dbc file recreation for Oracle APPS R12

2010-06-25T15:49:00.002+03:00

Introduction:
In this post, we would see how can we create the "dbc" file, if it is missing for some reason.

Cookbook:

The "dbc" file location in Oracle APPS R12 is $FND_SECURE. If for some reason it is missing or invalid, then it can be recreated.

Following is the command which can be used to recreate the "dbc" file.

java oracle.apps.fnd.security.AdminAppServer apps/apps ADD FNDNAM=apps GWYUID=applsyspub/pub TWO_TASK=VIS SECURE_PATH=$FND_TOP/secure GUEST_USER_PWD=guest/oracle APPS_JDBC_DRIVER_TYPE=THIN DB_HOST=appsdemo.test.com DB_PORT=1531

The only thing needs to be taken care is replace the values with respect to the environment, like the values for $FNDNAM, hostname, port number etc.

Enterprise Manager DB Console set up for Oracle APPS R12 database 11g

2010-05-20T12:34:00.003+03:00

Introduction:
In this article we will configure the Enterprise Manager DB console for Oracle 11g database for Oracle APPS R12.

CookBook:
Following points needs to be done.
1- Check for the "sysman" schema in 11g database, if it exist then it means there should be DB Console setup, to check this we can run the following statement,
$ emctl start dbconsole
if this successfull start the DB console then it means the EM DB console has already been configured, and if this statement returns any error, then better to resolve the issues or we can drop and recreate the DB Console. But before dropping the DB Console better to take the backup for the configuration files and database repository.

2- To drop the existing DB Console setup, we can use the following command.

/bin/emca -deconfig dbcontrol db -repos drop

This command will drop both the configuration files and database repository, and if this command is not working for some issues, we can go with manually deletion of the configuration files and database repository. Following commands can be used for manual deletion.

a- To drop the database repository
Step 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYSMAN
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE);

Step 2: Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM 'r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM 'r1.owner'.'r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

b- To drop the configuraiton files

/
/oc4j/j2ee/OC4J_DBConsole__

3- now we can use the following command to create the database repository and configuration files for DB Console.

-bash-3.00$ emca -config dbcontrol db -repos recreate
^C-bash-3.00$
-bash-3.00$
-bash-3.00$
-bash-3.00$ emca -config dbcontrol db -repos create
STARTED EMCA at May 20, 2010 9:32:40 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: ORA
Listener port number: 1581
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /oracle/oradb/tech_st/11.1.0
Local hostname ................ APPSDBServer.test.com
Listener port number ................ 1581
Database SID ................ ORA
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
May 20, 2010 9:33:13 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/oradb/tech_st/11.1.0/cfgtoollogs/emca/ORA/emca_2010_05_20_09_32_40.log.
May 20, 2010 9:33:16 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 20, 2010 10:16:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
May 20, 2010 10:16:31 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
May 20, 2010 10:23:33 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
May 20, 2010 10:23:41 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
May 20, 2010 10:23:41 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
May 20, 2010 10:24:27 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
May 20, 2010 10:24:27 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
May 20, 2010 10:25:12 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
May 20, 2010 10:25:12 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 20, 2010 10:27:21 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 20, 2010 10:27:21 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://APPSDBServer.test.com:1158/em <<<<<<<<<<<
May 20, 2010 10:27:37 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /oracle/oradb/tech_st/11.1.0/APPSDBServer.test.com_ORA/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 20, 2010 10:27:37 AM

4- Now the EM DB Console can be accessed by the following link.

https://APPSDBServer.test.com:1158/em

Access Forms directly in Oracle Applications R12

2010-05-18T15:40:00.002+03:00

Introduction:
In this article we would look how we can access the forms directly in Oracle Applications R12.
Cook Book:
Following steps needs to be done to access the forms directly.
1- Take the backup of $CONTEXT_FILE in $INST_TOP/appl/admin
2- change the value "SECURE" to "OFF" for parameter s_appserverid_authentication, in the above file.
3- shutdown all the services.
4- run the autoconfig for all the application tiers even if the APPL_TOP is shared.
5-start all the services.
6- access the direct forms link from the following url.
http://appsserver.test.com:8000/forms/frmservlet

Oracle E-Business R12 form and plsql library manual compilation

2010-04-02T01:57:00.002+03:00

Introduction:

In this post we will see how to compile the form and pl sql library in Oracle E-Business R12 manually.

Cookbook:

Identify the requirement, i.e. which form or pl sql library needs to compiled, following are the two examples.

a- to compile the form manually

/appstest/apps/oraapp/tech_st/10.1.2/bin/frmcmp_batch module=/appstest/apps/oraapp/apps_st/appl/au/12.0.0/forms/US/APXIISIM.fmb userid=APPS/***** output_file=/appstest/apps/oraapp/apps_st/appl/ap/12.0.0/forms/US/APXIISIM.fmx module_type=form batch=yes compile_all=special

b- to compile the pl sql library

/appstest/apps/oraapp/tech_st/10.1.2/bin/frmcmp_batch module=/appstest/apps/oraapp/apps_st/appl/au/12.0.0/resource/POXBWVRP.pll userid=APPS/**** output_file=/appstest/apps/oraapp/apps_st/appl/au/12.0.0/resource/POXBWVRP.plx module_type=library batch=yes compile_all=special

Test Cases for Oracle E-Bussines R12 project

2010-02-09T13:45:00.007+03:00

Introduction:
This article is related to the test cases for Oracle E-Business R12 project with respect to infrastructure i.e. Database tier and application tier.

Cookbook:
After completion of the Oracle E-Business R12 project, the environment needs to be handed over to the oniste engineers. The following document is a kind of template to do the test cases to make it sure that the infrastructure is working properly as agreed, the test features and tests can be changed with respect to each site and concerns.

Oracle 9i Application Server R2 X-Server change

2010-02-03T12:45:00.002+03:00

Introduction:
For Oracle 9i Application Server R2, there is requirement for the X Server, reports needs to have the X Server to run successfully, in this article we will see how to change the X Server from one machine to other machine.

Cookbook:
The X Server can easily be changed for the reports server, also there are many X Server available, the one is VNC X Server.

For Oracle 9i Application Server R2, there are 2 configuration files which needs to be altered if the X Server loaction has changed.

Follwing are the 2 files.
- reports.sh
- oc4j.properties

The location for the reports.sh is $ORACLE_HOME/bin/ and for the oc4.properties is $ORACLE_HOME/j2ee/OC4J_BI_Forms/config/.

Once the above files have been altered, then reports services needs to be restarted either from the Enterprise Manager or from the command line.

From the command line, we can start the reports servecis as follows..

$ORACLE_HOME/bin/rwserver.sh server=rep_machinename

Oracle APPS 12 (12.1.1) installation on Oracle Enterprise Linux R4 ( update 7) on Vmware server

2010-01-01T19:29:00.010+03:00

Introduction:
This article is related to the installation of Oracle APPS 12 (12.1.1) on Oracle Enterprise Linux R4 on Vmware Serv er.

Cookbook:
We used the following software.

1- Oracle APPS 12 (12.1.1)
this can be downloaded from http://edelivery.oracle.com
2- Oracle Enterprise Linux R4 (update 7)
3- Vmware Server

Following are the steps which we followed. Also this article is not related to the installation of Vmware Server and Oracle Enterprise Linux R4. We will concentrate only on APPS 12 installation.

1- Following rpm(s) needs to be installed on Oracle Enterprise Linux R4, we installed only the bold ones, as only those were missing for this release.

glibc-2.3.4-2.25
glibc-common-2.3.4-2.25
binutils-2.15.92.0.2-21
compat-libstdc++-296-2.96-132.7.2
gcc-3.4.6-3
gcc-c++-3.4.6-3
libgcc-3.4.6-3
libstdc++-3.4.6-3
libstdc++-devel-3.4.6-3
openmotif21-2.1.30-11.RHEL4.6**
pdksh-5.2.14-30.3
setarch-1.6-1
make-3.80-6.EL4
gnome-libs-1.4.1.2.90-44.1
sysstat-5.0.5-11.rhel4
compat-db-4.1.25-9
control-center-2.8.0-12.rhel4.5
xscreensaver-4.18-5.rhel4.11
libaio-0.3.105-2libaio-devel-0.3.105

Following web site can be used to download any missing rpms.
http://www.filewatcher.com

2- Following kernel parameters needs to be set in /etc/sysctl.conf file.

kernel.semmsl 256*
kernel.semmns 32000*
kernel.semopm 100*
kernel.semmni 142*
kernel.shmall 2097152
kernel.shmmax Half the size of the physical memory (in bytes), and at least 2147483648
kernel.shmmni 4096
kernel.msgmax 8192
kernel.msgmnb 65535
kernel.msgmni 2878
fs.file-max 65536
net.ipv4.ip_local_port_range 10000 65000**
net.core.rmem_default 262144
net.core.rmem_max 262144
net.core.wmem_default 262144
net.core.wmem_max 262144

3- Following OS utilities should exist.
ar
gcc
g++
ld
ksh
make
X Display Server

4- we also need to create 2 OS users i.e. oracle to own oracle database and applmgr to own apps tier.

5- For this test installation we can create only one directory i.e. /d01/oracle, this would contain the db tier and apps tier.

6- After creating the stage from the APPS 12 media, we can run the rapidwiz installer and following screens show the steps to be followed. And hopefull it would go very smooth, without any problms.

This last screen shows that APPS 12 has been installed successfully, we can use the sysadmin username with the same password and can run any request to check the forms and concurrent manager.

Oracle 10g R2 Database 10.2.0.4 - Part 3 of the series Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC

2009-12-30T15:46:00.002+03:00

Introduction:
This article is the last of the series "Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC". This last part is very easy and we will fetch the data from informix into Oracle and will see that all the configurations which was done is working properly.

Cookbook:
1- Install the Oracle 10g R2 (10.2.0.1).
2- Path Oracle 10g R2 (10.2.0.1) to 10.2.0.4. For Sun Sparc 64 bit platform, we need to down load the patch p6810189_10204_Solaris-64.zip.
3- After applying the patch, now we need to change the tnsnames.ora file of Oracle 10g R2 (10.2.0.4) database and need to add the following entry.

DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=oragateway_host)(PORT=1522))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)

4- we can test this tns entry by tnsping, following is the successfull test.

bash-2.05$ tnsping DG4ODBC

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 30-DEC-2009 16:26:41

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=oragateway_host)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))
OK (0 msec)
bash-2.05$

5- Now we can open the SQL session and fetch the data from the informix database.
SQL> select * from "emp"@dg4odbc;
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------Tyler Bennett E10297 32000 D101 1Tyler Bennett E10297 32000 D101 1John Rappl E21437 47000 D050 1George Woltman E00127 53500 D101 1Adam Smith E63535 18000 D202 0David McClellan E04242 41500 D101 1Rich Holcomb E01234 49500 D202 1Nathan Adams E41298 21900 D050 0Richard Potter E43128 15900 D101 0David Motsinger E27002 19250 D202 0Tim Sampair E03033 27000 D101 1
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------Kim Arlich E10001 57000 D190 1Timothy Grove E16398 29900 D190 1Tyler Bennett E10297 32000 D101 1Tyler Bennett E10297 32000 D101 1John Rappl E21437 47000 D050 1George Woltman E00127 53500 D101 1Adam Smith E63535 18000 D202 0David McClellan E04242 41500 D101 1Rich Holcomb E01234 49500 D202 1Nathan Adams E41298 21900 D050 0Richard Potter E43128 15900 D101 0
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------David Motsinger E27002 19250 D202 0Tim Sampair E03033 27000 D101 1Kim Arlich E10001 57000 D190 1Timothy Grove E16398 29900 D190 1
26 rows selected.

The important point is the table name is case sensitive and should be enclosed.

Conculsion:
We have seen in these 3 articles how to fetch data from informix database into Oracle database, this was the testing, any one who wants to implement this into production needs to see the compatibility of the products.

Oracle Database Gateways 11g (11.1.0.6.0) - Part 2 of the series Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC"

2009-11-25T11:30:00.026+03:00

Introduction:
This is the 2nd of the article of the series "Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC"
In this post we will do the installation of Oracle Database Gateways 11g for ODBC and will configure the listener so it could talk to ODBC, which we had configured in the previous article.

Also we have to patch this Oracle Database Gateways 11g 11.1.0.6.0 to 11.1.0.7. We need to download the patch 6890831.
Cookbook:
Installation of Oracle Database Gateways 11g (11.1.0.6):
Following are the images of install screen.

You need to select the option "Oralce Database Gateways for ODBC", I selected the option, but that is hidden in the following screen :)

After this installation, we need to apply the patch 6890831, and the version of Oracle Database Gateways 11g would be 11.1.0.7.0.

Configuration:

Now after the installtion and patching, we have to configure the Oracle Database Gateways 11g for ODBC, the metalink document "How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) [ID 561033.1]" can be followed to do this configuration.

Following is the configuration details.

1- Environment variables:

Following are the environment variables which was set with respect to Oracle Database Gateways 11g for ODBC.

ORACLE_HOME=/u04/oragateway/gateway

PATH=/usr/openwin/bin/xauth:/usr/bin:/usr/ucb:/etc:/usr/local/bin:/usr/X/bin:/u04/oragateway/gateway/bin:.

2- edit the file "initdg4odbc.ora" at the path $ORACLE_HOME/hs/admin, also after the change copy the file to $ORACLE_HOME

following is my initdg4odbc.ora

HS_FDS_CONNECT_INFO = Informix HS_FDS_TRACE_LEVEL = DEBUG HS_FDS_SHAREABLE_NAME = /export/home/odbc1/odbc/Connect64forODBC60/lib/libodbc.so
## ODBC specific environment variables#set ODBCINI= /export/home/odbc1/odbc/Connect64forODBC60/odbc.ini

3- configure the listener for Oracle Database Gateways 11g. Following is the configurtaion of the listener.ora at the path $ORACLE_HOME/network/admin

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = tabsmt04.mtcvb.com)(PORT = 1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=/u04/oragateway/gateway)
(ENVS=LD_LIBRARY_PATH=/export/home/odbc1/odbc/Connect64forODBC60/lib:/u04/oragateway/gateway/hs/lib:/u04/oragateway/gateway/hs/lib32:/u04/oragateway/gateway/lib)
(PROGRAM=dg4odbc)
)
)

4- configure the tnsnames.ora file, following is the confiugration details.

DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=tabsmt04)(PORT=1522))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)

5- verify the listener with the tnspin command.

bash-2.05$ tnsping dg4odbc

TNS Ping Utility for Solaris: Version 11.1.0.7.0 - Production on 25-NOV-2009 16:06:38

Copyright (c) 1997, 2008, Oracle. All rights reserved.

Used parameter files:
/u04/oragateway/gateway/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=tabsmt04)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))
OK (10 msec)
Used parameter files:
/u04/oragateway/gateway/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=tabsmt04)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))

This shows the successfull connection, in the next article we will see to install and patch the Oracle 10g R2 database and will configure the Oracle 10g R2 database so we could fetch the data from Infomix database.

Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC

2009-10-28T09:02:00.021+03:00

Introduction:

In this series of articles we will see how to fetch data from Informix database into Oracle Database.

There can be 2 methods to do this, or might be more :)

The use of Oracle Gateway 11g for Informix or Oracle Gateway 11g for ODBC, the first option requires the license from Oracle Support, and the second has no license requirement. But in second option we need to have a ODBC driver for Informix which is a licensed from the vendor.

Cook Book:

We would test the fetching of data from Informix database into Oracle database by the use of the second option, i.e. we would need the Oracle Gateway 11g for ODBC and offcourse we need the ODBC driver for Informix database, which can be downloadable freely for a trial.

We need the following software for this testing.

1- ODBC driver for Informix database

2- Oracle 10g R2 (10.2.0.4) database (in which we will fetch the data from Informix database)

3- Oracle Gateway 11g for ODBC (11.0.7.0)

4- Informix database

In this article we will install the ODBC driver and connect this with the Informix database to check the connectivity with the Informix at ODBC level. This is most important step, as if it is successfull only then we can move forward to the next steps.

1- ODBC driver for Informix installation and configuration:

Installation:

The ODBC driver can be downloaded from the www.datadirect.com site for a trial free of cost. We would downlad the evsol64.tar.Z, this is for the Sun Sparc 64 bit.

We have the Sun Sparc machine for the testing, so we downloaded the ODBC driver for Informix for 64 bit Sun Sparc version.

For the ODBC installation, it is better to create a OS user "odbc" user, which will own the ODBC binaries. The installation is simple and straightforward. And it will take around 300MB - 350MB approx.

The environment variable ODBC_HOME can be set to the installation directory, and the PATH environment variable also can be set for the ODBC binaries / executables.

Configuration:

After the installation, we need to configure the ODBC driver for Informix so it could connect to the Informix and can fetch data at the ODBC level.

For the configuration we need to edit the "odbc.ini" file, this is the only file which needs to be configured for the ODBC connectivity, and if once it is configured successfully then the rest would be very easy. Always remember this is the main core file for the connectivity of Informix database to Oracle Database.

If the file is checked, we can see that there are lot of sections inside this "odbc.ini" file, and every section has its own purpose.

First section in the odbc.ini file is [ODBC data sources], and it contains all the information regarding all the databases which can be configured with this ODBC driver. e.g. Infromix, Sybase, DB2, mySQL, Teradata, PostgreSQL, SqlServer, Greenplum etc

The second section is [ODBC] contains the installation directory, trace level, and trace file directory.

The remaining sections will contain the required information to connect to respective databases, and for this article we are concerned with the section [Informix].

We need to change the parameters related to the Informix server. Following parameters needs to be modified.

1- Database - The name of Informix database to connect

2- HostName - The machine name which hosts the Informix database

3- LogonID - Informix database userid

4- Password - Password for the Informix database userid

5- PortNumber - Informix database portnumber

6- ServerName - This can be found out from ONFONGI file and there is parameter DBSERVERNAME

After setting these parameters in the odbc.ini file, we need to test the configuration, whether it connects to the Informix database and fetches the records at ODBC level.

Testing the configuration:

For a testing it is better to create a test user on the Informix database. Once user created on Informix database, we need to create a test table and insert some records into the test table. In the ODBC installation directory there is "demo" directory, there are sql scripts for each database, we can use the script "empinformix.sql" script to create a test table. We used Sql Razor tool to connect to the Informix database with the test user and created the table emp and isnerted some records into this table.

The utiltiy "demoodbc" can be used to test the ODBC connectivity with Informix database.

the syntax of "demoodbc" is

$ demoodbc -uid username -pwd password Informix Database Source name (from the file odbc.ini)

We created the user as "test" on Informix database and password is abc12345 and the data source name is "Informix" in the odbc.ini file, now we can test the ODBC connection as follows...

$ demodbc -uid test -pwd abc12345 Informix

for this execution we needs to be in the directory "demo" inside the ODBC installation directory.

on the execution of the above statement we got the following error messages....

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

bash-2.05$ demoodbc -uid test -pwd abc12345 Informix

demoodbc DataDirect Technologies, Inc. ODBC Sample Application.

demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -329

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -329

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -329

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.

SQLConnect: Retrying Connect.

No connection could be established.

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

having seen the error message "carefully" we found that there is issue with the database name, when we checked the [Informix] section of the odbc.ini file, we found that the value for parameter "Database" is incorrect, on correction and re-execution of the "demoodbc" we got the following messages....

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

demoodbc DataDirect Technologies, Inc. ODBC Sample Application.

demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -761

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -761

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -761

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.

SQLConnect: Retrying Connect.

No connection could be established.

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

having seen the error messages, we corrected the Servername parameter value in the odbc.ini file, we got the correct value from the ONFONFIG file from the Informix database machine. after correcting this value and again executing the "demoodbc" , it was successfull with the following messages :)

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

bash-2.05$ demoodbc -uid test -pwd abc12345 Informix

demoodbc DataDirect Technologies, Inc. ODBC Sample Application.

demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.

First Name Last Name Hire Date Salary Dept

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

Tyler Bennett 32000 D101

John Rappl 47000 D050

George Woltman 53500 D101

Adam Smith 18000 D202

David McClellan 41500 D101

Rich Holcomb 49500 D202

Nathan Adams 21900 D050

Richard Potter 15900 D101

David Motsinger 19250 D202

Tim Sampair 27000 D101

Kim Arlich 57000 D190

Timothy Grove 29900 D190

Tyler Bennett 32000 D101

John Rappl 47000 D050

George Woltman 53500 D101

SQLFetch returns: SQL_NO_DATA_FOUND

In the next article we will move forward with installation, configuration and testing of Oracle Database Gateways 11g 11.1.0.6 and patching to 11.1.0.7, and then installation of the Oracle 10g R2, patching to 10.2.0.4.

Passed Oracle 11i APPS DBA OCP Exam

2009-09-26T17:37:00.003+03:00

I wanted to share a good news with you all, that I passed the Oracle 11i APPS DBA OCP Exam.

The exam was easy, but it took lot of time to prepare the exam, as there were 30 chapters in the course contents.

I have preapred some tips for myself, I will post them soon.

have a nice day.

kcrrwkx: nothing to do (end)

2009-09-07T09:57:00.002+03:00

Introduciton:
We got the following error message in one of the trace file in our background dump dest of Oracle 10g R2 (10.2.0.1) database.

kcrrwkx: nothing to do (end)

Troubleshooting and Resolution:
On searching of different forums and metalink, we found the note number: 372364.1, and according to the note, the message is safely ignored, and the only impact would be the size of the trace file, which needs to be purged manually or with cron job. The fix of this bug is in Oracle 11g or Oralce R2 10.2.0.3.

WARNING! Recovering data file 1 from a fuzzy file. If not the current file

2009-08-27T11:35:00.006+03:00

Introduction:

We use EMC BCV feature to clone our production database, and it was all working fine, but suddenly we started getting the errors , on recovery process. Following were the errors..

"WARNING! Recovering data file 1 from a fuzzy file. If not the current fileit might be an online backup taken without entering the begin backup command."

Troubleshooting and Resolution:

It was very interesting situation, as the same process which we were following was not working suddenly, our team tried the same procedure twice and every time they got the errors on recovery process. And we were following the below BCV cloning procedure.

Steps for cloning by use of BCV:

1- put the production database in backup mode.

2- sync the BCV volumes.

3- split the BCV volumes.

4- take the production database out of backup mode.

5- start the recovery on the cloned database.

6- open the database after successfull recovery.

We tried twice with the same proceudre but we were getting following errors..
---------------------------------------------
ALTER DATABASE RECOVER database using backup controlfile until cancel
Wed Aug 6 13:03:00 2009
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
---------------------------------------------

and we opend the cases with Oracle and EMC, but the error was showing some thing not correct with some of the procedure, either the production database didnt go properly in the backup mode, but when we checked the alert log, the database went to backup mode correctly, and it came out of the backup mode properly. Having seen the alert log, we suspected that something went wrong with BCV etc. But we were not sure, as we didnt get any error messages during the BCV sync and split.

Then we decided to give a one more try with the same procedure, and when we checked the team, they were not following the listed procedure, as they were doing split of the BCV after taking the database out of backup mode, whereas they should have done the split before the database taken out of backup mode. When we followed the same listed procedure, every thing was ok, and we were able to do successfull recovery of the database and able to open the database. :)

Oracle APPS related documents

2009-08-25T21:06:00.005+03:00

Oracle APPS related documents
I found the following document list from metalink note # 828157.1, this is complete Oracle APPS library which surely is very useful for every APPS professional.

And here are the document list, which is an asset for every APPS professional.

Document List

Java
----------------------------------------------------------------------------------------------------
Note 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
Note 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12
Note 384249.1 Using Latest Update of JDK 5.0 with Oracle E-Business Suite Release 12

Note 300482.1 Overview of Using Java with Oracle E-Business Suite Release 11i
Note 401561.1 Using J2SE Version 6 with Oracle E-Business Suite 11i
Note 304099.1 Using J2SE Version 5.0 with Oracle E-Business Suite 11i, Release 11.5.10
Note 246105.1 Upgrading to J2SE 1.4.2 with Oracle Applications 11i
Note 130091.1 Upgrading Oracle Applications 11i to use JDK 1.3

JRE/Jinitiator
----------------------------------------------------------------------------------------------------
Note 389422.1 Recommended Browsers for Oracle E-Business Suite Release 12
Note 393931.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12

Note 285218.1 Recommended Browsers for Oracle Applications 11i
Note 290807.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
Note 124606.1 Upgrading JInitiator with Oracle E-Business Suite 11i

Forms
----------------------------------------------------------------------------------------------------
Note 437878.1 Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
Note 750359.1 Upgrading to Previous Version of OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
Note 384241.1 Using Forms Socket Mode with Oracle E-Business Suite Release 12
Note 438652.1 R12 Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications

Note 125767.1 Upgrading Developer 6i with Oracle Applications 11i
Note 232313.1 Information on Previous Versions of Developer 6i Patchsets
Note 150168.1 Obtaining Forms Runtime Diagnostics (FRD) In Oracle Applications 11i
Note 760250.1 Diagnosing Forms Mouse Focus Problems Using JRE in Release 11i

OracleAS
----------------------------------------------------------------------------------------------------br>Note 454811.1 Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12
Note 743518.1 Starting up AS10g services in an EBusiness Suite Release 12 environment

JDBC
----------------------------------------------------------------------------------------------------
Note 557194.1 monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite

JVM
----------------------------------------------------------------------------------------------------
Note 362851.1 Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12
Note 567551.1 Configuring various JVM tuning parameters for Oracle E-Business suite 11i and R12
Note 370583.1 Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i

Apache/mod_jserv
----------------------------------------------------------------------------------------------------
Note 230688.1 11i Basic Apache/mod_jserv Troubleshooting with Hello.class

Debug/Trace
----------------------------------------------------------------------------------------------------
Note 422419.1 R12 - How To Enable and Collect Debug for HTTP, OC4J and OPMN
Note 443671.1 R12 - Collecting Configuration Information for HTTP, OC4J and OPMN
Note 427848.1 How to Enable Execution Context ID (ECID) in the R12 access_log
Note 455154.1 R12: How To Obtain A Thread Dump When OC4J is shutdown

Note 603390.1 How To Create a User Event Trace in R11i / R12

OA Framework
----------------------------------------------------------------------------------------------------
Note 391554.1 Oracle Application Framework Documentation Resources, Release 12

Note 275880.1 Oracle Application Framework Release 11i Documentation Road Map
Note 275875.1 Oracle Application Framework Troubleshooting Release 11i (11.5.10)
Note 357597.1 How To Generate A SQL Trace In OA Framework For Oracle Applications

WebCache
----------------------------------------------------------------------------------------------------
Note 380486.1 Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12
Note 306653.1 Installing and Configuring Web Cache 10.1.2 and Oracle E-Business Suite 11i

JDeveloper
----------------------------------------------------------------------------------------------------
Note 330236.1 Configuring JDeveloper For Use With Oracle Applications 11i and R12
Note 357218.1 Troubleshooting JDeveloper setup for Oracle Applications
Note 787209.1 How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12

Performance
----------------------------------------------------------------------------------------------------
Note 244040.1 bde_last_analyzed.sql - Verifies CBO Statistics
Note 74605.1 bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i instance
Note 396009.1 Database Initialization Parameters for Oracle Applications Release 12
Note 216205.1 Database Initialization Parameters for Oracle Applications 11i
Note 744143.1 Tuning performance on eBusiness suite
Note 169935.1 Troubleshooting Oracle Applications Performance Issues
Note 244040.1 Oracle E-Business Suite Recommended Performance Patches

Security
----------------------------------------------------------------------------------------------------
Note 738923.1 Oracle E-Business Suite Releases 11i and 12 Critical Patch Update note (January 2009)
Note 403537.1 Best Practices for Securing Oracle E-Business Suite Release 12
Note 189367.1 Best Practices for Securing the E-Business Suite Release 11i

Ihelp
----------------------------------------------------------------------------------------------------
Note 740834.1 R12: Troubleshooting iHelp

Advanced Configurations
----------------------------------------------------------------------------------------------------
Note 380483.1 Oracle E-Business Suite Release 12 Additional Configuration and Deployment Options
Note 217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
Note 233428.1 Sharing the Application Tier File System in Oracle Applications 11i
Note 233436.1 Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
Note 364439.1 Tips and Queries for Troubleshooting Advanced Topologies

DMZ
----------------------------------------------------------------------------------------------------
Note 380490.1 Oracle E-Business Suite R12 Configuration in a DMZ
Note 726953.1 Case History: Implementing a Reverse Proxy Alone in the DMZ Configuration - R12
Note 287176.1 DMZ Configuration with Oracle E-Business Suite 11i
Note 438744.1 Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11i
Note 460564.1 Hints and Tips for Troubleshooting the URL Firewal

Loadbalancer
----------------------------------------------------------------------------------------------------
Note 380489.1 Using Load-Balancers with Oracle E-Business Suite Release 12
Note 727171.1 Implementing Load Balancing On Oracle E-Business Suite - Documentation For Specific Load Balancer Hardware
Note 601694.1 How To Check Session Persistence On BigIP F5 And Cisco Ace Load Balancer Appliances
Note 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite Release 12

SSL
----------------------------------------------------------------------------------------------------
Note 376700.1 Using SSL with Oracle E-Business Suit Release 12
Note 123718.1 11i A Guide to Understanding and Implementing SSL for Oracle Applications

Portal
----------------------------------------------------------------------------------------------------
Note 305918.1 Using Oracle Portal 10g with Oracle E-Business Suite Release 11i

SSO/OID
----------------------------------------------------------------------------------------------------
Note 376811.1 Integrating Oracle E-Business Suite Release 12 with 10g AS Oracle Internet Directory and Oracle Single Sign-On

Note 261914.1 Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
Note 444573.1 Basic checks for user integration when using Oracle E-Business Suite 11i with Oracle AS 10g

Webcenter
----------------------------------------------------------------------------------------------------
Note 557221.1 Oracle WebCenter 10g Application Creation and Deployment Guide for Oracle E-Business Suite Release 12

Secure Enterprise Search
----------------------------------------------------------------------------------------------------
Note 566097.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.1.1
Note 744820.1 Oracle E-Business Suite Secure Enterprise Search Best Practices, Release 12
Note 462377.1 Installing Oracle E-Business Suite Secure Enterprise Search, Release 12
Note 740499.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.0.6
Note 726239.1 Oracle E-Business Suite Secure Enterprise Search Troubleshooting Guidelines, Release 12

SOA/BPEL/Webservices
----------------------------------------------------------------------------------------------------
Note 556540.1 Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.1
Note 726414.1 Oracle E-Business Suite Integrated SOA Gateway Troubleshooting Guide, Release 12
Note 755067.1 Using Oracle BPEL 10g with E-Business Suite Release 12.1.1
Note 755069.1 EBS R12 Packager and Adapters for External Application Servers
Note 782455.1 How Can I Expose E-Business Suite Integration Interfaces as Web Services in Release 11i and R12.0

MWA
----------------------------------------------------------------------------------------------------
Note 782162.1 MWA Troubleshooting Tips for Release 12
Note 269991.1 MWA Troubleshooting Tips for Release 11i

Certified/Supported
----------------------------------------------------------------------------------------------------
http://blogs.oracle.com/stevenchan/certifications.html
Note 363827.1 Rebaselined Oracle Applications Technology Components for Releases 11.5.7, 11.5.8, 11.5.9, and 11.5.10

WEBADI
----------------------------------------------------------------------------------------------------
Note 417692.1 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
Note 452452.1 R12 Installing, Configuring and Toubleshooting Web ADI (Web Applications Desktop Integrator)
Note 294739.1 How to enable tracing for WEBADI
Note 726989.1 List of Patches in Web Applications Desktop Integrator (Web ADI) for Releases 11i and 12

Diagnostics
----------------------------------------------------------------------------------------------------
Note 167000.1 E-Business Suite Diagnostics Installation Guide
Note 421245.1 E-Business Suite Diagnostics References for R12
Note 179661.1 E-Business Suite Diagnostics 11i Test Catalog

Installation
----------------------------------------------------------------------------------------------------
Note 761564.1 Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86
Note 402310.1 Oracle Applications Installation and Upgrade notes Release 12 (12.0) for Linux (32-bit)
Note 406982.1 Cloning Oracle Applications Release 12 with Rapid Clone
Note 559518.1 Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone

Note 421409.1 Unbreakable Linux enviroment check before R12 install
Note 458533.1 How to Enable Enterprise Manager on the Oracle E-Business Suite Release 12
Note 603716.1 Using AS10g AS Control with eBusiness Suite Rel 12
Note 452120.1 How to locate the log files and troubleshoot RapidWiz for R12
Note 316806.1 Oracle Applications Installation Update notes, Release 11i (11.5.10.2)
Note 230672.1 Cloning Oracle Applications Release 11i with Rapid Clone

Note 207296.1 How to Find out Linux Version Information?

Database
----------------------------------------------------------------------------------------------------
Note 454750.1 Oracle Apps Release 12 with Oracle Database 10.2.0 interoperability notes
Note 735276.1 Interoperability notes E-Business Suite R12 with Oracle Database 11gR1
Note 388577.1 Configuring Oracle Applications Release 12 with 10g R2 RAC
Note 466649.1 Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12

Note 216550.1 Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note 362203.1 Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Note 452783.1 Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
Note 362135.1 Configuring Oracle Applications Release 11i with 10g R2 RAC and ASM

Note 554539.1 Using Database Partitioning with Oracle E-Business Suite

Oracle APPS 12i Best Practices

2009-08-21T22:04:00.002+03:00

Oracle APPS 12i Best Practices

I wanted to share a very nice document on Oracle Metalink, it is a MUST READ for every Oracle APPS professional.... so here you go....

Subject: Best Practices for Adopting Oracle E-Business Suite, Release 12
Doc Id: 580299.1

Report Server issue in Oracle 9i Application Server R2 .....

2009-08-14T20:36:00.003+03:00

Introduction:
This post is related to describe the Report server issue with Oracle 9i AS R2. For this release of Application Server, the X Server is mandatory to run the report server. If there is any issue with the X Server then the Report Server will not work and users would be waiting to print their reprots.

Troubleshooting and Resolution:
We faced interesting issue with our reprot server for Oracle 9i AS R2. And following is procedure by which we successfully resolved the report server issues.

--> Report Server was not coming up, and the error message was following.

Error
REP-50001: Server is initializing

We waited a lot, but it was showing "REP-50001: Server is initializing", the command to run the report server was not throwing the following error after some time:

REP-56097: Engine rwEng-0 callback timeout

We used following command to run the report server:

./rwserver.sh server=rep_testserver4

and it was throwing error REP-56097, even we created different report server with different names, but the behavior was same, it was always throwing REP-50001, REP-56097.

Also we gathered the traces, but no clue was there, also X Server was working fine, in our case we were using vnc X Server. We raised a SR with Oracle Support, and they did many testings with the report server, but nothing worked.

And suddenly what we did we tried to check the X Server from vnc viewer and the report server windows was displayed as soon as we got the X Server console, and when we executed the reports it was all working. So the clue was we had to open the vnc X server console once to get the reports windows, and every thing was working fine. And later we closesd that vnc X Server window, and even then the reports were working all fine.

In Oracle 10g AS R2, there is no requirement for X Server for reports server. We would be migrating our Oracle 9i AS R2 to Oracle 10g AS R2 soon.

How to apply patch in Oracle Financials 11i

2009-08-10T15:13:00.008+03:00

Introduction:
The following document shows how to apply patch in Oracle Financials 11i with multi node isntallation.

Steps to apply patch:
These steps assuems that Oralce Financials 11i is installed on 2 nodes. Following are the details.

node1 : Concurrent Manager and Database
node2: Forms and Webserver

Patch is always applied on the recommendations of Oracle support, and norally they fix some bugs or they enhance some functionality.

The patch can contain the driver files, they can be either "copy", "database" and "generate" driver files. Or they can be in "unified" driver files.

Following steps show how to apply the patch which contains "c", "d" and "g" driver files.

1- Copy the patch on each node.
2- Shutdown all the services on Application tiere, only the database and listener should remain up and running.
3- Enable the maintenance mode by using "adadmin" tool.
4- Start the "adpatch" utility to apply the patch in the following order.
a- run the copy (c.drv) on every APPL_TOP of every node.
b- run the database driver (d.drv) from administration node.
c- run the generate (g.drv) on every APPL_TOP of Application nodes.
5- Disable the maintenance mode.
6- Restart the services.
7- Check the Applications by running one request.

Oracle Replication Broken jobs and how to fix them...

2009-07-27T20:33:00.004+03:00

Objective:

This article is related to Replication broken jobs. It will demonstrate how to build the broken jobs automatically and logging of the information. The auto build of the broken job is a great thing, which should be implemented for every replication setup. If there are many replication setups, then it can give the real benefit. And the DBA / Replication Administrator can see the logs to have an idea of the broken jobs. This article concentrates on the Multi Master Advanced Replication setup.

Introduction:

Replication makes the data available on more than one location. It offers redundancy at the database level. Replication works over long distances and WANS. It provides a copy of the database at other location. Some of the reasons to go for Replication are Availability, performance, disconnected computing, network load reduction,

The Replication is a complex environment, and it is difficult to maintain, because of its complexity. Normally the data which transfers from one database to another database can be out (no sync) with the original copy, the problem can be because of the broken jobs. In Multi Master Advanced Replication the data is pushed with the help of job queue. If the connectivity between the Replicated Databases becomes broken, then the job will try to push the data 16 times. And after 16 times the status of that particular job will be marked as “Y”, which means that job has broken. Now the transactions will start getting queued till the job is build again. The longer the time between making the broken job, the longer the queue, with transactions. So if the transactions start getting stuck, the data will not reach to the destination, and both the copies will start to become out of sync. Now imagine if there are many Replication setups, the network connectivity problem may arise any time, and no DBA / Replication Administrator can monitor the Replication setups all the times.

Mechanics:

The steps mentioned here should be run on the Test Replication Setup first.

The broken jobs can be made either manual which is truly painful. (Only those can imagine who are handling the Replication Setups). The other option is to make the broken jobs automatically. In this section we will demonstrate how we can build the broken jobs automatically.

Steps:

1- Login to the Database

1- Build the Log Table.

The log table can be used to log every make of the broken job. So after some time, the DBA or Replication Administrator can see the log tables, and can get an idea how frequently the jobs are breaking.

Name Null? Type

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

JOB NUMBER

BROKEN VARCHAR2(1)

FAILURES NUMBER

REMARKS VARCHAR2(50)

2- Build the procedure to make the Broken Jobs

This procedure will check the broken job, and if there is any broken job, it will make it and will log the information as well.

CREATE OR REPLACE

PROCEDURE UNBREAK_JOBS_TEST

/* Procedure to make the broken jobs in replication

This procedure will check the broekn jobs and if any

job is broken, then it will make them again

cursor test_broken_jobs is

select job,broken,failures

from dba_jobs

where upper(what) like '%DBMS_DEFER_SYS%' and broken='Y';

err_num number;

err_msg varchar2(150);

BEGIN

for broken_job in test_broken_jobs

loop

begin

/* dbms_job.broken(broken_job.job,FALSE,SYSDATE+10/86400); */

insert into broken_jobs values (broken_job.job,broken_job.broken,

broken_job.failures,to_char(sysdate,'dd mon yyyy hh24:mi,ss'));

commit;

/* dbms_job.run(broken_job.job); */

exception

WHEN OTHERS THEN

err_num := SQLCODE;

err_msg := SUBSTR(SQLERRM, 1, 100);

INSERT INTO job_errors VALUES (err_num,

err_msg,to_char(sysdate,'dd mon yyyy hh24:mi,ss'));

commit;

END;

end loop;

end;

3- Submit the job in the Database.

The UNBREAK_JOBS_TEST can be submitting with the help of DBMS_JOB to make it auto run.

SQL> variable jobno number;

SQL> begin

dbms_job.submit(:jobno,'UNBREAK_JOBS_TEST;',sysdate,'sysdate+90/(60*60*24));

commit;

end;

The job will run after each 90 seconds i.e. 3 minutes, this can be set to the particular requirement of the Replication Setup.

4- Test the UNBREAK_JOBS_TEST procedure.

Now the replication job can be broken by use of DBMS_JOB.BROKEN(JOB_NUMBER,TRUE);

Its broken status can be checked by the SQL statement:

SQL> select job,broken,failures

from dba_jobs

where upper(what) like '%DBMS_DEFER_SYS%'

And after wait of 90 seconds, again run the query the above job should be made by that UNBREAK_JOBS_TEST procedure.

Conclusion:

By testing this thoroughly, this can be implemented on the Production Replication setups. It can make the life of DBA and Replication Administrators easy.

huge ons.log size in Oracle 10g AS Infra

2009-06-10T10:36:00.003+03:00

Introduction:
We had an issue of huge size of ons.log file under Oracle 10g AS R2 infra tier, and it was making the 1.4 GB ons.log files and in days it was filling more then 20GB file system space.

Troubleshooting:
When we checked the ons.log file, it was having the following errors, and these errors were keep coming in the ons.log files.

--------------------
09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor
--------------------

In this test machien we had the following Oracle products installed.

1- Oracle 10g R2 Database
2- Oracle 10g AS R2 (infra and middle tier)
3- Oracle 10g AS R3 (SOA)

We raised a SR with Oracle support, and they came up that there is port conflict between one of the ons of above Oracle Products.

Resolution:

We checed the opmn/conf files from all the Oracle products, and found the port conflict between Oracle 10g R2 Database ons and Oracle 10g AS R2 infra ons.

There were following options to be applied.

1- The TNS listener should be configured so it would not let ons subscribe to it. (this option is for Oracle 10g R2 Database listener.) Following settings were recommended by Oracle support.

Following needs to be added in the listener.ora file.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

2- rename the ons.config file for the Oracle 10g R2 Database, so that ons should not be started and try to be subscribed to listener.

3- the last but not the least option would be to change the conflicted ports in the ons.config file of Oracle 10g R2 Database.

we changed the ports in ons.config file, but first we made sure that new ports should not be used in the system, we did this by checking netstat -rn command on Solaris box. After resetting the ports we stopped the ons, listener and restarted the ons and listener and every thing was fine.

Database crashed in manual hotbackup mode...

2009-04-28T13:13:00.008+03:00

Introduction:

One of our test database crashed, while it was in manual hotbackup mode. And when we tried to open the database, it didnt comeup as expected, becuase all the datafiles were in backup mode, and needed recovery. We were doing the manual hotbackup becuase of some limitations of the environment, where we were not able to use RMAN.

Troubleshooting and Resolution:

Before doing a blind recovery, we checked the alert log of the database, and searched theh v$backup view, to see which files were in backup mode. And then we initiated the recovery.

1- We checked the alert log file and found the following entries.

Thu Mar 16 04:02:57 2009
ARC0: Evaluating archive log 3 thread 1 sequence 52
ARC0: Beginning to archive log 3 thread 1 sequence 52
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/TEST/arch/TEST_s52.arc'
ARC0: Completed archiving log 3 thread 1 sequence 52
Thu Mar 16 04:03:46 2009
Shutting down instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 17937
Thu Mar 16 04:04:04 2009

2- When database was started again, it gave the following messages.

Completed: ALTER DATABASE MOUNT
Thu Mar 16 04:04:49 2009
alter database open
Thu Mar 16 04:04:49 2009
ORA-1113 signalled during: alter database open...
Thu Apr 16 04:05:24 2009

3- When we checked the v$backup view, 7 files were in backup mode, so we had to recover these 7 datafiles.

SQL> l
1 select * from v$backup
2* where status != 'NOT ACTIVE'
SQL>
SQL> /

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
18 ACTIVE 7.2622E+12 15-MAR-09
19 ACTIVE 7.2622E+12 15-MAR-09
37 ACTIVE 7.2622E+12 15-MAR-09
49 ACTIVE 7.2622E+12 15-MAR-09
51 ACTIVE 7.2622E+12 15-MAR-09
60 ACTIVE 7.2622E+12 15-MAR-09
75 ACTIVE 7.2622E+12 15-MAR-09

7 rows selected.

4- Then we started database recovery, having put database in autorecovery mode. Following is the log what we did, we recovered all the datafiles, which was in backup mode, and we got that information from v$backup view.

ALTER DATABASE RECOVER datafile 18
Thu Mar 16 04:05:24 2009
Media Recovery Datafile: 16
Media Recovery Start
Starting datafile 16 recovery in thread 1 sequence 45
Datafile 16: '/u02/oradata/TEST/test_idx_01.dbf'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 16 ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s45.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s46.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s47.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s48.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s49.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s50.arc
Recovery of Online Redo Log: Thread 1 Group 2 Seq 51 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo01.log
Media Recovery Complete
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Thu Mar 16 04:05:49 2009
alter database open
Thu Mar 16 04:05:49 2009
Beginning crash recovery of 1 threads
Thu Mar 16 04:05:49 2009
Started first pass scan
Thu Mar 16 04:05:49 2009
Completed first pass scan
136 redo blocks read, 20 data blocks need recovery
Thu Mar 16 04:05:49 2009
Started recovery at
Thread 1: logseq 53, block 2, scn 1690.3567247729
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo01.log
Thu Mar 16 04:05:49 2009
Ended recovery at
Thread 1: logseq 53, block 138, scn 1690.3567267893
20 data blocks read, 20 data blocks written, 136 redo blocks read
Crash recovery completed successfully
Thu Mar 16 04:05:49 2009
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 54
Thread 1 opened at log sequence 54
Current log# 2 seq# 54 mem# 0: /u02/oradata/TEST/redo02.log
Successful open of redo thread 1.
Thu Mar 16 04:05:49 2009
ARC0: Evaluating archive log 1 thread 1 sequence 53
ARC0: Beginning to archive log 1 thread 1 sequence 53
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/TEST/arch/TEST_s53.arc'
Thu Mar 16 04:05:49 2009
SMON: enabling cache recovery
Thu Mar 16 04:05:49 2009
ARC0: Completed archiving log 1 thread 1 sequence 53
Thu Mar 16 04:05:50 2009
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Mar 16 04:05:50 2009
SMON: enabling tx recovery
Thu Mar 16 04:05:50 2009
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

How to insert & as a column value in a table

2009-04-23T20:06:00.002+03:00

Introduction:

This post is very simple, but usefull, as when I tried to find this thing on google, I couldnt get any page, may be I didnt search properly, but I wanted to share how we can insert & as a column value. This post is very useful if you are inserting thousands of records and all have & in one of the column values. As if you will try to run in SQL*PLUS, it would be asking you the value for a variable preceeding &.

Reolution:

We did differnet testings, and finally was able to insert the & as a column value, following are the testings and results.

1- created an empty table with one field as text varchar2(60).

test-sql>create table testtable (text varchar2(60));
Table created.

2- different tries to insert the &, last one is successfull.

a- test-sql>insert into testtable values(' this is & record');
Enter value for record: &
old 1: insert into testtable values(' this is & record')
new 1: insert into testtable values(' this is &')

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &

b- test-sql>insert into testtable values(' this is '&' record');
insert into testtable values(' this is '&' record')
*
ERROR at line 1:
ORA-00917: missing comma

c- test-sql>insert into testtable values(' this is "&" record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record

d- test-sql>insert into testtable values(' this is '''&''' record');
insert into testtable values(' this is '''&''' record')
*
ERROR at line 1:
ORA-00917: missing comma

e- test-sql> insert into testtable values (' this is ' '&' ' record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record
this is & record

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

2009-04-20T11:31:00.004+03:00

Introduction:

We had issue with Listener connectivity from client. Even the Listener was running, but it was throwing the following error message.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connectdescriptor

Troubleshooting:

1- We checked with the tnsping utility from client side, and server side, and it was all working, it was displaying the following message.

TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 20-APR-20
09 10:02:32

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
D:\oracle\ora92\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = TESTSERVER)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_N
AME = TESTDB)))
OK (20 msec)

2- When we checked through sql*plus it was showing the following message.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

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

Enter user-name: system@TESTDB
Enter password:
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

3- We checked the local_listener parameter in the database and it was not set to any of the value.

SQL> show parameter local

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

4- Here are the listener.ora and tnsnames.ora files. The important point to note here is the port number was 1528

LISTNER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /u02/oracle/products/9.2.0)
(SID_NAME = TESTDB)
)
)

TNSNAMES.ORA

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)
Resolution:

1- We set the LOCAL_LISTENER parameter to service name "TESTDB" and checked and it was all working.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

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

Enter user-name: system@TESTDB_ora
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>

2- Following was the value of LOCAL_LISTENER parameter

SQL> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string TESTDB

Inactive - No Concurrent Manager after running autoconfig

2009-03-05T15:22:00.005+03:00

Introduction:

Due to IP change in one of our APPS testing environment, we ran autoconfig on all the application tier and database tier, but after the successful running of autoconfig, when we checked the concurrent manager by submitting "Active Users" request, it was showing "Inactive - No Concurrent Manager" error.

Troubleshooting:

Following are the steps which we did.

We shutdown all the application tier services web , forms , concurrent manager and again restarted, but it was showing the same error, "Inactive No Manager" for submission of "Active Users" request. see the screen shot.

We checked the log files for concurrent manager, and we found following error messages in ICM log files.

---------------------------------------------------------------------------Application Object Library: Concurrent Processing version 11.5
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
Internal Concurrent Manager started : 04-MAR-2009 09:44:58
---------------------------------------------------------------------------Spawned Process 8573Could not contact Service Manager FNDSM_TESTSERVER_uat. The TNS alias could not be located, the listener process on TESTSERVER could not be contacted, or the listener failed to spawn the Service Manager process.Spawned Process 8574
Process monitor session started : 04-MAR-2009 09:44:59---------------------

Resolution:

We searched on metalink site, and found following note 392246.1helpful.

Requests Stuck with Inactive No Manager - Routine AFPESA Cannot Construct the Name of an Executable File

When we checked the env files in $APPL_TOP/admin, we found that the Custom Top were not defined in that file. We updated the env file with the custom top and restarted the Concurrent manager and it was working fine.

Concurrent Manager Request error

2009-03-01T13:27:00.008+03:00

Introduction:

We got Concurrent Manager Request error for one of our List Generation request. When we checked the log file (by clicking on view log button),

we got the following message

Troubleshooting and Resolution:

We logged in with the "sysadmin" user and searched the required List Generation request.

And then clicked Diagnostics button, which showed the following message.

When we checked the executable, it was not on the path mentioned. It was a symbolic link to the directoy which was of produciton, as it was the cloned system. We remove the symbolic link, and recreated with the correct path. And this List Generation request worked fine.

ORA-27211: Failed to load Media Management Library

2009-02-25T13:41:00.013+03:00

Introduction:
We had interesting but painfull issue for Oracle backups which are done with Veritas Netbackup. There was activity of patching on Netbackup. And after the patching there was issues with Oracle Backups. All the Oracle backups were failing with following error messages....

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

Troubleshooting:

We tried different steps to resolve the error. Following are the steps which we did.

1- Checked with the "sbttest" command, to see whether Netbackup is doing successfull test backups. and we got the following errors.

$>sbttest test

The sbt function pointers are loaded from oracle.static library.libobk.so could not be loaded. Check that it is installed.

2- We tested with simple rman commands to allocate a tape channel and the release. and following was the error messages.

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:40:43
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

3- The above error messages were showing that MML is not able to load, and that libobk.so was being used as static from Oracle binaries.

We explicitly used the Netbackup /usr/openv/netbackup/bin/libobk.so64.1, inside the RMAN script, but we got the same error as of step 2. Following were the error messages

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1';
3> release channel ch1;
4> }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

The above all steps showed that there was strange kind of problem with Netbackup. As we were not able to allocate one channel with "sbt_tape" type, and all the times MML was not able to be loaded.

We also check in the udump of the Oracle Database server, and following were the error messages in udump trace file.

*** SESSION ID:(47.58746) 2009-02-25 13:40:43.872
SKGFQ OSD: Error in function sbtinit on line 2384
SKGFQ OSD: Look for SBT Trace messages in file /usr/oracle/home/oracle/admin/ORA/u
dump1/sbtio.log
SBT Initialize failed for oracle.static
*** 2009-02-25 13:41:11.006
Failed to load SBT library /usr/openv/netbackup/bin/libobk.so64.1

The above error messages of udump trace file, was clearly showing that the libobk.so64.1 was not able to load. And also we found valid symbolic link from $ORALCE_HOME/lib/libobk.so to /usr/openv/netbackup/bin/libobk.so64.1

Resolution:

After trying every way, finally our system admin did one thing and got resolved the issue. What he did, he renamed the /usr/openv/netbackup directory and again pushed the netbackup client from Netbackup Server, and we again tested the Oracle Backups and it was all working. Following is the simple allocation of channel of 'sbt_tape'

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 6.0 (2005090703)

released channel: ch1

RMAN>

Partitioned Index rebuild with "Parallel" and without "Parallel" clause

2009-02-22T12:11:00.020+03:00

Introduction:
In this article we will see, the impact of "Parallel" clause of rebuilding a partition index. We would also enable tracing on the session which execute the Alter index rebuild statement, and also all those parallel sessions, which are invoked automatically when we use "Parallel" clause.

Steps:
For this test, we can make one partitioned index unusable, we will use exchange partition clause of Oracle 9i, without specifying "Update global indexes", this will cause index unusable. And then we will rebuild both with "Parallel" and without "Parallel" clause.

1- Check the status of partitioned index.

SQL> select substr(index_name,1,20) index_name,substr(partition_name,1,20) part_name , status from dba_ind_partitions
where index_name like '%TEST%'
;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE

2- Exchange the partition with table.

a- we need to make an empty table of the partitioned table, and then we will exchange the partition with that empty table.

sql>create table TEST_PARTITIONED_TABLE_Test as select * from TEST_PARTITIONED_TABLE ;

Table created.

B- We will now exchange the partition JAN2008_02 with table TEST_PARTITIONED_TAB LE_Test.

SQL> alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

c- check the number of rows in the partition JAN2008_02, to verify that rows has been moved to the TEST_PARTITIONED_TABLE.

SQL>select count(1) from TEST_PARTITIONED_TABLE partition (jan2008_02);

COUNT(1)
----------
0

d- check the number of rows in teh TEST_PARTITIONED_TABLE

SQL>select count(1) from TEST_PARTITIONED_TABLE_test;

COUNT(1)
----------
14140873

e- get the data back in the partition JAN2008_02.

SQL>alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

3- Check index status after exchange partition.

INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 UNUSABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE

4- Rebuild unusable index partition JAN2008_02 without "Parallel"

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online;

Index altered.

5- We can check the trace file for the above statement, and can estimate the time taken to rebuild the above index.

From the trace file for the above session.

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 230.33 845.32 614267 522610 6455 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 230.33 845.32 614267 522610 6455 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 27213 0.13 56.25
direct path write 1113 0.73 169.77
direct path read 11273 0.53 232.59
db file sequential read 30 0.01 0.10
rdbms ipc reply 117 0.04 0.10
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00

The elapsed time is 845.32 seconds (approx. 14.08 min.)

Now to check the impact of the parallel clause, we need to repeat steps 1 to 3.

5- after completing the steps 1 - 3, now we can rebuild the index with parallel clause.

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel;

Index altered.

6- From the trace file,

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 1 0 0
Execute 1 1.58 765.66 599831 527410 12060 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.60 765.68 599831 527411 12060 0

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ksu process alloc latch yield 3 0.09 0.19
process startup 6 0.04 0.24
PX Deq: Join ACK 4 0.00 0.00
PX Deq: Execute Reply 683 1.96 761.48
PX Deq: Parse Reply 3 0.01 0.01
PX qref latch 23 0.01 0.30
PX Deq: Table Q qref 2 0.00 0.00
db file sequential read 9 0.01 0.01
rdbms ipc reply 98 0.04 0.12
log file sync 2 0.01 0.01
PX Deq: Signal ACK 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2443.89 2443.89

The elapsed time is 765.68 second. (approx. 12.76 min)

Result:

The rebuild with parallel clause is faster then normal rebuild of the index.

How to trace user session on logon

2009-01-28T09:25:00.004+03:00

How to trace a user session on logon

Introduction:

Some times it is not easy to trace a user session, especially if user is using forms applicaiton. For forms applicaiton some times single user open 2 session in database. So one have to trace both the sessions, otherwise valueable information can miss. And offcourse it takes time to tell user, that once you logged in then wait, dont do any thing, till I tell you to do so. Once user get logged in, then you have to go to database and need to get SID and SERIAL#, and then tracing can be enabled.

Logon Triggers:

To overcome on this probelm, we can make a trigger on logon event of database, the trigger will be fired as soon user logs in to database. And it will start tracing the session automatically.

Trigger code:

Following is the trigger, which will do tracing automatically for user "TEST". Also we can make 2 tables to log the information, these tables can be checked that whether the tracing was done successfully or not. We have 2 tables here, one for the successfull tracing information and other to handle the exception.

Following are the codes for tables and trigger.

1- To log the successfull tracing information.

create table trace_start_log (status varchar2(35),trace_date date);

2- To log the exception, in case of any error.

create table trace_error_log (status varchar2(35),trace_date date);

3- Logon Trigger

CREATE OR REPLACE TRIGGER SESSION_TRACE
AFTER LOGON ON DATABASE
declare
v_user varchar2(30);
begin
select user into v_user from dual;
if v_user='TEST_USER' then
execute immediate 'alter session set sql_trace=true';
insert into trace_start_log values('trace started for TEST_USER',sysdate);
end if;
exception
when no_data_found then
insert into trace_error_log values(' no data found for TEST_USER',sysdate);
end;
/

Testing of the trigger:

We can check by logging in by TEST_USER, and we can check the table TRACE_START_LOG, also one can find the trace files in udump directory.

sql>select * from trace_start_log;

STATUS TRACE_DAT
----------------------------------- ---------
trace started for TEST_USER 28-JAN-09

Oracle 9i Data Guard Configuration, Testing and Switch Over/ Fail Over

2008-12-06T01:19:00.002+03:00

Objective:

This paper is related to the Oracle 9i Data Guard Configuration, testing, switch over and fail over.

Introduction:

Data Guard provides high availability, data protection and disaster for the Enterprise Oracle Database. In Data Guard, the standby databases are created which is the exact replica of the production database and these are transactional sync copies of the production database. If for any reason production database goes down, the standby can be switched over as a production database. In the case of disaster, Data guard offers not a single transaction lost.

In this testing, there are two Oracle 9i Databases. Two machines were used, one is Primary Database and the second is Standby Database.

After configuring, the switch over process applied on the Oracle 9i Data Guard configuration. Which turn the old primary database into new standby database and old standby database into new primary database.

After the switch over, the fail over process also has been applied. Which is used in the case of complete disaster of the primary database, this will make the standby database into new primary database. And all the records are applied into the new primary database.

Data Guard Setup Procedure:

1- Prepare the Primary Database

a) Run the following queries:

SQL> select name,database_role
2 from v$database;

NAME DATABASE_ROLE
--------- ----------------
PRIMARY PRIMARY

SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination G:\oraclepdb\oradata\arch_2
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

b) SQL> show parameter log_archive

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable

log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0

c) alter database to force logging mode.

SQL> alter database force logging;

Database altered.

d) Select name from v$datafile

NAME
------------------------------------------------------------
G:\ORACLEPDB\ORADATA\PRIMARY\SYSTEM01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\UNDOTBS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\CWMLITE01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\DRSYS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\EXAMPLE01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\INDX01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\ODM01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\TOOLS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\USERS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\XDB01.DBF

e) shutdown immediate

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

f) Copy the datafiles to the Standy Mchine .

g) Startup the primary database

SQL> startup
ORACLE instance started.

Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

h) Create the standby controlfile

SQL> alter database create standby controlfile as 'G:\oraclepdb\oradata\standbyfiles\standbycontrolfile.ctl';

Database altered.

i) Create standby init file

SQL> create pfile='G:\oraclepdb\oradata\standbyfiles\standbyinitfile.ora' from spfile;

File created.

j) copy the standby controlfile and standby init file to the standby machine.

2- Prepare the Standby Database:

a) Setup the standby init parameters.

I. Change the controlfile parameter to reflect the standby controlfile.

control_files= ("F:\oraclesb\oradata\standbyfiles\STANDBYCONTROLFILE.CTL")

I. db_name=primary
II. standby_archive_dest="F:\oraclesb\oradata\arch"
III. remote_archive_enable=true
IV. #standby_file_management=auto
V. service_names="standby1"

3- Prepare the Primary Database TNS Names File:

a)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

STANDBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.199)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby1)
)
)

4- Prepare the Standby Database TNS Names file:

a)

STANDBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = epserver)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = standby1)
)
)

PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.71)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

5- Launch The Standby Database:

a) Make a password file
Orapwd file=………….. password=…………….. entries=…………………..

b) startup the database in nomount phase

startup nomount pfile='D:\oracle9201\admin\standby1\pfile\init_standby1_110504.ora'

c) Mount the database in standby phase

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

6- Start Sending Redo from Primary Database

a) See the log_archive parameter

SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable

log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable

b) Set the log_archive_dest_3 to the standby database.

SQL> alter system set log_archive_dest_3='service=standby1 lgwr' scope=both;

System altered.

c) Enable the log_archive_dest_3
SQL> alter system set log_archive_dest_state_3=enable;
System altered.
d) Archive log the current archive file. It should be archived on the primary as well as on the standby database.
See the following location for the Primary Database Archive Destination
G:\oraclepdb\oradata\arch
See the following location for the Standby Database Archive Destination
F:\oraclesb\oradata\arch

e) Verify whether Primary Database is sending the redo files.

SQL> select dest_id,sequence#,archived,applied
2 from v$archived_log;

DEST_ID SEQUENCE# ARC APP
---------- ---------- --- ---
1 1 YES NO
2 1 YES NO
1 2 YES NO
2 2 YES NO
3 2 YES NO
3 3 YES NO
1 3 YES NO
2 3 YES NO

7- Add the standby Redo Log files into Standby Database:

a) SQL> select *
2 from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 STALE ONLINE
G:\ORACLEPDB\ORADATA\PRIMARY\REDO03.LOG

2 ONLINE
G:\ORACLEPDB\ORADATA\PRIMARY\REDO02.LOG

1 ONLINE
G:\ORACLEPDB\ORADATA\PRIMARY\REDO01.LOG

SQL> alter database add standby redolog file 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
alter database add standby redolog file 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected

SQL> lter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
SP2-0734: unknown command beginning "lter datab..." - rest of line ignored.
SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG'
- file cannot be created
ORA-27040: skgfrcre: create error, unable to create file
OSD-04001: invalid logical block size (OS 512)

SQL> alter database add standby logfile 'F:\oraclesb\oradata\standby\sREDO03.LOG' size 100m;

Database altered.

SQL> alter database add standby logfile 'F:\oraclesb\oradata\standby\sREDO02.LOG' size 100m;

Database altered.

SQL> alter database add standby logfile 'F:\oraclesb\oradata\standby\sREDO01.LOG' size 100m;

Database altered.

b) SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

8- To make sure that Standby Redo log files are being used
(on Primary Database):

a) SQL> select *
2 from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 104857600 1 YES INACTIVE 225814 11-MAY-04
2 1 6 104857600 1 YES INACTIVE 225889 11-MAY-04
3 1 7 104857600 1 NO CURRENT 225897 11-MAY-04

SQL> alter system archive log current;

System altered.

SQL> select *
2 from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 8 104857600 1 NO CURRENT 227491 11-MAY-04
2 1 6 104857600 1 YES INACTIVE 225889 11-MAY-04
3 1 7 104857600 1 YES ACTIVE 225897 11-MAY-04

9- On the Standby Database:

a) SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select group#,sequence#,status
2 from v$standby_log;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------
4 8 ACTIVE
5 0 UNASSIGNED
6 0 UNASSIGNED

Primary Database

SQL> alter system archive log current;

System altered.

SQL> select *
2 from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 8 104857600 1 YES ACTIVE 227491 11-MAY-04
2 1 9 104857600 1 NO CURRENT 229368 11-MAY-04
3 1 7 104857600 1 YES ACTIVE 225897 11-MAY-04

Standby Database

SQL> select group#,sequence#,status
2 from v$standby_log;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------
4 9 ACTIVE
5 0 UNASSIGNED
6 0 UNASSIGNED

Object Creation in Primary Database

a) Create some objects and insert some data into it. So after switch over we could check whether the object and data have been applied into Data Guard (Standby Database)

SQL> create table scott.test( id number, subject varchar(20));

Table created.

SQL> insert into scott.test values (&n,'&subject_name')
2 .
SQL>
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> insert into scott.test values (&n,'&subject_name');
Enter value for n: 1
Enter value for subject_name: oracle
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (1,'oracle')

1 row created.

SQL> /
Enter value for n: 2
Enter value for subject_name: vb
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (2,'vb')

1 row created.

SQL> 3
SP2-0226: Invalid line number
SQL> /
Enter value for n: 3
Enter value for subject_name: principles of management

old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (3,'principles of management')
insert into scott.test values (3,'principles of management')
*
ERROR at line 1:
ORA-01401: inserted value too large for column

SQL> /
Enter value for n: 34
Enter value for subject_name: c++
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (34,'c++')

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

Changing Roles

Switch Over Process:

1- Prepare the Primary Database Parameters:

a) Show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string %ORACLE_HOME%\RDBMS
standby_file_management string MANUAL

b) Show parameter log_archive_dest

SQL> show parameter log_archive

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string service=standby1 lgwr
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string ENABLE
log_archive_dest_state_4 string enable

log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable

log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0

c) set standby_archive_dest into primary database

SQL> alter system set standby_archive_dest='G:\oraclepdb\oradata\arch' scope=spfile;

System altered.

d) set the standby_file_management to auto

SQL> alter system set standby_file_management=auto scope=spfile;

System altered.

2- Prepare the Standby Database Parameters:

a) show parameter log_archive

SQL> show parameter log_archive

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=F:\oraclesb\oradata\a
rch
log_archive_dest_10 string
log_archive_dest_2 string location=F:\oraclesb\oradata\a
rch_2
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0

b) set the log_archive_dest_3 to the primary database service

SQL> alter system set log_archive_dest_3='service=primary lgwr' scope=spfile;

System altered.

c) set log_archive_dest_stat_3 to DEFER

SQL> alter system set log_archive_dest_state_3=defer scope=spfile;

System altered.

3- Prepare to Switchover to Primary:

a) Select the switchover_status from the v$database

SQL> select switchover_status
2 from v$database;

SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE *it must be TO STANDBY

b) Select the username,status,type,process,sid,serial# from v$session

1 Select username,status,type,process,sid,serial# from v$session
2* where type='USER'
SQL> /

USERNAME STATUS TYPE PROCESS SID SERIAL#
------------------------------ -------- ---------- ------------ ---------- ----------
ACTIVE USER 1660 7 1
ACTIVE USER 1664 8 1
SYS ACTIVE USER 1376:2348 11 13

4- Switchover Primary to Standby:

a) SQL> alter database commit to switchover to physical standby;

Database altered.

b) alert log report from the Primary database

Wed May 12 12:29:01 2004
ARCH: Evaluating archive log 1 thread 1 sequence 20
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: Beginning to archive log 1 thread 1 sequence 20
Creating archive destination LOG_ARCHIVE_DEST_3: 'standby1'
Wed May 12 12:29:01 2004
ARC0: Evaluating archive log 1 thread 1 sequence 20
ARC0: Unable to archive log 1 thread 1 sequence 20
Log actively being archived by another process
Wed May 12 12:29:02 2004
Creating archive destination LOG_ARCHIVE_DEST_2: 'G:\ORACLEPDB\ORADATA\ARCH_2\ARCH201.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'G:\ORACLEPDB\ORADATA\ARCH\ARCH201.ARCH'
ARCH: Completed archiving log 1 thread 1 sequence 20
Wed May 12 12:29:03 2004
SMON: disabling tx recovery
Wed May 12 12:29:03 2004
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Active process 2452 user 'SYSTEM' program 'ORACLE.EXE'
Active process 552 user 'SYSTEM' program 'ORACLE.EXE'
CLOSE: waiting for server sessions to complete.
CLOSE: all sessions shutdown successfully.
Wed May 12 12:29:06 2004
SMON: disabling cache recovery
Wed May 12 12:29:06 2004
Shutting down archive processes
Archiving is disabled
Wed May 12 12:29:06 2004
ARCH shutting down
ARC0: Archival stopped
Wed May 12 12:29:06 2004
ARCH shutting down
ARC1: Archival stopped

Wed May 12 12:29:06 2004
Thread 1 closed at log sequence 21
Successful close of redo thread 1.
Wed May 12 12:29:06 2004
ARCH: noswitch archival of thread 1, sequence 21
ARCH: End-Of-Redo archival of thread 1 sequence 21
ARCH: Evaluating archive log 2 thread 1 sequence 21
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: Beginning to archive log 2 thread 1 sequence 21
Creating archive destination LOG_ARCHIVE_DEST_3: 'standby1'
Creating archive destination LOG_ARCHIVE_DEST_2: 'G:\ORACLEPDB\ORADATA\ARCH_2\ARCH211.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'G:\ORACLEPDB\ORADATA\ARCH\ARCH211.ARCH'
Wed May 12 12:29:06 2004
idle dispatcher 'D000' terminated, pid = (11, 1)
Wed May 12 12:29:07 2004
ARCH: Completed archiving log 2 thread 1 sequence 21
ARCH: archiving is disabled due to current logfile archival
Clearing standby activation ID 1363333035 (0x5142cfab)
The primary database controlfile was created using the
'MAXLOGFILES 50' clause.
There is space for up to 47 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
Archivelog for thread 1 sequence 21 required for standby recovery
MRP0 started with pid=10
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 21
Datafile 1: 'G:\ORACLEPDB\ORADATA\PRIMARY\SYSTEM01.DBF'
Starting datafile 2 recovery in thread 1 sequence 21
Datafile 2: 'G:\ORACLEPDB\ORADATA\PRIMARY\UNDOTBS01.DBF'
Starting datafile 3 recovery in thread 1 sequence 21
Datafile 3: 'G:\ORACLEPDB\ORADATA\PRIMARY\CWMLITE01.DBF'
Starting datafile 4 recovery in thread 1 sequence 21
Datafile 4: 'G:\ORACLEPDB\ORADATA\PRIMARY\DRSYS01.DBF'
Starting datafile 5 recovery in thread 1 sequence 21
Datafile 5: 'G:\ORACLEPDB\ORADATA\PRIMARY\EXAMPLE01.DBF'
Starting datafile 6 recovery in thread 1 sequence 21
Datafile 6: 'G:\ORACLEPDB\ORADATA\PRIMARY\INDX01.DBF'

Starting datafile 7 recovery in thread 1 sequence 21
Datafile 7: 'G:\ORACLEPDB\ORADATA\PRIMARY\ODM01.DBF'
Starting datafile 8 recovery in thread 1 sequence 21
Datafile 8: 'G:\ORACLEPDB\ORADATA\PRIMARY\TOOLS01.DBF'
Starting datafile 9 recovery in thread 1 sequence 21
Datafile 9: 'G:\ORACLEPDB\ORADATA\PRIMARY\USERS01.DBF'
Starting datafile 10 recovery in thread 1 sequence 21
Datafile 10: 'G:\ORACLEPDB\ORADATA\PRIMARY\XDB01.DBF'
Media Recovery Log G:\ORACLEPDB\ORADATA\ARCH_2\ARCH211.ARCH
Identified end-of-REDO for thread 1 sequence 21
Identified end-of-REDO for thread 1 sequence 21
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 309567
MRP0: Media Recovery Complete: End-Of-REDO
Resetting standby activation ID 1363333035 (0x5142cfab)
MRP0: Background Media Recovery process shutdown
Wed May 12 12:29:13 2004
Switchover: Complete - Database shutdown required
Completed: alter database commit to switchover to physical st

1- execute one statement on Primary database after moving it to Physical Standby DB

SQL> select *
2 from v$log;
from v$log
*
ERROR at line 2:
ORA-01507: database not mounted

c) shutdown the database

5- Switchover the Standby to Primary:

a. select the switchover_status from v$database

SQL> select switchover_status
2 from v$database;

SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE

b. switchover to primary

SQL> alter database commit to switchover to primary;

Database altered.

c) Alert log entries after switching over to primary.

Sun Nov 28 12:57:09 2004
alter database commit to switchover to primary
Sun Nov 28 12:57:09 2004
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
RESETLOGS after incomplete recovery UNTIL CHANGE 309567
Resetting resetlogs activation ID 0 (0x0)
Online log 2 of thread 1 was previously cleared
Sun Nov 28 12:57:24 2004
Online log 6 of thread 0 was previously cleared
Switchover: Complete - Database shutdown required
Completed: alter database commit to switchover to primary

d) Shutdown the database.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

6- Start the new Standby Database i.e. (old primary database)

a) Startup nomount

SQL> startup nomount
ORACLE instance started.

Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

b) Alert Log entries

ed May 12 12:55:41 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.2.1.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 109051904
large_pool_size = 25165824
java_pool_size = 25165824
control_files = G:\oraclepdb\oradata\primary\CONTROL01.CTL, G:\oraclepdb\oradata\primary\CONTROL02.CTL, G:\oraclepdb\oradata\primary\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 25165824
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = location=G:\oraclepdb\oradata\arch
log_archive_dest_2 = location=G:\oraclepdb\oradata\arch_2
log_archive_dest_3 = service=standby1 lgwr
log_archive_dest_state_3 = ENABLE
log_archive_min_succeed_dest= 2
standby_archive_dest = G:\oraclepdb\oradata\arch
log_archive_format = arch%s%t.arch

db_file_multiblock_read_count= 16
standby_file_management = AUTO
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = primary
dispatchers = (PROTOCOL=TCP) (SERVICE=primaryXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = D:\oracle\admin\primary\bdump
user_dump_dest = D:\oracle\admin\primary\udump
core_dump_dest = D:\oracle\admin\primary\cdump
sort_area_size = 524288
db_name = primary
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed May 12 12:55:44 2004
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC1 started with pid=13
ARC1: Archival started
Wed May 12 12:55:45 2004
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed May 12 12:55:45 2004
ARC0: Thread not mounted
Wed May 12 12:55:45 2004
ARC1: Thread not mounted

c) Show parameter log_archive

SQL> show parameter log_archive

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string service=standby1 lgwr
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string ENABLE
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0

d) Set the parameter log_archive_dest_stat_3 to defer

SQL> alter system set log_archive_dest_state_3=defer scope=both;

System altered.

e) mount the database in standby mount phase

SQL> alter database mount standby database;

Database altered.

f)

SQL> alter database recover managed standby database disconnect from session;

Database altered.

g) Add the standby redo log files

SQL> startup nomount
ORACLE instance started.

Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;

Database altered.

SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO02.LOG' size 100m;
Database altered.

SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO01.LOG' size 100m;
Database altered.

6- Start the new Primary Database i.e. (old standby database)

a) startup nomunt

SQL> startup nomount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

b) show parameter log_archive

SQL> show parameter log_archive

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=F:\oraclesb\oradata\a
rch
log_archive_dest_10 string
log_archive_dest_2 string location=F:\oraclesb\oradata\a
rch_2
log_archive_dest_3 string service=primary lgwr
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string DEFER
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0

c) set the log_archive_dest_state_3 to enable

SQL> alter system set log_archive_dest_state_3=enable scope=both;

System altered.

d) mount and open the database

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

e) Alert Log entries

alter database mount
Sun Nov 28 13:24:00 2004
Successful mount of redo thread 1, with mount id 1380889259.
Sun Nov 28 13:24:00 2004
Database mounted in Exclusive Mode.
Completed: alter database mount
Sun Nov 28 13:24:05 2004

alter database open
Sun Nov 28 13:24:06 2004
LGWR: Primary database is in CLUSTER CONSISTENT mode
Assigning activation ID 1380889259 (0x524eb2ab)

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
Creating archive destination LOG_ARCHIVE_DEST_3: 'primary'
LNS0 started with pid=15
Sun Nov 28 13:24:10 2004
LGWR: Beginning to archive log 1 thread 1 sequence 23
Thread 1 advanced to log sequence 23
Thread 1 opened at log sequence 23
Current log# 1 seq# 23 mem# 0: F:\ORACLESB\ORADATA\STANDBY1\REDO01.LOG
Successful open of redo thread 1.
Sun Nov 28 13:24:10 2004
SMON: enabling cache recovery
Sun Nov 28 13:24:10 2004
ARC0: Evaluating archive log 2 thread 1 sequence 22
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARC0: Beginning to archive log 2 thread 1 sequence 22
Creating archive destination LOG_ARCHIVE_DEST_3: 'primary'
Creating archive destination LOG_ARCHIVE_DEST_2: 'F:\ORACLESB\ORADATA\ARCH_2\ARCH221.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'F:\ORACLESB\ORADATA\ARCH\ARCH221.ARCH'
Sun Nov 28 13:24:11 2004
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Sun Nov 28 13:24:12 2004
kccrsz: expanded controlfile section 11 from 27 to 55 records
requested to grow by 27 record(s); added 2 block(s) of records
ARC0: Completed archiving log 2 thread 1 sequence 22
Sun Nov 28 13:24:12 2004
Dictionary check beginning

Dictionary check complete
Sun Nov 28 13:24:13 2004
SMON: enabling tx recovery
Sun Nov 28 13:24:13 2004
Database Characterset is WE8MSWIN1252
Updating 9.2.0.2.1 NLS parameters in sys.props$
-- adding 9.2.0.1.0 NLS parameters.
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

e) Alert Log entries in New Standby Database

RFS: Successfully opened standby logfile 4: 'G:\ORACLEPDB\ORADATA\PRIMARY\SREDO03.LOG'
Wed May 12 13:18:35 2004
Media Recovery Log G:\ORACLEPDB\ORADATA\ARCH\ARCH221.ARCH
Media Recovery Waiting for thread 1 seq# 23 (in transit)
Wed May 12 13:21:11 2004
Restarting dead background process QMN0
QMN0 started with pid=17

NOTE:
AT this stage the archive files are being sent from the new primary to new standby database.

So the complete switch over has been done.

7- Check the data into new Primary database, which were inserted into old Primary Database before switch over:

SQL> select *
2 from scott.test;

ID SUBJECT
---------- --------------------
1 oracle
2 vb
34 c++

This is the same data, which was inserted into old Primary Database before switch over.

Fail Over Process:

In fail over the standby database will become the primary database and the primary database needs to be reconstructed again as a standby database.

1- Make some Object and insert some records into Primary Database:

a) Make a test_new_primary table into primary database.

SQL> create table scott.test_new_primary(id number, name varchar2(20));

Table created.

b) Insert some data into test_new_primary table.

SQL> insert into scott.test_new_primary values (&n,'&name');
Enter value for n: 11
Enter value for name: data guard
old 1: insert into scott.test_new_primary values (&n,'&name')
new 1: insert into scott.test_new_primary values (11,'data guard')

1 row created.

SQL> /
Enter value for n: 22
Enter value for name: replication
old 1: insert into scott.test_new_primary values (&n,'&name')
new 1: insert into scott.test_new_primary values (22,'replication')

1 row created.

SQL> /
Enter value for n: 33
Enter value for name: rac
old 1: insert into scott.test_new_primary values (&n,'&name')
new 1: insert into scott.test_new_primary values (33,'rac')

1 row created.

SQL> commit;

2- Shutdowns abort the primary database. (Simulation of crashing the database)

SQL> shutdown abort;
ORACLE instance shut down.

a) Alert Log entries in the standby database

Wed May 12 16:25:33 2004
RFS: Possible network disconnect with primary database
Wed May 12 16:25:33 2004
RFS: Possible network disconnect with primary database
Wed May 12 16:26:31 2004
Restarting dead background process QMN0
QMN0 started with pid=9

b) Alert log entries in the primary database

Sun Nov 28 16:31:24 2004
Shutting down instance (abort)
License high water mark = 3
Instance terminated by USER, pid = 4112

3- Steps performed on the Standby Database:

a) Verify the archive gap

SQL> select *
2 from v$archive_gap;

no rows selected

b) Query the v$managed_standby view

SQL> select process,client_process,sequence#
2 from v$managed_standby;

PROCESS CLIENT_P SEQUENCE#
------- -------- ----------
ARCH ARCH 0
ARCH ARCH 25
MRP0 N/A 26

c) Query the v$database view

SQL> select switchover_status
2 from v$database;

SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE *

d) execute the recover statement
SQL> alter database recover managed standby database finish;
Database altered.

e) Alert Log entries from the Standby Database
alter database recover managed standby database finish
Wed May 12 16:36:29 2004
Terminal Recovery: request posted
Wed May 12 16:36:39 2004
TERMINAL RECOVERY changing datafile format version from 8.0.0.0.0 to 9.0.0.0.0
Switching logfile format version from 8.0.0.0.0 to 9.0.0.0.0
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 26 redo required
Terminal Recovery: G:\ORACLEPDB\ORADATA\PRIMARY\SREDO03.LOG
Identified end-of-REDO for thread 1 sequence 26
Incomplete recovery applied all redo ever generated.
Recovery completed through change 336495
MRP0: Media Recovery Complete
Terminal Recovery: enabled archive destination LOG_ARCHIVE_DEST_2
Switching logfile format version from 9.0.0.0.0 to 8.0.0.0.0
Terminal Recovery: successful completion
Wed May 12 16:36:41 2004
ARC1: Evaluating archive log 4 thread 1 sequence 26
ARC1: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_3 after log switch
ARC1: Beginning to archive log 4 thread 1 sequence 26
Creating archive destination LOG_ARCHIVE_DEST_3: 'standby1'
ARC1: Error 1034 Creating archive log file to 'standby1'
Wed May 12 16:36:41 2004
Errors in file d:\oracle\admin\primary\bdump\primary_arc1_4904.trc:
ORA-01034: ORACLE not available
Creating archive destination LOG_ARCHIVE_DEST_2: 'G:\ORACLEPDB\ORADATA\ARCH_2\ARCH261.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'G:\ORACLEPDB\ORADATA\ARCH\ARCH261.ARCH'
ARC1: Completed archiving log 4 thread 1 sequence 26
Wed May 12 16:36:56 2004
Resetting standby activation ID 1380889259 (0x524eb2ab)
MRP0: Background Media Recovery process shutdown
Wed May 12 16:36:56 2004
Terminal Recovery: completion detected
Completed: alter database recover managed standby database fi
Wed May 12 16:37:02 2004
Restarting dead background process QMN0
QMN0 started with pid=9

f) Conversion from Standby Database to Primary Database

SQL> alter database commit to switchover to primary;

Database altered.

g) Alert log entries from oldstandby database (New Primary Database)

Wed May 12 16:40:05 2004
alter database commit to switchover to primary
Wed May 12 16:40:05 2004
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
NORESETLOGS after complete recovery through change 336495
Resetting resetlogs activation ID 0 (0x0)
Wed May 12 16:40:20 2004
Online log 6 of thread 0 was previously cleared
Changing control file format version from 8.0.0.0.0 to 9.0.0.0.0
RESETLOGS changing datafile format version from 9.0.0.0.0 to 8.0.0.0.0
Switchover: Complete - Database shutdown required
Completed: alter database commit to switchover to primary

h) Shutdown the database
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

i) Startup the database

SQL> startup
ORACLE instance started.

Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

4- Verification to check that whether the data inserted in old crashed Primary database has been applied into New Primary Database

SQL> select *
2 from scott.test_new_primary;

ID NAME
---------- --------------------
11 data guard
22 replication
33 rac

This is the same object and data, which were inserted into the crashed primary database. This has been applied to new Primary Database.

Oracle 9i AS R2 Report Server shutting down issue

2008-11-24T11:52:00.001+03:00

Introduction:
We faced a problem in Oracle 9i AS R2 report services, the log messages was showing as (URLEngineImpl:shutdown): Shutting down engine.

Resolution:
We tried to restart the report services from command line and from enterprise manager console, but after successfull restart it was shutting down again.

When we ran the following command

./$ORACLE_HOME/bin/rwserver.sh server=rep_server

it was throwing the following error

REP-56033: Job 67192 does not exist.

We checked the XWindows server and it was working fine, so the issue was not with the Xwindows as well.

we did the backup of the rep_server.dat file and removed this file, it is at the path $ORACLE_HOME/reports/server, having removed this file, we again restarted the reports services with the command line and it was all working, we used the following command

nohup $ORACLE_HOME/bin/rwserver.sh server=rep_server &

Oracle 9i Multi Master Advance Replication setup, testing and troubleshooting

2008-11-13T13:15:00.001+03:00

Objective:
This paper is related to the Multi Master Replication setup, testing and troubleshooting.

Introduction:
In this Multi Master Replication testing two machines are being used.
There will be two databases on the two different machines i.e.

a) Master1 (Master definition Site)

This site will be used for the administration of the Replication setup.

b) Master2

This will act as a second Master site in the Replication.

Replication Setup Procedure
On Master1 Database:

1- Check the init parameters

a) SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0

It should be same as the Oracle 9i Release which is being used for the Replication

b) SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

Add 110MB in the shared_pool_size

c) SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150

Add 12 to the processes

d) SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

It should be TRUE

e) SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

It is the extension component of the local databases Global Name

f) SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

Add 2 for each master site into open_links

g) distributed_transaction * need to be clarify

h) SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

i) NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

Add 1 per additional Master

k) SQL> show parameter servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 5
parallel_min_servers integer 0

set parallel_max_servers to 10
set parallel_min_servers to 2

2- Check the init parameters after modifications

a) SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0

b) SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 167772160

c) SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 11
log_archive_max_processes integer 2
processes integer 170

d) SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

e) SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string world

f) SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 6
open_links_per_instance integer 4

g) SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

h) SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 11

i) SQL> show parameter parallel_max_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 10
SQL> show parameter parallel_min_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 2

3- Check the Tablespace Free Space

1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 4.25
TOOLS 3.9375
UNDOTBS1 189.9375
USERS 24.9375
XDB .1875

System Tablespace should have 80MB free space
Undo Tablespace should have 60 MB free space

3.1) Increase the required space

a) Check the total space for each tablespace in database

SQL> l
1 select file_name,bytes/1024/1024
2* from dba_data_files
SQL> /

FILE_NAME BYTES/1024/1024
------------------------------------------------------- ---------------
G:\ORACLEPDB\ORADATA\MASTER1\SYSTEM01.DBF 400
G:\ORACLEPDB\ORADATA\MASTER1\UNDOTBS01.DBF 200
G:\ORACLEPDB\ORADATA\MASTER1\CWMLITE01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\DRSYS01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\EXAMPLE01.DBF 148.75
G:\ORACLEPDB\ORADATA\MASTER1\INDX01.DBF 25
G:\ORACLEPDB\ORADATA\MASTER1\ODM01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\TOOLS01.DBF 10
G:\ORACLEPDB\ORADATA\MASTER1\USERS01.DBF 25
G:\ORACLEPDB\ORADATA\MASTER1\XDB01.DBF 38.125

b) Increase the system tablespace

1* alter database datafile 'G:\ORACLEPDB\ORADATA\MASTER1\SYSTEM01.DBF' resize 600m
SQL> /

Database altered.

c) Check the Free space again.

1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 204.25
TOOLS 3.9375
UNDOTBS1 189.8125
USERS 24.9375
XDB .1875

4- Install the Replication catalog:

Oracle 9i does not required to install the Replication Catalog

5- Net9 configuration:

MASTER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master1)
)
)

MASTER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master2)
)
)

6- Creation of Replication Administrator (REPADMIN):

SQL> create user repadmin identified by repadmin
2 default tablespace tools
3 temporary tablespace temp
4 quota unlimited on tools
5 quota unlimited on temp
6 quota 0 on system;

User created.

SQL> grant connect, resource to repadmin;

Grant succeeded.

SQL> exec dbms_repcat_admin.grant_admin_any_schema('REPADMIN')

PL/SQL procedure successfully completed.

SQL> grant comment any table to repadmin;

Grant succeeded.

SQL> grant lock any table to repadmin;

Grant succeeded.

7- Creation of Replication Propagator / Receiver:

The propagator user is responsible to forward the deferred transactions from the source master site to the destination master site.

The receiver user is responsible for applying these transactions at the destination master site.

The replication administrator, propagator and receiver are normally the same user i.e. REPADMIN

Register the REPADMIN as the propagator

SQL> execute dbms_defer_sys.register_propagator('REPADMIN')

PL/SQL procedure successfully completed. SQL> grant execute any procedure to repadmin;

Grant succeeded.

8- Creation of Schema Owner

This user is usually responsible for the day-to-day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN.
SQL> ed
Wrote file afiedt.buf
1 create user repdba identified by repdba
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 quota unlimited on temp
6* quota 0 on system
SQL> /
User created.

9- Create Database Links

All databases participating in the Replication must have a unique global name. The global name should be the same as the database name.
a) Check the global_name

SQL> select *
2 from global_name
3 ;

GLOBAL_NAME
--------------------------------------------------------
MASTER1.US.ORACLE.COM

b) Change the global_name

SQL> update global_name
2 set global_name='MASTER1';
1 row updated.

SQL> commit;
Commit complete.
c) Check the global_name
SQL> select *
2 from global_name;

GLOBAL_NAME
---------------------------------------
MASTER1

10- Create the Public Database Link

a) Check the database links in the database
SQL> select *
2 from dba_db_links;
no rows selected

b) Create the public database link
1 create public database link master2
2* using 'master2'
SQL> /
Database link created.

c) Check the database link
1 select *
2* from dba_db_links

SQL> /

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ ---------- ---------
PUBLIC MASTER2 master2 15-MAY-04

11- Create the Private Database Link for REPADMIN

a) Create the private database link by connecting as REPADMIN

1 create database link master2
2* connect to repadmin identified by repadmin
3 /

Database link created.

b) Check the database link

SQL> l
1 select *
2* from dba_db_links
SQL> /

OWNER DB_LINK USERNAME HOST CREATED
---------- -------------------- ------------------------------ ---------- ---------
PUBLIC MASTER2 master2 17-MAY-04
REPADMIN MASTER2 REPADMIN 17-MAY-04

12- Schedule “push” and “purge” jobs:

a) Connect with REPADMIN

b) Set up the “push” job to push the deferred queue automatically.

1 begin
2 dbms_defer_sys.schedule_push (
3 DESTINATION => 'master2',
4 INTERVAL => '/*1:Mins*/ sysdate + 10/(60*24)',
5 NEXT_DATE => sysdate,
6 STOP_ON_ERROR => FALSE,
7 delay_seconds => 0,
8 PARALLELISM => 1);
9* end;

SQL> /

PL/SQL procedure successfully completed.

c) Set up the “purge” job to purge the deferred queue automatically.

SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date => sysdate,
4 interval => '/*1:Hr*/ sysdate + 1/24',
5 delay_seconds => 0,
6 rollback_segment => '');
7 end;
8 /

PL/SQL procedure successfully completed.

On Master2 Database:

1- Check the init parameters

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0
It should be same as the Oracle 9i Release which is being used for the Replication

b) SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

Add 110MB in the shared_pool_size

c) SQL> SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150

Add 12 to the processes

d) SQL> SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

It should be TRUE

e) SQL> SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string world

It is the extension component of the local databases Global Name

f) SQL> SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

Add 2 for each master site into open_links

g) distributed_transaction * need to be clarify

h) SQL> SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

i) SQL> show parameter job_queue

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

Add 1 per additional Master
k) SQL> SQL> show parameter servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 5
parallel_min_servers integer 0

set parallel_max_servers to 10
set parallel_min_servers to 2

2- Check the init parameters after modifications

a) SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0

b) SQL> SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 167772160

c) SQL> SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 11
log_archive_max_processes integer 2
processes integer 170

d) SQL> SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

e) SQL> SQL> show parameter db_domain ***

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

f) SQL> SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 6
open_links_per_instance integer 4

g) SQL> SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

h) SQL> SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 11

i) SQL> SQL> show parameter parallel_max_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 10
SQL> SQL> show parameter parallel_min_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 2

3- Check the Tablespace Free Space

1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 4.25
TOOLS 3.9375
UNDOTBS1 194.375
USERS 24.9375
XDB .1875

System Tablespace should have 80MB free space
Undo Tablespace should have 60 MB free space
3.1) Increase the required space
b) Check the total space for each tablespace in database

SQL> l
1 1 select file_name,bytes/1024/1024
2* from dba_data_files
SQL> /

FILE_NAME BYTES/1024/1024
------------------------------------------------------- ---------------
F:\ORACLESB\ORADATA\MASTER2\SYSTEM01.DBF 400
F:\ORACLESB\ORADATA\MASTER2\UNDOTBS01.DBF 200
F:\ORACLESB\ORADATA\MASTER2\CWMLITE01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\DRSYS01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\EXAMPLE01.DBF 148.75
F:\ORACLESB\ORADATA\MASTER2\INDX01.DBF 25
F:\ORACLESB\ORADATA\MASTER2\ODM01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\TOOLS01.DBF 10
F:\ORACLESB\ORADATA\MASTER2\USERS01.DBF 25
F:\ORACLESB\ORADATA\MASTER2\XDB01.DBF 38.125

b) Increase the system tablespace
SQL> alter database datafile 'F:\ORACLESB\ORADATA\MASTER2\SYSTEM01.DBF' resize 600m;

Database altered.

c) SQL> select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3 group by tablespace_name
4 /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 204.25
TOOLS 3.9375
UNDOTBS1 194.375
USERS 24.9375
XDB .1875

4- Install the Replication catalog:

Oracle 9i does not required to install the Replication Catalog

5- Net9 configuration:

MASTER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = master2)
)
)

MASTER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.71)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = master1)
)
)

6- Creation of Replication Administrator (REPADMIN):

1 create user repadmin identified by repadmin
2 default tablespace tools
3 temporary tablespace temp
4 quota unlimited on tools
5 quota unlimited on temp
6* quota 0 on system
SQL> /

User created.
SQL> grant connect, resource to repadmin;

Grant succeeded.

SQL> exec dbms_repcat_admin.grant_admin_any_schema('REPADMIN')

PL/SQL procedure successfully completed.

SQL> grant comment any table to repadmin;

Grant succeeded.

SQL> grant lock any table to repadmin;

Grant succeeded.

7- Creation of Replication Propagator / Receiver:

The propagator user is responsible to forward the deferred transactions from the source master site to the destination master site.

The receiver user is responsible for applying these transactions at the destination master site.

The replication administrator, propagator and receiver are normally the same user i.e. REPADMIN
Register the REPADMIN as the propagator

SQL> execute dbms_defer_sys.register_propagator('REPADMIN')

PL/SQL procedure successfully completed. SQL> grant execute any procedure to repadmin;

Grant succeeded.

8- Creation of Schema Owner

This user is usually responsible for the day-to-day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN.

SQL> ed
Wrote file afiedt.buf

1 create user repdba identified by repdba
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 quota unlimited on temp
6* quota 0 on system
SQL> /

User created.

9- Create Database Links

All databases participating in the Replication must have a unique global name. The global name should be the same as the database name.
a) Check the global_name

SQL> select *
2 from global_name
3 ;

GLOBAL_NAME
--------------------------------------------------------
MASTER2

10-Create the Public Database Link

a) Check the database links in the database

SQL> select *
2 from dba_db_links;

no rows selected

b) Create the public database link

1 create public database link master1
2* using 'master1'
SQL> /

Database link created.

c) Check the database link

1 select *
2* from dba_db_links
SQL> /

OWNER DB_LINK USERNAME HOST CREATED
---------- ------------------------------ ------------------------------ ---------- ---------
PUBLIC MASTER1 master1 03-DEC-04

11-Create the Private Database Link for REPADMIN

a) Create the private database link by connecting as REPADMIN

1 create database link master1
2* connect to repadmin identified by repadmin
3 /

Database link created.

b) Check the database link

SQL> l
1 SQL> select *
2 from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- ------------------------------ ------------------------------ ---------- ---------
PUBLIC MASTER1 master1 03-DEC-04
REPADMIN MASTER1 REPADMIN 17-MAY-04

12-Schedule “push” and “purge” jobs:

a) Connect with REPADMIN

b) Set up the “push” job to push the deferred queue automatically.

1 begin
2 dbms_defer_sys.schedule_push (
3 DESTINATION => 'master1',
4 INTERVAL => '/*10:Mins*/ sysdate + 10/(60*24)',
5 NEXT_DATE => sysdate,
6 STOP_ON_ERROR => FALSE,
7 delay_seconds => 0,
8 PARALLELISM => 1);
9* end;
SQL> /

PL/SQL procedure successfully completed.

c) Set up the “purge” job to purge the deferred queue automatically.

SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date => sysdate,
4 interval => '/*1:Hr*/ sysdate + 1/24',
5 delay_seconds => 0,
6 rollback_segment => '');
7 end;
8 /

PL/SQL procedure successfully completed.

Object Creation on Master1

a) Connect with REPDBA and create the following objects

DROP TABLE DEPT; CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ; DROP TABLE EMP; CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);SQL> conn repdba/repdba@master1
Connected.
SQL> show user
USER is "REPDBA"
SQL> DROP TABLE DEPT;
DROP TABLE DEPT
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
3 DNAME VARCHAR2(14) ,
4 LOC VARCHAR2(13) ) ;

Table created.

SQL> DROP TABLE EMP;
DROP TABLE EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table created.

b) Create Master Replication Object Group at Master1 by connecting as REPADMIN

1 begin
2 dbms_repcat.create_master_repgroup(
3 gname => '"GROUP1"',
4 qualifier => '',
5 group_comment => '');
6* end;
SQL> /
PL/SQL procedure successfully completed.

c) Add Master Database

SQL> begin
2 dbms_repcat.add_master_database(
3 gname => '"GROUP1"',
4 master => 'master2',
5 use_existing_objects => TRUE,
6 copy_rows => TRUE,
7 propagation_mode => 'ASYNCHRONOUS');
8 end;
9 /

PL/SQL procedure successfully completed.

d) Create Objects into Group i.e. “group1”
1 begin
2 dbms_repcat.create_master_repobject(
3 gname => '"GROUP1"',
4 type => 'TABLE',
5 oname => 'DEPT',
6 sname => '"REPDBA"',
7 copy_rows => TRUE,
8 use_existing_object => TRUE);
9* end;
SQL> /
PL/SQL procedure successfully completed.

1 begin
2 dbms_repcat.create_master_repobject(
3 gname => '"GROUP1"',
4 type => 'TABLE',
5 oname => 'EMP',
6 sname => '"REPDBA"',
7 copy_rows => TRUE,

8 use_existing_object => TRUE);
9* end;
SQL> /
PL/SQL procedure successfully completed.

e) Generate the Replication support

SQL> begin
2 dbms_repcat.generate_replication_support(
3 sname => '"REPDBA"',
4 oname => '"DEPT"',
5 type => 'TABLE',
6 min_communication => TRUE);
7 end;
8 /

PL/SQL procedure successfully completed.

1 begin
2 dbms_repcat.generate_replication_support(
3 sname => '"REPDBA"',
4 oname => '"EMP"',
5 type => 'TABLE',
6 min_communication => TRUE);
7* end;
SQL> /

PL/SQL procedure successfully completed.

f) Resume activity on the Master1

SQL> begin
2 dbms_repcat.resume_master_activity(
3 gname => '"GROUP1"');
4 END;
5 /

PL/SQL procedure successfully completed.

Test the Replication

a) Insert rows into DEPT at Master1 site, by connecting REPDBA

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

b) As soon as the “push” job executes at Master1 site, these inserted records will be pushed into the Master2 site. This behavior is vice versa as this is Multi Master Replication setup.

c) Insert rows into EMP at Master1 site, by connecting REPDBA

SQL> conn repdba/repdba@master1
Connected.

SQL> INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,
to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

1 row created.

SQL> commit;

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

d) Check the EMP table in Master2 site by connecting REPDBA
SQL> conn repdba/repdba@master2
Connected.
SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20

After 10 minutes again apply the SELECT statement.

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

The same records have been pushed to site Master2. So the Multi Master Replication
is working fine.

The same this can be applied vice versa as well.

f) connect repdba/repdba@master2
SQL> conn repdba/repdba@master2
Connected.

g) SELECT the rows from the EMP table

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

h) Insert one new record into EMP table

1 INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',
2* 7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30)
SQL> /

1 row created.

SQL> commit;

Commit complete.
i) Again SELECT from emp

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

j) Now connect repdba/repdba@master1

SQL> conn repdba/repdba@master1
Connected.

k) SELECT records from the emp table

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

The third record has yet not been pushed to Master1 site. This will be pushed as soon as the “push” job is executed at Master2 site.

Again SELECT the records from EMP table.

SQL> select *
2 from emp
3 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

Now the same records have been pushed to site Master1 from Master2 site. So the Multi Master Replication is working fine from both the ends.

Views to check the status of the propagated transaction

a) Connect to Master1 as REPDBA

SQL> conn repdba/repdba@master1
Connected.

b) Insert one record into EMP

SQL> INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839, to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

1 row created.

SQL> commit;

c) Select the table EMP

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

d) Check the view “defcall”

SQL> select *
2 from defcall;

CALLNO DEFERRED_TRAN_ID SCHEMANAME PACKAGENAME PROCNAME ARGCOUNT
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
0 10.13.263 REPDBA EMP$RP REP_INSERT 10

e) Check the view “deftrandest”

SQL> select *
2 from deftrandest;
DEFERRED_TRAN_ID DELIVERY_ORDER DBLINK
------------------------------ -------------- --------------
10.13.263 302468 MASTER2

f) connect to Master2 as REPDBA

SQL> conn repdba/repdba@master2
Connected.

g) Check the EMP Table

SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

h) connect to Master1 as REPADMIN and execute the following procedure to push the transaction to the Master2

SQL> execute dbms_defer_sys.execute('master2');
PL/SQL procedure successfully completed.

i) Connect to Master 2 as REPDBA
SQL> conn repdba/repdba@master2
Connected.

j) Check the EMP table
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

login page problem in Oracle APPS 11i

2008-11-05T10:16:00.000+03:00

We faced an interesting problem in Oracle APPS 11i, that login page was not displaying when we clicked on the Ebusiness home page on http://appsserver:port, instead showing the login page, it showed us nothing.

When we checked the error_log file for Apache in forms node, it was showing the following error,

[Tue Nov 4 15:55:29 2008] [error] [client 172.21.8.50] client denied by server configuration: /oracle/app/comn/portal/uat_essappuat/oprocmgr-service

We checke almost all the configuration files, we also checked the tnsping from forms node, just to check whether it is able to connect to the database, we used sqlplus on forms node, to connect to the database node, and all was working.

we opened the SR with Oracle Support, and having seen the logs, they referred us to use note 372096.1, And when we checked the hosts file of forms node, the entry for the forms node server was with the following entry.

10.x.x.x servername.wrongdomain.com servername.correctdomain.com sername

what we did, we removed the servername,wrongdomain.com from /etc/hosts file, and restared the forms / appache services with ./adstrtall.sh and when we checked every thing was working and users were able to see the login page and logged in successfully.

cheers

Deleting bulk rows in batches

2008-07-07T15:23:00.000+03:00

There might be severe problems if the bulk number of rows are deleted fromt the production table. As this will lock the table in shared mode. And resource of the server will be used, offcourse it will use the UNDO tablespace heavily.

Following is the procedure which can be used to delete the bulk rows in batches on certain conditions. Also it will run in loops which can be controlled and also once it will delete the records it will put the message into purge_history table.

declare
type table_rowid_Array is table of rowid index by binary_integer;
l_rids table_rowid_Array;
v_counter number := 0;
v_counter_total number := 0;
v_id_counter number := 0;
begin
for i in 1 .. 500
loop
v_id_counter := i;
select rowid
bulk collect into l_rids
from BIG_LOG_TABLE where log_date < '1-JUN-2008' and rownum < 25001;
for i in 1 .. l_rids.count
loop
delete from BIG_LOG_TABLE where rowid = l_rids(i);
v_counter := v_counter +1 ;
end loop;
dbms_output.put_line( v_counter ' rows deleted' );
v_counter_total := v_counter_total + v_counter;
v_counter := 0;
insert into purge_history values (v_id_counter,v_counter_total,sysdate,BIG_LOG_TABLE );
commit;
end loop;
dbms_output.put_line( v_counter_total ' total rows deleted' );
end;

Recover the table data back in time with Flashback Query

2008-03-30T10:57:00.000+03:00

Recover the table data back in time with Flashback Query

Flashback is a pretty useful feature in the Oracle 9i. It can save a lot of time and energy. To use this feature the UNDO_MANAGEMENT should be set to AUTO in the database. And offcourse the UNDO_RETENTION should be large enough so the Flashback Query can recove the data back in time.

We recovered one of the table data. Some records were updated incorrectly and was commited. Then we recovered the old data just before the update statement took place. we used the following procedure and we followed the metlaink document number 174425.1. We went 60 minutes back in time from the current time.

Procedure to recover data back in time with Flashback feature of Oracle 9i:

DECLARE
CURSOR FLASH_RECOVER IS
select * from LIST_GENERATION;
list_generation_recover_rec LIST_GENERATION%ROWTYPE;
begin
DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate - 60/1440);
open FLASH_RECOVER;
DBMS_FLASHBACK.DISABLE;
loop
FETCH FLASH_RECOVER INTO list_generation_recover_rec;
EXIT WHEN FLASH_RECOVER%NOTFOUND;
insert into LIST_GENERATION
values
(list_generation_recover_rec.PARTY_ID ,
list_generation_recover_rec.TRANS_DATE ,
list_generation_recover_rec.CALL_FLAG ,
list_generation_recover_rec.CAMPAIGN_SCHEDULE_ID ,
list_generation_recover_rec.LIST_TYPE ,
list_generation_recover_rec.REQUEST_ID ,
list_generation_recover_rec.CREATION_DATE ,
list_generation_recover_rec.CREATED_BY ,
list_generation_recover_rec.LAST_UPDATE_DATE ,
list_generation_recover_rec.LAST_UPDATED_BY );
end loop;
CLOSE FLASH_RECOVER;
commit;
end;

PL/SQL procedure successfully completed.

apps-sql>select count(*) from LIST_GENERATION;

COUNT(*)
----------
5693