How to Change / Recover Lost SYS password

How to change / recover lost sys password?

there are following method to change sys password , each depends on type of password authentication

Method: 1

1. connect to database with sysdba user and execute (on server where oracle is installed)

SQL> alter user sys identified by password;

Method: 2

1. connect to database with sysdba user and execute (on server where oracle is installed)

SQL> passw

This command will ask you old password and new password

Method: 3

re-create password file (on server where oracle is installed)

1. connect to database with sysdba user

1.1 make sure "remote_login_password" parameter is set to SHARED or EXCLUSIVE

2. shutdown immediate

3. backup existing password file (password file located under $ORACLE_HOME/dbs directory)

$ORACLE_HOME/dbs $mv orapwSID.ora orapwSID.ora.bk

4. create new password file

$ORACLE_HOME/bin $orapwd file=$ORACLE_HOME/dbs/orapwSID.ora password=oracle entries=5

5. start oracle database

Method: 5

If oracle password is based on OS authentication then use the following way

1. Make user OS user is logged with member of dba group (on linux) , ORA_DBA group (on windows)

#1. Make sure that the OS user that is logged on is member of the dba group. If the dba group is not known, check the file:

$ORACLE_HOME/rdbms/lib/config.c (or config.s)
#2. Make sure that the sqlnet.ora file does not contain the:

sqlnet.authentication_services
parameter. If this parameter exist, it should be commented.

#3. check the ORACLE_HOME, ORACLE_SID and PATH parameters. They must correspond to the parameters of the Oracle instance where the SYS password has been lost.

If the instance is already started, use the document:

#4. connect to the instance using:

sqlplus /nolog

connect / as sysdba
#5. Change the sys password using:

alter user sys identified by password;

If a passwordfile is in use, the command would update the password file as well, so there is no need to recreate this file.

Enable auditing for SYSDBA (sys) user in 9ir2

SYS audit feature introduce in oracle 9ir2 

http://download.oracle.com/docs/cd/B10501_01/server.920/a96531/ch2_9ir2.htm#74452

How to enable SYS audit?

1. set the following three parameters

1.1 

audit_file_dest == (location of audit files (OS) based)

audit_sys_operations === true ( enable sys audit)

audit_trail = OS 

SQL> select * from v$version where rownum=1;

BANNER

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

Oracle9i Release 9.2.0.4.0 - Production

SQL> show parameter audit-

>

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      ?/rdbms/audit

audit_sys_operations                 boolean     FALSE

audit_trail                          string      NONE

transaction_auditing                 boolean     TRUE

SQL> host echo $ORACLE_HOME

/disk1/app/oracle/product/9.2.0

SQL>  alter system set audit_file_dest='/disk1/app/oracle/product/9.2.0/audit' s                                                                             cope=spfile;

System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

System altered.

SQL> alter system set audit_trail='OS' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  202445884 bytes

Fixed Size                   451644 bytes

Variable Size             184549376 bytes

Database Buffers           16777216 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

SQL> host ls -lrt $ORACLE_HOME/audit

total 8

-rw-r-----  1 oracle dba 1103 Sep 17 19:44 ora_25676.aud

-rw-r-----  1 oracle dba  734 Sep 17 19:44 ora_25677.aud

SQL>  host cat $ORACLE_HOME/audit/ora_25676.aud

Audit file /disk1/app/oracle/product/9.2.0/audit/ora_25676.aud

Oracle9i Release 9.2.0.4.0 - Production

JServer Release 9.2.0.4.0 - Production

ORACLE_HOME = /disk1/app/oracle/product/9.2.0

System name:    Linux

Node name:      SPI-ISIS

Release:        2.6.9-78.ELsmp

Version:        #1 SMP Wed Jul 9 15:39:47 EDT 2008

Machine:        i686

Instance name: STERLIVE

Redo thread mounted by this instance: 0

Oracle process number: 14

Unix process pid: 25676, image: oracle@SPI-ISIS (TNS V1-V3)

Sat Sep 17 19:44:16 2011

ACTION : 'CONNECT'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL:

STATUS: 0

Sat Sep 17 19:44:16 2011

ACTION : 'SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHO                                                                             W_SGA,    SUM(VALUE), DECODE (null,'', 'bytes','')  FROM V$SGA    UNION ALL    S                                                                             ELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE,    DECODE (null,'', 'bytes','') FROM                                                                              V$SGA'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL:

STATUS: 0

Sat Sep 17 19:44:20 2011

ACTION : 'ALTER DATABASE   MOUNT'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL:

STATUS: 0

Audit is not enabled but still .aud files generated

You might be not know... if database audit is not enabled then also oracle audit few database operation and records in the OS audit files (*.aud).

[oracle@spiora bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 17 19:21:11 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

SQL> show parameter audit_sys_operations

NAME                                 TYPE        VALUE

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

audit_sys_operations                 boolean     FALSE

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE

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

audit_trail                          string      DB_EXTENDED

SQL> show parameter audit_file

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /db/app/oracle/Db_1/rdbms/audi

                                                 t

SQL> host ls -lrt /db/app/oracle/Db_1/rdbms/audit

/db/app/oracle/Db_1/rdbms/audit:

ora_10033.aud  ora_12623.aud  ora_21641.aud  ora_30348.aud  ora_3974.aud

ora_10085.aud  ora_1264.aud   ora_21644.aud  ora_30349.aud  ora_3975.aud

ora_10148.aud  ora_12650.aud  ora_2186.aud   ora_30351.aud  ora_3991.aud

ora_10154.aud  ora_12652.aud  ora_21988.aud  ora_30352.aud  ora_3994.aud

lets have a look what inside this aud files

SQL>  host cat /db/app/oracle/Db_1/rdbms/audit/ora_26457.aud

Audit file /db/app/oracle/Db_1/rdbms/audit/ora_26457.aud

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

ORACLE_HOME = /db/app/oracle/Db_1

System name:    Linux

Node name:      spiora.dummy.local

Release:        2.6.9-34.ELsmp

Version:        #1 SMP Fri Feb 24 16:54:53 EST 2006

Machine:        i686

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 26457, image: oracle@spiora.dummy.local (TNS V1-V3)

Sat Sep 17 19:21:11 2011

LENGTH : '137'

ACTION :[7] 'CONNECT'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/1'

STATUS:[1] '0'

Below are the following operations which is audit if audit is not enabled. (mandatory audit)

mandatory auditing

Activities that are audited by default, regardless of whether or not auditing was enabled. These activities include connections to the instance with administrator privileges, database startups, and database shutdowns. Oracle Database writes these activities to the operating system audit trail.

Enable auditing for SYSDBA (sys) user in 11gr2

Database security is one of the major and important task for DBA's. ,

this article is basic of enabling auditing for SYS administrative user.

1. How to enable AUDIT for sys administrative user.

Step:

1. logon to database with sysdba user

2. set AUDIT_SYS_OPERATIONS parameter (mandatory) to TRUE

default value of this parameter is FALSE

This parameter is static means we required to bounce DATABASE to take effect of this parameter value.

3. set AUDIT_TRAIL parameter as per desired value.

default value of this parameter is none

4. set AUDIT_FILE_DEST = location path for audit records log file 

default value of this parameter %ORACLE_BASE%/ORACLE_SID/adump

IF AUDIT_FILE_DEST parameter doesn't set or not point to valid directory then oracle will create audit file in %ORACLE_HOME%/rdbms/audit directory.

5. restart database

6. Setting the size or age of the operating system audit files

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#CEGBDFCF

Example:

[oracle@recovery bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 17 21:46:36 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

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

SQL> alter system set audit_sys_operations=TRUE scope=spfile;

System altered.

SQL> alter system set audit_trail='DB' scope=spfile;

System altered.

SQL> alter system set audit_file_dest='/db/admin/oramfe/adump/' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2042241024 bytes

Fixed Size                  1337548 bytes

Variable Size             973080372 bytes

Database Buffers         1056964608 bytes

Redo Buffers               10858496 bytes

Database mounted.

Database opened.

SQL> host ls -lrt /db/admin/oramfe/adump/

total 32

-rw-rw---- 1 oracle oracle 2585 Sep 17 21:48 oramfe_ora_12487_1.aud

-rw-rw---- 1 oracle oracle  750 Sep 17 21:48 oramfe_ora_12500_2.aud

-rw-rw---- 1 oracle oracle 1451 Sep 17 21:50 oramfe_ora_12602_1.aud

-rw-rw---- 1 oracle oracle  980 Sep 17 21:51 oramfe_ora_12671_1.aud

SQL> host cat /db/admin/oramfe/adump/oramfe_ora_12671_1.aud

Audit file /db/admin/oramfe/adump/oramfe_ora_12671_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

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

ORACLE_HOME = /db/product/11.2.0/dbhome_1/

System name:    Linux

Node name:      recovery.fakhruddin.local

Release:        2.6.18-53.el5

Version:        #1 SMP Wed Oct 10 16:34:02 EDT 2007

Machine:        i686

Instance name: oramfe

Redo thread mounted by this instance: 1

Oracle process number: 20

Unix process pid: 12671, image: oracle@recovery.fakhruddin.local (TNS V1-V3)

Sat Sep 17 21:50:07 2011 +04:00

LENGTH : '160'

ACTION :[7] 'CONNECT'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[10] '3012735072'

Sat Sep 17 21:51:21 2011 +04:00

LENGTH : '173'

ACTION :[19] 'ALTER DATABASE OPEN'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[10] '3012735072'

On windows platform the audit records will captured in the event viewer log file.

start --->> control penals >>> administrative tools >>> event viewer >>>> application log

ORA-24247: network access denied by access control list (ACL)

In oracle 11gr1 or higher version new security policy implemented.

while executing UTL_SMTP package received following error message:

ORA-24247: network access denied by access control list (ACL)

Reason : New security policy implemented.

Solution:

1. Login with SYSDBA user

2. create procedure

3. grant access to particular user.

[oracle@fakorapd bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 12 17:20:14 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

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

SQL> set serveroutput on

SQL> SQL> show user

USER is "SYS"

SQL> create or replace procedure mailserver_acl(

  aacl       varchar2,

  2    3    acomment   varchar2,

  4    aprincipal varchar2,

  5    aisgrant   boolean,

  6    aprivilege varchar2,

  7    aserver    varchar2,

  8    aport      number)

  9  is

 10  begin

  begin

 11   12      DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);

 13       dbms_output.put_line('ACL dropped.....');

 14    exception

 15      when others then

 16        dbms_output.put_line('Error dropping ACL: '||aacl);

      dbms_output.put_line(sqlerrm);

 17   18    end;

 19    begin

 20      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,apr                                                                             ivilege);

 21      dbms_output.put_line('ACL created.....');

 22    exception

 23      when others then

 24        dbms_output.put_line('Error creating ACL: '||aacl);

 25        dbms_output.put_line(sqlerrm);

 26    end;

 27    begin

 28      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);

 29      dbms_output.put_line('ACL assigned.....');

 30    exception

 31      when others then

 32        dbms_output.put_line('Error assigning ACL: '||aacl);

 33        dbms_output.put_line(sqlerrm);

 34    end;

 35    commit;

 36    dbms_output.put_line('ACL commited.....');

 37  end;

 38  /

Procedure created.

SQL> show errors

No errors.

SQL> begin

  mailserver_acl(

    'mailserver_acl.xml',

    'ACL for used Email Server to connect',

    'OEMSYS', --(user name/schema)

    TRUE,

    'connect',

    'fakexc.dummy.local', --(your exchange mail server name)

    25); --default port number

end;

/  2    3    4    5    6    7    8    9   10   11

/

Error dropping ACL: mailserver_acl.xml

ORA-31001: Invalid resource handle or path name "/sys/acls/mailserver_acl.xml"

ACL created.....

ACL assigned.....

ACL commited.....

PL/SQL procedure successfully completed.

NOTE: 

1. ORA-31001 can be ignore first time, because procedure first drop then create ACL.

2. Replace above Italic value as per your environment.

How to force application users to change their password

Some time there is a requirement to force application users to change their password.
for eg: to implement new validation password policy (role).

There is two ways to achieve this
1. through sql script
2. oracle application "expire day" setting at system administration responsibility --> user ---> there is a option to set expiration no of days.

1. through sql script

there is a script called 'AFCPEXPIRE.sql' located under $FND_TOP/patch/115/sql
eg: sqlplus -a APPS/APPS @AFCPEXPIRE.sql
Or
concurrent program called 'CP SQL*Plus Expire FND_USER Passwords'

Oracle 11g Password CaSe SEnsiTivE

New Capability
Now Oracle 11g Database password is Case Sensitive.

C:\Users\ian>sqlplus CHEST/norma@m

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 10 22:56:47 2008

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\ian>sqlplus CHEST/NORMA@m

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 10 22:56:59 2008

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

ERROR:
ORA-01017: invalid username/password; logon denied

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708040800346734217

And in oracle 10g

C:\>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Mar 23 10:12:46 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn scott/TIGER
Connected.

How can disable password case sensitive option in oracle 11g

In 11g there is one password for enable / disable password case sensitive option.

sec_case_sensitive_logon

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams211.htm#REFRN10299

ORA-01996 & ORA-01994

ORA-01996 & ORA-01994

This error occurred when password file “entries” value is reached to maximum size.

First understand what “ENTRIES” parameter is.
Entries parameter is use for specify numeric value multiple of four.
We can assign any value but it is always use multiple of four.
Like 4,8,12 so on

Password files use for SYSDBA password. And ENTRIES clause use for HOW many database users can connect with SYSDBA privilege or how many times we can grant SYSDBA privilege to any database user.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'D:\oracle\product\10.1.0\Db_1\DATABASE\PWDorcl.ORA' is full

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=shared is set.

Solution:
1. Recreate PASSWORD FILE with MAXIMUM ENTRIES=N VALUE.
2. Restart Oracle Service After recreate PWD file.

Oracle Listener Security: Before 10g but not in 10g.

Did you thing about listener security from Unauthorized access from remote site ?



We can protect our oracle listener through password to prevent Unauthorized access.



There is three way to set listener password.

1. Manually (UNENCRYPTED FORMAT)

2. LSNRCTL tools (ENCRYPTED FORMAT)

3. OEM (Oracle Enterprise Manager)



Reference: http://www.petefinnigan.com/ramblings/set_listener_password.htm


Above procedure is useful only for prior 10g release becuase now in 10g and later listener less password is more secure than listener password.



below is some references there discuss security experts about their views

1. http://andrewfraser.wordpress.com/2007/05/24/listener-passwords-always-for-9i-never-for-10g/
2. http://laurentschneider.com/wordpress/2005/12/encrypted-listener-password.html
3. http://www.petefinnigan.com/weblog/archives/00000653.htm
4. http://andrewfraser.wordpress.com/2007/01/11/9i-unix-startup-shutdown-with-listener-password/
5. http://www.petefinnigan.com/forum/yabb/YaBB.cgi?board=ora_sec;action=display;num=1127398031

Connecting with sys user with Or without pwd

As we know "SYS" & "SYSDBA" is superuser privilege in oracle database.

There is two method to connect SYSDBA user.

1. WITH PASSWORD

2. WITHOUT PASSWORD

For example:

SQL> CONN / AS SYSDBA ---without password



For without password need follow thing to be done.

1. Oracle Database User must have SYSDBA privileges.

2. Operating System User must add in DBA group.

On windows: ORA_DBA group

On linux: DBA group

3. In sqlnet.ora file must content "NTS" in below line

SQLNET.AUTHENTICATION_SERVICES=(NTS)

For example:
SQL> CONN SYS/PWD AS SYSDBA --- with password or prevent to connect without password.

1. Remove OS user from DBA group

2. Edit sqlnet.ora file and change "NONE" to "NTS" in below line

SQLNET.AUTHENTICATION_SERVICES=(NONE)

Default Value is NTS when sqlnet.ora file created.