RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

RMAN backup failed with the below error message and its require archive log files to complete the RMAN backup.

Error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 01/24/2018 03:35:09
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/arch/1_282613_769193473.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

This issue will happen If in case archive logs was deleted or corrupted.

Fix:

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

OR

If you are using catalog database to maintain RMAN repository instead of controlfile, you can try following command:

RMAN> resync catalog;

Run the backup again.

How to Rotate or Purge Listener Log Data to Avoid Large listener.log File?

How to rotate or backup or rename listener.log or purge listener log data to avoid large listener.log file?

Below are the possible ways to rotate/rename listener.log files.

1) Stop the listener on a regular basis then you can simply rename the file at the OS level, then on next listener startup, a new one will be created.

OR

2) If you have a 24X7 environment and the listener can not be stopped, then you can use the following steps to rename / move the file without having to restart it:

LSNRCTL>
LSNRCTL> set current_listener <listenername>
LSNRCTL> set log_status off

In another window rename the log file. Then return to previous window

LSNRCTL> set log_status on

which will create a new and empty listener.log file which will continue logging. 


 OR

 3) Follow the document here:

Note 135063.1   How To Change the Listener Log Filename Without Stopping the Listener

How to move RAC Database ORACLE_HOME from one location to another location

Below are the steps to move the RAC database ORACLE_HOME from one location(filesystem) to another location.

This post for database home only and does not apply to CRS or GI home.

Execute all the following as database user:

Solution 1:

1. Install a new set of RAC binaries with OUI into a new home.
2. Apply all patches that have been installed in the old home, use "opatch lsinventory" to compare the old and new home once all patches are applied.
3. Copy over init{ORACLE_SID}.ora in dbs directory from the old home to the new home on all nodes.
4. Copy over network files (listener.ora, tnsnames.ora, and sqlnet.ora) in network/admin directory from the old home to the new home on all nodes.
5. Shutdown the database with srvctl from the old database home: srvctl stop database -d <dbname>
6. Modify the database resource as with srvctl from the old database home: srvctl modify database -d -o <new-database-home>
7. Start the database with srvctl from the new database home: srvctl start database -d <dbname>


Solution 2: (Clone)

1. Copy existing RAC binaries to a new location and keep the same ownership/permission on any node: cp -rp <old-database-home> <new-database-home>
2. Execute:
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2

$ perl $ORACLE_HOME/clone/bin/clone.pl '-O"CLUSTER_NODES={<comma separated listed of hostnames>}"' '-O"LOCAL_NODE=<this host’s hostname>"' ORACLE_BASE=<your ORACLE_BASE> ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=<your inventory name> '-O-noConfig' "

Refer to: https://docs.oracle.com/cd/E11882_01/rac.112/e41960/clonerac.htm

3. Extend the new home to other nodes: <new-database-home>/oui/bin/addNode.sh
4. Go to Step3 of "Solution 1"

ORA-00304: requested INSTANCE_NUMBER is busy

ORA-00304: requested INSTANCE_NUMBER is busy

On starting upon instance  in 2 Node RAC Database,We got the below error message.

SQL> startup;
ORA-00304: requested INSTANCE_NUMBER is busy

We have identified the background sessions are still hanging at the OS level.

Solution:

Solution is to Kill the hanging sessions at the os level and restart the instance.

ps -ef | grep -i DATABASE_SID

kill -9 pid1 pid2

after killing the hanging session , we are able to startup the instance without any issues.

How to find the Weblogic Admin Console URL Location in Oracle OEM?

 We can find the details from setupinfo.txt file. This file is created during the installation time.

 Location: $ORACLE_HOME/oms/install/setupinfo.txt 

 Output: 

Use the following URL to access:

        1. Enterprise Manager Cloud Controls URL: https://test.oracle.com:7802/em
        2. Admin Server URL: https://test.oracle.com:7102/console

The following details need to be provided during the additional OMS install:

        1. Admin Server Hostname: test.oracle.com

        2. Admin Server Port: 7102

How to find the OPP log file location in oracle application 11i/R12.1 and R12.2

Below script to find the OPP log file location in oracle E-Business Suite.

Script:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &&request_id;

Growth of oracle database size in day, week and month wise

Below query to get the database size growth in day, week and month wise.

Query:

set linesize 210
set linesize 210
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.K / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*30,2) || ' MB' "Growth MONTH",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*30,3) || '% MB' "Growth MONTH in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS K FROM dba_free_space) FREE
GROUP BY FREE.K;

Query to get the Custom Tables List in oracle application

Below query to get the list of custom tables in oracle application.

Query:

select object_name,
   object_type
from all_objects
where 1=1
and object_type='TABLE'
and status='VALID'
and object_name like 'XXCUST%';

Query to get list of Custom Form List in oracle application

Below query to get the list of Custom Form List in oracle application.

Query:

SELECT  DISTINCT forms.form_name,
         formstl.user_form_name,
         func.function_name,
         func.user_function_name,
         fm.menu_name,
         menu.prompt menu_prompt,
         menu.description,
         restl.responsibility_name
        -- FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions_VL FUNC,
         fnd_menu_entries_VL MENU,
         FND_MENUS FM,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE     1=1
         and forms.form_id = formstl.form_id
         and func.form_id = forms.form_id
         and menu.function_id = func.function_id
         and menu.menu_id=fm.menu_id
         and res.menu_id = menu.menu_id
         and res.responsibility_id = restl.responsibility_id
         and UPPER(forms.form_name) like 'XXCUST%'      
         order by 1;

Query to get list of Custom Concurrent Programs in oracle applications

Below query to get the list of Custom Concurrent Programs in oracle applications.

Query:

SELECT   DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",
fa.application_name,
fcp.concurrent_program_name conc_short_name,
decode( fef.execution_method_code, 'H','Host',
'I', 'PLSQL Stored Procedure',
'P', 'Report',
'L', 'SQL Loader',
'Q','SQL*Plus',
'A','Spawned',
'K','Java Concurrent Program',
'B','Request Set Stage Function',
'S','Immediate',
'J','Java Stored Procedure',
'M','Multi-Language Function',
fef.execution_method_code) "execution method",
fef.executable_name,
fcp.description "Concurrent Program Description",
fef.executable_name"Executable Name",
fef.description "Executable Description",
fef.execution_file_name ,
fcp.enabled_flag
,FCP.CREATION_DATE
FROM fnd_executables_form_v fef,
                fnd_concurrent_programs_VL fcp,
                fnd_application_tl fa
WHERE fcp.application_id=fef.application_id
and fa.application_id=fcp.application_id
AND fef.executable_id=fcp.executable_id
AND UPPER(fcp.user_concurrent_program_name) like 'XXCUST%'
ORDER BY 4;

Primary database error ORA-07217: sltln: environment variable cannot be evaluated

Error:

Primary database throw below error while trying to transport archive log to standby database

ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;
Errors in file /oracle/diag/rdbms/<oracle_sid>/trace/<oracle_sid>arc2_2894.trc:
ORA-07217: sltln: environment variable cannot be evaluated.
ARC2: FAL archive failed with error 7217. See trace for details
Errors in file /oracle/diag/rdbms/mdmprd/mdmprd/trace/<oracle_sid>_arc2_2894.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing

Fix:

$ORACLE_SID needs to be replaced by proper value

Modify log_archive_format to correct value and restart the redo transport.

ORA-04024: self-deadlock detected while trying to mutex pin cursor

Error:

Database errors when trying to open with the following:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 4
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x2CD828600

Fix:

Apply the below patch to resolve this issue.

Patch 21372829.

fs_clone Error 1 occurred while Executing txkADOPValidation script

Error:

Lines #(73-77):
    [PROCEDURE] Calling: /u01/app/applmgr/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl
    [EVENT]     Log: /u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test
    [UNEXPECTED]Error occurred running "perl /u01/app/applmgr/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl  -contextfile=/u01/app/applmgr/TEST/fs1/inst/apps/TEST_test/appl/admin/TEST_test.xml -patchctxfile=/u01/app/applmgr/TEST/fs2/inst/apps/TEST_test/appl/admin/TEST_test.xml -phase=fs_clone -logloc=/u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test -promptmsg=hide"
    [UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on test
[PROCEDURE] [START 2017/04/27 17:18:23] Unlocking sessions table

Lines #(74-78):
    [EVENT]     Log: /u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test
    [UNEXPECTED]Error occurred running "perl /u01/app/applmgr/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl  -contextfile=/u01/app/applmgr/TEST/fs1/inst/apps/TEST_test/appl/admin/TEST_test.xml -patchctxfile=/u01/app/applmgr/TEST/fs2/inst/apps/TEST_test/appl/admin/TEST_test.xml -phase=fs_clone -logloc=/u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test -promptmsg=hide"
    [UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on test
[PROCEDURE] [START 2017/04/27 17:18:23] Unlocking sessions table
[STATEMENT] Unlocking ad_adop_sessions table for test with wait interval of 60 seconds and number of tries 2

Fix:
To fix this issue test the following to upload the missing PATCH context file to the database:

1. Get the value of your patch context file.

    1.1. Source your PATCH file system

    1.2. Echo the value of $CONTEXT_FILE and take note of it. You will use this value in step #3.

2. Source the RUN filesystem

3. Execute the following command on the run filesystem to upload the patch context file to the database.

$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer \
action=upload \
contextfile=/u01/app/applmgr/TEST/fs2/inst/apps/TEST_test/appl/admin/TEST_test.xml \
logfile=/tmp/patchctxupload.log

Note: the context file parameter should point to the physical location of the patch context file in your system

4. Run the following query and it should return one entry for the recently uploaded context file.

select distinct(PATH) from FND_OAM_CONTEXT_FILES where NAME not in ('TEMPLATE','METADATA','config.txt') and CTX_TYPE='A' and (status is null or upper(status) in ('S','F')) and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'patch';

Oracle Application R12 forms not opening in Linux

When we are trying to access forms in Oracle apps R12.1 in Linux got the below error.

Error:

Fix:

When you done with the plugin installation, issue about:plugins and check whether your installed jre plugin is showing in mozilla. if it does you can access the forms. 

To know the recommended jre plugin version issue the below : 
grep plugin $CONTEXT_FILE 
<!-- JDK plugins --> 
<sun_plugin_ver oa_var="s_sun_plugin_ver">1.6.0_07</sun_plugin_ver> 
<sun_plugin_type oa_var="s_sun_plugin_type">jdk</sun_plugin_type> 

Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110621 Oracle/3.6-1.0.1.el5_6 Firefox/3.6.18 

Download and install the below : (Make sure you os bit version when you download) http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase6-419409.html#jre-6u33-oth-JPR

Install ---> jre-6u33-linux-x64.bin 

Since my operating ssystem were 64 bit we must create soft link to /usr/lib64 folder instead of /usr/lib 
go to /usr/lib64/mozilla/plugins 

Create softlink as below : 
ln -s /home/oracle/jre1.6.0_33/lib/amd64/libnpjp2.so . 

libnpjp2.so -> /oracle/jre1.6.0_33/lib/amd64/libnpjp2.so 

Now you  can able to access the forms in Linux.

error while loading shared libraries: libXtst.so.6: cannot open shared object file: No such file or directory

Error:

Concurrent program error with the following message.

error while loading shared libraries: libXtst.so.6: cannot open shared object file: No such file or directory.

Fix:

Install the following RPM's to resolve the issue.

yum install libXext.x86_64
yum install libXrender.x86_64
yum install libXtst.x86_64

GConf Error: Failed to contact configuration server; some possible causes are that you need to enable TCP/IP networking for ORBi

Error:

GConf Error: Failed to contact configuration server; some possible causes are that you need to enable TCP/IP networking for ORBit, or you have stale NFS locks due to a system crash. See http://projects.gnome.org/gconf/ for information. (Details - 1: Failed to get connection to session: Failed to connect to socket /tmp/dbus-QS6NzEDBEh: Connection refused)

In the /var/log/messages

gconfd (root-21343): Bad permissions 777 on directory /tmp/gconfd-root
gconfd (root-21343): Failed to get lock for daemon, exiting: Directory /tmp/gconfd-root has a problem, gconfd can't use it

Fix:

1.Change permissions to 700  on directory /tmp/gconfd-root
2. Restart the VNC server.

RC-50004: Fatal: Error occurred in ApplyDatabase:

While installing application R12.1.1, we got the below error.

Error:

RC-50004: Fatal: Error occurred in ApplyDatabase:

Control file creation failed

Cannot execute configure of database using RapidClone

RW-50010: Error: - script has returned an error:   1
RW-50004: Error code received when running external process.  Check log file for details.
Running Database Install Driver for TEST instance

Additional errors recorded in the "addlnctl.txt"

lsnrctl: error while loading shared libraries: /u01/DEV/db/tech_st/11.1.0/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Fix:

Following steps should be followed:

a) Switch SELinux from the default "Enforcing" mode that it is running in, to the "Permissive" mode. Contact Unix/Linux Admin for the same.

b) Clean up the File System (DB Tier, Application Tier & Inventory). Review the Note: How To Locate the Log Files and Troubleshoot Rapid Install for Release 12.0.x and 12.1.x (Doc ID 452120.1) for Cleanup instructions.

c) Run Rapidwiz.

IO Error: Got minus one from a read call vendor code 17002

When we are trying to connect database by using Sql developer, we got below error.

Error:

IO Error: Got minus one from a read call vendor code 17002

Fix:

You can follow these steps to avoid the error:

1. Go to directory $ORACLE_HOME/network/admin
2. Make sure you have backup of sqlnet.ora
3. Modify sqlnet.ora file with the parameter: (tcp.validnode_checking = no)
4. If you don't want to disable this, you can put the machine names as follows:  tcp.invited_nodes=(machine1, machine2)
5. Bounce the listener or reload the listner (lsnrctl reload)

License Manager Showing Asia/Pacific Localizations (JA) as 'Not Licensed'?

License Manager Showing Asia/Pacific Localizations (JA) as 'Not Licensed'.

How to get it Licensed ?

Please do the following:

• Go to SYSADMIN responsibility / Oracle Application manager / License Manager / Country Specific Localization.
• Select Singapore
• Retest your issue.

Below link to know Licensed enable or not.

http://krishnasudagani.blogspot.in/2017/04/how-to-find-asiapacific-localizations.html 

How to find Asia/Pacific Localizations (JA) Installed or not in Oracle E-Business Suite (R12.1 / R12.2)

Below script to find the Asia/Pacific Localizations (JA) Installed or not in Oracle E-Business Suite instance.

Script:

SELECT SUBSTR(a.application_name, 1,40) product_name
, a.application_id
, SUBSTR(application_short_name, 1,4) short_name,
DECODE(b.status,'I','INSTALLED',
DECODE(b.status,'S','SHARED',
DECODE(b.status,'N','NOTINSTALLED',b.status))) status
FROM fnd_application_vl a, fnd_product_installations b
WHERE a.application_id = b.application_id
and a.application_short_name like Upper('ja')
and upper(a.application_name) like upper('%asia%')
ORDER BY 1;

Output: