Skip to content
Advertisements

Drop all synonym from owner

Drop all synonym from owner

set head off
set newpage none
set pagesize 9999

spool drop_synonym.sql

select ‘drop synonym ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘;’ from dba_objects where OWNER =’OWNER’ and OBJECT_TYPE =’SYNONYM’;

spool off

Advertisements

Databaselink extract

Database link extract

 

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) FROM dba_db_links a;

 

 

compile jsp in r12

Stop application service

cd $FND_TOP
[appltrng@ebsdev bin]$ perl ojspCompile.pl –compile –flush -p 2

we need to check compiled successfully

start application service

VMware free self paced e-learning course

https://mylearn.vmware.com/mgrReg/plan.cfm?plan=33611&src=so_5a314d05ddb83&cid=70134000001SkJd

script for send alert email to itsupport when new employee joining

the below script for send alert email to itsupport when new employee joining

 

the HR team will update the new employee details in HRMS and after if we schedule the alert,  it will send alert to itsupport team for prepare the email/ext/laptop and others.

 

SELECT Distinct ppf.employee_number, ppf.full_name emp_name,
hr_general.decode_job (paf.job_id) POSITION,
xxhr_ace.get_dept_name (paf.organization_id,
paf.business_group_id
) department,
TO_CHAR (ppf.effective_start_date, ‘DD-MON-YYYY’) Joining_date,
hl.location_code,(select full_name from per_all_people_f where person_id=paf.SUPERVISOR_ID AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)Supervisor_name,fu.user_name
into &emp_num,&emp_name, &posi, &department,&Joining ,&Loc,&Sup,&UsrName
FROM per_all_people_f ppf,
per_all_assignments_f paf,
per_qualifications pq,
hr_locations hl,
fnd_user fu
WHERE 1 = 1
AND ppf.person_id = paf.person_id
AND ppf.business_group_id = paf.business_group_id
and paf.location_id=hl.location_id
AND pq.person_id(+) = ppf.person_id
and ppf.CREATION_DATE>=trunc(sysdate)
and fu.EMPLOYEE_ID(+)=ppf.PERSON_ID
AND ppf.last_update_date> TO_DATE(:DATE_LAST_CHECKED, ‘DD-MON-YYYY HH24:MI:SS’)
and not exists(select 1 from per_all_people_f where person_id=ppf.person_id and TRUNC (SYSDATE)-365 BETWEEN effective_start_date
AND effective_end_date )
ORDER BY Location_code,TO_NUMBER (ppf.employee_number);

User not able to view them responsibilities after logon EBS

In some situation, User not able to view them responsibilities after logon EBS.

 

Solution :

Submit Workflow Directory Services User/Role Validation concurrent request  and run

 

now they can able to view the responsibilities.

In case Still not view responsibilities,  run the Synchronize WF LOCAL tables concurrent request as well.

Oracle Linux 7.6 black screen while boot from virtualbox

I am trying to boot my oracle linux 7.6 which is running under virtual box but black screen appear long time

 

Causes :

 

Network manager & network manager wait online service have a problem

Solution :

Boot as rescue mode like below

Edit mode (e)

end of line linux16, add below command

systemd.unit=target.rescue

control + x  –> enter

 

Maintenance mode will appear

Enter root password maintenance

Execute below command

systemctl mask systemd-NetworkManager-wait-online.service

reboot the machine

The network manager may have a problem after restarted the machine, so start the network manager like below

systemctl enable NetworkManager

systemctl unmask NetworkManager.service

systemctl start NetworkManager.service

 

In case selinux may also making issue for black screen appeared while booting. Please disable and reboot the machine.

Error while executing service Service execution error – target name xml-multiple of processing instruction at line {1}

Custom REST web service failed with below errors

Tue Jun 18 17:09:13 AST 2019 : oracle.apps.fnd.soa.rt.rest.common.IsgExceptionMapper.toResponse : oracle.apps.fnd.soa.rt.rest.common.IsgRestInvocationException: ServiceExecError: Error while executing service Service execution error – target name xml-multiple of processing instruction at line {1}, column {2} is reserved target name xml-multiple of processing instruction at line {1}, column {2} is reserved
at oracle.apps.fnd.soa.rt.rest.common.ErrorContext.makeRestException(ErrorContext.java:106)
at oracle.apps.fnd.soa.rt.rest.common.ErrorContext.throwError(ErrorContext.java:88)
at oracle.apps.fnd.soa.rt.rest.provider.IsgRestServiceProvider.serviceJSONRequest(IsgRestServiceProvider.java:652)
at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:149)
at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:67)
at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:259)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:133)
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:83)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:133)
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:71)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:990)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:941)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:932)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:384)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:451)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:632)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:734)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:662)
Tue Jun 18 17:09:13 AST 2019 : oracle.apps.fnd.soa.rt.rest.common.IsgExceptionMapper.toResponse : Response Media Type – application/json
Tue Jun 18 17:09:13 AST 2019 : oracle.apps.fnd.soa.rt.rest.common.IsgExceptionMapper.toResponse : Error Response –
{
“ISGServiceFault” : {
“Code” : “ISG_SERVICE_EXECUTION_ERROR”,
“Message” : “Error while executing service”,
“Resolution” : “System error, please see service log trace for details”,
“ServiceDetails” : {
“ServiceName” : “XXACE_REV_SOA_CMS_PKG1”,
“OperationName” : “get_transaction_proc”,
“InstanceId” : “0”
}
}
}

Solution :

Download and apply the Patch 17428617 at Oracle Home (10.1.3) path on Application server using opatch

Custom REST webservice with nested array based package is failing while invoking (Doc ID 2439803.1)

Oracle database 18c yum installation on Oracle linux 7.6

I have downloaded the oracle database 18c rpm package from oracle and have plan to install manually without ULN support.

Installing Oracle Database RPM Manually

 

[root@dbhost1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.1.9.88 dbhost1.localdomain dbhost1

Download software :

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html

[root@dbhost1 ~]# yum -y install oracle-database-preinstall-18c
BDB2053 Freeing read locks for locker 0xbb: 12147/139929030764352
BDB2053 Freeing read locks for locker 0xbd: 12147/139929030764352
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
–> Running transaction check
—> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed

–> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================
Installing:
oracle-database-preinstall-18c x86_64 1.0-1.el7 ol7_latest 18 k

Transaction Summary
========================================================================================================================================================================
Install 1 Package

Total download size: 18 k
Installed size: 55 k
Downloading packages:
oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm | 18 kB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-database-preinstall-18c-1.0-1.el7.x86_64 1/1
Verifying : oracle-database-preinstall-18c-1.0-1.el7.x86_64 1/1

Installed:
oracle-database-preinstall-18c.x86_64 0:1.0-1.el7

Complete!

[root@dbhost1 install]# ls -ltr
total 6874960
drwxr-xr-x 7 oracle oinstall 117 Jan 26 2017 database
-rw-r–r– 1 root root 3586257564 Oct 24 2018 oracle-database-ee-18c-1.0-1.x86_64.rpm
-rwxrwxr-x. 1 oracle oinstall 3453696911 Jun 13 11:45 linuxx64_12201_database.zip

Install database software

[root@dbhost1 install]# yum -y localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining oracle-database-ee-18c-1.0-1.x86_64.rpm: oracle-database-ee-18c-1.0-1.x86_64
Marking oracle-database-ee-18c-1.0-1.x86_64.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================
Installing:
oracle-database-ee-18c x86_64 1.0-1 /oracle-database-ee-18c-1.0-1.x86_64 7.8 G

Transaction Summary
========================================================================================================================================================================
Install 1 Package

Total size: 7.8 G
Installed size: 7.8 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-database-ee-18c-1.0-1.x86_64 1/1
[INFO] Executing post installation scripts…
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-18c configure
Verifying : oracle-database-ee-18c-1.0-1.x86_64 1/1

Installed:
oracle-database-ee-18c.x86_64 0:1.0-1

Complete!

Oracle Database software created at /opt/oracle/product/18c/dbhome_1

Creating and Configuring an Oracle Database

there is a location to find the details for creating the database,

/etc/init.d/oracledb_ORCLCDB-18c

you can manually edit the db creation configuration. I modified the database name is UATCDB, Pluggable database is UATPDB1
As a root user, create the database.

[root@dbhost1 ~]# /etc/init.d/oracledb_UATCDB-18c configure
Configuring Oracle Database UATCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete

40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/UATCDB.
Database Information:
Global Database Name:UATCDB
System Identifier(SID):UATCDB
Look at the log file “/opt/oracle/cfgtoollogs/dbca/UATCDB/UATCDB.log” for further details.

Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using ‘sqlplus / as sysdba’ as the oracle user.

Verify database Creation as oracle user

[oracle@dbhost1 ~]$ export ORACLE_HOME=/opt/oracle/product/18c/dbhome_1
[oracle@dbhost1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@dbhost1 ~]$ export ORACLE_SID=UATDB
[oracle@dbhost1 ~]$ export ORACLE_SID=UATCDB
[oracle@dbhost1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 – Production on Sun Jun 30 16:03:19 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0

SQL> select name from v$database;

NAME
———
UATCDB

SQL> show user con_name
USER is “SYS”

CON_NAME
——————————
CDB$ROOT
SQL> select con_id, name from v$containers;

CON_ID
———-
NAME
——————————————————————————–
1
CDB$ROOT

2
PDB$SEED

3
UATPDB1

SQL>

Oracle database 12c (12.2.0.1.0) step by step installation guide on windows server 2016

Oracle Database 12cR2 installation documentation on Windows Server 2016

Oracle database 12c (12.2.0.1.0) step by step installation guide on windows server 2016