ODI-1228 with ORA-25153: Temporary Tablespace is Empty

Version : 

Oracle DB 11.2.0.4.0 - 64bit Production

Issue:

ETL using ODI failing with following error
   
ODI-1228: Task INT_HDI_ARC_HIERARCHY (Integration) fails on the target ORACLE connection HDI.
Caused By: java.sql.SQLException: ORA-25153: Temporary Tablespace is Empty

Analysis:

The error lookup with oerr command showed this

$ oerr ora 25153
25153, 00000, "Temporary Tablespace is Empty"
// *Cause: An attempt was made to use space in a temporary tablespace with no files.
// *Action: Add files to the tablespace using ADD TEMPFILE command.

Verified that the default tablespace of both DB and the DB user doing the ETL is temp1

SQL>  select property_value from database_properties where property_name like '%DEFAULT_TEMP_TABLESPACE%';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP1

SQL> select TEMPORARY_TABLESPACE from dba_users where USERNAME='HDI_UPGR2';

TEMPORARY_TABLESPACE
------------------------------
TEMP1

But when we select the tablepace name based on dba_temp_files view, TEMP1 is missing and onlt TEMP tablespace is seen as shown below.

SQL> select tablespace_name from dba_temp_files;

TABLESPACE_NAME
------------------------------
TEMP

Reason:

The file is deleted at OS level.

We cannot recreate the TEMP1 tablespace because the entry is still available in data dictionary and only the temp file associated with the tablespace is physically removed. If we try to recreate we will get following error.

ORA-01543: tablespace 'TEMP1' already exists

Solution:

Add a file to the TEMP1 tablespace using ALTER TABLESPACE command as shown here

SQL> alter tablespace temp1 add tempfile '<FILE_PATH>/temp01.dbf' size 100m autoextend on next 1m maxsize unlimited;

Tablespace altered.

Now when we query dba_temp_files, we can get TEMP1 as well.


SQL> select tablespace_name from dba_temp_files;

TABLESPACE_NAME
------------------------------
TEMP
TEMP1

ETL succeded after that

Adding additional managed server in new machine to the existing weblogic cluster

Version: 

WLS 10.3.6 - Clustered environment

Requirement:

Add a 3rd managed server to the existing weblogic cluster setup. Currently it has 1 Admin server and 1 Managed server running on Node 1 and another managed server running on Node 2. Our plan is to add the 3rd Managed server in Node 3 to the cluster.

Note:

Shared Storage: Storage is shared across all the nodes and same OS user/group configuration is used.  If the storage is not shared, then we need to pack the domain to new server and unpack it there.

We can add Managed Servers to a domain/cluster to increase the capacity of the system.  When a Managed Server is added to a cluster, it inherits the applications and services that are targeted to the cluster. So if we have deployed an application on top of the cluster, same would be reflected immediately on the managed server, which was added newly, when it is started up.

Steps 1: Add Managed Server

1. Login to Weblogic admin console URL
2. Click on "Lock & Edit" at the top Left corner of the console URL
3. In the left pane, expand Environment, then select Servers.
4. In the Servers table, click New button - It opens a new page -> Create a New Server - Domain Name
5. Enter the following information in that page
    - Server Name: Give the new Managed Server name (Eg: ManagedServer2)
    - Server Listen Address: Give fully qualified server name with domain here           (eg: myserver.mydomain)
    - Server Listen Port: Give same port number as of the existing managed servers since this is going to run in a different server. If same server, then port number should be different
    - Choose - "Yes, make this server a member of an existing cluster" , if this server is to be part of an existing cluster
6. Click Next
7. Review the details we provided and click Finish

Managed Server is now created. Next step is to create a machine and associate that with this managed server

Steps 2: Create a new Machine

1. Login to Weblogic admin console URL
2. Click on "Lock & Edit" at the top Left corner of the console URL
3. In the left pane, expand Environment, then select "Machines"
4. Click New button in the Machines table. It opens a new page -> Create a New Machine - Domain Name
5. Enter the following information in that page
    - Name: A suitable name for the machine. I normally give the hostname of the server without the domain name here
    - Machine OS: Choose Unix if it is a Unix flavour (Other options are virtual and other)
6. Click Next -> This will open the "Node Manager Properties" page. Enter the following information in that page
    - Type: SSL (protocol to communicate with the Nodemanager)
    - Listen Address: Give fully qualified server name with domain
    - Listen Port: Listen port. 5556 is the default
7. Click Finish

Now add managed server to this machine

Steps 3: Assign Managed Server to the Machine

1. Click on the machine name we just created. - It will open the Settings for <the new Machine> page.
2. Click on the "Servers" tab in that page
3. In the Servers table, click Add
4. Choose "Select an existing server, and associate it with this machine" option and choose the managed server we created above
5. Click Finish

Finally click on "Release Configuration"

Start up the nodemanager directly from the server where it is configured to run.
Verify that the machine status is "Reachable" in Environment => Machines => <Machine Name> => Monitoring tab
Go back to servers => Click control and select the managed server we created above. Press Start

Now we have the managed server running as part of the cluster.

The Java RunTime Environment was not found at /tmp/OraInstall/jdk/jre/bin/java

Version:

Oracle Fusion Middleware Forms and Reports version 11.1.2.1.0 on Linux 64 bit

Issue:

Run installer errors out with java issue

Error:

bash-4.1$ ./runInstaller 
Starting Oracle Universal Installer...

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-03-24_12-33-40AM. Please wait ...The Java RunTime Environment was not found at /tmp/OraInstall2015-03-24_12-33-40AM/jdk/jre/bin/java. Hence, the Oracle Universal Installer cannot be run.
Please visit http://www.javasoft.com and install JRE version 1.3.1 or higher and try again.
: No such file or directory

Analysis:

Java 1.6 update 65 is already installed and JAVA_HOME environment variable is set as well as PATH variable has $JAVA_HOME/bin entry. Verified the settings with which java and java -version commands


Solution:

oraparam.ini file under the software staged directory path /software/FR11121/Disk1/install/linux64 had an entry JRE_SCRATCH_LOC

It was set to 
JRE_SCRATCH_LOC=jdk/jre

Commented out above line using VI editor and saved the file

vi oraparam.ini

#JRE_SCRATCH_LOC=jdk/jre

Reinvoked runInstaller.

This time runInstaller prompted for java path and I gave the already installed path and installer took it


bash-4.1$ ./runInstaller 
Starting Oracle Universal Installer...


Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-03-23_08-06-23AM. Please wait ...
Please specify JRE/JDK location ( Ex. /home/jre ), <location>/bin/java should exist :/install/product/java

[VirtualHost: main] (13)Permission denied: make_sock: could not bind to address 0.0.0.0:80

Version:

Oracle HTTP Server 11.1.1.7.0 in Linux 64 Bit

Issue:

opmnctl startall command errors out when invoked with the OS user who installed the product.

Error:

From $INSTANCE_HOME/diagnostics/logs/OHS/ohs1/ohs1.log the following errors are seen

[2015-03-03T11:08:11.5520+00:00] [OHS] [NOTIFICATION:16] [OHS-9999] [core.c] [host_id: webtier1] [host_addr: *.*.*.*] [pid: 2414] [tid: 139750680717120] [user: oracle] [VirtualHost: main]  caught SIGTERM, shutting down

[2015-03-03T16:38:37.7081+05:30] [OHS] [INCIDENT_ERROR:32] [OHS-9999] [core.c] [host_id: webtier1] [host_addr: *.*.*.*] [pid: 5776] [tid: 140408010676032] [user: oracle] [VirtualHost: main] (13)Permission denied:  make_sock: could not bind to address 0.0.0.0:80

[2015-03-03T16:38:37.7081+05:30] [OHS] [INCIDENT_ERROR:20] [OHS-9999] [core.c] [host_id: webtier1] [host_addr: *.*.*.*] [pid: 5776] [tid: 140408010676032] [user: oracle] [VirtualHost: main]  no listening sockets available, shutting down

Reason:

Seeing the logs, we identified the main reason is Permission denied to use the port 80
Permission denied:  make_sock: could not bind to address 0.0.0.0:80

You will get the following error if you try to start Oracle HTTP Server on a port below 1024 as non-root user

Oracle HTTP Server will not start on ports below 1024 because root privileges are needed to bind these ports.

On a UNIX system the TCP/IP port numbers below 1024 are special in that only processes with root privileges are allowed to listen on those ports.

Solution:

By default, Oracle HTTP Server runs as a non-root user (the user that installed Oracle Fusion Middleware). Therefore, on UNIX systems, if you plan on running Oracle HTTP Server on a privileged port (for example, port 80, 443 etc), you must enable Oracle HTTP Server to run as root user

1. Take a backup of  ".apachectl file" (under $WT_HOME/ohs/bin directory) as the software owner (in my case, oracle) to keep current permissions on the file.

Please note that it is a hidden file (.apachectl). There is an unhidden file apachectl also in same dir.

2. Connect as root user and change to the above directory

[root@webtier1 ~]# cd $MW_HOME/Oracle_WT1/ohs/bin

3. Change permissions of the file using below given 2 commands

chown root .apachectl

chmod 6750 .apachectl

[root@webtier1 bin]# chown root .apachectl

[root@webtier1 bin]# chmod 6750 .apachectl

Now the permission of the .apachectl file would be as follows

-rwsr-s---  1 root   dba  13278 Feb  8  2013 .apachectl

4. Exit root and connect back as the S/W owner user (oracle)

5. Edit httpd.conf file

cd $MW_HOME/Oracle_WT1/ohs/conf

Backup httpd.conf before editing

6. Search for the User/Group strings in the file. Then uncomment or add the installation user details.

For eg: originally in my httpd.conf the entries were

#User USER_TEMPLATE

#Group GROUP_TEMPLATE

</IfModule>

The above lines are modified to

User oracle

Group dba

</IfModule>

where oracle is the user with which I installed the Webtier and dba is the primary group of oracle

7. Start the opmnctl service as S/W owner(oracle)

Data Pump Export workers are waiting on ‘wait for unread message on broadcast channel’

Version:

Oracle 11.2.0.3.0 - Production

Issue:

A database full export job triggered couple of days back is very slow or hung. Around 2 TB of dump files were generated, but it seemed to be hung after that.

The job status is shown as "Executing" but the dump file size showed no progress and stayed at same value for more than a day.

Analysis:

There were no messages or warnings in the alert log file.
Export status showed as executing and there was n't any errors.


Export> status

Job: SYS_EXPORT_FULL_01

  Operation: EXPORT

  Mode: FULL

  State: EXECUTING

  Bytes Processed: 2,104,376,791,712

  Percent Done: 94

  Current Parallelism: 10

  Job Error Count: 0

  Dump File: /backup/prod/DB/orcl/expdp/orcl_datapump_%u.dmp  

An event 10046 trace showed that it is waiting on the following

"wait for unread message on broadcast channel"

There is a support Doc for similar issue. Reason is the statistics are not up-to-date

Solution:

1) Gather statistics on the data dictionary and system tables.

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (null);

2) Restart the EXPDP job 

After gathering statistics. The performance was back to normal.

Restarting explorer.exe process in Windows 2008 server connected remotely

Version: 

Version: Windows Server 2008; Release 2 64 Bit

Issue:

Accidentally killed explorer.exe process via Process Explorer tool in Windows 2008 server. The connection to the server was via Remote Desktop

Killing the explorer resulted in all windows getting closed. No start screen or taskbar either

Solution:

1. Press  <CTRL> <ALT> <END>  keys on the remote desktop session. This command is similar to   <CTRL> <ALT>  <DEL>  on local system , which will open the Task Manager

2. In the task manager window click "New Task" under Applications tab.

3. In the New Task window opened, type "explorer.exe" and  click OK. 

This will open a new instance of explorer

FRM-92101: There was a failure in the Forms Server during startup

Version:

Oracle R12 12.1.3 in OEL 5 64 bit

Issue: 

Forms is not accessible, on accessing forms from URL it threw error. 
All the services were started up using adstrtal.sh script and there was no failures detected at the script startup time

Error:

The following error was reported on accessing forms URL.

FRM-92101: There was a failure in the Forms Server during startup. This could happen due to invalid configuration. 

Analysis:

Checked the forms log files available under $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1. From the application.log, the error messages were identified

15/02/02 23:52:34.437 formsweb: 10.1.3.4.0 Started
15/02/02 23:57:03.843 formsweb: ListenerServlet init()
15/02/02 23:57:05.159 formsweb: Forms session <1> aborted: runtime process failed during startup with errors /u01/E-BIZ/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory

15/02/02 23:57:05.159 formsweb: Forms session <1> exception stack trace:
oracle.forms.engine.RunformException: Forms session <1> failed during startup: no response from runtime process
        at oracle.forms.servlet.RunformProcess.connect(Unknown Source)

Went through the following Oracle support docs, and identified that the error is due to missing RPM
The RPM which was missing is openmotif21

After EBS Fresh Install accessing any Forms Responsibility fails with error "FRM-92101: Forms Server" (Doc ID 1192205.1)
Check RPMs from Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (Doc ID 761566.1) 

Solution:

1) Installed the missing RPM after downloading it form a site. 

In OEL 5, the missing RPM is openmotif21-2.1.30-11.EL5.i386.rpm. you can verify the document ID (402310.1 for 32 bit and 416305.1 for 64 bit) for correct RPM required for your platform. 

The RPM, openmotif21-2.1.30-11.EL5.i386.rpm is not part of the OS distribution media and must be downloaded separately

[root@Appsnode tmp]rpm -ivh openmotif21-2.1.30-11.EL5.i386.rpm
Preparing...                ########################################### [100%]
  1:openmotif21           ########################################### [100%]
[root@Appsnode  tmp]# rpm -qa|grep openmotif21

openmotif21-2.1.30-11.EL5

Note:
For OEL 6, the rpm required is openmotif21-2.1.30-11.EL6.i686.rpm

2) Restart the middletier after applying the package.