Farooq Malik's DBA Blog

Duplicating Oracle 11g Database using Active database option

2011-03-28T21:30:00.001-04:00

Source DB
===========
Host Name=oradbsrvr1
DB Name=racdb
Oracle Version=11.2.0.1.0
Oracle Home=/u01/oracle/product/11.2.0/db
dbfiles_loc=/u01/oradata/racdb

Target DB
===========
Host Name= oradbsrvr2
DB Name=testdb
Oracle Version=11.2.0.1.0
Oracle Home=/u01/oracle/product/11.2.0/db1
dbfiles_loc=/u02/oradata/testdb

Step1: Creating Init.ora File on target system
=================================================
*.audit_file_dest='/u01/oracle/oraclebase/admin/testdb/adump'
*.compatible='11.2.0'
*.control_files='/u02/oradata/testdb/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='TESTDB'#Modified by RMAN duplicate
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u02/oradata'
*.db_unique_name='TESTDB'#Modified by RMAN duplicate
*.diagnostic_dest='/u01/oracle/oraclebase'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.java_pool_size=134217728
*.job_queue_processes=10
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oradbsrvr2)(PORT=1521))'
*.log_buffer=11485660
*.open_cursors=300
*.pga_aggregate_target=298844160
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=300
*.sga_target=897581056
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/oradata/racdb/','/u02/oradata/testdb/'
*.log_file_name_convert='/u01/oradata/racdb/','/u02/oradata/testdb/'

Step2: Creating password files
==================================
orapwd file=orapwtestdb password=oracle ignorecase=y entries=5

Step3: Configuring Listener
==============================
First I tried to connect to database without adding any entries

oracle@oradbsrvr2:/appl/oracle>sqlplus sys/oracle@testdb as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 28 16:51:46 2011

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

After this error I checked the listener status

oracle@oradbsrvr2:/appl/oracle>lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAR-2011 16:52:15

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                21-MAR-2011 19:16:26
Uptime                    6 days 21 hr. 35 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/oracle/oraclebase/diag/tnslsnr/oradbsrvr2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "testdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

testdb instance status was blocked so I added sid_list_listener values

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.2.0/grid)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = testdb)
      (SID_NAME = testdb)
      (ORACLE_HOME = /u01/oracle/product/11.2.0/grid)
    )
)

Then restarted the listener

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-MAR-2011 16:56:37
Uptime                    0 days 0 hr. 0 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/oracle/oraclebase/diag/tnslsnr/oradbsrvr2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Step4: Duplicating Database
===============================

oracle@oradbsrvr2:/home/oracle>rman target sys/oracle@racdb auxiliary sys/oracle@testdb

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 28 18:53:59 2011

connected to target database: RACDB (DBID=3431901857)
connected to auxiliary database: TESTDB (not mounted)

RMAN> run
2> {
duplicate target database to testdb from active database;
3> 4> }

Starting Duplicate Db at 28-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set db_name =
''RACDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set db_unique_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format '/u02/oradata/testdb/controlfile/control01.ctl';
   alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''RACDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started

Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                343934448 bytes
Database Buffers             532676608 bytes
Redo Buffers                  14733312 bytes

Starting backup at 28-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=93 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/28/2011 18:54:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 18:54:32
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

RMAN> exit

This error was occurred because the TNS entries of our auxiliary database were missing on the source database. This is required as we are duplicating the active database and it need connection to auxiliary database. After adding TNS entries, the rman process was started again

oracle@oradbsrvr2:/home/oracle>rman target sys/oracle@racdb auxiliary sys/oracle@testdb

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 28 19:13:57 2011

connected to target database: RACDB (DBID=3431901857)
connected to auxiliary database: TESTDB (not mounted)

RMAN> run
2> {
3> duplicate target database to testdb from active database;
4> }

Starting Duplicate Db at 28-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

Starting backup at 28-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=93 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/28/2011 19:14:17
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:14:17
ORA-17628: Oracle error 19505 returned by remote Oracle server

RMAN> exit

This error was occurred because the controlfile location was specified wrongly. After correcting the control file path, the script executed once again

oracle@oradbsrvr2:/home/oracle>rman target sys/oracle@racdb auxiliary sys/oracle@testdb

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 28 19:17:12 2011

connected to target database: RACDB (DBID=3431901857)
connected to auxiliary database: TESTDB (not mounted)

RMAN> run
2> {
3> duplicate target database to testdb from active database;
4> }

Starting Duplicate Db at 28-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area 893562880 bytes

Fixed Size                     2218512 bytes
Variable Size                343934448 bytes
Database Buffers             532676608 bytes
Redo Buffers                  14733312 bytes

Starting backup at 28-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=107 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/oracle/product/11.2.0/db/dbs/snapcf_racdb.f tag=TAG20110328T191731 RECID=4 STAMP=746997451
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-MAR-11

database mounted

contents of Memory Script:
{
   set newname for datafile 1 to
"/u02/oradata/racdb/system01.dbf";
   set newname for datafile 2 to
"/u02/oradata/racdb/sysaux01.dbf";
   set newname for datafile 3 to
"/u02/oradata/racdb/undotbs01.dbf";
   set newname for datafile 4 to
"/u02/oradata/racdb/users01.dbf";
   set newname for datafile 5 to
"/u02/oradata/racdb/example01.dbf";
   set newname for datafile 6 to
"/u02/oradata/racdb/user_data01.dbf";
   backup as copy reuse
   datafile 1 auxiliary format
"/u02/oradata/racdb/system01.dbf"   datafile
2 auxiliary format
"/u02/oradata/racdb/sysaux01.dbf"   datafile
3 auxiliary format
"/u02/oradata/racdb/undotbs01.dbf"   datafile
4 auxiliary format
"/u02/oradata/racdb/users01.dbf"   datafile
5 auxiliary format
"/u02/oradata/racdb/example01.dbf"   datafile
6 auxiliary format
"/u02/oradata/racdb/user_data01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

Starting backup at 28-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/racdb/system01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:17:42
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oradata/racdb/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:17:43
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oradata/racdb/example01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:17:45
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oradata/racdb/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:17:46
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/oradata/racdb/user_data01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:17:47
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/racdb/users01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/28/2011 19:17:48
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2011 19:17:48
ORA-17628: Oracle error 19505 returned by remote Oracle server

RMAN> exit

This time the duplicate process was failed because the two parameters were not specified in init.ora parameter file:

*.db_file_name_convert='/u01/oradata/racdb/','/u02/oradata/testdb/'
*.log_file_name_convert='/u01/oradata/racdb/','/u02/oradata/testdb/'

After putting these two parameters, the script was started once again

oracle@oradbsrvr2:/home/oracle>rman target sys/oracle@racdb auxiliary sys/oracle@testdb

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 28 19:24:43 2011

connected to target database: RACDB (DBID=3431901857)
connected to auxiliary database: TESTDB (not mounted)

RMAN> run
2> {
3> duplicate target database to testdb from active database;
4> }

Starting Duplicate Db at 28-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

sql statement: alter system set db_name = ''RACDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                343934448 bytes
Database Buffers             532676608 bytes
Redo Buffers                  14733312 bytes
Starting backup at 28-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/oracle/product/11.2.0/db/dbs/snapcf_racdb.f tag=TAG20110328T192504 RECID=5 STAMP=746997905
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-MAR-11

database mounted

contents of Memory Script:
{
   set newname for datafile 1 to
"/u02/oradata/testdb/system01.dbf";
   set newname for datafile 2 to
"/u02/oradata/testdb/sysaux01.dbf";
   set newname for datafile 3 to
"/u02/oradata/testdb/undotbs01.dbf";
   set newname for datafile 4 to
"/u02/oradata/testdb/users01.dbf";
   set newname for datafile 5 to
"/u02/oradata/testdb/example01.dbf";
   set newname for datafile 6 to
"/u02/oradata/testdb/user_data01.dbf";
   backup as copy reuse
   datafile 1 auxiliary format
"/u02/oradata/testdb/system01.dbf"   datafile
2 auxiliary format
"/u02/oradata/testdb/sysaux01.dbf"   datafile
3 auxiliary format
"/u02/oradata/testdb/undotbs01.dbf"   datafile
4 auxiliary format
"/u02/oradata/testdb/users01.dbf"   datafile
5 auxiliary format
"/u02/oradata/testdb/example01.dbf"   datafile
6 auxiliary format
"/u02/oradata/testdb/user_data01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

Starting backup at 28-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/racdb/system01.dbf
output file name=/u02/oradata/testdb/system01.dbf tag=TAG20110328T192512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oradata/racdb/sysaux01.dbf
output file name=/u02/oradata/testdb/sysaux01.dbf tag=TAG20110328T192512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oradata/racdb/example01.dbf
output file name=/u02/oradata/testdb/example01.dbf tag=TAG20110328T192512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oradata/racdb/undotbs01.dbf
output file name=/u02/oradata/testdb/undotbs01.dbf tag=TAG20110328T192512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/oradata/racdb/user_data01.dbf
output file name=/u02/oradata/testdb/user_data01.dbf tag=TAG20110328T192512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/racdb/users01.dbf
output file name=/u02/oradata/testdb/users01.dbf tag=TAG20110328T192512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-MAR-11

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like "/u01/flash_recovery/RACDB/archivelog/2011_03_28/o1_mf_1_143_6s26j8mh_.arc" auxiliary format
"/u02/oradata/TESTDB/archivelog/2011_03_28/o1_mf_1_143_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 28-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=143 RECID=272 STAMP=746998218
output file name=/u02/oradata/TESTDB/archivelog/2011_03_28/o1_mf_1_143_1am8ciee_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
Finished backup at 28-MAR-11

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u02/oradata/TESTDB/archivelog/2011_03_28/o1_mf_1_143_1am8ciee_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/TESTDB/archivelog/2011_03_28/o1_mf_1_143_1am8ciee_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=746998230 file name=/u02/oradata/testdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=746998230 file name=/u02/oradata/testdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=746998231 file name=/u02/oradata/testdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=746998231 file name=/u02/oradata/testdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=746998231 file name=/u02/oradata/testdb/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=746998231 file name=/u02/oradata/testdb/user_data01.dbf

contents of Memory Script:
{
   set until scn 4176301;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-MAR-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 143 is already on disk as file /u02/oradata/TESTDB/archivelog/2011_03_28/o1_mf_1_143_1am8ciee_.arc
archived log file name=/u02/oradata/TESTDB/archivelog/2011_03_28/o1_mf_1_143_1am8ciee_.arc thread=1 sequence=143
media recovery complete, elapsed time: 00:00:01
Finished recover at 28-MAR-11

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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 893562880 bytes

Fixed Size                     2218512 bytes
Variable Size                343934448 bytes
Database Buffers             532676608 bytes
Redo Buffers                  14733312 bytes

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

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 893562880 bytes

Fixed Size                     2218512 bytes
Variable Size                343934448 bytes
Database Buffers             532676608 bytes
Redo Buffers                  14733312 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP 1 ( '/u02/oradata/testdb/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u02/oradata/testdb/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u02/oradata/testdb/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u02/oradata/testdb/system01.dbf'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   set newname for tempfile 1 to
"/u02/oradata/testdb/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy "/u02/oradata/testdb/sysaux01.dbf",
"/u02/oradata/testdb/undotbs01.dbf",
"/u02/oradata/testdb/users01.dbf",
"/u02/oradata/testdb/example01.dbf",
"/u02/oradata/testdb/user_data01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/testdb/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/oradata/testdb/sysaux01.dbf RECID=1 STAMP=746998255
cataloged datafile copy
datafile copy file name=/u02/oradata/testdb/undotbs01.dbf RECID=2 STAMP=746998255
cataloged datafile copy
datafile copy file name=/u02/oradata/testdb/users01.dbf RECID=3 STAMP=746998255
cataloged datafile copy
datafile copy file name=/u02/oradata/testdb/example01.dbf RECID=4 STAMP=746998255
cataloged datafile copy
datafile copy file name=/u02/oradata/testdb/user_data01.dbf RECID=5 STAMP=746998255

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=746998255 file name=/u02/oradata/testdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=746998255 file name=/u02/oradata/testdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=746998255 file name=/u02/oradata/testdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=746998255 file name=/u02/oradata/testdb/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=746998255 file name=/u02/oradata/testdb/user_data01.dbf

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

database opened
Finished Duplicate Db at 28-MAR-11

RMAN>exit

This time we were succeeded in duplicating the database with active database option.

Hope this help

NFS Configuration for Oracle RAC

2011-02-20T17:23:00.001-05:00

Today I was going to Install/configure Oracle 11gR2 RAC without using shared storage on Red Hat Enterprise Linux. I had read about that before but never tested so I tried and failed first time but after some re-configuration, I was able to configure clusterware and was able to store OCR Voting and database files on NFS mount points.

Step 1
=========
On First node e.g. RAC node1, create the following directory. You can use any directory as your NFS source mount point.

mkdir /u01/shared_config
chown -R oracle:oinstall /u01/shared_config

Step 2
=========
Add the following lines to the /etc/exports file.

vi /etc/exports
------------------------------------------------
/u01/shared_config *(rw,sync,no_wdelay,insecure_locks,no_root_squash)

Step 3
========
Run the following command to export the NFS shares.
Check that the NFS server will get started during boot of this server.

chkconfig --level 345 nfs on

Now, start the NFS server process on the NFS server.
service nfs start

Check, if the directory is exported correctly by issuing the exportfs –v command:
/usr/sbin/exportfs -v

Step 4
========
On both node1 and node2 create the directories in which the Oracle configuration files will be created.

mkdir /shared_config
chown -R oracle:oinstall /shared_config
chmod -R 775 /shared_config

Step 5
=======
Add the following lines to the "/etc/fstab" file.
---------------------------------------------------------------
racnode1:/u01/shared_config /shared_config nfs rw,bg,hard,nointr,tcp,vers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0 0 0

Note: I used following Oracle recommended settings for nfs share and at the end my ocr and voting disks configuration process was failed:
racnode1:/u01/shared_config /shared_config nfs rw,bg,hard,intr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600 0 0

Step 6
=======
Mount the NFS shares on all servers.

mount /shared_config

Make sure the permissions on the shared directories are correct i.e oracle.
Check, if the NFS export is correctly mounted with the mount command.
# mount
It will give you output like this:
esidbapoc01:/sharedconfig on /shared_config type nfs (rw,bg,hard,nointr,tcp,nfsvers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0,addr=192.168.1.3)

Hope this will help…………

Remote Telnet on Unix Servers

2011-01-27T16:55:00.001-05:00

I have created simple script which will telnet to any unix server and will perform the tasks required.

#Remote_Telnet.sh
------------------------------------
#!/bin/ksh
. ~/.profile

HOST='192.168.1.100'
DELAY=3
USER='oracle'
PASSWORD='oracle'
COMMAND1='cd /home/oracle'

(
sleep 3
print "${USER}"
sleep 3
print "${PASSWORD}"
sleep 3
print "Starting Patch Deployment on ${HOST}"
sleep 1
print "cd /app/dbsrvr"
print ". ./dbenv.sh"
print "cd bin"
print "./stop_server.sh"
print "cd /app/dbserver/patches"
print "opatch apply"
print "cd /app/dbsrvr/bin"
print "./start_server.sh"
sleep 1
print "exit"
sleep 3
) | telnet "${HOST}"$

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

Currently I have hard-coded IP and user details but you can change this to enter the values at run time and change the script to accept those values and connect to that specific server.
for example:
#Remote_Telnet.sh `192.168.1.100` oracle password
------------------------------------
#!/bin/ksh
. ~/.profile

HOST='$1'
DELAY=3
USER='$2'
PASSWORD='$3'
------------------------------------

Oracle Applications (EBS) Useful Commands

2010-03-29T09:15:00.001-04:00

Hi All, I am compiling some useful commands for Oracle Application 11i and R12. I will try to update the list as I found new commands. Please share the scripts to me so that I may also include those to have online shared library of these useful commands reference.

How to find oracle apps release version

select APPLICATIONS_SYSTEM_NAME,RELEASE_NAME from apps.fnd_product_groups;

How to find Workflow Version embedded in Apps 11i ?

select TEXT from apps.WF_RESOURCES where NAME='WF_VERSION';

How to find version of JDK Installed on Apps ?

There might be multiple JDK installed on Operating System.
Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using Context File.
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version

Finding Host Details

select     substr(node_name, 1, 20) node_name,
    server_address, substr(host, 1, 15) host,
    substr(domain, 1, 20) domain,
    substr(support_cp, 1, 3) cp,
    substr(support_web, 1, 3)
    web,
    substr(SUPPORT_DB, 1, 3) db,
    substr(VIRTUAL_IP, 1, 30) virtual_ip
from apps.fnd_nodes;

Checking Profile options: (should be enabled)

select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from apps.fnd_profile_options a, apps.fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';

Checking listener status

lsnrctl status APPS_ABPRD
where ABPRD is my db sid name

Checking Concurrent Processes

select CONCURRENT_QUEUE_NAME,max_processes,running_processes from
apps.FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

How to check if a patch is applied or not ?

--Patch information is stored in AD_BUGS & AD_APPLIED_PATCHES table.
select bug_number from apps.AD_BUGS where bug_number ='&patch_no';

How to confirm form versions

First of all find the location of form and then enter the following command
strings -a $AP_TOP/forms/US/APXINWKB.fmx |grep '$Header'

More commands will be added soon…………………………………….

Here is the list of few important tables.

Concurrent Manager

FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND

FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

More commands will be added soon…………………………………….

Convert Oracle RAC database to Single Instance

2010-03-16T02:06:00.003-04:00

You will find number of articles on My Oracle Support (metalink) and Google for converting single instance database to RAC but its hard to find the documents to convert RAC to single instance.
I will present two methods to convert the RAC to single instance.

i. Converting RAC to single instance on same machine.
ii. Copying the RAC database to another machine and converting that to Single Instance

Converting RAC to single instance on same machine

This is based on Oracle 10G Release 2 and assumes:
1. Oracle RAC running with cluster file system
2. You have basic knowledge about Oracle RAC

Test Server:
OS : Red Hat Enterprise Linux Server release 5.4
Database Version : 10.2.0.4
File system: OCFS2

1. Stop database and CRS on both node
$ srvctl stop database -d RACDB # crsctl stop crs

2. Turn Off RAC

SQL> startup
ORA-29702 error occurred in Cluster Group Service operation

Relink with the RAC OFF.
$ cd $ORACLE_HOME/rdbms/lib $ /usr/ccs/bin/make -f ins_rdbms.mk rac_off
Relinking oracle
$ make -f ins_rdbms.mk ioracle
## OR , both working fine
$ cd $ORACLE_HOME/bin $ relink oracle

If ASM Instance Exist, run below command as root
# /u01/oracle/product/10.2.0/db/bin/localconfig delete # /u01/oracle/product/10.2.0/db/bin/localconfig add

3. Parameter(Pfile/spfile) & database changes
SQL> startup SQL> alter database disable thread 2; SQL> alter system set remote_listener='';

3a. Remove unwanted logfile
SQL> select thread#, group# from v$log; SQL> alter database drop logfile group 3; SQL> alter database drop logfile group 4;

3b. Remove unwanted tablespace
SQL> drop tablespace UNDOTBS2 including contents and datafiles;

3c. Rename instance name.
SQL> alter system set instance_name=<new_name> scope=spfile; SQL> shutdown immediate SQL> startup - Change your ORACLE_SID environment

4. Run $ORA_CRS_HOME/install/rootdelete.sh on both node
- This will stop and remove all CRS startup related file

5. Remove $ORA_CRS_HOME binary using Clusterware OUI installer
- Ignore any error if 2nd node already down
- rm -rf $ORA_CRS_HOME

6. Modify listener file
$ vi $ORACLE_HOME/network/admin/listener.ora

6a. Modify tnsname file
$ vi $ORACLE_HOME/network/admin/tnsnames.ora

That’s it. You have successfully converted your RAC database to Single Instance on same machine.
Note: You can convert your single instance DB to RAC again by following metalink note:747457.1

Copying the RAC database to another machine and converting that to Single Instance

You often need to copy your production database to test environment to reproduce any defect or to have same replica of your production environment. This way you will required both RAC and single instance setup available in parallel.
I assume that you have following things available on your system.

OS : Red Hat Enterprise Linux Server release 5.4
Database Version : 10.2.0.4
File system: OCFS2 (I will update the document for ASM based RAC database migration in few days)

This will be updated shortly InshaAllah……………….

How to recover dropped table in Oracle 10g using flashback feature

2010-03-15T06:05:00.002-04:00

How to recover dropped table in Oracle 10g using flashback feature……………

Enter user-name: fkhalid@malik
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

STEP 1 : WE ARE GOING TO DROP TABLE test2.
================================
SQL> desc test2;
Name Null? Type
------------ -------- ----------------------------
ID NUMBER(3)

SQL> select * from test2;

ID
----------
1
2
3
4
5
6

6 rows selected.

STEP 2 : NOW DROP THE TABLE.
=====================

SQL> drop table test2;

Table dropped.

SQL> show user
USER is "FKHALID"

SQL> select * from test2;
select * from test2
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now We are confirmed that table has been dropped.

SQL> show user
USER is "FKHALID"

STEP 3 : CHECK WHEATHER THE TABLE IS AVAILABLE IN RECYCLEBIN.
===========================================

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$gGu93nOJ0VrgQ6wUECjRWg==$0 TEST2

STEP 4 : USE THIS FLASHBACK COMMAND TO RECOVER THE DROPPED TABLE.
===============================================

SQL> flashback table test2 to before drop;

Flashback complete.

SQL> select * from test2;

ID
----------
1
2
3
4
5
6

6 rows selected.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit

NOTE: IF THE DROPPED TABLE IS NOT IN RECYCLEBIN THEN YOU CANT USE FLASHBACK FEATURE TO RECOVER THE TABLE.
You need to specify the retention period for flashback logs to retain the tables for longer duration.

Otherwise you'll need a Backup or a dump to restore your table like duplicating your database elsewhere and get back the Table by DataPump (expdp/impdp).

GC Buffer Busy Waits in RAC: Finding Hot Blocks:

2010-03-15T05:49:00.002-04:00

I was reading following article from a this blog by Jeremy Schneider"
link to the original blog (http://www.ardentperf.com/2007/09/12/gc-buffer-busy-waits-in-rac-finding-hot-blocks/)

Using the ASH

I’ll show two different ways here to arrive at the same conclusion. First, we’ll look a the ASH to see what the sampled sessions today were waiting on. Second, we’ll look at the segment statistics captured by the AWR.

First of all we will get the snap_id’s from dba_hist_snapshot table.

col begin_interval_time format a30
col end_interval_time format a30

select snap_id,begin_interval_time, end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='15-MAR-2010'
order by snap_id;

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
      4486 15-MAR-10 12.00.57.293 AM      15-MAR-10 01.00.06.447 AM
      4487 15-MAR-10 01.00.06.447 AM      15-MAR-10 02.00.17.979 AM
      4488 15-MAR-10 02.00.17.979 AM      15-MAR-10 03.00.20.419 AM
      4489 15-MAR-10 03.00.20.419 AM      15-MAR-10 04.00.22.574 AM
      4490 15-MAR-10 04.00.22.574 AM      15-MAR-10 05.00.24.538 AM
      4491 15-MAR-10 05.00.24.538 AM      15-MAR-10 06.00.29.219 AM
      4492 15-MAR-10 06.00.29.219 AM      15-MAR-10 07.00.40.423 AM
      4493 15-MAR-10 07.00.40.423 AM      15-MAR-10 08.00.58.573 AM
      4494 15-MAR-10 08.00.58.573 AM      15-MAR-10 09.00.29.703 AM
      4495 15-MAR-10 09.00.29.703 AM      15-MAR-10 10.00.38.566 AM
      4496 15-MAR-10 10.00.38.566 AM      15-MAR-10 11.00.45.651 AM
      4497 15-MAR-10 11.00.45.651 AM      15-MAR-10 12.00.52.478 PM
      4498 15-MAR-10 12.00.52.478 PM      15-MAR-10 01.00.56.997 PM

This is the window I’m going to use; 12:00am to 12:00pm today.
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 4486 and 4498
group by wait_class_id, wait_class
order by 3;

WAIT_CLASS_ID WAIT_CLASS                            CNT
------------- ------------------------------ ----------
   3290255840 Configuration                         169
   2000153315 Network                               934
   4108307767 System I/O                           7199
   3386400367 Commit                               7809
   4217450380 Application                         12248
   3875070507 Concurrency                         14754
   1893977003 Other                               35499
                                                  97762
   3871361733 Cluster                            104810
   1740759767 User I/O                           121999
You can see that there were a very large number of cluster events recorded in the ASH. Let’s look a little closer.
select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 4486 and 4498 and wait_class_id=3871361733
group by event_id, event
order by 3;

  EVENT_ID EVENT                                           CNT
---------- ---------------------------------------- ----------
3905407295 gc current request                                4
3785617759 gc current block congested                       10
2705335821 gc cr block congested                            15
 512320954 gc cr request                                    16
3794703642 gc cr grant congested                            17
3897775868 gc current multi block request                   17
1742950045 gc current retry                                 18
1445598276 gc cr disk read                                 148
1457266432 gc current split                                229
2685450749 gc current grant 2-way                          290
 957917679 gc current block lost                           579
 737661873 gc cr block 2-way                               699
2277737081 gc current grant busy                           991
3570184881 gc current block 3-way                         1190
3151901526 gc cr block lost                               1951
 111015833 gc current block 2-way                         2078
3046984244 gc cr block 3-way                              2107
 661121159 gc cr multi block request                      4092
3201690383 gc cr grant 2-way                              4129
1520064534 gc cr block busy                               4576
2701629120 gc current block busy                         14379
1478861578 gc buffer busy                                67275
Notice the huge gap between the number of buffer busy waits and everything else. Other statistics I checked also confirmed that this wait event was the most significant on the cluster. So now we’ve got an event and we know that 67,275 sessions were waiting on it during ASH snapshots between 12:00am and 12:00pm today. Let’s see what SQL these sessions were executing when they got snapped. In fact lets even include the “gc current block busy” events since there was a bit of a gap for them too.
select sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 4486 and 4498
and event_id in (2701629120, 1478861578)
group by sql_id
having count(*)>1000
order by 2;

SQL_ID               CNT
------------- ----------
6kk6ydpp3u8xw       1011
2hvs3mpab5j0w       1022
292jxfuggtsqh       1168
3mcxaqffnzgfw       1226
a36pf34c87x7s       1328
4vs8wgvpfm87w       1390
22ggtj4z9ak3a       1574
gsqhbt5a6d4uv       1744
cyt90uk11a22c       2240
39dtqqpr7ygcw       4251
8v3b2m405atgy      42292
Wow – another big leap – 4,000 to 42,000! Clearly there’s one SQL statement which is the primary culprit. What’s the statement?
select sql_text from dba_hist_sqltext where sql_id='8v3b2m405atgy';

SQL_TEXT
---------------------------------------------------------------------------
insert into bigtable(id, version, client, cl_business_id, cl_order_id, desc
Its an insert statement. Any guesses yet about what the problem might be? Well an insert statement could access a whole host of objects (partitions and indexes)… and even more in this case since there are a good number of triggers on this table. Conveniently, the ASH in 10g records what object is being waited on so we can drill down even to that level.
select count(distinct(current_obj#)) from dba_hist_active_sess_history
where snap_id between 4486 and 4498
and event_id=1478861578 and sql_id='8v3b2m405atgy';

COUNT(DISTINCT(CURRENT_OBJ#))
-----------------------------
                           14

select current_obj#, count(*) cnt from dba_hist_active_sess_history
where snap_id between 4486 and 4498
and event_id=1478861578 and sql_id='8v3b2m405atgy'
group by current_obj#
order by 2;

CURRENT_OBJ#        CNT
------------ ----------
     3122841          1
     3122868          3
     3173166          4
     3324924          5
     3325122          8
     3064307          8
          -1         10
     3064369        331
           0        511
     3122795        617
     3064433        880
     3208619       3913
     3208620       5411
     3208618      22215
Well a trend is emerging. Another very clear outlier – less than a thousand sessions waiting on most objects but the last one is over twenty-two thousand. Let’s have a look at all three of the biggest ones.
select object_id, owner, object_name, subobject_name, object_type from dba_objects
where object_id in (3208618, 3208619, 3208620);

 OBJECT_ID OWNER      OBJECT_NAME                    SUBOBJECT_NAME                 OBJECT_TYPE
---------- ---------- ------------------------------ ------------------------------ -------------------
   3208618 JSCHDER    BIGTABLE_LOG                   P_2007_09                      TABLE PARTITION
   3208619 JSCHDER    BIGTABL_LG_X_ID                P_2007_09                      INDEX PARTITION
   3208620 JSCHDER    BIGTABL_LG_X_CHANGE_DATE       P_2007_09                      INDEX PARTITION
Now wait just a moment… this isn’t even the object we’re updating!! Well I’ll spare you the details but one of the triggers logs every change to BIGTABLE with about 7 inserts into this one. It’s all PL/SQL so we get bind variables and everything – it’s just the sheer number of accesses that is causing all the contention.

One further thing we can do is actually see which blocks are getting most contended for – the ASH records this too. (Isn’t the ASH great?)
select current_file#, current_block#, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 4486 and 4498
and event_id=1478861578 and sql_id='8v3b2m405atgy'
and current_obj# in (3208618, 3208619, 3208620)
group by current_file#, current_block#
having count(*)>50
order by 3;

CURRENT_FILE# CURRENT_BLOCK#        CNT
------------- -------------- ----------
         1330         238073         51
         1542          22645         55
         1487         237914         56
         1330         238724         61
         1330         244129         76
         1487         233206        120
One thing that I immediately noticed is that there does not seem to be a single hot block!!! (What?) Out of 40,000 sessions accessing these three objects no more than 120 ever tried to hit the same block. Let’s quickly check if any of these are header blocks on the segments.
select segment_name, header_file, header_block
from dba_segments where owner='JHEIDER' and partition_name='P_2007_09'
and segment_name in ('PLACEMENTS_LOG','PLCMNTS_LG_X_ID',
  'PLCMNTS_LG_X_CHANGE_DATE');

SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
BIGTABL_LG_X_CHANGE_DATE              1207       204809
BIGTABL_LG_X_ID                       1207       196617
BIGTABLE_LOG                          1209        16393
No – all seem to be data blocks. Why so much contention? Maybe the RAC and OPS experts out there already have some guesses… but first let’s explore one alternative method to check the same thing and see of the numbers line up.

AWR Segment Statistics

Here’s a handy little query I made up the other day to quickly digest any of the segment statistics from the AWR and grab the top objects for the cluster, reporting on each instance.

As an aside, there is a line in the middle that says “GC_BUFFER_BUSY_DELTA”. You can replace that line with any of these values to see the top objects for the corresponding waits during the reporting period:
LOGICAL_READS_DELTA
BUFFER_BUSY_WAITS_DELTA
DB_BLOCK_CHANGES_DELTA
PHYSICAL_READS_DELTA
PHYSICAL_WRITES_DELTA
PHYSICAL_READS_DIRECT_DELTA
PHYSICAL_WRITES_DIRECT_DELTA
ITL_WAITS_DELTA
ROW_LOCK_WAITS_DELTA
GC_CR_BLOCKS_SERVED_DELTA
GC_CU_BLOCKS_SERVED_DELTA
GC_BUFFER_BUSY_DELTA
GC_CR_BLOCKS_RECEIVED_DELTA
GC_CU_BLOCKS_RECEIVED_DELTA
SPACE_USED_DELTA
SPACE_ALLOCATED_DELTA
TABLE_SCANS_DELTA
col object format a60
col i format 99
select * from (
select o.owner||'.'||o.object_name||decode(o.subobject_name,NULL,'','.')||
  o.subobject_name||' ['||o.object_type||']' object,
  instance_number i, stat
from (
  select obj#||'.'||dataobj# obj#, instance_number, sum(
GC_BUFFER_BUSY_DELTA
) stat
  from dba_hist_seg_stat
  where (snap_id between 12831 and 12838)
  and (instance_number between 1 and 6)
  group by rollup(obj#||'.'||dataobj#, instance_number)
  having obj#||'.'||dataobj# is not null
) s, dba_hist_seg_stat_obj o
where o.dataobj#||'.'||o.obj#=s.obj#
order by max(stat) over (partition by s.obj#) desc,
  o.owner||o.object_name||o.subobject_name, nvl(instance_number,0)
) where rownum<=40;

OBJECT                                                         I       STAT
------------------------------------------------------------ --- ----------
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]                    2529540
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]               1     228292
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]               2     309684
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]               3     289147
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]               4     224155
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]               5    1136822
JSCHDER.BIGTABLE_LOG.P_2007_09 [TABLE PARTITION]               6     341440
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]        2270221
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]   1     220094
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]   2     313038
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]   3     299509
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]   4     217489
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]   5     940827
JSCHDER.BIGTABL_LG_X_CHANGE_DATE.P_2007_09 [INDEX PARTITION]   6     279264
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                      1793931
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                 1     427482
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                 2     352305
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                 3     398699
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                 4     268045
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                 5     269230
JSCHDER.BIGTABLE.P_WAREHOUSE [TABLE PARTITION]                 6      78170
JSCHDER.BIGTABL_LG_X_ID.P_2007_09 [INDEX PARTITION]                  771060
JSCHDER.BIGTABL_LG_X_ID.P_2007_09 [INDEX PARTITION]            1     162296
JSCHDER.BIGTABL_LG_X_ID.P_2007_09 [INDEX PARTITION]            2     231141
JSCHDER.BIGTABL_LG_X_ID.P_2007_09 [INDEX PARTITION]            3     220573
JSCHDER.BIGTABL_LG_X_ID.P_2007_09 [INDEX PARTITION]            4     157050
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                        393663
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                  1      66277
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                  2      10364
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                  3       6930
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                  4       3484
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                  5     266722
JSCHDER.BIGTABLE.P_DEACTIVE [TABLE PARTITION]                  6      39886
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]                 276637
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]           1      13750
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]           2      12207
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]           3      23522
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]           4      28336
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]           5      99704
JSCHDER.BIGTABLE.P_ACTIVE_APPROVED [TABLE PARTITION]           6      99118

40 rows selected.
Now as you can see, these statistics confirm what we observed from the ASH: the top waits in the system are for the BIGTABLE_LOG table. However this also reveals something the ASH didn’t – that the date-based index on the same table is a close second.

The Real Culprit

Any time you see heavy concurrency problems during inserts on table data blocks there should always be one first place to look: space management. Since ancient versions of OPS it has been a well-known fact that freelists are the enemy of concurrency. In this case, that was exactly the culprit.
select distinct tablespace_name from dba_tab_partitions
where table_name='BIGTABLE_LOG';

TABLESPACE_NAME
------------------------------
BIGTABLE_LOG_DATA

select extent_management, allocation_type, segment_space_management
from dba_tablespaces where tablespace_name='BIGTABLE_LOG_DATA';

EXTENT_MAN ALLOCATIO SEGMEN
---------- --------- ------
LOCAL      USER      MANUAL

SQL> select distinct freelists, freelist_groups from dba_tab_partitions
  2  where table_name='BIGTABLE_LOG';

 FREELISTS FREELIST_GROUPS
---------- ---------------
         1               1
And there you have it. The busiest table on their OLTP RAC system is using MSSM (Manual segment space management) with a single freelist group. I’m pretty sure this could cause contention problems! But in this case it wasn’t quite what I expected. It looks to me like the single freelist itself wasn’t the point of contention – but it was pointing all of the nodes to the same small number of blocks for inserts and these data blocks were getting fought over. But they were probably filling up quickly and so no single block had a large number of waits reported in the ASH. If anyone has another idea to identify the hot blocks then leave a comment and let me know!

Managing control files in ASM

2009-10-26T09:05:00.002-04:00

Copying Control File to Other locations
Just a couple of notes on how to create multiple ASM control files and moving control files to a different diskgroup.
Assuming we already have a ASM control file in the DATA diskgroup and we wish to add one to the FRA diskgroup

SQL>show parameter control_files

+DATA/SID/controlfile/current.730.698514673

SQL>alter system set control_files='+DATA','+FRA' scope=spfile;

SQL>shutdown immediate;

SQL>startup nomount;

SQL>exit

rman target

RMAN>restore controlfile from '+DATAHR/SID/CONTROLFILE/current.730.698514673';

SQL>alter database mount;

SQL>alter database open;

SQL>show parameter control_files

+DATA/SID/controlfile/current.730.698514673, +FRAHR/SID/controlfile/current.766.698514673

Moving Control File to Other locations

Here is an example of how to migrate from one ASM diskgroup to another Add the new disk discovery path in (if needed), create a diskgroup and add disks

SQL>alter system set asm_diskstring='/dev/oracle','/dev/oracle_hr/r*';

SQL>create diskgroup DATANEW external redundancy disk '/dev/oracle_hr/rdisk1','/dev/oracle_hr/rdisk2';

SQL>show parameter control_files

control_files string

+DATA10/SID/controlfile/current.796.695384211

SQL>alter system set CONTROL_FILES = '+DATANEW' scope=spfile

SQL>shutdown

rman target /

RMAN>startup nomount

RMAN>restore controlfile from '/+DATA/SID/controlfile/current.796.695384211';

SQL>alter database mount;

SQL>alter database open;

Ordering the sequence of execution plan steps

2009-08-12T00:04:00.002-04:00

Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed.

SQL execution plans are interpreted using a preorder traversal (reverse transversal) algorithm which you will see below. Preorder traversal is a fancy way of saying:

1. That to read an execution plan, look for the innermost indented statement. That is generally the first statement executed.
2. In most cases, if there are two statements at the same level, the first statement is executed first.

In other words, execution plans are read inside-out, starting with the most indented operation. Here are some general rules for reading an explain plan.

1. The first statement is the one that has the most indentation.
2. If two statements appear at the same level of indentation, the top statement is executed first.

To see how this works, take a look at this plan. Which operation is first to executed?

| 0 | SELECT STATEMENT | | 10 | 650 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 10 | 650 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| JOB | 4 | 160 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 10 | 250 | 3 (0)| 00:00:01 |

The answer is that the full table scan operation on the job table will execute first. Let’s look at another example plan and read it…

ID Par Operation

0 SELECT STATEMENT Optimizer=FIRST_ROWS

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 NESTED LOOPS

3 2 TABLE ACCESS (FULL) OF 'DEPT'

4 2 INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE)

By reviewing this hierarchy of SQL execution steps, we see that the order of operations is 3,4, 2, 1.

SEQ ID Par Operation

0 SELECT STATEMENT Optimizer=CHOOSE

3 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

4 2 1 NESTED LOOPS

2 3 2 TABLE ACCESS (FULL) OF 'DEPT'

1 4 2 INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE)

Understanding the sequence of explain plan steps is a critical skill, so let’s try some more examples:

Consider this SQL query:

select
   a.empid,
   a.ename,
   b.dname

from
emp a,
dept b

where
a.deptno=b.deptno;

We get this execution plan:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=150000

Bytes=3300000)

1 0 HASH JOIN (Cost=40 Card=150000 Bytes=3300000)

2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=150000 Bytes=

1800000)

What is the order of operations here?

Answer: Execution plan steps are 2, 3, 1

Consider this query:

select
   a.empid,
   a.ename,
   b.dname

from
emp a,
dept b

where
a.deptno=b.deptno;

We get this execution plan:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=150000
Bytes=3300000)

1 0 HASH JOIN (Cost=864 Card=150000 Bytes=3300000)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=826 Card=1

Bytes=10)

3 2 INDEX (FULL SCAN) OF 'IX_DEPT_01' (NON-UNIQUE) (Cost=26

Card=1)

4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=150000

Bytes=1800000)

What is the order of operations here?

Answer: Execution plans steps are 3, 2, 4, 1

Here is the same query, but slightly different plan:

select
   a.empid,
   a.ename,
   b.dname

from
emp a,
dept b

where
a.deptno=b.deptno;

We get this execution plan:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=39 Card=150000 Byte=3300000)

1 0 NESTED LOOPS (Cost=39 Card=150000 Bytes=3300000)

2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

3 1 TABLE ACCESS (FULL) OF 'EMP'
(Cost=37 Card=150000 Bytes=1800000)

What is the order of operations here?

Answer: Execution plans steps are 2, 3, 1

Let’s find the SQL execution steps for a three table join:

select
   a.ename,
   a.salary,
   b.dname,
   c.bonus_amount,
   a.salary*c.bonus_amount

from
   emp a,
   dept b,
   bonus c

where
a.deptno=b.deptno
and
a.empid=c.empid;

What is the order of operations here?

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=168 Card=82 Bytes=3936)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=12)

2 1 NESTED LOOPS (Cost=168 Card=82 Bytes=3936)

3 2 MERGE JOIN (CARTESIAN) (Cost=4 Card=82 Bytes=2952)

4 3 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

5 3 BUFFER (SORT) (Cost=2 Card=82 Bytes=2132)

6 5 TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82

Bytes=2132)

7 2 INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE) (Cost=1

Card=1)

This is a little tougher….

The execution order is 4,6,5,3,7,2,1.

Final Exam! What are the steps for this execution plan?

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2871 Card=2 Bytes=143)

1 0 UNION-ALL

2 1 SORT (GROUP BY) (Cost=2003 Card=1 Bytes=59)

3 2 FILTER

4 3 HASH JOIN (Cost=1999 Card=1 Bytes=59)

5 4 INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE)

(Cost=489 Card=14436 Bytes=404208)

6 4 INDEX (RANGE SCAN) OF 'XIN3BAG_TAG_FLT_LEG' (UNIQUE)

(Cost=1501 Card=10121 Bytes=313751)

7 1 SORT (GROUP BY) (Cost=868 Card=1 Bytes=84)

8 7 FILTER

9 8 NESTED LOOPS (Cost=864 Card=1 Bytes=84)

10 9 HASH JOIN (Cost=862 Card=1 Bytes=57)

11 10 INDEX (FAST FULL SCAN) OF 'XIN1SCHED_FLT_LEG' (UNIQUE)

(Cost=371 Card=1426 Bytes=41354)

12 10 INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE)

(Cost=489 Card=14436 Bytes=404208)

13 9 INDEX (RANGE SCAN) OF 'XIN2BAG_TAG_FLT_LEG' (UNIQUE)

(Cost=2 Card=2 Bytes=54)

Answer: The order of operations is 5, 6, 4, 3, 2, 11, 12, 10, 13, 9, 8, 7, 1.

Hope this help…………………………………..

Confiugure ssh in Three Simple Steps

2009-07-17T08:23:00.002-04:00

Configure Secure Shell to authenticate users without providing password

This procedure is useful for sftp batch transfers too.
This scenario assumes there are two nodes, named HOSTA and HOSTB. The same username is assumed to exist on both nodes.

1. Generate public keys on both nodes

Connect to the first node (HOSTA) and execute the following commands to generate RSA and DSA keys for both nodes:
/usr/bin/ssh.-keygen -t rsa
/usr/bin/ssh.-keygen -t dsa

2. Create the authorized_keys file and transfer it to the other node

Repeat the same command on your second node (HOSTB). When finished put the generated public keys on the authorized_keys file and ensure the public keys generated from the HOSTA is placed on the .ssh at the HOSTB and vice versa.

cat $HOME/.ssh/id_ras.pub >> authorized_keys
cat $HOME/.ssh/id_das.pub >> authorized_keys
HOSTA$ scp authorized_keys HOSTB:./.ssh
and on HOSTB
HOSTB$ scp authorized_keys HOSTA:./.ssh

Make sure you don't overwrite each others authorized_keys file.

3. Testing

Test both nodes can authenticate the user without requiring it to provide password:

Hope this will help!!!!!!!!!!!!!!!!!!!!!!!!

Accessing Sqlserver from Oracle Database

2009-07-13T04:53:00.002-04:00

Scenario:I want to access sql server 2000 database from oracle server 11g.

Following are the details about the servers.

Sql Server
=========
OS= Windows 2003 Server
Sqlserver Version= 2000
IP=192.168.1.100
default port=1433
DB name=hds

Oracle Server
============
OS= AIX 6.1
DB Version=11.1.0.7

Download Oracle Gateway software from OTN
http://download.oracle.com/otn/aix/oracle11g/aix.ppc64_11gR1_gateways.zip

1. Install oracle gateway under the ORACLE_HOME
2. Configure the files under $ORACLE_HOME/dg4msql/admin
copy the default initdg4msql.ora to your init{SID}.ora
cp initdg4msql.ora inithds.ora (in my case my sqlserver db name is hds)

more inithds.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=192.168.1.100:1433//hds
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

3. Similarly after configuring the gateway file, configure the tnsnames.ora and listener.ora files as:
cd $ORACLE_HOME/network/admin
more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/11.1.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=hds)
(ORACLE_HOME=/u01/app/oracle/11.1.0/db_1)
(PROGRAM=dg4msql)
)
)

more tnsnames.ora
CARS2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = EDWDEV)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hds))
(HS=OK)
)
)

4. After restarting the listener check the tnsping status. It should return ok result.
5. Then connect with sqlplus and create a dblink as:
sql>CREATE PUBLIC DATABASE LINK CARS_LNK CONNECT TO “edw” IDENTIFIED BY “edw” USING 'CARS';
Database link created.
sql>
6. After that try to select any data using db link it prompt me errors:
SQL> SELECT * FROM DUAL@cars_lnk;
D
-
X

1 row selected.
SQL>

Please note that in Oracle 11g The User/Passwords are case sensitive so make sure to enclose both in double quots.
If you have multiple sqlserver databases then you have to perform same steps and create seperate init.ora files and place the entries in tnsnames.ora and listener.ora files.
Reference:

http://download-uk.oracle.com/docs/cd/B28359_01/gateways.111/b31043/sqlserver.htm
metalink note : 437374.1

Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Tracking & auditing changes in init.ora parameters

2009-07-13T03:01:00.002-04:00

Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task. Sometimes, users which have “alter system” privilege can make unauthorized changes to the initialization parameters in the spfile on a production database. Hence, auditing changes to parameters is a critical DBA task. Fortunately, it's quite simple to audit these changes by implementing the audit_sys_operations=true.

Here is a method to track changes to the initialization parameters. In order to track all changes to parameters we can use audit for the alter system statement for any specific user

We should follow below steps to track changes to init.ora parameters:

1. ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
2. SHUTDOWN IMMEDIATE
3. STARTUP
4. CREATE USER TEST IDENTIFIED BY TEST;
5. GRANT DBA TO TEST;
6. AUDIT ALTER SYSTEM BY test;
7. CONN TEST/TEST
8. ALTER SYSTEM SET AUDIT_TRAIL=db SCOPE=SPFILE;

9. Create an alert script to notify the DBA when a parameter has changed.

Let's start by finding the action_name in the dba_audit_trail view for the alter system command:

SQL> select username, timestamp, action_name from dba_audit_trail;

USERNAME                       TIMESTAMP ACTION_NAME
------------------------------ -------------   ----------------------------
TEST                               29-MAY-09    ALTER SYSTEM

STEP 1 - We can track changes made by SYS user by setting audit_sys_operations parameter to TRUE.

SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.

STEP 2 - Next, we bounce the instance to make the change take effect:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Here we see our auditing parameters:
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /home/oracle/oracle/product/10 .2.0/db_1/admin/fkhalid/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

SQL> alter system set audit_trail=db scope=spfile;
System altered.

STEP 3 - Here we go to the adump directory and examine the audit files:
SQL> host
[oracle@localhost bin]$ cd /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/

[oracle@localhost adump]$ ls
ora_5449.aud ora_5476.aud ora_5477.aud ora_5548.aud ora_5575.aud ora_5576.aud

[oracle@localhost adump]$ cat ora_5576.aud
Audit file /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/ora_5576.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1/
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-92.el5
Version:        #1 SMP Tue Jun 10 18:49:47 EDT 2008
Machine:        i686
Instance name: kam
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5576, image: oracle@localhost.localdomain (TNS V1-V3)
Fri May 29 02:38:30 2009
ACTION : 'alter system set audit_trail=db scope=spfile'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0

STEP 4 - Now, create a crontab job to seek new entries in the adump directory.
#******************************************************
# list the full-names of all possible adump files . . . .
#******************************************************
rm -f /tmp/audit_list.lst
find $DBA/$ORACLE_SID/adump/*.trc -mtime -1 -print >> /tmp/audit_list.lst
STEP 5 - When found, send the DBA an e-mail:
# If initialization paramneter has changed, send an e-mail
if [ -f /tmp/audit_list.lst]; then
then
    # Now, be sure that we don't clog the mailbox.
    # the following statement checks to look for existing mail,
    # and only sends mail when mailbox is empty . . .
    if [ ! -s /var/spool/mail/oramy_sid ]
    then
       cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid
    fi
    sendmail . . .
fi

Please beware that using the auditing command imposes additional work on the production database.

Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Database Auditing (DDL & DML)

2009-07-13T01:14:00.001-04:00

Today I want to share some simple trigger to set the database wise auditing.

Method1:

CREATE TABLE ddl_audit_log (
   stamp     DATE,
   username VARCHAR2(30),
   osuser    VARCHAR2(30),
   machine   VARCHAR2(30),
   terminal VARCHAR2(30),
   operation VARCHAR2(30),
   objtype   VARCHAR2(30),
   objname   VARCHAR2(30))
/

CREATE OR REPLACE TRIGGER fkhalid.audit_ddl_changes
   AFTER create OR drop OR alter
      ON FKHALID.SCHEMA -- for individual schema auditing
      -- ON DATABASE     -- for whole database auditing
BEGIN
INSERT INTO fkhalid.ddl_audit_log VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER')||' FOR '||sys.dictionary_obj_owner,
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME
        );
END;
/
show errors

-- Now, let's try that
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table purge
/
SELECT * FROM ddl_audit_log
/

Method 2:

CREATE TABLE FKHALID.EVENT_TABLE
(
OBJECT_OWNER VARCHAR2(100 BYTE),
OBJECT_NAME   VARCHAR2(30 BYTE),
OBJECT_TYPE   VARCHAR2(20 BYTE),
DATE_CREATED DATE,
DDL_TYPE      varchar2(50)
);

create or replace trigger fkhalid.after_create_trg
after create
   on fkhalid.schema
begin
/* This trigger tracks the creation of table, index, sequence,
      procedure, function, package and type. */
   if sys.dictionary_obj_type='TABLE'
   OR sys.dictionary_obj_type='SEQUENCE'
    OR sys.dictionary_obj_type='PROCEDURE'
     OR sys.dictionary_obj_type='INDEX'
      OR sys.dictionary_obj_type='FUNCTION'
       OR sys.dictionary_obj_type='TYPE'
        OR sys.dictionary_obj_type='PACKAGE'
    then
     insert into fkhalid.event_table values
          (sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'CREATE');
   end if;
end;
/

create or replace trigger fkhalid.after_alter_trg
after alter
   on fkhalid.schema
begin
/* This trigger tracks the creation of table, index, sequence,
      procedure, function, package and type. */
   if sys.dictionary_obj_type='TABLE'
   OR sys.dictionary_obj_type='SEQUENCE'
    OR sys.dictionary_obj_type='PROCEDURE'
     OR sys.dictionary_obj_type='INDEX'
      OR sys.dictionary_obj_type='FUNCTION'
       OR sys.dictionary_obj_type='TYPE'
        OR sys.dictionary_obj_type='PACKAGE'
    then
     insert into fkhalid.event_table values
          (sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'ALTER');
   end if;
end;
/

alter table test_table2
modify (ename varchar2(100),enumber number);

create table test_table2
(ename varchar2(20),enumber number);

Method 3:

SHOW PARAMETER AUDIT
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SHUTDOWN
STARTUP

-----------------------------------------------------
CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp1
QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
-----------------------------------------------------
CREATE TABLE test_tab (
id NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;
-----------------------------------------------------

SELECT username,
    extended_timestamp,
        owner,
        obj_name,
        action_name
FROM    dba_audit_trail
WHERE   owner = 'audit_test'
ORDER BY timestamp;

Hope this will help
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Reading Alert Log Files using Shell

2009-06-30T02:49:00.001-04:00

Method 1:
This method is the simplest one and it just get the lines containing ORA in their lines.

cat alert_ABCD.log | grep 'ORA' | more

Sample Output:

WARNING: inbound connection timed out (ORA-3136)
ORA-00604: error occurred at recursive SQL level 1
ORA-03113: end-of-file on communication channel
WARNING: inbound connection timed out (ORA-3136)

Method 2:
This is little complex method using awk command.

cat alert_ABCD.log | awk '
BEGIN{date=""}
/ORA-/ {print date; print $0;next}
{date=$0;next}'

Sample Output: date and timestamp is included.

Wed May 20 14:41:59 2009
WARNING: inbound connection timed out (ORA-3136)
Error stack returned to user:
ORA-00604: error occurred at recursive SQL level 1
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Thu May 21 17:00:10 2009
WARNING: inbound connection timed out (ORA-3136)
Thu May 21 17:03:23 2009

Method 3:
This is the more complex method which used some patterns to find and present the alert log errors.

cat alert_ABCD.log | awk '
/ORA-/ {print last_date; print $0}
/^[F-W][a-u][d-u] [A-S][a-u][b-y] *[0-9]* [0-2][0-9]:[0-5][0-9]:[0-5][0-9] 20[0,1][0-9]/ {last_date = $0 }'

Some explanations:
First 3 characters are Mon/Tue/Wed/Thu/Fri/Sat/Sun so I "simplyfy" my pattern search by looking the lowest and highest first character: F (of Fri) and W (of Wed); same for second, ...
Then a space
Then the month: Jan/Feb/Mar/... So same idea: A (of April) to S (of September), then a to u, and so on
Then 1 or 2 spaces and 1 or 2 digits and a space
Then the time: hour: 00 to 23 then a ":" then minute: 00 to 59 and so on, then a space
And lastly the year, I chose to search for 2000 to 2019

Sample Output: Only the error lines will be displayed. Output is more accurate than both above outputs.

Wed May 20 14:41:59 2009
WARNING: inbound connection timed out (ORA-3136)
Thu May 21 16:44:35 2009
ORA-00604: error occurred at recursive SQL level 1
Thu May 21 16:44:35 2009
ORA-03113: end-of-file on communication channel
Thu May 21 17:00:10 2009
WARNING: inbound connection timed out (ORA-3136)

Cheers!!!!!!!!!!!!

12. Install Crystal Reports on Report Server or client

2009-06-30T02:26:00.003-04:00

coming sooon!!!!!!!!!!

11. Install configure Cobol to compile Cobol programs

2009-06-30T02:26:00.001-04:00

coming soon!!!!!!!!!!!!!!!!

How to fix - ORA-12514

2009-06-29T05:26:00.001-04:00

This simple two part procedure will help to diagnose and fix the most common sqlnet and tnsnames configuration problems.

1. Test communication between the client and the listener

We will use tnsping to complete this step. It's a common misconception that tnsping tests connectivity to the instance. In actual fact, it only tests connectivity to the listener.

Here, we will use it to prove that a) the tnsnames.ora has the correct hostname and port, and b) that there is a listener listening on the specified host and port. Run tnsping:

tnsping <your_tns_entry_name>If it is successful you will see something like this:

oracle@bloo$ tnspinng scr9

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP) (HOST = bloo)(PORT = 1521))) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = scr9)))
OK (40 msec)
If not, here are some common errors, and some suggestions for fixing them:

TNS-03505: Failed to resolve name
The specified database name was not found in the tnsnames.ora, onames or ldap. This means that tnsping hasn't even got as far as trying to make contact with a server - it simply can't find any record of the database that you are trying to tnsping. Make sure that you've spelled the database name correctly, and that it has an entry in the tnsnames.ora.

If you have a sqlnet.ora, look at for the setting NAMES.DEFAULT_DOMAIN. If it is set, then all entries in your tnsnames.ora must have a matching domain suffix.

TNS-12545: Connect failed because target host or object does not exist
The host specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping <hostname>'. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.

TNS-12541: TNS:no listener
The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.

tnsping hangs for a long time
I've seen this happen in situations where there is something listening on the host/port - but it isn't an oracle listener. Make sure you have specified the correct port, and that your listener is running. If all looks ok, try doing a 'netstat -ap | grep 1521' (or whatever port you are using) to find out what program is listening on that port.

2. Attempt a connection to the instance
Once you have proven that the tnsnames is talking to the listener properly, the next step is to attempt a full connection to the instance. To do this we.ll use sqlplus:

sqlplus [username]/[password]@<your_tns_entry_name>
If it works you will successfully log into the instance. If not, here are some common errors:

ORA-01017: invalid username/password; logon denied
This is actually a good error in these circumstances! Even though you didn't use the correct username or password, you must have successfully made contact with the instance.

ORA-12505: TNS:listener does not currently know of SID given in connect
Either the SID is misspelled in the tnsnames, or the listener isn't listening for it. Check the tnsnames.ora first. If it looks ok, do a 'lsnrctl status' on your server, to see what databases the listener is listening for.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
This is quite a common error and it means that, while the listener was contactable, the database (or rather the service) specified in the tnsnames wasn't one of the things that it was listening out for.
Begin by looking at your tnsnames.ora. In it, you will a setting like SERVICE_NAME=<name>.

If you are running a single instance database (ie. not RAC), and you are sure that you are not using services, it might be easier to change SERVICE_NAME= to SID= in your tnsnames. Using service names is the more modern way of doing things, and it does have benefits, but SID still works perfectly well (for now anyway).
If you would prefer to continue using service names, you must first check that you have not misspelled the service name in your tnsnames. If it looks alright, next check that the listener is listening for the service. Do this by running 'lsnrctl services' on your server. If there isn't an entry for your service, you need to make sure that the service_names parameter is set correctly on the database.

Avoiding Outer Joins

2009-05-17T06:59:00.001-04:00

Solution 1:

Oracle “Normal” joins are used to retrieve records from both the tables which satisfy join conditions. But sometimes we need records from the table which satisfy the join condition and also those records which are not present in another table. In this situation we go for Outer Joins.

If the tables being joined are having millions of records then using outer joins on such tables takes lots of time for execution. Even though the query is returning 10 or 100 records, the execution time taken by a query will be huge.

Please have a look at a simple query mentioned below. The query provides department names for, employees with ID between 1 and 100 and also those employees which are not allocated to any department.

select e.ID,e.NAME, d.NAME

from dept d, emp e

where d.id (+) = e.DEPT_ID

and e.id between 1 AND 100

In the above example we need only one column, department name, from DEPT table. But for this we need to perform a costly outer join on DEPT table. In such situations it is possible to avoid outer joins. Let’s have a look at the example given below,

select e.ID,e.NAME,(select NAME from dept d where d.id = e.DEPT_ID)

from emp e

where e.id between 1 AND 100

In this solution we are removing the DEPT table from the FROM clause of main query. The DEPT table is used as a sub query to retrieve values for department name column. This sub query will be executed only 100 times. If the DEPT (ID) column is indexed then the query on DEPT table will be faster.

Above solution gives positive results when,

Only one column is being selected from the huge INNER table
The query is joining tables with millions of records
Query return very few records as an output

Solution 2:

In case of normalized database we need to join multiple tables and some times we need to use outer joins. These queries take lots of time for retrieving 10 or 100 records.

Please see the example mentioned below. The query is trying to retrieve all the transaction between dates 01-Jan-2009 and 10-Jan-2009. For each demat transaction the query provides details for Beneficiary account, Depository participant account and settlement account involved in the transaction. The demat transaction details are stored in the DEMAT_TRANSACTION table and demat account details are stored in DEMAT_ACCOUNT table. DEMAT_ACCOUNT represents multiple accounts involved in a transaction and stores one record per account.

SELECT demat_trn.transaction_id,

benacct.party_name "Beneficiary Account",

dpacct.party_name "Depository Participant Party",

settleacct.part_name "Settlement Account"

FROM demat_transaction demat_trn,

demat_account benacct,

demat_account dpacct,

demat_account settleacct

WHERE demat_trn.transaction_id = benacct.transaction_id(+)

AND demat_trn.transaction_id = dpacct.transaction_id(+)

AND demat_trn.transaction_id = settleacct.transaction_id(+)

AND demat_transaction.transaction_date BETWEEN

TO_DATE ('01012009', 'DDMMYYYY')

AND TO_DATE ('10012009', 'DDMMYYYY')

In this example we can avoid outer joins by denormalizing the DEMAT_TRANSACTION and DEMAT_ACCOUNT tables. If there are maximum 3 accounts involved in the demat transaction, then the DEMAT_TRANSACTION table should be altered to accommodate details for all the three accounts for each transaction. After denormalization the final query will look like as mentioned below,

SELECT demat_trn.transaction_id,

benacct.party_name "Beneficiary Account",

dpacct.party_name "Depository Participant Party",

settleacct.part_name "Settlement Account"

FROM demat_transaction demat_trn

WHERE demat_trn.transaction_date BETWEEN

TO_DATE ('01012009', 'DDMMYYYY')

AND TO_DATE ('10012009', 'DDMMYYYY')

Since the denormalized query is fetching records from only one table, hence this query will definitely be executed faster than the normalized query.

Conclusion:

Outer joins are very costly when the tables being joined are very big and having millions of records. Depending on queries output it is possible to easily avoid the outer joins using Sub queries or denormalizing the tables involved in the Join.

15 Tips for New Employees

2009-05-17T06:56:00.001-04:00

Don’t be a know-it-all
By jumping into things, older and more experienced colleagues will perceive you as arrogant and you could lose your respect.
Appreciate others
Saying “thank you” to your boss and other colleagues for their help and guidance will have long-term positive effects.
Give in something extra
Volunteer for extra tasks and assignments to show your commitment, loyalty and diligence.
Listen before you speak
Try to understand and grasp every piece of information and make sure you understand everything before you give in your contributions.
Understand the culture of the organization
Observe how people interact with others and how things are done. Find out who is most admired and/or most influential and why.
Learn more about yourself
Your first job gives you a chance to figure out what you are best at and what kind of work you enjoy the most.
Take time out for developing new skills
Look out for opportunities of career development within and outside the organization and acquire new skills for your professional growth.
Dress professionally
Observe how people are generally dressed in the organization and follow that dress code.
Don’t feel shy to ask questions
The embarrassment will be much more if you end up messing up your work because you did not take clear instructions. So ask questions and take down notes.
Be punctual
Never be late for work and keep a good attendance. Try to come in before the official work time and stay a little late.
Stay away from office gossip
Don’t get involved in the office politics and gossip. This is especially important in the first few months.
Get involved in the informal events
Joining the sports club or other social events organized by the firm will give you a chance to develop cordial relationships with your co-workers.
Keep your boss informed
Give your boss updates of all your activities at work. This will increase your reliability and will show how responsible you are.
Remember people’s names
As soon as possible try to remember names of people you come into contact with.
Don’t complain or criticize
You might not be happy with the entire management system but wait before you start to give suggestions

Installation of Peoplesoft Components

2009-05-08T01:50:00.003-04:00

Following should be the sequence of Installing/Configuring Peoplesoft Applications

1.    Install Tuxedo 9.1
2.    Install BEA weblogic or IBM Websphere or oracle app server
3.    Install Peopletools 8.49 on Server
4.    Install Peopletools 8.49 on Windows client
5.    Install Peoplesoft HRMS Application on Server
6.    Install Peoplesoft HRMS Application on Windows client
7.    Install Database software (Oracle Database 10g Release 2)
        7.1    Peoplesoft Database creation part I :
        7.2    Peoplesoft Database creation part II:
        7.3    Peoplesoft Database creation part III:
8.    Configure Application Server
9.    Configure Web Server
        9.1    Encrypting the Password (AIX Only)
        9.2    Starting and Stopping WebSphere Application Servers
        9.3    Accessing the PeopleSoft Signon
10.    Configure Process Scheduler
11.    Install configure Cobol to compile Cobol programs
12.    Install Crystal Reports on Report Server or client

1 Installing Tuxedo 9.1

2009-05-08T01:50:00.001-04:00

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Entreprise", plateform "IBM AIX 64-bit", then under "PeopleSoft Enterprise - PeopleTools 8.49 Media Pack", choose the disk :
"BEA Tuxedo 9.1 for AIX, Solaris, and Linux"
As root, create the groups and the Oracle user :
[root@psoft ~]# groupadd dba
[root@psoft ~]# groupadd oinstall
[root@psoft:/root] ()$ useradd -g oinstall -G dba ora102
[root@psoft:/root] ()$ passwd ora102
Changing password for user ora102.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

[root@psoft:/root]$ cd /apps
[root@psoft:/apps]$ mkdir -p bea/tuxedo/9.1
[root@psoft:/apps/psoft/bea]$
2. Install software as oracle user.
[tuxedo@psoft:/software/BEA_Tuxedo_9.1]$ sh ./install.sh
01) ibm/aix53 02) linux/sles9 03) sun/sol9
Please select a platform? [1-3, q to quit, l for list]: 1
Installing for ibm/aix53
Is this correct? [y,n,q]: y
If you wish to quit the installation at any point
press the interrupt key or .
BEA Home being defaulted to /bea (y/n)
n
Please supply alternative BEA Home:
/apps/bea
Tuxedo 9.1 will be installed to /apps/bea/tuxedo91 (y/n):
n
Please supply alternative Tuxedo location:
/apps/bea/tuxedo/9.1
Accept default TListen password 'password' (y/n):
y
Continue with installation of Tuxedo 9.1 (y/n):
y
Installing...
Tuxedo 9.1 base installation complete
Installing patch at /apps/bea/tuxedo/9.1
using /bin/gunzip to uncompress archive
Installing server and client files...
Enter owner for patch files:
oracle
Enter group for patch files:
oinstall
The patch installation finished successfully.
[tuxedo@psoft:/software /BEA_Tuxedo_9.1]$
3. Add the two last line (in bold) in tuxedo's .profile
[tuxedo@psoft:/home/tuxedo]$ more .profile
# .profile
# Get the aliases and functions
if [ -f ~/.kshrc ]; then
. ~/.kshrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export BEAHOME=/apps/bea
export TUXDIR=/apps/bea/tuxedo/9.1
[tuxedo@psoft:/home/tuxedo]$
4. After reconnection, check the installation by existence of two files :
[tuxedo@psoft:/home/tuxedo]$ cd $TUXDIR
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ ls
bin cobinclude help include lib locale samples tux.env udataobj uninstaller
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ find . -name lic.txt
./udataobj/lic.txt
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ find . -name tlisten.pw
./udataobj/tlisten.pw
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ ln -s $TUXDIR/locale/C $TUXDIR/locale/Ja_JP
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ ls -lrt locale
total 60
-rw-rw-r-- 1 oracle oinstall 24 Jan 5 15:00 CATNAMES
lrwxrwxrwx 1 oracle oinstall 1 Jan 5 15:01 en_US.UTF-8 -> C
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 japanese.euc -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 japanese -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 ja_JP.ujis -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 ja_JP -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 13 Jan 5 15:05 ja_JP.eucjp -> ./ja_JP.eucJP
drwxrwxr-x 2 oracle oinstall 4096 Jan 5 15:05 ja_JP.SJIS
drwxrwxr-x 2 oracle oinstall 4096 Jan 5 15:05 ja_JP.eucJP
drwxrwxr-x 2 oracle oinstall 4096 Jan 5 15:05 C
lrwxrwxrwx 1 oracle oinstall 29 Jan 5 15:09 Ja_JP -> /apps/bea/tuxedo/9.1/locale/C
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$

2 Install BEA weblogic or IBM Websphere or oracle app server

2009-05-08T01:49:00.001-04:00

[root@psoft:/root]$ cd /apps/IBM
[root@psoft:/apps/IBM]$ ls
tuxedo
[root@psoft:/apps/IBM]$ mkdir -p Websphere/9.2
[root@psoft:/apps/IBM]$
2. Install software as Websphere user.
[Websphere@psoft:/home/Websphere]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export BEA_HOME=/apps/IBM/Websphere/9.2
[Websphere@psoft:/home/Websphere]$

Open a command prompt and execute the command - “installWAS. [bat/sh]”, which will start the installation of WebSphere ND6.1.

This will invoke the default GUI install window. If a window does not appear on a UNIX system, check that the DISPLAY environment variable is set properly. On this window, accept the Software license agreement and Click Next.

The next window will ask you for Install Location, followed by profile creation & server environments. Select ‘NONE’ as the WebSphere Application server environment option. PeopleSoft Internet Application install will create a customized cell profile for you.

Note. On Windows platform, selecting anything other than NONE as the WebSphere Application Server environment Profile may cause the install to not complete successfully. The other selections, besides NONE, create a Windows-OS lock on the profile, restricting PeopleSoft related interim fixes to not install properly.

A warning message will appear to check whether you want to proceed without creating a profile, Click ‘Yes’ to continue.

Warning Message

Click Next, to proceed with the WebSphere ND 6.1 installation.

After the installation has been completed, an ‘Installation Results’ window will appear. On this window, Uncheck the Create a new WebSphere Server profile option, as shown below, and then Click Finish.

You have completed the installation of the WebSphere ND v6.1.0.3 using the GUI mode.

Apply Interim Fixes

Refer customer connection link ftp://ftp.peoplesoft.com/outgoing/PTools/websphere/6103 to check whether any interim fixes are needed. If they are present, you must install them using the readme file.

3 Install Peopletools 8.49 on Server

2009-05-08T01:21:00.001-04:00

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Entreprise", plateform "AIX 64-bit", then under "PeopleSoft Enterprise - PeopleTools 8.49 Media Pack", then disk named as
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 1 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 2 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 3 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 4 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 5 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 6 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 7 of 7"
Here below, I'll show the Peopletools installation for the HRMS application. It's better to install one Peopletools software by application directory.
1. As root, the group oinstall has already been created previously.
[root@psoft:/apps/bea/weblogic/9.2]$
2. Start the installation in GUI mode.
At this step you'll need the license code from (depending of the database vendor) http://licensecodes.oracle.com/ent_keys_by_prod.html#PeopleTools
The license code are same for production usage.
In XClient window :
[oracle@psoft:/software/PeopleTools8.49/Disk1]$ ./setup.aix

Insert or change the directory of CD on demand (all the 7 cd will be required) :

4. Install the patch 8.49.08
Downloaded from ftp://ftp.peoplesoft.com/outgoing/ptools/84919
Password for unzipping the compress file from http://www.metalink3.com (a valid customer ID required)
Tab Update+Fixes, then Apply to Release
Choose Product Line => Peopletools
Product => Peopletools
Release => 8.49
Check the Bunldes checkbox, then search
Open the user doc html files to get the password.
Lastly, at this step you'll need the license code (depending of the database vendor) from http://licensecodes.oracle.com/ent_keys_by_prod.html#PeopleTools
In XClient window :
[oracle@psoft:/software/PeopleTools8.49.08/cd84908/disk1]$ ls
media.inf pt_responsefile.txt readme.txt setup setup.aix setup.exe setup.hp setup.hp-ia64 setup.jar setup.linux setup.solaris [oracle@psoft:/software/PeopleTools8.49.08/cd84908/disk1]$ ./setup.aix

5. Modify the .bash_profile
[oracle@psoft:/home/hrms9]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export ORACLE_HOME=/apps/oracle/ora102
export PS_HOME=/apps/psoft/hrms9
. $PS_HOME/psconfig.sh
6. Run the psadmin to check your install :

4 Install Peopletools 8.49 on Windows client

2009-05-08T01:19:00.001-04:00

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Enterprise", plateform "Linux x86", then under "PeopleSoft Enterprise - PeopleTools 8.49 Media Pack", then disk named as
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 1 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 2 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 3 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 4 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 5 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 6 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 7 of 7"

At this step you'll need the license code from (depending of the database vendor) http://licensecodes.oracle.com/ent_keys_by_prod.html#PeopleTools
The license code are same for production usage.
Just run the setup.exe located into the installation directory, and follow the instruction.
You have to indicate the ORACLE_HOME/bin directory (which is not the case on Unix/Linux).
Note, the Disk 5 will be skipped (not required).

Choose Unicode database if you want to load global application with different languages, choose Non-Unicode if not. Take care , a Unicode database requires much more space (between 2 and 3 times more than a Non-Unicode database).

Choose the module you want to install, in most of case, check all the checkboxes :

If you want later install some ML translation application,keep the Language pack :

Change the folder for each installation CD :

Then, you have to install the patch 8.49.08, the screenshots are almost same as here above.
Lastly, some programs have been added in your programs list, run the Application Designer to check the Peopletools version as well :

5 Install Peoplesoft HRMS Application on Server

2009-05-08T01:18:00.001-04:00

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Entreprise", plateform "IBM AIX 64-bit", then under "PeopleSoft Enterprise - Human Resources Management System and Campus Solutions 9.0 Translations Media Pack", then disk named as
"PeopleSoft Enterprise Human Resources Management System and Campus Solutions 9.0 Multi-Language"
"PeopleSoft Enterprise Human Resources Management System and Campus Solutions 9.0 (CD)"
1. Create a new folder, temporary for the installation task, as root
[root@psoft:/]$ mkdir tmphrms9
2. Start the installation in console mode:
At this step you'll need the license code from (depending of the database vendor) http://licensecodes.oracle.com/ent_keys_by_prod.html#HCM
The license code are same for production usage.
The temporary parameter (tempdir) might be skip if you have enough space in your default /tmp directory.
[oracle@psoft:/hrms9]$ ./setup.aix

Choose Unicode or Non-Unicode respecting what you choosed for the Peopletools installation where you'll install that Application :

For the Windows Server, on which you can run Process Scheduler, even if the database is on a Unix/Linux machine, for example n/Vision reports, that can help, check what you need to configure :

Give the path directory of the Peopletools you previously installed

Then uncheck the module you don't want to install :

Now, same for the ML, take care, the license code is the one for Tranlsation, regardless of the Application and Tools you install :

[...]
The screenshots are almost the same, eventhough, two difference :
1. You have not to choose which module you want to install
2. You have to choose your language :

The InstallShield Wizard has successfully installed PeopleSoft Enterprise HRMS and Campus Solutions 9.0 ML. Choose Finish to exit the wizard.