Managing control files in ASM

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

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

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

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

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)

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

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

 

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

11. Install configure Cobol to compile Cobol programs

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

How to fix - ORA-12514

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

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,

1. Only one column is being selected from the huge INNER table
2. The query is joining tables with millions of records
3. 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

1. 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.
2. Appreciate others
   Saying “thank you” to your boss and other colleagues for their help and guidance will have long-term positive effects.
3. Give in something extra
   Volunteer for extra tasks and assignments to show your commitment, loyalty and diligence.
4. 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.
5. 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.
6. 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.
7. 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.
8. Dress professionally
   Observe how people are generally dressed in the organization and follow that dress code.
9. 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.
10. 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.
11. Stay away from office gossip
    Don’t get involved in the office politics and gossip. This is especially important in the first few months.
12. 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.
13. 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.
14. Remember people’s names
    As soon as possible try to remember names of people you come into contact with.
15. 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

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

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

[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.