Oracle DB – Connect Fails

Oracle Database – ORA-01017 when running “sqlplus / as sysdba”

 Smart Panda - Oracle DB SecurityI had a new oracle database server that was working great, and after some security, changes to tighten up the server, the operating system user was receiving an ORA-01017 error every time it tried to connect using the “sqlplus / as sysdba” command. The user could connect with no problem using the command “sqlplus sys as sysdba” and entering the password.

As it turned out the operating system user in this case “oracle” was not in the Oracle group “dba”.

After adding the user back into the dba group the user was able to log back in without an issue:

usermod -a -G dba oracle

PeopleSoft – PUM – Failure to Start

Smart Panda - PeopleSoft in the CloudPeopleSoft – PUM – Failure to Start

During the last Windows Update on the PUM server the Virtual Box Image failed to start correctly. In the Logs you will see:

Starting PeopleSoft Application Server Domain APPDOM: [FAILED]


Starting PeopleSoft PIA Domain peoplesoft: [FAILED]

This environment had been running for sometime prior to this, and so doing a little investigate it turned up that there was no drive space left on the associated “/” root drive.

The quickest way to resolve this is to log into the PUM environment as root, and cleanup the files in the application server and process scheduler directories.  We have found that there is “core” files that are generated every time the PUM server is improperly shutdown and typically these files are very large.  Removing them is often the quickest way to get space back.  Look at the following directory locations:

Remove core dump files (core.*), trace files, log files and caches files from the following directories:


Once complete re-check the drive space availability and you should see a significant amount of space available again which will allow log writing to occur again.


Oracle – OPSTAT Maintenance

Smart Panda - Database

Oracle – OPSTAT Maintenance

The other day a clients production environment was having some really bad performance issues and it was causing some jobs to fail with deadlock or wait timeouts.  This seemed highly odd as the wait timeouts where on dedicated temporary tables which actually makes no sense.  After a deeper dive the system was trying to do statistics updates on the table and it was trying to purge from the OPSTAT tables.

When reviewing the OPSTAT tables which are tables associated with Oracle’s Optimizer Statistics, it turned out that there was 330 million entries in the wri$opstat_histhead_history table. Approximately 5 millions rows for 31 days, but you may say that isn’t 330 million and you’d be correct. There is a background MMON process that runs every 24 hours that which must run in 5 minutes or the task is not executed.  Basically once the table got to a specific size the maintenance job couldn’t keep up with the volume.

This was caused when the client wanted stats to be ran nightly on the entire main schema which contains 72,000 records and indexes.  So the first thing was to change the statistics job to weekly. Next the retention policy was changed to 7 days:

exec dbms_stats.alter_stats_history_retention(7);

Now to clean the necessary statistics:

exec dbms_stats.purge_stats(sysdate-7);

Yes, this is a terribly thing to run and it took 7 hours on the environment, but no outages where necessary and the environment continued to function without issues.

Here is a great script to see the size of the AWR being used (Thanks to Oracle DBA – A Lifelong learning experience):

set linesize 120
set pagesize 100
col ash form a30
col retention form a30
col snap form a30
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Move Procedure" FORMAT A40
SELECT  occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
WHERE occupant_name = 'SM/AWR'

PeopleSoft Oracle – Security Setup

 Smart Panda - Oracle DB SecurityOracle – Security Setup

I had a client ask me last week to change up all the oracle – security setup in their production Oracle Database. The cleanup was relatively straightforward because we only use the basic accounts for the PeopleSoft environment and all the passwords had been changed since going live so there was very little risk to the environment, but they wanted to be safe so:

PeopleSoft Default Accounts: Access-Id, this account is typically by default sysadm, however you can define this account to be pretty much any name, it just needs to ensure that it is an 8-digit id and even though Oracle says the password can be greater, I highly recommend the password be 8 digits and make sure not to include any funky characters (we have seen all sorts of issues with this password being anything special). The next account we need to ensure it is working correctly is the Connect-Id, this account is used for authentication purposes with PeopleSoft.  This account has a base of 3 tables it can select data from, PSOPRDEFN, PSACCESSPRFL, PSSTATUS. As of PeopleTools 8.55, an additional grant for select is on table PSACCESSPROFILE which basically replaces PSACCESSPRFL and supposedly supports passwords up to 30 characters. The next account you will see is the PS account, this account is created when the environment is built and contains an table PS.PSDBOWNER that shows the database name with the schema owner for that database. The PS account is not allowed to be connected to.  The last two accounts you should have be default are SYS & SYSTEM, neither of which should be accessed by anybody expect your DBA.

The first major item is that they wanted the dba’s to have dedicated accounts instead of using the sysadm account.  So to do this:

In SQL*Plus:

create user dbauser1 identified by dbapassword1 ;

grant create session, grant any privilege to dbauser1;

grant connect, resource, dba to dbauser1;

grant unlimited tablespace to dbauser1;

All the work the dbauser1 needs to do is in the sysadm schema, but when it logs in it will default to the schema dbauser1.  So to fix this, we build a trigger to execute a call to switch the current schema on login:

In SQL*Plus:

exsql VARCHAR2(100);

In order to troubleshoot the environment we had created a sysadm_read account which had the role sysadm_read_only, which has select access to all PS tables in the SYSADM schema. This account was shared by multiple users for data validation.  It was determined that they wanted to have dedicated accounts for each user and they wanted to have a higher level of password security so this is where things go creative.

The first element that I want to enable is a better level of password security. Oracle has delivered password verify functions which can be applied to the environment quickly and easily.  You will want to modify the script to ensure that it affects the correct profile for your environment.  The script is:  utlpwdmg.sql which for Oracle 12c introduced two functions ora12c_verify_function and ora12c_strong_verify_function.  For my example I went with the ora12c_verify_function that more than meets the minimum requirements for the client. So now we create a new profile for the database users:

In SQL*Plus:

create profile sysadm_read_only limit
failed_login_attempts 3
password_lock_time 1
password_life_time 90
password_reuse_max 3
PASSWORD_VERIFY_FUNCTION    ora12c_verify_function    ;

This creates a new profile that allows for only 3 failed login attempts, if the user fails all 3 times the account is locked out for 1 day. Password is valid for 90 days, and can only be used every 4th time it is changed, and the password verification as at least 8 characters, at least 1 letter, at least 1 digit, must not contain database name, must not contain user name or reverse user name, must not contain oracle, must not be too simple like welcome1, and must differ by at least 3 characters from the old password (ora12c_verify_function).

Now we just need to create the new users with the sysadm_read_role:

In SQL*Plus:

create user readuser1 identified by readpassword password expire profile sysadm_read_only;

grant sysadm_read_role to readuser1;

grant connect to readuser1;

Now when the user logs into the environment they will be required to change their password and they will have read only access to the sysadm schema.  We have stored procedure that is scheduled to run nightly that grants select access to all PS tables in the sysadm schema and creates public synonyms for each one so that the read only users can select from the PS tables.  The grants are done to the sysadm_read_role Role.

Oracle 12c – TableSpace Maintenance

Smart Panda - DatabaseOracle TableSpace Maintenance

Normally, I don’t get to worried about the size of TableSpaces, as they grow and shrink all the time. However, recently while setting up some automation scripts I was having problems with multiple databases being on the same server due to space issues.  So I decided it was time to take a review of the table spaces and see if there was some that I could easily do some quick maintenance to so that I could free up some space.

As always I found some guide examples out there using good old google.  The main item that I found that I could easily and quickly adjust was the undo tablespace.  Since  these scripts were for refreshing the environment and these environments are used by a very small subset of people, have a 32gb undo tablespace seemed a wee-bit excessive. So after the database clone is done I execute the following commands:

CREATE UNDO TABLESPACE undotbs2 DATAFILE ‘/u01/oracle/mynewdatabase/datafiles/undotbs02.dbf’ SIZE 2G AUTOEXTEND ON NEXT 250M;

After a stop and restart of the database 30 gigabytes of space are instantly reclaimed. So for 3 databases that is almost 100gb of dead space that is now free!

I also found a excellent bit of SQL for Oracle to show usage of the tablespace from Oracle-Base:Smart Panda - Idea

SELECT tablespace_name, size_mb, free_mb, max_size_mb, max_free_mb, TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (SELECT a.tablespace_name, b.size_mb, a.free_mb, b.max_size_mb,a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name, TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) ORDER BY tablespace_name;

For Temporary Tablespaces:

Maintenance on Temporary Tablespaces is a little easier than working with regular table spaces, you can use the following to easily shrink a temporary space:



ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/oracle/mynewdatabase/datafiles/temp01.dbf’ KEEP 40M;

If you don’t specify a KEEP option it while shrink to the smallest possible size it can.

For Regular Tablespaces

I am simply going to refer to guide I was using from Oracle-Base.  There are many ways to do maintenance on these tablespaces, and since I was looking for quick and simple ideas, the temporary and undo tablespace shrink was by far the easiest to do, but it is always a good strategy to do tablespace maintenance.