oracle password file issue

There is a online help from a old fellow which it’s oracle RAC env meet the error root in corrupt of orapwd file.This kind of issue can be fixed by following guide:

source from:http://www.adp-gmbh.ch/ora/admin/password_file.html

Oracle’s password file

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.
Default location and file name
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.
Deleting a password file
If password file authentication is no longer needed, the password file can be deleted and the init parameter remote_login_passwordfile set to none.
Password file state
If a password file is shared or exclusive is also stored in the password file. After its creation, the state is shared. The state can be changed by setting remote_login_passwordfile and starting the database. That is, the database overwrites the state in the password file when it is started up.
A password file whose state is shared can only contain SYS.
Creating a password file
Password files are created with the orapwd tool.
Adding Users to the password file
Users are added to the password file when they’re granted the SYSDBA or sysoper privilege.

SYS@ora10> show user;
USER is "SYS"
SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

SYS@ora10> grant SYSDBA to rene;

Grant succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
RENE                           TRUE  FALSE

SYS@ora10> grant SYSOPER to rene;

Grant succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
RENE                           TRUE  TRUE

SYS@ora10> revoke SYSDBA from rene;

Revoke succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
RENE                           FALSE TRUE

SYS@ora10> revoke SYSOPER from rene;

Revoke succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

–EOF–

Firewall、DCD、TCP Keep alive

from :http://blogread.cn/it/article/5198?f=wb

在以前我写的一篇文章《Oracle与防火墙》中提到,网络防火墙会切断长时间空闲的TCP连接,这个空闲时间具体多长可以在防火墙内部进行设置。防火墙切断连接之后,会有下面的可能:

 

  • 切断连接之前,连接对应的Oracle会话正在执行一个耗时特别长的SQL,比如存储过程而在此过程中没有任何数据输出到客户端,这样当SQL执行完成之后,向客户端返回结果时,如果TCP连接已经被防火墙中断,这时候显然会出现错误,连接中断,那么会话也就会中断。但是客户端还不知道,会一直处于等待服务器返回结果的状态。

 

从上面的前面2种情况来看,防火墙切断数据库TCP连接,引起的后果就会有: Continue reading “Firewall、DCD、TCP Keep alive”

upgrade oracle 11.2.0.3 RAC to 11.2.0.4

1.enviroment information

two nodes RAC on ASM based on oracle enterprise linux 6

crs version:11.2.0.3
rdbms version:11.2.0.3

2.precheck works
run the script “runcluvfy.sh” to verify if the two nodes is ready for upgrade
you can find the script runcluvfy.sh in the grid software package

3.prepare the directory
I will use the out-of-space upgrade,so I need to prepare the directory in advance.
out-of-space upgrade is recommended by oracle,prepare the seperated directory for the new GI home and RDBMS home Continue reading “upgrade oracle 11.2.0.3 RAC to 11.2.0.4”

srvctl: line xxx: /bin/java: No such file or directory

it’s quit strange,after opatching,the srvctl went wrong:

[oracle@node1 bin]$ srvctl
/opt/app/oracle/product/10.2.0/db_1/bin/srvctl: line 188: /bin/java: No such file or directory

it’s different from the situation of bug 3937317.

after comparing the content of srvctl,I find some missing value such as:

//there should be values in “JREDIR” and “JLIBDIR”

------------------------------------------------
CHOME=/local/oracle/app/OraHome_1/crs
OHOME=/local/oracle/app/OraHome_1/db_1
if [ "X$CHOME" != "X$OHOME" ]
then
    case $ORACLE_HOME in
        "") echo "****ORACLE_HOME environment variable not set!"
            echo "    ORACLE_HOME should be set to the main"
            echo "    directory that contains Oracle products."
            echo "    Set and export ORACLE_HOME, then re-run."
            exit 1;;
    esac
else
    ORACLE_HOME=/local/oracle/app/OraHome_1/crs
    export ORACLE_HOME
fi

# External Directory Variables set by the Installer
JREDIR=/local/oracle/app/OraHome_1/db_1/jdk/jre
JLIBDIR=/local/oracle/app/OraHome_1/db_1/jlib

# jar files
-------------------------------------------------

while there are no values in the srvctl where the issue happened,there should be

# External Directory Variables set by the Installer
JREDIR=
JLIBDIR=

//adding the values.attention,it’s different in $ORACLE_HOME/bin/srvctl and $ORACLE_CRS_HOME/bin/srvctl
//there were diffenrent,take that!

FOR:$ORACLE_HOME/bin/srvctl

# External Directory Variables set by the Installer
JREDIR=/opt/app/oracle/product/10.2.0/db_1/jdk/jre
JLIBDIR=/opt/app/oracle/product/10.2.0/db_1/jlib

FOR:$ORACLE_CRS_HOME/bin/srvctl

# External Directory Variables set by the Installer
JREDIR=/opt/app/oracle/product/10.2.0/crs/jdk/jre
JLIBDIR=/opt/app/oracle/product/10.2.0/crs/jlib

//problem solved
–EOF–

GSD offline in 10g RAC

The function of GSD (10g and above) is to service requests for 9i RAC management clients and therefore when there are no 9i databases present, there is nothing for GSD to do. Consequently, there will be no impact on a RAC cluster if GSD is offline and 9i is not used.

You may meet the situation that the GSD resource is offline in CRS stack,you can leave it alone.But you may meet the problem of long time running when you restart the CRS deamon,you can speed it up by the following trick as MOS note “GSD Is Used Only if 9i RAC Database is Present (Doc ID 429966.1)” described.

For 10g,you can follow this:

Disable GSD (pre 11.2)
After confirming that there are no 9i databases being used you can disable GSD by adding 'exit 0' after the initial comments in the script $ORACLE_HOME/bin/gsdctl where $ORACLE_HOME is the home from which nodeapps are running (i.e. crs home).

#case $ORACLE_HOME in  
# "") echo "****ORACLE_HOME environment variable not set!"  
# echo " ORACLE_HOME should be set to the main"  
# echo " directory that contains Oracle products."  
# echo " Set and export ORACLE_HOME, then re-run."  
# exit 1;;  
#esac  
exit 0 ## Manually added as a temporary workaround 

finally,after you fix this issue,the startup of CRS will come much more quickly.

–EOF–

How To Enable Archive Mode In RAC

How to enable the archive mode in three nodes RAC environment,this is what the page will talk about.

ref doc:How To Enable/Disable Archive Log Mode in Real Application Cluster Environment (Doc ID 235158.1)

1.the current information of RAC

[oracle@node3 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....a1.inst application    ONLINE    ONLINE    node1
ora....a2.inst application    ONLINE    ONLINE    node2
ora....a3.inst application    ONLINE    ONLINE    node3
ora.aaa.db     application    ONLINE    ONLINE    node1
ora....E1.lsnr application    ONLINE    ONLINE    node1
ora.node1.gsd  application    ONLINE    ONLINE    node1
ora.node1.ons  application    ONLINE    ONLINE    node1
ora.node1.vip  application    ONLINE    ONLINE    node1
ora....E2.lsnr application    ONLINE    ONLINE    node2
ora.node2.gsd  application    ONLINE    ONLINE    node2
ora.node2.ons  application    ONLINE    ONLINE    node2
ora.node2.vip  application    ONLINE    ONLINE    node2
ora....E3.lsnr application    ONLINE    ONLINE    node3
ora.node3.gsd  application    ONLINE    ONLINE    node3
ora.node3.ons  application    ONLINE    ONLINE    node3
ora.node3.vip  application    ONLINE    ONLINE    node3

2.prepare the arch directory for on each node.(3 directories for each node) Continue reading “How To Enable Archive Mode In RAC”

db user fail logon counts

oracle 10g中,缺省的profile中有10次fail logon attemps,如何查看消耗了几次”失败登录”呢?

user$记录了其中的信息,lcount /* count of failed login attempts */

也就是,lcount记录了”失败的尝试登录次数”

SQL> select name,lcount from user$ where name='TEST';

NAME                               LCOUNT
------------------------------ ----------
TEST                                    0

当前,test用户的失败登录次数为0,人为制造一个失败登录记录。

SQL> conn test/dadada
ERROR:
ORA-01017: invalid username/password; logon denied

查看test用户的失败登录次数记录。

SQL> select name,lcount from user$ where name='TEST';

NAME                               LCOUNT
------------------------------ ----------
TEST                                    1

再使用test登录,输入正确的密码,制造成功登录后,lcount会归零。

SQL> conn test/test
Connected.
SQL> conn / as sysdba
Connected.

SQL> select name,lcount from user$ where name='TEST';

NAME                               LCOUNT
------------------------------ ----------
TEST                                    0

–EOF–

oracle 11203 RAC on OEL 6 using udev based on ASM

Oracle 11G RAC在OEL6平台上的部署与OEL5基本差不多,但仍然有两个需要额外注意的地方:
1.建议使用udev的方式来管理裸磁盘分区
其实在oel5平台上,我仍然建议使用linux的udev来管理设备,而不是asmlib。而到了oel6(rhel6)平台上,asmlib干脆没有了。

2.建议安装oracle-rdbms-preinstall rpm包,搞定基本的安装前参数配置
oel5平台上,可以借鉴oracle-validated包来搞定一些安装前的配置工作,例如:/etc/sysctl.conf文件中的参数设置。而到了oel6上,那个rpm包变成了oracle-rdbms-preinstall,可以google一下,即可得到答案。 Continue reading “oracle 11203 RAC on OEL 6 using udev based on ASM”

oracle 10g rac add node

目的就是为现有的一个10205双节点RAC集群再添加一个node,从当前的两个节点扩充至三个节点

1.环境的检查

首先查看双节点上os、disk space等信息。

当前rac集群的一些信息。

[oracle@node1 ~]$ olsnodes -p
node1 node1-priv
node2 node2-priv
[oracle@node1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....a1.inst application ONLINE ONLINE node1
ora....a2.inst application ONLINE ONLINE node2
ora.aaa.db application ONLINE ONLINE node1
ora....E1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application ONLINE ONLINE node1
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip application ONLINE ONLINE node1
ora....E2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application ONLINE ONLINE node2
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip application ONLINE ONLINE node2
[oracle@node1 ~]$

1.1 os版本
[oracle@node1 ~]$ echo $ORACLE_HOME
/opt/app/oracle/product/10.2.0/db_1

[oracle@node1 ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel r on an m

再准备一个os,版本为oel5u5。 Continue reading “oracle 10g rac add node”

oracle instance and listener auto start

这里记录一下oracle单实例情况下的instance和listener的自动启动,因为RAC环境中,当os启动的时候,crs会协调好自启动的事情,不用管理员额外操心。在win平台,instance和listener是以服务的形式存在于os上,缺省也是自动随着os启动的。linux环境中,管理员需要额外的操心,写个简单的脚本,让instance和listener随着os能启动起来。

$ORACLE_HOME/bin中有这么一个文件,dbstart。这个其实就是oracle准备好的现成启动脚本,其中包含了:instance、listener部分,甚至包含了asm instance部分。管理员只需将dbstart脚本中做很少的改动,然后放到linux启动顺序执行的脚本中即可,或者放到/etc/rc.local中。

su - oracle -c "$ORACLE_HOME/bin/dbstart"

除了/etc/oratab中将”开关”至于”Y”上,dbstart中需要额外注意两部分。

listener部分:将ORACLE_HOME_LISTNER赋予相应的值即可,这部分负责监听程序的启动。

# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=/home/oracle/product/10.2.0
if [ ! $ORACLE_HOME_LISTNER ] ; then
  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
  echo "Usage: $0 ORACLE_HOME"
else
  LOG=$ORACLE_HOME_LISTNER/listener.log

  # Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
  # a different ORACLE_HOME for each entry in the oratab.
  export ORACLE_HOME=$ORACLE_HOME_LISTNER

  # Start Oracle Net Listener
  if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
    echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1
    $ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &
    export VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`
  else
    echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
  fi
fi

instance部分:确保$ORACLE_HOME参数正确即可。

# Starts a Database Instance
startinst() {
  # Called programs use same database ID
  export ORACLE_SID

  # Put $ORACLE_HOME/bin into PATH and export.
  PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH
  # add for bug # 652997
  LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib ; export LD_LIBRARY_PATH
  PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
  SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
  SPFILE1=${ORACLE_HOME}/dbs/spfile.ora

  echo ""
  echo "$0: Starting up database "$ORACLE_SID""
  date
  echo ""

  checkversionmismatch

  # See if it is a V6 or V7 database
  VERSION=undef
  if [ -f $ORACLE_HOME/bin/sqldba ] ; then
    SQLDBA=sqldba
    VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
      /SQL*DBA: (Release|Version)/ {split($3, V, ".") ;
      print V[1]}'`
    case $VERSION in
      "6") ;;
      *) VERSION="internal" ;;
    esac
  else
    if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
      SQLDBA=svrmgrl
      VERSION="internal"
    else
      SQLDBA="sqlplus /nolog"
    fi
  fi

  STATUS=1
  if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf ] ; then
    STATUS="-1"

可以手工运行dbstart,看是否能正确的把instance和listener启动起来。

–EOF–