How to change NLS_CHARACTERSET to AL32UTF8

Below are steps:

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT;
ORACLE instance started.

Total System Global Area 6.0663E+10 bytes
Fixed Size                  2268032 bytes
Variable Size            5637145728 bytes
Database Buffers         5.4895E+10 bytes
Redo Buffers              128344064 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

Database altered.

ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+ERP_DB/spfileerp.ora_1522172043297' ORA-17503: ksfdopn:10 Failed to open file +ERP_DB/spfileerp.ora_1522172043297 ORA-01034: ORACLE not available ORA-27121: unable to determine size of shared memory segment Linux-x86_64 Error: 13: Permission denied Additional information: 6761 Additional information: 60391427

Issue Type:

SQL> startup mount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+ERP_DB/spfileerp.ora_1522172043297'
ORA-17503: ksfdopn:10 Failed to open file +ERP_DB/spfileerp.ora_1522172043297
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux-x86_64 Error: 13: Permission denied
Additional information: 6761
Additional information: 60391427

Solution:
***********


To place your database in archivelog mode, perform the following steps:

  1. Check listener status.
  2. Check oracle utility permission for both users grid & oracle.
  3. Change permission of oracle utility for both users grid & oracle.
  4. Startup your database.

[grid@myserver]$ cd $GRID_HOME/bin
[grid@myserver]$ chmod 6751 oracle

[oracle@myserver]$ cd $ORACLE_HOME/bin
[oracle@myserver]$ chmod 6751 oracle

Process (index=2,uid=1039335819,pid=14570) time out while waiting for a managed process to stop Log: /opt/erp/product/inst/apps/erp1/logs/ora/10.1.3/opmn/default_group~oafm~default_group~2.log

Solution:

$ $ORACLE_HOME/opmn/bin/opmnctl shutdown
opmnctl: opmn is not running
 
$ cd $ORACLE_HOME/opmn/logs
$ mkdir oracle_backup
$ mv *.* oracle_backup

 cd $ADMIN_SCRIPTS_HOME

# adapcctl.sh start

Issue is fixed.

GI ocssd.log rotation fails and logfile grows to huge size

Solution 1:

The CSSD thread that encountered the LFI-00142 error needs to be restarted to ensure log rotation works again.

Manually deleting the logfile will not resolve the log rotation problem.

1).  Shutdown CRS on the node reporting the problem.

# crsctl stop crs

2).  Once CRS is down,  proceed to manually delete the 'ocssd.l10' file, or copy the logfile to another location if you need to keep a backup.

# rm  $GRID_HOME/log/<hostname>/cssd/ocssd.l10

3).  Startup Clusterware again

# crsctl start crs

Solution 2:

If you don't get downtime, then follow below step. But it is not sure whether issue will resolve.

% echo 0 > ocssd.l10

Please note this does not resolve the log rotation problem but only allows you to free up some space.

Connection issue from

While connecting to Database from client or using DB link if you face below issue , Please follow below mentioned steps.

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

I wrote this piece after a team of operation at my workplace required a partial database. When I prepared what they wanted and sent them a TNS to connect, they informed me that they could not  connect because of our login trigger.

That was quite easy to understand the problem. Our login trigger was recording sessions in a table that resides on a tablespace that I have not restored =)

If you wat to disable all the system triggers in your database, you must set an undocumented parameter in your parameter file as below:

_system_trig_enabled=FALSE

 alter system set "_system_trig_enabled"=FALSE; --- On Database side.

Thanks.

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch Log file:

 Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5931)
StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:644)
StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1088)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
StackTrace: java.lang.reflect.Method.invoke(Method.java:592)
StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:317)
StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:2581)
StackTrace: oracle.opatch.OPatch.main(OPatch.java:626)


Cause:

Linux/Unix:

According to the above  error message, oracle binary ($ORACLE_HOME/bin/oracle) is still active or in use.
It can also  fail with other ORACLE_HOME active files/libraries .
Example:
/u01/app/oracle/10.2.0/bin/tnslsnr
/u01/app/oracle/11.2.0.3/lib/libclntsh.so.11.1

Windows:

Please be aware that before applying patches, you must shutdown the database, listener, sqlplus, EM dbconsole/dbcontrol and any and all processes, including 3rd-party processes, which use files in the ORACLE_HOME.  The error messages in the OPatch log files show that there are one or more programs still running that are using files in the ORACLE_HOME you are trying to patch.

Linux/Unix:

Verify and ensure that all database instances/services that are running from the $ORACLE_HOME being patched are cleanly shutdown.
Also  identify the process that is using these active files/libraries. Then stop the process who is still accessing it and then apply/rollback the patch.

To identify active process, execute

$ fuser -c $ORACLE_HOME

Windows:

If the database and Oracle services are already shutdown, then perhaps it is a 3rd-party program.  If you do not already have it, you should consider installing "Process Explorer" from Microsoft.  After starting procexp.exe, press Ctl-F (Find) and type in one of the DLLs from the "active" list (in the OPatch log). For this example, based on the "active" list shown above, the first DLL to check is orageneric11.dll.  That should tell you what process is using that DLL so you can shut it down. Process Explorer is fairly easy to use, but if you need help with downloading or using it, please contact Microsoft Support as usage of Process Explorer is outside the scope of Oracle Support.

There are a variety of non-Oracle 3rd-party programs which could be using files in the ORACLE_HOME.  One culprit is WMI (Windows Management Instrumentation).  Once you shutdown the 3rd-party process(es) using files in the ORACLE_HOME, and Process Explorer comes back clean, then attempt to apply the patch again and the OUI-67074 error should not reoccur.

Thanks.

How to check Oracle Application URL in 11i and R12 on UNIX prompt

Connect to Application tier with applmgr account.

[appl@prapp1 ~]$cat SID_hostnameapp1.xml| grep http | grep login

How to find Oracle Applications Login URL from database

Execute either of the following query logging into Oracle Applications database from "APPS" user:-

1.
SELECT home_url
  FROM icx_parameters;


2.
SELECT profile_option_value
  FROM fnd_profile_option_values
 WHERE profile_option_id=
   (SELECT profile_option_id                          
      FROM fnd_profile_options WHERE profile_option_name ='APPS_FRAMEWORK_AGENT')                            
   AND level_value = 0;

Thanks