Oracle Database 11g: Architecture Overview

Oracle Database 11g: INTERACTIVE QUICK REFERENCE 

If you are registered user with Oracle, you may obtain the quick reference video from this link 
  

Also, Oracle Documentation provides detailed explanation of each and every processes & memory structures as per below diagram

 Ref: Oracle Documentation Page

How to Turn off / Disable User Account Expiration

Oracle User Account Expired. How to disable or turn off EXPIRE
===========================================


Issue:

One of the critical User account got expired, expire to be turned off to avoid connection issue on application connectivity

Issue Details:


1. Found User account is about to expire and it is on GRACE Period

SQL> select username,account_status,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE CREATED PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 18-OCT-11 09-APR-11 DEFAULT


2. Confirmed with profile settings that 180 expiration


SQL> select * from dba_profiles where profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7



3. To disable/Turn off the expiration, we need to set profile to UNLIMITED:


SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

4. While Checking the status, it still shows Expiry date as shown earlier, so, what could be cause or reason???


SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


5. Profile shows UNLIMITED, but why user account did not get effect???


SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

16 rows selected.


6. Tried resetting the profile again to user, but no luck... :-(


SQL> alter user TEST profile default;

User altered.

SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


7. Tried setting account unlock...but still no luck....

SQL> alter user TEST account unlock;

User altered.

SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


SOLUTION:


8. Then found that, when user is already in GRACE Period, even altering profile, will NOT take effect until the password is changed

Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value

Be careful if you plan to set the PASSWORD_LIFE_TIME parameter of CREATE PROFILE or ALTER PROFILE to a low value (for example, 1 day). If the user who is assigned this profile is concurrently logged in when you make this modification, then Oracle Database sets the user's account status from OPEN to EXPIRED(GRACE)with the warning error ORA-28002: the password will expire within n days. You may not be notified of this change because the user can still connect to the Oracle database. You can find the concurrently logged in users by querying the USERNAME column of the V$SESSION dynamic performance view.

Note the following:

If the user is not logged in when you set PASSWORD_LIFE_TIME to a low value, then the user's account status does not change when the user does log in.

You can set the PASSWORD_LIFE_TIME parameter to UNLIMITED, but this only affects accounts that have not entered their grace period. If the user has already entered the grace period, then he or she must change the password.


Ref: http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#i1007339



SQL> alter user TEST identified by test;

User altered.


SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST'

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST OPEN 09-APR-11 DEFAULT

Alert Log : Diskgroup Mounted & Dismounted

I could see the following messages in Alertlog , was curious to know the reason behind it…

Mon Oct 10 11:49:00 2011

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

Mon Oct 10 11:50:00 2011

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

Mon Oct 10 11:51:00 2011

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

This is expected behavior. The messages you see are not errors (that is why they are prefixed with "SUCCESS"). The database instance is creating and then closing archive logs one at a time in the ARCH_QA disk group. Whenever a database closes its last file in a disk group, it dismounts the disk group. Whenever a database accesses a disk group when it does not have any other files open in the disk group, it mounts the disk group.

If you do not want to see the log messages frequently, you can put a mirrored control-file, or mirrored online redo, or dummy online tablespace data file on the diskgroup. This message will not be output frequently in this case. Because CKPT keeps opening a control-file, LGWR keeps opening an online redo, DBW keeps opening an online tablespace data file. So, the diskgroup is not dismounted until the database shutdown.

TailPiece: we get this messages only for respective ASM disk groups for archive logs are written and in a high number

Ref: Why FRA Diskgroup Gets Mounted/Dismounted ? [ID 603204.1]

Cross Platform Database : Best Approach

How to Move Huge Database from Sun to Linux : Best Approach

You wanna transfer your huge database cross platform sun to linux ??

Lets Look at the options:

1. Best Option : RMAN

RMAN Cross-Platform Transportable Databases and Tablespaces

2. Other Options:

Export/Import
Datapump : Expdp/Impdp
Transport tablespace

Please share your ideas,best methods/practice ....

All About ADI in Oracle Applications

What is ADI? How this is used? Let's see the complete details about it...

Oracle ADI is a spreadsheet-based extension to Oracle Applications that offers full cycle accounting and asset management within the comfort and familiarity of a spreadsheet. Oracle ADI combines a spreadsheet’s ease of use with the power of Oracle Applications, to provide true desktop integration during every phase of your accounting cycle. You can create budgets, record transactions, add assets, reconcile inventory, and run financial statements and inventory reports all without leaving your spreadsheet.

You can run Oracle ADI as a stand-alone application, without installing the full client version of Oracle General Ledger (GL) or Oracle Assets on your PC. This gives you the ability to confirm and reconcile inventory, revise budgets, create journal entries, and define financial and asset reports from any location, without being connected to your server. You only need to connect when you want to transfer data to or from Oracle Applications.

Figure describes how ADI integrates with other applications.

Oracle ADI Key Features
Oracle ADI key features are as follows:
■ Budget Wizard, Journal Wizard, Report Wizard, and Analysis Wizard to
simplify your work with GL.
■ Account Hierarchy Editor to graphically create, maintain, and review account
structure hierarchies.
■ Spreadsheet-based interface to simplify asset creation and physical inventory
process.
■ Request Center for submitting, monitoring, and publishing your reports.

Oracle ADI Integrations
Oracle ADI integrates with the following modules:
Oracle General Ledger
GL is a comprehensive financial management solution that enhances financial
controls, data collection, information access, and financial reporting throughout the
enterprise. It is part of the Oracle E-Business Suite, an integrated set of applications
that are engineered to work together.
Oracle Assets
Oracle Assets is a comprehensive asset management solution that ensures
maintenance of accurate property and equipment inventory as well as optimal
accounting and tax strategies. It is part of the Oracle E-Business Suite, an integrated
set of applications that are engineered to work together.

Ref: http://download.oracle.com/docs/cd/B15436_01/current/acrobat/115csadiug.pdf

EBS 11i Application Desktop Integrator (ADI) Certified on 10gR2 Database

Client Applications Desktop Integrator (ADI) is now certified with Oracle E-Business Suite Release 11i running on the 10gR2 Database. Customers may continue using the client-installed ADI even after upgrading their E-Business Suite Release 11i environment to the 10gR2 Database.

This certification applies to all ADI functionality, including:

• General Ledger Journal Wizard
• Budget Wizard
• Report Wizard
• Analysis Wizard
• Account Hierarchy Editor
• Request Center
• Assets Integrators

Note that this certification applies only to Client ADI with Applications 11i. Client ADI cannot be used with Oracle E-Business Suite Release 12; webADI is available for Release 12 users.

For more details (and a useful Frequently Asked Questions summary for ADI), see:

• Client ADI for EBS 11i is Certified with 10g (Metalink Note 747386.1)

Ref: http://blogs.oracle.com/stevenChan/entry/ebs_11i_application_desktop_integrator_10gr2_certification

Unable to View Concurrent Requests Ouput Files

ISSUE:

"Unable to view Concurrent Requests log/output files"..

We are running 11.5.10.2 and recently did a clone of production to test. However, in the test environment when I run a concurrent request I am no longer able to view the logs or outputs through Oracle.

ERROR

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_TESTDB There may be a network configuration problem, or the TNS listener on node FNDFS_TESTDB may not be running. Please contact your system administrator.


CHECKING:

a) Check whether Concurrent Request log & Output files generated in $APPLCSF/log & $APPLCSF/out to confirm this is NOT Concurrent issue

RESEARCH:

• ADI: Unable To View Reports In ADI [ID 1018797.102]
• How To Set RRA:Service Prefix for Multiple Instances on One Server [ID 235785.1]
• Tracing the sqlnet connection in ADI [ID 223136.1]
• Troubleshooting the "Error Occurred While Attempting to Establish an Applications
File Server Connection" [ID 117012.1]

SOLUITION Path:

1. Perform the RRA Setup Diagnostic Test

This Step will identify all Profile Settings & TNSNames.ora,Hostname in
FND_CONCURRENT_QUEUES table in one go.

Ref: 11i : Oracle Application Object Library Report Review Agent Setup Test
[ID 200358.1]


Command-Line Usage Instructions

• If the latest Oracle Diagnostics patch has been installed, this diagnostic script
is already available on your system. Change directory to $IZU_TOP/bin and execute
the test from there.

• If the latest Oracle Diagnostics patch has not been installed, download and unzip
the latest Oracle Diagnostics patch (see Note 167000.1)
Once unzipped, the Oracle Diagnostics patch can be installed in the normal way
using adpatch (see Note 167000.1) or the test can be executed directly from the
patch directory. Change directory to /izu/bin and execute the test
from there.

• Run the test as follows:

UNIX
$ ./FNDValidateFNDFS115.sh

Windows NT/2000
> sh FNDValidateFNDFS115.sh

2. CrossCheck for multiple Node Name

Verify that the nodename specified in the FNDFS_ entry is the
same as the nodename in the above error message. This should also match the
node name returned from the following select statement:

SQL> select distinct LOGFILE_NODE_NAME
from FND_CONCURRENT_REQUESTS;

3. If this is Cloned Instance from PROD, then check whether any profile values still
pointing to PROD

SQL> select a.profile_option_value,b.profile_option_name from
fnd_profile_option_values a , fnd_profile_options b
where a.profile_option_id=b.profile_option_id
and upper(profile_option_value) like '%PROD%' ;

SQL> select * from fnd_profile_options where profile_option_id=&ID;
SQL> select * from fnd_profile_option_values where profile_option_id=&ID;

4. Mostly issue could be either RRA Profile setting issue or any other profile
setting pointing to PROD

Oracle APPS : Workflow Tables

Oracle Applications - Workflow Tables (Quick Review)

Important WF Tables:

wf_user_role_assignments
wf_user_roles
wf_roles
wf_items
wf_item_attributes
wf_item_attribute_values
wf_item_attributes_tl
wf_activities
wf_activities_tl
wf_activity_attributes
wf_activity_attributes_tl
wf_activity_transitions
wf_deferred--wf_control

WF_NOTIFICATION_ATTRIBUTES
WF_MESSAGES
WF_MESSAGES_TL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES_TL
WF_ETS
WF_PROCESS_ACTIVITIES


WF_ACTIVITIES table stores the definition of an activity. Activities can be Processes, notifications, functions or folders. A process activity is a modelled workflow process, which can be included as an activity in other processes to represent a subprocess. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.

WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time a message is sent.

DBA_Objects shows identical object on same schema?

MATERIALIZED VIEW
=================

Whenever a Materialized view created, you should see 2 entries in dba_objects with same name. Because, it creates a table(segment) and a materialized view.
So, We should NOT get confused that why 2 objects has same name on same schema.

SQL> create materialized view mv1 as select * from i;
Materialized view created.

SQL>select object_name,object_id,data_object_id,object_type,status from dba_objects where object_name='MV1'

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
------------------------------ ---------- -------------- ------------------- -------
MV1 4838577 4838577 TABLE VALID
MV1 4838579 MATERIALIZED VIEW VALID


SQL> alter MATERIALIZED VIEW MV_MOVEMENT_PROCESSES compile;

SQL> execute dbms_mview.refresh('MINE1.MV_MOVEMENT_PROCESSES');
PL/SQL procedure successfully completed.

Im Back!!

Hi All,

I m back to my Blog after a long gap.
You will see my intensive knowledge sharing all my experience in the recent past.

Your Suggestions & Contributions are most welcome!!!


Happy Knowledge Sharing.....

Why there is a diff btwen DBA_SEGMENTS & DATAPUMP of particular schema size

Why there is a difference between DBA_SEGMENTS & DataPUMP of size of particular schema
======================================================================================

1. SELECT SUM(bytes)/1024/1024/1024 AS size_gig FROM DBA_SEGMENTS WHERE OWNER= 'XXX';

2. $ expdp estimate_only=y schemas=xxx

In Common, data pump export would show less value than the size computed from dba_segments/extents


ESTIMATE_ONLY - Use this parameter to estimate the required export file size without starting an actual export job.
ESTIMATE - The ESTIMATE parameter will tell you how much space your new export job is going to consume.
Estimate_only shows the export dump file size not a schema size.

Also, DBA_SEGMENTS contains INDEX segments too,

So, The following query may give you close result

select sum(bytes)/1024/1024/1024 as size_gig, segment_type
from dba_segments
where owner='ENDUR01'
group by segment_type;

How to get the Schema/User size

In SQLPLUS run any of the following scripts to find the total size occupied by a particular schema.


1.

SQL> SELECT sum(bytes)/1024/1024 FROM user_segments;


The output above query would be similar as follows:


SUM(BYTES)/1024/1024
——————–
2552.75


-- OR --

2.

SQL> SELECT tablespace_name, Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = Upper(&User_Name)
GROUP BY owner, rollup(tablespace_name)


-- OR --

3. To be more precise on particular table which excluding index,lob segments etc

SQL> SELECT sum(bytes)/1024/1024/1024 as size_gig, segment_type
FROM dba_segments
WHERE owner='XXX'
GROUP BY segment_type;