Change Date and Time Formats In Oracle Database?

Option 1. If We want to change this format to DD/MONTH/YYYY format.

SQL> alter session set nls_date_format=’DD/MONTH/YYYY’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

—————–

20/OCTOBER  /2017

Option 2. If we want get the both date and time

SQL> alter session set nls_date_format=’DD/MONTH/YYYY HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

————————–

20/OCTOBER  /2017 17:19:12 –It is 24 hours format.

SQL> alter session set nls_date_format=’DD/MONTH/YYYY HH:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

————————–

20/OCTOBER  /2017 05:19:43  –It is 12 hours format.

Difference between P-File and SP-File?

Parameters can be changed through following options:

1)alter system

2)alter session

Command

SQL>alter system set resource_limit=true scope=_________;

Where ______ is

BOTH=changes will be applied for current instance and future instance;

OR

MEMORY=changes will be applied only for current instance;

OR

SPFILE=changes will be applied after restart the database.

SP-File  Can be created with the following command

SQL> create spfile from pfile;

And Vice Versa Pfile can be Created by

SQL> create pfile from spfile;

Oracle Real Application Cluster Intro

RAC Defination

Oracle RAC stands for Oracle Real Application cluster. It is a clustering solution that ensures high availability of databases by providing instance failover and Load Balancing features.

Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.

Oracle RAC instances use two processes

1- GES(Global Enqueue Service)

• The Global Enqueue Service (GES) manages or tracks the status of all the Oracle enqueuing mechanism.
• This involves all non Cache fusion intra-instance operations.
• GES performs concurrency control on dictionary cache locks, library cache locks, and the transactions.
• GES does this operation for resources that are accessed by more than one instance.

2- GCS(Global Cache Service)

• The main function of the global cache service (GCS) is to track the status and location of the data blocks.
• Status is the resource role and the resource mode.
• The GCS is the main mechanism by which cache coherency among the multiple caches is maintained.
• GCS maintains the modes for blocks in the global role and is responsible for block transfers between the instances.
• The LMS processes handle the GCS messages and carry out the bulk of the GCS processing.

Oracle RAC instances are composed of following Background Processes

ACMS—Atomic Controlfile to Memory Service (ACMS)

In an Oracle RAC environment, the atomic controlfile to memory service (ACMS) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure.

GTX0-j—Global Transaction Process

he GTX0-j process provides transparent support for XA global transactions in a RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions.

LMON—Global Enqueue Service Monitor

The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.

LMD—Global Enqueue Service Daemon

The LMD process manages incoming remote resource requests within each instance.

LMS—Global Cache Service Process

The LMS process maintains records of the datafile statuses and each cached block by recording information in a Global Resource Directory (GRD). The LMS process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances. This processing is part of the Cache Fusion feature.

LCK0—Instance Enqueue Process

The LCK0 process manages non-Cache Fusion resource requests such as library and row cache requests.

RMSn—Oracle RAC Management Processes (RMSn)

The RMSn processes perform manageability tasks for Oracle RAC. Tasks accomplished by an RMSn process include creation of resources related Oracle RAC when new instances are added to the clusters.

RSMN—Remote Slave Monitor

Remote Slave Monitor manages background slave process creation and communication on remote instances. These background slave processes perform tasks on behalf of a coordinating process running in another instance.