Bobby Durrett's DBA Blog

I did a quick survey of the latest PeopleSoft manuals to find a set of links to pass on to a couple of coworkers of mine that are interested in doing PeopleSoft DBA work so I thought I’d include the links in a post.  This might give a new PeopleSoft DBA some highlights without having to read the entire manual set.

This page has a nice picture of how the environments connect:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tgst/concept_UnderstandingthePeopleSoftTopology-827f9d.html

This is the top level URL for the PeopleTools 8.53 documentation:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/index.html

Another nice architecture diagram:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tgst/task_WebServer-827f33.html

Nice overview of application development using app designer:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tapd/task_PeopleSoftApplicationDesignerImplementation-0776f7.html

Yet another architecture diagram:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_PeopleSoftPureInternetArchitectureFundamentals-c071ce.html

More in depth view of app server and its processes:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_ApplicationServers-c071d0.html

Web server with discussion of servlets and jolt:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_WebServer-c071dc.html

Nice overview of datamover commands:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tadm/concept_UnderstandingDataMoverScripts-077b05.html

Datamover basics

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tadm/task_CreatingandRunningPeopleSoftDataMoverScripts-077af9.html

Nice explanation of Oracle connections from PeopleSoft:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tadm/task_MonitoringPeopleSoftDatabaseConnections-077989.html

Good to know but not very clear explanation:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsec/concept_PeopleSoftAuthorizationIDs-c07669.html

Important to know but not very clear:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsec/concept_PeopleSoftSignIn-c0766f.html

PS_HOME versus PS_CFG_HOME

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/concept_UnderstandingPS_HOMEandPS_CFG_HOME-eb7ece.html

Starting psadmin

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_StartingPSADMIN-c07e6b.html

Nice run down of config files:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingPSADMINConfigurationFiles-c07e7a.html

App server menu:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingtheApplicationServerAdministrationMenu-c07e84.html

process scheduler menu:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingtheProcessSchedulerMenu-c07ea3.html

web server menu – I don’t think I’ve ever used this:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingtheWebPIAServerMenu-1773ed.html

– Bobby

4/27/2021

These links are all out of date. Here is the current PeopleTools manual page:

https://docs.oracle.com/en/applications/peoplesoft/peopletools/index.html

You might be able to find the current version of some of the things I mentioned in this older post.

I’m still studying for my Oracle 12c OCP exam and I was trying to run a simple example of using SQL*Loader Express and the first thing I did blew up and I think it is a bug.  When I load a table with one or two columns it works fine, but when I load a table with 3 or 4 columns the last column is not loaded.  Tell me this isn’t a special feature! 🙂

First I create the table with four columns:

create table test
(a varchar2(20),
 b varchar2(20),
 c varchar2(20),
 d varchar2(20));

Then I create a comma separated values file named test.dat with four values per line:

[oracle@ora12c dpsl]$ cat test.dat
a,b,c,d
f,g,h,i
j,k,l,m

Then I run sql*loader in express mode:

[oracle@ora12c dpsl]$ sqlldr system/xxxxxx table=test

SQL*Loader: Release 12.1.0.1.0 - Production on Mon Apr 21 07:32:43 2014

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

Express Mode Load, Table: TEST
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:
  3 Rows successfully loaded.

Check the log files:
  test.log
  test_%p.log_xt
for more information about the load.

Then I query the newly loaded table:

ORCL:CDB$ROOT:SYSTEM>select * from test;

A                    B                    D
-------------------- -------------------- --------------------
a                    b                    d
f                    g                    i
j                    k                    m

Queue the mysterious music.  Actually, now that I look at it really it is the third column that is missing.  Maybe it doesn’t work with a column named C.

Sure enough, here it is with column C replaced with column X:

A                    B                    X                    D
-------------------- -------------------- -------------------- --------------------
a                    b                    c                    d
f                    g                    h                    i
j                    k                    l                    m

So, I guess SQL*Loader Express doesn’t work with columns named C?  Odd.

– Bobby

Update on 05/16/2014:

As you probably can expect, this was user error on my part.  My standard header for sqlplus scripts has this code:

column u new_value us noprint;
column n new_value ns noprint;
column c new_value cs noprint;
 
select name n from v$database;
select user u from dual;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') c FROM DUAL;

I use this code to build a prompt that will tell me which container I’m in like this:

set sqlprompt &ns:&cs:&us>

But, this means I can’t use columns named n, u, or c, but in my sql*loader test I was using c.  So, not a bug, just a user error!

– Bobby

I was trying to recreate the scenario where a 12c container database is flashed back to a SCN before the point that I recovered a pluggable database to using point in time recovery.

I got this ugly ORA-00600:

RMAN> recover pluggable database pdborcl;

Starting recover at 16-APR-14
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/16/2014 06:07:40
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 datafile 32 , 33 , 34 , 35
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3631], [32], [4096], [4210689], [], [], [], [], [], [], [], []

I think the above error message stems from this bug:

Bug 14536110  ORA-600 [ktfaput: wrong pdb] / crash using PDB and FDA

There may have been some clever way to recover from this but I ended up just deleting and recreating the CDB through DBCA which was good experience playing with DBCA in Oracle 12c.  I’m trying to learn 12c but I have a feeling that I have hit a bug that keeps me from testing this flashback database, point in time recovery of a pluggable database scenario.  I wonder if I should patch?  I think that Oracle has included a patch for this bug in a patch set.  It could be good 12c experience to apply a patch set.

– Bobby

I got the newly available Kaplan test prep software for the Oracle 12c OCP upgrade exam.

I took the test in certification mode when I was tired at the end of the day some day last week and got 44% right – fail!  I usually wait until I get all the questions right before taking the real test so I have a ways to go.

The practice test software has been useful  in terms of showing me things I didn’t study very well or at all.  I’m expecting to significantly improve my correct answer percentage on my next pass.

I’m a little nervous though because it seems that the real test involves some questions that are generic database questions and I don’t think that the test prep software includes that section.  If you look at the list of topics they have a  section called “Key DBA Skills”.  I’d hope that after 19 years as an Oracle DBA I’d have some skills, but there are plenty of things I don’t do every day, such as setting up ASM.  I guess I’ll just have to bone up on the key areas of pre-12c that I don’t use all the time and hope I’m not surprised.

Anyway, I’m at 44% but hoping to make some strides in the new few weeks.

– Bobby

Here is a zip of two examples I built as I’m learning about the new adaptive plans features of Oracle 12c: zip

The first example has the optimizer underestimate the number of rows and the adaptive plans feature switches the plan on the fly from nested loops to hash join.

In the second example the optimizer overestimates the number of rows and the adaptive plans feature switches the plan from merge join to nested loops.

I ran the same scripts on 12c and 11.2.0.3 for comparison.

Example 1 11g:

Plan hash value: 2697562628

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      18 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |      18 |
|   2 |   NESTED LOOPS                |      |      1 |        |      8 |00:00:00.01 |      18 |
|   3 |    NESTED LOOPS               |      |      1 |      1 |      8 |00:00:00.01 |      17 |
|*  4 |     TABLE ACCESS FULL         | T1   |      1 |      1 |      8 |00:00:00.01 |      14 |
|*  5 |     INDEX RANGE SCAN          | T2I  |      8 |      1 |      8 |00:00:00.01 |       3 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |      8 |      1 |      8 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Example 1 12c:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |      1 |      8 |00:00:00.01 |       6 |  2168K|  2168K|     1/0/0|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      8 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |      1 |     16 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Example 2 11g

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |      16 |       |       |          |
|   2 |   MERGE JOIN                  |      |      1 |      4 |      1 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2   |      1 |     16 |      2 |00:00:00.01 |       2 |       |       |          |
|   4 |     INDEX FULL SCAN           | T2I  |      1 |     16 |      2 |00:00:00.01 |       1 |       |       |          |
|*  5 |    SORT JOIN                  |      |      2 |      4 |      1 |00:00:00.01 |      14 | 73728 | 73728 |          |
|*  6 |     TABLE ACCESS FULL         | T1   |      1 |      4 |      1 |00:00:00.01 |      14 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Example 2 12c

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   NESTED LOOPS                |      |      1 |        |      1 |00:00:00.01 |       5 |
|   3 |    NESTED LOOPS               |      |      1 |      4 |      1 |00:00:00.01 |       4 |
|*  4 |     TABLE ACCESS FULL         | T1   |      1 |      4 |      1 |00:00:00.01 |       3 |
|*  5 |     INDEX RANGE SCAN          | T2I  |      1 |        |      1 |00:00:00.01 |       1 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

The output of the plans for the 12c examples end with this line:

Note
-----
   - this is an adaptive plan

So, that tells me it is the adaptive plan feature that is changing the plan despite the wrong estimate of the number of rows.

– Bobby

Spent a good amount of time yesterday and today reading about auditing in Oracle 12c.  Can’t say I read every word, but I think it was worth reading the three chapters in the Security manual related to auditing:

Chapter 21 Introduction to Auditing
Chapter 22 Configuring Audit Policies
Chapter 23 Administering the Audit Trail

I haven’t used these features but it seems like a major new piece of code with the Unified Audit Trail.

I also read this chapter of the VLDB guide because it seemed to have a lot of things that were either new to 12c or new to me:

Chapter 5 Managing and Maintaining Time-Based Information

This chapter describes features that cause data to age out and get moved on to less expensive storage automatically over time.

Anyway, just wanted to pass on some full chapters that I’ve read and am pondering as I try to comprehend the new 12c features.

– Bobby

A RMAN restore and recovery that I completed today answered a couple of questions that remained after the recovery that was the topic of my post from June.  Both today’s recovery and June’s involved a restore of a production database on another host and a recovery of that database to a particular point in time.

Question 1: How to rename redo logs?

When doing a restore and recovery to a point in time RMAN does not restore the redo logs.  So, the production redo log directory does not have to exist on your target.  All you have to do is rename the redo logs after the restore and recover rman commands and before the alter database open resetlogs command.

Oracle document 1338193.1 in step 8 titled “Relocate all the online redo logs” documents the needed command and when to run it.

For each production redo log you run a command like this on the mounted but not opened restored and recovered database:

alter database rename file 
'old redo log path and name' to 
'new redo log path and name';

Question 2: How to I prevent the restored archive logs from filling up the archive filesystem?

It turns out that there is an option of the recover command that limits the amount of space the restored archive logs will take up and there is another option that causes the recover command to delete the archive logs after applying them:

recover database delete archivelog maxsize 50 G;

Otherwise this was the same case as the earlier blog post.  But, at least in this case I didn’t worry about archivelogs filling up the filesystem and I was able to put the redo logs where I wanted them.

– Bobby

This is the second in a series of posts about different performance tuning authors and speakers who have had a big impact on my career.  My first post was on Craig Shallahamer.  I am ordering these posts chronologically.  Craig was the first to impact my career by improving my Oracle performance tuning knowledge.  The next person was Don Burleson.

I have heard Don Burleson speak at several user group conferences and got a lot out of these talks, but I got the most benefit from his book titled “Oracle 9i High-Performance Tuning with STATSPACK“.  Don’s 9i Statspack book helped me understand what was in the various v$ views that Statspack captures.  I mentioned in an earlier post that a coworker of mine asked how to learn about the different parts of an AWR report.  I learned about these initially from Don Burleson’s book.  Today, three major database versions later, on Oracle 12c and with the more full featured AWR reports I still use information I learned about v$ views from Don’s 9i Statspack book.  The idea of taking snapshots of the v$ views like v$system_event and getting the difference between the values captured in two snapshots remains a critical component to Oracle database tuning.

In addition to understanding the v$ views I learned how to add operating system metrics like CPU utilization, disk I/O latency, and network latency to the database metrics captured by Statspack.  A very cool connection occurred when I realized that Don Burleson’s approach to augmenting Statspack database v$ information with Unix operating system metrics mirrored the way Craig Shallahamer’s OSM Toolkit stores both v$ information and Unix OS metrics.  It has been a huge help to understand what the operating system tools like sar and vmstat are telling you and compare that to what Statspack is saying as you observe the changes in v$ view values over time.

Lastly, I remember learning about database internals and their impact on performance.  One example that stands out is that I learned from Don Burleson’s book that indexes are more effective if rows in the table are ordered by the indexed columns.  Later I learned more about “clustering factor” and how this value attempts to quantify how ordered the rows are relative to the indexed columns.  But, Don’s 9i book first opened my eyes to the relevance of the way the rows are sorted in the table relative to the columns in the index.  I’m sure I learned other key things but the relationship between the indexed columns and the order of rows really opened my eyes to how data organization on the disk can impact performance.

In conclusion, Don Burleson was the second major figure in my Oracle performance tuning journey.  His 9i Statspack book in particular improved my knowledge of v$ tables, operating system performance metrics, and Oracle database tuning topics and internals.  Each of these three types of knowledge have proven invaluable in developing my performance tuning skills.

– Bobby

p.s. I believe this is the expanded more current AWR based version of the 9i Statspack book: Oracle Tuning: The Definitive Reference, Third Edition

In an earlier blog post I showed how I used coe_xfr_sql_profile.sql from the sqlt toolkit from Oracle support to force a particular sql statement to run with a more efficient plan that it had used in the past.

Today, I’m trying a slight variation.  Take the problem query, run it with hints, and use coe_xfr_sql_profile.sql to apply the resulting plan to the original query.  I built a quick and dirty test to make sure it works.

-- create test table with index and stats

SQL> create table test as select * from dba_tables;

SQL> create index testi on test(owner);

SQL> execute dbms_stats.gather_table_stats(NULL,'TEST');

-- run query unhinted

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- show plan - uses the index

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 1551939256

--------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |
|   1 |  SORT AGGREGATE              |       |     1 |     9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |  2122 | 19098 |
|*  3 |    INDEX RANGE SCAN          | TESTI |  2122 |       |
--------------------------------------------------------------

-- use hint to force full scan

SQL> select /*+full(test) */ sum(blocks) from test 
where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- get plan with full scan

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5521zhmq67vun, child number 0
-------------------------------------
select /*+full(test) */ sum(blocks) from test 
where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

So, let’s pretend that sql_id=a0nw2xjnmjcxd is the real production query you are trying to tune.  Without hints it is doing an index range scan.  With a hint you run a similar query forcing a full scan.  The new query, sql_id=5521zhmq67vun has a plan with this hash value: 3467505462.  So, I used coe_xfr_sql_profile.sql to force the unhinted original query to run with the hinted query’s plan:

cd /var/opt/oracle/admin/sqlt/utl

-- login to sqlplus as system

@coe_xfr_sql_profile.sql a0nw2xjnmjcxd 3467505462

-- login to sqlplus as system

@coe_xfr_sql_profile_a0nw2xjnmjcxd_3467505462.sql

Now when I run the original query it runs with the new plan and indicates that the profile is in use:

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

Note
-----
   - SQL profile coe_a0nw2xjnmjcxd_3467505462 used for 
     this statement

I edited the output to make it more readable.  See the zip with the full scripts and logs.

So, if you have a recurring production SQL query that runs faster with hints just run that query once with the hints and then use coe_xfr_sql_profile.sql to force the real query to run unchanged but with the same plan as the hinted query.

– Bobby

I attended two events this week that used the term “analytics” to describe a performance tuning feature that displays metrics in various useful ways.

On Tuesday, I attended a Delphix 4.0 Webinar where Delphix’s CTO Adam Leventhal described the new Delphix Analytics including the very interesting Latency Heat Map.  The latency heat map looked like a customer usable front end to the sort of performance information that Delphix support had collected when I worked with them on some network issues.  In this case the word analytics seems to mean the ability to look at the performance information in an easy to understand visual way that you can customize to see various perspectives. such as NFS latency, disk latency, etc.

Yesterday, Oracle gave us a four-hour on site OEM 12c seminar which included “ASH analytics”.  It was similar to the Delphix analytics in that you could see performance information visually and choose among various dimensions – machine, wait, session id, etc.

Perhaps Delphix and Oracle drew from business reporting – cubes, dimensions, drilling down – and applied the approach to performance information.  I have not used either example to solve a real problem so I can not vouch for the usefulness of either, but I thought it was an interesting coincidence that I ran into two such similar features in the same week.

– Bobby