Author: aartisinha123

Create Password File

Password file (orapwd utility) in Oracle
Oracle password file stores passwords for users with administrative privileges.

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate the DBA. Obviously, DBA password cannot be stored in the database, because Oracle cannot access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA:
(i) Using the password file or
(ii) Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.

The default location for the password file is:
$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix, %ORACLE_HOME%databasePWD%ORACLE_SID%.ora on Windows.

REMOTE_LOGIN_PASSWORDFILE
The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without bouncing the database.

Following are the valid values for REMOTE_LOGIN_PASSWORDFILE:

NONE – Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. 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.

EXCLUSIVE (default) – Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

SHARED – The password file is shared among databases. 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. However, the only user that can be added/authenticated is SYS.
A SHARED password file cannot be modified i.e. 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 or SYSASM (this is from Oracle 11g) privileges generates an error. All users needing SYSDBA or SYSOPER or SYSASM 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.

This option is useful if you are administering multiple databases or a RAC database.

If a password file is SHARED or EXCLUSIVE is also stored in the password file. After its creation, the state is SHARED. The state can be changed by setting REMOTE_LOGIN_PASSWORDFILE and starting the database i.e. the database overwrites the state in the password file when it is started up.

ORAPWD
You can create a password file using orapwd utility. For some Operating systems, you can create this file as part of standard installation.

Users are added to the password file when they are granted the SYSDBA or SYSOPER or SYSASM privilege.

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]

ENTRIES
Entries specify the maximum number of distinct SYSDBA, SYSOPER and SYSASM users that can be stored in the password file.

This argument specifies the number of entries that you require the password file to accept. The actual number of allowable entries can be higher than the number of users, because the orapwd utility continues to assign password entries until an OS block is filled. For example, if your OS block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

Granting SYSDBA or SYSOPER or SYSASM privileges
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER or SYSASM system privileges for a database.
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
——– —— ——- ——
SYS TRUE TRUE FALSE

Source: Internet

DB Migration from Linux to Windows

1> Create Database Services
oradim -NEW -SID PVMI -STARTMODE manual -PFILE “C:oracleproduct11.2.0dbhome_1databaseinitPVMI.ora”

2>Create Parameter file from the source server and modify.

3> Create Password File.
orapwd file=C:oracleproduct11.2.0dbhome_1databasepwdpvmi.ora password=pvmi entries=6 force=y

4> Start up nomount
set oracle_sid=pvmi
set oracle_home=C:oracleproduct11.2.0dbhome_1
startup nomount

5>Restore controlfile
rman target /
set dbid=3575447358
restore controlfile from “E:PVMI_2_March_Backupcontrol_PVMI_19285_1_873310962.rman”

6>Startup mount

7>Generate file conversion script
select ‘set newname for datafile ‘ || file_id || ‘ to ”E:PVMI’ ||
substr(file_name,instr(file_name,’/’,-1)+1) || ”’;’
from dba_data_files;

8> Catalog Backup pieces and Recover Database
rman
{
set newname for datafile 4 to ‘E:PVMIusers01.dbf’;
set newname for datafile 3 to ‘E:PVMIsysaux01.dbf’;
set newname for datafile 20 to ‘E:PVMIvmi_data12.dbf’;
set newname for datafile 1 to ‘E:PVMIsystem01.dbf’;
set newname for datafile 5 to ‘E:PVMIexample01.dbf’;
set newname for datafile 6 to ‘E:PVMIvmi_data01.dbf’;
set newname for datafile 7 to ‘E:PVMIvmi_data02.dbf’;
set newname for datafile 8 to ‘E:PVMIvmi_data03.dbf’;
set newname for datafile 9 to ‘E:PVMIvmi_data04.dbf’;
set newname for datafile 10 to ‘E:PVMIvmi_index01.dbf’;
set newname for datafile 11 to ‘E:PVMIvmi_index02.dbf’;
set newname for datafile 12 to ‘E:PVMIvmi_data05.dbf’;
set newname for datafile 13 to ‘E:PVMIvmi_data06.dbf’;
set newname for datafile 14 to ‘E:PVMIvmi_data07.dbf’;
set newname for datafile 15 to ‘E:PVMIundo0201.dbf’;
set newname for datafile 2 to ‘E:PVMIpatrol_data.dbf’;
set newname for datafile 16 to ‘E:PVMIvmi_data08.dbf’;
set newname for datafile 17 to ‘E:PVMIvmi_data09.dbf’;
set newname for datafile 18 to ‘E:PVMIvmi_data10.dbf’;
set newname for datafile 19 to ‘E:PVMIvmi_data11.dbf’;
set newname for datafile 21 to ‘E:PVMIvmi_data13.dbf’;
set newname for datafile 22 to ‘E:PVMIpvmi_tbsidx01.dbf’;
set newname for datafile 23 to ‘E:PVMIundo0202.dbf’;
set newname for datafile 24 to ‘E:PVMIvmi_index16k01.dbf’;
set newname for datafile 25 to ‘E:PVMIvmi_index16k02.dbf’;
restore database;
recover database;
}

Script to set newname for rman

When taking database files out of ASM and into a regular filesystem, 2 options exist: 1) set db_create_file_dest in your init.ora or use the rman set newname command. The latter is demonstrated here:

select ‘set newname for datafile ‘ || file_id || ‘ to ”/u02/oradata/DEV/’ ||
substr(file_name,instr(file_name,’/’,-1)+1) || ”’;’
from dba_data_files;

A phased approach to securing your database infrastructure

Phase 1 - Duration: One Day
1.1 Remove Default Passwords
1.2 Configure Oracle Binary Permissions
1.3 Secure Other Executables
1.4 Use umask
1.5 Limit SYSDBA Login
1.6 Create a Listener Password
1.7 Protect the Listener
1.8 Trim Sweeping Privileges
1.9 Change DBSNMP Password

Phase 2- Duration: One Week
2.1 Remove utl_file_dir
2.2 Limit OS Authentication
2.3 Disable Remote OS Authentication
2.4 Secure SQL*Plus Using Product Profile
2.5 Rein In SQL*Plus
2.6 Wrap Sensitive Code
2.7 Convert Derived Grants to Direct Grants
2.8 Limit Tablespace Quotas
2.9 Monitor Listener Logs for Attempted Break-Ins
2.10 Audit and Analyze User Access

Phase 3 - Duration: One Month
3.1 Remove Passwords from Scripts
3.2 Remove Password from RMAN
3.3 Move DBA Scripts to Scheduler
3.4 Lock Down Objects
3.5 Create Profiles of Database Users
3.6 Create and Analyze Object Access Profiles
3.7 Enable Auditing for Future Objects
3.8 Restrict Access from Specific Nodes Only

Phase 4 - Duration: One Quarter
4.1 Enable Fine Grained Auditing
4.2 Activate a Virtual Private Database
4.3 Mask Sensitive Columns
4.4 Encrypt Sensitive Data
4.5 Secure Backups
4.6 Mine History

Difference between DB_NAME and DB_UNIQUE_NAME

Over the past couple of years I have implemented Oracle Data Guard physical standby around a dozen times. Every time I fine-tune the procedure a little more – either eliminating an unnecessary step or discovering some new and often undocumented Oracle functionality.
My preference when building a standby environment is to keep the design as symmetrical as possible. This preference mainly affects naming conventions.
The DB_NAME parameter specifies the name of the database and is the same at all locations. In this example the DB_NAME parameter is PROD.
The DB_UNIQUE_NAME parameter allows a location specific alias to be created for a database. I always try to avoid using names indicative of the role such as PROD_PRIMARY and PROD_STANDBY. These names work well until a switchover is performed at which point the switch-back operation can become really confusing and difficult to document. Therefore I usually try to use geographical values for the DB_UNIQUE_NAME parameter such as PROD_EAST and PROD_WEST. The following discussion uses these values.
The DB_UNIQUE_NAME parameter defaults to the value of the DB_NAME parameter. This is useful when you are creating a new database; fewer parameters to specify; but is not so efficient if that database will become part of a standby configuration.
The DB_UNIQUE_NAME parameter is actually used by Oracle in a number of places. It is used to specify

  • the database name in the OCR;
  • the database directory name within ASM disk groups
  • the diagnostic directory name.

For example if I use DBCA to create a database using the DB_NAME of PROD, the DB_UNIQUE_NAME will default to PROD and will result in the following:

  • The database will be called PROD in the OCR
  • The database directory name within ASM will be PROD e.g. +DATA/PROD/ and +FRA/PROD/
  • The diagnostic location will be $ORACLE_BASE/diag/rdbms/prod

Under normal conditions this is probably what you want. However for a Data Guard standby database you may however, want the location specific value for the DB_UNIQUE_NAME in which case:

  • The database will be called PROD_EAST in the OCR (the database name is still PROD)
  • The database directory name within ASM will be PROD_EAST e.g. +DATA/PROD_EAST/ and +FRA/PROD_EAST/
  • The diagnostic location will be $ORACLE_BASE/diag/rdbms/prod_east

It is easy enough to modify the DB_UNIQUE_NAME parameter after the database has been created, but then you are left with a few problems.

  • The database will be called PROD in the OCR. The original data files etc will have been created in +DATA/PROD
  • Any archived redo logs will be in +FRA/PROD
  • The diagnostic location is $ORACLE_BASE/diag/rdbms/prod

So what happens when we rename DB_UNIQUE_NAME from PROD to PROD_EAST?

  • SRVCTL does not allow databases to be renamed so the entries for the database and instances must be deleted and recreated using the new name of PROD_EAST.
  • If we really care then we need to copy all the data files and redo logs from +DATA/PROD to +DATA/PROD_EAST and to update the control file
  • I have never figured out how to relocate files in the FRA; it may not even be possible.
  • The diagnostic location is created automatically as $ORACLE_BASE/diag/rdbms/prod_east,  but the previous location will still exist and contains some useful files notably the alert.log generated during database creation.

It is possible to modify the DB_UNIQUE_NAME parameter, but the result is a bit messy; not so good for standards-conscious organizations.
How can you work around this? Well the answer is really simple; set the DB_UNIQUE_NAME parameter during database creation. The parameter can be set in the Initialization Parameters page in the DBCA.
However, setting the DB_UNIQUE_NAME parameter in the DBCA will probably result in a failure during database creation. The cause of the failure is the CREATE SPFILE file which is one of the first statements executed during database creation.
If the DB_UNIQUE_NAME is PROD_EAST then by default the CREATE SPFILE statement will attempt to create an SPFILE called +DATA/PROD_EAST/spfilePROD_EAST.ora. However at this stage the +DATA/PROD_EAST directory will not exist within the +DATA disk group and the statement will fail. This appears to be an issue with the CREATE SPFILE statement; other statements such as CREATE DATABASE appear to create the directory if it does not already exist.
You can workaround this problem by creating the directory manually using SQL*Plus or ASMCMD. For example:

$ asmcmd mkdir +DATA/PROD_EAST

If the directory is created before DBCA is executed then the database should be created successfully with the correct names in the OCR, ASM and diagnostic areas.
However, this should really not be necessary; I don’t have access to the source code, but I’m guessing that it would be trivial for Oracle to fix the CREATE SPFILE to create intermediate ASM directories if they do not exist. So I am hoping Oracle product manager will read this and fix the problem in the next patch set….