ORA-00257: archiver error. Connect internal only, until freed

Today I faced this issue. The users were unable to login to Oracle Apps. When I checked the system the archive logs SAN volume was full. The database was not able to write the online logs to destination  path. I tried to connect to database using SQL Plus and it gave me the following error:

ORA-00257: archiver error. Connect internal only, until freed

I took the following steps to resolve the issue.

• Tried to delete old archive logs using RMAN but rman was also not able to connect
• Then I deleted the old logs using OS command and freed some space.
• Switch the log as

              SQL> alter system switch logfile;

• Check the current status of archive logs as

             SQL> archive log list;

• Then finally I bounced the Apps and DB services.

How to join GL tables with XLA (SubLedger Accounting) tables

GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS (je_batch_id)

GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je_header_id)

GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFERENCES (je_header_id, je_line_num)

GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)

XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id)

XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (application_id, event_id)

XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)

The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.

The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

Time difference calculation between two dates

SELECT user_name,
             TO_CHAR (creation_date, 'dd-Mon-yyyy hh24:mi:ss') creation_date,
             TRUNC (SYSDATE - creation_date) "Days",
             TRUNC (MOD ( (SYSDATE - creation_date) * 24, 24)) "Hours",
             TRUNC (MOD ( (SYSDATE - creation_date) * 24 * 60, 60)) "Miutes",
             TRUNC (MOD ( (SYSDATE - creation_date) * 24 * 60 * 60, 60)) "Seconds",
             TO_CHAR (SYSDATE, 'dd-Mon-yyyy hh24:mi:ss') AS system_date,
             SYSDATE - creation_date "Total days",
             (SYSDATE - creation_date) * 24 "Total Hours",
             (SYSDATE - creation_date) * 26 * 60 "Total Minutes",
             (SYSDATE - creation_date) * 26 * 60 * 60 "Total Seconds"
  FROM fnd_user;

Force termination of a concurrent request

SQL> update fnd_concurrent_requests
set status_code='X', phase_code='C'
where request_id=xxxxxx;

SQL> commit;

TO CHECK WORKFLOW VERSIONS, PENDING JOBS AND USER RESPONSIBILITIES ISSUES

Run the below sql script and check the output.

$FND_TOP/sql/wfver.sql

Implement ISNUMERIC function in SQL

a)
CREATE OR REPLACE FUNCTION isNumeric1 (p_num VARCHAR2)
 RETURN VARCHAR2
IS
 result VARCHAR2 (50);
BEGIN
 SELECT LENGTH (TRANSLATE (TRIM (p_num), ' +-.0123456789', ' ')) INTO result FROM DUAL;
 RETURN result;
END;
/
      It will return null if numeric, return count if non numeric characters otherwise.

b)
CREATE OR REPLACE FUNCTION isNumeric2 (p_num VARCHAR2)
 RETURN NUMBER
IS
 result NUMBER;
BEGIN
 SELECT INSTR (TRANSLATE (P_NUM, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X') INTO result FROM DUAL;

 RETURN result;
END;
/

    It returns 0 if it is a number, 1 if it is non-numeric.

UNLOCK A LOCKED DATABASE USER ACCOUNT

a) login on Database server as oracle user

b) set enviornment variable of database

c) sqlplus /nolog

d) conn / as sysdba

e) alter user user_name account unlock

f) exit

Check the file version

To check the file version use any of the following methods.

a) $ ident path/file_name

b) $ strings -a path/file_name |grep '$Header'

All Receiving Transactions Fail with RVTTH-445: Subroutine ORA-20001: -: XLA-95103

Error:

RVTTH-445: Subroutine ORA-20001: -: XLA-95103: An internal error occurred. Please inform your
system administrator or support representative that: An internal error has occurred in the program
xla_e

Solution 1:

1. Manually re-compile following database packages even if they are valid to ensure dependent called objects are compiled :

RCV_ACCRUALACCOUNTING_GRP
RCV_ACCEVENTS_PVT
RCV_SEEDEVENTS_PVT

SQL> alter package RCV_ACCRUALACCOUNTING_GRP compile;
SQL> alter package RCV_ACCRUALACCOUNTING_GRP compile body;

SQL> alter package RCV_ACCEVENTS_PVT compile;
SQL> alter package RCV_ACCEVENTS_PVT compile body;

SQL> alter package RCV_SEEDEVENTS_PVT compile;
SQL> alter package RCV_SEEDEVENTS_PVT compile body;
2. Next, relink receiving executables - logged into the application tier with the environment is set

cd $PO_TOP/bin
$ adrelink.sh force=y ranlib=y "PO RCVOLTM"
$ adrelink.sh force=y ranlib=y "PO RVCTP"
3. Stop and Start the Receiving Transaction Managers to call the newly recompiled executables.
(System Administrator > Concurrent > Manager > Administer (Deactivate and then Activate))

4. Test and confirm the error is no longer present.

Solution 2:

1. Resolve INVALID Objects
2. Relink receiving executables
   cd $PO_TOP/bin
   $ adrelink.sh force=y ranlib=y "PO RCVOLTM"
   $ adrelink.sh force=y ranlib=y "PO RVCTP"
3. Bounce Receiving Transaction Manager:
   - $ ps -ef | grep RCVOLTM (to see how many processes are running)
   -Deactivate Receiving Transaction Manager
   (System Administrator > Concurrent > Manager > Administer)
   -$ ps -ef | grep RCVOLTM (repeat until no processes are running)
   -Restart Receiving Transaction Manager
4. Perform transaction.

Certain Operations from EM dbConsole Fail With 'ERROR: NMO Not Setuid-root (Unix-only)'

1. Stop the dbconcole:

cd <ORACLE_HOME>/bin
emctl stop dbconcole

2. To set the correct permissions:
Run <ORACLE_HOME>/root.sh , logged in as the root user.
OR
Change the permissions of the above executables manually:

- Login as the root user:
- Execute:

For 10G:

# cd <ORACLE_HOME>/bin
# chown root $ORACLE_HOME/bin/nmo
# chmod 6750 $ORACLE_HOME/bin/nmo
# chown root $ORACLE_HOME/bin/nmb
# chmod 6750 $ORACLE_HOME/bin/nmb

For 11G:
# cd <ORACLE_HOME>/bin
# chown root $ORACLE_HOME/bin/nmo
# chmod 4710 $ORACLE_HOME/bin/nmo
# chown root $ORACLE_HOME/bin/nmb
# chmod 4710 $ORACLE_HOME/bin/nmb

Note: It is recommended to fix this issue by running the <ORACLE_HOME>/root.sh script, rather than manually modifying the permissions. The root.sh script makes lot more changes which are needed for ensuring that the Agent is correctly configured on the Unix machine.

3. Re-start the dbconsole:

cd <ORACLE_HOME>/bin
emctl start dbconsole
4. Re-try the operation from the EM dbConsole.

Configuring RMAN Recovery Catalog Using Enterprise Manager DB Console

1)  Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:

$ sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE apps_ts_tools
TEMPORARY TABLESPACE temp1
QUOTA UNLIMITED ON apps_ts_tools;

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;

2)  Creating the Recovery Catalog

Log in to rman and create the catalog schema.Look at this example:

In the below example " prod " is the catalog database connection string. Before creating the recovery catalog make sure to have the tnsnames.ora entry for the catalog database in the target server and the listener must be up and running in the catalog database server.You must be able to connect to the catalog database from sqlplus from the target server.

$ rman catalog rman/rman@prod

RMAN> CREATE CATALOG;

The catalog should be created manually before configuring the recovery catalog through EM.

3)  Login to Enterprise manager dbconsole as SYSDBA user

4)  Recovery catalog can be configured using the RECOVERY CATALOG SETTINGS tab.

The Navigation path is :
-> Availability
-> Backup/Recovery
-> Recovery Catalog Settings

5)  Click on ADD RECOVERY CATALOG and provide the below information

   Host :  yourhost.yourdomain
   Port : 1521
   SID : PROD
   Recovery Catalog Username : rman
   Recovery Catalog Password : rman

6)  Once the above information is processed the database will be registered in the Recovery Catalog.

7)  Now select the option 'USE RECOVERY CATALOG' on the RECOVERY CATALOG SETTINGS page. This will enable the usage of recovery catalog for rman backups.

8)  Now you can schedule a rman backup.This backup will be done using the recovery catalog.

Reference: How To Configure RMAN Recovery Catalog Using Enterprise Manager DB Console. [ID 467969.1]

OPP Log Shows: Unable to determine SMTP server to use: set FND_SMTP_HOST

You have run a concurrent request and you want its output to be emailed on spefied email addrees specified email address on submit request form > delivery options.
But the request in completed in warning. When OPP log was checked there was following exception:

oracle.apps.fnd.cp.opp.PostProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST
 at oracle.apps.fnd.cp.opp.EmailDeliveryProcessor.deliver(EmailDeliveryProcessor.java:66)
 at oracle.apps.fnd.cp.opp.DeliveryProcessor.process(DeliveryProcessor.java:91)
 at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)

To resolve the issue:
1. Go to System Administrator responsibility
2. Navigate to Profile -> System
3. Query the %fnd%smtp% profiles
4. Set the following profile values
    FND: SMTP Host (host name or IP)
    FND: SMTP Port (port number, default 25)