Oracle AppsDBAs World

web cache cloning

leesenshan@gmail.com (Senthil Kumaran) — Fri, 08 Aug 2008 17:18:00 +0000

WEBCACHE CLONING
===================

SOURCE SERVER (192.9.200.251)
DESTINATION SERVER (192.9.200.227)

Create groups and users using the following commands

#groupadd dba

#groupadd oinstall

#useradd -g oinstall -G dba oraweb

id must be same in source and destination
(uid=500(oraweb) gid=500(oinstall) groups=500(oinstall),501(dba) context=user_u:system_r:unconfined_t)

Change the permisson

chmod 777 /u01

copy datas from source to destination through
scp -r oracle oraweb@192.9.200.227:/u01

after copying
set the environment in .bashprofile

su - oraweb

vi .bash_profile
export ORACLE_HOME=/u01/oracle/10.1.2/OraHome_1
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin

change hostname(if its required only)
vi /etc/sysconfig/network

put hostentry
vi /etc/hosts

change ip
netconfig

start emctl start iasconsole
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://csd.oneapps.com:1156/emd/console/aboutApplication
Oracle Enterprise Manager 10g Application Server Control is running.

start opmnctl startall
starting opmnctl got error

log file=cat /u01/oracle/10.1.2/OraHome_1/webcache/logs/event_log

error: couldnot increase the numberof file/socket descriptors to 1100
======
check $ORACLE_HOME/webcache/webcache.xml

MULTIPORT>
==========

solution steps
=================
(Port numbers less than 1024 are reserved for use by privileged processes on UNIX.
If you want to configure OracleAS Web Cache to listen on a port less than 1024, such as on port 80, then the webcached executable must run as the root user)

set oraclehome

export ORACLE_HOME=/u01/oracle/10.1.2/OraHome_1

solution: from root user execute ./webcache_setuser.sh setidentity 500
=========

Create A Production (Full or Partial) Duplicate On The Same Host

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 17:44:00 +0000

Reference:
-------------
Primary DB : ORCL
Clone DB : AUX

Production Database should be archive enabled.

Startup mount;
alter database archivelog;
alter database open;
archive log list;

Recovery catalog for RMAN

Creating the Recovery Catalog Owner
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:

% sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE tools
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON tools;

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;

Creating the Recovery Catalog

% rman catalog rman/rman@ORCL

RMAN> CREATE CATALOG;

Registering the target database

% rman TARGET / CATALOG rman/rman@ORCL

RMAN> REGISTER DATABASE;

Reference : Note:452529.1
Reference : RMAN: How to Query the RMAN Recovery Catalog ( Note:98342.1 )

Example Source Listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /newpart//product/10.2.0/)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /newpart//product/10.2.0)
(SID_NAME = ORCL)
)
(SID_DESC =
(GLOBAL_DBNAME = AUX)
(ORACLE_HOME = /newpart//product/10.2.0)
(SID_NAME = AUX)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

Example Source tnsnames.ora

AUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = AUX)(UR=A)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Note : Make use of netca and netmgr to configure listener and tnsnames

# Find Production Database Files:

SQL> select name from v$datafile;
Create the Auxiliary Database directories needed
cd $ORACLE_HOME/dbs
create parameter file initAUX.ora

db_file_name_convert = ('/old/path1', '/new/path1',
'/old/path2', '/new/path2',
'/old/path3', '/new/path3')

log_file_name_convert = ('/old/path1', '/new/path1',
'/old/path2', '/new/path2',
'/old/path3', '/new/path3')

eg:-

db_name = aux
db_block_size = 8192
compatible = 10.2.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/newpart
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora
SQL> exit
Start the Duplication
ORACLE_SID=AUX; export ORACLE_SID # ksh
sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora
SQL> exit

# Set your SID back to the TARGET for duplication.

> rman trace.log

Recovery Manager: Release 10.2.0.1.0 - Production
Copyright (c) Oracle. All rights reserved.

RMAN> connect target
connected to target database: V10GREL4 (DBID=2510891965)

RMAN>backup database;

RMAN>sql 'alter system switch logfile';

RMAN> connect auxiliary sys/pwd@AUX
connected to auxiliary database: AUX (not mounted)

RMAN> duplicate target database to AUX device type disk;
Once this is done, login to duplicate database with alter database open resetlogs.

Creating a Duplicate Database on a New Host Using RMAN

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 17:32:00 +0000

For reference:
----------------

Primary Database SID: ORCL
Duplicate Database SID: AUX
RMAN Catalog SID: RMAN

Backup of the primary database.

Host A (Target)

# export ORACLE_SID=ORCL

# rman target=/ catalog=rman/rman@ORCL

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
This command will perform a full database backup including archivelogs and the current controlfile.

Host B (Aux)

Making the backup available for the duplicate process.

If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created.
RMAN> list backup;

Create same directory of host b and give appropriate permissions for the oracle user

Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database.
---------------------------------------------------------------
db_name = aux
db_block_size = 8192
compatible = 10.2.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/d02/oradata/aux/control01.ctl')
db_file_name_convert = ('/newpart/oradata/orcl',
'/d02/oradata/aux')
log_file_name_convert = ('/newpart/oradata/orcl',
'/d02/oradata/aux')
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
------------------------------------------------------------------------------
Following the creation of the initAUX.ora startup nomount the auxiliary instance.
export ORACLE_SID=AUX

sqlplus '/as sysdba'

startup nomount;

Ensuring SQL*NET connections to primary database and RMAN catalog are working.

Host B(AUX)

sqlplus ‘sys/oracle@PROD as sysdba’

sqlplus rman/rman@PROD (not mandatory)
Add tnsnames.ora entry – Eg:
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
Prepare RMAN duplicate script.

run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
Save it as dup.sql

Execute the RMAN script

Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
# export ORACLE_SID=AUX

# rman target sys/pwd@ORCL catalog rman/rman@ORCL auxiliary /

RMAN> @dup.sql
After this, login to aux database and alter database open with resetlogs option.

Database Cloning

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 17:24:00 +0000

Database Cloning
--------------------

1. Ensure that your database is recoverable back to original location and state by backing it up before executing this procedure:

Database in NON-ARCHIVE LOG MODE - take an off-line database backup.
Database in ARCHIVELOG MODE - take an on-line or off-line database backup.

2. Connect to sql*plus as the user ‘SYS AS SYSDBA’ and ensure that all users have logged off the system by executing the following command.

SQL> connect / as sysdba
Connected.
SQL> select username from v$session;
SQL> select username from v$session;
USERNAME

SYS
8 rows selected.
SQL>

You should see several fields returning with no username (these are internal oracle logins and the will not affect your operations) and one SYS login (this is you).

3. Place the system in restricted mode:

SQL> alter system enable restricted session;

System altered.

4. Connect as SYS with SYSDBA privileges and execute the following SQL command:

SQL> connect / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
SQL>

The [alter database backup controlfile to trace;] command will generate a SQL script that can be used to regenerate the database.

5. Execute the sql*plus command show parameter to find your user dump directory.

SQL> show parameter user_dump_dest
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
user_dump_dest string /opt/app/oracle/product/admin/orcl/udump

6. Exit to the OS layer and move to the user_dump_dest directory. Trace file names are generated by oracle in the following format [instance_name]+[oracle generated number].trc, so use the timestamp to locate the last trace file generated. Open the file in a text editor and remove everything above the ‘STARTUP NOMOUNT ..' statement. Alter the ‘CREATE CONTROLFILE’ statement for the following uses:

Rename DB in Archivelog mode:
CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS
ARCHIVELOG

Rename DB in Non-Archivelog mode:
CREATE CONTROLFILE SET DATABASE "new_db_name" NORESETLOGS
NOARCHIVELOG

Reuse DB name in Archivelog mode:
CREATE CONTROLFILE REUSE DATABASE "db_name" RESETLOGS ARCHIVELOG

Reuse DB name in Non-Archivelog mode:
CREATE CONTROLFILE REUSE DATABASE "db_name" NORESETLOGS
NOARCHIVELOG

7. Edit the rest of this file to reference the correct locations of you log files, datafiles and temp files.

8. Move the trace file to an appropriate location and name to recreate_db.sql.

9. Connect as SYS with SYSDBA privileges and shutdown the database in immediate mode:
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

10. Move the following files to there corresponding locations in updated recreate_db.sql file.
DATAFILES
Log files

11. Restore or copy the backup and the archived logs (if any) to the new machine.

12. Move your INIT$ORACLE_SID.ORA file to its new location. Ensure your controlfile and archive destinations in INIT are set properly to the new locations.

13. Connect to SQLPLUS on your new machine as SYS AS SYSDBA and run create_db.sql script as follows:

SQL> @[directory_location]/create_db.sql

14. Change the global database name if you renamed your database as follows:

SQL> ALTER DATABASE RENAME GLOBAL_NAME = new_db_name;

15. Perform full system backup and store in a safe location.

11g top new features

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 16:49:00 +0000

Oracle Database 11g Top New Features
===============================

1) Automatic Diagnostic Repository [ADR]

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Performance Analyzer (SPA) New

13) DBMS_STATS Enhancements

14) The Result Cache

15) Total Recall (Flashback Data Archive)

Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

Oracle 11g Database DBA New Features with brief explanation
==========================================

# Database Capture/replay database workloads :
This allows the total database workload to be captured, transferred to a test database
create from a backup or standby database, then replayed to test the affects of an upgrade or
system change. Currently, these are working to a capture performance overhead of 5%,
so this will capture real production workloads

# Automatic Memory Tuning:

Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was already
introduced in Oracle 10g. But In 11g, all memory can be tuned automatically by setting one
parameter. We can literally tell Oracle how much memory it has and it determines how
much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set

# Interval partitioning for tables :

Interval partitions are extensions to range partitioning. These provide automation for
equi-sized range partitions. Partitions are created as metadata and only the start partition is
made persistent. The additional segments are allocated as the data arrives. The additional
partitions and local indexes are automatically created.

# Feature Based Patching:

All one-off patches will be classified as to which feature they affect. This allows you to easily
identify which patches are necessary for the features you are using. EM will allow you to
subscribe to a feature based patching service, so EM automatically scans for available
patches for the features you are using

# RMAN UNDO bypass :

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of
useless information. Now rman can bypass those types of tablespace. Great for exporting a
tablespace from backup.

# Virtual columns/indexes :

User can create Virtual index on table. This Virtual index is not visible to optimizer, so it will
not affect performance, Developer can user HINT and see is Index is useful or not.Invisible
Indexesprevent premature use of newly created indexes

# New default audit settings :

Oracle database where general database auditing was "off" by default, logging is intended
to be enabled by default with the Oracle Database 11g beta secure configuration. Notable
performance improvements are planned to be introduced to reduce the performance
degradation typically associated with auditing.

# Case sensitive password :

Passwords are expected to also become case sensitive This and other changes should result
in better protection against password guessing scenarios. For
example, in addition to limiting the number of failed login attempts to 10 (default
configuration in 10gR2), Oracle 11g beta’s planned default settings should expire
passwords every 180 days, and limit to seven the number of times a user can login with an
expired password before disabling access.

# Faster DML triggers : Create a disabled trigger; specify trigger firing order

# Fine grained access control for Utl_TCP:

in 10g all port are available, now it is controlled.

# Data Guard supports "Flashback Standby"

# New Trigger features

# Partitioning by logical object and automated partition creation.

# LOB's - New high-performance LOB features.

# New Oracle11g Advisors

# Enhanced Read only tables

# Table trigger firing order

# Enhanced Index rebuild online : - Online index build with NO pause to DML.

# No recompilation of dependent objects:- When

A) Columns added to tables
B) Procedures added to packages

# Improved optimizer statistics collection speed

# Online index build with NO pause to DML

# Read only table :-

alter table t read only

alter table t read write

Oracle 11g Database SQL/PL-SQL New Features
----------------------------------------------

Fine Grained Dependency Tracking:

In 11g we track dependencies at the level of element within unit. so that these changes have
no consequence

• Transparent performance improvement
•Unnecessary recompilation certainly consumes CPU

create table t(a number)

create view v as select a from t

alter table t add(Unheard_Of number)

select status from User_Objectswhere Object_Name = 'V'
- -----
VALID

No recompilation of dependent objects when Columns added to tables OR Procedures
added to packages

> Named and Mixed Notation from SQL:

select fun(P4=>10) from DUAL

In 10g not possible to call function in select statment by passing 4th parameter,
but in 11g it is possible

> PL/SQL "continue" keyword - It is same as we read in c/c++ loop

> Support for “super”: It is same "super" in Java.

> Powerfull Regular Expression:

Now we can access data between TAGS like data between tags .........

The new built-in REGEXP_COUNT returns the number of times the pattern is matched in the
input string.

> New table Data Type "simple_integer"

> SQL Performance Analyzer(SPA) :

It is same as Database replay except it not capture all transaction.The SQL Performance
Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA
provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a
performance baseline before a major database or system change, make the desired change
to the system, and then replay the SQL workload against the modified database or
configuration. The before and after performance of the SQL workload can then be compared
with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that
are now performing poorly and tune them via the SQL Tuning Advisor

> Caching The Results with /*+ result_cache */ :

select /*+ result_cache */ * from my_table, New for Oracle 11g, the result_cache hint caches
the result set of a select statement. This is similar to alter table table_name cache,but as you
can adding predicates makes /*+ result_cache */ considerably more powerful by caching a
subset of larger tables and common queries.

select /*+ result_cache */ col1, col2, col3 from my_table where colA = :B1

> The compound trigger :

A compound trigger lets you implement actions for each of the table DML timing points in a
single trigger

> PL/SQL unit source can exceeds 32k characters

> Easier to execute table DDL operations online:

Option to wait for active DML operations instead of aborting

> Fast add column with default value:

Does not need to updateall rows to default value.

Oracle 11g Database Backup & Recovery New Features
------------------------------------------------

* Enhanced configuration of archive deletion policies Archive can be deleted , if it is not
need DG , Streams Flashback etc When you CONFIGURE an archived log deletion policy
applies to all archiving destinations, including the flash recovery area. BACKUP ... DELETE
INPUT and DELETE... ARCHIVELOG use this configuration, as does the flash recovery area.
When we back up the recovery area, RMAN can fail over to other archived redo log
destinations if the flash recovery area is inaccessible.

* Configuring backup compression:

In 11g can use CONFIGURE command to choose between the BZIP2 and ZLIB compression
algorithms for RMAN backups.

* Active Database Duplication:

Now DUPLICATE command is network aware i.e.we can create a duplicate or standby
database over the network without taking backup or using old backup.

* Parallel backup and restore for very large files:

RMAN Backups of large data files now use multiple parallel server processes to efficiently
distribute theworkload for each file. This features improves the performance of backups.

* Improved block media recovery performance:

RECOVER command can recover individual data blocks.
RMAN take older, uncorrupted blocks from flashback and the RMAN can use these blocks,
thereby speeding up block media recovery.

* Fast incremental backups on physical standby database:

11g has included new feature of enable block change tracking on a physical standby
database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING SQL statement).
This new 11g feature enables faster incremental backups on a physical standby database
than in previous releases.because RMAN identifywe the changed blocks sincethe last
incremental backup.

11g ASM New Features
-----------------------

The new features in Automatic Storage Management (ASM) extend the storage
management automation, improve scalability, and further simplify management for
Oracle Database files.

■ ASM Fast Mirror Resync

A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executed
after a failed disk has been repaired. The command first brings the disk online for
writes so that no new writes are missed. Subsequently, it initiates a copy of all extents
marked as stale on a disk from their redundant copies.
This feature significantly reduces the time it takes to repair a failed diskgroup,
potentially from hours to minutes. The repair time is proportional to the number of
extents that have been written to or modified since the failure.

■ ASM Manageability Enhancements

The new storage administration features for ASM manageability include the following:

■ New attributes for disk group compatibility
To enable some of the new ASM features, you can use two new disk group
compatibility attributes, compatible.rdbms and compatible.asm. These
attributes specify the minimum software version that is required to use disk
groups for the database and for ASM, respectively. This feature enables
heterogeneous environments with disk groups from both Oracle Database 10g and
Oracle Database 11g. By default, both attributes are set to 10.1. You must advance
these attributes to take advantage of the new features.

■ New ASM command-line utility (ASMCMD) commands and options

ASMCMD allows ASM disk identification, disk bad block repair, and backup and
restore operations in your ASM environment for faster recovery.

■ ASM fast rebalance

Rebalance operations that occur while a disk group is in RESTRICTED mode
eliminate the lock and unlock extent map messaging between ASM instances in
Oracle RAC environments, thus improving overall rebalance throughput.
This collection of ASM management features simplifies and automates storage
management for Oracle databases.

■ ASM Preferred Mirror Read

When ASM failure groups are defined, ASM can now read from the extent that is
closest to it, rather than always reading the primary copy. A new initialization
parameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administrator
specify a list of failure group names that contain the preferred read disks for each node
in a cluster.

In an extended cluster configuration, reading from a local copy provides a great
performance advantage. Every node can read from its local diskgroup (failure group),
resulting in higher efficiency and performance and reduced network traffic.

■ ASM Rolling Upgrade

Rolling upgrade is the ability of clustered software to function when one or more of
the nodes in the cluster are at different software versions. The various versions of the
software can still communicate with each other and provide a single system image.
The rolling upgrade capability will be available when upgrading from Oracle
Database 11g Release 1 (11.1).

This feature allows independent nodes of an ASM cluster to be migrated or patched
without affecting the availability of the database. Rolling upgrade provides higher
uptime and graceful migration to new releases.

■ ASM Scalability and Performance Enhancements

This feature increases the maximum data file size that Oracle can support to 128 TB.
ASM supports file sizes greater than 128 TB in any redundancy mode. This provides
near unlimited capacity for future growth. The ASM file size limits are:

■ External redundancy - 140 PB
■ Normal redundancy - 42 PB
■ High redundancy - 15 PB

Customers can also increase the allocation unit size for a disk group in powers of 2 up
to 64 MB.

■ Convert Single-Instance ASM to Clustered ASM

This feature provides support within Enterprise Manager to convert a non-clustered
ASM database to a clustered ASM database by implicitly configuring ASM on all
nodes. It also extends the single-instance to Oracle RAC conversion utility to support
standby databases.

Simplifying the conversion makes it easier for customers to migrate their databases
and achieve the benefits of scalability and high availability provided by Oracle RAC.

■ New SYSASM Privilege for ASM Administration

This feature introduces the new SYSASM privilege to allow for separation of database
management and storage management responsibilities.
The SYSASM privilege allows an administrator to manage the disk groups that can be
shared by multiple databases. The SYSASM privilege provides a clear separation of
duties from the SYSDBA privilege.

For complete list of 11g ASM New Feature, You can refer the following

Note 551205.1 11g ASM New Features Technical White Paper
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-asm.html

For Complete list of 11g New Features, You can refer to the following link

Indian localization patch

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 16:36:00 +0000

INDIAN LOCALIZATION PATCH

$FND_TOP/patch/115/sql/afopp002.sql ( FND_CP_GSM_OPP_AQ Queue is Invalidated )

6491231
-------

perl install.pl appspwd=apps japwd=ja

5498551
----------

select count(*) from dba_objects where status='INVALID'

exec sys.utl_recomp.recomp_parallel(8)

select object_name, object_type from all_objects where object_name = 'DBMS_SUPPORT' and object_type like 'PACKAGE%' ;

sqlplus apps/apps @$ORACLE_HOME/rdbms/admin/dbmssupp.sql

sqlplus apps/apps @ja_chk_stat.sql

Note:382829.1 Usage of DFF in India Localization

update MTL_SYSTEM_ITEMS set ATTRIBUTE11 = ATTRIBUTE15;

select owner, table_name from all_tables where table_name = 'JA_IN_INSTALL_CHECK_INFO_D' and owner = 'APPS' ;

perl $JA_TOP/inpatch/indpatch.pl drvr_file=5498551.drv fnd_patchset=H appspwd=apps japwd=ja systempwd=manager

perl compile_il_apps_obj.pl appspwd=apps

sqlplus apps/apps @$JA_TOP/5498551/11_5_3/ja_in_match_lines_v.sql

sqlplus apps/apps @$JA_TOP/5498551/11_5_3/ja_in_po_lines_tax_update_trg.sql

sqlplus apps/apps @$JA_TOP/5498551/11_5_3/ja_in_tds_temp_after_insert_trg.sql

po/po

create index ja_in_rcv_shipment_headers_n1 on rcv_shipment_headers(receipt_num);
create index ja_in_rcv_transactions_n1 on rcv_transactions (rma_reference);

ar/ar

create index ja_in_ra_customer_trx_all_n1 on ra_customer_trx_all(interface_header_attribute1, interface_header_attribute6);

inv/inv

create index ja_in_mtl_transactions_n1 on mtl_material_transactions(rcv_transaction_id);

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct patch/115/import/US/AP_INVOICE_DISTRIBUTIONS_E.ldt

cp backup/resource/CUSTOM.pll $AU_TOP/resource/CUSTOM.pll

f60gen module=CUSTOM userid=apps/apps module_type=LIBRARY

- Go to "Application Developer" Responsibility
- Open View -> Requests
- Click on "Submit New Request"
- Click on "Single Request"
- Enter "Generate Messages" in Name field
- Enter the following parameters
Language : US
Application : Asia/Pacific Localizations
Mode : DB_TO_RUNTIME
- Click Ok and then Submit the request, ensure that request completes successfully

Use "adadmin" utility to compile the menus

6167056
----------

perl $JA_TOP/inpatch/indpatch.pl drvr_file=6167056.drv fnd_patchset=H appspwd=apps japwd=ja systempwd=manager

cp backup/resource/CUSTOM.pll $AU_TOP/resource/CUSTOM.pll

f60gen module=CUSTOM userid=apps/apps module_type=LIBRARY

6079063
--------

perl $JA_TOP/inpatch/indpatch.pl drvr_file=6079063.drv fnd_patchset=H

6410725
------
cp $JA_TOP/6410725/patch/115/import/*.lct $JA_TOP/patch/115/import

perl $JA_TOP/inpatch/indpatch.pl drvr_file=6410725.drv fnd_patchset=H

cp backup/resource/CUSTOM.pll $AU_TOP/resource/CUSTOM.pll

f60gen module=CUSTOM userid=apps/apps module_type=LIBRARY

Attach "India Local Project Billing" responsibility to application user

6410766
--------

perl compile_il_apps_obj.pl appspwd=apps

perl $JA_TOP/inpatch/indpatch.pl drvr_file=6410766.drv fnd_patchset=H

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct patch/115/import/US/RCV_SHIPMENT_HEADERS_2_E.ldt - CUSTOM_MODE=FORCE

i. To add the Custom responsibility:
---------------------------------
a. From the Oracle System Administrator menu, select Security > Responsibility > Define.
b. Enter the following values.
Responsibility Name : XXXXXXXXXX
Application : iSupplier Portal
Responsbility Key : XXXXXXXXXX
Description : XXXXXXXXXX

Available form(Radio button): Select the following
(X) "Oracle Self Service Web Applications"

Data Group:
Name : Standard
Application : iSupplier Portal

Menu : "ILS iSupplier Portal New Application Menu" (Please note that this menu contains personalized IL functions)

c. Apply Securing Attributes to Custom Responsibility

There are three securing attributes that can be used to control access. These attributes
are all seeded with the pre-defined Oracle iSupplier Portal responsibilities that are
released with the product: (Please refer iSupplier implementation guide for details)

? ICX_SUPPLIER_ORG_ID - Identifier for the supplier.
? ICX_SUPPLIER_SITE_ID - Identifier for the supplier site.
? ICX_SUPPLIER_CONTACT_ID - Identifier for the supplier contact.

1. Select Securing Attributes region in Responsibility form, search for the three securing attributes that have to be applied to this responsibility.
2. When you have identified all the attributes to apply to this responsibility,
select ICX_SUPPLIER_CONTACT_ID > click Values button and give ?9999,
select ICX_SUPPLIER_SITE_ID > Click Values button and give -9999.

d. Click Save.

ii. Assign Responsibilities to Users
-----------------------------------------
a. From the Oracle System Administrator menu, select Security > User > Define.
b. Enter the name of the user to whom you wish to grant the responsibility.
c. In the Direct Responsibilities section, enter the custom responsibility in the
Responsibility text box.
d. Click Save.

6410797
--------
perl compile_il_apps_obj.pl appspwd=apps

perl $JA_TOP/inpatch/indpatch.pl drvr_file=6410797.drv fnd_patchset=H

i. To add the Custom responsibility:
---------------------------------
a. From the Oracle System Administrator menu, select Security > Responsibility > Define.
b. Enter the following values.
Responsibility Name : XXXXXXXXXX
Application : Self-Service Web Applications
Responsbility Key : XXXXXXXXXX
Description : XXXXXXXXXX

Available form(Radio button): Select the following
(X) "Oracle Self Service Web Applications"

Data Group:
Name : Standard
Application : Self-Service Web Applications

Menu : "India Local Procurement home" (Please note that this menu contains personalized IL functions)

d. Click Save.

ii. Assign Responsibilities to Users
--------------------------------
a. From the Oracle System Administrator menu, select Security > User > Define.
b. Enter the name of the user to whom you wish to grant the responsibility.
c. In the Direct Responsibilities section, enter the custom responsibility in the
Responsibility text box.
d. Click Save.

iii.Assign User Authorisations
--------------------------
Two new Profiles specific to iProcurement for IL Support are added to System Profile Values to restrict tax modification and entry:
a. ?IL: Access iProcurement Tax information? - Authority to modify IL taxes in iProcurement
b. ?IL: Allow iProcurement RTV CENVAT Process? - Authority to claim CENVAT during iProcurement Return to Vendor transactions.
c. Default values for these profiles are set to ?NO? implying No authorization.System Administrators should set the profile values to each iProcurement users with appropriate values to ensure the proper user authorization levels.

6372361
--------

6053828
6030861

perl $JA_TOP/inpatch/indpatch.pl drvr_file=6372361.drv fnd_patchset=H

Labels: Apps 11i

New background processes in 10g

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 16:28:00 +0000

New background processes in 10g
---------------------------------------

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.

RVWR

Process responsible for writing flashback logs which stores pre-image of data-blocks.

These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

M000

MMON background slave (m000) processes.

RBAL

RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx

These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB

The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

R12.AD.A.DELTA.4 Patch

leesenshan@gmail.com (Senthil Kumaran) — Wed, 06 Aug 2008 16:12:00 +0000

R12.AD.A.DELTA.4

patch-6510214

--Instructions For Applying This Patch

Execute the following command to generate your instance specific installation
instructions

1. Source the Applications environment file
2. Run the Patch Application Assistant by entering "admsi.pl".

--Preparation Tasks

The tasks in this section can be completed without taking any Applications services or users offline.

--Pre-install Tasks

You must shut down all Application tier services before performing the tasks in this section.

AD.A Grants

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

Usage:

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

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

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

4. Use SQL*Plus to run the script:

UNIX:
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql

Windows:
C:\> sqlplus /nolog
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\appsutil\admin\adgrants_nt.sql

--Apply patch

You must apply critical patch 6767273 before applying the AD Mini pack. This patch can not be merged with the AD Mini pack and must be applied separately.

Note: If you have not installed an off-cycle product, you may skip this step. To determine if your environment has an off cycle product, verify the following.

* Check for prod.txt files located under the $APPL_TOP/admin directory.

* For the products found in the directory, make sure there is an entry in the applprod.txt file located under the $APPL_TOP/admin directory

* You must apply patch 6767273 if you have products not listed in the applprod.txt file.

--Apply The Patch

his patch contains the following unified driver file to be applied with
AutoPatch:
u6510214.drv (This is the unified driver)

--Post-install Tasks

You must complete the tasks in this section before starting up Application tier services.

10g Data Pump

leesenshan@gmail.com (Senthil Kumaran) — Tue, 29 Jul 2008 09:11:00 +0000

Data pump:
---------------
In Oracle 10g, exp and imp have been redesigned as the Oracle Data Pump, which is much faster than these old client commands. Data Pump runs as a job inside the database, rather than as a stand-alone client application. This means that jobs are somewhat independent of the process that started the export or import. One machine (say a scheduled job) could start the export, while another machine (such as a DBA's laptop) can check the status of the job. Since the job is inside the database

Basic settings:
--------------------
if you want to export to a file, the first thing that you must do is create a database DIRECTORY object for the output directory, and grant access to users who will be doing exports and imports.

Why Directory Objects?
They are needed to ensure data security and integrity. Otherwise, users would be able to read data that they should not have access to and perform unwarranted operations on the server.

For example will use scott user account and create a directory object it can access,

connect to sqlplus as sys user,

$sqlplus '/as sysdba'

make sure that scott user account has unlocked by connecting to sqlplus as scott user.

Assign create directory privilege to scott,

GRANT CREATE ANY DIRECTORY TO scott;
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/logicalbkp';
Assign read,write privilege to scott on that particular directory where we need to take the backup.
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Tables export and import:
-----------------------------------

expdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schemas export and import:
------------------------------------

expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
impdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database exports and imports:
-------------------------------

expdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:
expdp scott/tiger schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log
The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:
system@db10g> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION
------------------------------ ------------------------------ ------------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ---------- -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT
FULL EXECUTING 1 1

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:
expdp scott/tiger schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

All data pump actions are performed by multiple jobs (server processes not DBMS_JOB jobs). These jobs are controlled by a master control process which uses Advanced Queuing. At runtime an advanced queue table, named after the job name, is created and used by the master control process. The table is dropped on completion of the data pump job. The job and the advanced queue can be named using the JOB_NAME parameter. Cancelling the client process does not stop the associated data pump job. Issuing "ctrl+c" on the client during a job stops the client output and presents a command prompt. Typing "status" at this prompt allows you to monitor the current job:

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: D:TEMPDB10G.DMP
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: SYSMAN
Object Name: MGMT_CONTAINER_CRED_ARRAY
Object Type: DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Completed Objects: 261
Total Objects: 261
The HELP=Y option displays the available parameters:

expdp help=y
Export: Release 10.1.0.2.0 - Production on Tuesday, 23 March, 2004 8:33

Copyright (c) 2003, Oracle. All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
ADD_FILE=dumpfile-name
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

impdp help=y

Import: Release 10.1.0.2.0 - Production on Saturday, 11 September, 2004 17:22

Copyright (c) 2003, Oracle. All rights reserved.

The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply (Y/N) to specific objects.
Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.
ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

Script to start/stop R12 services

leesenshan@gmail.com (Senthil Kumaran) — Sat, 19 Jul 2008 10:55:00 +0000

Script to start R12 services
-------------------------------------
Open a file in vi editor and save it as a script(.sh) format.

#vi start.sh

echo "Starting Database Listener"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addlnctl.sh start VIS'
echo "Database Listener started"
sleep 5
echo "Starting Oracle Database"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addbctl.sh start'
echo "Oracle Database started"
sleep 15
echo "Starting Oracle Applications"
/usr/bin/su - applvis -c '/u01/apps/VIS_lab/admin/scripts/adstrtal.sh apps/apps'
echo "Oracle Applications Started"

:wq!(save & quit)

Script to stop R12 services
-----------------------------------
Open a file in vi editor and save it as a script(.sh) format.

#vi stop.sh

echo "Stopping Oracle Applications"
/usr/bin/su - applvis -c '/u01/apps/VIS_lab/admin/scripts/adstpall.sh apps/apps'
echo "Oracle Applications stopped"
sleep 5
kill -9 `ps -ef | grep applvis | awk '{print $2}'`
echo "Stopping Database Listener"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addlnctl.sh stop VIS'
echo "Database Listener stopped"
echo "Stopping Oracle Database"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addbctl.sh stop immediate'
sleep 5
echo "Oracle Database stopped"

:wq(save & quit)

Use these shell scripts to start/stop r12 services instead of doing manually.

Listener password

leesenshan@gmail.com (Senthil Kumaran) — Wed, 16 Jul 2008 02:42:00 +0000

Set listener password:
----------------------------
LSNRCTL> set save_config_on_stop on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
LISTENER parameter "save_config_on_stop" set to ON
The command completed successfully
LSNRCTL> set password t0p53cr3t
The command completed successfully
LSNRCTL>

The commands will instruct the listener to save the configuration to the file after stopping the listener, as mentioned by the command set save_config_on_stop on. This will place the following lines in the file listener.ora

#----ADDED BY TNSLSNR 05-JUL-2003 00:12:48---
SAVE_CONFIG_ON_STOP_LISTENER = ON
#--------------------------------------------

#----ADDED BY TNSLSNR 05-JUL-2003 00:13:49---
PASSWORDS_LISTENER = 8D5438362F7F2951
#--------------------------------------------

Note how the password has been recorded in the file, but in an encrypted manner.

In this setup, where the encrypted password is mentioned in the parameter file, setting the password is like the case where the password is not specified in the parameter file.

sqlplus command line history

leesenshan@gmail.com (Senthil Kumaran) — Wed, 16 Jul 2008 00:48:00 +0000

Command Line History and Editing in SQL*Plus and RMAN on Linux
------------------------------------------------------------------------

The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input for any other command. This is a really handy addition to SQL*Plus and RMAN on Linux.

Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/
Unzip and install the software using the following commands.
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Run the following commands, or better still append then to the ".bash_profile" of the oracle software owner.

alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'

You can now start SQL*Plus or RMAN using "rlsqlplus" and "rlrman" respectively, and you will have a basic command history and the current line will be editable using the arrow and delete keys.

Script to start/stop release12 services

leesenshan@gmail.com (Senthil Kumaran) — Wed, 16 Jul 2008 00:12:00 +0000

Release12 Start/stop scripts
----------------------------------
Script to start services in Release12
------------------------------------------
login as root user then,
#vi start.sh
echo "Starting Database Listener"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addlnctl.sh start VIS'
echo "Database Listener started"
sleep 5
echo "Starting Oracle Database"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addbctl.sh start'
echo "Oracle Database started"
sleep 5
echo "Starting Oracle Applications"
/usr/bin/su - applvis -c '/u01/apps/VIS_lab/admin/scripts/adstrtal.sh apps/apps'
echo "Oracle Applications Started"

:wq(save and quit)

Script to stop Release12 services
---------------------------------------
log in as root user then
#vi stop.sh
echo "Stopping Oracle Applications"
/usr/bin/su - applvis -c '/u01/apps/VIS_lab/admin/scripts/adstpall.sh apps/apps'
echo "Oracle Applications stopped"
sleep 5
kill -9 `ps -ef | grep applvis | awk '{print $2}'`
echo "Stopping Database Listener"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addlnctl.sh stop VIS'
echo "Database Listener stopped"
echo "Stopping Oracle Database"
/usr/bin/su - oravis -c '/u02/oracle/db/tech_st/10.2.0/appsutil/scripts/VIS_lab/addbctl.sh stop immediate'
sleep 5
echo "Oracle Database stopped"

:wq (save and quit)

Use these scripts to start/stop release services.

Creating custom top

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 14:09:00 +0000

Creating custom top
-----------------------

SCHEMA NAME : XXRLT

TOP NAME : XXRLT_TOP

Application : XXRLT Custom Application

Data Group : XXRLTGroup

Request Group : XXRLT Request Group

Menu : XXRLT_CUSTOM_MENU

Responsibility : XXRLT Custom

1) Make the directory structure for your custom application files.

cd $APPL_TOP

mkdir xxrlt

mkdir xxrlt/12.0.0

mkdir xxrlt/12.0.0/admin

mkdir xxrlt/12.0.0/admin/sql

mkdir xxrlt/12.0.0/admin/odf

mkdir xxrlt/12.0.0/sql

mkdir xxrlt/12.0.0/bin

mkdir xxrlt/12.0.0/reports

mkdir xxrlt/12.0.0/reports/US

mkdir xxrlt/12.0.0/forms

mkdir xxrlt/12.0.0/forms/US

mkdir xxrlt/12.0.0/lib

mkdir xxrlt/12.0.0/out

mkdir xxrlt/12.0.0/log

2) Add the custom module into the environment

cd $APPL_TOP

vi customPROD_app01.env

XXRLT_TOP=/sandb1/oracle/PROD/apps/apps_st/appl/xxrlt/12.0.0

export XXRLT_TOP

source the environment file ( /sandb1/oracle/PROD/apps/apps_st/appl/APPSPROD_pkgprod.env )

Make entry to context file

/sandb1/oracle/PROD/inst/apps/PROD_pkgprod/appl/admin/PROD_pkgprod.xml

/sandb1/oracle/PROD/apps/apps_st/appl/xxrlt/12.0.0

cd $INST_TOP/admin/install

sh adgentopfile.sh

Open new session, source environment file, and stop middle tier services, run autoconfig

Open new session, source environment file, check for custom top in topfile.txt in $APPL_TOP/admin, start the middle tier services.

3) create tablespace XXRLT datafile '/sandb1/oracle/PROD/db/apps_st/data/xxrlt01.dbf' size 500M

4) create user xxrlt identified by xxrlt

default tablespace xxrlt

temporary tablespace temp1

quota unlimited on xxrlt

grant connect, resource to xxrlt;

5) Register your Oracle Schema.

Login to Applications with System Administrator responsibility

Navigate to Application-->Register

Application = XXRLT Custom Application

Short Name = XXRLT

Basepath = XXRLT_TOP

Description = XXRLT Custom Application

6) Register Oracle User

Naviate to Security-->Oracle-->Register

Database User Name = XXRLT

Password = XXRLT

Privilege = Enabled

Install Group = 0

Description = XXRLT Custom Application User

7) Add Application to a Data Group

Navigate to Security-->Oracle-->DataGroup

Data Group = XXRLTGroup

Description = XXRLT Custom Data Group

Click on "Copy Applications from" and pick Standard data Group, then add the following entry.

Application = XXRLT Custom

Oracle ID = APPS

Description = XXRLT Custom Application

8) Create custom request group

This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)

Navigate to Security-->responsbility-->Request

Group = XXRLT Request Group

Application = XXRLT Custom

Code = XXRLT

Description = XXRLT Custom Requests

We will not define any requests to add to the group at this stage, but you can add some now if required.

9) Create custom menu

This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage). We will create two menus, one for Core Applications and one for Self Service.

Navigate to Application-->Menu

Menu = XXRLT_CUSTOM_MENU

User Menu Name = XXRLT Custom Application

Menu Type =

Description = XXRLT Custom Application Menu

Seq = 100

Prompt = View Requests

Submenu =

Function = View All Concurrent Requests

Description = View Requests

Seq = 110

Prompt = Run Requests

Submenu =

Function = Requests: Submit

Description = Submit Requests

Menu = XXRLT_CUSTOM_MENU_SSWA

User Menu Name = XXRLT Custom Application SSWA

Menu Type =

Description = XXRLT Custom Application Menu for SSWA

10) Create new responsibility. One for Core Applications and One for Self Service (SSWA)

Navigate to Security-->Responsibility-->Define

Responsibility Name = XXRLT Custom

Application = XXRLT Custom

Responsibility Key = XXRLTCUSTOM

Description = XXRLT Custom Responsibility

Available From = Oracle Applications

Data Group Name = XXRLTGroup

Data Group Application = XXRLT Custom

Menu = XXRLT Custom Application

Request Group Name = XXRLT Request Group

Responsibility Name = XXRLT Custom SSWA

Application = XXRLT Custom

Responsibility Key = XXRLTCUSTOMSSWA

Description = XXRLT Custom Responsibility SSWA

Available From = Oracle Self Service Web Applications

Data Group Name = XXRLTGroup

Data Group Application = XXRLT Custom

Menu = XXRLT Custom Application SSWA

Request Group Name = XXRLT Request Group

11) Add responsibility to user

Navigate to Security-->User-->Define

Add XXRLT Custom responsibility to users as required.

12) Other considerations

You are now ready to create your database Objects, custom Reports, Forms, Packages, etc

Create the source code files in the XXRLT_TOP directory appropriate for the type of object. For example forms would be located in $XXRLT_TOP/forms/US or package source code in $XXRLT_TOP/admin/sql for example.

Database Objects, such as tables, indexes and sequences should be created in the XXRLT schema, and then you need to

a) Grant all privilege from each custom data object to the APPS schema.

For example : logged in as XXRLT user

grant all privileges on myTable to apps;

b) Create a synonym in APPS for each custom data object

For example : logged in as APPS user

create synonym myTable for XXRLT.myTable;

13) Login to sysadmin, Application Developer Responsibility

Application > Form ( Register the form )

Application > Funtion ( Add the form to a function )

Application > Menu ( Attach the function to a menu )

Menu that is added to a particular responsbility is given to specific user

Security > User > Define

Installation of maintenance wizard

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 11:51:00 +0000

INSTALLING MAINTAINENCE WIZARD(Metalink Doc.id:329476.1)
=========================================

System Requirements
---------------------
Maintenance Wizard must be installed into an RDBMS 10g with a 10g iAS ORACLE_HOME on a UNIX or Linux machine. (Your e-Business Suite Applications may be on other operating systems, however).

create group
---------------
groupadd dba

create user
----------------
useradd -g dba oracle

MAINTENCE WIZARD
================

Prerequistite Instructions
--------------------------
Setting up OpenSSH
On the Maintenance Wizard Node:
Log in as the user who will own Maintenance Wizard.
Generate the ssh key by issuing the following command:
ssh-keygen -t rsa
Press the Enter key three times.
CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.
Use FTP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.

On the Remote Node(s):
----------------------
Login as the user who owns the product being upgraded or maintained. For Applications tiers, use the applmgr account. For the database tiers, use the oracle account.
Remove specialized .login, .profile or similar files which cause extra verbiage to be displayed to the screen.
Create a directory called .ssh in the home directory.
If an authorized_keys file does not exist, create a new, empty one.
Copy the content of /tmp/id_rsa.pub (from the Maintenance Wizard Node) into a new file named authorized_keys. (If you already have a file called authorized_keys in this directory, open it in vi, go to the bottom of the file, add one blank line and then copy and paste the contents of the file /tmp/id_rsa.pub into the existing file and save it.)
Verify that the .ssh directory permission is set to 700.
Verify that the authorized_keys file permission is set to 600.
Verify that the $HOME directory does not have write permissions for group and others.
chmod go-w $HOME
Repeat the above steps for each Remote Node

Test the installation:
----------------------
Login to the Maintenance Wizard Node as Maintenance Wizard owner.
Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:
ssh -l [user] [SERVER]
When prompted to confirm the identity, respond "Yes".
Disconnect from the server.
Execute the following command:
ssh -l [user] [SERVER] echo 'foo'
Only the word foo should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:
The content of the authorized_keys file contains the content of id_rsa.pub of the client
The permissions of the authorized_keys file are set to 600.
The permissions of the .ssh directory are set to 700.
The permissions of the $HOME directory do not include execute for group and other.
If more than the word foo appears, then repeat the step above to remove all .login, .profile or similar files until ONLY the word foo appears.

INSTALL 10g RDBMS 10.2.0
========================
hosts file
----------
vi /etc/hosts
192.9.200.225 ias.oneapps.com ias

Set Kernel Parameters
---------------------
/etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

/sbin/sysctl -p

/etc/security/limits.conf
soft nproc 2047
hard nproc 16384
soft nofile 2048
hard nofile 65536

set environment file in bash profile

run ./runinstaller

CREATE DATABASE USING: $ORACLE_HOME/bin run DBCA
-------------------------------------------------
Choose "Create a Database".
Choose "General Purpose".
Specify the Global Database Name.
Specify whether to configure to database with Enterprise Manager.
Set the default password for the SYS and SYSTEM accounts. (PLEASE, record these in a secure location!)
Choose your storage mechanism. We recommend that you choose "File System" and select a Common Location.
Specify any desired recovery options.
Specify NO sample schemas.
In the Character Sets tab, do NOT modify the Default Language (American) and Default Date Format (US).
Specify the desired values for parameters SGA, PGA and processes.
When the process has completed, exit the Database Creation Assistant.

INSTALL 10G ias 10.1.3
----------------------
rpms

glibc-2.3.4-2.9
glibc-common-2.3.4-2.9
binutils-2.15.92.0.2-13
compat-libstdc++-296-2.96-132.7.2
gcc-3.4.3-22.1
gcc-c++-3.4.3-22.1
libstdc++-3.4.3-22.1
libstdc++-devel-3.4.3-22.1
openmotif21-2.1.30-11.RHEL4.4
pdksh-5.2.14-30
setarch-1.6-1
make-3.80-5
gnome-libs-1.4.1.2.90-44.1
sysstat-5.0.5-1
compat-db-4.1.25-9
compat-libstdc++-devel-7.3-2.96.128
compat-glibc-7.x-2.2.4.32.6
compat-libstdc++-7.3-2.96.128
control-center-2.8.0-12
xscreensaver-4.18-5.rhel4.2

./runinstaller

To copy the file:
-----------------
cp /network/admin/tnsnames.ora /network/admin/

OR

create a symbolic link:

cd /network/admin
ln -s /network/admin/tnsnames.ora tnsnames.ora

Create the NEWEOF tablespace where the Maintenance Wizard schema objects will be stored
sqlplus
system user

create tablespace NEWEOF datafile '/u01/oracle/oradata/eof01.dbf' size 50M AUTOEXTEND ON NEXT 5M MAXSIZE 75M;

Verify that Zip and UnZip executables are installed and that ping is in the $PATH using the following commands:
which zip
which unzip
which ping

Installation Steps
==================
SU - ORACLE

SET BASH PROFILE RDBMS 10g

ORACLE_HOME=; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
TWO_TASK=;export TWO_TASK
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

create the Maintenance Wizard directory in the RDBMS $ORACLE_HOME directory

cd $ORACLE_HOME
mkdir eof
EOF_HOME=$ORACLE_HOME/eof; export EOF_HOME

DOWN LOAD THE Maintenance Wizard PATCH =6972475

unzip it into $EOF_HOME

cd ${EOF_HOME}/config

Run the installation script
./setup.sh

Stop and restart iAS.
opmnctl stopall
opmnctl startall

Post Install Steps
==================

Secure the $EOF_HOME/EOF.env file.
chmod 700 $EOF_HOME/EOF.env

URL http://.:/EOF/

SYSADMIN/YOURPASS:

Create A Production (Full or Partial) Duplicate On The Same Host

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 11:40:00 +0000

Create A Production (Full or Partial) Duplicate On The Same Host
-------------------------------------------------------------------------
(Metalink Doc.id:388424.1)

Primary DB : ORCL
Clone DB : AUX

Production Database should be archive enabled.

Startup mount;
alter database archivelog;
alter database open;
archive log list;

Recovery catalog for RMAN

Creating the Recovery Catalog Owner
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:

% sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE tools
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON tools;

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;

Creating the Recovery Catalog

% rman catalog rman/rman@ORCL

RMAN> CREATE CATALOG;

Registering the target database

% rman TARGET / CATALOG rman/rman@ORCL

RMAN> REGISTER DATABASE;

Reference : Note:452529.1
Reference : RMAN: How to Query the RMAN Recovery Catalog ( Note:98342.1 )

Example Source Listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /newpart//product/10.2.0/)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /newpart//product/10.2.0)
(SID_NAME = ORCL)
)
(SID_DESC =
(GLOBAL_DBNAME = AUX)
(ORACLE_HOME = /newpart//product/10.2.0)
(SID_NAME = AUX)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

Example Source tnsnames.ora

AUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = AUX)(UR=A)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Note : Make use of netca and netmgr to configure listener and tnsnames

#Find Production Database Files:

SQL> select name from v$datafile;
Create the Auxiliary Database directories needed
cd $ORACLE_HOME/dbs
create parameter file initAUX.ora

db_file_name_convert = ('/old/path1', '/new/path1',
'/old/path2', '/new/path2',
'/old/path3', '/new/path3')

log_file_name_convert = ('/old/path1', '/new/path1',
'/old/path2', '/new/path2',
'/old/path3', '/new/path3')

eg:-

db_name = aux
db_block_size = 8192
compatible = 10.2.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/newpart/oradata/aux/control01.ctl',
'/newpart/oradata/aux/control02.ctl')
db_file_name_convert = ('/newpart/oradata/orcl',
'/newpart/oradata/aux')
log_file_name_convert = ('/newpart/oradata/orcl',
'/newpart/oradata/aux')
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

# create a passwordfile for remote connections as sysdba

% orapwd password= file=orapwAUX

% sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora
SQL> exit
Start the Duplication
ORACLE_SID=AUX; export ORACLE_SID # ksh
sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora
SQL> exit

# Set your SID back to the TARGET for duplication.

> rman trace.log

Recovery Manager: Release 10.2.0.1.0 - Production
Copyright (c) Oracle. All rights reserved.

RMAN> connect target
connected to target database: V10GREL4 (DBID=2510891965)

RMAN>backup database;

RMAN>sql 'alter system switch logfile';

RMAN> connect auxiliary sys/pwd@AUX
connected to auxiliary database: AUX (not mounted)

RMAN> duplicate target database to AUX device type disk;
Once this is done, login to duplicate database with alter database open resetlogs.

Creating a Duplicate Database on a New Host Using RMAN

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 11:20:00 +0000

Creating a Duplicate Database on a New Host Using RMAN(Metalink Doc.id:388431.1)
--------------------------------------------------------------------------------------
Reference:
---------------
Primary Database SID : ORCL
Duplicate Database SID : AUX
RMAN Catalog SID : RMAN

# export ORACLE_SID=ORCL

# rman target=/ catalog=rman/rman@ORCL

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
This command will perform a full database backup including archivelogs and the current controlfile.

Host B (Aux)

Making the backup available for the duplicate process.

If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created.
RMAN> list backup;

Create same directory of host b and give appropriate permissions for the oracle user

Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database.
------------------------------------------------------------------------------------------
db_name = aux
db_block_size = 8192
compatible = 10.2.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/d02/oradata/aux/control01.ctl')
db_file_name_convert = ('/newpart/oradata/orcl',
'/d02/oradata/aux')
log_file_name_convert = ('/newpart/oradata/orcl',
'/d02/oradata/aux')
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
------------------------------------------------------------------------------
Following the creation of the initAUX.ora startup nomount the auxiliary instance.
export ORACLE_SID=AUX

sqlplus '/as sysdba'

startup nomount;

Ensuring SQL*NET connections to primary database and RMAN catalog are working.

Host B(AUX)

sqlplus ‘sys/oracle@PROD as sysdba’
sqlplus rman/rman@PROD (not mandatory)

Add tnsnames.ora entry – Eg:
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

Prepare RMAN duplicate script.
------------------------------------

run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}

Save it as dup.sql

Execute the RMAN script

Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
# export ORACLE_SID=AUX

# rman target sys/pwd@ORCL catalog rman/rman@ORCL auxiliary /

RMAN> @dup.sql
After this, login to aux database and alter database open with resetlogs option.

Database cloning

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 11:09:00 +0000

1. Ensure that your database is recoverable back to original location and state by backing it up before executing this procedure:

Database in NON-ARCHIVE LOG MODE - take an off-line database backup.
Database in ARCHIVELOG MODE - take an on-line or off-line database backup.

2. Connect to sql*plus as the user ‘SYS AS SYSDBA’ and ensure that all users have logged off the system by executing the following command.

SQL> connect / as sysdba
Connected.
SQL> select username from v$session;
USERNAME

-----
-----
-----
-----
SYS
8 rows selected.
SQL>

You should see several fields returning with no username (these are internal oracle logins and the will not affect your operations) and one SYS login (this is you).

3. Place the system in restricted mode:

SQL> alter system enable restricted session;

System altered.

4. Connect as SYS with SYSDBA privileges and execute the following SQL command:

SQL> connect / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
SQL>

The [alter database backup controlfile to trace;] command will generate a SQL script that can be used to regenerate the database.

5. Execute the sql*plus command show parameter to find your user dump directory.

SQL> show parameter user_dump_dest
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
user_dump_dest string /opt/app/oracle/product/admin/orcl/udump

6. Exit to the OS layer and move to the user_dump_dest directory. Trace file names are generated by oracle in the following format [instance_name]+[oracle generated number].trc, so use the timestamp to locate the last trace file generated. Open the file in a text editor and remove everything above the ‘STARTUP NOMOUNT ..' statement. Alter the ‘CREATE CONTROLFILE’ statement for the following uses:

Rename DB in Archivelog mode:
CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS
ARCHIVELOG

Rename DB in Non-Archivelog mode:
CREATE CONTROLFILE SET DATABASE "new_db_name" NORESETLOGS
NOARCHIVELOG

Reuse DB name in Archivelog mode:
CREATE CONTROLFILE REUSE DATABASE "db_name" RESETLOGS ARCHIVELOG

Reuse DB name in Non-Archivelog mode:
CREATE CONTROLFILE REUSE DATABASE "db_name" NORESETLOGS
NOARCHIVELOG

7. Edit the rest of this file to reference the correct locations of you log files, datafiles and temp files.

8. Move the trace file to an appropriate location and name to recreate_db.sql.

9. Connect as SYS with SYSDBA privileges and shutdown the database in immediate mode:
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

10. Move the following files to there corresponding locations in updated recreate_db.sql file.
DATAFILES
Log files

11. Restore or copy the backup and the archived logs (if any) to the new machine.

12. Move your INIT$ORACLE_SID.ORA file to its new location. Ensure your controlfile and archive destinations in INIT are set properly to the new locations.

13. Connect to SQLPLUS on your new machine as SYS AS SYSDBA and run create_db.sql script as follows:

SQL> @[directory_location]/create_db.sql

14. Change the global database name if you renamed your database as follows:

SQL> ALTER DATABASE RENAME GLOBAL_NAME = new_db_name;

15. Perform full system backup and store in a safe location.

10g AS installation with linux

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 10:43:00 +0000

Part I: Installing Linux
----------------------------
This Minimum Hardware Configuration For Linux Server:
• 800MHz Pentium III CPU
• 1GB of RAM (Minimum)
• Hard Disk – 40 GB ((IDE, SCSI, or FireWire) with at least 10GB of free space.
• One 100Base-T Ethernet adapter
A fresh install of OS (Linux ) is Desired (as opposed to an upgrade), that the server will be
dedicated to Oracle, and that no other operating systems or data are on the server.
RHEL4 Installation
Oracle Database 10g Release 2 is certified to run the base release of RHEL4 (Advanced
Server and Enterprise Server) without updates.
Steps:
---------
1. Boot the server using the first CD.
o You may need to change your BIOS settings to allow booting from the CD.
2. The boot screen appears with the boot: prompt at the bottom of the screen.
o Select Enter to continue with a graphical install on the console.
o The installer scans your hardware, briefly displays the Red Hat splash screen,
and then begins a series of screen prompts.
3. Language Selection
o Accept the default.
4. Keyboard Configuration
o Accept the default.
5. Welcome Screen
o Click on Next.
6. Disk Partitioning Setup
Note: Improperly partitioning a disk is one of the surest and fastest ways to wipe out
everything on your hard disk. If you are unsure how to proceed, stop and get help,
or you will risk losing data!

Minimum Partition Requirements
-------------------------------------
The 9GB disk on the first controller (/dev/sda) will hold all Linux and
Oracle software and contains the following partitions:
100MB /boot partition
1,500MB swap partition—Set this to at least twice the amount of RAM in
the system but to no more than 2GB. (As 32-bit systems do not support
swap files larger than 2GB.)
8,192MB for /usr
1024 MB for /tmp
256MB for /boot
1024 MB for /home
512MB for /opt
512MB for /var
OR
15,630 MB(15GB) for root partition—This partition will be used for
everything, including /usr, /tmp, /var, /opt, /home, and more. Boot
Loader Configuration

7. Network Configuration
o It is usually best to configure database servers with a static IP address. To do
so, click on Edit .
o A pop-up window appears. Uncheck the Configure using DHCP box, and
enter the IP Address and Netmask for the server. Be sure that Activate on
boot is checked, and click on OK .
o In the Hostname box, select manually and enter the hostname.
o In the Miscellaneous Settings box, enter the remaining network settings.

8. Firewall Configuration
o For the purposes of this installation, no firewall is configured. Select No
firewall
o Select Disabled on the "Enable SELinux" drop down list.
o Click on Proceed when the "Warning - No Firewall" window appears.

9. Additional Language Support
o Accept the default.

10. Time Zone Selection
o Choose the time settings that are appropriate for your area. Setting the system
clock to UTC is usually a good practice for servers. To do so, click on System
clock uses UTC.

11. Set Root Password
o Enter a password for root, and enter it again to confirm.
Note: If you need more than 2GB of swap space, create multiple swap
partitions.

12. Package Installation Defaults
o Select Customize software packages to be installed.
o Select Install Everything
o Click on Next to proceed.

13. Installing Packages
o Software will be copied to the hard disk and installed. Change disks as
prompted.

14. Congratulations
o Remove the installation media from the system, and click on Reboot.

15. The system automatically reboots and presents a new welcome screen.
o Click on Next.

16. License Agreement
o Read the license agreement. If you agree to the terms, select Yes, I agree to
the License Agreement and click on Next.

17. Date and Time
o Set the Date and Time.
o If you want to use an NTP server (recommended), select Enable Network
Time Protocol and enter the name of the NTP server.

18. Display
o Accept the defaults or change as required.

19. Red Hat Login
o Enter your Red Hat Network login and password or create a new one.

20. System User
o Create an account for yourself.
o Do not create an account for oracle at this time.

21. Additional CDs
o Click on Next.

22. Finish Setup
o Click on Next.

23. A graphical login screen appears.

24. Congratulations! Your RHEL4 software is now installed.
Verifying the Installation
Required kernel version: 2.6.9-5.0.5.EL This kernel, or any of the kernels supplied in
updates, works with Oracle Database 10g Release 2 .

Check your kernel version by running the following command:
#uname -r
2.6.9-11.ELsmp
Once you've completed the steps above, all of the packages required for Oracle Database
10g Release 2 will have been installed. Verify this using the example below.

Required package versions (or later):
-----------------------------------------
• binutils-2.15.92.0.2-10.EL4
• compat-db-4.1.25-9
• control-center-2.8.0-12
• gcc-3.4.3-9.EL4
• gcc-c++-3.4.3-9.EL4
• glibc-2.3.4-2
• glibc-common-2.3.4-2
• gnome-libs-1.4.1.2.90-44.1
• libstdc++-3.4.3-9.EL4
• libstdc++-devel-3.4.3-9.EL4
• make-3.80-5
• pdksh-5.2.14-30
• sysstat-5.0.5-1
• xscreensaver-4.18-5.rhel4.2
• libaio-0.3.96
• openmotif21-2.1.30-11.RHEL4.2

To see which versions of these packages are installed on your system, run the following
command:
# rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \ gnomelibs
libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
Check for any uninstalled RPMs
Some RPMs need to be upgraded
For RPM installation
# rpm –ivh
For RPM Update
# rpm –Uvh

If any of the package versions on your system are missing or the versions are earlier than
those specified above, you can download and install the updates from the Linux Portal
(rpm.pbone.net/).
# rpm –Uvh # for Updating Installed RPM to the required version.

Part II: Configuring Linux for Oracle
-------------------------------------------
Now Linux software is installed, we need to configure it for Oracle. The steps required to
configure Linux for Oracle Database 10g Release 2.
Verifying System Requirements
To verify that your system meets the minimum requirements for an Oracle Database 10g
Release 2 database, log in as root and run the commands below.

To check the amount of RAM and swap space available, run this:
# grep MemTotal /proc/meminfo
MemTotal: 1034680 kB
# grep SwapTotal /proc/meminfo
SwapTotal: 1534196 kB
The minimum RAM required is 1024MB, and the minimum required swap space is 1GB.
Note: Swap space should be twice the amount of RAM for systems with 2GB of RAM or
less and between one and two times the amount of RAM for systems with more than 2GB.

The Minimum Space Requirement for Oracle DB 10g R2
• 2.5GB for the Oracle Database 10g Release2 Software
• 1.2GB for the database.
• The /tmp directory needs at least 400MB of free space.

To check the available disk space on your system, run the following command:
# df -h
[oracle@oneman ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hdc7 4.9G 485M 4.1G 11% /
/dev/hdc1 291M 16M 260M 6% /boot
/dev/hdc2 79G 6.3G 69G 9% /d01
/dev/hdc9 26G 8.4G 16G 35% /d02
none 497M 0 497M 0% /dev/shm
/dev/hdc3 15G 218M 14G 2% /home
/dev/hdc6 9.7G 56M 9.1G 1% /tmp
/dev/hdc5 12G 5.8G 5.3G 53% /usr

Create the Oracle Groups and User Account
Oracle AS 10gR2 Comprising of two tiers
1. Infra Tier ( Infrastructure )
• Database
• HTTP Server
• OC4J
• OID

2. Middle Tier
• Application Server
• Discoverer
• Portal
• Forms
• Reports
• OC4J
• HTTP Server

So We need to Create 2 User Accounts ( For Infra & Middle Tier)
Create the Linux groups and user account that will be used to install and maintain the Oracle
Database 10g Release 2 software. The user accounts will be called orainfra, and oramid
the groups will be oinstall and dba. Execute the following commands as root:
# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba orainfra
# id orainfra
uid=500(orainfra)gid=502(oinstall)groups=502(oinstall),503(dba)

Set the password on the orainfra account:
# passwd orainfra
Changing password for user orainfra.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
# useradd -g oinstall -G dba oramid
# id oramid
uid=501(oramid) gid=502(oinstall) groups=502(oinstall),503(dba)
# passwd oramid
Changing password for user oramid.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

Creation of Directories
-----------------------------
Now create two directories to store the Oracle Database 10g Release 2 Infrastructure and
the Middle Tier.
Note: The following directories are being created in the root filesystem. This is done for the
sake of simplicity and is not recommended as a general practice. These directories would
normally be created as separate file systems.

Issue the following commands as root:
--For Infra Tier
#mkdir -p /u01/OraHome_1
#chown -R orainfra:oinstall /u01/OraHome_1
#chmod -R 777 //u01/OraHome_1
--For Middle Tier
#mkdir -p /u01/OraHome_2
#chown -R oramid:oinstall /u01/OraHome_1
#chmod -R 777 //u01/OraHome_1

Configuring the Linux Kernel Parameters
Linux allows modification of most kernel parameters while the system is up and running.
There's no need to reboot the system after making kernel parameter changes. Oracle
Database 10g Release 2 requires the kernel parameter settings shown below. The values
given are minimums, so if your system uses a larger value, don't change it.
Run the Following Commands as Root
# cat >> /etc/sysctl.conf <> kernel.sysrq = 0
> kernel.shmmax = 4294967295
> kernel.shmmni = 4096
> kernel.sem = 256 32000 100 142
> kernel.msgmnb = 65535
> EOF

Note: If you're following along and have just installed Linux, the kernel parameters will
all be at their default values and you can just cut and paste the following commands
while logged in as root.
To Refresh the Keranel Issue the following Command.
# /etc/sysctl -p

Part III: Installing Oracle Infrastructure
-------------------------------------------------
Installing Oracle Infrastructure
Log in using the orainfra account.
# su - orainfra
Mount the CD to the desired point, if not Automatically mounted
Start the Oracle Universal Installer.
$ /Media/Cdrecorder/runInstaller

1. Specify Inventory Directory and Credentials
o Inventory Directory: /u01/oraInventory
o Operating System group name: oinstall
o Click on Next
Click Here
2. Select Installation Path

o Installation Directory: /u01/OraHome1
Change the Path to
/u01/OraHome_1
Select Oracle Application Server infra Structure

o Installation Type: Identity Management and Metadata Repository (3.63GB)

Follow the Instructions Carefully from the GUI (like running
/u01/OraHome_1/root.sh after logging on as root )

o Incase any Error then resolve it by doing necessary action (For Eg. Any
Kernel Parameter is not configured properly will result in error).
Note: If any error occurs then resolve it first and click Back rather than
Retry.

o Incase any Error then resolve it by doing necessary action (For Eg. Any
Kernel Parameter is not configured properly will result in error).
Note: If any error occurs then resolve it first and click Back rather than retry.

o Confirm that all the kernel parameters memory and swap space are
configured properly before continuing.

Select the Required components from the list
o OID
o Oracle Application SSO
o Oracle Application Server Delegated Administration
o Oracle Applications Server Directory Integration

Note: High Availability and Replication is selected based on the requirement.

Port Configuration

o Go for Automatic ones

Namespace in Internet Directory

o Go for the Suggested namespace (ie, the Default)

Database Configuration Options

o Specify the Database name and SID
o Specify the Oradata Directory for File location

Change the DB name
Specify the Location
Specify Database Schema Passwords

o Select Use the same password for all accounts or can give separate
passwords for each schema depends on requirement.
o Enter Password and Click NEXT
Confirm the Passwords
Specify Instance name and ias_admin Password

o Instance Name:
o Specify ias_admin Password
o Then click NEXT

o Click Install to start the installation of the Listed Products
o On successful Installation close the Window by clicking exit.

Part IV: Installing Oracle Application Server
--------------------------------------------------
Installing Oracle Infrastructure
--------------------------------------
Log in using the oramid account.
# su - oramid
Mount the CD to the desired point, if not Automatically mounted
Start the Oracle Universal Installer.
$ /Media/Cdrecorder/runInstaller

2. Select Installation Path

o Installation Directory: /u01/OraHome_2

Select Oracle Application Server infra Structure

Installation Type: Business Intelligence and Forms (1.90GB)

o Select Business Intelligence and Forms and then click NEXT
Pre-RequisiteChecks

o Incase any Error then resolve it by doing necessary action (For Eg. Any
Kernel Parameter is not configured properly will result in error).

Note: If any error occurs then resolve it first and click Back rather than
Retry.

o Confirm that all the kernel parameters memory and swap space are
configured properly before continuing. Then click NEXT

Select the Required components from the list
o Oracle Application Server 10g Portal
o Oracle Application Server 10g Wireless
o Oracle Application Server Discoverer
o Oracle Applications Server Personalization
o Oracle Applications Server Forms Services
o Oracle Applications Server Report Services

Then Click Next

o Specify the Port Configuration Options as Automatic

Registering with Oracle Internet Directory

o Specify Hostname
o Specify Port (389 for Normal and 686 for SSL)
Note:
1. Hostname can be obtained by the following command
# hostname
Csdtest.oneapps.com
2. Port no can be obtained by the following command
# vi /OraHome_1/install/portlist.ini
Specify the Hostname & Port No Then
Click Next
Specify OID Login

o Go for the Default name
o Specify password for the same
o Then Click NEXT
Then Finalize the setting same as for Infra Tier Except the instance name (Specify
different Instance name for Middle Tier)

Configuring Bash_profiles
--------------------------------
FOR INFRA TIER
o Switch to orainfra user
# su – orainfra
o Edit the Following Parameter in the bash_profile

# vi .bash_profile

ORACLE_HOME=
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin
export PATH
ORACLE_SID=
export ORACLE_SID=
unset USERNAME
Then Run the same by the Following command
# . .bash_profile

FOR MIDDLE TIER
o Switch to oramid user
# su – oramid
o Edit the Following Parameter in the bash_profile

# vi .bash_profile

ORACLE_HOME=
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin
export PATH
unset USERNAME

Then Run the same by the Following command
# . .bash_profile
POST INSTALLATION ACTIVITIES
INFRA Tier
Starting Services
Start the Listener
Start the Database
Start all the services of Oracle Process Management Notifications
(opmnctl)
Start the services of Enterprise Manager Control (emctl).

# su - orainfra
# lsnrctl status / start
# sqlplus “ / as sysdba”
( if connected to an Idle Instance then issue STARTUP
command)
# opmnctl startall / status
# emctl start / status iasconsole
# emctl start / status dbconsole

Note:
1.If some components of opmnctl is not coming up (down) then issue the
following command to bring it alive.
# opmnctl startproc ias-component =
2. Start the service in the Given Sequence.

MIDDLE Tier
--------------
Starting Services
Start all the services of Oracle Process Management Notifications
(pmnctl)
Start the services of Enterprise Manager Control (emctl).
# opmnctl startall / status
# emctl start / status iasconsole
# emctl start / status dbconsole

Note:
1.If some components of opmnctl is not coming up (down) then issue the
following command to bring it alive.
# opmnctl startproc ias-component =
2. Shutdown the services in the Reverse Order
i.e, First stop all the services of Middle Tier
# emctl stop iasconsole
# opmnctl stopall
Then,
Do the same for Infra Tier and Shutdown the Database and Stop the
Listener.

Installing Oracle Database 9i (9.2.0.8) on RHEL 4

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 06:32:00 +0000

Prerequisites to Install Oracle Database 9i (9.2.0.8) on RHEL 4
-------------------------------------------------------------------------

Things to check before installation:
------------------------------------------
Prerequisite Red Hat Packages:
---------------------------------------
Install below Red Hat RPM packages:

make-3.80-6.EL4
compat-db-4.1.25-9
compat-gcc-32-3.2.3-47.3
compat-gcc-32-c++-3.2.3-47.3
compat-oracle-rhel4-1.0-5
compat-libcwait-2.0-2
compat-libgcc-296-2.96-132.7.2
compat-libstdc++-296-2.96-132.7.2
compat-libstdc++-33-3.2.3-47.3
gcc-3.4.6-3
gcc-c++-3.4.6-3
gnome-libs-1.4.1.2.90-44.1
gnome-libs-devel-1.4.1.2.90-44.1
libaio-devel-0.3.105-2
libaio-0.3.105-2
openmotif21-2.1.30-11.RHEL4.6
xorg-x11-deprecated-libs-devel-6.8.2-1.EL.13.37.5
xorg-x11-deprecated-libs-6.8.2-1.EL.13.37.5

Oracle Supplied Compat Packages:
----------------------------------------
compat-oracle-rhel4-1.0-5.i386.rpm
compat-libcwait-2.0-2.i386.rpm

Oracle Software:
--------------------
Download below files
ship_9204_linux_disk1.cpio.gz
ship_9204_linux_disk2.cpio.gz
ship_9204_linux_disk3.cpio.gz

Patches:
--------------
p4198954_21_LINUX.zip
p4547809_92080_LINUX.zip
p5632264_92080_LINUX.zip

Check Installed Required RPMs
-------------------------------------
rpm -q make compat-db compat-gcc-32 compat-gcc-32-c++ compat-oracle-rhel4 compat-libcwait compat-libgcc-296 compat-libstdc++-296 compat-libstdc++-33 gcc gcc-c++ gnome-libs gnome-libs-devel libaio-devel libaio openmotif21 xorg-x11-deprecated-libs-devel xorg-x11-deprecated-libs

If any of packages are not installed, Download and intsall that packages using below command
rpm -ivh (or)Uvh packagename --nodeps
-ivh – to install packages
-Uvh – to upgrade packages
-- nodeps to uncheck dependencies.

Install oracle supplied packages:
-------------------------------------------

[root@csd u01]# cd 4198954/
[root@csd 4198954]# rpm -Uvh compat-oracle-rhel4-1.0-5.i386.rpm compat-libcwait-2.0-2.i386.rpm
Preparing... ########################################### [100%]
cat: /etc/ld.so.preload: No such file or directory
1:compat-libcwait ########################################### [ 50%]
2:compat-oracle-rhel4 ########################################### [100%]
[root@deskpro 4198954]#

Create USER and GROUPS:
--------------------------------

[root@csd ~]# groupadd dba
[root@csd ~]# groupadd oinstall
[root@csd ~]# useradd -c "Oracle software owner" -g oinstall -G dba oracle
[root@csd ~]# passwd oracle
Changing password for user oracle.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@csd 4198954]#

Create Oracle Software Directory:
-----------------------------------------

[root@csd ~]# mkdir -p /u01/app/oracle/product/9.2.0
[root@csd ~]# chown -R oracle.oinstall /u01
[root@csd ~]# chmod -R 775 /u01

Set Kernel Parameters, File Handlers, and Limits:
-------------------------------------------------------
Adding these parameters to /etc/sysctl.conf will take effect only after reboot. To make this effective immediately, issue the following command

net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
kernel.shmmax = 1073741824
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536

Shell Limits:
--------------------
Login as root, and add below lines to /etc/security/limits.conf
oracle soft nofile 63536
oracle hard nofile 63536
oracle soft nproc 16384
oracle hard nproc 16384
Add soft and hard limit to ~oracle/.bash_profile
ulimit -n 63536
ulimit -u 16384

Create Swap Space:
-------------------------
root@csd ~]# grep MemTotal /proc/meminfo
MemTotal: 385956 kB
[root@csd ~]# cat /proc/swaps
Filename Type Size Used Priority
/dev/hda2 partition 1048468 160 -1

Here is the command to create temporary swap space.
[root@csd ~]# dd if=/dev/zero of=tmpswap bs=1k count=900000
900000+0 records in
900000+0 records out
[root@csd ~]# chmod 600 tmpswap
[root@csd ~]# mkswap tmpswap
Setting up swapspace version 1, size = 921595 kB
[root@csd ~]# swapon tmpswap

Setup Oracle Environment Variables:
-------------------------------------------
Add below lines to ~oracle/.bash_profile
export LD_ASSUME_KERNEL=2.4.19
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=db9i1
export ORACLE_TERM=xterm
export NLS_LANG=AMERICAN

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
Unpack Oracle Software Archives:

[oracle@csd ~]$ gunzip ship_9204_linux_disk1.cpio.gz ship_9204_linux_disk2.cpio.gz ship_9204_linux_disk3.cpio.gz

[oracle@csd ~]$ cpio -idmv < ship_9204_linux_disk1.cpio
[oracle@csd ~]$ cpio -idmv < ship_9204_linux_disk2.cpio
[oracle@csd ~]$ cpio -idmv < ship_9204_linux_disk3.cpio
[oracle@csd ~]$ chown -R oracle.dba Disk1/ Disk2/ Disk3/

Verify X-Windows:
-----------------------
Execute the below command
[oracle@csd ~]$ xclock
If you don’t see a graphical clock on the console then, Issue the below command in terminal
[oracle@csd ~]$ echo "DISPLAY=localhost:0.0;export DISPLAY"
[oracle@csd ~]$ source .bash_profile
[oracle@csd ~]$ xclock

Starting Oracle Universal Installer:
--------------------------------------------
Execute the below command to start the installation
[oracle@csd ~]$ cd Disk1/
[oracle@csd Disk1]$ ./runInstaller

Opening forms from linux machine

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 04:11:00 +0000

Opening forms forms from linux machine
----------------------------------------------

I found out no plugins were installed in Firefox yet, so I had to download the sun java plugin.
I decided to download version 1.5.0.12. After installing the downloaded file, the plugin has to be noticed by Firefox. You can do this by creating a link in the FIREFOX_HOME/plugins directory.

Create a link to the libjavaplugin_oji.so from the installed jre...
ln -s /usr/java/jre1.5.0_12/plugin/i386/ns7/libjavaplugin_oji.so ./libjavaplugin_oij.so

After the link is created, the java plugin is recognized in Firefox.
Next step is to let EBS now, you can also use the sun java plgin instead of the default jinitiator. This can be done by adjusting the $OA_HTML/bin/appsweb_sid.cfg and change the following lines.

sun_plugin_mimetype=application/x-java-applet;jpi-version=1.5.0_12
sun_plugin_version=1.5.0_12

After bouncing the webserver you can use the sun java plugin, but also the default jinitiator.

Replacing Jinitiator with JRE for Oracle Apps 11i

leesenshan@gmail.com (Senthil Kumaran) — Tue, 15 Jul 2008 03:53:00 +0000

Replacing Jinitiator with JRE for Oracle Apps 11i
----------------------------------------------------

Download Interoperability patch 6863618 (Metalink)

Download the Java Runtime Environment (JRE) 6 plug-in at, http://java.sun.com/javase/downloads/index.jsp

Downloaded JRE Native plug-in file from jre-6_uX-windows-i586-p.exe and rename to j2se160x.exe
(Where X = the version number. Using jre-6u5 as an example this would be j2se16005.exe)

Move the j2se1600X.exe file to the web application tier and place it in [COMMON_TOP]/util/jinitiator

Apply Developer 6i Patchset 18 ( 4948577 )

unzip patch to $ORACLE_HOME
cd $ORACLE_HOME/developer6i_patch18
./patch_install.sh 2>&1 | tee patch_install_p18.log

cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install
cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install

cp developer6i_patch18/bin/genshlib $ORACLE_HOME/bin

Additional Patches
------------------
5713544

sh patch.sh
adrelink.sh force=y "fnd f60webmx"

4261542

cd $ORACLE_HOME
unzip p4261542_600_GENERIC.zip

cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542

cd $ORACLE_HOME/4261542/oracle/forms/engine
cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
cd $ORACLE_HOME/4261542/oracle/forms/handler
cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class

Generate Product JAR Files ( adadmin )

5216496
sh patch.sh

5753922
sh patch.sh
adrelink.sh force=y "fnd f60webmx"

6195758
cd $ORACLE_HOME
unzip p6195758_600_GENERIC.zip
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758
cd $ORACLE_HOME/6195758/oracle/forms/handler
cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class
Generate Product JAR Files ( adadmin )

5938515
sh patch.sh
adrelink.sh force=y "fnd f60webmx"

3830807
sh patch.sh

4586086
cd $ORACLE_HOME
unzip .zip
cd $ORACLE_HOME/forms60/lib
mv env_forms60.mk env_forms60.mk.PRE_BUG4586086
cp $ORACLE_HOME/bug4586086/env_forms60.mk $ORACLE_HOME/forms60/lib
cd $ORACLE_HOME/forms60/lib
make -f cus_forms60w.mk libso_install
adrelink.sh force=y "fnd f60webmx"

relink - adadmin
select 'Maintain Applications Files Menu' and then select 'Relink Applications Program'
Enter list of products to link ('all' for all products)[all] : fnd
Generate specific executables for each selected product [No] ? y
Relink with debug information [No] ? n
Enter executables to relink, or enter 'all' [all] : f60webmx ar60run ar60runb ar60rund *

4888294 (adpatch )

5884875

cd $ORACLE_HOME
unzip p5884875_600_GENERIC.zip
cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG5884875
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG5884875
cd $ORACLE_HOME/5884875/oracle/forms/engine
cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
cd $ORACLE_HOME/5884875/oracle/forms/handler
cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/engine/AlertDialog.class
Run adadmin "Generate JAR Files"

(6863618)
---------

Apply the Patch Driver using adpatch
Run the txkSetPlugin.sh script from the /6863618/fnd/bin/

txkSetPlugin.sh 16005

Start Oracle Applicatons 11.5.10.2 and start using Java instead of Jinititor for Applets

Disable concurrent manager

leesenshan@gmail.com (Senthil Kumaran) — Tue, 08 Jul 2008 11:09:00 +0000

Disable concurrent manager:
====================
1. Log in as application user

2. Stop all application services

3. Go to $INST_TOP/appl/admin/

Then Change the parameter "s_concstatus" from enabled to disabled

run autoconfig. And start application services.

Query to kill inactive sessions

leesenshan@gmail.com (Senthil Kumaran) — Fri, 27 Jun 2008 04:15:00 +0000

Query to kill inactive sessions:
-----------------------------------
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='INACTIVE';

Save this query output as .sql format. Then connect to sql prompt and execute this .sql file. It'll kill
all inactive sessions.

Installing 11g(11.1.0) database on Redhat Linux 4

leesenshan@gmail.com (Senthil Kumaran) — Thu, 26 Jun 2008 11:32:00 +0000

Operating System:
==============
Red Hat Enterprise Linux 4

Database Version:
==============
Oracle 11g Release 1

You can download the oracle 11g r1 software in the below link

Download link:
===========
http://www.oracle.com/technology/software/products/database/index.html

Pre-Installation Tasks:
=================
Creating OS User account and group:
------------------------------------------
$groupadd dba -creating OS group
$useradd -g dba oracle -creating OS user and assigning to group
$passwd oracle -- set password for that OS user.

Checking physical RAM size:
--------------------------------
$ grep MemTotal /proc/meminfo
Make it sure that physical memory size should be 1GB. And it shouldn't less than 900M.

Checking SWAP size:
-------------------------
$ grep SwapTotal /proc/meminfo
Note:swap should be double of memory

Checking space in “/tmp”
-----------------------------
$ df -k /tmp

Checking available free space on the system:
-------------------------------------------------
$ df –k

Checking the Software Requirements:
===========================
Checking linux version:
---------------------------
$ uname –a

Checking required packages:
---------------------------------
binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

OS command to check RPMS are installed or not:
------------------------------------------------------
Execute as root user
# rpm –q libaio-devel-0.3.105-2
# rpm –q unixODBC-devel-2.2.11
Like this check all rpms.

Like this check for all the above RPMS.

If any rpms are missing download it from
http://rpm.pbone.net/

Installing/Upgrading RPMS:
----------------------------------
Login as the ROOT user
# rpm –ivh unixODBC-devel-2.2.11.3-1.i386.rpm --to install
Or
# rpm –Uvh unixODBC-devel-2.2.11.3-1.i386.rpm --to upgrade

Creating Directories for installation:
-----------------------------------------
Base Directory and Oracle Home
Do it as oracle user,
#mkdir /u01 -- Base directory
# mkdir /u01/app/oracle/product/11.0.1 -- ORACLE_HOME
# mkdir –p /u02/oradata -- DATA_TOP

If you create these directories as a oracle user, the owner of that directory
is oracle only. If you do it as root user you need to change the ownership and
permissons to oracle user for that directory.

# chown –R oracle:dba /u01/app/oracle/product/11.0.1
# chown –R oracle:dba /u02/oradata

Changing permissions:
----------------------------
# chmod –R 755 /u01/app/oracle/product/11.0.1
# chmod –R 755 /u02/oradata

Configuring Kernel Parameters:
------------------------------------
Check the current parameters and values in /etc/sysctl.conf

fs.file-max = 76800
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

if any of these entries are missing just add it. Before that take a ackup of that file
#cp /etc/sysctl.conf /etc/sysctl.confbkp

Changing current values of the kernel parameters:
-------------------------------------------------------
# /sbin/sysctl -p

Ckeck the following lines in the /etc/security/limits.conf file.
-----------------------------------------------------------------
Before editig take a backup of that file.

# cp –p /etc/security/limits.conf /etc/security/limits.confbkp

And add this lines in /etc/security/limits.conf if its not existing,

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add or edit the following line in the /etc/pam.d/login file,
--------------------------------------------------------------
if this lines are not exist:
-----------------------------
Before editing that file take a backup.
# cp –p /etc/pam.d/login /etc/pam.d/loginbkp

session required /lib/security/pam_limits.so
session required pam_limits.so

Check Oracle User’s Environment:
---------------------------------------
$ vi .bash_profile

Remove the oracle environments. If you set already.
(if u created a new user no need to check this .bash_profile)

Set DISPLAY variable, if its not set
----------------------------------------
$ echo $DISPLAY - to check display has set or not
$ DISPLAY=:0.0 - if its returning this we need to set Display using
the below command,
$ set DISPLAY=(ipaddress of your server):0.0
$ export DISPLAY

Then type, as root user and check xlcock also
# xhost +
# xclock (should display the clock window)

Starting installation:
-------------------------
Login as oracle user
Su – oracle
Go the directory where Oracle software is copied
or if its cd or dvd, mount the device and run runinstaller

$ cd /u01/software
Unzip the dump,
$ unzip linux_11gR1_database.zip

Go to Disk1, Then run runinstaller

./.runInstaller

For sample screen shots just check this link
http://www.oracle.com/technology/obe/11gr1_db/install/dbinst/dbinst.htm