Forbidden Error When Accessing OracleAS Single Sign-On Administration
When trying to access the OracleAS Single Sign-On Administration page, users see the following error:
Forbidden. You don't have permission to access .../pls/orasso/orasso.home on this server.
$/home/oracle/orainfra/jdk/bin/java -jar /home/oracle/orainfra/sso/lib/ossoreg.jar -oracle_home_path /home/oracle/orainfra -site_name sys38.doyen.in:389 -config_mod_osso TRUE -mod_osso_url http://sys38.doyen.in:7777 -u oracle
[oracle@sys38 ~]$ /home/oracle/orainfra/jdk/bin/java -jar /home/oracle/orainfra/sso/lib/ossoreg.jar -oracle_home_path /home/oracle/orainfra -site_name sys38.doyen.in:389 -config_mod_osso TRUE -mod_osso_url http://sys38.doyen.in:7777 -u oracle
Parameters passed to SSO registration tool :
param0:-oracle_home_path param1:/home/oracle/orainfra param2:-site_name param3:sys38.doyen.in:389 param4:-config_mod_osso param5:TRUE param6:-mod_osso_url param7:http://sys38.doyen.in:7777 param8:-u param9:oracle
-DinstallType=
-DoldOracleHome=
-DoldOHSUser=root
Check /home/oracle/orainfra/sso/log/ssoreg.log for details of this registration
SSO registration tool finished successfully.
Tue Jan 08 11:44:47 IST 2008 =========================================
Tue Jan 08 11:44:47 IST 2008 OracleAS Single Sign-On Registration Tool
Tue Jan 08 11:44:47 IST 2008 =========================================
Tue Jan 08 11:44:47 IST 2008 Parameters passed to SSO registration tool :
param0:-oracle_home_path param1:/home/oracle/orainfra param2:-site_name param3:sys38.doyen.in:389 param4:-config_mod_osso par
am5:TRUE param6:-mod_osso_url param7:http://sys38.doyen.in:7777 param8:-u param9:oracle
Tue Jan 08 11:44:47 IST 2008 -DinstallType=
Tue Jan 08 11:44:47 IST 2008 -DoldOracleHome=
Tue Jan 08 11:44:47 IST 2008 -DoldOHSUser=root
Tue Jan 08 11:44:47 IST 2008 SSO database is jdbc:oracle:thin:@ldap://sys38.doyen.in:389/test,cn=oraclecontext
Tue Jan 08 11:44:48 IST 2008 Creating sso partner
Tue Jan 08 11:44:48 IST 2008 Mod_osso file /home/oracle/orainfra/Apache/Apache/conf/osso/osso.conf already exists.
Tue Jan 08 11:44:48 IST 2008 The existing partner application will be removed.
Tue Jan 08 11:44:49 IST 2008 Executing command: /home/oracle/orainfra/dcm/bin/dcmctl ssoPropagate -u oracle -f /home/oracle/
orainfra/Apache/Apache/conf/osso/osso.txt -out /home/oracle/orainfra/Apache/Apache/conf/osso/osso.conf -d -v
Tue Jan 08 11:44:55 IST 2008 dcmctl returned exit value 0
Tue Jan 08 11:44:55 IST 2008 SSO registration tool finished successfully.
A.1.6 Forbidden Error When Accessing OracleAS Single Sign-On Administration
When trying to access the OracleAS Single Sign-On Administration page, users see the following error:
Forbidden. You don't have permission to access .../pls/orasso/orasso.home on this server.
Problem
This message may appear when you try to access the single sign-on administration URL. Perhaps the password for the ORASSO schema was changed in the database, but not in the dads.conf file.
Solution
Perform these steps:
Update ORACLE_HOME/Apache/modplsql/conf/dads.conf.
Restart the Oracle HTTP Server:
ORACLE_HOME/opmn/bin/opmnctl restartproc process-type=HTTP_Server
If the schema password is correct to begin with, check the Oracle HTTP Server error log for error messages: ORACLE_HOME/Apache/Apache/logs/error_log.
https://metalink.oracle.com/metalink/plsql/f?p=130:10:7255648600606491996::::docId,knowledge,archive,bug,numHits:368806.1,TRUE,TRUE,TRUE,100
Processes Used by E-Business Suite
Process Name Description Script
tnslsnr Applications RPC Listener process adalnctl.sh
httpd Apache Web Server Listener adapcctl.sh
httpds
java
FNDLIBR Concurrent Manager adcmctl.sh
FNDSM
INVLIBR
oad Discoverer processes addisctl.sh
osagent
jre oracle.disco.locator.Locator
d2lc60 Forms Metrics Client adfmcctl.sh
d2ls60 Forms Metrics Server adfmsctl.sh
f60srvm Forms Server Listener process adfrmctl.sh
f60webmx
rwmts60 Reports Server adrepctl.sh
jre oracle.apps.fnd.tcf.ServerControl TCF SocketServer process adtcfctl.sh
Fulfillment Server process jtffmctl.sh
java oracle.apps.jtf.fm.engine.processor.Processor
java oracle.apps.jtf.fm.engine.remote.RemoteCommand
tnslsnr Applications RPC Listener process adalnctl.sh
httpd Apache Web Server Listener adapcctl.sh
httpds
java
FNDLIBR Concurrent Manager adcmctl.sh
FNDSM
INVLIBR
oad Discoverer processes addisctl.sh
osagent
jre oracle.disco.locator.Locator
d2lc60 Forms Metrics Client adfmcctl.sh
d2ls60 Forms Metrics Server adfmsctl.sh
f60srvm Forms Server Listener process adfrmctl.sh
f60webmx
rwmts60 Reports Server adrepctl.sh
jre oracle.apps.fnd.tcf.ServerControl TCF SocketServer process adtcfctl.sh
Fulfillment Server process jtffmctl.sh
java oracle.apps.jtf.fm.engine.processor.Processor
java oracle.apps.jtf.fm.engine.remote.RemoteCommand
OracleAS 10g with the Oracle E-Business Suite
Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
Purpose of Internet Directory instead of storing the information directly in an Oracle database
User identities and credentials
User profiles
User application preferences
Application authorization policies
Application-discovery information
Application- and service-specific management and configuration data, and
Network configuration and management policy data
What is Oracle Application Server?
Oracle Application Server provides the infrastructure to develop and deploy e-business portals, transactional applications, and Web services.
Oracle AS 10g includes Oracle Containers for J2EE (OC4J), Oracle HTTP Server (powered by Apache), Oracle JDeveloper, Oracle Forms, Oracle Identity Management, Oracle Portal, Oracle Business Intelligence, Oracle Business Integration, and Oracle WebCache.
Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i Doc ID: Note:233436.1
Integrate AS10g with Apps 11i.
Go to FND_TOP/bin and run below script. Its located in FND_TOP.
[applmgr@sys4 bin]$ txkrun.pl -script=SetSSOReg
Enter the host name where Oracle iAS Infrastructure database is installed ? sys38.doyen.in
Enter the Oracle iAS Infrastructure database port number ? 1521
Enter the Oracle iAS Infrastructure database SID ? test
Enter the LDAP Port on Oracle Internet Directory server ? 389
Enter Oracle E-Business apps database user password ? apps
Enter Oracle iAS Infrastructure database ORASSO schema password ? orasso
Enter Oracle E-Business SYSTEM database user password ? oracle
Enter E-Business Suite existing SSOSDK schema password or choose a password to use with the new SSOSDK schema if the schema does not exist ? SSOSDK
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? welcome99
Enter the instance password that you would like to register this application instance with ? welcome99
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /oracle/app/apps/viscomn/rgf/VIS_sys4/sso/txkSetSSOReg_Fri_Jan_4_16_00_37_2008.log
Program : /oracle/app/apps/visappl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl started @ Fri Jan 4 16:01:22 2008
*** Log File = /oracle/app/apps/viscomn/rgf/VIS_sys4/sso/txkSetSSOReg_Fri_Jan_4_16_00_37_2008.log
######################## WARNING ########################################
This application works with SSOSDK version 9.0.2 or higher. If lower version
(3.0.9) of SSOSDK was installed in your system and you have a registered
partner application, this process will remove the 3.0.9 version of the SSOSDK
schema and install the 9.0.2 version.
######################## WARNING ########################################
Beginning input parameter validation for SSO registration.
Beginning loading SSO SDK into database if necessary.
Loading of SSO SDK into database completed successfully.
Input parameters validation for OID registration completed.
BEGIN SSO REGISTRATION:
Beginning to register partner application.
Partner application has been registered successfully.
Single Sign-On partner application registered successfully.
BEGIN OID REGISTRATION:
Beginning to register Application and Service containers if necessary.
Application and Service containers were created successfully.
Beginning to register application in Oracle Internet Directory.
Registration of application in Oracle Internet Directory completed successfully.
Beginning to register instance password in Oracle Internet Directory.
Registration of instance password in Oracle Internet Directory completed successfully.
Beginning to test application registration in Oracle Internet Directory.
Testing of application registration in Oracle Internet Directory completed successfully.
Beginning to register provisioning profile in Oracle Internet Directory.
Registration of provisioning profile in Oracle Internet Directory completed successfully.
Application is now registered successfully with provisioning in Oracle Internet Directory.
End of /oracle/app/apps/visappl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered
Once register sucessfully you can proceed below.
a. do an anonymous bind...
ldapbind -h sys38.doyen.in -p 389
b. do a superuser bind....
ldapbind -h sys38.doyen.in -p 389 -D "cn=orcladmin" -w welcome99
c. do an encrypted super user bind...
ldapbind -h sys38.doyen.in -p 389 -U 1 -D cn=orcladmin -w welcome99
d. do a specific user bind....for example:
ldapbind -h sys38.doyen.in -p 389 -D "cn=orcladmin,cn=users,dc=doyen,dc=in" -w welcome99
Exporting Application Accounts into Intermediate LDIF File
Determine which accounts to migrate
Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) – An account will not be migrated if the user level profile value of the account is ‘LOCAL’, i.e. the account is a local account.
Applications SSO LDAP Synchronization (APPS_SSO_LDAP_SYNC) -- An account will not be migrated if the user level profile value of the account is ‘N’, i.e. the account is marked to not to synchronize with Oracle Internet Directory.
Note: The site level profile value will be used in the cases where the user level profile value is not set.
Oracle E-Business Suite ships a number of standard accounts, such as SYSADMIN and GUEST. These accounts should not be migrated. To enforce this, the SYSADMIN and GUEST accounts are pre-seeded with Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) set to ‘LOCAL’ and Applications SSO LDAP Synchronization (APPS_SSO_LDAP_SYNC) set to ‘N’.
Administrators should check whether there are any additional accounts that should not be migrated, especially accounts with user_id less than 10 (check with: select user_name from FND_USER where user_id<10).>Use AppsUserExport to extract user information
For example:
java oracle.apps.fnd.oid.AppsUserExport \
-v \
-dbc $FND_TOP/secure/myebiz.dbc \
-o users.txt \
-pwd welcome \
-g \
-l users.log
The resulting data file and log file may contain confidential information, such as the start and end dates for a user’s account, and should therefore be secured appropriately.
java oracle.apps.fnd.oid.AppsUserExport -dbc /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis.dbc -o /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out -pwd apps
User Export to /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out
User Export to /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out started..
User Export completed successfully. For further details please refer to log file at: /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out.log
[oracle@sys38 ~]$ $ORACLE_HOME/bin/ldapsearch -h sys38 -p 389 -D "cn=orcladmin" -w welcome99 -b "cn=Provisioning Profiles, cn=Changelog Subscriber, cn=Oracle Internet Directory" -s sub "objectclass=*" > profiles.ldif
[oracle@sys38 ~]$ $ORACLE_HOME/bin/ldapsearch -h sys38 -p 389 -D "cn=orcladmin" -w welcome99 -s sub "orclguid=42E42E799F8C50F5E040A8C0260051FA" -b "" dn
orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in
[oracle@sys38 ~]$ oidprovtool operation=disable ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in profile_mode=BOTH
This Provisioning Profile is disabled.
Converting Intermediate LDIF File to Final LDIF File
Before performing loading data into Oracle Internet Directory, the Oracle Internet Directory administrator needs to ensure that:
The extracted data file is copied from the Oracle E-Business Suite instance to Oracle Internet Directory.
If the provisioning profile has been set up for the Oracle E-Business Suite instance and the profile mode is either OUTBOUND or BOTH
To convert the intermediate LDIF file to the final LDIF format:
. Use oidprovtool with operation=DISABLE to disable the profile before the migration is started.
For example:
oidprovtool operation=disable ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in profile_mode=BOTH
After migration is complete, update the lastchangenumber attribute of the profile:
First, find the current last change number in Oracle Internet Directory with the ldapsearch command:
ldapsearch –h sys38.doyen.in -p welcome99 -D cn=oracladmin -w welcome99 -s base -b "" "objectclass=*" lastchangenumber=2623
Next, use the following command to update the lastchangenumber attribute to the number n that was discovered in the last step:
[oracle@sys38 bin]$ oidprovtool operation=MODIFY ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in orclLastAppliedChangeNumber=2623
The Provisioning Profile for the Application has been modified.
3. Use oidprovtool with operation=ENABLE to enable the profile.
[oracle@sys38 bin]$ oidprovtool operation=enable ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in profile_mode=BOTH
This Provisioning Profile is enabled.
4. The intermediate LDIF file created by AppsUserExport has two variables that an Oracle Internet Directory administrator needs to instantiate using the Oracle Internet Directory ldifmigrator utility:
[oracle@sys38 bin]$ ldifmigrator "input_file=/home/oracle/mig_user/vis01.out" "output_file=data.ldif" "s_UserContainerDN=cn=users,dc=doyen,dc=in" "s_UserNicknameAttribute=uid"
Migration of LDIF data completed. All the entries are successfully migrated
Loading Final LDIF file into Oracle Internet Directory
Once the final LDIF file has been generated, the user data is ready to be loaded into Oracle Internet Directory using the Oracle Internet Directory bulkload tool.
Before performing a bulk load:
1. Stop all OID processes before using the bulkload utility to load the LDIF file:
$ORACLE_HOME/opmn/bin/opmnctl stopall
Note the OID password, which should be the same as the instance and orcladmin passwords. You will be prompted for this when running the utility.
2. In case the oidmon or oidctl commands were used to manually start the oid proceses, you must make sure that they have stopped:
On Unix Run: $ORACLE_HOME/ldap/bin/ldapcheck
[oracle@sys38 bin]$ ./ldapcheck
Checking Oracle Internet Directory Processes ...ALL
Not Running ---- Process oidmon
Not Running ---- Process oidldapd
Not Running ---- Process oidrepld
Not Running ---- Process odisrv
No LDAP Processes are Running
3. If any other OID processes such as odisrv are still running, you must stop them manually using:
oidctl connect=test server=oidsrv instance=1 stop
You must ensure that no OID processes are running before continuing with the bulkload.sh command.
1. Run the bulkload utility with the -check and –generate options to verify that there are no duplicate users.
For example:
bulkload.sh –connect-check –generate
[oracle@sys38 bin]$ ./bulkload.sh -connect "test" -check -generate /home/oracle/orainfra/opmn/bin/data.ldif
Verifying node "test"
-----------------------------
This tool can only be executed if you know database user password
for OiD on test
Enter OiD password ::
------------------------------------------------------------------
Checking Internet Directory current schema state
------------------------------------------------------------------
-------------------------------------------------------------------
Checking and Generating Internet Directory data for bulk loading
-------------------------------------------------------------------
Schema Check Errors are logged in : /home/oracle/orainfra/ldap/log/bulkload.log
Bad Entries are logged in : /home/oracle/orainfra/ldap/load/badentry.ldif
Non-Unique Distinguished Names are logged in : /home/oracle/orainfra/ldap/log/duplicateDN.log
-------------------------------------------------------------------
Data Generated for bulk loading
-------------------------------------------------------------------
Please Remove the errors
[oracle@sys38 bin]$ ./bulkload.sh -connect "test" -check -generate /home/oracle/orainfra/opmn/bin/data.ldif
Verifying node "test"
-----------------------------
This tool can only be executed if you know database user password
for OiD on test
Enter OiD password ::
------------------------------------------------------------------
Checking Internet Directory current schema state
------------------------------------------------------------------
-------------------------------------------------------------------
Checking and Generating Internet Directory data for bulk loading
-------------------------------------------------------------------
No Schema Check Errors.
No Bad Entries found.
No Duplicate DN Entries.
-------------------------------------------------------------------
Data Generated for bulk loading
-------------------------------------------------------------------
2. Check the log file for duplicate users
If you get any Bad Entries and Duplicate DN Entries, Just remove those entries and run again setp 1.
3. If the log file indicates duplicate users, manually remove these users from the LDIF file.
4. Rerun Step 1 to verify all duplicates have been successfully removed.
5. Once all duplicates are removed, run the bulkload utility with the –load option to load the users.
For example:
bulkload.sh –connect–load
[oracle@sys38 bin]$ ./bulkload.sh -connect "test" -load /home/oracle/orainfra/opmn/bin/data.ldif
Verifying node "test"
-----------------------------
This tool can only be executed if you know database user password
for OiD on test
Enter OiD password ::
It is recommended to use -check option before generating/loading data
Do you want to continue (y/n?) [n]
y
Loading data on : "test"
===============================
------------------------------------------------------------------
Preparing Internet Directory schema for bulk data loading
------------------------------------------------------------------
------------------------------------------------------------------
Initiating bulk load...
------------------------------------------------------------------
Loading Attribute Search Catalogs..
battr_store001..
battr_store002..
battr_store003..
attr_store001..
attr_store002..
attr_store003..
c..
cn..
ctcaladmd..
ctcalcountry..
ctcalhost..
ctcalmobiletelephonetype..
ctcalorganization..
ctcalorgunit1..
ctcalorgunit2..
ctcalorgunit3..
ctcalorgunit4..
ctcalprmd..
ctcalpublishedtype..
ctcalresourcecapacity..
ctcalresourcenumber..
ctcalxitemid..
dc..
description..
employeenumber..
facsimiletelephonenumber..
generationqualifier..
givenname..
homephone..
initials..
krbprincipalname..
labeleduri..
l..
mail..
manager..
member..
middlename..
mobile..
name..
objectclass..
o..
orclaci..
orclcalendarhost..
orclcalendarresourcecapacity..
orclcalendarresourcenumber..
orclcalendarstore..
orclcertificatehash..
orclcertificatematch..
orclcommonkrbprincipalattribute..
orcldasispersonal..
orcldassearchable..
orcldasvalidatepwdreset..
orcldasviewable..
orcldbaqgeneric..
orcldbaqobjname..
orcldbaqobjowner..
orcldbaqobjtype..
orcldbaqpointerattr..
orcldbaqreglocation..
orcldbaqregnamespace..
orcldbaqregrejected..
orcldbaqregservers..
orcldbaqregsubscription..
orcldbaqregunreachable..
orcldbaqreguser..
orcldbdistinguishedname..
orcldbentroleassigned..
orcldbentuser..
orcldbglobalname..
orcldblscolumnname..
orcldblsfullname..
orcldblsgroupparent..
orcldblsnumerictag..
orcldbnativeuser..
orcldbroleoccupant..
orcldbservermember..
orcldbserverrole..
orcldbtargetuser..
orcldbtrusteddomain..
orcldbtrusteduser..
orclentrylevelaci..
orcleventtime..
orcleventtype..
orclglobalid..
orclguid..
orclhostname..
orclipaddress..
orclisenabled..
orclisvisible..
orcljazncodebase..
orcljaznjavaclass..
orcljaznpermissionactions..
orcljaznpermissiontarget..
orcljaznprincipal..
orcljaznrealmisexternal..
orcljaznsigner..
orcljaznsubscriberdn..
orclldapinstanceid..
orclldapprocessid..
orclmailaci..
orclmailaddresscategory..
orclmailemail..
orclmailfolderdn..
orclmailgroupadminmailid..
orclmailgrouphasarchive..
orclmailgroupisexternal..
orclmaillistsuspendedmember..
orclmailnewsfoldercreatetime..
orclmailnntpactivegroup..
orclmailnntplocalgroup..
orclmailnntpmoderatedgroup..
orclmailowner..
orclmailpublicdisplayname..
orclmailrenamedsharedfolder..
orclmailrulescope..
orclmailstore..
orclmailstoredn..
orclmailuserdldn..
orclnethostname..
orclnetservicename..
orclnewsgroupdn..
orclnormdn..
orclobjectguid..
orclodipagentname..
orclodipprofileexecgroupid..
orclodipprofilename..
orclownerguid..
orclrealmname..
orclreplicadn..
orclresourcename..
orclresourcetypename..
orclsamaccountname..
orclsecondaryuid..
orclservicemember..
orclservicesubtype..
orclservicetype..
orclstatus..
orclsuitetype..
orclumattendantextension..
orclumdeviceaddress..
orclumlocale..
orclumnamehash..
orcluserapplnprovstatus..
orcluserdn..
orclwirelessaccountnumber..
ou..
owner..
pager..
personalmobile..
personalpager..
postalcode..
pwdaccountlockedtime..
sn..
st..
street..
targetdn..
telephonenumber..
title..
uid..
uniquemember..
vpimmail..
x509issuer..
Loading Distinguished Name Catalog..
------------------------------------------------------------------
Performing post bulk load processing for Internet Directory schema
------------------------------------------------------------------
------------------------------------------------------------------
Creating Indexes ...
------------------------------------------------------------------
-------------------------------------------------------------
Verifying Index creation ...
-------------------------------------------------------------
All indexes created successfully
-------------------------------------------------------------
Index creation verification Done ...
-------------------------------------------------------------
-------------------------------------------------------------
Altering Index ...
-------------------------------------------------------------
-------------------------------------------------------------
Altering Index Done ...
-------------------------------------------------------------
------------------------------------------------------------------
Generating Database Statistics ...
------------------------------------------------------------------
-------------------------------------------------------------
Done.
-------------------------------------------------------------
Migrating Existing Accounts from Oracle Internet Directory to Oracle E-Business Suite Release 11i
The LDAPUserImport command-line utility takes an LDIF file generated from Oracle Internet Directory, and inserts appropriate data into the E-Business Suite schema. It can be used for bulk migration of existing accounts from Oracle Internet Directory to Oracle E-Business Suite. LDAPUserImport updates both FND and TCA schema.
Export Oracle Internet Directory users into LDIF file Using ldifwrite
The Oracle Internet Directory ldifWrite command-line utility is used to create an LDIF file that can be loaded into the E-Business Suite schema via the LDAPUserImport command-line utility.
General Syntax:
ldifwrite –c-b -f
Example:
ldifwrite -c test -b "cn=Users,dc=doyen,dc=in" -f output.ldif
[oracle@sys38 bin]$ ldifwrite -c test -b "cn=Users,dc=doyen,dc=in" -f output.ldif
This tool can only be executed if you know database user password for OiD
Enter OiD Password ::
------------------------------------------------------------
Reading entries under BaseDN "cn=users,dc=doyen,dc=in"...
-------------------------------------------------------------
------------------------------------------------------------
1872 Entries are written to "output.ldif".
------------------------------------------------------------
There is no need to modify the output file output.ldif in any way before proceeding with Task 2.
Task 2: Import LDAP Users into Oracle E-Business Suite using LDAPUserImport
The LDAPUserImport tool is invoked from the command line.
1. Ensure the environment is set up properly: $APPL_TOP/java must be in the CLASSPATH environment variable),
2. Invoke the LDAPUserImport tool with the following syntax:
java oracle.apps.fnd.oid.LDAPUserImport \
-v \
-dbc $FND_TOP/secure/myebiz.dbc \
-f users.ldif \
-n uid \
-l users.log
For example:
[applmgr@sys4 VIS_sys4]$ java oracle.apps.fnd.oid.LDAPUserImport -v -dbc /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis.dbc -f /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/output.ldif -n uid -l ouput.log
LDAP user import from /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/output.ldif started..
LDAP user import completed sucessfully. For further details refer to log file at ouput.log
PatchSet:
1. 11i.ATG_PF.H.delta.6 (RUP 5) or above.
2. 6117031 - 11i.ATG_PF.H RUP6 SSO 10g Integration,
Check and apply all the prerequisites.
Important Metalink Documents
186981.1 : Frequently Asked Questions
293849.1 : Roadmap: Using OracleAS 10g with the Oracle E-Business Suite (Overview Presentation)
233436.1 : OracleAS 10g + E-Business Suite Installation Guide
261914.1 : Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
305918.1: Portal 10g + E-Business Suite Installation Guide
313418.1: Discoverer 10g + E-Business Suite Installation Guide
306653.1: Web Cache 10g + E-Business Suite Installation Guide
223927.1 : Oracle Application Server Integration with Oracle E-Business Suite: Statement of Direction
Purpose of Internet Directory instead of storing the information directly in an Oracle database
User identities and credentials
User profiles
User application preferences
Application authorization policies
Application-discovery information
Application- and service-specific management and configuration data, and
Network configuration and management policy data
What is Oracle Application Server?
Oracle Application Server provides the infrastructure to develop and deploy e-business portals, transactional applications, and Web services.
Oracle AS 10g includes Oracle Containers for J2EE (OC4J), Oracle HTTP Server (powered by Apache), Oracle JDeveloper, Oracle Forms, Oracle Identity Management, Oracle Portal, Oracle Business Intelligence, Oracle Business Integration, and Oracle WebCache.
Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i Doc ID: Note:233436.1
Integrate AS10g with Apps 11i.
Go to FND_TOP/bin and run below script. Its located in FND_TOP.
[applmgr@sys4 bin]$ txkrun.pl -script=SetSSOReg
Enter the host name where Oracle iAS Infrastructure database is installed ? sys38.doyen.in
Enter the Oracle iAS Infrastructure database port number ? 1521
Enter the Oracle iAS Infrastructure database SID ? test
Enter the LDAP Port on Oracle Internet Directory server ? 389
Enter Oracle E-Business apps database user password ? apps
Enter Oracle iAS Infrastructure database ORASSO schema password ? orasso
Enter Oracle E-Business SYSTEM database user password ? oracle
Enter E-Business Suite existing SSOSDK schema password or choose a password to use with the new SSOSDK schema if the schema does not exist ? SSOSDK
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? welcome99
Enter the instance password that you would like to register this application instance with ? welcome99
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /oracle/app/apps/viscomn/rgf/VIS_sys4/sso/txkSetSSOReg_Fri_Jan_4_16_00_37_2008.log
Program : /oracle/app/apps/visappl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl started @ Fri Jan 4 16:01:22 2008
*** Log File = /oracle/app/apps/viscomn/rgf/VIS_sys4/sso/txkSetSSOReg_Fri_Jan_4_16_00_37_2008.log
######################## WARNING ########################################
This application works with SSOSDK version 9.0.2 or higher. If lower version
(3.0.9) of SSOSDK was installed in your system and you have a registered
partner application, this process will remove the 3.0.9 version of the SSOSDK
schema and install the 9.0.2 version.
######################## WARNING ########################################
Beginning input parameter validation for SSO registration.
Beginning loading SSO SDK into database if necessary.
Loading of SSO SDK into database completed successfully.
Input parameters validation for OID registration completed.
BEGIN SSO REGISTRATION:
Beginning to register partner application.
Partner application has been registered successfully.
Single Sign-On partner application registered successfully.
BEGIN OID REGISTRATION:
Beginning to register Application and Service containers if necessary.
Application and Service containers were created successfully.
Beginning to register application in Oracle Internet Directory.
Registration of application in Oracle Internet Directory completed successfully.
Beginning to register instance password in Oracle Internet Directory.
Registration of instance password in Oracle Internet Directory completed successfully.
Beginning to test application registration in Oracle Internet Directory.
Testing of application registration in Oracle Internet Directory completed successfully.
Beginning to register provisioning profile in Oracle Internet Directory.
Registration of provisioning profile in Oracle Internet Directory completed successfully.
Application is now registered successfully with provisioning in Oracle Internet Directory.
End of /oracle/app/apps/visappl/fnd/11.5.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered
Once register sucessfully you can proceed below.
a. do an anonymous bind...
ldapbind -h sys38.doyen.in -p 389
b. do a superuser bind....
ldapbind -h sys38.doyen.in -p 389 -D "cn=orcladmin" -w welcome99
c. do an encrypted super user bind...
ldapbind -h sys38.doyen.in -p 389 -U 1 -D cn=orcladmin -w welcome99
d. do a specific user bind....for example:
ldapbind -h sys38.doyen.in -p 389 -D "cn=orcladmin,cn=users,dc=doyen,dc=in" -w welcome99
Exporting Application Accounts into Intermediate LDIF File
Determine which accounts to migrate
Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) – An account will not be migrated if the user level profile value of the account is ‘LOCAL’, i.e. the account is a local account.
Applications SSO LDAP Synchronization (APPS_SSO_LDAP_SYNC) -- An account will not be migrated if the user level profile value of the account is ‘N’, i.e. the account is marked to not to synchronize with Oracle Internet Directory.
Note: The site level profile value will be used in the cases where the user level profile value is not set.
Oracle E-Business Suite ships a number of standard accounts, such as SYSADMIN and GUEST. These accounts should not be migrated. To enforce this, the SYSADMIN and GUEST accounts are pre-seeded with Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) set to ‘LOCAL’ and Applications SSO LDAP Synchronization (APPS_SSO_LDAP_SYNC) set to ‘N’.
Administrators should check whether there are any additional accounts that should not be migrated, especially accounts with user_id less than 10 (check with: select user_name from FND_USER where user_id<10).>Use AppsUserExport to extract user information
For example:
java oracle.apps.fnd.oid.AppsUserExport \
-v \
-dbc $FND_TOP/secure/myebiz.dbc \
-o users.txt \
-pwd welcome \
-g \
-l users.log
The resulting data file and log file may contain confidential information, such as the start and end dates for a user’s account, and should therefore be secured appropriately.
java oracle.apps.fnd.oid.AppsUserExport -dbc /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis.dbc -o /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out -pwd apps
User Export to /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out
User Export to /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out started..
User Export completed successfully. For further details please refer to log file at: /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis01.out.log
[oracle@sys38 ~]$ $ORACLE_HOME/bin/ldapsearch -h sys38 -p 389 -D "cn=orcladmin" -w welcome99 -b "cn=Provisioning Profiles, cn=Changelog Subscriber, cn=Oracle Internet Directory" -s sub "objectclass=*" > profiles.ldif
[oracle@sys38 ~]$ $ORACLE_HOME/bin/ldapsearch -h sys38 -p 389 -D "cn=orcladmin" -w welcome99 -s sub "orclguid=42E42E799F8C50F5E040A8C0260051FA" -b "" dn
orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in
[oracle@sys38 ~]$ oidprovtool operation=disable ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in profile_mode=BOTH
This Provisioning Profile is disabled.
Converting Intermediate LDIF File to Final LDIF File
Before performing loading data into Oracle Internet Directory, the Oracle Internet Directory administrator needs to ensure that:
The extracted data file is copied from the Oracle E-Business Suite instance to Oracle Internet Directory.
If the provisioning profile has been set up for the Oracle E-Business Suite instance and the profile mode is either OUTBOUND or BOTH
To convert the intermediate LDIF file to the final LDIF format:
. Use oidprovtool with operation=DISABLE to disable the profile before the migration is started.
For example:
oidprovtool operation=disable ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in profile_mode=BOTH
After migration is complete, update the lastchangenumber attribute of the profile:
First, find the current last change number in Oracle Internet Directory with the ldapsearch command:
ldapsearch –h sys38.doyen.in -p welcome99 -D cn=oracladmin -w welcome99 -s base -b "" "objectclass=*" lastchangenumber=2623
Next, use the following command to update the lastchangenumber attribute to the number n that was discovered in the last step:
[oracle@sys38 bin]$ oidprovtool operation=MODIFY ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in orclLastAppliedChangeNumber=2623
The Provisioning Profile for the Application has been modified.
3. Use oidprovtool with operation=ENABLE to enable the profile.
[oracle@sys38 bin]$ oidprovtool operation=enable ldap_host=sys38.doyen.in ldap_port=389 ldap_user_dn=cn=orcladmin ldap_user_password=welcome99 application_dn=orclApplicationCommonName=VIS,cn=EBusiness,cn=Products,cn=OracleContext,dc=doyen,dc=in profile_mode=BOTH
This Provisioning Profile is enabled.
4. The intermediate LDIF file created by AppsUserExport has two variables that an Oracle Internet Directory administrator needs to instantiate using the Oracle Internet Directory ldifmigrator utility:
[oracle@sys38 bin]$ ldifmigrator "input_file=/home/oracle/mig_user/vis01.out" "output_file=data.ldif" "s_UserContainerDN=cn=users,dc=doyen,dc=in" "s_UserNicknameAttribute=uid"
Migration of LDIF data completed. All the entries are successfully migrated
Loading Final LDIF file into Oracle Internet Directory
Once the final LDIF file has been generated, the user data is ready to be loaded into Oracle Internet Directory using the Oracle Internet Directory bulkload tool.
Before performing a bulk load:
1. Stop all OID processes before using the bulkload utility to load the LDIF file:
$ORACLE_HOME/opmn/bin/opmnctl stopall
Note the OID password, which should be the same as the instance and orcladmin passwords. You will be prompted for this when running the utility.
2. In case the oidmon or oidctl commands were used to manually start the oid proceses, you must make sure that they have stopped:
On Unix Run: $ORACLE_HOME/ldap/bin/ldapcheck
[oracle@sys38 bin]$ ./ldapcheck
Checking Oracle Internet Directory Processes ...ALL
Not Running ---- Process oidmon
Not Running ---- Process oidldapd
Not Running ---- Process oidrepld
Not Running ---- Process odisrv
No LDAP Processes are Running
3. If any other OID processes such as odisrv are still running, you must stop them manually using:
oidctl connect=test server=oidsrv instance=1 stop
You must ensure that no OID processes are running before continuing with the bulkload.sh command.
1. Run the bulkload utility with the -check and –generate options to verify that there are no duplicate users.
For example:
bulkload.sh –connect
[oracle@sys38 bin]$ ./bulkload.sh -connect "test" -check -generate /home/oracle/orainfra/opmn/bin/data.ldif
Verifying node "test"
-----------------------------
This tool can only be executed if you know database user password
for OiD on test
Enter OiD password ::
------------------------------------------------------------------
Checking Internet Directory current schema state
------------------------------------------------------------------
-------------------------------------------------------------------
Checking and Generating Internet Directory data for bulk loading
-------------------------------------------------------------------
Schema Check Errors are logged in : /home/oracle/orainfra/ldap/log/bulkload.log
Bad Entries are logged in : /home/oracle/orainfra/ldap/load/badentry.ldif
Non-Unique Distinguished Names are logged in : /home/oracle/orainfra/ldap/log/duplicateDN.log
-------------------------------------------------------------------
Data Generated for bulk loading
-------------------------------------------------------------------
Please Remove the errors
[oracle@sys38 bin]$ ./bulkload.sh -connect "test" -check -generate /home/oracle/orainfra/opmn/bin/data.ldif
Verifying node "test"
-----------------------------
This tool can only be executed if you know database user password
for OiD on test
Enter OiD password ::
------------------------------------------------------------------
Checking Internet Directory current schema state
------------------------------------------------------------------
-------------------------------------------------------------------
Checking and Generating Internet Directory data for bulk loading
-------------------------------------------------------------------
No Schema Check Errors.
No Bad Entries found.
No Duplicate DN Entries.
-------------------------------------------------------------------
Data Generated for bulk loading
-------------------------------------------------------------------
2. Check the log file for duplicate users
If you get any Bad Entries and Duplicate DN Entries, Just remove those entries and run again setp 1.
3. If the log file indicates duplicate users, manually remove these users from the LDIF file.
4. Rerun Step 1 to verify all duplicates have been successfully removed.
5. Once all duplicates are removed, run the bulkload utility with the –load option to load the users.
For example:
bulkload.sh –connect
[oracle@sys38 bin]$ ./bulkload.sh -connect "test" -load /home/oracle/orainfra/opmn/bin/data.ldif
Verifying node "test"
-----------------------------
This tool can only be executed if you know database user password
for OiD on test
Enter OiD password ::
It is recommended to use -check option before generating/loading data
Do you want to continue (y/n?) [n]
y
Loading data on : "test"
===============================
------------------------------------------------------------------
Preparing Internet Directory schema for bulk data loading
------------------------------------------------------------------
------------------------------------------------------------------
Initiating bulk load...
------------------------------------------------------------------
Loading Attribute Search Catalogs..
battr_store001..
battr_store002..
battr_store003..
attr_store001..
attr_store002..
attr_store003..
c..
cn..
ctcaladmd..
ctcalcountry..
ctcalhost..
ctcalmobiletelephonetype..
ctcalorganization..
ctcalorgunit1..
ctcalorgunit2..
ctcalorgunit3..
ctcalorgunit4..
ctcalprmd..
ctcalpublishedtype..
ctcalresourcecapacity..
ctcalresourcenumber..
ctcalxitemid..
dc..
description..
employeenumber..
facsimiletelephonenumber..
generationqualifier..
givenname..
homephone..
initials..
krbprincipalname..
labeleduri..
l..
mail..
manager..
member..
middlename..
mobile..
name..
objectclass..
o..
orclaci..
orclcalendarhost..
orclcalendarresourcecapacity..
orclcalendarresourcenumber..
orclcalendarstore..
orclcertificatehash..
orclcertificatematch..
orclcommonkrbprincipalattribute..
orcldasispersonal..
orcldassearchable..
orcldasvalidatepwdreset..
orcldasviewable..
orcldbaqgeneric..
orcldbaqobjname..
orcldbaqobjowner..
orcldbaqobjtype..
orcldbaqpointerattr..
orcldbaqreglocation..
orcldbaqregnamespace..
orcldbaqregrejected..
orcldbaqregservers..
orcldbaqregsubscription..
orcldbaqregunreachable..
orcldbaqreguser..
orcldbdistinguishedname..
orcldbentroleassigned..
orcldbentuser..
orcldbglobalname..
orcldblscolumnname..
orcldblsfullname..
orcldblsgroupparent..
orcldblsnumerictag..
orcldbnativeuser..
orcldbroleoccupant..
orcldbservermember..
orcldbserverrole..
orcldbtargetuser..
orcldbtrusteddomain..
orcldbtrusteduser..
orclentrylevelaci..
orcleventtime..
orcleventtype..
orclglobalid..
orclguid..
orclhostname..
orclipaddress..
orclisenabled..
orclisvisible..
orcljazncodebase..
orcljaznjavaclass..
orcljaznpermissionactions..
orcljaznpermissiontarget..
orcljaznprincipal..
orcljaznrealmisexternal..
orcljaznsigner..
orcljaznsubscriberdn..
orclldapinstanceid..
orclldapprocessid..
orclmailaci..
orclmailaddresscategory..
orclmailemail..
orclmailfolderdn..
orclmailgroupadminmailid..
orclmailgrouphasarchive..
orclmailgroupisexternal..
orclmaillistsuspendedmember..
orclmailnewsfoldercreatetime..
orclmailnntpactivegroup..
orclmailnntplocalgroup..
orclmailnntpmoderatedgroup..
orclmailowner..
orclmailpublicdisplayname..
orclmailrenamedsharedfolder..
orclmailrulescope..
orclmailstore..
orclmailstoredn..
orclmailuserdldn..
orclnethostname..
orclnetservicename..
orclnewsgroupdn..
orclnormdn..
orclobjectguid..
orclodipagentname..
orclodipprofileexecgroupid..
orclodipprofilename..
orclownerguid..
orclrealmname..
orclreplicadn..
orclresourcename..
orclresourcetypename..
orclsamaccountname..
orclsecondaryuid..
orclservicemember..
orclservicesubtype..
orclservicetype..
orclstatus..
orclsuitetype..
orclumattendantextension..
orclumdeviceaddress..
orclumlocale..
orclumnamehash..
orcluserapplnprovstatus..
orcluserdn..
orclwirelessaccountnumber..
ou..
owner..
pager..
personalmobile..
personalpager..
postalcode..
pwdaccountlockedtime..
sn..
st..
street..
targetdn..
telephonenumber..
title..
uid..
uniquemember..
vpimmail..
x509issuer..
Loading Distinguished Name Catalog..
------------------------------------------------------------------
Performing post bulk load processing for Internet Directory schema
------------------------------------------------------------------
------------------------------------------------------------------
Creating Indexes ...
------------------------------------------------------------------
-------------------------------------------------------------
Verifying Index creation ...
-------------------------------------------------------------
All indexes created successfully
-------------------------------------------------------------
Index creation verification Done ...
-------------------------------------------------------------
-------------------------------------------------------------
Altering Index ...
-------------------------------------------------------------
-------------------------------------------------------------
Altering Index Done ...
-------------------------------------------------------------
------------------------------------------------------------------
Generating Database Statistics ...
------------------------------------------------------------------
-------------------------------------------------------------
Done.
-------------------------------------------------------------
Migrating Existing Accounts from Oracle Internet Directory to Oracle E-Business Suite Release 11i
The LDAPUserImport command-line utility takes an LDIF file generated from Oracle Internet Directory, and inserts appropriate data into the E-Business Suite schema. It can be used for bulk migration of existing accounts from Oracle Internet Directory to Oracle E-Business Suite. LDAPUserImport updates both FND and TCA schema.
Export Oracle Internet Directory users into LDIF file Using ldifwrite
The Oracle Internet Directory ldifWrite command-line utility is used to create an LDIF file that can be loaded into the E-Business Suite schema via the LDAPUserImport command-line utility.
General Syntax:
ldifwrite –c
Example:
ldifwrite -c test -b "cn=Users,dc=doyen,dc=in" -f output.ldif
[oracle@sys38 bin]$ ldifwrite -c test -b "cn=Users,dc=doyen,dc=in" -f output.ldif
This tool can only be executed if you know database user password for OiD
Enter OiD Password ::
------------------------------------------------------------
Reading entries under BaseDN "cn=users,dc=doyen,dc=in"...
-------------------------------------------------------------
------------------------------------------------------------
1872 Entries are written to "output.ldif".
------------------------------------------------------------
There is no need to modify the output file output.ldif in any way before proceeding with Task 2.
Task 2: Import LDAP Users into Oracle E-Business Suite using LDAPUserImport
The LDAPUserImport tool is invoked from the command line.
1. Ensure the environment is set up properly: $APPL_TOP/java must be in the CLASSPATH environment variable),
2. Invoke the LDAPUserImport tool with the following syntax:
java oracle.apps.fnd.oid.LDAPUserImport \
-v \
-dbc $FND_TOP/secure/myebiz.dbc \
-f users.ldif \
-n uid \
-l users.log
For example:
[applmgr@sys4 VIS_sys4]$ java oracle.apps.fnd.oid.LDAPUserImport -v -dbc /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/vis.dbc -f /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/output.ldif -n uid -l ouput.log
LDAP user import from /oracle/app/apps/visappl/fnd/11.5.0/secure/VIS_sys4/output.ldif started..
LDAP user import completed sucessfully. For further details refer to log file at ouput.log
PatchSet:
1. 11i.ATG_PF.H.delta.6 (RUP 5) or above.
2. 6117031 - 11i.ATG_PF.H RUP6 SSO 10g Integration,
Check and apply all the prerequisites.
Important Metalink Documents
186981.1 : Frequently Asked Questions
293849.1 : Roadmap: Using OracleAS 10g with the Oracle E-Business Suite (Overview Presentation)
233436.1 : OracleAS 10g + E-Business Suite Installation Guide
261914.1 : Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
305918.1: Portal 10g + E-Business Suite Installation Guide
313418.1: Discoverer 10g + E-Business Suite Installation Guide
306653.1: Web Cache 10g + E-Business Suite Installation Guide
223927.1 : Oracle Application Server Integration with Oracle E-Business Suite: Statement of Direction
Create StatsPack and How does one use it
SQL> connect sys as sysdba
CREATE TABLESPACE
CREATE TABLESPACE perfstat
DATAFILE ‘/oracle/app/oracle/visdata/perfstat01.dbf’ SIZE 500m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k;
Drop Existing statspack
sqlplus "/ as sysdba" @spdrop.sql
Create New Statspack
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
SQL> @spcreate.sql
TABLESPACE_NAME CONTENTS
------------------------------ ---------
ODM PERMANENT
OLAP PERMANENT
OWAPUB PERMANENT
PERFSTAT PERMANENT
PORTAL PERMANENT
SYNCSERVER PERMANENT
TEMP TEMPORARY
TEST PERMANENT
XYZ PERMANENT
20 rows selected.
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: PERFSTAT
Using PERFSTAT for the default tablespace
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: TEMP
Use Statspack:
[oracle@sys4 admin]$ sqlplus perfstat/perfstat
Take a performance snapshots
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
SNAP_ID SNAP_TIME
---------- ---------
1 04-JAN-08
2 04-JAN-08
Enter two snapshot id's for difference report
SQL> @spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
190608494 VIS 1 VIS
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
190608494 1 VIS VIS sys4.doyen.i
n
Using 190608494 for database Id
Using 1 for instance number
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
VIS VIS 1 04 Jan 2008 12:31 5
2 04 Jan 2008 12:35 5
Enter Snapshot ID
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: report_0408
Staspack output
-rw-r--r-- 1 oracle dba 77294 Jan 4 12:37 report_0408.lst
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
CREATE TABLESPACE
CREATE TABLESPACE perfstat
DATAFILE ‘/oracle/app/oracle/visdata/perfstat01.dbf’ SIZE 500m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k;
Drop Existing statspack
sqlplus "/ as sysdba" @spdrop.sql
Create New Statspack
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
SQL> @spcreate.sql
TABLESPACE_NAME CONTENTS
------------------------------ ---------
ODM PERMANENT
OLAP PERMANENT
OWAPUB PERMANENT
PERFSTAT PERMANENT
PORTAL PERMANENT
SYNCSERVER PERMANENT
TEMP TEMPORARY
TEST PERMANENT
XYZ PERMANENT
20 rows selected.
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: PERFSTAT
Using PERFSTAT for the default tablespace
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: TEMP
Use Statspack:
[oracle@sys4 admin]$ sqlplus perfstat/perfstat
Take a performance snapshots
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
SNAP_ID SNAP_TIME
---------- ---------
1 04-JAN-08
2 04-JAN-08
Enter two snapshot id's for difference report
SQL> @spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
190608494 VIS 1 VIS
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
190608494 1 VIS VIS sys4.doyen.i
n
Using 190608494 for database Id
Using 1 for instance number
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
VIS VIS 1 04 Jan 2008 12:31 5
2 04 Jan 2008 12:35 5
Enter Snapshot ID
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press
Enter value for report_name: report_0408
Staspack output
-rw-r--r-- 1 oracle dba 77294 Jan 4 12:37 report_0408.lst
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
How to enable trace in Oracle
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
sql_trace = TRUE
to disable trace:
sql_trace = FALSE
- or -
to enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
to stop trace run:
SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;
2. Enable trace at session level
to start trace:
ALTER SESSION SET sql_trace = TRUE;
to stop trace:
ALTER SESSION SET sql_trace = FALSE;
- or -
EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);
- or -
EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;
3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
SELECT * FROM v$session WHERE osuser = OSUSER;
to start trace:
EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);
to stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);
- or -
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);
SQL> select sid, serial# from v$session where username = 'USER';
SQL> alter system kill session 'SID,SERIAL#';
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
sql_trace = TRUE
to disable trace:
sql_trace = FALSE
- or -
to enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
to stop trace run:
SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;
2. Enable trace at session level
to start trace:
ALTER SESSION SET sql_trace = TRUE;
to stop trace:
ALTER SESSION SET sql_trace = FALSE;
- or -
EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);
- or -
EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;
3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
SELECT * FROM v$session WHERE osuser = OSUSER;
to start trace:
EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);
to stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);
- or -
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);
SQL> select sid, serial# from v$session where username = 'USER';
SQL> alter system kill session 'SID,SERIAL#';
Backup & Recovery with RMAN
Agenda
• Types of failures and backups in Oracle
• RMAN Architecture
• Manual vs. RMAN backups
• On-tape backups with RMAN
• RMAN Configuration
• RMAN backup strategies
• RMAN backups Syntax
• Exemplary recovery scanarios
Types of failures
• Instance Failure
– Usually connected with an Oracle process failure
• Media Failure
– Disk failure, storage array controller failure etc.
• Human error
– In most cases accidentally deleted/updated data
– Database user or DBA
• Disaster
– Fire, flood, earthquake, plane crash etc.
Backup options in Oracle
• Physical backups
– Cold (off-line) backups
• Full database only
• Require downtime
– Hot (on-line) backups
• Different types of backups: full, incr. (cumulative, differential), archivelogs
• cumulative backup: which backs up all blocks changed after the most recent incremental backup at level 0
• differential backup: which backs up all blocks changed after the most recent incremental backup at level 1 or 0
• Different scopes: full database, tablespace(s) or datafile(s)
• Do not require database downtime
• Can be used to recover full database, single/multiple tablespace(s)/datafile(s) or a corrupted block
• Database can be recovered to any point in time within assumed backup retention period
RMAN architecture
Types of RMAN hot backups
• Copy or backupset
• Full database backup
• Incremental backups (in 10g 2 levels available: 0 and 1)
– Cumulative, differential
• Archivelog backups
• Tablespace(s), datafile(s) backups
Manual vs. RMAN backups
• RMAN advantages:
– Supports incremental backup strategies
– RMAN on-line backups are not so heavy for the system as manual on-line backups
– RMAN can detect corrupted blocks
– RMAN automatically track database structure changes
– Provides easy, automated backup, restore and recovery operations
– Keeps invenotory of taken backups
– Can seamlessly work with third party media managers
• Disadvantage: something new to learn
– RMAN concepts and command syntax sometimes are not intuitive
On-tape backups with RMAN
• RMAN allows to take on-disk backups out of the box
– Flash recovery area, if configured, further simplifies such backups
– On disk backups are interesting but usually not sufficient for a disaster recovery
• On-disk backups can be manually sent to tapes
– Recovery can be very troublesome
• RMAN can seamlessly work with third party Media Managers
– Media Manager Library (MML) is required
– Different configuration tasks for different MMLs
• Many vendors of Media Management software provide MMLs
• Most popular are:
– Tivoli Storage Manager
– Veritas NetBackup
RMAN Configuration
• RMAN can be preconfigured
– Configuration is stored in the control file and in the recovery catalog (if used)
– Can facilitate backup automation
• Most useful settings:
RMAN Configuration
• Example:
configure RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
configure DEFAULT DEVICE TYPE TO 'sbt';
configure DEVICE TYPE 'sbt' PARALLELISM 2;
configure CHANNEL DEVICE TYPE ‘sbt’ parms='ENV=
(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)';
configure DEVICE TYPE DISK PARALLELISM 2;
configure MAXSETSIZE TO 200 G;
configure archivelog backup copies for device type 'sbt' to 1;
configure controlfile autobackup on;
• The SHOW ALL command lists all RMAN configuration settings
• To clear a given settings append CLEAR at the end of the CONFIGURE command
RMAN backup strategies
• RMAN allows many types of backups
• It possible to build own backup strategy that suits given database best
• Both Oracle-recommended strategies implemented for all production systems
• Incremental backup strategy:
– Backups go to tapes
– Weekly or biweekly level 0 backups (depending on the DB size)
– A level 1 cumulative backup inbetween
– Daily incremental level 1 differential backups
– Archivelog backup every 30 minutes
• Incrementally updated DB copy strategy:
– daily incremental differential backups applied with 2 days of delay
– Copies, incremental backups and archived redo logs stored in the Flash Recovery Area
Backup operations
RMAN> RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK/SBT
FORMAT '/u01/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
}
Backup operations
Backup operations
RMAN> backup as copy database;
RMAN> backup copy of database;
RMAN> backup database;
RMAN> configure device type disk backup type to compressed backupset;
RMAN> backup as compressed backupset full database plus archivelog;
Complete database recovery
• Needed when:
– All datafiles are lost or the SYSTEM tablespace datafiles are lost
– At least one member of each redo log group survived
• Requires:
– Control file recovery (if it’s lost)
– Datafile restore from a backup
– Database recovery using incremental backups and/or archived redo logs and online redo logs
Database point-in-time recovery
• Needed when:
– all datafiles are lost
– All copies of the current control file are lost
– Or all online redo log group members are lost
• If done after a disaster it has to be preceded by:
– Hardware configuration
– OS and Oracle software installation
– Re-creation or restore from non-RMAN backup of listener.ora, tnsnames.ora and other important configuration files
– ASM instance and diskgroup configuration (if needed)
– MML installation and configuration
• Requires
– Spfile restore
– Controlfile restore
– Datafiles restore and recovery
Recovery
Tablespace point-in-time recovery
• Needed
– Mainly to address a human error
• Oracle makes efforts to automate it
– Can be done with few clicks in OEM
• Requires
– Point in time recovery of the whole database Export/import of selected tablespaces schemas or objects
Block media recovery
• Needed when:
– Database reports either single or multi block corruption
• Can be done with an open database
• Database corruptions can be discovered with RMAN backup validate database command
• Corrupted blocks can be found in V$DATABASE_BLOCK_CORRUPTION
Single/multiple datafile/tablespace recovery
• Needed when
– Single/multiple tablespaces or datafiles have been lost
– SYSTEM tablespace is intact
– Controlfiles and online redo logs are intact
• Requires
– To put offline datafiles and tablespaces being recovered
– The database can be open and available to users
v$views
• V$BACKUP_ARCHIVELOG_DETAILS
• V$BACKUP_ARCHIVELOG_SUMMARY
• V$BACKUP_CONTROLFILE_DETAILS
• V$BACKUP_CONTROLFILE_SUMMARY
• V$BACKUP_COPY_DETAILS
• V$BACKUP_COPY_SUMMARY
• V$BACKUP_PIECE_DETAILS
• V$RMAN_BACKUP_JOB_DETAILS
• V$RMAN_BACKUP_TYPE
• V$FLASH_RECOVERY_AREA_USAGE
v$views
DBA_HIST_INSTANCE_RECOVERY
DBA_RECOVERABLE_SCRIPT
DBA_RECOVERABLE_SCRIPT_BLOCKS
DBA_RECOVERABLE_SCRIPT_ERRORS
DBA_RECOVERABLE_SCRIPT_PARAMS
GV_$INSTANCE_RECOVERY
GV_$RECOVER_FILE
GV_$RECOVERY_FILE_STATUS
GV_$RECOVERY_LOG
GV_$RECOVERY_PROGRESS
GV_$RECOVERY_STATUS
V_$FLASH_RECOVERY_AREA_USAGE
V_$INSTANCE_RECOVERY
V_$RECOVER_FILE
V_$RECOVERY_FILE_DEST
V_$RECOVERY_FILE_STATUS
V_$RECOVERY_LOG
V_$RECOVERY_PROGRESS
V_$RECOVERY_STATUS
Complete Restore / Recover Syntax
ORACLE_SID=TARGBD
Export ORACLE_SID
rman target rman/rman
RMAN> set dbid=1138590899
Executing command: SET DBID
RMAN> startup nomount
Oracle instance started
RMAN> set controlfile autobackup format for device type disk to 'c:\backup\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;
RMAN> mount database;
database mounted
RMAN> restore database;
Starting restore at …..
RMAN> recover database;
Starting recover at ….
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
Block Media Recovery
Oracle introduced the ability to perform block level recovery in 9i. The following syntax can be used to perform block level recovery:
RMAN> blockrecover datafile 1 block 2;
OR
run {
allocate channel c1 device type disk|sbt;
blockrecover datafile 1 block 2;
}
Note these restrictions of block media recovery:
• You can only perform block media recovery with RMAN. No SQL*Plus recovery interface is available.
• You can only perform complete recovery of individual blocks. In other words, you cannot stop recovery before all redo has been applied to the block.
• You can only recover blocks marked media corrupt. The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were marked corrupt since the most recent BACKUP or BACKUP ... VALIDATE command was run against the file.
• You must have a full RMAN backup. Incremental backups are not allowed. Note that Block media recovery is able to restore blocks from parent incarnation backups and recover the corrupted blocks through a RESETLOGS.
• Blocks that are marked media corrupt are not accessible to users until recovery is complete. Any attempt to use a block undergoing media recovery results in an error message indicating that the block is media corrupt.
Export ORACLE_SID
rman target rman/rman
RMAN> set dbid=1138590899
Executing command: SET DBID
RMAN> startup nomount
Oracle instance started
RMAN> set controlfile autobackup format for device type disk to 'c:\backup\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;
RMAN> mount database;
database mounted
RMAN> restore database;
Starting restore at …..
RMAN> recover database;
Starting recover at ….
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
Block Media Recovery
Oracle introduced the ability to perform block level recovery in 9i. The following syntax can be used to perform block level recovery:
RMAN> blockrecover datafile 1 block 2;
OR
run {
allocate channel c1 device type disk|sbt;
blockrecover datafile 1 block 2;
}
Note these restrictions of block media recovery:
• You can only perform block media recovery with RMAN. No SQL*Plus recovery interface is available.
• You can only perform complete recovery of individual blocks. In other words, you cannot stop recovery before all redo has been applied to the block.
• You can only recover blocks marked media corrupt. The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were marked corrupt since the most recent BACKUP or BACKUP ... VALIDATE command was run against the file.
• You must have a full RMAN backup. Incremental backups are not allowed. Note that Block media recovery is able to restore blocks from parent incarnation backups and recover the corrupted blocks through a RESETLOGS.
• Blocks that are marked media corrupt are not accessible to users until recovery is complete. Any attempt to use a block undergoing media recovery results in an error message indicating that the block is media corrupt.
Subscribe to:
Posts (Atom)
