Oracle: Password Policy

Well working with a new PeopleSoft Oracle Database, I went to create a new connect id user on the database with a relatively straight forward password and the database angry told me – NO. It said that the password policy required specific elements to be included with the password.  As most people know the system administrator password and connect id password associated with the Oracle Database need to be 8 characters long and should only contact alpha and numeric characters.  Seriously do not try to make it long or shorter and never put special characters in the password.  Over the years I have been mesmerized by the number of times this password has caused me grief.

So if there is a complex password policy set on the default profile you can turn it off by issuing the following command within sqlplus:

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

To determine what the profile is, you can issue the following statement:

select profile from DBA_USERS where username = '<username>';

Good To Know:

There are many elements in the profile that can cause issues one of them is the password expiry option.  Often not a good plan to have you main database account to stop functioning because of a password expiration, because Murphy’s Law says it will expire the day your DBA is on vacation and they will NOT pick the phone up that day.

Click here to go article:  Oracle Database User – Password Expiry

Oracle Database Export Data Pump (expdp):  Directory Default Setup

Data Export Pump utility (expdp) is designed for unloading data and metadata into a set of operating system files called a dump file set. The Data Export Pump Dump file set can be imported only by the Data Import Pump utility (impdp). The Data Export Pump Dump file set can be imported on the same system or it can be moved to another system and loaded there. The Data Export Pump Dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The Data Export Pump Dump files are written in a proprietary, binary format. During an import operation, the Data Import Pump utility uses these Data Export Pump Dump files to locate each database object in the dump file set.

Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects. In order to have the Data Export Pump Dump file be in a specific location you will need to do the following:

Step 1:

Login into the database using the command:  sqlplus / as sysdba

Step 2:

SQL> create directory dmpdirectory as ‘/my/new/dump/directory’;

Step 3:

SQL> grant read, write on directory dmpdirectory to scott;

Step 4:

Execute the command:  expdp scott/tiger directory=dmpdirectory dumpfile=scottdata.dmp

Good To Know:

As of Oracle 10gR2, there is a specific directory that is used by default:  DATA_PUMP_DIR, to see what the default value is you can run the command:

SQL> select directory_path from dba_directories where directory_name = ‘DATA_PUMP_DIR’;

DIRECTORY_PATH
--------------------------------------------------------------------------------
/oracle/product/11.2.0.4/db_1/rdbms/log/

Here is a great link for working with Oracle Data Pump.
Happy Exporting!

Pivot Grid: Setup Issue

Pivot Grid supports operational dashboard reporting within the Oracle PeopleSoft PeopleTools framework to provide a pivot table and chart representation of data using PeopleSoft Query data source. The framework also enables users to see different views of the data as in an Microsoft Excel pivot table, and the same data is also available in a chart view.

When doing a recent upgrade for a client they were experiencing issues opening up some pages in the Billing module.  The reason was that the Pivot Grid popups for the page were generating the error:

Generic error while executing the Query datasource. (268,91)

Interestingly, this error was only occurring in my Oracle Database systems.  No errors were being generated in SQL Server.  It would appear that there is a corruption in the data load for the data sources of the Pivot Grid.  In order to resolve this, My Oracle Support (MOS) recommends that you do the following:

Step 1:

Login with a super user who has ‘PivotGridAdmin’ role. This role can be added from the navigation: PeopleTools > Security > User Profiles > User Profiles – Roles page.

Step 2:

Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard.

Step 3:

Open up the following Pivot Grids in the Pivot Grid Wizard and just move through steps 1 to 5 (no changes are necessary) and then save the pivot grid:

For Billing:

BI_BILLS_BY_STATUS_PVG
BI_CUST_IVC_PVG
BI_CUST_IVC_PVG2
BI_IVC_ANALYSIS_PVG
BI_LOC_PENDING_DRAW
BI_POST_IVC_PVG

For Accounts Payable:

AP_DB_VENDOR_LIABILITY
AP_S360_DB_VENDOR_BALANCE
AP_S360_DISCOUNTS
AP_S360_DB_AMOUNT_BY_STATUS
AP_PYCYCL_PGV
AP_S360_MATCH_EXCEPTION
AP_DB_AMOUNT_BY_STATUS

Step 4:

Retest the issue.

Recently, I was working on a PeopleTools 8.44 to 8.54 Upgrade and ran into some major issues with the fact that the database so far behind in so many areas.  On the Oracle platform PeopleSoft changed the way it handled Long datatype fields and also how it handled Date & Time datatype fields.  The  part that I found was very difficult was some of the scripts that are more current don’t seem to take into account that coming from 8.44 might be an issue in handling these different datatypes.

Basically, in order to make this upgrade work I did a 8.44 to 8.53 upgrade first, and made sure that the LONG TO LOB datatype conversion was ran before the upgrade and that the DATABASE_OPTIONS field on PSSTATUS was set to 34 datatype setting.  Otherwise I ran into all sorts of weird issues with datatypes.

DATE and DATETIME, LONG and LOB fields in PeopleSoft and Oracle database

Oracle Database: Oracle Performance Secret

When working with a new Oracle Database on a Windows 2012R2 server running Oracle 11.2.0.4.  The PeopleTools Upgrade process was on the last step which is to do a Final Alter Audit (FNLALTAUD).  This Upgrade Step looks at all the tables within the system and determines if the metadata is in sync with what is actually in the database.

14 hours into the process and has to be done, but as per usual it is next to impossible to tell with PeopleSoft.  Since this really should not take anymore than an hour or two, the process is killed and the following Oracle Performance Secret is executed in SQLPlus:

analyze table sys.CDEF$ delete statistics;
analyze table sys.CON$ delete statistics;
analyze table sys.USER$ delete statistics;

Rerunning the Upgrade Task after apply the Oracle Performance Secret and it is now running in:  29 minutes.  That is much more acceptable.

What is the SYS Schema?

The SYS schema stores all of the base tables and views for the Oracle Database’s Data Dictionary.  The Data Dictionary is critical to the operation of Oracle Database.  It is imperative to maintain the integrity of the data dictionary, so manipulation of the Data Dictionary should only be done by Oracle itself.

Where did this fix come from?

During upgrades of environments there has been some reported cases of very poor performance using Oracle PeopleSoft Data Mover to import data into the new environment.  We have experienced this problem while upgrading environments specifically the Final Table Audit (or Initial Audit) Upgrade Tasks.  PeopleSoft has a very high number of constraints (Finance v9.2, I just queried had 2.7 million rows), which are stored on the CDEF$ & CON$ Data Dictionary Tables. If the Cost Based Optimizer (COB) has bad statistics to work from it is often seen that the optimization plan will come up with Full Table Scans which are extremely poor performing.  Patch #4618615 & #5103126 from Oracle apparently address this issue, and so the problem should be limited to Oracle Database 10.2 to 11.0.  However, often times when poor performance is effecting an environment we apply this Oracle Performance Secret and performance typically is greatly improved.