How to add database to oracle restart.

June 8, 2017 Comments off
Categories: Dataguard., Oracle DBA

Resetting the password for oracle 11g or 12c users with same password.

December 23, 2016 Comments off

I used to get a request from users to reset the password but they do not know the password since the password has been hard coded in their application and password has been expired due to password grace period.

SQL> set lines 120
set pages 120
column username format a20
column account_status format a20
column profile format a15
select username,account_status,profile,EXPIRY_DATE from dba_users where username like ‘TVIJAY’ order by 1;

USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
——————– ——————– ————— ———
TVIJAY EXPIRED(GRACE) DEFAULT 19-DEC-17

SQL>

The above situation happened because PASSWORD_LIFE_TIME has been set to 180 days. For every 180 days user has to reset the password, however in some cases user can not change the password due to various reason like he has to hard code password in applications in many places etc…So in those cases we need to retain old password.  At times users do not know the password also.

SET LINES 120
SET PAGES 120
COLUMN LIMIT FORMAT A30
COLUMN RESOURCE_NAME FORMAT A30
Column PROFILE format a20
SELECT * FROM DBA_PROFILES
where PROFILE in (‘DEFAULT’);

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
——————– —————————— ——– —————————— —
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO
DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO
DEFAULT IDLE_TIME KERNEL UNLIMITED NO
DEFAULT CONNECT_TIME KERNEL UNLIMITED NO
DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO

16 rows selected.

SQL>

For this situation, like user does not know the password but we need to reset to old password,

For this situation, we can use below method.

Collect the current password in the encrypted format.

SQL> select password from sys.user$ where name=’TVIJAY’;

PASSWORD
——————————
03BE7DDA8399B6A7

SQL>

Reset the password

alter user TVIJAY identified by values ’03BE7DDA8399B6A7′;

Now check the status of the user.

set lines 120
set pages 120
column username format a20
column account_status format a20
column profile format a15
select username,account_status,profile,EXPIRY_DATE from dba_users where username like ‘TVIJAY’ order by 1;

USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
——————– ——————– ————— ———
TVIJAY OPEN DEFAULT 21-JUN-17

SQL>

There are situations to change the password life also. In those situations, you can use below method.

Get the current value.

SQL> SET LINES 120
SET PAGES 120
SELECT * FROM DBA_PROFILES where PROFILE in (‘DEFAULT’) AND RESOURCE_NAME IN (‘PASSWORD_LIFE_TIME’) ORDER BY PROFILE ;SQL> SQL>

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
——————– —————————— ——– —————————— —
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO

SQL>

change to your desired value

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 270;

Profile altered.

SQL> SET LINES 120
SET PAGES 120
SELECT * FROM DBA_PROFILES where PROFILE in (‘DEFAULT’) AND RESOURCE_NAME IN (‘PASSWORD_LIFE_TIME’) ORDER BY PROFILE ;SQL> SQL>

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
——————– —————————— ——– —————————— —
DEFAULT PASSWORD_LIFE_TIME PASSWORD 270 NO

SQL> set lines 120
set pages 120
column username format a20
column account_status format a20
column profile format a15
select username,account_status,profile,EXPIRY_DATE from dba_users where username like ‘TVIJAY’ order by 1;SQL> SQL> SQL> SQL> SQL>

USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
——————– ——————– ————— ———
TVIJAY OPEN DEFAULT 19-SEP-17

SQL>

Categories: Oracle DBA

Enable the trace for a particular SQL.

June 28, 2016 Comments off

Today i was asked to collect the trace for a particular SQL.   I did the following way.   I might have done it earlier also, but felt to record again.

 

 

Please generate 10046 level 12 on primary and standby for the query following the below steps

At Primary
========

SPOOL primary.OUT
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’PRIMARY_10046′;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’;
****YOUR QUERY GOES HERE***********
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
SPOOL OFF
EXIT
At Standby
==========

SPOOL Standby.OUT
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’STANDBY_10046′;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’;
****YOUR QUERY GOES HERE***********
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
SPOOL OFF
EXIT

Categories: Oracle DBA

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