Find data guard archived redo log lag gap time

When you are using Dataguard, there are several scenarios when physical standby can go out of sync with the primary database.

Before doing anything to correct the problem, we need to verify that why standby is not in sync with the primary. In this particular article, we are covering the scenario where a log is missing from the standby but apart from the missing log, all logs are available.

Verify from v$archived_log that there is a gap in the sequence number. All the logs up to that gap should have APPLIED=YES and all the sequence# after the missing log sequence# are APPLIED=NO. This means that due to the missing log, MRP is not applying the logs on standby but the logs are still being transmitted to the standby and are available.

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

So for example, if the missing log sequence# is 400, then the above query should show that up to sequence#399, all have APPLIED=YES and starting from 401, all are APPLIED=NO.

There are few steps to be performed when the standby is not in sync with the primary because there is a gap of logs on standby.

These steps are:

STEP #1: Take an incremental backup of primary from the SCN where standby is lagging behind and apply on the standby server

STEP #2: If step#1 is not able to sync up, then re-create the controlfile of standby database from the primary

STEP #3: If after step#2, you still find that logs are not being applied on the standby, check the alert log and you may need to re-register the logs with the standby database.

*******************************************************************************************

STEP#1

1. On STANDBY database query the v$database view and record the current SCN of the standby database:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1.3945E+10

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)

----------------------------------------
13945141914

2. Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

If you see the above error, it means Managed Recovery is already off

You can also confirm from the view v$managed_standby to see if the MRP is running or not

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

3. Connect to the primary database as the RMAN target and create an incremental backup from the current SCN of the standby database that was recorded in step 1:

For example,

BACKUP INCREMENTAL FROM SCN 13945141914 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FOR STANDBY'

You can choose a location other than /tmp also.

4. Do a recovery of the standby database using the incremental backup of primary taken above:

On the Standby server, without connecting to recovery catalog, catalog the backupset of the incremental backup taken above. Before this, of course you need to copy the backup piece of the incremental backup taken above to a location accessible to standby server.

$ rman nocatalog target /
RMAN> CATALOG BACKUPPIECE '/dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1';

Now in the same session, start the recovery

RMAN> RECOVER DATABASE NOREDO;

You should see something like:

Starting recover at 2015-09-17 04:59:57
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=309 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
....
..
..
.
channel ORA_DISK_1: reading from backup piece /dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1 tag=FOR STANDBY
channel ORA_DISK_1: restore complete, elapsed time: 01:53:08Finished recover at 2015-07-25 05:20:3

Delete the backup set from standby:

RMAN> DELETE BACKUP TAG 'FOR STANDBY';
using channel ORA_DISK_1
List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
17713   17713   1   1   AVAILABLE   DISK        /dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1

Do you really want to delete the above objects (enter YES or NO)? YES

deleted backup piece

backup piece handle=/dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1 recid=17713 stamp=660972421

Deleted 1 objects

5. Try to start the managed recovery.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

If you get an error here, you need to go to STEP#2 for bringing standby in sync.

If no error, then using the view v$managed_standby, verify that MRP process is started and has the status APPLYING_LOGS.

6. After this, check whether the logs are being applied on the standby or not:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

After doing a recovery using the incremental backup, you will not see the sequence#'s which were visible earlier with APPLIED=NO because they have been absorbed as part of the incremental backup and applied on standby during recovery.

The APPLIED column starts showing YES for the logs which are being transmitted now, this means logs are being applied.

Check the status of MRP process in the view v$managed_standby. The status should be APPLYING_LOGS for the duration that available logs are being applied and once all available logs have been applied, the status should be WAITING_FOR_LOGS

7. Another check to verify that primary and standby are in sync. Run the following query on both standby and primary:

SQL> select max(sequence#) from v$log_history.

Output should be same on both databases.

*******************************************************************************************

STEP #2:

Since Managed recovery failed after applying the incremental backup, we need to recreate the controlfile of standby. The reason for recreating the controlfile is that the state of the database was same because the database_scn was not updated in the control file after applying the incremental backup while the scn for datafiles were updated. Consequently, the standby database was still looking for the old file to apply.

A good MOSC note for re-creating the controlfile in such a scenario is 734862.1.

Steps to recreate the standby controlfile and start the managed recovery on standby:

1. Take the backup of controlfile from primary

rman target sys/oracle@proddb catalog rman/cat@emrep
backup current controlfile for standby;

2. Copy the controlfile backup to the standby system (or if it is on the common NFS mount, no need to transfer or copy) and restore the controlfile onto the standby database

Shutdown all instances (If standby is RAC) of the standby.

sqlplus / as sysdba
shutdown immediateexit

Startup nomount, one instance.

sqlplus / as sysdba

startup nomount

exit

Restore the standby control file.

rman nocatalog target /

restore standby controlfile from '/tmp/o1_mf_TAG20070220T151030_.bkp';

exit

3. Startup the standby with the new control file.

sqlplus / as sysdba

shutdown immediate

startup mount

exit

4.  Restart managed recovery in one instance (if standby is RAC) of the standby database:

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

The above statement may succeed without errors but the MRP process will still not start. The reason is that since the controlfile has been restored from the primary, it is looking for datafiles at the same location as are in primary instead of standby. For example, if the primary datafiles are located at '+DATA/proddb_1/DATAFILE' and standby datafiles are at '+DATA/proddb_2/DATAFILE', the new controlfile will show the datafile's location as '+DATA/proddb_1/DATAFILE'. This can be verified from the query "select name from v$datafile" on the standby instance. We need to rename all the datafiles to reflect the correct location.

There are two ways to rename the datafiles:

1. Without using RMAN

Change the parameter standby_file_management=manual in standby's parameter file.

ALTER DATABASE RENAME FILE '+DATA/proddb_1/datafile/users.310.620229743' TO '+DATA/proddb_2/datafile/USERS.1216.648429765';

2. Using RMAN

rman nocatalog target /

Catalog the files, the string specified should refer to the diskgroup/filesystem destination of the standby data files.

RMAN> catalog start with '+diskgroup/<dbname>/datafile/';

e.g.:

RMAN> catalog start with '+DATA/proddb_2/datafile/';

This will give the user a list of files and ask if they should all be cataloged. The user should review and say YES if all the datafiles are properly listed.

Once that is done, then commit the changes to the controlfile

RMAN> switch database to copy;

Now start the managed recovery as:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

and check for processes in the view v$managed_standby.  MRP process should be there. It will also start applying all the archived logs that were missing since last applied log.  This process might take hours.

5. Another check to verify that primary and standby are in sync:

Run the following query on both standby and primary after all logs in v$archived_log show APPLIED=YES:

SQL> select max(sequence#) from v$log_history.

Output should be same on both databases.

*****************************************************************************************

STEP #3

After recreating the controlfile, you still find that logs are being transmitted but not being applied on the standby. Check the alert log of standby. For example, see if you find something similar to below snippet:

Fetching gap sequence in thread 1, gap sequence 74069-74095

Wed Sep 17 06:45:47 2015

RFS[1]: Archived Log: '+DATA/ipwp_sac1/archivelog/2008_09_17/thread_1_seq_74093.259.665649929'

Wed Sep 17 06:45:55 2015

Fetching gap sequence in thread 1, gap sequence 74069-74092

Wed Sep 17 06:45:57 2015

RFS[1]: Archived Log: '+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74094.258.665649947'

Wed Sep 17 06:46:16 2015

RFS[1]: Archived Log: '+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74095.256.665649957'

Wed Sep 17 06:46:26 2015

FAL[client]: Failed to request gap sequence

 GAP - thread 1 sequence 74069-74092

The contents of alert log shows that logs sequence# from 74069 to 74092 may have been transmitted but not applied. The view v$archived_log shows the sequence# starting from 74093 and APPLIED=NO.

So this situation means that logs up to 74068 were applied as part of the incremental backup and from 74069 to 74093 have been transferred to standby server but they must have failed to register with standby database. Try the following steps: 

1. Locate the log sequence# shown in alert log (for example 74069 to 74092). For example,+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74069.995.665630861
2. Register all these archived logs with the standby database.

alter database register logfile '+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74069.995.665630861';

alter database register logfile '+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74070.998.665631405';

alter database register logfile '+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74071.792.665633755';

alter database register logfile '+DATA/proddb_2/archivelog/2008_09_17/thread_1_seq_74072.263.665633713';

??..

?.and so on till the last one.

3. Now check the view v$archived_log and finally should see the logs being applied. The status of MRP should change from ARCHIVE_LOG_GAP to APPLYING_LOGS and eventually WAITING_FOR_LOGS.

*******************************************************************************************

Contributed by:

Name: Rohit Gupta
Current title : Technology Architect
Current Organization : Infosys Ltd.
Overall DBA Experience : 11+ yrs
Certifications :OCP 9i, 10g,11g DBA, ITIL foundation, Oracle Certified Exadata Implementation Specialist

Blogs:

Oracle DataGuard – How to resize Redo Log and Standby Redo Log

How to resize Redo Log and Standby Redo Log in Oracle DataGuard environment?

I needed to resize the redo log as it takes about 4 days to do a log switch. The recommended log switch frequency is at least once an hour.  It is not possible to resize the redo log or standby redo log (SRL) dynamically. We have to drop and recreate them.

Primary Server

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 15:05:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN         CSS                    PRIMARY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 GROUP# Size in MB
---------- ----------
 1 500
 2 500
 3 500
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 GROUP# Size in MB
---------- ----------
 4 500
 5 500
 6 500
 7 500

Standby Server

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 15:05:40 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED css PHYSICAL STANDBY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 GROUP# Size in MB
---------- ----------
 1 500
 2 500
 3 500
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 GROUP# Size in MB
---------- ----------
 4 500
 5 500
 6 500
 7 500

Do this for both Primary and Standby

SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management      string                                          AUTO
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management      string                                          MANUAL

Primary

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
 1 ONLINE /u01/oradata/css/redolog_1a.dbf NO
 1 ONLINE /u02/oradata/css/redolog_1b.dbf NO
 2 ONLINE /u01/oradata/css/redolog_2a.dbf NO
 2 ONLINE /u02/oradata/css/redolog_2b.dbf NO
 3 ONLINE /u01/oradata/css/redolog_3a.dbf NO
 3 ONLINE /u02/oradata/css/redolog_3b.dbf NO
 4 STANDBY /u01/oradata/css/stby_log_4a.dbf NO
 4 STANDBY /u02/oradata/css/stby_log_4b.dbf NO
 5 STANDBY /u01/oradata/css/stby_log_5a.dbf NO
 5 STANDBY /u02/oradata/css/stby_log_5b.dbf NO
 6 STANDBY /u01/oradata/css/stby_log_6a.dbf NO
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
 6 STANDBY /u02/oradata/css/stby_log_6b.dbf NO
 7 STANDBY /u01/oradata/css/stby_log_7a.dbf NO
 7 STANDBY /u02/oradata/css/stby_log_7b.dbf NO
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
 1 CURRENT
 3 INACTIVE
 2 INACTIVE

SQL> alter database drop logfile group 3;
Database altered.
# rm /u01/oradata/css/redolog_3a.dbf /u02/oradata/css/redolog_3b.dbf
SQL> alter database add logfile group 3 ('/u01/oradata/css/redolog_3a.dbf','/u02/oradata/css/redolog_3b.dbf') size 100M;
Database altered.

Repeat this for all inactive redo log group.  Do a switch log on primary to change the current redo log group before dropping and re-creating the redo log group.

SQL> select group#,status,BYTES/1024/1024 mb from v$log;
GROUP# STATUS MB
---------- ---------------- ----------
 1 CURRENT 500
 3 UNUSED 100
 2 UNUSED 100
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,BYTES/1024/1024 mb from v$log;
GROUP# STATUS MB
---------- ---------------- ----------
 1 INACTIVE 500
 3 UNUSED 100
 2 CURRENT 100

Final output should look similar to this;

SQL> select group#,status,BYTES/1024/1024 mb from v$log;
GROUP# STATUS MB
---------- ---------------- ----------
 1             UNUSED           100
 3             UNUSED           100
 2            CURRENT         100

Now for the standby redo log on primary; Since all is unused, you may just drop and re-create them without any issue. SRL is only used by standby database to apply the redo log. It is being created on the primary so that if the primary is being switch-over to standby role, we do not have to create the SRL again. 

SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED
SQL> alter database drop standby logfile group 4;
Database altered.
# rm /u01/oradata/css/stby_log_4a.dbf','/u02/oradata/css/stby_log_4b.dbf
SQL> alter database add standby logfile group 4 ('/u01/oradata/css/stby_log_4a.dbf','/u02/oradata/css/stby_log_4b.dbf') size 100M; 

Do it for all the SRL groups. Verify final configuration. 

SQL> select group#,status,bytes/1024/1024 mb from v$standby_log; 
GROUP# STATUS MB ---------- ---------- ---------- 
4 UNASSIGNED 100 
5 UNASSIGNED 100 
6 UNASSIGNED 100 
7 UNASSIGNED 100

On the standby database;

SQL> select * from v$logfile;
 GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
 1 ONLINE /u01/oradata/css/redolog_1a.dbf NO
 1 ONLINE /u02/oradata/css/redolog_1b.dbf NO
 2 ONLINE /u01/oradata/css/redolog_2a.dbf NO
 2 ONLINE /u02/oradata/css/redolog_2b.dbf NO
 3 ONLINE /u01/oradata/css/redolog_3a.dbf NO
 3 ONLINE /u02/oradata/css/redolog_3b.dbf NO
 4 STANDBY /u01/oradata/css/stby_log_4a.dbf NO
 4 STANDBY /u02/oradata/css/stby_log_4b.dbf NO
 5 STANDBY /u01/oradata/css/stby_log_5a.dbf NO
 5 STANDBY /u02/oradata/css/stby_log_5b.dbf NO
 6 STANDBY /u01/oradata/css/stby_log_6a.dbf NO
 6 STANDBY /u02/oradata/css/stby_log_6b.dbf NO
 7 STANDBY /u01/oradata/css/stby_log_7a.dbf NO
 7 STANDBY /u02/oradata/css/stby_log_7b.dbf NO
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
3 CLEARING
2 CURRENT
SQL>alter database clear logfile group 3;
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database drop logfile group 3
#rm /u01/oradata/css/redolog_3a.dbf /u02/oradata/css/redolog_3b.dbf

Repeat this for the Redo Log group with status on “CLEARING”. After which do the following;

SQL> alter database recover managed standby database disconnect from session using current logfile;

Do a switch log on the primary database. 

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
 1 CURRENT
 3 UNUSED
 2 CLEARING
SQL> alter database recover managed standby database cancel;
For the SRL group;

SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
 4 ACTIVE
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

Do the same for the “UNASSIGNED” SRL group. 

SQL> alter database clear logfile group 4;
Database altered.
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

Final redo log and SRL group config;

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
 GROUP# size in MB
---------- ----------
 1 100
 2 100
 3 100
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
 GROUP# size in MB
---------- ----------
 4 100
 5 100
 6 100
 7 100
SQL> alter system set standby_file_management=auto; => For both primary and standby
SQL> alter database recover managed standby database disconnect from session using current logfile; => For standby
Verify MRP has started.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_LOG 87
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
 87

Ref-newbiedba.wordpress.com

How to find latest oracle database patchset

It is sometimes a bit of a hassle, to have the latest patch name or number on hand, when you need them. Ok, you may search on My Oracle Support and save it as custom search. But it may happen that the search is inaccurate and the required patch is not found. A much easier way is to use the Oracle Metalink Notes, which have been available for a while. These MOS Notes are updated regularly with the latest patch information. My favorite is definitely the Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets [1454618.1]. But there are more interesting MOS notes.

Which Patch’s are available?

MOS notes about patches, patch sets, PSU, SRU and bundle patches :

• Introduction to Oracle Recommended Patches [756388.1]
  This MOS notes is the main entry to the Oracle recommended patches. It includes further links to Oracle Database, Oracle Enterprise Manager, Oracle Fusion Middleware and other products.
• Oracle Recommended Patches — Oracle Database [756671.1]
  This notes includes the links for the latest recommended patches of Oracle Databases on Unix and Linux since Oracle 10.2.0.3
• Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms [161549.1]
  As the name says, this note contains further links for recommended patches of Oracle Databases on Microsoft Windows
• Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets [1454618.1]
  This MOS note is some kind of a master note for any PSU, CPU, Bundle Patches and Patchset. Here you’ll find any patch number without struggling yourself first through all the Oracle recommendations 
• Release Schedule of Current Database Releases [742060.1]
  On this MOS Note you do not really find any patch numbers or names but you’ll find the release schedules of upcoming patch set. Ok you do not see an exact date. But at least the quarter of the year.

Which Patch has been installed?

The easies way to list the installed patches in the current ORACLE_HOME is to use the patch utility.

List of installed patches:

$ORACLE_HOME/OPatch/opatch lsinventory

Grep on the patch description:

$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"

A more verbose list on the installed patches:

$ORACLE_HOME/OPatch/opatch lsinventory -details

Which Patch has been applied?

The table REGISTRY$HISTORY does contain information on applied patches respectively PSU, SRU or CPU. SinceSince I use this query regularly during the tests of the Critical Patch Update, I have it packed in a handy script ( cpui.sql).

SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12
SELECT * FROM registry$history;

Who is locking your accounts (ORA-01017 and ORA-28000 errors) ?

I have decided to write this blog post after second time I received question on how to know from where are coming connections that are locking an account in an Oracle database…

Do not smile, I have seen at least two situations where, after a password change, a batch job was initiating plenty of connection (with previous wrong password) and no one was able to know from where this batch job was running (!!).

As a reminder, with default profile in Oracle 11g, accounts are automatically locked 1 day (PASSWORD_LOCK_TIME) after 10 failed login attempt (FAILED_LOGIN_ATTEMPTS):

SQL> SET lines 200 SQL> SET pages 200 SQL> SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;   PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED   16 ROWS selected.

Oracle client session will received 10 times ORA-01017: invalid username/password; logon denied error message and then ORA-28000: the account is locked error message (for one day and then back to ORA-01017 error message).

The final question is how to identify from where (client IP address/name) are coming those tentative connections… I have done my testing using Oracle 11.2.0.3 running on Oracle Linux Server release 6.3.

ORA-01017/ORA-28000 with AUDIT_TRAIL

The first and preferred solution is with Oracle standard auditing feature. Start by setting initialization parameter AUDIT_TRAIL to db and restart your Oracle database as it is static parameter.

Then activate network auditing with (as SYS):

SQL> AUDIT network BY ACCESS;   AUDIT succeeded.

With below query you get everything needed:

SELECT * FROM dba_audit_session ORDER BY sessionid DESC;

Returncode column contains Oracle error code and so different of 0 if logon/logoff issue. The invalid password is the error we are chasing:

[oracle@server1 ~]$ oerr ora 1017 01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action:

So if you find 1017 values in this column then we have found what we were looking for. For example with my test case where I intentionally specify a wrong password for my account:

SQL> SELECT username,userhost,returncode FROM dba_audit_session WHERE username='YJAQUIER' ORDER BY sessionid DESC;   USERNAME USERHOST RETURNCODE ------------------------------ -------------------- ---------- YJAQUIER server1 1017 YJAQUIER GVADT30596 0 YJAQUIER server1 0 YJAQUIER server1 0 . . .

And if you insist, as explained, you get:

SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='YJAQUIER';   USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- -------------------- ------------------------------ YJAQUIER LOCKED(TIMED) 23-nov-2012 10:30:37 DEFAULT

If you set AUDIT_TRAIL to db behave the size of SYS.AUD$ table as a small list of audits are already implemented by default:

SQL> SET lines 200 SQL> SET pages 200 SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;   USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER SYSTEM BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS

So you must put in place a purging policy for this table.

ORA-01017/ORA-28000 without AUDIT_TRAIL

The only drawback of the previous solution is that you have to restart the database. And maybe two times because after problem solved you would like to deactivate auditing. This is most probably not reliable solution on a production database so I have been looking for a better solution with no database reboot.

I initially thought of the AFTER LOGON trigger but you need to be logged-in and the BEFORE LOGON does not exits. Then at same documentation place I found the AFTER SERVERERROR trigger and decided to give it a try.

First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):

CREATE TABLE sys.logon_trigger ( USERNAME VARCHAR2(30), USERHOST VARCHAR2(128), TIMESTAMP DATE );

Second I created below trigger:

CREATE OR REPLACE TRIGGER sys.logon_trigger AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR(1017)) THEN INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE); COMMIT; END IF; END; /

Then third simulated a wrong password access with my account and issued:

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';   SESSION altered.   SQL> SET lines 200 SQL> col USERHOST FOR a30 SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;   USERNAME USERHOST TIMESTAMP ------------------------------ ------------------------------ -------------------- yjaquier ST\GVADT30596 23-nov-2012 11:05:56

And that’s it !!

Refrence-http://blog.yannickjaquier.com