How to increase PROCESSES initialization parameter:

1.    Login as sysdba
    sqlplus / as sysdba
   
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
   
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
       
4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile;
    sql> alter system set sessions=555 scope=spfile;
    sql> alter system set transactions=610 scope=spfile;
    sql> shutdown abort
    sql> startup

Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-19758136241&id=2118136.2

Listing inactive database accounts without auditing turned on

I sometimes get asked for information on when users last logged into a database so that the application owner can identify inactive accounts. This is especially useful if they are licensed by the number of users.

This is fine if you are on V12:

select username,last_login from dba_users;

USERNAME             LAST_LOGIN
--------             ----------
ANDY                 10-JAN-17 10:53:22

However, V11 doesn't have this column, and unless you have auditing on you can't use the audit trail.

Due to a quirk with the accounts, an account_status will show as OPEN if the user hasn't logged in since the password expired.

So this query:

select a.name,a.ctime,a.ptime,b.account_status,b.profile,c.limit
from sys.user$ a,dba_users b,dba_profiles c
where c.resource_name='PASSWORD_LIFE_TIME'
and a.name=b.username
and b.profile=c.profile
and c.limit != 'UNLIMITED';

will show the last time the password was changed (ptime) and the number of days after which it will expire (limit).

 NAME CTIME     PTIME    ACCOUNT_STATUS     PROFILE            LIMIT
--------------- -------  ---------          --------           ---- 
ANDY 02-FEB-15 02-FEB-15 OPEN         APP_ADM_DEFAULT          40

So this shows that even though the user was created and password set on the 02-FEB-15, and the password expires after 40 days, the account is still OPEN, not LOCKED or EXPIRED.

According to this Oracle Support note -


Why Account Status Is Open When Expiry Date is Old Date in DBA_USERS (Doc ID 291195.1)

unless the user attempts to connect after the password has expired, the account will show as OPEN.

So any account that is in OPEN status and is past the expiry_date for the profile is likely to be unused.

Why HugePages?

HugePages is a feature integrated into the Linux kernel with release 2.6. This feature basically provides the alternative to the 4K page size (16K for IA64) providing bigger pages.

Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory.

• HugePages can be allocated on-the-fly but they must be reserved during system startup. Otherwise the allocation might fail as the memory is already paged in 4K mostly.

• HugePage sizes vary from 2MB to 256MB based on kernel version and HW architecture (See related section below.)

• HugePages are not subject to reservation /  release after the system startup unless there is system administrator intervention, basically changing the hugepages configuration (i.e. number of pages available or pool size)

The Size of a HugePage

The size of a single HugePage varies according to:

• Kernel version/linux distribution
• HW Platform

The actual size of the HugePage on a specific system can be checked by:
      $ grep Hugepagesize /proc/meminfo

-->Following command to determine the current HugePage usage. The default HugePage size is 2MB on Oracle Linux 5.x and as you can see from the output below, by default no HugePages are defined.

        $ grep Huge /proc/meminfo

1. Large Pages used by this instance (#pages) = HugePages_Total – HugePages_Free + HugePages_Rsvd
2. (HugePages_Total- HugePages_Free)*2MB will be the approximate size of SGA
3. Disable AMM (Automatic Memory Management)
4. Check the MEMORY_TARGET parameters are not set for the database and SGA_TARGET and PGA_AGGREGATE_TARGET parameters are being used instead.
5. If you are running Oracle 11.2.0.2 or later, you can set the USE_LARGE_PAGES initialization parameter to "only" so the database fails to start if it is not backed by HugePages.

Ref.

• Huge Page Implementation Note 361468.1
• Large Pages Information in the Alert Log Note: 1392543.1
• Oracle Not Utilizing Hugepages (Doc ID 803238.1)
• Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
• USE_LARGE_PAGES To Enable HugePages (Doc ID 1392497.1)
• Maximum SHMMAX values for Linux x86 and x86-64 (Doc ID 567506.1)
• Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device (Doc ID 301830.1)

How to enable/disable Archivelog mode in Oracle?

There are two types of logging modes in Oracle database :

ARCHIVELOG : In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

NOARCHIVELOG : In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.

How to check log mode

SQL> archive log list

How to enable ARCHIVELOG mode

SQL> shut immediate;

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

How to disable ARCHIVELOG mode

SQL> shut immediate

SQL> startup mount

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list

How to check ORA error or Viewing Errors with the oerr Utility?

Many Oracle products on UNIX have a utility called "oerr" that can be used to aid error investigation. 
The "oerr" utility is a shell script that is located in the ORACLE_HOME/bin directory. "oerr" requires the ORACLE_HOME to be set and two parameters when called: the facility which is the three to four letter code to the left of dash and the error number.

How to Prevent a User to Drop Own Objects???

CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'VIEW' 
AND ora_dict_obj_owner = 'QO112_1616' 
AND ora_login_user = 'QO112_1616' 
AND ora_dict_obj_name='SAMPLE_NEW'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP SAMPLE_NEW VIEW!');
END IF;
END;

We will get an erorr message when trying to delete object in my case VIEW

How to check if your Oracle client is 32 bit or 64 bit?

SOLUTION 1:
If logged into SQL Plus, the banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit (even though it does not explicitly say so).

SOLUTION 2:
You can specifically query the info by running the following:
SELECT * FROM V$VERSION;

The banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit.
SOLUTION 3:
Run the query to check the following:
select distinct address from v$sql where rownum<2;
If the address returned is 16 characters long, it is 64 bit.
If it is 32 bit you will get an 8 character address.

SOLUTION 4:
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit. If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
*Headsup: In newer versions of the client, the library is not included and this directory may not exist.