R12/11i Apps Scripts

How to change APPS Server Date & Time

Linux Set Date and Time From a Command Prompt

How can I set the system date and time from the command prompt (bash shell)? I don't have GUI installed and I am login over ssh session. How can I set date under Linux operating systems?

Use the date command to display the current date and time or set the system date / time over ssh session. You can also run the date command from X terminal as root user.
This is useful if the Linux server time and/or date is wrong, and you need to set it to new values from the shell prompt.
You must login as root user to use date command.

Linux Set Date

Use the following syntax to set new data and time:
date --set="STRING"
For example, set new data to 2 Oct 2006 18:00:00, type the following command as root user:
# date -s "2 OCT 2006 18:00:00"
OR
# date --set="2 OCT 2006 18:00:00"
You can also simplify format using following syntax:
# date +%Y%m%d -s "20081128"

Linux Set Time

To set time use the following syntax:
# date +%T -s "10:13:13"
Where,
  • 10: Hour (hh)
  • 13: Minute (mm)
  • 13: Second (ss)
Use %p locale’s equivalent of either AM or PM, enter:
# date +%T%p -s "6:10:30AM"
# date +%T%p -s "12:10:30PM"


[oracle@ebs602 scripts]$ su - root
Password:
[root@ebs602 ~]# date
Tue Apr 21 12:07:45 IST 2009

[root@ebs602 ~]# date -s "21 sep 2012 12:00:00"

Fri Sep 21 12:00:00 IST 2012

[root@ebs602 ~]# date

Fri Sep 21 12:00:08 IST 2012

[root@ebs602 ~]#

R12: Change Application user password and end date from sqlplus

DECLARE
  v_user_name     VARCHAR2(80) := 'GFMISDBA';
  v_new_password  VARCHAR2(80) := 'oracle123';
  v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
BEGIN
  fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                 ,val  => 'N');
  -- Call the procedure
  fnd_user_pkg.updateuser(x_user_name            => v_user_name
                         ,x_owner                => 'CUST'
                         ,x_unencrypted_password => v_new_password
                         ,x_end_date             => SYSDATE + 10000);
  fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                 ,val  => v_hard_password);
COMMIT ;
EXCEPTION
  WHEN OTHERS THEN
    fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
                   ,val  => v_hard_password);
    RAISE;
END;

Complie oracle forms in Oracle Apps R12

Execute the following comman as applmgr user:

frmcmp_batch.sh module=$AU_TOP/forms/US/xxxx.fmb   userid=apps/password@SID
module_type=form Output_File=$PROD_TOP/forms/US/xxx.fmx

Oracle Apps R12 Change sysadmin password from sqlplus

Two methods to change the sysadmin password:
1- CPASS tool: This is an Oracle tool that allow change system and database user password's
like SYSADMIN, APPLSYSPUB, APPS, GL, AR.

FNDCPASS apps/appspwd 0 Y system/manager USER SYSADMIN newspassword

2- fnd_user_pkg tool: This package maintains oracle application user accounts
To change the sysadmin password execute the following script:

declare
   l_flag  boolean;
begin
  l_flag = fnd_user_pkg.change_password('sysadmin','newpassword');
end;

To check if the new password take place run the following:
select   fnd_web_sec.validate_login('sysadmin','newpassword')    from dual;

The expected result would be Y.

Differences between 9i and 10g that affects RAC

1- Oracle Clusterware: In 9i was called oracm cluster manager; renamed to Oracle Cluster Ready Services     10.1. Finally in 10.2 called Oracle Clusterware.

2- Virtual IP: In 9i it was take a long time to recover clients connection after node failure.
Introduced in 10.1 VIP used instead of Public IP. When a node failure happens the client connection    
transferred from the failed node to another node using the VIP.

3- ASM ( Automatic Storage Management ): This feature introduced in 10.1 that is an alternative to the volume manager. It also provide double or triple mirroring. The only exception in ASM that it allow to store Oracle Database files  and associated files like archive redo logs and flash recover area files.

4- AWR (Automatic Workload Repository): Introduced in 10.1; Records an extended set of database usage statistics in the database for subsequent analysis and performance tuning. Its an evolution of STATSPACK.

5- ASH (Active Session History): Introduced in 10.1; Records wait events for each session. This information is initially stored in the SGA and subsequantaly summerized and flushed to the database to be kept for time-based performance tuning.


6- FAN(Fast Application Notification): Introduced in 10.1;allows databases, listeners, application servers and clients to receive rapid notifications of database events such as start/stop database, instances or services.
Well-written application can be also utilize this to reconnect to the instance without end user aware.

7- ADDM(Automatic Database Diagnostics Monitor):Introduced in 10.1; It provides the same STATSPACK reports functionality plus detecting problems and provide resolutions.

7- Database services: Introduced in 8i and enhanced in 10.1; It's logical grouping for sessions doing the same kind of work. Service later assigned to a Preferred instance(s) and Available(backup) instance(s) 

8- Database scheduler: Introduced in 10.1 and replaced DBMS_JOB package. Its more felxable by allowing jobs to be grouped in classes, job chains and windows to be created and external jobs such as shell scripts to be executed.
 
9- CVU (Cluster Verification Utility): Introduced in 10.2 to provide a method for verifying each stage of RAC installation process.

10- OEM (Oracle Enterprise Manager): This tool is converted from Java application to HTML web application and extended greatly. Two types of OEM available:
   A- Grid Control: Allow managing multiple nodes, database, instances and more.
   B- Database Control: This allow managing single database node.

Create a context file on Oracle Apps R12

For the dbTier Under $ORACLE_HOME/appsutil/bin
./adbldxml.pl tier=db appsuser=apps appspass=apps

For the appTier Under $AD_TOP/bin

perl adbldxml.pl tier=apps appsuser= appspasswd= (apps password )

Oracle RAC Background Processes

RAC Background Processes:

1. Lock Monitor Processes ( LMON)
2. Lock Monitor Services (LMS)
3. Lock Monitor Daemon Process ( LMD)
4. LCKn ( Lock Process)
5. DIAG (Diagnostic Daemon)

1. Lock Monitor Processes ( LMON)

It Maintains GCS memory structures.
Handles the abnormal termination of processes and instances.
Reconfiguration of locks & resources when an instance joins or leaves the cluster are handled by LMON ( During reconfiguration LMON generate the trace files)
It responsible for executing dynamic lock remastering every 10 mins ( Only in 10g R2 & later versions).
LMON Processes manages the global locks & resources.
It monitors all instances in cluster, primary for dictionary cache locks,library cache locks & deadlocks on deadlock sensitive on enqueue & resources.
LMON also provides cluster group services.
Also called Global enqueue service monitor.

2. Lock Monitor Services (LMS)

LMS is most very active background processes.
Consuming significant amount of CPU time. ( 10g R2 - ensure that LMS process does not encounter the CPU starvation).
Its primary job is to transport blocks across the nodes for cache-fusion requests.
If there is a consistent-read request, the LMS process rolls back the block, makes a Consistent-Read image of the block and then ship this block across the HSI (High Speed Interconnect) to the process requesting from a remote node.
LMS must also check constantly with the LMD background process (or our GES process) to get the lock requests placed by the LMD process.
Each node have 2 or more LMS processes.

GCS_SERVER_PROCESSES --> no of LMS processes specified in init. ora parameter.
Above parameter value set based on number of cpu's ( MIN(CPU_COUNT/2,2))
10gR2, single CPU instance,only one LMS processes started.
Increasing the parameter value,if global cache activity is very high.
Also called the GCS (Global Cache Services) processes.

Internal View: X$KJMSDP

3. Lock Monitor Daemon Process ( LMDn)

LMD process performs global lock deadlock detection.
Also monitors for lock conversion timeouts.
Also sometimes referred to as the GES (Global Enqueue Service) daemon since its job is to manage the global enqueue and global resource access.
LMD process also handles deadlock detection and remote enqueue requests.
Remote resource requests are the requests originating from another instance.
Internal View: X$KJMDDP

4. LCKn ( Lock Process)

Manages instance resource requests & cross instance calls for shared resources.
During instance recovery,it builds a list of invalid lock elements and validates lock elements.

5. DIAG (Diagnostic Daemon)

Oracle 10g - this one new background processes ( New enhanced diagnosability framework).
Regularly monitors the health of the instance.
Also checks instance hangs & deadlocks.
It captures the vital diagnostics data for instance & process failures.

Create Application User with sqlplus in Apps R12

-- Create a Oracle Application user with the system administrator
-- and application developer responsibilities. Just the "UserName"
-- and "Your complete name" to

BEGIN
fnd_user_pkg.CreateUser(x_user_name => 'UserName'
,x_owner => 'CUST'
,x_unencrypted_password => '12345'
,x_description => 'Your complete name'
);

fnd_user_pkg.AddResp(username => 'UserName'
,resp_app => 'FND'
,resp_key => 'APPLICATION_DEVELOPER'
,security_group => 'STANDARD'
,description => 'Applicatie Developer'
,start_date => SYSDATE
,end_date => NULL
);

fnd_user_pkg.AddResp(username => 'UserName'
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'System admin'
,start_date => SYSDATE
,end_date => NULL
);
COMMIT;
-- The commit here is very important
END;

Count the concurrent users in apps r12

SQL> select count(distinct d.user_name) from

apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1);



COUNT(DISTINCTD.USER_NAME)

--------------------------

                        12

 
Oracle Apps 11i/R12 Maintenance mode
 
 
Maintenance mode – (adadmin)
when you are going to install a patch on application the recommended option is that enable maintenance node. to bring the application in maintenance mode. when you enable or disable ‘maintenance mode’, adadmin will execute the script.
enable maintenance mode:
@>/ad_top/patch/115/sql/adsetmmd.sql enable
disable maintenance mode:
@>/ad_top/patch/115/sql/adsetmmd.sql disable
to verify if the environment is in maintenance mode or not execute following script.
select fnd_profile.value('APPS_MAINTENANCE_MODE') as status
from dual;
if the status
“maint” =       maintenance mode has been enabled and the users will not be able to login.
“normal”       =       maintenance mode has been de-activated and the users will be able to login.
how to enable the maintenance mode
set the environment variable on application instance.
run the ad administration utility by typing adadmin on console window. chose option 5 from the selection menu.
1.generate applications file menu.
2.maintain applications file menu.
3.compile/reload applications database entities menu.
4.maintain applications database entities menu.
5.change maintenance mode.
6.exit ad administration.

Apps DBA Daily tasks

eBusiness 11i/R12 – Daily Checks (for Apps. DBA) – Admin Node
Being Apps. DBA, one should check the subsequent on Daily Basis in order to make sure that everything is working fine. This post is relating to ADMIN NODE.

Listener Status
[oracle@r0469 11.1.0]$ lsnrctl status VIS
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 21-SEP-2012 16:18:39
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=r0469.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias VIS
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 19-SEP-2012 12:21:31
Uptime 2 days 3 hr. 57 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/VIS/db/tech_st/11.1.0/network/admin/VIS_r0469/listener.ora
Listener Log File /oracle/VIS/db/tech_st/11.1.0/log/diag/tnslsnr/r0469/vis/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=r0469.oracle.com)(PORT=1521)))
Services Summary...
Service "VIS" has 2 instance(s).
Instance "VIS", status UNKNOWN, has 1 handler(s) for this service...
Instance "VIS", status READY, has 1 handler(s) for this service...
Service "VIS_XPT" has 1 instance(s).
Instance "VIS", status READY, has 1 handler(s) for this service...
The command completed successfully

File System Space Utilization
$ df -h
Operating System – eBusiness DB and Concurrent Processes
[oracle@r0469 11.1.0]$ ps -ef |grep pmon|grep -v grep

oracle 22862 1 0 Sep19 ? 00:00:06 ora_pmon_VIS

[oracle@r0469 11.1.0]$ ps -ef |grep FNDLIBR|grep -v grep

oracle 15978 15973 0 Sep20 pts/0 00:00:21 FNDLIBR
oracle 16153 16024 0 Sep20 ? 00:00:01 FNDLIBR
oracle 16154 16024 0 Sep20 ? 00:00:01 FNDLIBR
oracle 16159 16024 0 Sep20 ? 00:00:01 FNDLIBR
oracle 16164 16024 0 Sep20 ? 00:00:00 FNDLIBR
eBusiness DB Instance
SQL> select instance_name,host_name,To_CHAR(startup_time,'DD-MON-YYY') startedat from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STARTEDAT
-------------------
VIS
ebs602.oracle.com
03-APR-009

SQL> select instance_name,host_name,To_CHAR(startup_time,'DD-MON-YYY HH:MM:SS') startedat from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STARTEDAT
----------------------------
VIS
ebs602.oracle.com
03-APR-009 10:04:31

SQL> select instance_name,host_name,To_CHAR(startup_time,'DD-MON-YYY HH:MM:SS AM') startedat from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STARTEDAT
-------------------------------
VIS
ebs602.oracle.com
03-APR-009 10:04:31 PM



Invalid Objects
SQL> select owner,object_name,object_type from dba_objects where status='INVALID';



SQL> select owner,object_name,object_type from dba_objects where status='VALID';

Inactive Users
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status='INACTIVE';

Active Sessions
SQL> select inst_id,count(*) from gv$session group by inst_id;
INST_ID COUNT(*)
---------- ----------
1 100

Active Users Detail
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status='INACTIVE';

Data Files Status:
SQL> select name,status from v$datafile;
Log Files Status
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 184 314572800 1 NO INACTIVE

1.0132E+13 19-SEP-12

2 1 185 314572800 1 NO CURRENT

1.0132E+13 21-SEP-12

3 1 183 314572800 1 NO INACTIVE

1.0132E+13 17-SEP-12

Archiving Errors
SQL> select error from v$archive_dest;

ERROR
-----------------------------------------------------------------
10 rows selected.

Tablespace Free Space
SQL> select tablespace_name,round (sum(bytes)/1024/1024) Free_Space_MB from dba_free_space group by (TABLESPACE_NAME);

Invalid Indexes
SQL> select index_name,table_name from dba_indexes where status='INVALID' and owner='APPS';
no rows selected

eBusiness 11i/R12 – Applications Listener Status
$ sh $COMMON_TOP/admin/scripts/PROD_oracle2/adalnctl.sh status
Checking on the status of Report Server
$ sh $ORACLE_HOME/admin/scripts/PROD_oracle2/adrepctl.sh status
Database Connectivity via Apps. user without Connect String
$ sqlplus -S -M “HTML ON TABLE ‘BORDER=”2″‘” “apps/****” @/oracle/appsprod/oui/venkat/appschk.sql > /oracle/appsprod/oui/venkat/output.html

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...