Skip to content
Advertisements

Archive for

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.