Skip to content
Advertisements

How to exit out of ed in sql

When you try to edit using ed

SQL> ed
Wrote file afiedt.buf
35
q

1* select count(*) from dba_objects
SQL>

 

Solution :

type ‘ q ‘ and enter

 

Even sometime  if you type q and enter, not working

SQL> ed
Wrote file afiedt.buf
35
;djg;fg;
?
p
/
p
/
i
k
l
m
q
nm
n;

Solution

ctrl + D

 

Use editor vi

SQL> define_editor=vi

SQL> ed
Wrote file afiedt.buf

1* select count(*) from dba_objects where owner=’APPS’
SQL> /

COUNT(*)
———-
183940

Advertisements

rman backup script

RMAN Backup script :

Older backup will delete from previous folder

current backup will be moved to previous folder

Taking full database backup

Output will send to users by email

 

[orauat@dbs-XXX-xx ~]$ cat /u01/UAT/scripts/DBbackup.sh
#!/bin/sh
ORACLE_HOME=/u01/UAT/db/tech_st/12c export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH export PATH
ORACLE_SID=UAT export ORACLE_SID
cd /ebsbackup/UAT/previous
rm -rf UAT*
rm -rf db_hot_current*
cd /ebsbackup/UAT
mv UAT* /ebsbackup/UAT/previous
mv db_hot_current* /ebsbackup/UAT/previous

export log=/ebsbackup/log/bak-$(date +%Y-%m-%d).log
rman target sys/xxxxxxx cmdfile=/u01/UAT/scripts/DBbackup.rcv msglog=/ebsbackup/log/bak-$(date +%Y-%m-%d).log
mailx -s “UAT Backup log” uatuser@xxxxx.com — -f UAT@alerts.xxxxx.net < $log

 

[orauat@dbs-xxx-xx ~]$ cat /u01/UAT/scripts/DBbackup.rcv
run
{ allocate channel d1 type disk;
backup format ‘/ebsbackup/UAT/UAT%U’ database plus archivelog delete input;
backup current controlfile format ‘/ebsbackup/UAT/db_hot_current_controlfile_%s_%p_%t’;
release channel d1;
}

Easy to export DDL in TOAD

I am thinking to export DDL of some objects from EBS Database. Okay I am using TOAD for export DDL, My work is simply done.

 

Connect the schema

Database — export — Export DDL

Click Add

 

Here you can choose “Like” option also

And you can save the all the OBJECTS DDL with single file

 

 

Script Options

 

ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT

While Cloning

 

– 50% completed ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -25153: ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -25153: ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java:220)
at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchronizer.java:328)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(FileSysDBCtxMerge.java:721)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:226)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1690)
at oracle.apps.ad.clone.ApplyApplTop.runCVM(ApplyApplTop.java:510)
at oracle.apps.ad.clone.ApplyApplTop.runAutoConfig(ApplyApplTop.java:552)
at oracle.apps.ad.clone.ApplyApplTop.doConf(ApplyApplTop.java:339)
at oracle.apps.ad.clone.ApplyApplTop.doApply(ApplyApplTop.java:382)
at oracle.apps.ad.clone.ApplyApplTop.<init>(ApplyApplTop.java:267)
at oracle.apps.ad.clone.ApplyAppsTier.<init>(ApplyAppsTier.java:105)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
at java.lang.Thread.run(Thread.java:662)
/ 90% completed

ERROR while running Apply…
Mon Dec 10 09:21:51 2018

ERROR: Failed to execute /u01/EBSUPGR/apps/apps_st/comn/clone/bin/adclone.pl

Please check logfile.

 

Solution :

I checked my temporary tablespace (v$tempfile), Files are there but still I am getting the issue

 

This issue occurred due to one of temporary tablespace group temp file not there.

SQL> select
tablespace_name, group_name
from
DBA_TABLESPACE_GROUPS; 2 3 4

TABLESPACE_NAME GROUP_NAME
—————————— ——————————
TEMP1 TEMP
TEMP2 TEMP

select file_name from dba_temp_files where TABLESPACE_NAME=’TEMP2′;

no rows selected

 

Add tempfile to temp2 also

SQL> alter tablespace temp2 add tempfile ‘/u01/EBSUPGR/db/apps_st/data/temp02.dbf’ size 5G autoextend on;

Tablespace altered.

 

Run autoconfig db tier and start to do perl adcfgclone.pl appstier again

target node/queue unavailable

After Clone or some unwanted situations, you may get the below issue when you’re trying to start the concurrent manager

 

target node/queue unavailable

 

Solution :

If unnecessary nodes are added in your FND, you need to clear the nodes and then update with current nodes

 

  1. EXEC FND_CONC_CLONE.SETUP_CLEAN;

commit;

2.  Run autoconfig on all tiers (db, apps & web tier)

3. clean all pending requests using cmclean.sql

4. start the services and check everything started or not

 

Still not started , you’re facing same issue,

do the below

 

update fnd_concurrent_queues set target_node=’target node name’,control_code=null;

 

commit;

 

start the services from front end

deactivate internal manager and activate internal manager,

and then Start the concurrent manager using adcmctl.sh

 

If conflict resolution manager not started due to same issue , Please update the above command and the deactivate and activate internal manger and then start the concurrent manager services.

 

 

creating local repository yum install linux

Follow the below procedure

[root@ebs-app-xx-xx u01]# yum repolist

[root@ebs-app-xx-xx u01]# yum list installed | grep createrepo

createrepo.noarch                         0.9.9-28.el7             @anaconda/7.5

[root@ebs-app-xx-xx u01]# createrepo /u01/Packages/

 

[root@ebs-app-xx-xx u01]# cd /etc/yum.repos.d

[root@ebs-app-xx-xx yum.repos.d]# ls -tlr

total 4

-rw-r–r–. 1 root root 358 Nov  4 22:55 redhat.repo

Now, create custom repository

[root@ebs-app-xx-xx yum.repos.d]# cat custom.repo

[customrepo]

name=Custom Repository

baseurl=file:///u01/Packages/

enabled=1

gpgcheck=0

 

you can check how much packages in the repository

 

[root@ebs-app-xx-xx yum.repos.d]# yum repolist

Now, we can install the rpm packages from the repository

 

[root@ebs-app-xx-xx Packages]# yum install tigervnc-1.8.0-5.el7.x86_64.rpm

 

Issue faced :

 

if  repository giving below error message,then your repository is corrupted.

 

Error Downloading Packages:

InstallMedia: [Errno 256] No more mirrors to try.

 

Solution:

Refer

https://access.redhat.com/solutions/203603

 

Remove this file if there is no X server

Try to start VNC, you may get the below error

 

[root@fusiondev ~]# vncserver

Warning: fusiondev.xxxxx.com:1 is taken because of /tmp/.X1-lock
Remove this file if there is no X server fusiondev.xxxx.com:1

New ‘fusiondev.xxxxx.com:2 (root)’ desktop is fusiondev.xxxxx.com:2

Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/fusiondev.xxxxx.com:2.log

 

Solution :

[root@fusiondev tmp]# rm -rf /tmp/.X1-lock

[root@fusiondev tmp]# rm -rf /tmp/.X11-unix/X1

[root@fusiondev tmp]# vncserver

New ‘fusiondev.xxxxxx.com:1 (root)’ desktop is fusiondev.xxxxxx.com:1

Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/fusiondev.xxxxxx:1.log

REST API Record Type Example

Goal :

Multi data insert into table of EBS 12.1 or 12.2 Enviornment

Follow the procedure :

Steps 1:

Create a package using with record type

 

CREATE OR REPLACE PACKAGE APPS.xxace_ap_restapi
AS
/* $Header: xxace_ap_restapi$ */
/*#
* This custom PL/SQL package can be used to load AP Invoices from restapi test.
* @rep:scope public
* @rep:product AP
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
*/
type xx_invoice_rec_test is record
(p_invoice_type VARCHAR2(30)
,p_invoice_number VARCHAR2(30)

);
type xx_invoice_tbl_test is table of xx_invoice_rec_test INDEX BY BINARY_INTEGER;
PROCEDURE xxace_ap
(
p_invoice_tbl1 in xx_invoice_tbl_test
,p_out_success_1 out VARCHAR2
,p_out_failure_2 out VARCHAR2
)
/*#
* Procedure to Load AP Invoice
* @param p_invoice_tbl1 in parameter 1
* @param p_out_success_1 out parameter 1
* @param p_out_failure_2 out parameter 2
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
* @rep:scope public
* @rep:lifecycle active
*/
;
end xxace_ap_restapi;
/

 

Package Body

CREATE OR REPLACE PACKAGE BODY APPS.xxace_ap_restapi
AS
PROCEDURE xxace_ap (p_invoice_tbl1 IN xx_invoice_tbl_test,
p_out_success_1 OUT VARCHAR2,
p_out_failure_2 OUT VARCHAR2)
IS
l_count NUMBER := 0;
–l_inv_seq NUMBER:= AP_INVOICES_INTERFACE_S.NEXTVAL;
excp EXCEPTION;

v_count NUMBER := 0;

V_PRE NUMBER;
V_AMT NUMBER;
v_cur VARCHAR2 (100);
BEGIN
FOR i IN p_invoice_tbl1.FIRST .. p_invoice_tbl1.LAST
LOOP
p_out_success_1 := NULL;
p_out_failure_2 := NULL;
v_count := v_count + 1;

p_out_success_1 :=
p_invoice_tbl1(i).p_invoice_number|| ‘no data’
|| ‘ ‘
|| v_count;

IF p_invoice_tbl1(i).p_invoice_number IS NULL
THEN
p_out_failure_2 := ‘Invoice Number is Missing.’;
— RAISE EXCP;
ELSIF p_invoice_tbl1(i).p_invoice_type IS NULL
THEN
p_out_failure_2 := ‘Invocie Type is Missing.’;
— RAISE EXCP;
END IF;

BEGIN
INSERT
INTO xxace.XXACE_CMS_AP_INV_STG (staging_id,
invoice_type,
invoice_number)
VALUES (
xxace_ap_stg_seq.NEXTVAL,
CASE
WHEN UPPER (p_invoice_tbl1(i).p_invoice_type) LIKE
‘CREDIT NOTE%’
THEN
‘Standard’
WHEN UPPER (p_invoice_tbl1(i).p_invoice_type) LIKE
‘DEBIT NOTE%’
THEN
‘Debit Memo’
ELSE
p_invoice_tbl1(i).p_invoice_type
END,
p_invoice_tbl1(i).p_invoice_number);
EXCEPTION
WHEN EXCP
THEN
ROLLBACK;
END;
END LOOP;

COMMIT;

END xxace_ap;
END xxace_ap_restapi;
/

 

Pls File : xxace_ap_restapi.pls

CREATE OR REPLACE PACKAGE xxace_ap_restapi
AS
/* $Header: xxace_ap_restapi $ */
/*#
* This custom PL/SQL package can be used to load AP Invoices from restapitest.
* @rep:scope public
* @rep:product AP
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
*/
type xx_invoice_rec_test is record
(p_invoice_type VARCHAR2(30)
,p_invoice_number VARCHAR2(30)

);
type xx_invoice_tbl_test is table of xx_invoice_rec_test INDEX BY BINARY_INTEGER;
PROCEDURE xxace_ap
(
p_invoice_tbl1 in xx_invoice_tbl_test
,p_out_success_1 out VARCHAR2
,p_out_failure_2 out VARCHAR2
)
/*#
* Procedure to Load AP Invoice
* @param p_invoice_tbl1 in parameter 1
* @param p_out_success_1 out parameter 1
* @param p_out_failure_2 out parameter 2
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
* @rep:scope public
* @rep:lifecycle active
*/
;
end xxace_ap_restapi;

 

 

Step 2:

create Business Entity using Application Developer

Business Entity : XXAPTEST_INT

Step 3: PLS File Upload

copy xxace_ap_restapi.pls to /tmp & $AP_TOP/patch/115/sql

cd $AP_TOP/patch/115/sql

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=intuser ap:patch/115/sql:xxace_ap_restapi.pls:12.0=/tmp/xxace_ap_restapi.pls -outdir=$AP_TOP/patch/115/irep/patch/115/sql/

cd $AP_TOP/patch/115/irep/patch/115/sql/

$FND_TOP/bin/FNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xxace_ap_restapi_pls.ildt

Step 4:

Deploy REST Service using Integrated SOA Gateway

Internal name : xxace_ap_restapi

and give grant

 

Step 5: Testing the REST API (REST API Chrome Client )

 

 

Body :

 

{
“xxace_ap”: {
“@xmlns”: “http://XXXXXXX.com:8065/webservices/rest/xxace_ap_restapi/xxace_ap/&#8221;,
“RESTHeader”: {
“@xmlns”: “http://xxxxxxxxxcom:8065/webservices/rest/xxace_ap_restapi/xxace_ap/&#8221;,
“Responsibility”: “20-***_**_USER”,
“RespApplication”: “SQLAP”,
“SecurityGroup”: “STANDARD”,
“NLSLanguage”: “AMERICAN”
},
“inputParameters”: {
“P_INVOICE_TBL1”:
{
“attributes1” : {“P_INVOICE_TYPE”: “CREDIT NOTE”,
“P_INVOICE_NUMBER”: “12345test” },
“attributes2” : {“P_INVOICE_TYPE”: “DEBIT NOTE”,
“P_INVOICE_NUMBER”: “12345testnew” },
“attributes3” : {“P_INVOICE_TYPE”: “DEBIT NOTE”,
“P_INVOICE_NUMBER”: “12345testnewAPI” }
}
}
}
}

 

POST Data Send

Output :

 

Check the tables inserted or not

 

 

NOTE :

  1. Parsing parameter should be “RECORD TYPE SEQUENCE” which  you written the query RECORD TYPE  in Packages
  2. Parsing the VALUE in REST API Client , you should send like ” attribute1,attribute2,attribute3… this is common name”

Manual Checking :

 

DECLARE

l_out_success_1   varchar2 (30);

l_out_failure_2   varchar2 (30);

l_invoice_data    xxace_ap_restapi.xx_invoice_tbl_test;

BEGIN

l_invoice_data (1).p_invoice_type := ‘Type1’;

l_invoice_data (1).p_invoice_number := ‘Inv001’;

xxace_ap_restapi.xxace_ap (p_invoice_tbl1    => l_invoice_data,

p_out_success_1   => l_out_success_1,

p_out_failure_2   => l_out_failure_2);

 

DBMS_OUTPUT.put_line (‘ l_out_success_1 ‘ || l_out_success_1);

DBMS_OUTPUT.put_line (‘ l_out_failure_2 ‘ || l_out_failure_2);

END;

 

Master Note for Handling Oracle Database Corruption Issues

Very useful document

 

Master Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)

Enable SOA Log for EBS 12.1 and 12.2

EBS 12.1

$INST_TOP/ora/10.1.3/j2ee/oafm/config/oc4j.properties

Add the lines

SOA_ENABLE_STANDALONE_LOGGING=TRUE

 

Bounce the adoafmctl.sh

and then log file will generate $INST_TOP/soa/SOALog.log

 

EBS 12.2 :

How to Generate File ISGLog.log for SOAP Services in Release 12.2.X? (Doc ID 2008707.1)

Please be note, you need to see the logfile when the transaction are incoming to server

/u01/EBSLEBUAT/fs2/FMW_Home/user_projects/domains/EBS_domain_LEBDEV/servers/oafm_server1/logs