How to configure DB user accounts or passwords to never expires?

Most of the time we will encounter our database passwords used to expire after 180 days.

If we don’t have any security constraint then by using the below approach we can make them never expire.

Using profile, you can enforce a limit on resource utilization using resource limit parameters Also you can maintain database security by using password management feature.  Using the below query we can know ,which profile is used by each user account:

SQL> SELECT USERNAME, PROFILE FROM DBA_USERS;

sqlnoexpire.JPG

You can see all my SOA related user accounts are using “DEFAULT” profile.

To know all the details imposed on DEFAULT profile we have to use the below query.

SQL> select resource_name, limit from dba_profiles where profile=’DEFAULT’ and resource_type=’PASSWORD’;

RESOURCE_NAME                                            LIMIT
——————————–                           —————————————-
FAILED_LOGIN_ATTEMPTS                           10
PASSWORD_LIFE_TIME                                 180
PASSWORD_REUSE_TIME                             UNLIMITED
PASSWORD_REUSE_MAX                              UNLIMITED
PASSWORD_VERIFY_FUNCTION                 NULL
PASSWORD_LOCK_TIME                              1
PASSWORD_GRACE_TIME                            7

As shown above all our passwords will expire in 180 days. So for setting them to never expire we have use the below query.

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
Now we can verify the seeing by running the below query.
SQL> select resource_name, limit from dba_profiles where profile=’DEFAULT’ and resource_type=’PASSWORD’;
sqlnoexpire1

The sequence named [TEST_SEQ] is setup incorrectly. Its increment does not match its pre-allocation size.

If we have used Native sequencing in DB adapter and default increment size of  sequence does not match with DB adapter outbound connection pool then we will get the below error.

Error –>

The sequence named [TEST_SEQ] is setup incorrectly. Its increment does not match its pre-allocation size.

Solution –>

To resolve the issue we have to update the db adapter connection pool property “SequencePreAllocationSize” to sequence increment size, in our case it is 1.

Navigation –>

Navigate to Deployments->dbAdapter -> Click on Configuration’s Tab-> Click on your Outbound Connection Pools and then click on Connection Factory name -> Open Properties Tab

Change ‘sequencePreallocationSize’ property’s value to 1. Press ENTER to actually change the value. Click on Save button.

seqpreallocationsize

How to use Oracle Sequence in Database Adapter

In this post we will see how to use a oracle sequence in database adapter.

In some cases where you want to use the database adapter for inserts data in to a database table , you may want to use native sequencing to populate a primary key for some field.

I have created a below table and sequence in oracle database and i wanted use this table and sequence from SOA using database adapter.

CREATE TABLE TEST_SEQ_TAB (“ID” VARCHAR2(4000 BYTE) NOT NULL ENABLE,
“SOURCE” VARCHAR2(4000 BYTE) NOT NULL ENABLE,
“TARGET” VARCHAR2(4000 BYTE) NOT NULL ENABLE,
“REASON” CLOB,
“CREATE_TIME” TIMESTAMP (6) NOT NULL ENABLE,
“ATTRIBUTE1” VARCHAR2(250 BYTE),
“GUID” VARCHAR2(400 BYTE)
) ;

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1;

Now i have created a Database adapter to use this sequence.

For using this sequence we have to use “native sequencing” in DB adapter in primary key column.

testseq.JPG

You can see the primary key column in the table is linked with sequence in the mappings file .

seqprimarykey

testseqprimaryid.JPG

MFT Embedded servers are not starting in SOA suite 12.2..1.0.0

Issue –> Both the FTP and sFTP Embedded servers in Oracle MFT domain are not able to be started. When the start button is clicked, the embedded server status moves from Stopped to Failed.

Error –> 

[ERROR] [] [oracle.mft.COMMON] [tid: [ACTIVE].ExecuteThread: ’38’ for queue: ‘weblogic.kernel.Default (self-tuning)’] [userId: <anonymous>] [ecid: 0c70210eb-5a2e-471f-8132-94412facnjab-00000009,0:44] [APP: mft-app] [partition-name: DOMAIN] [tenant-name: GLOBAL] Embedded Servers initialization failed[[
MFTException [threadName=[ACTIVE] ExecuteThread: ’38’ for queue: ‘weblogic.kernel.Default (self-tuning)’, errorID=2185305d-2d6e-43ca-b745-d12f06cc7b5e, errorDesc=MFT-3601_Error while starting the hosted server {0} with root dir {1} and port {2}., cause=Exception creating DefaultSslConfiguration: Problem with Keystore creation. Keystore should not be null.]
at oracle.tip.mft.init.MFTServer.initEmbeddedServers(MFTServer.java:663)
at oracle.tip.mft.init.MFTServer.initialize(MFTServer.java:341)

Cause –> The Keystores were not configured for these servers. Oracle Managed File Transfer uses SSL and SSH keys for embedded server security and must be configured for FTPs and sFTP Embedded Servers.

Solution –> You will need to configure the SSL Keystore for your FTP server, and the SSH Keystore for your sFTP server.

After this is done, please restart your managed server and retry starting the embedded servers.

The steps to configure these have been copied below. Please note that you will need to connect to the MFT Host and Port with WLST for these commands to work.

Configuring the SSL Keystore

The default keystore is used for storing Oracle MFT SSL keys and certificates. To configure the default keystore, use WLST and the Oracle Managed File Transfer console.

The steps for this process are:

1. Start WLST 

2. Access the Oracle Platform Security Services key store service:

svc = getOpssService(name=’KeyStoreService’)
3. Create the SSL keystore:

svc.createKeyStore(appStripe='<StripeName>’, name='<StoreName>’, password='<StorePassword>’, permission=false/true)
4. Create the SSL keys:

svc.generateKeyPair(appStripe=’StripeName’, name=’StoreName’, password=’StorePassword’, dn=’cn=CompanyURL’, keysize=’1024′,alias=’Alias’, keypassword=’KeyPassword’)
For example:

svc.generateKeyPair(appStripe=’mft’, name=’mftDefaultStore’, password=’P@s$W0rd’, dn=’cn=www.mycompany.org’, keysize=’1024′,alias=’mftssl’, keypassword=’P@s$W0rd2′)
Specify mft as the stripe name and mftDefaultStore as the store name. Oracle Managed File Transfer uses these names by default. The store and key passwords are optional.

When securing the FTP server, you reference the SSL private key alias configured in this step. See the Certificate Alias description in FTPS (FTP Over SSL).

5. Exit WLST .

6. In the Oracle Managed File Transfer console, on the left pane of the Administration page, click Keystores.

7. If you specified key and store passwords in previous steps, you must enter them on this page. Enter the key password in the Private Key Password field and the store password in the Key Password field.

8. Click Save.

Configuring the SSL Keystore

The default keystore is used for storing Oracle MFT SSL keys and certificates. To configure the default keystore, use WLST and the Oracle Managed File Transfer console.

Configuring the SSH Keystore

To configure the SSH keystore, use WLST and the Oracle Managed File Transfer console.

The steps for this process are:

1. Start WLST.

2. Use the generateKeys WLST command to create a password-protected private SSH key. The key type is RSA and the key size is 1024 bits. For example:

generateKeys(‘SSH’, ‘P@s$W0rd’,’/export/ssh/ssh-pvt-keys.ppk’)
If you are an advanced user and want to set additional key parameters, you can use the ssh-keygen command instead. For example:

ssh-keygen -t rsa -b 2048 -f /export/ssh/ssh-pvt-keys.ppk -N P@a$W0rd

For more information about ssh-keygen, see ssh-keygen(1) – Linux man page.
The password is optional for either command.

3. Use the importCSFKey WLST command to import and create an alias for the key. For example:

importCSFKey(‘SSH’, ‘PRIVATE’, ‘mftssh’, ‘/export/ssh/ssh-pvt-keys.ppk’)

When securing the sFTP server, you reference the SSH private key alias configured in this step. See the Host Key Alias description in sFTP (SSH-FTP).
4. Exit WLST.

5. In the Oracle Managed File Transfer console, on the left pane of the Administration page, click Keystores.

6. If you specified a password in step 2, you must enter it in the SSH Private Key Password field.

7. Click Save.

MFT Notification Template Variable “TARGETNAME” is not coming in email

Issue–> If your familiar with using post transfer notification in MFT , then you might have faced this issue.

In Email template file if you add the target name and target endpoint reference, then you wont get those details in your Email and some times you will get below error.

Error–> Variable %%TARGETNAME%% is not getting resolved.

Solution –> For solving this issue , Oracle has provided a patch.

Patch # 24901957

MFT File rename function failing with, ‘Error occurred while moving or renaming a file after sending’ in SOA Suite 12.2.1.0.0

Issue –> When we are using rename function using SFTP remote , we will get the below error.

Error –> 

<Error> <oracle.soa.adapter.ftp> <BEA-000000> <Exception while setting up session
BINDING.JCA-11443
Adapter internal error.
Adapter internal error.
The adapter has become unstable. This could be because of incorrect parameters supplied to the adapter. The parameter: {0} had value: {1}
Please make sure that SFTP has been setup correctly.

at oracle.tip.adapter.ftp.SshImpl.SSHSessionImpl.setContext(SSHSessionImpl.java:1510)
at oracle.tip.adapter.ftp.SshImpl.SSHSessionImpl.setUpPasswordSocketConnection(SSHSessionImpl.java:268)
at oracle.tip.adapter.ftp.SshImpl.SSHSessionImpl.<init>(SSHSessionImpl.java:204)
at oracle.tip.adapter.ftp.SshImpl.SshImplFactory.getSshImpl(SshImplFactory.java:26)
at oracle.tip.adapter.ftp.SFTPManagedConnection.setupSftpConnection(SFTPManagedConnection.java:138)
at oracle.tip.adapter.ftp.SFTPManagedConnection.<init>(SFTPManagedConnection.java:63)
at oracle.tip.adapter.ftp.FTPManagedConnectionFactory.createManagedConnection(FTPManagedConnectionFactory.java:264)
at oracle.tip.adapter.ftp.FTPConnectionManager.allocateConnection(FTPConnectionManager.java:45)

Solution –>  For this oracle has provided the below patch.

Patch 22534593

1. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Retest the issue.
5. Migrate the solution as appropriate to other environments.