Tuesday, February 10, 2009

Clean Up a Failed Install of Oracle Apps 11.5

How to Clean Up a Failed Install of Oracle Applications 11.5

The instructions will remove ALL Oracle Applications environments from the server(s) involved. Do use for removing one environment and leaving one in tact.

NOTE: This method should only be used on a system where no other Oracle software has been installed and this is a first time installation of Oracle Applications.

1. Using the Windows Explorer, navigate to the c:\Program Files directory and remove the following: \Oracle directoryconfig.txt file rapidinstall directory

2. If any Oracle Services are running stop them via the control panel otherwise you may not be able to delete the Oracle files.

3. Using the Windows NT Explorer, navigate to the drive or drives where you specified the ORACLE_HOME and APPL_TOP to be installed and remove those files.

4. Using the registry editor (regedit), navigate to the following directory structure: HKEY_LOCAL_MACHINE\SOFTWARE select the Oracle folder and delete it.

5. Using the registry editor, remove all the services that were installed.WARNING! Be very careful. If you delete the wrong service you can make your NT stop functioning!
5a. Navigate to the following directory: HKEY_LOCAL_MACHINE->SYSTEM->ControlSet001->Services
5b. Remove all entries beginning with Oracle.
5c. Navigate to the following directory: HKEY_LOCAL_MACHINE->SYSTEM->ControlSet002->Services
5d. Remove all entries beginning with Oracle. HKEY_LOCAL_MACHINE->SYSTEM->CurrentControlset->Services
5f. Remove all entries beginning with Oracle.

6. Remove any Oracle related "User" environment variables
6a. Open Control Panel/System
6b. Click on Environment Tab.
6c. Remove any variables from the User section, such as NLS*, ORACLE*, LOCAL, or APPL*
6d. In the System Variables section delete CLASSPATH if set.
6e. Also remove any Oracle specific directories from the PATH variable.
6f. Cleanup the wv_gateway environment variable

7. Perform instructions in step 6 for the System environment section

8. Right-click on the start button and select open all users. Select program and delete any oracle directories that exist. Also delete the two Apache directories.

9. Reboot the machine.

Monday, February 9, 2009

Oracle Apps ERP 11i (11.5.10.2) setup on Win2003

Single Node Oracle Applications 11i (11.5.10.2) Setup on 32bit Windows 2003 Enterprise Edition Server.
1. Ask your system administrator to prepare a Windows 2003 Server with Service pack 2. Create a domain user (applmgr) and add it to the administrator group of this server. From the SAN storage map and mount 300 to 400 GB space as ntfs on to this server.
c:\>diskpart
DISKPART> automount enable
Automatic mounting of new volumes enabled.

2. Install the following softwares in c:\ driver and see to it that the directory path should not have any space.

MKS 9.1 Toolkit --> c:\mks
VC++-->c:\vc
GNUMAKE-->c:\Gnumake
XMLSDK

Note that JDK and PERL should not exist in the server. This will be installed automatically while we do the setup of oracle applications 11i (11.5.10.2)

-->Copy link.exe from c:\vc\vc98\bin, copy cc.exe, which.exe, gnumake.exe to c:\windows\system32
-->Set path=%PATH%;C:\Gnumake\make-3.81;C:\Gnumake\make-3.81\WinRel
-->Add node name, ipaddress in c:\windows\system32\drivers\etc\hosts file

3. On Windows platforms, Rapid Install requires Microsoft XML Parser 3.0 Service Pack 4 (or later) in order to set up and start services. Before you begin your installation, follow these steps to determine if the correct version of the XML Parser is installed on your system, and take action as necessary.
--> Open the Windows Explorer
--> Navigate to the system root directory (default is C:\winnt\system32).
--> Right-click msxm13.dll. Select Properties. them click Version.
--> If XML Parser is not already installed, or if the version on your system is not 3.0 SP4 (or later) , go to the following URL and download the Microsoft XML Parser 3.0 SP4 Software Development Kit:

http://www.microsoft.com/downloads/ThankYou.aspx?familyId=b43
2cd45-b7ed-4c32-a443-ec56f10ee175&displayLang=en

--> After installing MSXML 3.0 SP4 SDK, copy msxm12.lib from \lib to \lib, where is the installed location of the MSXML 3.0 SP4 SDK, and is the installed location of Visual C++/C 6.0.

i.e from C:\Program Files\Microsoft XML Parser SDK\lib to C:\VC\VC98\Lib directory.

-->Restart server after all the above setups and before installing oracle applications 11i.

4. Now we will start rapidwiz to do the oracle applications setup. If you were doing the setup for multinode system then rapidwiz should be started from the database node. It makes DB setup first then if you want to select Apps setup seperately then from Apps Node start rapidwiz and select config.txt file to do the apps-tier setup.

Single node oracle applications setup has Oracle Database 9i (9.2.0.6) and oracle applications 11i (11.5.10.2) by default. Later you can also upgrade the database from 9i to 10g (10.2.0.4).

Start rapidwiz to do the setup,
c:\> cd Stage11i\startCD\Disk1\rapidwiz
c:\Stage11i\startCD\Disk1\rapidwiz> rapidwiz.cmd


NOTE: During the setup on Dell servers I faced the following error and resolved the problem through metalink note 433203.1 Oracle Applications 11i Install Fails on Windows when Number of Processors is Greater than 12

Problem:
There was an error while running the command -
I:\oracle\proderpdb\9.2.0\temp\proderp_oragridctl\adrun9i.cmd APPS APPS

The process tried to write to a nonexistent pipe.
The process tried to write to a nonexistent pipe.
The process tried to write to a nonexistent pipe.
The process tried to write to a nonexistent pipe.

RW-50004: Error code received when running external process. Check log file for details.
Running Database Install Driver for PRODERP instance Database Availability Check

Cause:
The issue is caused by the following unpublished bug :
Bug.5661195 RAPIDWIZ FAILS ON WINDOWS PLATFORM WHEN NUMBER OF CPU CORE EXCEEDS 12
This is a Release 12 bug which has been fixed in the file InstantiateFile.java for R12 . This fix will be later included in R12 TXK patch. But it is not yet fixed for 11i. The following bug is the backport logged for 11i version. This bug is also unpublished.
Bug.5694544 BACKPORT OF Bug 5661195

Solution
In the server it was showing 16 processors (4 x dual core x 2 logical processors=16 processors) so even after changing the value of the environment variable NUMBER_OF_PROCESSORS to 12 and after rebooting the value was again set back to 16. So the system administrator went to the BIOS of the server and disabled logical processors i.e disabled HYPER THREADING of processors, after this it showed only 8 processors(4 x dual core=8 processors). Then restarted the server and started the installation without any issues.

To overcome this problem encountered on Windows, please execute the following steps:
1. Right click on "My Computer" > "Advanced" tab > Click on button "Environment variables".
2. Edit the value of NUMBER_OF_PROCESSORS from the value which is greater than 12(e.g. in this case 16) to 12. Apply the changes.
3. Reboot the machine to ensure that the environment variable NUMBER_OF_PROCESSORS is set to the corrected value i.e.12.
4. Clean the current install. Refer the following metalink document :Note 143976.1 How to Clean Up a Failed Install of Oracle Applications 11.5.2 on an NT Platform
5. Check the value of NUMBER_OF_PROCESSORS on the command prompt before the rapidwiz is started
echo %NUMBER_OF_PROCESSORS%
6. Run rapidwiz and finish database(of the Oracle Applications) installation.
7. Once the Oracle Applications is installed, the value of environment variable NUMBER_OF_PROCESSORS could be reset to its original value.

Screen 1: Install Oracle Applications - Welcome

Screen 2: Select wizar operation

Select 'Install Oracle Applications E-business 11i' radio button.
Don't select 'Use Express Configuration'
Don't select 'Upgrade

Screen 3: Load Configuration
Select 'NO'.

Screen 4: Install Type
Select 'Single-node (All services on a single node)' radio button.
Don't select 'Multi-node (Services distributed on two or more nodes)'

Screen 5: Database Type
Select Database Type as 'Vision Demo Database' from list box and give database name as 'proderp'

Screen 6: Database Install Information
Base Install Directory = i:\oracle
Oracle Home= i:\oracle\proderpdb\9.2.0
Data Top (SYS)=i:\oracle\proderpdata
Data Top (LOG)=i:\oracle\proderpdata
Data Top (TXN)=i:\oracle\proderpdata
Data Top (Archive)=i:\oracle\proderpdata

Screen 7: Admin Server / Concurrent Mgr / Forms Server / Web Server
MKS directory=c:\mks
MSDEV directory=c:\vc
Base install directory=i:\oracle
APPL_TOP Mount point=i:\oracle\proderpappl
APPL_TOP aux. 1=i:\oracle\proderpappl
APPL_TOP aux.2=i:\oracle\proderpappl
APPL_TOP aux.3=i:\oracle\proderpappl
COMMON_TOP=i:\oracle\proderpcomn
8.0.6 ORACLE_HOME=i:\oracle\proderpora\8.0.6
iAS ORACLE_HOME=i:\oracle\proderpora\iAS
Temp Directory=i:\oracle\proderpcomn\temp

Screen 8: Global System Settings
Domain Name=oracle.com
Port Pool=2
Database Port=1523
RPC Port=1628
Reports Port=7002
Web Listener Port=8002
OProcMgr Port=8202
Servlet Port=8802
Forms Listener Port=9002
Metric Server Data Port=9102
Metric Server Req. Port=9202
JTF Fulfillment Server Port=9302
Map Viewer Servlet Port=9802
OEM Web Utility Port=10002
VisiBroker OrbServer Agent Port=10102
MSCA Server Port=10202
MSCA Dispatcher Port=10302
Java Object Cache Port=12347
OACORE Servlet Port Range=16020-16029
Discoverer Servlet Port Range=17020-17029
Forms Servlet Port Range=18020-18029
XMLSVCS Servlet Port Range=19020-19029

Screen 9: Save Instance Specific Configuration
Enter the full path of the configuration file=c:\config.txt
Here when you press next the following is performed.
-Port Availability
-O/S User and Group Check
-File System Check
-Host/Domain Check
-System Utilities Check
-Operating System Check
-Port Uniqueness
-File Space Check
-OS Patch Checks

If any of the above failed then first resolve it and then proceed with the following steps else the setup will not be completed successfully.

Screen 10: Component Installation Review
The setup portion of the installation is complete. Clicking Next will install the Oracle Application components.
Screen 11: Post Install Checks
This screen shows that this instance has passed all of the post install tests like Database Availability check, Environment File Check, DBC File check, HTTP Check, JSP Check and PHP Check.

Screen 10: Finish
Finish Screen completes the setup. Now to start the applications page run the following URL in the browzer,

http://servername.domainname:8002/OA_HTML/AppsLocalLogin.jsp


Login as SYSADMIN/SYSADMIN to connect to the applications as a super user.


Your Comments/Queries: email me at masamimalik@yahoo.com

Thursday, February 5, 2009

Recovery of a lost datafile without backup

Its the demo for recovery of a lost datafile without backup, but you should have database in archivelog in order to make complete recovery and also all the archive logs along with the current redo log should be available, otherwise in noarchivelog mode and archivelog missing you may lose data.

Every steps is self explainatory so i feel need'nt to add some textual description during demo.

sql> conn system/manager

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Feb 8 10:35:24 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
ORCL

SQL> create tablespace my_tablespace datafile 'C:\oracle\product\10.2.0\oradata\orcl\my_datafile.dbf' size 500M;

Tablespace created.

SQL> set linesize 1000
SQL> select * from v$tablespace ;

TS# NAME INC BIG FLA
---------- ------------------------------ --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP YES NO YES
6 EXAMPLE YES NO YES
9 MY_TABLESPACE YES NO YES

7 rows selected.

SQL> alter user scott default tablespace my_tablespace;

User altered.

SQL> conn scott/tiger@orcl
Connected.

SQL> create table my_table as select * from all_objects ;

Table created.

SQL> desc my_table

Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)


SQL> conn sys/sys@orcl as sysdba
Connected.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>cd C:\oracle\product\10.2.0\oradata\orcl

C:\oracle\product\10.1.0\oradata\orcl>dir *.dbf
Volume in drive C has no label.
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008 10:41 AM 157,294,592 EXAMPLE01.DBF
02/08/2008 10:41 AM 524,296,192 MY_DATAFILE.DBF
02/08/2008 10:41 AM 283,123,712 SYSAUX01.DBF
02/08/2008 10:41 AM 482,353,152 SYSTEM01.DBF
02/07/2008 11:43 PM 104,865,792 TEMP01.DBF
02/08/2008 10:41 AM 31,465,472 UNDOTBS01.DBF
02/08/2008 10:41 AM 56,369,152 USERS01.DBF
7 File(s) 1,639,768,064 bytes
0 Dir(s) 63,114,174,464 bytes free

C:\oracle\product\10.2.0\oradata\orcl>del my_datafile.dbf

C:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive C has no label.
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008 10:41 AM 157,294,592 EXAMPLE01.DBF
02/08/2008 10:41 AM 283,123,712 SYSAUX01.DBF
02/08/2008 10:41 AM 482,353,152 SYSTEM01.DBF
02/07/2008 11:43 PM 104,865,792 TEMP01.DBF
02/08/2008 10:41 AM 31,465,472 UNDOTBS01.DBF
02/08/2008 10:41 AM 56,369,152 USERS01.DBF
6 File(s) 1,115,471,872 bytes
0 Dir(s) 63,638,204,416 bytes free

C:\oracle\product\10.2.0\oradata\orcl>

SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF'


SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ---------
6 ONLINE ONLINE FILE NOT FOUND 0

SQL>alter database create datafile
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF' as
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE01.DBF';

Database altered.

C:\oracle\product\10.1.0\oradata\orcl>dir my_*.dbf
Volume in drive C has no label.
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl
02/08/2008 10:53 AM 524,296,192 MY_DATAFILE01.DBF
1 File(s) 524,296,192 bytes
0 Dir(s) 63,110,365,184 bytes free

C:\oracle\product\10.1.0\oradata\orcl>

SQL> recover tablespace my_tablespace
Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn scott/tiger@orcl
Connected.
SQL> desc my_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

Wednesday, February 4, 2009

Alternative for standby/dataguard - Manual

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2
Applies to any platform.

Goal:
Alternative for standby/dataguard in standard edition
Standby/Dataguard is a high availability feature available only with Enterprise edition.

Here we discuss how to create a duplicate(clone) database of production DB in standard edition
which supports the following features of (physical)standby in Enterprise Edition.

++ Keep the clone database in synchronized state with primary by applying
change records(archivelogs) from primary.
++ Open the database in read only mode for reporting purpose.

Solution:
This alternative standby database doesn’t support advance dataguard features like switch over,automatic log shipping and apply services(Managed recovery) etc.

Note:
1. primary database archivelogs have to be shipped manually to the log_archive_dest of standby instance.
The parameter standby_archive_Dest has no meaning this standby setup.

2. The normal procedure of setting up a clone instance with same DB_NAME as that of production is applicable for this setup also if you are planning to have primary and standby in the same box.
Eg: usage of parameter lock_name_space.

Here are the steps to create the dummy standby database
Take a backup of primary database
++ Any supported method can be used to take this backup.
Eg: RMAN/MANUAL/HOT/COLD..

Take a backup of controlfile from primary.
++ It can be a cold copy of controlfile after a clean shutdown of the DB(Recommended)
++ If you can't shutdown the DB make a binary backup of the controlfile using

SQL>alter database backup controlfile to '';

++ Also you can use a controlfile taken using rman backup keeping following point in mind

Note: The controlfile used for standby should be having a checkpoint_change# higher than that
of restored datafiles.For this you need to restore datafiles from a backup taken prior to
the time stamp of the controlfile which we are planning to use for standby.

Create a new instance for standby and keep in nomount stage
++ The following parameters need to be same in both primary and standby.
DB_NAME,db_block_size,compatible etc.
The standby instance can have a different instance_name but same DB_NAME as the primary instance.
If you want to mount two database with same DB_NAME in the same node you need to use lock_name_space if they are under same ORACLE_HOME(This is applicable even if you use different instance_name)

++ The control_files parameter of standby should point to the backup controlfile that we took in step 2.

SQL>startup nomount(pfile =)

Mount the standby DB using controlfile taken from primary.

++ SQL>alter database mount ( Please keep in mind that we are not creating an actual standby database.
So "STANDBY" keyword is not needed in alter database commands)

Restore the datafiles to the standby location.(Using rman or manually)
++ If you have a different file system structure you can use "set newname" command in rman to restore to a
different location.If you are restoring files manually,restore the files to new location and follow the next step
to rename files in the controlfile.

If the standby site is having a different file system structure rename the datafiles using
SQL>alter database rename file to

Start the recovery.
SQL>recover database using backup controlfile until cancel

Apply all the available archivelogs
(You should apply atleast few archivelogs to make the checkpoint_change# of controlfile and datafiles are
same.This is to make the datafiles and controlfile consistent)

If you want to open the DB for reporting purpose read only mode.

SQL> alter database open read only.

Add tempfiles to temp tablespace.

SQL> alter tablespace temp add tempfile '' size ..
This is because recovery using backup controlfile command automatically removed tempfile records from controlfile.
Now this DB is ready for users to use for reporting.

Now as primary keeps generating archivelogs you need to keep the standby in SYNC with primary

++ Copy the new archivelogs to log_archive_dest of standby
++ Shutdown the standby and startup mount.

++ Apply the available archivelogs using 'recover database using backup controlfile until cancel' command.
++ Once all the archivelogs are applied you can open the standby in read only mode.

If you want to failover to this new standby for disaster recovery)
++ Apply maximum archivelog from primary to standby(Including online redologs if possible)

SQL>recover database ung backup controlfile until cancel
++ alter database open resetlogs.

NOTE:
1) If you create a new tablespace in primary you may get the following errors while applying the corresponding logs in
reporting server.
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 5: '/home/jobin/tes1.dbf'

At this stage you need to run a command like this to create an empty datafile in standby.

a) SQL>select name from v$datafile where name like '%UNNAMED%';
b) SQL> alter database create datafile '/home/oracle/product/ora101/dbs/UNNAMED00005'
as '/oradata/dummy/test01.dbf';
Where /oradata/dummy is location for datafiles in standby.
Now you can restart the recovery process.

2) To find out the last archived log applied on this standby you can use this query on mount stage.
SQL>select max(fhrba_Seq) from x$kcvfh;

mlnote=333749.1 (24-JUN-2008)