Configuring new disks for ASM

RAC Speedup

If you want to make the query response speed up, then alter table with parallel option

alter table tablename parallel;

VIP Concept

1- The system shown here is 2 node RAC

Create and configure a listener

The listener forwards client requests to supported services. These services can be configured statically in the listener.ora file or they can be dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the PMON process.

Create and manage multiple network configuration files

LISTENER.ORA

If you have a server that is running multiple versions of Oracle software (multiple Oracle homes, each home has one or more database instances). You can use multiple listener.ora files.

Transparent Data Encryption (TDE) & LogMiner

1- Change in sqlnet.ora
######################SQLNET.ORA###############################

NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
sqlnet.authentication_services= (NTS)

Create and Manage a tablespace that uses NFS mounted file system file

Direct NFS
Direct NFS is a new feature introduced with Oracle 11g and  is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.

Create and manage bigfile tablespaces

Bigfile Tablespaces
- a tablespace with a single, but very large (up to 4G blocks) datafile.
-A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile.A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile.

Create and manage database configuration files

1- PFILE

Related Topic: Click here to view
Related Topic: Click here to view

Write alert to alert log

execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' Test alert ');

Configure the database environment to support optimal data access performance

The Symptoms and the Problems
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:

Stripe data files across multiple physical devices and locations

Goal of striping data is simple:-
To avoid I/O bottlenecks during parallel processing, all tablespaces  accessed by parallel operations should be striped.  Many current OS's support disk striping hence DBA's need not stripe data  across the disk.

Create and manage temporary, permanent, and undo tablespaces

Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations.Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.

Determine and set sizing parameters for database structures

You can determine/set parameter sizes using Enterprise Manager's Server Tab-> Storage section. This is first section of configuration of database structure, and second is parameters form parameter file.

Create the database

Note: Practice performed on 11g Rel 1 (APPS DB)

Considerations Before Creating the Database
Database Planning Tasks
- Plan the database tables and indexes and estimate the amount of space they will require.
- Plan the layout of the underlying operating system files your database will comprise. To greatly simplify this planning task, consider using Oracle
Managed Files and Automatic Storage Management to create and manage the operating system files that comprise your database storage.

Error: Procedure entry point longjmp could not be located in dynamic link library orauts.dll

If you get "Procedure entry point longjmp could not be located in dynamic link library orauts.dll" while connecting the SQL Plus, your environment variables are not set properly.

Spell the numbers - Examples

Using JSP format

SELECT TO_CHAR(TO_DATE(123.50,'J'),'JSP') to_words FROM   dual;

SELECT    TO_CHAR (TO_DATE (TRUNC (&num), 'J'), 'JSP')
       || ' Point '
       || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (&num, INSTR (&num, '.') + 1)),'J'),'JSP')
  FROM DUAL;

Collect Full DML Statements for specific user without AUDIT

Use a logon trigger for the specific user to enable SQL tracing : it  generates a  trace file in the USER_DUMP_DEST location.The trace file contains all the SQL statements executed by the user after logon.
Create the following trigger in SYS schema:

create or replace trigger SCOTTLOG
after logon
on scott.schema
begin
   -- optionally add a tag to the trace file name to make the files discernable:
   execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER =''SCOTT''';
   -- enable sql_trace for this session:
   execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/

Note that all the SQLs of SCOTT user after logon will be traced and if the required collection is sufficient, disable the trigger to stop the collection and generation of trace files.

SQL> alter trigger scottlog disable;

Note
 i) Overhead involved is trace file generation in user_dump_dest.
 ii) Grant the CREATE SESSION system privilege to the user account rather than granting the CONNECT role. This is necessary  because in the versions older than 10gR2 the CONNECT role includes the ALTER SESSION system privilege and the user can disable sql tracing after logon. To avoid this, do not grant the CONNECT and RESOURCE roles , but grant the necessary privileges explicitly.

Ref: 309798.1

How to check if the IO of the Database is Slow

We will outline some of the thresholds whereby RDBMS Support may consider IO to be slow and thus a potential reason for a performance problem. If the underlying cause for slow performance is found to be a result of slow IO at the OS level, then the appropriate vendor responsible for the IO subsystem (hardware and software) should be engaged to diagnose and correct the situation

How To Configure Anti-Virus On Windows Server Running Oracle Database

When an Anti-virus performs a scan on a file it holds a lock on it. This lock interrupts the normal functioning of the database. To prevent any disaster situation such as database crash/hang, we recommend the following files to be excluded from online anti-virus scanning.

DCD & TCP Keep Alive (Windows)

KNOWN PROBLEMS OR LIMITATIONS  for DCD
1- Of the few reported problems, perhaps the most significant is DCD's poor performance on Windows NT. Dead connections are cleaned up only when the server is rebooted and the database is restarted. Exactly how well DCD works on NT depends on the client's proto implementation.

All Users with Client side Info

Create Or Replace Force View Sys.Av_users_all (Logon_time,
                                               Sid,
                                               Serial#,

Calling External Procedure (DLL) from PL/SQL

If Oracle has been configured for the external procedures , you can call any DLL from your PL/SQL. How to configure for EXTPROC please click on the link below

Configuring SQL*Net for External Procedures

External procedures are functions written in a third-generation language  
(3GL) such as C, and callable from within PL/SQL or SQL as if they were a 
PL/SQL procedure or function.  External procedures enable you to take  
advantage of the strengths and capabilities of a 3GL programming language  
in a PL/SQL environment.

Restricting user to change the password

If for some reason you want to restrict the user to change the password ,

Use the event trigger AFTER ALTER with the attribute function ora_des_encrypted_password 

specific for ALTER USER events:

CREATE or REPLACE TRIGGER pass_change 
    AFTER ALTER on database
          BEGIN
               IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and ora_des_encrypted_password is not null
               THEN
                  RAISE_APPLICATION_ERROR(-20003,  'You are not allowed to alter password user.');
               END IF;
          END;
/

Note:
In the trigger, instead of raising the error, you may want to insert a row into a custom audit table.

How To Audit Application Username Using Trigger

With default auditing Oracle stores Oracle username but not the application username. In order to store application username you need to set CLIENT IDENTIFIER for the application session which is connecting to the database.