Create Oracle password file

In Oracle there is two ways to authenticate database administrators. OS authentication or with Oracle password file. If you have set 'shared or 'exclusive' in remote_login_passwordfile init parameter then Oracle password file is used. 'exclusive' is the default value.

And the default place for password file is on unix:

$ORACLE_HOME/dbs/orapw$ORACLE_SID

and on windows:

%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora

You can create oracle password file with orapwd utility (as oracle user and $ORACLE_SID and $ORACLE_HOME env parameters set):

orapwd file=orapwtestdb password=xxxxx

you can also use 'entries' option when you create paasword file. this options tells how many user can be added into the file:

orapwd file=orapwtestdb password=xxxxx entries=5

If you give user SYSDBA or SYSOPER privilege that user will be added into the password file automatically.

You can check password file users from sqlplus  v$pwfile_users  view:

select * from  v$pwfile_users;

Oracle Database Availability Changes.

There are several ways to affect Oracle database availability.

1. First when you start the database you can start it in mount or in nomount state. 
SQL> STARTUP NOMOUNT;
SQL> STARTUP MOUNT;

These states are used with administrating operations. For example with nomount state you can run restore jobs for controlfiles with RMAN and with mount state you can change database settings such as archiving. But with these states you database is answering only locally and only for certain commands.

For opening database to general use run following (this is same as: alter database open read write; ):
SQL> ALTER DATABASE OPEN;
If you wan't to open the database only READ access you can do it with this (this doesn't allow any writes):

SQL> ALTER DATABASE OPEN READ ONLY;

You can also use (RESTRICT can be also used with open, mount and nomount):

SQL> STARTUP RESTRICT;

With restricted mode only users with RESTRICTED SESSION system privilege can connect into database and only local connections are available. Only database administrators should have this privilege.

2. Second you can use logon and logoff triggers to make restrictions for user sessions connecting into database or disconnecting:

Remember that connection restriction made by these kind of trigger does not affect database administrator users unless you exclude ADMINISTER DATABASE TRIGGER privilege from that dba user.

Syntax goes like this:

#Logon trigger:

SQL> CREATE OR REPLACE TRIGGER <trigger_name> 

           AFTER LOGON ON <schema_name>

           BEGIN

               <trigger actions>

          END;

#Logoff trigger:

SQL> CREATE OR REPLACE TRIGGER <trigger_name> 
           BEFORE LOGOFF ON <schema_name>
           BEGIN
               <trigger actions>
          END;


More info can be find for example here:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_triggers.htm#TDDDG52900




3. Third you can use sqlnet.ora (this works on Oracle11g with older version you need to use 
protocol.ora config file) to make IP restriction for database connections:


Add following lines in sqlnet.ora file:
#This creates a hard failure when host name in exclude/invited list fail to resolve IP address:
tcp.validnode_checking = YES
#To specify which clients are denied access to the database:  
tcp.excluded_nodes = {list of IP_addresses}
#To specify which clients are allowed access to the database:
tcp.invited_nodes = {list of IP_addresses}

After you have done these changes you have to restart the listener:
lsnrctl status
lsnrctl stop
lsnrctl start
lsnrctl status






4. Fourth you can use your firewall settings (or iptables locally) to restrict database availability.

Oracle in vmware memory recommendations

There are several vmware documents that gives recommendations about memory usage with Oracle servers. Example: http://www.vmware.com/files/pdf/partners/oracle/Oracle_Databases_on_VMware_-_Best_Practices_Guide.pdf
And there are 2 main points in those documents.

1. Set server memory reservations equal to the size to the Oracle SGA. This is because it's avoid unnecessary  kernel swapping between ESX and quest OS.

2. Use large memory pages. This is because it's improve Oracle databases performance in vmware.


Large Memory Pages usage in Linux:

This is what you need to do to start using large memory pages in linux:

1. Add memlock user limits in: /etc/security/limits.conf
Set the value (in KB) slightly smaller than installed RAM.
And if you already have these values in limits.conf then check that they are correctly set.
    #HugePages   
    *   soft   memlock    6291456
    *   hard   memlock    6291456

2. Logon to the Oracle product owner (usually oracle) and check the memlock limit
    $ ulimit -l
     60397977


3. With Large Memory Pages ( HugePages ) you have to use ASMM (Automatic Shared Memory Management) because AMM ( Automatic Memory Management ) is not working with HugePages.

So when you creating new database you have to choose ASMM and set PGA Max and SGA Max (and these combained max should be same as limits.conf memlock setting). You can also change AMM to ASMM after database installation but it is easier to do it when you install the database.



4. After you have started to use your database and there is data inside it you have to run hugepages_settings.sh script. This script calculate a recommended value for the vm.nr_hugepages parameter.
You can get this script from My Oracle Support:

Shell Script to Calculate Values Recommended Huge Pages/Huge TLB Configuration ( MOS ID 401749.1 )
    $ ./hugepages_settings.sh
    ...
    Recommended setting: vm.nr_hugepages = 1496
    $


5. Add vm.nr_hugepages and vm.hugetlb_shm_group  parameters in /etc/sysctl.conf file. vm.hugetlb_shm_group parameter value is the dba group id )


6. Stop all the database instances and reboot the server.
After reboot check that values are correct:
grep HugePages /proc/meminfo
HugePages_Total:    1496
HugePages_Free:      485
HugePages_Rsvd:      446
HugePages_Surp:        0

To make sure that the configuration is valid, the HugePages_Free value should be smaller
than HugePages_Total and there should be some HugePages_Rsvd.
The sum of Hugepages_Free and HugePages_Rsvd may be smaller than your total combined SGA as instances allocate pages dynamically and proactively as needed.



7. Remember that if you change following you have to check your recommended hugepages setting again with the script:
    Amount of RAM installed for the Linux OS changed
    New database instance(s) introduced
    SGA size / configuration changed for one or more database instances




There is document in My Oracle Support (MOS) how to use Large Memory Pages in Linux:
HugePages on Oracle Linux 64-bit (MOS ID 361468.1)
( https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocDsrc=KB&bmDocID=361468.1&bmDocType=REFERENCE&viewingMode=1143&from=BOOKMARK&bmDocTitle=HugePages%20on%20Oracle%20Linux%2064-bit%29%29 )