Rotate Oracle Application Server logs

How to rotate Oracle Application Server logs

We are using the Oracle10g Application Server version 10.1.2.2.0. As the logfiles generated on OAS are occupying the space some times we need to stop the OAS to clear the log files. Alternatively we can set the parameters on the OAS config to rotate the log files.

We can achieve the log rotation using Oracle Diagnostic Logging (ODL). Follow the below steps to rotate the logs.

1. Go to the $ORACLE_HOME/j2ee/oc4j_bi_forms/config
2. Take a backup of the application.xml file.
3. Find the below lines on the application.xml file.



and change it to



Note: specify the max-file-size and max-directory-size values appropriately.

4. Take a backup of the j2ee-logging.xml file in the same directory
5. Add the below code into the log loader

Check the top 10 space using files and the directories

To identify the top 10 space using files and directories use the du commnad as below.

du -a /opt/oracle | sort -n -r | head -n 10

If you want to list n or more files then change the value 10 to the required number of files. eg: 30

Unix - Delete files older than 7 days

We are using IBM AIX servers. We wanted to delete the log files older than 7 days to reclaim the space.

First i need to find the files older than 7 days and then remove them.

$find . -mtime +7 -exec rm -f {} \;

Here i have used find command to find the files in current directory("." means current directory) alternatively you can specify a directory path like "/opt/oracle/admin/log"

And used the parameter -mtime to specify the number of days where i have given +7.

If you specify 7 then seven or more days old files are selected
If you specify +7 then 8 or more days old files are selected.

Then execute the rm command with the -exec option.

FRM-92101: There was a failure in the forms server during startup

Today my client raised a support request as Oracle Forms throws a FRM-92101 error.

FRM-92101: There was a failure in the forms server during startup. This could happen due to invalid configuration. Please look into the web-server log file for details.

I checked and restarted the OAS on command line and was confident that this would have fixed the issue but didn't.

I again restarted the Oracle Application Server(OAS) from the OAS console and Oracle forms were working fine.

So today's lesson i learned is don't rely only on Command line :-)

Consistent export using datapump

In older export method we can specify consistent=y parameter in export commands or in parameter file for export.

In datapump we can achieve the consistent export using flashback_time parameter as below

expdp system/password@TEST directory=TEST_DIR DUMPFILE=TEST_expfull.dmp full=y LOGFILE=expfull.log FLASHBACK_TIME=\"TO_TIMESTAMP\(to_char\(sysdate,\'DD-MM-YYYY HH24:MI:SS\'\),\'DD-MM-YYYY HH24:MI:SS\'\)\"

Remember to create the directory before executing the expdp command.

Oracle Application Server is up but agent shows down

I installed Oracle Enterprise Manager agent on Oracle Application Server. The OAS is listed on the Grid control immediately after installation of the agent. However on the OEM Grid Control the status is shown as down but the Oracle Application Server is up.

I checked the components, all seems to be up on the OAS and also on OEM Grid Control but the server status is shown as down on OEM.

Do you face similar issue? Then here is the solution

1. Go to Agent Home on the Oracle Application Server(OAS)
2. Under AGENT_HOME/sysman/admin/scripts/ and take backup of formsinit.pl file
3. Goto OAS_HOME/sysman/admin/scripts/ and copy the formsinit.pl file
4. Paste the formsinit.pl file to AGENT_HOME/sysman/admin/scripts/
5. Stop and start the agent as below
C:>emctl stop agent
C:>emctl start agent
C:>emctl status agent

Now check the OEM grid control, the OAS status will be up and running...

Oracle Failsafe

Today i got an opportunity to work on Oracle FailSafe... The concept wise i looked it as some what similar to RAC that is virtual IP, clusterware installation, primary and seconday configuration and switching to secondary node for failover etc..

I was initially scared and motivated myself to take it as a challenge and an opportunity to learn something new today on Oracle.

The Oracle FailSafe was so simple mostly uncheck or tick the checkbox, click option button etc.. It is very user friendly and simple but a good fun learning experience.

Thought of sharing one of the task of shutdown and database recovery task with you..

1. Before shutdown of the database, stop the monitoring of the oracle Failsafe.
a. On the failsafe server, Goto start, then programs and Oracle - OfsHome33
b. On the Oracle File Safe Manager, select the Clusters then Server which you need to manage and then expand Cluster Resources
c. Select the database, on the right side, click the policies tab
d. Under Restart Policy, select the option "Do not restart the resources on the current node"
e. And under Failover Policy, uncheck the box for "If the resource fails and is not restarted, failover the group.

Thats it...then all ususal oracle database steps, execute your shutdown command connected to the database using sqlplus.

And recover the database from your backup. Once finished with the database side, donot forget to undo the changes on the Oracle Failsafe.

To enable Oracle Failsafe for the database,

Under Restart Policy as specified above, now select the option, "Attempt to restart the resource on the current node" with your configuration of no of attempts with the seconds.

And also check the box for "If the resource fails and is not restarted, fail over the group".

Done...

ORA-00600: [kmgss_activate_granule_1] Database Instance Crash

Today one of our database crashed due to ORA-00600: internal error code, arguments: [kmgss_activate_granule_1].

I have checked the logs and identified that the database was crashed due to dynamic change of the parameter DB_KEEP_CACHE_SIZE.

After execution of the statement

ALTER SYSTEM SET db_keep_cache_size='100M' SCOPE=BOTH;

Internal Errors started appearing on the alert log ORA-00600: internal error code, arguments: [kmgss_activate_granule_1], [], [], [], [], [], [], []

and followed by

MMAN: terminating instance due to error 822

ORA-00822: MMAN process terminated with error.

The above crash can also happen in the database due to bug on Oracle Server Enterprise Edition Version: 10.2.0.1 to 10.2.0.4.

The problem can occur due to dynamic changes of the parameters
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

The solution suggested by Oracle is to upgrade to 11g or higher releases or to apply 10.2.0.5 patchset.

Error: java.sql.SQLException: ORA-01722: invalid number

Today i received an error on the emoms.trc file on Oracle Enterprise Manager Grid control. The error is:

WARN eml.XMLLoader LoadFiles.680 - Marking the file for retry : 50000909090.xml after receiving exceptionjava.sql.SQLException: ORA-01722: invalid number

2009-10-05 05:00:35,878 [XMLLoader0 500000909090.xml] ERROR eml.XMLLoader markFileAsError.824 - Failed to load 50000909090.xml even after 2 attempts; moving to errors directory. Error: java.sql.SQLException: ORA-01722: invalid number

This is a problem with the OMS Loader. The OMS fails to load xml to repository with an error ORA-01722: invalid number.

This OMS error is a bug in Enterprise Manager Grid Control - Version: 10.2.0.1.0 to 10.2.0.3.0.

The solution is to upgrade the OEM to 10.2.0.4 or 10.2.0.5 by applying the patch. Or apply the verrsion 10.1.0.4.2 of patch 4488715 to fix this particular problem.

I decided to upgrade the OEM to 10.2.0.4.

Oracle10g Deprecated parameters

Max_enabled_roles parameter is deprecated on Oracle10g. This parameter is included only for backward compatibility.

This parameter is specified in Init.ora file to limit the maximum number of roles a user can have enabled.

ftp files using shell script or windows batch file

How to ftp files using shell script or windows batch file?

The below script helps to ftp file from remote server to the local machine. You can ftp using the below script on unix and windows.

To ftp from windows

Save the below code to a file ftpfile.bat and execute the batch script. Change the directory path of the file, username, password and remote server name on the script.

@echo off
echo user oracle> ftpftp.dat
echo password>> ftpftp.dat
echo bin>> ftpftp.dat
echo cd /u01/oradata>>ftpftp.dat
echo lc I:\data>>ftpftp.dat
echo get fileabc.dmp>> ftpftp.dat
echo bye>> ftpftp.dat
ftp -n -s:ftpftp.dat

To ftp from Unix

Save the above code to a file ftpfile.sh and execute the script. Change the directory path of the file, username, password and remote server name on the script.

delete or drop a database

How to delete / drop a database?

To drop a database from batch file

echo connect / as sysdba >xyz.tmp
echo shutdown abort; >>xyz.tmp
echo startup mount exclusive restrict; >>xyz.tmp
echo drop database; >>xyz.tmp
echo exit; >>xyz.tmp;

sqlplus.exe /nolog @xyz.tmp
del xyz.tmp

To drop a database from shell script

echo connect / as sysdba >xyz.tmp
echo shutdown abort; >>xyz.tmp
echo startup mount exclusive restrict; >>xyz.tmp
echo drop database; >>xyz.tmp
echo exit; >>xyz.tmp;

sqlplus.exe /nolog @xyz.tmp
rm xyz.tmp

consistent database export using datapump

How to take consistent database export using datapump?

expdp system/xxxx@TESTDB directory=TEST_DIR DUMPFILE=TEST_expfull.dmp LOGFILE=expfull.log FLASHBACK_TIME=\"TO_TIMESTAMP\(to_char\(sysdate,\'DD-MM-YYYY HH24:MI:SS\'\),\'DD-MM-YYYY HH24:MI:SS\'\)\"

FRM-92050: Failed to connect to the server

FRM-92050: Failed to connect to the server:/forms/lservlet-1 java.io.IOException:Server returned HTTP response code 500 for URL

or 500 Internal server error "Too many open files"

Many users received this FRM-92050: Failed to connect to the server:/forms/lservlet-1 error from yesterday. The error appeared on and off.

This error seems to be a bug.

The cause of the error is as given below

The form runtime does not release the configuration files like environment files. The config files are locked and the new connections are trying to open these files at the same time. This causes the error "Too many open files"

Solution:

Oracle suggests to set a hidden parameter(undocumented parameter) for this bug.

"Waittime" parameter sets the time in milliseconds for the Forms applet to wait before retrying with the request. The default value (500 mS) should suit most deployments. How ever if your windows server cannot cope with the load then increase the value of this parameter. eg: 5000 milliseconds(5 seconds).

Metalink reference - Doc ID: 390176.1

To set the parameter on the OAS,

1.Go to $ORACLE_HOME\j2ee\OC4J_BI_Forms\applications\formsapp\formsweb\WEB-INF\ directory and open web.xml file in notepad and add the parameter.


2. Save the file and open the formsweb.cfg file and add WaitTime=5000

3. Save and restart the OC4J_BI_FORMS on the Oracle Application Server.

Gather schema stats

Gather schema stats

exec dbms_stats.gather_schema_stats (ownname => 'SCOTT', cascade =>true, estimate_percent => dbms_stats.auto_sample_size);

where ownname is the schema name and cascade +> true will include indexes.


Gather table stats


exec dbms_stats.gather_table_stats( -
ownname => 'SCOTT', -
tabname => 'EMP', -
estimate_percent => 100, -
method_opt => 'for all columns size skewonly', -
cascade => true -
);

FRM-41213: Unable to connect to the Report server

FRM-41213: Unable to connect to the Report server

If the form received the FRM-41213 error, then check the report server

http://oasserver:7778/reports/rwservlet/showjobs?server=reportservername

If the report showed the REP_56055: Exceed max connections allowed: 20 error message then

Increase the maxconnect parameter value appropriately on the console, report server -> edit configuration

ORA-00353: log corruption near block

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 1210 change 25869322 time 03/27/2009
20:19:21
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10g\ORADATA\ORCL\REDO03.LOG'

Action: Do recovery with a good copy of the log (If available) or do incomplete recovery up to the indicated change or time.

restart the reports server on Oracle Application Server using command line

To restart the reports server on Oracle Application Server using command line

$ORACLE_HOME/dcm/bin/dcmctl restart -co OC4J_BI_Forms -v

Where ORACLE_HOME is the OAS home.

reset ias_admin password on Oracle Application Server

How to reset ias_admin password on Oracle Application Server?

1. Stop the application server control using emctl

On Unix:
ORACLE_HOME/bin/emctl stop iasconsole

On Windows:
Stop the Oracle processes on the services on Windows Administrator tools.

2. Open the jazn-data.xml file from the below location
ORACLE_HOME/sysman/j2ee/config/

3. Search for ias_admin

example:
ias_admin
{903}7sp13cyQ3KeO8U6U4iq+MD42D+WBy6nz8ebS=

4. Enter the new password with exclamation (!) as shown below
!password007

5. Remember the below points for your new password
Minimum five alphanumeric characters
Atleast one number

6. Start the Application Server Control.

Check Oracle Application Server Status

How to check Oracle Application Server Status?

On Windows services, check the status of OracleASControl and OracleProcessManager.

And for Oracle Process Monitor(OPMN) on command prompt execute opmnctl status command as shown below

ORACLE_HOME\opmn\bin>opmnctl status

Processes in Instance: OAS.XYZ.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 7688 | Alive
HTTP_Server | HTTP_Server | 22196 | Alive
WebCache | WebCache | 2332 | Alive
WebCache | WebCacheAdmin | 2340 | Alive
OC4J | home | 2372 | Alive
OC4J | OC4J_BI_Forms | 2364 | Alive
OC4J | OC4J_BI_Forms | 2380 | Alive
OC4J | OC4J_BI_Forms | 2396 | Alive
OC4J | OC4J_BI_Forms | 2388 | Alive
DSA | DSA | N/A | Down



The dcm, HTTP Server, WebCache, OC4J status should be alive.

start dcm-daemon manually

How to start dcm-daemon manually?

Follow the below steps to start the dcm daemon manually

1. On the command promt go the oas opmn directory
$OAS_HOME/OPMN/BIN

2. Execute the command opmnctl to start the dcm-daemon

$OAS_HOME/OPMN/BIN>opmnctl startproc ias-component=dcm-daemon

3. To check the status

$OAS_HOME/OPMN/BIN>opmnctl status

REP-52251: Cannot get output of job ID

REP-52251: Cannot get output of job ID

This is a undocumented bug. the cause is SSO turned on for one component only, either Forms or Reports. This bug applies to Oracle Application Server version 10.1.2.2

By default, the SSO is turned off for Oracle Forms and turned on for Oracle reports.

The solution suggested by Oracle is to either enable SSO for both Forms and reports or disable SSO for both forms and reports.

Rman Unregister database

I have taken a cold backup of my "test" database. The current archive log sequence is as below
Oldest online log sequence 1053
Next log sequence to archive 1057
Current log sequence 1057

This database is registered with the rman catalog.

After using the database for 10 days i wanted to restore the database to my old flat file backup. But current log sequence is as below

Oldest online log sequence 1105
Next log sequence to archive 1109
Current log sequence 1109

The current archive log sequence is higher than the restored database log sequence So the rman backup with catalog will fail.

So as i have registered the database with rman catalog. i need to resync the database.

Connect to the rman catalog and target database and execute the below commands

rman>unregister database;

rman>register database;

These commands will unregister and re-register the database in the rman catalog.

Now check the latest backup in the catalog database

rman>list backup;

The lastest backup shown is the backup taken before the cold backup which is resynced with the database controlfile.