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.