how to disable trigger in oracle

Here I will explain how to disable trigger on in oracle. Some time we have to disable the trigger on table. Use below query to disable the trigger in oracle database.


Disable all trigger on table

SQL>alter table table_name disable all triggers;


Disable all trigger on schema

We can build script using below sql and execute them on sql prompt.

SQL>spool schema_trigger.sql

SQL>select 'alter trigger ' || table_owner || '.' || trigger_name || ' disable;' from user_triggers;

SQL>@schema_trigger.sql


Disable specific trigger on table

SQL>alter trigger trigger_name disable;


User below query to find the status of trigger

SQL>select status from user_triggers where trigger_name='Trigger_name';

Backup database plus archivelog delete input

In oracle database its very important to manage the archive logs to free up the space on archive log destination for new archive logs. 

Below are some rman commands to delete the old archive log which was already backuped up on disk.


rman target /

RMAN>


run
{
    allocate channel c1 type disk;

    #Backup archivelog oder than 2 day's and delete them.
    backup
    format '/backup01/prod/%d_ARC_%u_%s_%p'
    archivelog until time 'sysdate-2' delete input;
    
    release channel c1;

}

============================================
run {

allocate channel c1 type disk;

 #Delete archielog older than 2 day's

delete archivelog all completed before ‘sysdate-2’;

release channel c1;

}

===============================================
run {
allocate channel c1 type disk;

#Backup archivelog that was not backup.

backup archivelog all not backed up 1 times;

release channel c1;
}

============================================
run{
allocate channel c1 type disk;
sql'alter system archivelog current';
#Backup database plus archivelog older than 2 day's and delete them.
BACKUP
FORMAT '/backup01/prod/%d_DB_%u_%s_%p'
DATABASE
PLUS ARCHIVELOG UNTIL TIME 'sysdate-2'
delete input;
release channel c1;
}

==============================================

Run {

allocate channel c1 type disk;

#Delete archielog older than 5 day's which was backup atleast 1 time.

DELETE ARCHIVELOG UNTIL TIME 'sysdate-5' BACKED UP 1 TIMES TO DEVICE TYPE DISK;

release channel c1;
}

================================================
Use below script to delete noprompt expired archivelog .

Run{
allocate channel c1 type disk;
delete expired archivelog all;
release channel c1; }

or  -------------------------------------------------------

Run{

allocate channel c1 type disk;
delete noprompt expired archivelog all;
release channel c1;
}



Click here for  more information on rman backup of oracle database. If you want to restore the rman backup click here.

adrelink is exiting with status 1 r12

When attempting to apply R12.ATG_PF.B.delta.3 patch 8919491, error appears on relink, 
the following error occurs on relinking executables task. 

ERROR -- adrelink failed in r12

adrelink.log:-

Relink of module "MSCCPP" failed.
Relink of module "MSCMON" failed.
Relink of module "MSCNEW" failed.
Relink of module "MSCNSP" failed.
Relink of module "MSCNSPNM" failed.
Relink of module "MSCPCL" failed.
Relink of module "MSCPDW" failed.
Relink of module "MSCPRG" failed.
Relink of module "MSCSDW" failed.
Relink of module "MSCSLD" failed.
Relink of module "MSCXGCAL" failed.
Relink of module "MSONEW" failed.

----------------------- 
Relink of module "MSCPDW" failed. 
See error messages above (also recorded in log file) for possible 
reasons for the failure. Also, please check that the Unix userid 
running adrelink has read, write, and execute permissions 
on the directory /data03/APPS_8027/apps/apps_st/appl/msc/12.0.0/bin, 
and that there is sufficient space remaining on the disk partition 
containing your Oracle Applications installation. 
.. 
collect2: ld returned 1 exit status 
make: *** 
[/data03/GST_APPS_8027/apps/apps_st/appl/msc/12.0.0/bin/MSCCPP] Error 1 
Done with link of msc executable 'MSCCPP' on Thu Jul 26 10:29:09 IST 2018 
Relink of module "MSCCPP" failed. 
See error messages above (also recorded in log file) for possible 
reasons for the failure. Also, please check that the Unix userid 
running adrelink has read, write, and execute permissions 
on the directory /data03/GST_APPS_8027/apps/apps_st/appl/msc/12.0.0/bin, 
and that there is sufficient space remaining on the disk partition 
containing your Oracle Applications installation. 
Relinking module 'MSCMON' in product msc ... 

adrelink is exiting with status 1 

End of adrelink session
Date/time is  Wed Sep 5 09:33:39 IST 2018
**********************************************************

Line-wrapping log file for readability ...
Done line-wrapping log file.

Original copy is /acedata03/GST_APPS_8027_1/apps/apps_st/appl/admin/GST/log/adrelink.lsv
New copy is /data03/GST_APPS_8027_1/apps/apps_st/appl/admin/GST/log/adrelink.log


An error occurred while relinking application programs.

Continue as if it were successful [No] :

Solution:-

To fix this problem, users are required to replace the following line under the Linux section of the $AD_TOP/bin/adrelinknew.sh:

CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh'

with

CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh -Wl,--noinhibit-exec'

After making this change, users are then required to run the adadmin utility and relink application executables.

Ensure to create a copy of the file before trying any change. Also try this change in Test environment before doing it in a production. 

Create bigfile tablespace

A bigfile tablespace is a tablespace with a single, but very large (up to 4g blocks) datafile. A bigfile tablespace with a typical 8K block can contain a single 32-terabyte datafile. If you’re using a 32K block, it can contain a 128-terabyte datafile. 


SQL> CREATE BIGFILE TABLESPACE bigtbls DATAFILE ‘/data01/prod/bigtbs01.dbf’ SIZE 50G;

If the default tablespace type was set to bigfile at database creation, no need to specify the keyword bigfile in the create tablespace statement. A bigfile tablespace will created by default.

If the default tablespace type was set to bigfile at database creation, but you want to create a traditional (smallfile) tablespace, then specify a createsmallfiletablespace statement to override the default tablespace type for the tablespace that you are creating.

Create tablespace in oracle database.

If you want to increase the size of oracle database. We must create tablespace or increase the size of existing tablespace by resize datafile or add datafile in existing tablespace.

Create Tablespace example.

Check Tablespace size in oracle database.

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
 order by 5 desc;

Check datafile size:-

select file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name='&TablespaceName';

Create tablespace with Single datafile.

SQL>Create tablespace xxcustom datafile ‘/path/prod/xxcustom01.dbf’ size 300m;

Create tablespace with multiple datafiles.

SQL>Create tablespace xxcustom 
           datafile   ‘/path/prod/xxcustom01.dbf’ size 300m, 
                          ‘/path/prod/xxcustom02.dbf’ size 300m,
                          ‘/path/prod/xxcustom03.dbf’ size 300m;

Alter database datafile resize.

If we want to resize the existing data file. We must use Alter database data file resize command.

                          

Before doing resize the data file in oracle database, we will have to check the existing data file size.

SQL>select file_name,bytes/1024/1024 SIZE_IN_MB from 

dba_data_files where tablespace_name=’XXCUSTOM’;

Resize the oracle database data file using below command.

SQL>alter database datafile ‘/path/filename.dbf’ resize 6g;

For additional information on Administering Database Size.Please check. 👉alter tablespace add datafile.

Alter tablespace add datafile

Suppose our database getting full. In that situation we have to increase the size of database by adding datafile in existing tablespace or creating new tablespace along with datafile.

To add datafile in existing tablespace follow the below steps:-

1. Check the existing file in Tablespace:-

SQL>select file_name,bytes/1024/1024 SIZE_IN_MB from dba_data_files where tablespace_name=’XXCUSTOM’;

2. Add datafile:-

SQL> alter tablespace xxcustom add datafile ‘/data01/prod/xxcustom03.dbf’ size 5g;

For additional information on Administering Database size . 👉 Check how to resize the datafile in oracle database.

Weblogic 10.3.6 Patching Error Using BSU - "java.lang.OutOfMemoryError: GC overhead limit exceeded"

While trying to apply the BSU patch, facing below error.

$./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=KRDE
-prod_dir=/data01/apps1225/fs1/FMW_Home/wlserver_10.3

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
        at java.util.HashMap.createEntry(HashMap.java:897)
        at java.util.HashMap.addEntry(HashMap.java:884)
        at java.util.HashMap.put(HashMap.java:505)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.loadPropertyMap(XBeanDataHandler.java:778)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.<init>(XBeanDataHandler.java:99)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.createDataHandler(XBeanDataHandler.java:559)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.getComplexValue(XBeanDataHandler.java:455)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:442)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:464)
        at com.bea.plateng.patch.dao.cat.PatchCatalog.getPatchDependencies(PatchCatalog.java:56)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getInvalidatedPatchMap(PatchCatalogHelper.java:1621)
        at com.bea.plateng.patch.PatchSystem.updatePatchCatalog(PatchSystem.java:436)
        at com.bea.plateng.patch.PatchSystem.refresh(PatchSystem.java:130)
        at com.bea.plateng.patch.PatchSystem.setCacheDir(PatchSystem.java:201)
        at com.bea.plateng.patch.Patch.main(Patch.java:281)

Solution:-

Follow these steps to resolve the issue:

A.==>Go to $FMW_Home/utils/bsu.
B.==>Edit bsu.sh (for UNIX) or bsu.cmd (for Windows).
C.==>Here you will find the following: MEM_ARGS="-Xms256m -Xmx512m"
D.==>Increase these values as needed: for example, to "-Xms2048m -Xmx2048m".

Unable to initialize state monitor. Not able to create new database connection

Unable to initialize state monitor.

oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Not able to create new database connection: FNDSECURITY_APPL_SERVER_ID

In my case FND_NODES table and dbc file both have different SERVER_ID. Once I have updated the dbc file as FND_NODES , issue got resolved.

The APPL_SERVER_ID in the dbc file should match the server_id in FND_NODES table.

cd $FND_SECURE

first of all backup the existing one and modify the correct server id.


Just go under $APPLCSF/log


Check the FNDOPP2330321.txt . Following error will appear as below


Unable to initialize state monitor.
oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Not able to create new database connection: FNDCURITY_APPL_SERVER_ID
        at oracle.apps.fnd.profiles.Profiles.getProfileOption(Profiles.java:1509)
        at oracle.apps.fnd.profiles.Profiles.getProfile(Profiles.java:362)
        at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfileFromDB(ExtendedProfileStore.java:211)
        at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfile(ExtendedProfileStore.java:171)
        at oracle.apps.fnd.profiles.ExtendedProfileStore.getProfile(ExtendedProfileStore.java:148)
        at oracle.apps.fnd.common.logging.DebugEventManager.configureUsingDatabaseValues(DebugEventManager.java:1294)
        at oracle.apps.fnd.common.logging.DebugEventManager.configureLogging(DebugEventManager.java:1149)
        at oracle.apps.fnd.common.logging.DebugEventManager.internalReinit(DebugEventManager.java:1118)
        at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:1085)
        at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:1072)
        at oracle.apps.fnd.common.AppsLog.reInitialize(AppsLog.java:595)
        at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
        at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:570)
        at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:524)
        at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
        at oracle.apps.fnd.cp.util.CPAppsDatabaseContext.<init>(CPAppsDatabaseContext.java:64)
        at oracle.apps.fnd.cp.util.CPContextFactory.makeContext(CPContextFactory.java:56)
        at oracle.apps.fnd.cp.gsf.GSMStateMonitor.init(GSMStateMonitor.java:70)
        at oracle.apps.fnd.cp.gsf.GSMStateMonitor.<init>(GSMStateMonitor.java:62)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.init(GSMServiceController.java:111)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.<init>(GSMServiceController.java:66)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.main(GSMServiceController.java:428)
Caused by: oracle.apps.fnd.common.PoolException: Not able to create new database connection: FND
        at oracle.apps.fnd.security.DBConnObj.<init>(DBConnObj.java:255)
        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.fnd.common.Pool.createObject(Pool.java:1273)
        at oracle.apps.fnd.common.Pool.borrowObject(Pool.java:1027)


Now I was able to see the login page as well...


After that file FNDOPP* file will display the logs as below, once we will stop the all application services and start it again.


FNDOPP2330399.txt:-


Starting GSF service with concurrent process id = 2330399.
Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=10
[Thread-19] Service thread starting up.