Find out who is issuing wrong passwords which locks Oracle account using Auditing option

I get several calls on finding out how user accounts are locked and how can we track the host server and OS account name which issued wrong passwords. Here is one method of doing this if you have auditing enabled.

1)      Make sure that auditing is enabled and audit_trail is OS. You could find several documents on how to do auditing using DBA option and later querying aud$ table or checking aud file created if it is audit_trail is OS.

SQL> show parameter audit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      /audit/ppos1

audit_sys_operations                 boolean     TRUE

audit_syslog_level                   string

audit_trail                          string      OS

2)      Connect as sysdba and issue audit command

[oracle@admtrac001:/backup/arac/audit] $ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 6 13:40:50 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL>  audit session by WASADMIN by session whenever not successful;

Audit succeeded.

In 11g, you need to do “by access” instead of by session ( audit session by WASADMIN by access whenever not successful)

3)      Look unsuccessful login attempts by looking for Error Code: 1017 in *.aud files at /audit/ppos1 directory.

$ grep 'RETURNCODE\:\[4\] \"1017\"' *.aud

ora_madhu.aud:SESSIONID:[7] "8167751" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[5] "WASADMIN" USERHOST:[10] "admtrac001" TERMINAL:[5] "pts/3" ACTION:[3] "100" RETURNCODE:[4] "1017" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "oracle"

4)      Stop auditing when you got enough information by connecting as SYSDBA

SQL> noaudit session by WASADMIN;

Noaudit succeeded.

So in my case it was OS user, oracle which was trying to connect as WASADMIN with wrong password.

 

Duplicating Oracle 11g database using RMAN ACTIVE BACKUP

I hope you knew that we can duplicate one Oracle 11g database to a new server using a latest RMAN option which eliminates the need to backup to tape or shared file system and restore this rman backup at new target location. In Oracle 11g, Oracle has introduced new ACTIVE DATABASE DUPLICATE option to create a copy of source database.

Assume that you have one Oracle 11.2 database named “mango” running on mango.tarikida domain and you want to restore this database to an instance named orange in a  new server named orange.tarikida. Data file systems in mango are /db0X/mango and data file systems in orange are /db0X/orange.

1)      Login to server orange.tarikida as oracle user and set env variables

oracle@orange dbs]$ . oraenv

ORACLE_SID = [orange] ?

The Oracle base remains unchanged with value /apps/oracle

2)      Create password file in $ORACLE_HOME/dbs in orange

oracle@orange dbs]$ orapwd file=orapworange password=passw0rd

3)      Start listener LISTENER_orange in server orange.

[oracle@orange dbs]$ lsnrctl start LISTENER_orange

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:02:07

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /apps/oracle/product/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /apps/oracle/product/11.2.0/db/network/admin/listener.ora

Log messages written to /apps/oracle/diag/tnslsnr/orange/listener_orange/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orange)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.168.72)(PORT=7388)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orange)))

4)      Verify that you can perform tnsping to mango and orange from servers mango and orange. Please make sure that you perform this step on both servers.

oracle@orange dbs]$ tnsping mango

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:01:45

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/apps/oracle/product/11.2.0/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.71)(PORT = 7387))) (CONNECT_DATA = (SERVICE_NAME = mango.tarikida)))

OK (10 msec)

[oracle@orange admin]$ tnsping orange

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:21:23

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/apps/oracle/product/11.2.0/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.72)(PORT = 7388))) (CONNECT_DATA = (SERVICE_NAME = orange)))

OK (0 msec)

5)      Create a dummy initorange.ora file in ORACLE_HOME/dbs at server orange with just following lines. Please add correct values.

DB_NAME=orange

CONTROL_FILES=

DB_BLOCK_SIZE=

DB_FILE_NAME_CONVERT=

LOG_FILE_NAME_CONVERT=

DIAGNOSTIC_DEST=

COMPATIBLE=11.2.0

6)      Start oracle instance orange in nomount mode in server orange.

[oracle@orange dbs]$ pwd

/apps/oracle/product/11.2.0/db/dbs

[oracle@orange dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 5 16:03:03 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=initorange.ora

ORACLE instance started.

Total System Global Area  263049216 bytes

Fixed Size                  2227456 bytes

Variable Size              96469760 bytes

Database Buffers          159383552 bytes

Redo Buffers                4968448 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

7)      Start rman for duplicating database

[oracle@orange dbs]$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 5 16:04:22 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect TARGET sys/passw0rd@mango;  -- mango is my source

connected to target database: MANGO (DBID=3989853245)

RMAN> connect AUXILIARY sys/passw0rd@orange;  -- orange is my new instance

connected to auxiliary database: ORANGE (not mounted)

RMAN> duplicate TARGET DATABASE TO orange FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT 'mango','orange'  SET DB_FILE_NAME_CONVERT 'mango','orange' SET LOG_FILE_NAME_CONVERT 'mango','orange';

For a small database above task should finish in less than 15 minutes.

 

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:00:22 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN>

connected to target database: MADM (DBID=xvxvxvxv)

RMAN>

connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

allocated channel: t1

channel t1: SID=1726 device type=SBT_TAPE

channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 01/17/2012 15:00:36

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

RMAN>

Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.

SQL> alter system flush shared_pool;

ORA-29701: unable to connect to Cluster Manager while starting ASM

I prefer to install Oracle binaries using Silent Cloning method and manually create database instances using my custom scripts. 99% percent of the time I follow this method to build production databases at my work and use 1% only in case I evaluate a new version of Oracle. I need to admit that I never managed an ASM instance really and all our RAC installations are non ASM based with Veritas SFRAC as the shared disk sub system.  Since I don’t want to be left out of this ASM band wagon, last week I started building a new ASM instance from scratch without going through Oracle Installer GUI and DBCA. Everything went smooth till I mount the ASM instance. Then I came across ORA-29701. Please note that this is a non CRS server and I don't plan to run a RAC instance here.

echo $ORACLE_SID
+ASM
$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 6 15:42:21 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

ORA-29701: unable to connect to Cluster Manager

ORA-29701. Oracle uses cssd daemon for database instances which uses ASM to communicate with ASM instance. This is needed even if you don't plan to have a RAC instance or Oracle Clusterware installed on this node. Since I did not build ASM using Oracle GUI, Oracle did not add or configure cssd daemon to the OS stack. So  I need to login as root and configure it manually as follows

# cd $ORACLE_HOME/bin

# localconfig add

This step will does the task for you and you can start your ASM instance without any issues.