Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup

Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup

Content from http://arup.blogspot.co.uk/2009/12/resolving-gaps-in-data-guard-apply.html

Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure. This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either. In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the customer complained. This standby database is usually opened for read only access from time to time.This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately, at this time it had become a rather political issue.

Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary, there are two parts of the problem:

(1) What happened
(2) How to fix it

What Happened

Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases. On the primary:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447102
On the standby:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1301571
Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(1447102) from dual;

SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM
I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):

SQL> select scn_to_timestamp(1301571) from dual;

SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM
This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.

The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:

…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
… …
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…
Going back in the alert log, I found these lines:

Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”. This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.

The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now. The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either. This has caused the standby to lag behind since that time.

So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert log of the primary instance. The following lines were of interest:

…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…
These lines showed everything clearly. The issue was:

ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.

Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the FAL did not get that log was finally cleared.

Solution

Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?

One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].

1. [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.
2. [Standby] Shutdown the standby database

3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

RMAN> run { 
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb'; 
3> backup incremental from scn 1301571 database;
4> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK

Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
… …
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1
4. [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';

Database altered.
5. [Primary] Copy these files to standby host:

oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04 
6. [Standby] Bring up the instance in nomount mode:

SQL> startup nomount

7. [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl
8. [Standby] Replace the controlfile with the one you just created in primary.

9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl
10.[Standby] Mount the standby database:

SQL> alter database mount standby database;
11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009

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

connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback

List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
12.Recover these files:

RMAN> recover database;

Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...…
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
… …
13. After some time, the recovery fails with the message:

archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'
This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.

14.At this point exit RMAN and start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.
15.Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447474
[Primary] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447478Now they are very close to each other. The standby has now caught up.

DB Stats Gather

As you might know, there is an automatically created SCHEDULER JOB in every 10g database which runs every night and checks for object which have either no statistics at all or for which the statistics have becomeSTALE (which means stat at least 10% of the values have changed). This job is call GATHER_STATS_JOB and belongs to the autotask job class. It uses a program which again call a procedure from built in package DBMS_STATS which does the statistics collection. This feature only works if the initialization parameterSTATISTICS_LEVEL is set to TYPICAL at least (which is the DEFAULT in 10g) and it utilizes the TABLE MONITORINGfeature.

But here we creating separate Job for the database statistics collection.

Disable the default database stats collection job:

BEGIN

      sys.dbms_scheduler.disable(name=>'"SYS"."GATHER_STATS_JOB"', force => TRUE);

END;

Ensure default DB stats job has been disabled by running following query. The value of Enabled column should be FALSE.

select

owner,

job_name,

job_creator,

job_action,

repeat_interval,

last_start_date,

last_run_duration,

enabled

from dba_scheduler_jobs

Create new job for the Database stats gather.

BEGIN

    SYS.DBMS_SCHEDULER.CREATE_JOB (

            job_name => '"SYS"."JOB_GATHER_DB_STATS"',

            job_type => 'PLSQL_BLOCK',

            job_action => 'begin

DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent => NULL, block_sample => FALSE, method_opt => ''''FOR ALL COLUMNS SIZE 1'''', cascade => TRUE, options =>''''GATHER'''', gather_sys => TRUE);

end;

',

            number_of_arguments => 0,

            start_date =>  trunc(sysdate)+22/24,

            repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',

            end_date => NULL,

            job_class => '"SYS"."DEFAULT_JOB_CLASS"',

            enabled => FALSE,

            auto_drop => TRUE,

            comments => 'Daily Database Stats Gathering Job');

     

    SYS.DBMS_SCHEDULER.enable(

             name => '"SYS"."JOB_GATHER_DB_STATS"');

END;

/

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036194

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#CIHEHDHA

Automatic Database Restart

To automate database startup and shutdown by using the dbstart and dbshut scripts:

1. Log in as the root user.
2. Edit the oratab file for the platform.
   To open the file, use one of the following commands:
   
   • On Solaris:
     

     # vi /var/opt/oracle/oratab
     

   • On AIX, HP-UX, and Linux:
     

     # vi /etc/oratab
     

   Database entries in the oratab file are displayed in the following format:
   

   SID:ORACLE_HOME:{Y|N|W}
   

   In this example, the values Y and N specify whether you want the scripts to start or shut down the database, respectively. For each database for which you want to automate shutdown and startup, first determine the instance identifier (SID) for that database, which is identified by the SID in the first field. Then, change the last field for each to Y.
   You can set dbstart to autostart a single-instance database that uses an Automatic Storage Management installation that is auto-started by Oracle Clusterware. This is the default behavior for an Automatic Storage Management cluster. If you want to do this, then you must change the oratab entry of the database and the Automatic Storage Management installation to use a third field with the value W and N, respectively. These values specify thatdbstart auto-starts the database only after the Automatic Storage Management instance is started.
   
   Note:

   If you add new database instances to the system and automate startup for them, then you must edit the entries for those instances in the oratab file.
3. Change directory to one of the following depending on the operating system:
   

   Platform	Initialization File Directory
   AIX	/etc
   Linux and Solaris	/etc/init.d
   HP-UX	/sbin/init.d

4. Create a file called dbora, and copy the following lines into this file:
   
   Note:

   Change the value of the ORACLE_HOME environment variable to an Oracle home directory for the installation. Change the value of the ORACLE environment variable to the user name of the owner of the database installed in the Oracle home directory (typically, oracle).

   #! /bin/sh  -x
   #
   # Change the value of ORACLE_HOME to specify the correct Oracle home
   # directory for your installation.
   
   ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
   #
   # Change the value of ORACLE to the login name of the
   # oracle owner at your site.
   #
   ORACLE=oracle
   
   PATH=${PATH}:$ORACLE_HOME/bin
   HOST=`hostname`
   PLATFORM=`uname`
   export ORACLE_HOME PATH
   #
   if [ ! "$2" = "ORA_DB" ] ; then
      if [ "$PLATFORM" = "HP-UX" ] ; then
         remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB"
         exit
      else
         rsh $HOST -l $ORACLE  $0 $1 ORA_DB
         if [ "$PLATFORM" = "Linux" ] ; then
             touch /var/lock/subsys/dbora
         fi
         exit
      fi
   fi
   #
   case $1 in
   'start')
           $ORACLE_HOME/bin/dbstart $ORACLE_HOME &
           ;;
   'stop')
           $ORACLE_HOME/bin/dbshut $ORACLE_HOME &
           ;;
   *)
           echo "usage: $0 {start|stop}"
           exit
           ;;
   esac
   #
   exit
   

   ####################### Or Use Below Script ##########################

   #!/bin/sh
   # chkconfig: 345 99 10
   # description: Oracle auto start-stop script.
   #
   # Set ORA_HOME to be equivalent to the $ORACLE_HOME
   # from which you wish to execute dbstart and dbshut;
   #
   # Set ORA_OWNER to the user id of the owner of the 
   # Oracle database in ORA_HOME.
   
   ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
   #ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
   #ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
   ORA_OWNER=oracle
   
   if [ ! -f $ORA_HOME/bin/dbstart ]
   then
       echo "Oracle startup: cannot start"
       exit
   fi
   
   case "$1" in
       'start')
           # Start the Oracle databases:
           # The following command assumes that the oracle login 
           # will not prompt the user for any values
           su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
           touch /var/lock/subsys/dbora
           ;;
       'stop')
           # Stop the Oracle databases:
           # The following command assumes that the oracle login 
           # will not prompt the user for any values
           su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
           rm -f /var/lock/subsys/dbora
           ;;
   esac
   

   Note:

   This script can only stop Oracle Net listener for which a password has not been set. In addition, if the listener name is not the default name, LISTENER, then you must specify the listener name in the stop and start commands:
   

   $ORACLE_HOME/bin/lsnrctl {start|stop} listener_name
   

5. Change the group of the dbora file to the OSDBA group (typically dba), and set the permissions to 750:
   

   # chgrp dba dbora
   # chmod 750 dbora
   

6. Create symbolic links to the dbora script in the appropriate run-level script directories as follows.
   

   Platform	Symbolic Links Commands
   AIX	# ln -s /etc/dbora /etc/rc.d/rc2.d/S99dbora # ln -s /etc/dbora /etc/rc.d/rc0.d/K01dbora
   HP-UX	# ln -s /sbin/init.d/dbora /sbin/rc3.d/S990dbora # ln -s /sbin/init.d/dbora /sbin/rc0.d/K001dbora
   Linux	# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora # ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora # ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
   Solaris	# ln -s /etc/init.d/dbora /etc/rc0.d/K01dbora # ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora

   http://www.oracle-base.com/articles/linux/AutomatingDatabaseStartupAndShutdownOnLinux.php

RMAN-06024 during duplicate database for standby

If you get RMAN-06024 when running "duplicate target database for standby" and you are sure you have run a "backup current controlfile for standby" then try doing a log switch on the target database to fix the issue.

RMAN-06024: no backup or copy of the control file found to restore

Health Check on the Database

How to Perform a Health Check on the Database [ID 122669.1] Applicable from Oracle v7 to v11.

about 7 hours ago from web

Install Oracle Database 10.2.0.4 on Windows 7

Yes you can.

You need to amend below lines in "\database\stage\prereq\db\refhost.xml" file.

...........................................

........................................

And Also

You need to tweak the file oraparam.ini in "\database\install" directory.

There are at least two lines you either need to add or change. The first is

Windows=5.0,5.1,5.2,6.0,6.1

I added the 6.1

Next is

[Windows-6.1-optional]

I added that below

[Windows-6.0-optional]

Setup will now run. During the install you will get a warning about the OS version but you can "OK" your way through it. Here is the entire contents of the file after changes

[Oracle]
DISTRIBUTION=TRUE
SOURCE=../stage/products.xml
LICENSE_LOCATION=
JRE_LOCATION=../stage/Components/oracle.swd.jre/1.5.0.11.0/1/DataFiles
JRE_MEMORY_OPTIONS=" -mx192m"
DEFAULT_HOME_LOCATION=oracle\product\10.2.0\db
DEFAULT_HOME_NAME=OraDb10g_home
NO_BROWSE=/net
NLS_ENABLED=TRUE
BOOTSTRAP=TRUE
OUI_VERSION=10.2.0.4.0
ADDITIONAL_COMPONENTS={"oracle.sysman.ccr:ALWAYS","oracle.rdbms.rat:ALWAYS","oracle.jdk:UPGRADE","oracle.swd.jre:UPGRADE"}
## JRE_VERSION is only for Windows. This is to copy accessbridge dlls
JRE_VERSION=1.5.0
#SHOW_HOSTNAME=ALWAYS_SHOW shows the hostname panel always
#SHOW_HOSTNAME=NEVER_SHOW does not the hostname panel
#SHOW_HOSTNAME=CONDITION_SHOW shows the hostname panel on condition
SHOW_HOSTNAME=NEVER_SHOW
#THIN_JDBC_FILENAME is optional and defaults to classes12.jar
#The value specified for this should be packaged with OUI, and should
#be relative to /jlib/
THIN_JDBC_FILENAME=classes12.jar
#RUN_OUICA specifies the batch script name that needs to be run
#The script is ouica.bat for win32, and ouica.sh for solaris.
#If the value is not specified, then the OUICA script is not run
RUN_OUICA=ouica.bat
CLUSTERWARE={"oracle.crs","10.1.0.2.0"}
SILENT_VARIABLE_VALIDATION=TRUE

[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0,6.1

#Windows 2000 use winver.exe to find version
[Windows-5.0-required]
#Minimum display colours for OUI to run
MIN_DISPLAY_COLORS=256
#Minimum CPU speed required for OUI
#CPU=300

[Windows-5.0-optional]

#Windows XP use winver.exe to find version
[Windows-5.1-required]
#Minimum display colours for OUI to run
MIN_DISPLAY_COLORS=256
#Minimum CPU speed required for OUI
#CPU=300

[Windows-5.1-optional]

#Windows .net use winver.exe to find version
[Windows-5.2-required]
#Minimum display colours for OUI to run
MIN_DISPLAY_COLORS=256
#Minimum CPU speed required for OUI
#CPU=300

[Windows-5.2-optional]

[Windows-6.0-required]
#Minimum display colours for OUI to run
MIN_DISPLAY_COLORS=256
#Minimum CPU speed required for OUI
#CPU=300

[Windows-6.0-optional]

[Windows-6.1-optional]

[IMAGES]
FILE1=images/rdbms_1.gif
FILE2=images/rdbms_2.gif
FILE3=images/rdbms_3.gif
FILE4=images/rdbms_4.gif
FILE5=images/rdbms_5.gif
FILE6=images/rdbms_6.gif
FILE7=images/rdbms_7.gif
FILE8=images/rdbms_8.gif

LICENSE_LOCATION=license.txt

Edited by: 835955 on Feb 11, 2011 6:51 AM

Revoke SYSDBA from the Users

Revoke SYSDBA priv

select * from v$pwfile_users where sysdba='TRUE';

Fact sheet about Oracle database passwords

Fact sheet about Oracle database passwords


Oracle Password Algorithm (7-10g Rel.2) (Designed by Bob Baldwin)

Up to 30 characters long. All characters will be converted to uppercase before the hashing starts
8-byte hash, encrypted with a DES encryption algorithm without real salt (just the username).
The algorithm can be found in the book "Special Ops Host and Network Security for Microsoft, Unix, And Oracle" on page 727.

Oracle database 11g offers the (optional) possibility to use passwords up to 50 characters (uppercase/lowercase). In Oracle 11g the passwords are now hashed with DES (column: password) AND using SHA-1 (column: spare4). The SHA-1 passwords are now supporting mixed-case passwords. In 11g the password hashes are no longer available in dba_users.

• Oracle (7-10g R2) encrypts the concatenation of (username||password)
  and sys/temp1 and system/p1 have the identical hashkey (2E1168309B5B9B7A)
• Oracle (11g R1) uses SHA-1 to hash the concatenation of (password||salt)

Oracle Password Cracker

A comparision of different password cracker can be found here. Benchmarks for different password crackers are available here (10g) and here (11g) .


Location of Oracle password hashes

• Database - SYS.USER$ - Password
• Oracle Password File
• Data File of the system tablespace
• (full) Export-Files
• archive logs

Show Oracle password hashkey (old DES hash)

You should always select database users from the table not from the views (ALL_USERS, DBA_USERS). An explanation (modification of database views via rootkits) can be found here.

• DBA_USERS : SELECT username, password FROM DBA_USERS;
• SYS.USER$ : SELECT name,password FROM SYS.USER$ WHERE password is not null;

Show Oracle password hashkey (11g, new SHA-1 hash)

In 11g the password hash is no longer accessible via dba_users

• SYS.USER$ : SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;

How to change an Oracle password?

You should always use the password command because the password is sent unencrypted over the net (without Advanced Security Option) if you use the alter user syntax.

• alter user myuser identified by my!supersecretpassword;
• grant connect to myuser identified by my!supersecretpassword
• update sys.user$ set password='F894844C34402B67' where name='SCOTT'; (restart of the database necessary)
• SQL*Plus command: password or password username

How to change an Oracle password temporarily?

In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME PASSWORD
-------- ----------------
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;


Now login with the following credentials: scott/tiger
After doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user scott identified by values 'F894844C34402B67';


Oracle default password list

600+ default Oracle passwords


Oracle Password Policy

It is possible to setup a password policy (for strong Oracle passwords). A sample file how to do this can be found at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. If you use this functionality please be aware that the password policy function has access to the cleartext password (for the comparisions reasons). With Oracle 11gR1 Oracle greatly enhanced the password verification function.

A hacker could modify your function and log all cleartext passwords entered by the users to a table or send it to a foreign webserver with utl_http. That's why you should checksum this function, e.g. with Repscan.

Oracle brute force attacks / Oracle Password Decryption (7-10gR2)

It is not possible to decrypt a hashstring but the simple Oracle salt (=Username) it is possible to do a brute force or dictionary attack. There are several Oracle brute force or dictionary attack tools available. These tools encrypt the username/password and compare the hashkeys. If the hashkey are identical the password is known. From simple SQL based tools (<500 href="http://www.red-database-security.com/software/checkpwd.html">checkpwd. The fastest tool for brute force attacks orabf calculates 1.100.000 passwords/second. The fastest tool for dictionary attacks are checkpwd and repscan with 600.000 pw per second. On a Pentium 4 with 3 GHz it takes (26 ascii characters only, e.g. 26^5)

• 10 seconds to calculate all 5-ascii-character-combinations
• 5 minutes to calculate all 6-ascii-character-combinations
• 2 hours to calculate all 7-ascii-character-combinations
• 2,1 days to calculate all 8-ascii-character-combinations
• 57 days to calculate all 9-ascii-character-combinations
• 4 years to calculate all 10-ascii-character-combinations

You should always use strong and long passwords to avoid brute force or dictionary attacks.


Typical Error messages related to Oracle database passwords

The following error messages are related to Oracle passwords:

• ORA_28000: The account is locked
• Wait for PASSWORD_LOCK_TIME or contact your DBA
• ORA-28001: The password has expired
• Change the password or contact your DBA
• ORA-00988: Missing or invalid password(s)
• Use double quotes for the password (e.g. alter user scott identified by "!alex";)
• ORA-01017: Invalid username/password; logon denied
• Contact your DBA if the problem still persists

Oracle database passwords in cleartext

Cleartext passwords can be typically but not necessarily found at the following places

• Server
• Shell History files
• Unix Scripts
• Log Files
• Dump Files
• Trace Files
• Application Server
• JDBC-Config-Files
• Trace Files
• DBA Client PC
• Desktop-Shortcut
• Batch-Files
• Configuration files of Oracle Tools (like connections.ini)
• Trace Files

References

• Oracle Password Cracker Checkpwd 2.00 by Red-Database-Security GmbH (fast, 600.000 pw/sec, C-based, 7-11g)
• Oracle Password Cracker Benchmark Benchmark for various Oracle password crackers (old password algorithm)
• Oracle 11g Password Cracker Benchmark Benchmark for various Oracle11g password crackers
• Oracle Default Password Scanner (DPS) SQL Script that checks 689 default passwords
• Oracle Password Algorithmus 7-10g Rel.2
• Analysis Oracle Password Algorithmus 11g
• Oracle Password Cracker 11g (P.o.C.)
• Comparision Chart Oracle Password Cracker
• Hardening DBA Client PC
• An Assessment of the Oracle Password Hashing Algorithm