CHECK TIMED OUT status for ora.scan1.vip

April 10, 2016 Comments off

Recently i was working on system with two node rac. On one of the node,  ora.scan1.vip is INTERMEDIATE and “CHECK TIMED OUT”.   i resolved using below.

nXXXXXXX1314<grid>crsctl status resource -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.FRA.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.LISTENER.lsnr
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.RDOEV.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.asm
ONLINE ONLINE nXXXXXXX1314 Started
ONLINE ONLINE nXXXXXXX2316 Started
ora.gsd
OFFLINE OFFLINE nXXXXXXX1314
OFFLINE OFFLINE nXXXXXXX2316
ora.net1.network
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.ons
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.registry.acfs
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE OFFLINE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE nXXXXXXX1314
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE nXXXXXXX1314
ora.cvu
1 ONLINE ONLINE nXXXXXXX1314
ora.nXXXXXXX1314.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.nXXXXXXX2316.vip
1 ONLINE ONLINE nXXXXXXX2316
ora.oc4j
1 ONLINE ONLINE nXXXXXXX1314
ora.scan1.vip
1 ONLINE INTERMEDIATE nXXXXXXX1314 CHECK TIMED OUT
ora.scan2.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.scan3.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.utbcop.db
1 ONLINE ONLINE nXXXXXXX1314 Open
2 ONLINE ONLINE nXXXXXXX2316 Open
nXXXXXXX1314<grid>

I did the following steps and resolved the issue.

nXXXXXXX1314<grid>crsctl stop res ora.scan1.vip
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘nXXXXXXX1314’
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘nXXXXXXX1314’ succeeded
nXXXXXXX1314<grid>crsctl status resource -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.FRA.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.LISTENER.lsnr
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.RDOEV.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.asm
ONLINE ONLINE nXXXXXXX1314 Started
ONLINE ONLINE nXXXXXXX2316 Started
ora.gsd
OFFLINE OFFLINE nXXXXXXX1314
OFFLINE OFFLINE nXXXXXXX2316
ora.net1.network
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.ons
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.registry.acfs
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE OFFLINE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE nXXXXXXX1314
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE nXXXXXXX1314
ora.cvu
1 ONLINE ONLINE nXXXXXXX1314
ora.nXXXXXXX1314.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.nXXXXXXX2316.vip
1 ONLINE ONLINE nXXXXXXX2316
ora.oc4j
1 ONLINE ONLINE nXXXXXXX1314
ora.scan1.vip
1 OFFLINE OFFLINE
ora.scan2.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.scan3.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.utbcop.db
1 ONLINE ONLINE nXXXXXXX1314 Open
2 ONLINE ONLINE nXXXXXXX2316 Open
nXXXXXXX1314<grid>crsctl start res ora.scan1.vip
CRS-2672: Attempting to start ‘ora.scan1.vip’ on ‘nXXXXXXX2316’
CRS-2676: Start of ‘ora.scan1.vip’ on ‘nXXXXXXX2316’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN1.lsnr’ on ‘nXXXXXXX2316’
CRS-2676: Start of ‘ora.LISTENER_SCAN1.lsnr’ on ‘nXXXXXXX2316’ succeeded
nXXXXXXX1314<grid>crsctl status resource -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.FRA.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.LISTENER.lsnr
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.RDOEV.dg
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.asm
ONLINE ONLINE nXXXXXXX1314 Started
ONLINE ONLINE nXXXXXXX2316 Started
ora.gsd
OFFLINE OFFLINE nXXXXXXX1314
OFFLINE OFFLINE nXXXXXXX2316
ora.net1.network
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.ons
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
ora.registry.acfs
ONLINE ONLINE nXXXXXXX1314
ONLINE ONLINE nXXXXXXX2316
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE nXXXXXXX2316
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE nXXXXXXX1314
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE nXXXXXXX1314
ora.cvu
1 ONLINE ONLINE nXXXXXXX1314
ora.nXXXXXXX1314.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.nXXXXXXX2316.vip
1 ONLINE ONLINE nXXXXXXX2316
ora.oc4j
1 ONLINE ONLINE nXXXXXXX1314
ora.scan1.vip
1 ONLINE ONLINE nXXXXXXX2316
ora.scan2.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.scan3.vip
1 ONLINE ONLINE nXXXXXXX1314
ora.utbcop.db
1 ONLINE ONLINE nXXXXXXX1314 Open
2 ONLINE ONLINE nXXXXXXX2316 Open
nXXXXXXX1314<grid>

 

 

Categories: RAC, RAC11G., Uncategorized

How to create asmsnmp user in ASM instance(11g).

March 9, 2016 Comments off

While i got a chance to create asmsnmp user in ASM instance which is RAC invironment.

Initially i thought asmsnmp user already exists and tried to change the password. But i found the below when i tried to change password when user does not exits.

njunnode2<grid>sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 8 15:31:19 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> Alter user asmsnmp identified by tibcouat123;
Alter user asmsnmp identified by tibcouat123
*
ERROR at line 1:
ORA-15306: ASM password file update failed on at least one node
SQL>

And found that user does not exists.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
—————————— —– —– —–
SYS TRUE TRUE FALSE

SQL>

Then i checked the presence of password file on both nodes.

njundNODE1<grid>cd $ORACLE_HOME/dbs
njundNODE1<grid>ls -ltr
total 25
-rw-r–r– 1 grid oinstall 2851 May 15 2009 init.ora
-rw-r—– 1 grid oinstall 1536 Jul 11 2014 orapw+ASM.old
-rw-r—– 1 grid oinstall 1536 Nov 4 15:47 orapw+ASM
-rw-rw—- 1 grid oinstall 1780 Mar 4 10:52 ab_+ASM1.dat
-rw-rw—- 1 grid oinstall 1544 Mar 8 10:09 hc_+ASM1.dat
njundNODE1<grid>

njundNODE2<grid>cd $ORACLE_HOME/dbs
njundNODE2<grid>ls -ltr
total 25
-rw-r–r– 1 grid oinstall 2851 Jul 11 2014 init.ora
-rw-r—– 1 grid oinstall 1536 Jul 11 2014 orapw+ASM.old
-rw-r—– 1 grid oinstall 1536 Nov 4 15:49 orapw+ASM
-rw-rw—- 1 grid oinstall 1782 Mar 8 10:09 ab_+ASM2.dat
-rw-rw—- 1 grid oinstall 1544 Mar 8 10:09 hc_+ASM2.dat
njundNODE2<grid>

Now i created the user.

SQL> create user asmsnmp identified by tibcouat123;

User created.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
—————————— —– —– —–
SYS TRUE TRUE FALSE
ASMSNMP FALSE FALSE FALSE

SQL> grant sysdba to asmsnmp;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
—————————— —– —– —–
SYS TRUE TRUE FALSE
ASMSNMP TRUE FALSE FALSE

SQL>

 

Now i checked at OS level for password file details. Interestingly passwordfile has been changed on both nodes.

njundNODE1<grid>ls -ltr
total 25
-rw-r–r– 1 grid oinstall 2851 May 15 2009 init.ora
-rw-r—– 1 grid oinstall 1536 Jul 11 2014 orapw+ASM.old
-rw-rw—- 1 grid oinstall 1780 Mar 4 10:52 ab_+ASM1.dat
-rw-rw—- 1 grid oinstall 1544 Mar 8 10:09 hc_+ASM1.dat
-rw-r—– 1 grid oinstall 1536 Mar 8 15:32 orapw+ASM
njundNODE1<grid>

njundNODE2<grid>ls -ltr
total 25
-rw-r–r– 1 grid oinstall 2851 Jul 11 2014 init.ora
-rw-r—– 1 grid oinstall 1536 Jul 11 2014 orapw+ASM.old
-rw-rw—- 1 grid oinstall 1782 Mar 8 10:09 ab_+ASM2.dat
-rw-rw—- 1 grid oinstall 1544 Mar 8 10:09 hc_+ASM2.dat
-rw-r—– 1 grid oinstall 1536 Mar 8 15:32 orapw+ASM
njundNODE2<grid>s

Please note that password has been changed @ Mar 8 15:32

 

 

Categories: ASM

script to implement 3rd Saturday of the month.

February 16, 2016 Comments off

Today i got a requirement to run particular script on every 3rd Saturday of the month.  I tried to implement through crontab but somehow it did not worked out.  So i tried to write a script.

#################################################

CURR_DAY_WEEK=`date +%a`
CURR_DAY=`date +%d`
if ( [ $CURR_DAY -gt 14 ] && [ $CURR_DAY -lt 23 ] && [ $CURR_DAY_WEEK -eq “Sat” ] )### Third Saturday fals between 15th to 22nd of every month.

#if ( [ $CURR_DAY -gt 7 ] && [ $CURR_DAY -lt 16 ] && [ $CURR_DAY_WEEK -eq “Sat” ] )### Second Saturday

then
{entire script will be placed here.}
fi

#################################################

 

Note:-   This is simple script but i felt to record for future purpose.    You can change as per your requirement.

Categories: Scripts

how to recycle lisetner.log file in oracle.

February 12, 2016 Comments off

Today i came across rotating listener.log file since its size too big. There are different methods available, however i implemented below method.

 

Method 1:-

Script:-

cut and past below to a file called rotate_listener.sh file.

 

###========================Start===========

CURR_TIME=`date +%Y%m%d.%H%M%S`
echo $CURR_TIME
NEW_FILE=”$1_${CURR_TIME}”
echo $NEW_FILE
cp -p $1 $NEW_FILE

if [ -f $NEW_FILE ];
then
# echo “File $NEW_FILE exist.”
echo “” > $1
compress $NEW_FILE
else
# echo “File $NEW_FILE does not exist”
fi
ls -ltr $1_*
###========================END===========

Execution method:-

sh rotate_listener.sh /tmp/listener.log

Method 2:-

Following need to be tested. i just got it from other source but not yet verified.

. /home/grid/db.env
LOGFILE=${2}
DATE_STAMP=`date +%m%d%y%s`
echo $DATE_STAMP
lsnrctl <<END
set current_listener ${1}
set log_status off
exit
END
mv $2 $2.${DATE_STAMP}
ls -ltr $2
lsnrctl <<END
set current_listener ${1}
set log_status on
exit
END
compress $2.${DATE_STAMP}

 

 

 

Categories: 10g, 11g

WARNING OGG-00706 Failed to add supplemental log group on table SCOTT.EMP

February 6, 2016 Comments off

Today i was trying to enable supplemental log data for a table i got the below error.

Error:-

GGSCI (ORA11G01.world.com) 3> add trandata scott.emp

2016-02-06 07:48:26 WARNING OGG-00706 Failed to add supplemental log group on table SCOTT.EMP due to ORA-01031: insufficient privileges SQL ALTER TABLE “SCOTT”.”EMP” ADD SUPPLEMENTAL LOG GROUP “GGS_73201” (“EMPNO”) ALWAYS /* GOLDENGATE_DDL_REPLICATION */.

GGSCI (ORA11G01.world.com) 4>

root cause is:-

gguser does not have alter any table priv on scott.emp table hence the issue.

i given below and worked fine.

============================================

SYS@INDIADB>grant alter any table to gguser;

Grant succeeded.

SYS@INDIADB>

============================================

GGSCI (ORA11G01.world.com) 5> add trandata scott.emp

Logging of supplemental redo log data is already enabled for table SCOTT.EMP.

GGSCI (ORA11G01.world.com) 6>

 

All the best.

 

 

 

Categories: ISSUES

How to change the asm related users(sys,asmsnmp) passowords in 12c.

December 24, 2015 Comments off

Today i came across changing the passwords for sys and asmsnmp users passwords in 12c and i followed the below procedure.   System itself taken care of

 

njunpXXXXXXX<oracle>su – grid
Password:
Oracle Corporation SunOS 5.11 11.2 July 2015
njunpXXXXXXX<grid>asmcmd
ASMCMD> passwd
usage: passwd <user>
help: help passwd
ASMCMD> passwd sys
Enter old password (optional):
Enter new password: *************
ASMCMD> exit
njunpXXXXXXX<grid>sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 23 16:29:56 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

SQL> alter user asmsnmp identified by XXXXXXXXX;

User altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
njunpXXXXXXX<grid>

Also below one also worked for me.

njunpXXXXXXX<grid>asmcmd
ASMCMD> passwd asmsnmp
Enter old password (optional):
Enter new password: **************
ASMCMD>

 

Categories: ASM

How to change the oracle_home for GRID based database.

December 10, 2015 Comments off

Today i was asked to change the oracle home of my database due to naming standards. So i had installed oracle home in new location.

OLD oracle_home:- /local0/app/oracle/product/11.2

NEW oracle_home:- /local0/app/oracle/product/11.2.0.4

After that i did the following actions.

  1. Copied the parameter and password files  to $ORACLE_HOME/dbs of new.
  2. Collected the current configuration of the database using srvctl.

    ncclpdomds06<grid>srvctl config database -d domds
    Database unique name: domds
    Database name:
    Oracle home: /local0/app/oracle/product/11.2
    Oracle user: oracle
    Spfile: +data/domds_06/parameterfile/spfile.409.892459461
    Domain:
    Start options: open
    Stop options: immediate
    Database role: PRIMARY
    Management policy: AUTOMATIC
    Database instance: domds
    Disk Groups: DATA,FRA,RDOEV
    Services:
    ncclpdomds06<grid>

  3. Change the oracle_home location using srvctl using below command.

ncclpdomds06<grid>srvctl modify database -d domds -o /local0/app/oracle/product/11.2.0.4

     4.   Now check the configuration changes reflected or not?

ncclpdomds06<grid>srvctl config database -d domds
Database unique name: domds
Database name:
Oracle home: /local0/app/oracle/product/11.2.0.4
Oracle user: oracle
Spfile: +data/domds_06/parameterfile/spfile.409.892459461
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: domds
Disk Groups: DATA,FRA,RDOEV
Services:
ncclpdomds06<grid>

5.   Start the database.

ncclpdomds06<grid>srvctl start database -d domds

 

Categories: 11g
Follow

Get every new post delivered to your Inbox.