Steps to create password file in windows & unix

Step 1.
Log on the database machine and create a password file:
For Unix (Shell)
    orapwd  file=$ORACLE_HOME/dbs/orapw password=password_for_sys
For Windows (Command Prompt)
    orapwd  file=%ORACLE_HOME%\database\PWDsid_name.ora
    password=password_for_sys
Step 2.
Add the following line to initservice_name.ora in UNIX, or init.ora in Windows:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
Step 3.
Restart the Database and Test the Remote Login.
connect sys/password_for_sys@tns_name_of_db as sysdba

SYSDBA AUTHENTICATING APPROACHES
   
A SYSDBA authenticating approach is the method of verifying the identity of database administrators. On the layer of data dictionary, Oracle database administrators are authenticated using an account password like other users. In addition to the normal data dictionary, the following approaches are available to secure the authentication of administrators with the SYSDBA privilege:
    * Operating-System-based Authentication;
    * Password-File-based Authentication;
    * Strong and Centralized Authentication (from 11g on).
   
      Operating-System-Based Authentication    
    
It means to authenticate database administrators by establishing a user group on the operating system, granting Oracle DBA privileges to that group, and then adding the database administrative users to that group. Users authenticated in this way can logon to the Oracle database as a SYSDBA without having to enter a user name or password (i.e. “connect / as sysdba”). On UNIX platform, the special user group is called the DBA group, and on Windows systems, it is called the ORA_DBA group.
   
      Password-File-Based Authentication    
      Oracle Database uses database-specific password files to keep track of the database users who have been granted the SYSDBA and SYSOPER privileges    
      Strong and Centralized Authentication    
    
This authenticating approach (from 11g on) is featured by a network-based authentication service, such as Oracle Internet Directory. It is recommended by Oracle for the centralized control of SYSDBA access to multiple databases. One of the following methods can be used to enable the Oracle Internet Directory server to authorize SYSDBA connections:
    * Directory Authentication;
    * Kerberos Authentication;
    * Secure Sockets Layer Authentication.
      CONFIGURING STEPS    
To use the password file authentication, you must configure the database to use a password file. To do so, you first need to create the password file, and then configure the database so that it knows to use it. Steps 1 to 3 require the local login to the database server.
      Step 1:
Create the Password File   
To set a password file on the server-side, log on the server machine where the remote Oracle database resides.
Create the database password file by using the Oracle utility “orapwd.”
      The Orapwd Command For Oracle 8.1.7 through 10g :    
Usage: orapwd file=<filename> password=<password> [entries=<numusers>]
where
    * file – (mandatory) The password filename (Refer to Notice 1);
    * password – (mandatory) The password for the sys user (Refer to Notice 3);
    * entries – (Optional) Maximum number of entries (user accounts) to permit in the file (Refer to Notice 2);
There are no spaces around the equal-to (=) character.
In UNIX:
   
      For Shell :    
    
orapwd  file=$ORACLE_HOME/dbs/orapw password=change_on_install
entries=30
      For SQL* Plus :    
    
host  orapwd file=$ORACLE_HOME/dbs/orapw password=change_on_install
entries=30
The above command creates a password file named “orapw” that allows up to 30 privileged users with different passwords.

In Windows:
   
      For Command Prompt :    
orapwd file=%ORACLE_HOME%\database\PWDorcl92.ora
password=change_on_install entries=30
      For SQL* Plus :    
host orapwd file=%ORACLE_HOME%\database\PWDorcl92.ora
password=change_on_install  entries=30
The above command creates a password file named “PWDorcl92″ that allows up to 30 privileged users with different passwords.
   
      The Orapwd Command For Oracle 11g Release 1 :    
    
Usage: orapwd file=<filename> [entries=<numusers>] [force={y|n}] [ignorecase={y|n}] [nosysdba={y|n}]
where
    * file – (mandatory) The password filename ;
    * entries – (Optional) Maximum number of entries (user accounts) to permit in the file;
    * force – (Optional) If y, permits overwriting an existing password file;
    * ignorecase – (Optional) If y, passwords are treated as case-insensitive;
    * nosysdba – (Optional) For Data Vault installations
There are no spaces around the equal-to (=) character.
The command, when executed, prompts for the SYS password and stores the password in the created password file.
   
Orapwd Command Examples:
In UNIX :
orapwd file=$ORACLE_HOME/dbs/orapw entries=30
Enter password: change_on_install
The above commands create a password file named “orapw” that has “change_on_install” as the password for the sys user and allows up to 30 privileged users with different passwords.
   
In Windows :
orapwd file=%ORACLE_HOME%\database\PWDorcl11.ora entries=30
Enter password: change_on_install
The above commands create a password file named “PWDorcl11″ that has “change_on_install” as the password for the sys user and allows up to 30 privileged users with different passwords.
   
           
      Step 2:
Configure the Database to Use the Password File    
    
By default, an Oracle database is not configured to use the password file. However, you’d better first verify the value of the parameter “remote_login_passwordfile” in initservice_name.ora, in UNIX, or init.ora, in Windows. If the value is “exclusive,” continue with Step 3: Restart the Database. If the value is “shared,” or if the line “REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE” is remarked off, continue with the procedure below: Stop the Database.
Use the SQLPlus show statement to check the parameter value:
   
    
SQL> show parameter password;
   
    
NAME
—————————————–
remote_login_passwordfile  
    TYPE
———–
string
    VALUE
———————–
EXCLUSIVE    
    
Stop the database by stopping the services or using the SQLPlus shutdown immediate statement.
Add the following line to initservice_name.ora, in UNIX , or init.ora, in Windows
   
    
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
   
      Step 4 : (Optional) Change the Password for the SYS User    
    
SQL>PASSWORD sys;
Changing password for sys
New password: password
Retype new password: password
   
      Step 5 : Verify Whether SYS Has the SYSDBA Privilege    
    
Use the SQLPlus select statement to check the password file users:
   
    
SQL> select * from v$pwfile_users;
   
      USERNAME
————————————
SYS     SYSDB
————-
TRUE     SYSOP
————–
TRUE

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...