Thursday, October 15, 2015

Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication



My beloved developer team is trying to connect MSSQL instance with third party tool ( unix platform) with domain user and they got below error.

DEV team need to use connect-string as per the authentication used for DB connection.


For DBA side we need to verify :1. Check windows user is create and valid permission to access the database.2. SQL instance should be in mixed mode.
References :http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
http://kristofmattei.be/2013/04/12/serverconnection-and-login-failed-for-user-reason-attempting-to-use-an-nt-account-name-with-sql-server-authentication/

Thursday, September 24, 2015

How to set Page_verify

Good day greeting to every one.

   In my company , auditing is going on for SQL server databases. They want to know that whether checksum is enable on page_verify.

Why this option will be used for databases?

Page_verify can be set for 3 options

1. CHECKSUM
2. TORN_PAGE_DETECTION
3. NONE

Dictionary meaning : a digit representing the sum of the correct digits in a piece of stored or transmitted digital data, against which later comparisons can be made to detect errors in the data.

CHECKSUM : When this option is enabled , SQL server database engine will calculate a checksum over the contents of whole page and store the values in page header. When a page written to disk or page is reading from disk than the checksum is recompute and compared to the checksum value that is stored in the page header.
By this it helps to provide a high level of data file integrity.


How to enable this Option:

Right Click on database --> choose option properties --> Click on options --> search for Page_Verify






  • Script to list All user databases and their page verify option set.

SELECT  name AS [Database] ,
CASE WHEN page_verify_option = 0 THEN 'NONE'
WHEN page_verify_option = 1 THEN 'TORN_PAGE_DETECTION'
WHEN page_verify_option = 2 THEN 'CHECKSUM'
END AS [Page Verify]
FROM    sys.databases
WHERE   database_id > 4


  • Change PAGE_VERIFY Setting to CHECKSUM for Database Using TSQL Query

Use Master
GO

SELECT
    'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;'
           AS [Change Page Verify Settings to Checksum]
FROM SYS.DATABASES
        WHERE STATE_DESC ='ONLINE' AND page_verify_option_desc != 'CHECKSUM'
GO


To be continued.....


References : http://www.sqlskills.com/


Friday, July 19, 2013

Load-Balancers with Oracle E-Business Suite Release 12


Using Load-Balancers with Oracle E-Business Suite Release 12 [ID 380489.1]


I am going to cover most common load balancer setup in Oracle Apps 11i i.e. Setup Two or more Middle tier with Web Server & Form Server with Hardware Load balancer in front of middle tier . This type of setup is also called as "HTTP Layer Hardware Load balancing" in Oracle Applications.
For overview & basics of Load balancing visit my previous post at

http://becomeappsdba.blogspot.com/2006/09/configure-load-balancer-in-oracle-apps.html

http://becomeappsdba.blogspot.com/2006/09/configure-hardware-load-balancer-at.html

Below are steps you need to follow to configure Multiple Middle Tier (Web & Forms Server ) with Hardware Load balancer in front.
1. Setup a Load balancer such that it forwards requests to first middle tier only.
2. Make sure sticky bit is set on load balancer for session persistence (Check Load balancer documentation), You can Network level switch as well to send requests to multiple middle tier
3. Apply Prereq. patches (3175852, 3077386, 3209878)
4. Edit following entry in XML or Context File directly ($APPL_TOP/admin/$CONTEXT_NAME.xml) or via Oracle Application Manager
-- 4.1 "Web entry point Host" to the HTTP load-balancer machine name
-- 4.2 "Web entry point Domain" to the HTTP load-balancer domain name
-- 4.3 "Web entry protocol" to the HTTP load-balancer protocol e.g. "http" or "https"
-- 4.4 "Active Web Port" to the value of the HTTP load-balancer's external port
-- 4.5 "Login Page" to include "Web entry protocol"://"Web Host entry point"."Web domain entry point":"Active Web Port"
-- 4.6 "disco_machine" to load balancer machine
5. Run Autoconfig
6. Start Services & check if you can login to applications via Load balancer
7. Now clone this middle tier to another node (where you want to add another node)
8. Make sure above parameter are pointing to load balancer in second node as well
9. Change load balancer to point to second node only
10. shutdown services on first node
11. try accessing application from second node (you should be able to access application without issues)
11.1 -- If forms are not opening properly , check "ICX Form Launcher .." Profile option should point to load balancer
12. Now start application on both middle tier
13. Point load balancer to both middle tier
14. You should be able to access applications
Things to Note :
------------------
Above solution is for HTTP Layer load balancing & HTTP Server will forward Forms request to form server running on same node .
Related Links
Metalink Note : 217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i





What is Load Balancer in Oracle Applications 11i ?
As name suggest its used to balance load on oracle applications, so lets say you initially sized (How to size server configuration in your apps 11i Instance during Implementation coming soon..) your server for 100 concurrent users & now additional 90 users started using your system , you can configure one more Application Tier node & distribute load of 190 users across two application tier node by configuring load balancer (Currently I am discussing only about Application Tier & once you are comfortable with Middle Tier I’ll discuss on database tier)
Why I need load balancer in Apps 11 ?
Is Increased load only reason for introducing load balancer in Apps 11i , No not at all . There are other reasons as well like (These are more advanced reasons & if you don’t understand don’t worry read it for understanding purpose at this minute)
- You want to Hide Server Name so instead of users accessing it viahttp://hostname:port you want users to access byhttp://somefancyname.com use load balancer (I know you can do this by various other ways as well like reverse proxy, via dns ..)
- You want on some node only web server & some both form server & web server
and there can lot of other reason why you need load balancer (May be you want to put it in your CV that you know how to configure load balancer, I know this is stupid reason :) )
What is Role of Load balancer in Oracle Application 11i ?
Usually load balancer act as mediator in passing your request to respective Middle Tiers (Usually web server ), so lets say you have configured load balancer in front of two middle tier which are part of your Applications 11i lets call them MT1 & MT2 . Load balancer will accept client’s request and pass on to MT1 & MT2 ( round robin or the way you have configured load balancer, you here is Unix Administrators)
What are different kind of load balancer ?
Session Persistent – These kind of load balancers assign request from a client to same Application Tier. so lets say user1 tried connecting applications via load balancer and LB sent that request to MT1 then in future LB will send requests from user1 always to MT1 for time being specified in session persistence duration parameter specified in LB configuration or till user logs out from Applications. This property is called as Stickness or sticky Bit
Non Session Persistent - These kind assign request to MT in round robin way so lets assume user1 requested for login page , LB sends this request to MT1 then user1 again clicked on user responsibility and this time LB sent request from user1 to second middle tier i.e. MT2
Other types of LB, I am not discussing here as these are not required at this minute
What all different kind of load balancing options available in Oracle 11i ?
DNS Level Load Balancing ( Check image at top , this represent DNS level LB)
HTTP Layer Load balancing (You need hardware load balancer which accepts http requests)
Jserv Load balancing (This is example of software load balancer, via mod_oprocmgr Process Manager Module)
Forms level load balancing – Jserv load balancing via forms servlets (More coming in future) , forms metrics server adfmsctl.sh

Wednesday, July 17, 2013

Role of APPLSYSPUB, GUEST, APPLSYS, APPS users

Role of APPLSYSPUB, GUEST, APPLSYS, APPS users:

Role of APPLSYSPUB user/schema in Oracle Applications:

When we login to applications,initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.
  • The public ORACLE username and password that grants access to the Oracle E-Business Suite initial sign-on form. The default is APPLSYSPUB/PUB.
  • Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.
  • In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.
When Autoconfig is not being used:
If you are not using Autoconfig you must manually edit the following configuration files :
1) FND_TOP/resource/appsweb.cfg
2) OA_HTML/bin/appsweb.cfg
3) FND_TOP/secure/HOSTNAME_DBNAME.dbc

To change password of APPLSYSPUB with FNDCPASS:$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] ORACLE APPLSYSPUB [new_passs].

0 & Y are flags for FNDCPASS
0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form)
'Y' indicates that this method is directly invoked from the command-line and not from the Submit Request Form.
  • All application tier processes (Apaches) must be restarted following the password change.

Role of GUEST user/schema in Oracle Applications:

  • GUEST is a dummy schema.
  • By default it has ORACLE as password.
  • GUEST/ORACLE password is present in DBC file at $FND_TOP/secure directory as well as at $FND_TOP/secure/SID_hostname directory.
  • If a user logs in without any role mappings, the user will get the Guest role, which has a default permission of "R".
  • GUEST user is used by JDBC Drivers and Oracle Self Service Web Applications like istore, irecruitment, iprocurement, ipayables, ireceivables etc to make initial Connection.

Role of APPLSYS & apps user/schema in Oracle Applications:

  • APPLSYS user is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc. In the same manner APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
  • Applsys schema has applications technology layer products like FND and AD etc.
  • Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).
  • APPS is central Schema which holds synonyms for all other Users Database Objects.

Note: APPLSYS and APPS should have same password.

Reason why these contains same password.

Both apps & applsys need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.
During signon process it uses both applsys and apps, hence this expects both the password to be identical. If the password for applsys & apps are not identical (Different) Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login.
Difference B/W APPLSYSPUB & GUEST:
  • APPLSYSPUB/PUB - is DB user which is used by any utility to retrieve APPS schema password for further logins.
  • GUEST/ORACLE - is EBS user with no or max limited privileges to execute authorization function.

Difference Between Oracle apps 11i & R12


Difference Between Oracle apps 11i & R12


Summary of Changes
Component
Release 11i
Release 12
Database
9.2
10.2
Developer
6i

10i
Application Server
1.0
10.1
Client Plug-in
Jinitiator
SUN JRE

Java Containers
Jserv

OC4J


Changes in Detail
APPMGR

OLD
NEW
APPL_TOP
$HOME/<SID>appl
$HOME/apps/apps_st/appl
COMMON_TOP
$HOME/<SID>comn
$HOME/apps/apps_st/comn
ORACLE_HOME
$HOME/<SID>ora/8.0.6
$HOME/apps/tech_st/10.1.2
IAS_ORACLE_HOME
$HOME/<SID>ora/iAS
$HOME/apps/tech_st/10.1.3
ORACLE

OLD
NEW
ORACLE_HOME
$HOME/<SID>db/10.2.0
$HOME/db/tech_st/10.2.0
ORADATA
$HOME/<SID>data
$HOME/db/apps_st/data
INST_TOP (New Addition)

OLD
NEW
INST_TOP
NA
$HOME/inst/apps/<context_name>

















What’s new in Oracle Apps R12

Key points to Note:
  1. Oracle Applications Release 12 is the latest release in the chain of E-Business Suite Releases by Oracle.
  2. This release came up with the new file system model
  3. Autoconfig will not write anything in APPL_TOP, COMMON_TOP area in R12.
  4. All instance specific configurations, log files are written in INST_TOP area. This home provides the ability to share Applications and technology stack.

R12 new features
  1. Applications Server 9i is replaced by 10g (10.1.3.X)
  2. Forms & Reports Version 6i (8.0.6) are replaced by Forms & Reports Version 10g i.e. 10.1.2.X
  3. mod_jserv is replaced by oc4j
  4. Java/ JDK version 1.3.X or 1.4.X will be replaced by JDK 1.5.X
Techstack Components Changes
  • Database (RDBMS_ORACLE_HOME) - 10.2.0.2
  • FORMS ORACLE_HOME (8.0.6 ORACLE HOME equivalence) - 10.1.2
  • OC4J_ORACLE_HOME (iAS ORACLE_HOME equivalence) - 10.1.3
File system level changes
  • A new top INSTANCE_TOP is introduced in Release 12 for configuration and log files along with the other TOP's in existing in 11i.
  • All instance specific configurations, log files are written in INST_TOP area. 
  • This home provides the ability to share Applications and technology stack.
What is INSTANCE TOP 
  • Instance home is the top-level directory for an applications instance which is known as Instance Home and is denoted the environment variable $INST_TOP. 
  • This contains all the config files, log files, SSL certificates etc.
Advantages of new INSTANCE HOME
  • The additional Instance Home makes the middle tier easier to manage and organized since the data is kept separate from the config files.
  • The Instance Home also has the ability to share the Applications and Technology stack code across multiple instances.
  • Another advantage of the Instance Home is that the autoconfig writes only in INST_TOP so APPL_TOP and ORACLE_HOME can also be made read only file system if required.

Thursday, July 11, 2013

Parallel Concurrent Processing on R12



Pre-Requisite : Make sure there are atleast two nodes for PCP Setup.

Autoconfig on all the middle tier must be run prior to the PCP Setup , resolve all autoconfig issues that are encountered.

Make sure tnsnames.ora file has both the FNDSM and FNDFS for all the concurrent manager nodes.

Restart the application listener on all the concurrent manager nodes.

Verify whether the Internal Monitors for the Concurrent Manager nodes are defined properly with the correct primary and secondary nodes.

Make sure the Executable and Workshifts are properly defined.

Activate the Internal Monitors for all the Concurrent Manager Nodes from the Administer Screen of the Concurrent Manager.

Query for Internal% in the Administration Screen and you should see the Internal Monitors for all the Concurrent Manager Nodes with Actual and Target as 1.

Make sure APPLCSF and APPLTMP are setup properly

Set Up Load Balancing on Concurrent Processing Nodes

* Edit the applications context file through Oracle Applications Manager interface and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to load balancing alias (_balance>)

* Execute AutoConfig by using $INST_TOP/admin/scripts/adautocfg.sh on all concurrent nodes.


Testing the PCP Setup :
Define a Custom Manager and point the primary node as the new node added ( node2 ) and allocate 3 process. From the Administer Concurrent Manager screen activate the manager and you should be seeing 3 FNDLIBR spawning on the Node2.
Now in the Specialization Rule tab Include "Active User" Program and Exclude it from the Standard Manager.

Submit an Active User and verify if it Completes Normal.

Wednesday, July 10, 2013

db Tier VS db Techstack



when we gave db techstack we have to recreate control file and recover the database...and open.
but in the case of dbtier,it will create control file and opens the datbase db..no need to recover the db as it was cold backup option.

adcfgclone.pl stages

appldev@pind31(DEV_806_BALANCE/CCM:Admin):$ perl adcfgclone.pl
USAGE:
adcfgclone.pl [context file]
WHERE:
: { appsTier | appltop | atTechStack }
: { dbTier | database | dbTechStack }
Depending on the context type.
NOTE:
if no context is specified, adcfgclone.pl will
prompt for cloned system target values to create
the new context file


1. perl adcfgclone.pl atTechStack

Only run cloning procedure on TechStack: that is 8.0.6 and iAS Oracle Home under $ORA_TOP.

2. perl adcfgclone.pl appltop

Only run cloning procedure on $APPL_TOP and $COMMON_TOP.

3. perl adcfgclone.pl appsTier

For all apps tier: $APPL_TOP, $COMMON_TOP and $ORA_TOP.

4. perl adcfglone.pl dbTechStack

Only run cloning procedure on RDBMS: database Oracle Home.

5. perl adcfgclone.pl database

Create control file, start database etc.

6. perl adcfgclone.pl dbTier
For all database tier procedures.