ORA-16038 ORA-19809 ORA-00312

Database startup fails:

ORA-16038: log one sequence 3144 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312 online log 1 thread 1 <path to redo log file>

In the alert.log:
ORA-16014 signalled during: ALTER DATABASE open …
ORA-19815: WARNING: db_recovery_file_dest_size of xxx bytes is 100.00% used, and has 0 remaining bytes available.

solution :

support id : Flash Recovery Area Full – Database Fails to Open ORA-16038 (Doc ID 337258.1)

creating a pluggable database from an existing non-CDB Using the DBMS_PDB package

There are three ways of creating a pluggable database from an existing non-CDB:
• Using the DBMS_PDB package to generate metadata and then creating the pluggable database
• Data Pump (using the transportable tablespace feature)
• GoldenGate replication

Note :

When using the DBMS_PDB package to convert a non-CDB to a pluggable database, the non-CDB must be Oracle12c or higher.

Take care of your Non CDB and CDB chraceter set (more...)

ORA-23515: materialized views and/or their indices exist in the tablespace

When I am trying to drop the tablespace, I am getting below error

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution :

Step 1: Find the materialized views and/or their indices

SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql

SQL>select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’XXXXXXX’);

SQL>spool off

Step 2:  Drop (more...)

flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let (more...)

APP-RG-09518 : An error occurred while creating a database link

When creating a database link in General Ledger FSG Transfer Program to copy FSGs from one instance to another the following error may be encountered:

APP-RG-09518 : An error occurred while creating a database link

Create a dblink like below

GL –> setup–> database links

Database name : SID

Description : null

connect string : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ipaddress)(PORT=1586))(CONNECT_DATA=(service_name=xxxxxxx)(instance_name=xxxxxx)))

domain name : aceins.com  (Ensure that there is no dash (-) in the domain name and the domain is (more...)

oracle applications r12 auto start on linux

Step 1: connect as a database os user

su – oradev

startDB.sh edit and saved

. /u01/EBSDEV/db/tech_st/11.2.0/EBSDEV_ebsdev.env
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addbctl.sh start
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh start EBSDEV

stopDB.sh edit and saved

. /u01/EBSDEV/db/tech_st/11.2.0/EBSDEV_ebsdev.env
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addbctl.sh stop
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh stop EBSDEV

give execute permission chmod 750 for both file

Step 2: connect as applmgr os user

startAPP.sh edit and saved


How to get the list of users assigned with responsibilities as per Operating Unit

Query :

SELECT hou.NAME,fpov.profile_option_value org_id,frv.responsibility_name, d.full_name,a.USER_NAME, b.START_DATE,b.end_date
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv,apps.FND_USER a,apps.FND_USER_RESP_GROUPS_all b,apps. per_all_people_f d,apps.FND_RESPONSIBILITY_TL res
fpov.level_value = frv.responsibility_id and b.responsibility_id = res.responsibility_id
and a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID and a.employee_id=d.person_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = ‘MO: Operating Unit’
AND fpov.profile_option_id (more...)

Why users or sysadmin login is taking time

If users or sysadmin user trying to login in r12, Its taking time for login process. The problem most probably its is something to do with profile option. Please make sure the debug and diagnostics is not enable at the user level for sysadmin user or users.

Linux Shell Script To Monitor Space Usage and Send Email


LC_ALL=C df -hP | column -t | grep -vE ‘^Filesystem|tmpfs|cdrom’ | awk ‘{ print $5 ” ” $1 }’ | while read output;
echo $output
usep=$(echo $output | awk ‘{ print $1}’ | cut -d’%’ -f1 )
partition=$(echo $output | awk ‘{ print $2 }’ )
if [ $usep -ge 90 ]; then
echo “Running out of space ”$partition ($usep%)” on $(hostname) as on $(date)” |
mailx -s “Alert: Almost out (more...)

Disable cache obiee 11g

You can able to disable the cache from Fusion middleware enterprise manager console. No need to manul edit in NQSConfig.ini file


Go to :

Business Intelligence –> coreapplication –> select Capacity Management tab –> Select Performance tab –> click “Lock and Edit configuration”  –> deselect the cache enabled –> apply –> activate changes

Restart the BI services