All posts by Vincent

Oracle DBA OCP 10g, 9i, 8 Electronic music producer

ASM, RAC

RAC architecture concepts

2019/04/25 Vincent

Oracle Real Application clusters (RAC) allows multiple instances to access a single Oracle database. These instances often run on multiple nodes.

The Component Diagram with explanation shows all the components that inter-relate to each other and together make up THE “Oracle RAC architecture“.

RAC is the principal component for the Oracle Grid Architecture. It’s an option to the Oracle Database that provides High Availability (HA) and scalability to the Oracle Database without requiring any application changes.

From a system point of view, a group of independent servers defines a cluster. These servers are inter-connected and cooperate as a single system.

Oracle RAC is heavily dependent on the interconnect, an efficient and high speed private network

Shared vs Dedicated Database components in a RAC architecture

As an Oracle DBA, you know that a standard database runs on a single instance. In the RAC architecture, the concept is different because some components are shared and others are dedicated for each instance.

Shared Database Components in Real Application Cluster

Datafiles, Control Files and Flash Recovery Log

Control Files, Datafiles and Flash Recovery Log are shared accross all instances in a shared storage area (NAS, SAN).

Online Redo Logfile

In an Oracle RAC database, each instance must have at least two groups of redo log files.
Only one instance can write in it’s place but other instances can read during recovery and archiving. If an instance is down, log file switches by remaining instances can force the idle instance redo logs to be archived.

Dedicated Database components

SGA

Each instance has its own SGA.
Though each instance has a local buffer cache, Cache Fusion causes sharing of cache and hence resolves the problems like concurrency.
Oracle Cache Fusion is the magic that works in the background to synchronize the cache of all the instances running on the different nodes. This synchronization allows multiple users sessions to execute concurrent transactions on either instance of the Oracle RAC Database without incurring stale reads.

New in Oracle19c RAC, Database Reliability Framework(DRF) attempts to detect any problems early before it can cause disruption in service. The concept is to detect problems and identify root cause.

Background processes

Each instance has its own set of background processes.

Archived Redo Logfile

Private to the instance but other instances will need access to all required archive logs if a media recovery is required by the system.

Alert Log and Trace Files

These files or private to each instance. Other instances never read or write to those files.

The ORACLE_HOME

On a single instance, you create the Oracle database home on the same server than the Database. As a shared file system is mandatory for RAC, you can use it to install your Oracle Home. In this case, you will create it on an Oracle ASM Cluster File System (ACFS). The Oracle Home will be available on all nodes of the cluster.

Installing a Shared Oracle database home has many management advantages in a multi node cluster environment: Out-of-place patching with ACFS snapshots significantly improves the patching process. It eliminates database downtime when coupled with the on-line migration feature of RAC and minimized downtime otherwise.

On Node-Local Oracle Database Homes; The advantage it provides is the ability to apply certain one-off patches in a rolling upgrade fashion.

Specific RAC components

The major components of a Oracle RAC system are:

Shared disk system
Oracle Clusterware
Cluster high-speed Interconnect

Shared disk system

The shared storage provides the concurrent access by all the cluster nodes to the storage array.
Oracle provides a very flexible and high performing shared storage File System, “Automatic Storage Management” or ASM.

ASM is Oracle’s recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices. ASM uses disk groups to store datafiles; an ASM disk group is a collection of disks that ASM manages as a unit. The ASM volume manager functionality provides flexible server-based mirroring options. ASM also uses the Oracle Managed Files (OMF) feature to simplify database file management.

When using Oracle RAC, it’s a good idea to deploy Oracle Flex ASM.
This feature enables an Oracle ASM instance to run on a separate physical server from the database servers. With this deployment, larger clusters of Oracle ASM instances can support more database clients while reducing the Oracle ASM footprint for the overall system.

Clusterware, CRS and OCR

Each of the instances in the cluster configuration communicates with other instances by using the cluster manager or clusterware.
Oracle Clusterware is the technology which unifies servers in a server farm to form a cluster.
Oracle Clusterware is a complete, free-of-charge clustering solution that can be used with Oracle RAC, RAC One Node and even Single instance Oracle databases. It is shipped with Oracle Grid Infrastructure (GI). GI is a suite of software packages which includes Oracle Automatic Storage Management (ASM) for databases and Oracle Automatic Storage Cloud File System (ACFS).

New in 19c is support for bidirectional ACFS snapshots and even better integration with Oracle Data Guard when using ACFS to store the datafiles.

The Clusterware software is run by the Cluster Ready Services (CRS) using the Oracle Cluster Registry (OCR). The OCR records and maintains the cluster and node membership information and the voting disk which acts as a breaker during communication failures. Consistent heartbeat information travels across the interconnect to the voting disk when the cluster is running.

Oracle Clusterware 19c enhances the new deployment options for easier management and deployments of large pool of clusters. This new architecture is called Oracle Cluster Domain. It of a single Domain Services Cluster (DSC) and one or more Member Clusters. DSC provides many services which can be utilized by the four new types of Member Clusters.

Cluster high-speed Interconnect

In an Oracle Real Application Clusters (RAC) environment, all the instances or servers communicate with each other using high-speed interconnect on a private network. The “Interconnect” enables all the instances to be in sync in accessing the data.

You can use the Oracle Enterprise Manager Interconnects page to monitor the Oracle Clusterware environment. The Interconnects page shows the public and private interfaces on the cluster and the load contributed by database instances on the interconnect.

The CLUSTER_INTERCONNECTS parameter can be used to override the default interconnect with a preferred cluster traffic network. This parameter is useful in Data Warehouse systems that have reduced availability requirements and high interconnect bandwidth demands.

What are the benefits of integrating the Oracle cluster architecture?

Your application is more scalable ; if you need more power, just add a new node.
You can also reduce the total cost of ownership for the infrastructure by providing a scalable system using low-cost commodity hardware
In case of a problem, you have the ability to fail over from one node to another
You can increase throughput on demand for cluster-aware applications. One more time, increase cluster resources by adding servers to your cluster
Increase throughput for cluster-aware applications by enabling the applications to run on all of the nodes in a cluster or just in a selection of nodes
You can easily program the startup of applications in a planned order. In that way you ensure dependent processes are started in the correct sequence
Ability to monitor processes and restart them if they stop
With the RAC architecture, you eliminate your Single Point of Failure (SPOF) and unplanned downtime due to hardware or software malfunctions
And finally, you can reduce or eliminate planned downtime for software maintenance

If you want more information than this tutorial you can read the official overview for Oracle 19c: https://www.oracle.com/technetwork/database/options/clustering/rac-twp-overview-5303704.pdf

Thank you for reading this blog!

Author: Vincent Fenoll – Oracle DBA

Reports

View Oracle hidden parameters

2019/04/14 Vincent

Hidden parameters in Oracle always start with an underscore.

It is not possible for the DBA to see the hidden parameters with the SQL*Plus command “show parameter” or by querying v$parameter. Unless the hidden parameter is explicitly set in spfile/init.ora file.

How Can I list all Hidden Parameters set in The database?

As they are explicitely set in the init file, you can create a report that shows all the hidden parameters using the v$parameter view.
The following sql statemant lists undocumented parameters but can also be used to list documented parameters, that can be set in the spfile or init.ora file:

col name for A45
set lines 120
col value for A40
set pagesize 100
select name, value from v$parameter where name like '\_%' escape '\';

How can I list all hidden parameters available?

If you want to list all hidden parameters available for your version along with a description:

select 
ksppinm,
ksppdesc 
from 
x$ksppi
where 
substr(ksppinm,1,1) = '_';

How can I set the value of a hidden parameter?

You can change a hidden parameter, the same way as you would any other init.ora parameters but you need to put double quotes for the parameter name:

alter system set "_pga_max_size"=5G scope=spfile sid='*';

A Good DBA needs to know what hidden parameters are set in the database and their values. Especially during upgrade, database migrations or performance tuning problems.

Oracle has hundreds of initialization parameters, which are hidden and undocumented. Many savvy Oracle professionals are known to commonly adjust the hidden parameters to improve the overall performance of their systems.

Disclaimer: It is not recommended to change hidden parameter without consent of Oracle Support since Oracle can make your system unsupported. You can be responsible for data corruption, performance degradation because of bad SQL plans or other problem. the undocumented init parameters are only used in emergencies or to fix a bug. Some of these parameters are Operating system specific and used in unusual recovery situations. Hence, these parameters should be manipulated carefully and preferably not without recommendation from an Oracle Database Administrator.

Author: Vincent Fenoll Oracle DBA

Reports, Troubleshooting

Ultimate Database Health Check (DBMS_HM)

2019/04/11 Vincent Leave a comment

The Oracle DBA wants to run Health Checks command with Health Monitor upon his database.

With Oracle 12c/18c, these checks can be done on a regular basis daily/monthly:
– DB Structure Integrity Check
– CF Block Integrity Check
– Data Block Integrity Check
– Redo Integrity Check
– Transaction Integrity Check
– Undo Segment Integrity Check
– Dictionary Integrity Check
– ASM Allocation Check

Perhaps you have datafile, dictionary, block, undo, redo, or another corruption in your database? You might actually be running just fine and not even know it.

Oracle Database 12c/18c includes a framework called Health Monitor for running diagnostic checks on your database.

How to run a health check on the Oracle database?

BEGIN
   DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');
END;
/

or

BEGIN
   DBMS_HM.RUN_CHECK (check_name     => 'Transaction Integrity Check',
                      run_name       => 'my_transaction_run',
                      input_params   => 'TXN_ID=22.87.1');
END;

Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;

Listing all the Health Check executed (Health Monitor View):

SELECT run_id,
       name,
       check_name,
       run_mode,
       status,
       src_incident,
       num_incident,
       error_number
  FROM v$hm_run;

Viewing the list of checks that can be done on your database

 SELECT name
  FROM v$hm_check
 WHERE internal_check = 'N';

Health checks accept input parameters, some are mandatory while others are optional.

Displaying parameter information for all health checks

  SELECT c.name check_name,
         p.name parameter_name,
         p.TYPE,
         p.DEFAULT_VALUE,
         p.description
    FROM v$hm_check_param p, v$hm_check c
   WHERE p.check_id = c.id AND c.internal_check = 'N'
  ORDER BY c.name;

Periodic database health checks help keep your database running smoothly without corruption and prevent more serious conditions from developing later.

Health Monitor checks and examine the several parts of the Oracle database stack. This tool detects data dictionary corruptions, datafile corruptions. It will check logical or physical logical block corruptions and rollback (undo) or redo corruptions.

The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.

For a Healthy database!

Vincent Fenoll – Oracle OCP Database administrator in Montreal

Datapump

How to Kill a datapump job and cleanup orphaned jobs in Oracle

2019/04/09 Vincent Leave a comment

Download Brave : Secure, Fast & Private Browser with Adblocker

In this article you will learn how to:

Kill a datapump job from dba_datapump_jobs using expdp or with an internal package
Cleanup orphaned datapump jobs in Oracle

Perhaps you were looking for stop/drop job in DBMS_SCHEDULER.

How to kill a datapump job in DBA_DATAPUMP_JOBS?

You can kill an oracle datapump job using two methods:
– First method includes killing data pump job via data pump export prompt
– The second method is by running a SQL package in SQL*Plus.

1- Kill Oracle datapump export job from expdp

— Find the job name of the datapump export
select job_name from dba_datapump_jobs;
==> SYS_EXPORT_FULL_05

-- Connect to datapump export and Kill the job
$ expdp system/my-password@ORCL attach=SYS_EXPORT_FULL_05
Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes

2- How to Interrupt and Kill a Data Pump Job from a SQL package

— Find the job name of the datapump export
select job_name, owner_name from dba_datapump_jobs;
==> SYS_EXPORT_FULL_05 and SYS

-- Connect to datapump export and Kill the job
DECLARE
v1 NUMBER;
BEGIN
v1:=DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_05','SYS');
DBMS_DATAPUMP.STOP_JOB (v1,1,0);
END;
/

After stopping/cancelling the datapump job, oracle removes the dmp files that have been produced. You can check again the dba_datapump_jobs view to verify:
SQL> select * from dba_datapump_jobs;

More info on view dba_datapump_jobs in official the Oracle Documentation.

How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?

Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.

Below is a step-by-step instruction on how to do this.

Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

Step 2: Drop the master tables

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

Step 3: Identify orphan DataPump external tables

Check and drop external tables created for datapump jobs with select object_name, created from dba_objects where object_name like ‘ET$%’

Step 4: Purge recycle bin

If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘”‘ || job_name || ‘”;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

Step 5: Confirm that the job has been removed

Run sql statement from step 1.

Now you should be able to run your script with the same job name without any issues.

Hope this post will help!

Author: Vincent Fenoll – Oracle DBA

Compatibility: Oracle Database – Standard/Enterprise Edition – Version 10g to 18c [Release 10.1 to 12.2/18]

Unix

Lock, expire and delete Linux account

2019/04/08 Vincent

Sometimes the Oracle DBA has to I delete a Unix user account under Linux operating systems including the home directory.

How do I expire, delete or remove a user’s access from my server?

Deleting user account in Linux is an administrative task to remove user login credentials from system configuration files such as /etc/passwd, /etc/shadow and files which are owned by that particular user from the Unix server.

These command must be run as root user on Linux.

# Just Lock the password
usermod -L myusername

# Just Expire the account
chage -E0 myusername

# Delete the account. userdel is a low level utility for removing users. On Debian, administrators should usually use deluser instead.

# Be careful, User deletion is irreversible!
userdel myusername

-- Use these 2 options to delete that user's home directory and the spool of mails
-r : Remove Unix user account including home directory and mail spool
-f : Delete Linuxuser account with force removal of files

The userdel command modifies the following system account files:
/etc/group, /etc/login.defs, /etc/shadow, /etc/subgid and /etc/subuid.

How to clean associated objects?

If you want to clean other objects like cron jobs, files, print jobs; you will have to do it manually like that.

How to clean cron table
crontab -r -u myusername

How to clean print jobs
lprm myusername

How to change the owner of files owned by myusername
find / -user myusername -exec chown newUserName:newGroupName {} \;

Author: Vincent Fenoll, Oracle DBA in Montreal

Oracle Database SQL scripts