Pages

Wednesday, March 29, 2017

To check oracle custom alert

To check oracle custom alert


SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y'  -- show only enabled alerts;

To check all scheduled concurrent requests 11i/r12

To check all scheduled concurrent requests 11i/r12


select r.request_id,
       p.user_concurrent_program_name || case
         when p.user_concurrent_program_name = 'Report Set' then
          (select ' - ' || s.user_request_set_name
             from fnd_request_sets_tl s
            where s.application_id = r.argument1
              and s.request_set_id = r.argument2
              and language = 'US')
         when p.user_concurrent_program_name = 'Check Periodic Alert' then
          (select ' - ' || a.alert_name
             from alr_alerts a
            where a.application_id = r.argument1
              and a.alert_id = r.argument2
              and language = 'US')
       end concurrent_program_name,
       case
         when p.user_concurrent_program_name != 'Report Set' and
              p.user_concurrent_program_name != 'Check Periodic Alert' then
          r.argument_text
       end argument_text,
       r.requested_start_date next_run,
       r.hold_flag on_hold,
       decode(c.class_type,
              'P',
              'Periodic',
              'S',
              'On Specific Days',
              'X',
              'Advanced',
              c.class_type) schedule_type,
       case
         when c.class_type = 'P' then
          'Repeat every ' ||
          substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
          decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
                 'N',
                 ' minutes',
                 'M',
                 ' months',
                 'H',
                 ' hours',
                 'D',
                 ' days') ||
          decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
                 'S',
                 ' from the start of the prior run',
                 'C',
                 ' from the completion of the prior run')
         when c.class_type = 'S' then
          nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
          decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
          decode(sign(to_number(substr(c.class_info, 33))),
                 '1',
                 'Days of week: ' ||
                 decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
                 decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
                 decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
                 decode(substr(c.class_info, 36, 1), '1', 'We ') ||
                 decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
                 decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
                 decode(substr(c.class_info, 39, 1), '1', 'Sa '))
       end schedule,
       c.date1 start_date,
       c.date2 end_date,
       c.class_info
  from fnd_concurrent_requests r,
       fnd_conc_release_classes c,
       fnd_concurrent_programs_tl p,
       (SELECT release_class_id,
               substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
          FROM (select release_class_id,
                       rank() over(partition by release_class_id order by s) a,
                       s
                  from (select c.class_info,
                               l,
                               c.release_class_id,
                               decode(substr(c.class_info, l, 1),
                                      '1',
                                      to_char(l)) s
                          from (select level l from dual connect by level <= 31),
                               fnd_conc_release_classes c
                         where c.class_type = 'S')
                 where s is not null)
        CONNECT BY PRIOR
                    (a || release_class_id) = (a - 1) || release_class_id
         START WITH a = 1
         group by release_class_id) dates
 where r.phase_code = 'P'
   and c.application_id = r.release_class_app_id
   and c.release_class_id = r.release_class_id
   and nvl(c.date2, sysdate + 1) > sysdate
   and c.class_type is not null
   and p.concurrent_program_id = r.concurrent_program_id
   and p.application_id = r.program_application_id
   and p.language = 'US'
   and dates.release_class_id(+) = r.release_class_id
 order by on_hold, next_run;

To check long running concurrent request in Oracle Apps 11i or R12

To check long running concurrent request in Oracle Apps 11i or R12



SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;

Saturday, March 25, 2017

To check all recent DDL modifications

To check all recent DDL modifications



select owner, object_type, object_Name, Timestamp Last_DDL from DBA_OBJECTS where  sysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1 order by Owner, Object_Name;



ASMLib download

ASMLib download


$ rpm -qa | grep asmlib

$ /usr/sbin/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

If the ASMLib RPM's are missing in your environment.

Please download the appropriate rpms for your version.

http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html

Thursday, March 23, 2017

OPP Log File Location

OPP Log File Location


Identify the OPP log file :

1.Directly from the file system based after identifying the corresponding OPP log file name using the following SQL statement:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp WHERE fcpp.processor_id = fcp.concurrent_process_id AND fcpp.action_type = 6 AND fcpp.concurrent_request_id = &&request_id;


2.Via the Forms application:

1. Login to the application as SYSADMIN
2. Responsibility: System Administrator
3. Function: Concurrent --> Manager --> Administration
4. Select the Output Post Processor
5. Click on the Processes button
6. Select the Concurrent Process which was active during the time that the request ran
7. Click on the Manager Log button to open the Output Post Processor log file


3.Via the Oracle Application Manager (OAM):

1. Login to the application as SYSADMIN
2. Responsibility: System Administration
3. Function: Oracle Applications Manager --> Concurrent Managers
4. Select the Output Post Processor Service and click on View Details
5. Click on View Processes
6. Select the Concurrent Process which was active during the time that the request ran
7. Click on the Log button to open the Output Post Processor log file

Determine what the heap size per OPP process is currently:

select DEVELOPER_PARAMETERS from FND_CP_SERVICES where SERVICE_ID = (select MANAGER_TYPE from

FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');


To know OPP logfiles :

cd $APPLCSF/$APPLLOG

grep <REQUEST_ID> FNDOPP*
grep Error* FNDOPP*

Log files In r12

Log files In r12


In r12, the log files are located in $INST_TOP/logs

Adpreclone Executable:

Database Tier:
cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME

Application Tier:
cd $ADMIN_SCRIPTS_HOME

Adcfgclone Executable:

Database Tier:

cd $ORACLE_HOME/appsutil/clone/bin

Application Tier:

cd $COMMON_TOP/clone/bin

Cloning log files in R12:

adpreclone.pl logs are Located at:

On the database tier:
cd $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/StageDBTier_< timestamp >.log

On the application tier:
cd $INST_TOP/admin/log/StageAppsTier_< timestamp >.log

adcfgclone.pl logs are Located at:

On the database tier:
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_< timestamp >.log

On the application tier:
$INST_TOP/admin/log/ApplyAppsTier_< timestamp >.log

Logs for the adconfig are Located at:

On the database tier:
cd $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/< timestamp >/adconfig.log
cd $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/< timestamp >/NetServiceHandler.log

On the application tier:
cd $INST_TOP/admin/log/< timestamp >/adconfig.log
cd $INST_TOP/admin/log/< timestamp >/NetServiceHandler.log

logs for the admkappsutil.pl are Located at:

On the application tier:
cd $INST_TOP/admin/log/MakeAppsUtil_< timestamp >.log

Apache Logs:

(10.1.3 Oracle Home which is equivalent to iAS Oracle Home - Apache, OC4J and OPMN)

apache error and access Log files
cd $LOG_HOME/ora/10.1.3/Apache

j2ee Log files
cd $LOG_HOME/ora/10.1.3/j2ee      

opmn Log files
cd $LOG_HOME/ora/10.1.3/opmn      

Patch log files Location:
cd $APPL_TOP/admin/$SID/log/

Concurrent Mangers log files are located in the $APPLCSF/$APPLLOG location.

cd $APPLCSF/$APPLLOG or cd $LOG_HOME/appl/conc

ICM Log:
ls -lrt *$TWO_TASK*

SM Log:  
ls -lrt w*.mgr

CRM Log:
ls -lrt c*.mgr


ICM logs with query:

SELECT 'ICM_LOG_NAME=' || fcp.logfile_name FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id AND fcp.queue_application_id = fcq.application_id AND fcq.manager_type = '0' AND fcp.process_status_code = 'A';

startup scripts log files:
cd $LOG_HOME/appl/admin/log    

Forms & Reports Logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)

cd $LOG_HOME/ora/10.1.2/forms
cd $LOG_HOME/ora/10.1.2/reports

In 11i, the log files of concurrent manager, apache etc are at: $APPLCSF/$APPLLOG or $APACHE_TOP/Apache/logs

Database Tier Logs:
Alert Log File location:
cd $ORACLE_HOME/admin/$CONTEXT_NAME/diag/alert_$SID.log

alertlog with query:

select value from v$diag_info where name='Diag Alert';

OPMN log file location
$ORACLE_HOME/opmn/logs/ipm.log

Apache, Jserv, JVM log files locations:
cd $IAS_ORACLE_HOME/Apache/Apache/logs/ssl_engine_log
cd $IAS_ORACLE_HOME/Apache/Apache/logs/ssl_request_log
cd $IAS_ORACLE_HOME/Apache/Apache/logs/access_log
cd $IAS_ORACLE_HOME/Apache/Apache/logs/error_log
cd $IAS_ORACLE_HOME/Apache/JServ/logs

Worker Log file location:
$APPL_TOP/admin/PROD/log

AutoConfig log files location:

Application Tier:
cd $APPL_TOP/admin/SID_Hostname/log//DDMMTime/adconfig.log

Database Tier:
cd $ORACLE_HOME/appsutil/log/SID_Hostname/DDMMTime/adconfig.log

Error log file location:

Application Tier:
cd $APPL_TOP/admin/PROD/log

Database Tier:
cd $ORACLE_HOME/appsutil/log/$CONTEXT_NAME

Wednesday, March 22, 2017

Example of DDL, DML, DCL and TCL Commands

Example of DDL, DML, DCL and TCL Commands


1) Data Definition Language (DDL)

- These SQL commands are used for creating, modifying, and dropping the structure of database objects.

create
alter
drop
truncate
rename

a) CREATE - To create objects in the database

ex:
create table mqm (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));

Note: You can't create same table with same name, if you trying to create it will throuh an error

ERROR at line 1:
ORA-00955: name is already used by an existing object


b) ALTER - alters the structure of the database

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ex:
ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

c) DROP - delete objects from the database i,e. Indexes tables, and databases can easily be

deleted/removed with the DROP statement.

ex:
drop index index_name ON table_name

DROP TABLE table_name

DROP DATABASE database_name

d) TRUNCATE - remove all records from a table, including all spaces allocated for the records are

removed. What if we only want to delete the data inside the table, and not the table itself? Then, use the

TRUNCATE TABLE statement:

ex:
TRUNCATE TABLE table_name

f) RENAME - rename an object

ex:
ALTER TABLE table_name RENAME TO new_table_name

ALTER TABLE table_name RENAME COLUMN column 1 TO column 2;

2) Data Manipulation Language (DML)

- These SQL commands are used for storing, retrieving, modifying, and deleting data.

select
insert
update
delete

a) SELECT - retrieve data from the a database

ex:
SELECT column_name,column_name FROM table_name;
SELECT * FROM table_name;
SELECT CustomerName,City FROM Customers;

b) INSERT - insert data into a table

ex:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES

('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
insert into mqm (lastname, firstname) values ('&mam','&lns');

c) UPDATE - updates existing data within a table

ex:
UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg';

d) DELETE - deletes all records from a table, the space for the records remain

ex:
DELETE FROM table_name;

or

DELETE * FROM table_name;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

3) Data Control Language (DCL)

- These SQL commands are used for providing security to database objects.

grant
revoke

a) GRANT - gives user's access privileges to database

ex:
grant dba to abc;
grant connect, resource to abc;

b) REVOKE - withdraw access privileges given with the GRANT command

ex:
revoke dba from abc;
revoke connect, resouce from abc;

4) Transaction Control Language (TCL)

- These SQL commands are used for managing changes affecting the data.

commit
savepoint
rollback

a) COMMIT - save work done

b) SAVEPOINT - identify a point in a transaction to which you can later roll back

c) ROLLBACK - restore database to original since the last COMMIT








Monday, March 13, 2017

DataInstall Errors: "Java.Lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver

DataInstall Errors: "Java.Lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver


Symptoms:


Find that when the following command is executed to start the DataInstall process -

jre oracle.apps.per.DataInstall <apps username> <apps password> thin <connection>


the following error occurs:

java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver
at oracle.apps.per.DataInstall.connect(Compiled Code)
at oracle.apps.per.DataInstall.main(Compiled Code)


Cause:

This error is due to using the wrong syntax in the DataInstall command.


Solution:

Need to run the following command against the database tier:

java oracle.apps.per.DataInstall <un> <pw> thin <host:port: sid>

where:

<un> is the username of the main apps account
<pw> is the password for this account
<host:port: sid> represents the database connection information

For example: java oracle.apps.per.DataInstall apps apps thin dbsvr1:1521:testdb


Reference metalink Doc ID 438183.1

Sunday, March 12, 2017

After Upgrade to 12.1.3, "There are no active responsibilities available for this user" Error is Received after logging.

After Upgrade to 12.1.3, "There are no active responsibilities available for this user" Error is Received after logging


Symptoms:

When you log in, your  home page displays an empty list of responsibilities and message:
"There are no active responsibilities available for this user".
 Same issue is faced by all the users including SYSADMIN.

Changes:

E-Business Suite upgrade from 12.1.1 to 12.1.3


Cause:

EFFECTIVE_START_DATE and EFFECTIVE_END_DATE in WF_LOCAL_USER_ROLES and WF_USER_ROLE_ASSIGNMENTS tables are not correctly synchronized.


Solution:

As you don't have access to any responsibility, to fix the issue, you must execute wf_maintenance.ValidateUserRoles API from sql*plus prompt:


sqlplus apps/apps_pwd

begin
wf_maintenance.ValidateUserRoles(p_BatchSize => NULL, p_check_dangling => TRUE, p_check_missing_ura => TRUE, p_UpdateWho => FALSE);
end;
/

This API invokes the same PL/SQL code than the concurrent program: "Workflow Directory services user/role validation" with argument values as NULL:Yes:Yes:No:NULL


Reference metalink Doc ID 1511839.1

During Login Responsibilities Assigned to Users do not Appear in the Navigator Page

During Login Responsibilities Assigned to Users do not Appear in the Navigator Page



Solution:

User data in the FND_USER, FND_RESPONSIBILITY, WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS tables is not valid.  To synchronize data follow the steps below:

1.  Navigate to the define user form (FNDSCAUS).
2.  Query up the user (that is having the issue) and insure the responsibility is NOT end dated.
3.  END DATE the USER (not the responsibility) and save the record.
4.  UNEND DATE the USER and save the record.
5.  Run the request 'Sync responsibility role data into the WF table'.
6.  Run the request "Synchronize WF LOCAL tables".
7.  Run the request "Workflow Directory Services User/Role Validation" (with parameters 10000, yes, yes, yes).
8.  Have the user log off and back on and verify that the responsibility appears.


Reference metalink Doc ID 565130.1

Friday, March 10, 2017

Cloud Computing

Cloud Computing


What is cloud computing?

In cloud computing, multiple servers, computers, or any computing device connected through network are used together to complete a given task. There is no need for the computing device or any other peripherals to be physically present.

Example of cloud computing :

Google email services. Here end user is only concerned with the email services provided by the google, and end users need not bother about the emails servers, network devices and storage devices put together to provide the email services. Now these required servers, network devices, storage devices etc can be scattered all across the globe but are configured together to provide one specific task.

Advantages of Cloud Computing:

In cloud computing, the actual goal is achieved using multiple devices over network and the devices are scattered across the network which reduces the dependency on any single location, or device and at the same time increases the computing power. End user can focus on its business goal without being concerned about how the infrastructure , the software has been installed and maintained. End user can pay only for that much which is used by him.

Challenges of Cloud Computing:

The level of control on the infrastructure lies in the service provider and end user has to bind  the level of control provided by the service providers. End user has no control over the version of the infrastructure or the software. End user has to follow the maintenance cycle of the service provider.

Security is a big concern in shared cloud environment. As we have gone through the pros and cons of cloud computing, now is the time to list out types of cloud computing environment.

On the basis of usage, cloud computing is divided into Public and Private cloud computing.
On the basis of type of service being provided cloud computing can be listed as of below types:

Software as a Service or commonly called as SAAS.
Infrastructure as a Service or commonly called as IAAS.
Platform as a Service or commonly called as PAAS.

Private Cloud Computing : When the complete infrastructure or the software or any other kind of service is used by only one consumer and it is not shared by any other user, this kind of  arrangement is called as Private Cloud Computing.

Public Cloud Computing : When the complete infrastructure of the software of any other kind of service can be used by multiple users then the arrangement is called as public cloud computing.

Platform as a Service : In PAAS , complete platform is provided as a service where which can be used by the end user.

Software as a Service: In SAAS, software services are provided as a service.

Infrastructure as a Service: In IAAS, Infrastructure like Physical host devices, network devices like routers, operating systems etc are provided as a service to be used by the end user.






Wednesday, March 8, 2017

RMAN Delete Command

RMAN Delete Command



The RMAN DELETE command deletes backups from disk and/or from the catalog

To delete all backups for the target database use:

RMAN> DELETE BACKUP;
Delete Backupset

To delete a backup set specify the set number e.g. 23:

RMAN> DELETE BACKUPSET 23;
NOPROMPT keyword

By default the DELETE command will prompt for confirmation before deleting any backup files

Do you really want to delete the above objects (enter YES or NO)?
To suppress the prompt specify the NOPROMPT keyword. For example:

RMAN> DELETE NOPROMPT BACKUP;
Image Copies

To delete all datafile copies:

RMAN> DELETE DATAFILECOPY ALL;
To delete an individual datafile copy use:

RMAN> DELETE DATAFILECOPY <key>;
For example:

RMAN> DELETE DATAFILECOPY 26;
Alternatively specify the datafile image copy name. For example:

RMAN> DELETE DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';
To delete a specific controlfile copy use:

RMAN> DELETE CONTROLFILECOPY <key>;
For example:

RMAN> DELETE CONTROLFILECOPY 20;
Alternatively specify the control file copy name e.g.:

RMAN> DELETE CONTROLFILECOPY '/u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd';
To delete all backups of the USERS tablespace use:

RMAN> DELETE BACKUP OF TABLESPACE USERS;
Expired Backups

To delete any expired backups detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED BACKUP;
To delete all expired archive logs detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED ARCHIVELOG ALL;
Obsolete Backups

To delete backups that have become obsolete based on the retention policy.

RMAN> DELETE OBSOLETE;

ORA-600 lookup tool

ORA-600 lookup tool


ORA-600/ORA-7445 are generic internal error numbers for Oracle program exceptions.
Sometimes these errors are unique for your specific problem and cannot be found via search engines.Using "ORA-600 lookup tool" may point your specific problem, faster and accurate.

The tool can be found in ORA-600 lookup tool - Metalink Document ID 153788.1

The flowing video will guide you the usage of the LookUp Tool (11:12) - Metalink Document ID 1082674.1