Oracle: Password Policy – Turn Off

Oracle: Password Policy

Smart Panda - DatabaseWell 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>';

 


Smart Panda - IdeaGood 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

PeopleSoft Object Owner Id (Objectownerid)

Smart Panda - Propeller Hat

PeopleSoft Object Owner: (OBJECTOWNERID)

Many years ago PeopleSoft introduced the object owner id which helps identify pretty much every PeopleSoft object (records, pages, content references, etc….) into where that object belongs.

This past week while working on a security matrix, the matrix needed to be broken down by module.  While with a little SQL magic and a little help from Excel and its filters the task was made significantly easier. In order to get a listing of the object owner ids within the system you can use look them up in the PSXLATITEM table.

SELECT * FROM PSXLATITEM WHERE FIELDNAME = ‘OBJECTOWNERID’ ORDER BY FIELDVALUE

If you want a specific application, Finance Objects have objectownerid starting with “F”, Human Resources “H”, Supply Chain “D”, Campus Solutions “S”, PeopleTools = “PPT”.  I also found some modules had multiple objectownerid values.

 

Smart Panda - Idea
This handy bit of SQL courtesy of my friend Issam came in really handy, I honestly had never worked with the “WITH” function.  This code is specific to SQL Server 2014, but with a tweak or two it will work in Oracle 12 without an issue.  I am sure it will run in earlier versions, but as I have limited experience with the “WITH” function, it was only tested with MSSQL 2014 and Oracle 12c.

WITH PR (PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH) AS (
SELECT P.PORTAL_NAME, P.PORTAL_PRODUCT, P.PORTAL_SEQ_NUM, P.OBJECTOWNERID, P.PORTAL_OBJNAME, P.PORTAL_LABEL, P.PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, cast(P.PORTAL_LABEL as varchar(4000)) AS MYPATH FROM PSPRSMDEFN P
WHERE P.PORTAL_LABEL = ‘Root’
AND P.PORTAL_NAME = ‘EMPLOYEE’
UNION ALL
SELECT P_ONE.PORTAL_NAME, P_ONE.PORTAL_PRODUCT, P_ONE.PORTAL_SEQ_NUM, P_ONE.OBJECTOWNERID, P_ONE.PORTAL_OBJNAME, P_ONE.PORTAL_LABEL, P_ONE.PORTAL_REFTYPE, P_ONE.PORTAL_URI_SEG1, P_ONE.PORTAL_URI_SEG2, cast( (MYPATH + ‘ –> ‘ + P_ONE.PORTAL_LABEL) as varchar(4000)) AS MYPATH FROM PR P INNER JOIN PSPRSMDEFN P_ONE ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
AND P.PORTAL_REFTYPE = ‘F’
AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME WHERE P_ONE.PORTAL_LABEL <> ‘Root’ AND P_ONE.PORTAL_NAME = ‘EMPLOYEE’ )

SELECT PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH FROM PR;

GO

Oracle: Data Export Pump – Directory Setup

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

Smart Panda - Export Data PumpData 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


Smart Panda - IdeaGood 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!

PeopleTools: Long/Lob & DataTime Datatype Issues

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

In PeopleTools 8.49 and earlier, Datetime and time fields are stored as DATE datatype in Oracle database. For example, you could see a DATETIME or a TIME field in Application designer might show as a DATE field in the Oracle database. Similar issues could be seen with LONG, CLOB and LOB datatype fields as well.From PeopleSoft PeopleTools 8.50 and higher, the TIMESTAMP datatype is now supported for TIME and DATETIME field types. These datatype changes are mandatory, and the DATE datatype will no longer be used for the  DATETIME and DATE datatype fields.Updating the Database for Timestamp:
This is a separate step performed during an application or tools upgrade. This step invokes a SQL statement called UPGDBOPTIONS_ENABLETIMESTAMP.SQL from PS_HOME/scripts folder. This DMS script updates the database to indicate that the new TIMESTAMP datatype is now enabled. If the upgrade is performed through PeopleSoft Change Assistant(as it should be), the step is displayed and can be run only if the upgrade is from PeopleSoft PeopleTools 8.49 or earlier.

What does this step do?
Why the DATE, TIME and DATETIME formats chosen in PeopleSoft application designer show as DATE field in the database and how is this controlled?
This feature is controlled by the DATABASE_OPTIONS field in  PSSTATUS record.
DATABASE_OPTIONS field can store four different values:

  1. 0 – PeopleSoft is using the old datatypes (Long fields and Legacy Unicode implementation).
  2. 2 – PeopleSoft is using the new Oracle datatypes supported with PT8.48 (LOBs and CLS Unicode implementation).
  3. 32 – PeopleSoft is using old datatypes, (LONGs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled.
  4. 34 – PeopleSoft is using the new Oracle datatypes supported with PT8.48 (LONGs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled.
PeopleTools Version and Features supported:
  1. Datatypes supported with PT8.48 – LOBs and CLS Unicode implementation
  2. Datatypes supported with PT8.50 – TIMESTAMP enabled
Depending upon which version of PeopleTools is installed and what features are enabled, an appropriate number from 0, 2, 32 and 34 will be updated while running this step.

Oracle: UDE-00008: operation generated ORACLE error 31626

Yesterday, I was working with Oracle’s data pump export tool, which is something I don’t normally do. I am not really sure why Oracle is so difficult to work with in regards to backing up, migration of data and recovery of data. I mean it is just flat out confusing when you don’t do it on a regular bases.

So I ran into all sorts of weird errors. The first one was:

ORA-23603: STREAMS enqueue aborted due to low SGA – after some research I found that the my SGA area was too small, which in my opinion “Who Cares” you are simply dumping data to a flat file why in the world does that need to be complicated, but….

The quick google search turned up to increase the streams_pool_size parameter and increase the SGA memory target area. The first change I needed to make was to increase the sysctl parameter to as this parameter defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space. So I modified the sysctl.conf file and increased my kernel.shmmax paramter and set the change active with the sysctl -p call. You need to shutdown/start the database for this change to be made. I also modified the init.ora file to increase the streams_pool_size parameter.

It took a couple of tries to get everything to run correctly, however, the last run I did presented me with the UDE-00008 error. What the heck, everything looks great, so again another google search reveals a novel idea: check the logs:

Master table “SYSTEM”.”SYS_EXPORT_FULL_02? successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
/oracle/datadump/fs92demo.dmp
Job “SYSTEM”.”SYS_EXPORT_FULL_02? successfully completed at Wed Jun 17 11:13:53 2015 elapsed 0 00:41:17

Well, what do we have here. Everything completed successfully. Apparently there is a glitch in Oracle’s data pump that sometimes appears randomly however, the job has ran successfully. Simply ignore the error and move on! If you want to read up on the bug apparently the bug number on Oracle is: 5969934. I honestly, didn’t take the time to read it.