Archive for 10/01/2008 - 11/01/2008

How To Recover a Database With Missing Archived Logs

Even if it's difficult to be in such a position, if you can't open your database because it needs recovery and you don't have archivelogs to recover or a cold backup to restore, this situation does not mean you'll say 'bye bye' to your database.
There is an undocumented parameter "_allow_resetlogs_corruption" that you can use in this situation. Setting this parameter TRUE in init.ora file may help you save (maybe not all) data. Procedure includes backing up the database, opening it with the updated pfile, discover the problematic data and try to recover all you can...
Here is a detailed article about the use of _allow_resetlogs_corruption parameter. Thanks to Terry Sutton, the author, for this very useful article which covers this lifesaver topic systematically.
Do not forget that you should not use an undocumented parameter without the support of Oracle.

Starting and Stopping Grid Control Services

To start the grid control you must start the agent and the web components
in the oms home along with the agent in the agent home:

Start everything

startup database
$export ORACLE_HOME=$ORACLE_BASE/OracleHomes/db10g/
$export ORACLE_SID=emrep
$sqlplus / as sysdba
SQL>startup
start listener
$lsnrctl start
start oms server
$ORACLE_BASE/OracleHomes/oms10g/opmn/bin/opmnctl startall
start agent
$ORACLE_BASE/OracleHomes/agent10g/bin/emctl start agent

Stop everything

stop
agent
$ORACLE_BASE/OracleHomes/agent10g/bin/emctl stop agent
stop oms server
$ORACLE_BASE/OracleHomes/oms10g/opmn/bin/opmnctl stopall
shutdown database
$export ORACLE_HOME=$ORACLE_BASE/OracleHomes/db10g/
$export ORACLE_SID=emrep
$sqlplus / as sysdba
SQL>shutdown immediate
stop listener
$lsnrctl stop

Check status

$ORACLE_BASE/OracleHomes/oms10g/opmn/bin/opmnctl status

Processes in Instance: EnterpriseManager0.buyukada
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
HTTP_Server | HTTP_Server | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | home | N/A | Down
OC4J | OC4J_EM | N/A | Down
OC4J | OC4J_EMPROV | N/A | Down
WebCache | WebCache | 22513 | Alive
WebCache | WebCacheAdmin | 22514 | Alive

Start Components one by one

$ORACLE_BASE/OracleHomes/oms10g/opmn/bin/opmnctl startproc ias-component=DSA
$ORACLE_BASE/oracle/OracleHomes/oms10g/opmn/bin/opmnctl startproc ias-component=LogLoader
etc.

Password Verify Function

There is a default password verify function under $ORACLE_HOME/rdbms/admin with filename utlpwdmg.sql. This script creates a password verify function named "verify_function" and alters the default profile with the below attributes:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

verify_function has the following attributes:
- Check if the password is same as the username
- Check for the minimum length of the password (default = 4)
- Check if the password is too simple. A dictionary of words may be maintained and a check may be made so as not to allow the words that are too simple for the password. ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd' words are not accepted as password by default)
- Check if the password contains at least one letter, one digit and one punctuation mark.
- Check if the password differs from the previous password by at least 3 letters.

You can customize this script to have different password verify function attributes, profile attributes and to apply to another profile.

SQLNET.AUTHENTICATION_SERVICES = NONE

Setting "SQLNET.AUTHENTICATION_SERVICES" parameter to "NONE" in sqlnet.ora file will make it not possible to connect to the database without a password as sysdba. (sqlplus / as sysdba)

This parameter may also have the values : NTS for Windows NT native authentication, ALL for all authentication methods.

Authentication Methods Available with Oracle Advanced Security:
  • kerberos5 for Kerberos authentication

  • cybersafe for Cybersafe authentication

  • radius for RADIUS authentication

  • dcegssapi for DCE GSSAPI authentication

If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

Grid Control Agent "Failed to start HTTP listener"

Error:
bash-3.00$ ./emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ....... failed.
Failed to start HTTP listener.
Consult the log files in: ...../OracleHomes/agent10g/sysman/log

Solution:
To prevent the agent from binding to all IP's in the machine:

- Go to AGENT_HOME/sysman/config/emd.properties
Modify this parameter :

AgentListenOnAllNICs=TRUE

To

AgentListenOnAllNICs=FALSE

- Startup the agents now.

If it doesn't work for you, you should also try to change the port in emd.properties file, EMD_URL parameter.

Also look at the trace file at $AGENT_HOME/sysman/log/emagent.trc , if you have an error like "ERROR http: snmehl_connect: failed to create socket: Too many open files (error = 24)" you should check running agent processes (ps -ef |grep agent), kill them and restart agent... If the agent starts but after a while stops itself again, this may be caused by operating system running out of open file handles limit. Check out the kernel which limits the max open file number for a process for your OS.

Description for these parameters:
AgentListenOnAllNIC: Whether the EMD should listen on all NICs on the current host (the default) or just the NIC associated with the hostname in EMD_URL
EMD_URL: EMD main servlet URL

SQL LOADER PERFORMANCE - Using a larger bind array


The bind array is the area in memory where SQL*Loader stores a batch of data to be loaded. When the bind array fills, it is transmitted to the database. Generally, the larger the bind array, the more rows can be loaded in one operation, and the better your performance will be. The bind array size is controlled by the READSIZE and BINDSIZE parameters, and the default size is 64KB (256KB in Oracle9i). Finding the optimal setting for a given load takes a bit of trial and error.

First, increase the READSIZE and BINDSIZE settings (usually, both should be the same). Then, determine the number of rows that will fit within the bind array, and set the ROWS parameter to that value. Run the load. If you see a performance gain, try increasing the bind array size once again. Continue this process until you are comfortable with the performance.

ROWS – BINDSIZE – READSIZE – How it Works?

  • When we are using both ROWS and BINDSIZE options, first sqlldr calculates the space(in bytes) a row can take (based on the column datatypes and the number of columns of the table). And then multiplies that size by the number of ROWS. If that size is less than the BINDSIZE(bind array size) mentioned, it is taking the value of ROWS as commit interval.
  • If the size of the number of ROWS is large that it does not fit in the BINDSIZE value specified, then it uses small number of rows that can fit into the BINDSIZE and then commits at that interval.
  • BINDSIZE value should match with the READSIZE value. If we give very large value for BINDSIZE that exceeds the maximum limit set for READSIZE , it throws an error. If the BINDSIZE value does not exceed max value of Read buffer size(set on the platform), then the Read buffer size is automatically increased to the size of Bind array and it reads that many number of bytes from datafile.
  • The READSIZE parameter is used only when reading data from datafiles. When reading records from a control file, a value of 64 kilobytes (KB) is always used as the READSIZE.
  • The READSIZE parameter lets you specify (in bytes) the size of the read buffer, if you choose not to use the default. The maximum size allowed is 20 megabytes (MB) for both direct path loads and conventional path loads.

DEFINITIONS:

rows : number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all)

bindsize : size of conventional path bind array in bytes (Default 256000)

readsize : size of read buffer (Default 1048576)

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -