Oracle DBA - Power of knowledge is in sharing - Syed Zulfikar, UK: September 2009

Wednesday 30 September 2009

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.

Friday 25 September 2009

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

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.