What is oracle database ? Part VII

     2. Password

If the DBA wants to startup a stopped oracle instance whose database dictionary is not accessible how oracle would authenticate the DBA?
With the Help of password file & parameter remote_login_passwordfile or using OS authentication
This will allow authentication of the DBA to happen outside of the database either using password file or through the operating system (sqlplus “/ as sysdba”) from oracle owner.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

Password files creation:

orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

Complete file name path to be specified along with name e.g. FILE=/u01/app/ora11g/product/11.2.0/dbhome_1/orapwMYDB.
Supplying only a file name will create password file in current directory
The contents of this file are encrypted.

Number of entries/distinct users allowed connecting DB using SYSDBA/SYSOPER
Entries can be reused as users are added to and removed from the password file
Refer below snaps to identify how ENTRIES can be validated using v$pwfile_users

If set to Y, overwrite an existing password file.
If not set or set to N an error is returned if a password file of the same name already exists

If set to y, passwords are case-insensitive.
If not set or set to N password is case-sensitive.


In addition to creating the password file, initialization parameter REMOTE_LOGIN_PASSWORDFILE should be set to the appropriate value. The possible values are:

NONE: Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.

EXCLUSIVE: (default) An EXCLUSIVE password file can be used with only one instance of one database. It enables to add, modify, and delete users & to change the SYS password with the ALTER USER command.

SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
This option is useful if you are administering multiple databases or a RAC database.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

        3. Tnsnames

tnsnams.ora is a configuration file mostly required by clients (but located on both client & Server)  connecting to DB for resolving the connection specification which contains net service names mapped to connect descriptors or net service names mapped to listener protocol addresses.
File Located in $ORACLE_HOME/network/admin

Sample TNS entry:

    (ADDRESS = (PROTOCOL = TCP) (HOST = remedy-ebu-test-db1)(PORT = 1521))

A successful connection can be made it tnsping, ping, telnet to specified host/IP resolves

[ora11g@remedy-ebu-test-db1 ~]$ tnsping REMTST
TNS Ping Utility for Linux: Version - Production on 04-DEC-2012 18:07:14
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-ebu-test-db1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = REMTST)))
OK (50 msec)

       4.  Listener
Listener.ora is a SQL*Net configuration file used to configure Oracle Database Listeners required to accept remote connection requests
File Located in $ORACLE_HOME/network/admin

      (ADDRESS = (PROTOCOL = TCP) (HOST = remedy-ebu-test-db1)(PORT = 1526))


No comments:

Post a Comment

My Popular Posts