September 2014 - Page 2 of 2

Smart Panda - Database I have a little test site setup for Oracle PeopleSoft and I ran into an issue the other day where my sysadm account was expired. I typically have never ran across Oracle Database expiring my administrative user accounts. A little google search later and I found Amit Rath’s blog on fixing this problem. It turned out that I had a default profile that had a time limit of 180 days for the password.

SQL> select profile from dba_users;

All of my system administrative users had the profile: DEFAULT

SQL> select * from dba_profiles where profile=’DEFAULT’ and RESOURCE_NAME=’PASSWORD_LIFE_TIME’;

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

————————- —————————— ———————— ——————————

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

The password will expire every 180 days, so to change that to unlimited:

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL> select * from dba_profiles where profile=’PROFILE’ and RESOURCE_NAME=’PASSWORD_LIFE_TIME’;

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

————————- —————————— ———————— ——————————

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

I had to actually reset my users in order to get the account opened

SQL> ALTER USER sysadm IDENTIFIED BY PassW0rd;

SQL> ALTER USER people IDENTIFIED BY PassW0rd;

SQL>select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from dba_users;

Check that the account in OPEN and the EXPIRY_DATE is NULL:

USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE

—————————— ————————- —————— ————————-

SYSADM OPEN DEFAULT

people OPEN DEFAULT

This solved my issue. Not sure if I missed a script on the initial setup or if this is a new function within the Oracle Database but it will cause an issue with the access profile system user account is unable to log into the database.

Here is a nice clean way to clone an Oracle database.

STEP 1:

Generate a control file trace which will allow you to create a new database. On the current database server, go into SQL*Plus as sysdba: enter the command:

alter database backup controlfile to trace as ‘/filepath/filename.sql’;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 8
MAXLOGMEMBERS 4
MAXDATAFILES 1021
MAXINSTANCES 1
MAXLOGHISTORY 584
LOGFILE
GROUP 1 ‘/oracle/db/olddb/redo/redo01.log’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/oracle/db/olddb/redo/redo02.log’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/oracle/db/olddb/redo/redo03.log’ SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/oracle/db/olddb/data/system01.dbf’,
‘/oracle/db/olddb/data/sysaux01.dbf’,
‘/oracle/db/olddb/data/psundots01.dbf’,
‘/oracle/db/olddb/data/psdefault.dbf’,
‘/oracle/db/olddb/data/aaapp.dbf’,
…..All the PeopleSoft Datafiles will be listed here…….
‘/oracle/db/olddb/data/aalarge.dbf’,
‘/oracle/db/olddb/data/psimage2.dbf’
CHARACTER SET WE8ISO8859P15
;

STEP 2:

Shutdown the OLDDB database

STEP 3:

Copy all of the associated files with the database into a new directory or to a new server. Make sure you end up with the same structure as before just with the NEWDB name. So in my case I did: cp -rf /oracle/db/olddb /oracle/db/newdb, make sure you create the bdump, udump and cdump directories

STEP 4:

Using the script from Step 1, change the controlfile creation script:

Original: CREATE CONTROLFILE REUSE DATABASE “OLDDB” NORESETLOGS
Modified: CREATE CONTROLFILE SET DATABASE “NEWDB” RESETLOGS

STEP 4a:

Identify the TEMPFILE’s reuse statements in the script and make sure to keep them as part of the script (Should be at the end of the trace file)

STEP 5:

Remove any other instructions from the trace file and save it.

STEP 6:

Change the olddb references in the datafile and controlfile sections to the NEWDB location.

Original: DATAFILE ‘/oracle/db/olddb/data/system01.dbf’,
Modified: DATAFILE ‘/oracle/db/newdb/data/system01.dbf’,

STEP 6a:

Change the paths for the TEMPFILE to the new database location:

ALTER tablespace {TableSpaceName} add TEMPFILE ‘/oracle/db/{newdb}/data/psgtt01.dbf’ reuse;

STEP 7:

Save as newdb_create.sql.

STEP 8:

Copy the old initOLDDB.ora file to initNEWDB.ora
Modify the initNEWDB.ora and make the changes from the OLDDB to the NEWDB.

STEP 9:

Add entries in the tnsnames.ora, listener.ora and oratab for the NEWDB.

STEP 10:

Change to the NEWDB and STARTUP NOMOUNT and run the @newdb_create.sql

STEP 11:

ALTER DATABASE OPEN RESETLOGS;

STEP 12:

For a PeopleSoft Environment, you will want to update the PS.PSDBOWNER table to reflect the NEWDB.

Oracle Database Password Expiry

Oracle: How To Easily Clone a Database