All posts by dpafumi

12c to 19: Autoupgrade

AutoUpgrade enables customers to upgrade one or many Oracle databases at the command-line with a single command and a single configuration file. AutoUpgrade will run the preupgrade tasks, perform automated fixups where needed, execute the database upgrade, and finish by taking care of post-upgrade tasks. It includes automatic retry and fallback, the possibility to schedule upgrades for future points in time, and the ability to set, change or remove initialization parameters as desired. 

Database can directly be upgraded to 19c from below releases: 11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.0.2, 12.2.0.1 and 18

Continue reading 12c to 19: Autoupgrade

Upgrade 12c to 19c: Traditional

Introduction

This page described how to upgrade single instance of FMS Database from version 11.2.0.4 to 19.3.0.0 in Windows Server 2012 R2 Standard

Source Server Preparation Upgrade Checklist

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

Introduction

This page described how to upgrade single instance of FMS Database from version 11.2.0.4 to 19.3.0.0 in Windows Server 2012 R2 Standard

Source Server Preparation Upgrade Checklist

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

Continue reading Upgrade 12c to 19c: Traditional

RAC Filesystem Options

RAC Review

Let’s begin by reviewing the structure of a Real Applications Cluster. Physically, a RAC consists of several nodes (servers), connected to each other by a private interconnect. The database files are kept on a shared storage subsystem, where they’re accessible to all nodes. And each node has a public network connection.

In terms of software and configuration, the RAC has three basic components: cluster software and/or Cluster Ready Services, database software, and a method of managing the shared storage subsystem.

  • The cluster software can be vendor-supplied or Oracle-supplied, depending on platform. Cluster Ready Services, or CRS Where vendor clusterware is used, CRS interacts with the vendor clusterware to coordinate cluster membership information; without vendor clusterware, CRS, which is also known as Oracle OSD Clusterware, provides complete cluster management.
  • The database software with the RAC option, of course.
  • Finally, the shared storage subsystem can be managed by one of the following options: raw devices; Automatic Storage Management (ASM); Vendor-supplied cluster file system (CFS), Oracle Cluster File System (OCFS), or vendor-supplied logical volume manager (LVM); or Networked File System (NFS) on a certified Network Attached Storage (NAS) device.

Continue reading RAC Filesystem Options

Converting Non-CDB as PDB

Here we are going to convert a NON-CDB to PDB.

The process is simple , from the 12c Oracle Home start DBCA and create a CDB Instance with one PDB. Then you have all the necessary setup for the migration. Now with few simple steps one can migrate to the CDB instance.

In this demo my Non-CDB name is DIEGO and the newly created CDB instance name is GLOBALDB, it has one PDB named PDB001 and will later add DIEGO into the CDB as a PDB.

RAC Basic Information

What is Clustering?
Clustering is the grouping of individual computers in such a way that they can act as a single computer system. These combined resources can then be presented to applications as a single system.  One benefit of a cluster is that the application does not need to be cluster “aware”, and requires no special coding to take advantage of most cluster related services.

Oracle clustering is a shared everything architecture that involves sharing of storage by the systems of the cluster.

Oracle RAC:
Oracle RAC stands for Real Application Cluster. It was introduced by Oracle with 9i release.
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for all your business applications. Oracle RAC is a key component of Oracle’s private cloud architecture.

Single Instance Vs RAC Instance
As can be seen from the below diagram Oracle RAC database has more than one instance, whereas the database is single and shared across all the instances. Thus focus of RAC is on share everything concept. This ensures availability of Oracle database and also the scalability. Each RAC instance is installed on different machine (called nodes) thus scaling the computing power and maximizing availability.

Below is the architecture diagram which shows the implementation of Oracle RAC instance in real life scenario. What it additionally shows is the interconnect (hb) i.e high speed communication between the RAC instances to ensure ACID (Atomicity, Consistency, Independent and Durability).

ASM (Automatic Storage Management) Instance:

An Oracle ASM instance is a special Oracle instance that manages Oracle ASM disks.
It’s an instance for Storage Management. Both the ASM and the database instances require shared access to the disks in an ASM disk group.
ASM instances manage the metadata of the disk group and provide file layout information to the database instances. Database instances direct I/O to ASM disks without going through an ASM instance.
An ASM instance is built on the same technology as a database instance. For example, an ASM instance has a system global area (SGA) and background processes that are similar to those of a database instance. However, an ASM instance cannot mount a database and performs fewer tasks than a database instance.


Oracle Commands for RAC & ASM

I have tried to highlight the commands which are used for different instances. As the system has grown with different components, so have the commands to manage and monitor them. Below table shows the list of commands, their usage, where used (RAC/ASM) and home directory where the command is located.

CommandsUsageRACASMHome
cluvfyCluster Verify Utility or cvu performs system checks in integrity of all nodes, integrity of OCR, connectivity between all nodes, compare properties of one node with that of other and space availabilityYYGrid Home and Oracle Home
olsnodesThe olsnodes command provides the list of nodes, local node name, vip addresses of nodes, status of nodes and type of nodes in cluster.Y
Oracle CRS Home, Run as root user
crsctlStarting and stopping Oracle Clusterware resources, Enabling and disabling Oracle Clusterware daemons, Checking the health of the cluster, Managing resources that represent third-party applications.YY* Voting diskGrid Home
srvctlServer Control Utility to manage the Oracle RAC database. SRVCTL commands to add, remove, start, stop, modify, enable, and disable a number of components/objects, such as databases, instances, listeners, SCAN listeners, services, grid naming service (GNS), and Oracle ASM.Some SRVCTL operations for instance, database, service and nodeapps modify the configuration data stored in the Oracle Cluster Registry (OCR). Good link which shows various options for this command is hereYYOracle Home,Run as oracle user
asmcmdASMCMD is a command-line utility that you can list the content, perform searches, create and remove directories and aliases, display space utilization within Automatic Storage Management (ASM) disk groups. Orafaq link for ASMCMD is here
YPreferably separate ASM home
ocrconfigThe ocrconfig command is used to manage the OCR. Ocrconfig provides the ability to import, export, add, delete, restore, overwrite, backup, repair, replace, move, upgrade, or downgrade the OCR. Same command can be used for OLR using –local option.YYGrid Home,Run as root user
ocrdumpThe ocrdump utility allows you to dump the contents of the OCR or the OLR to a file or to stdout. You can then read the resulting output for diagnostic and administration purposes.YYGrid Home,Run as root user
ocrcheckThe ocrcheck utility is used to validate the integrity of the OCR and the OLR. Use the ocrcheck command whenever you have made any changes to your cluster (such as changing the VIP for example)YYGrid Home,Run as root user
oifcfgThe Oracle Interface Configuration Tool is used to define and administer network interfaces such as the public and private interfaces.Y
Grid Home,Run as oracle

Start DG Database Automatically

In order to start a DG database automatically you can use DGMGRL. You must have parameter dg_broker_start=true,
You can set it to true using:
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

When database is started from DGMGRL, it starts the database in mode according to database_role.

For Primary, it will start database in open read write stage.
For Physical Standby, it will start database in mount stage and also start the managed recovery.
For Logical Standby, it will start database in open stage and also start the SQL apply.

Steps for Windows:
1- Set ORA_SID_AUTOSTART to false in windows registry.

2- Create a batch job on the both primary and standby database:
start1.bat
=========
set ORACLE_SID=
dgmgrl / “startup”

start2.bat
======
set ORACLE_SID=
dgmgrl sys/ “startup”

++ Use either of 2 (start1.bat or start2.bat), only difference in these is OS and database authentication.

3- Now schedule this bat file to execute automatically at the system reboot to start the database automatically

Continue reading Start DG Database Automatically

All Datapump Options

Oracle 12c datapump have following clauses, which we would discuss one by one:-

EXCLUDE – This is used to filter the metadata from the export operation. This is more like a where clause which you can use to exclude any object or schema from the export job.

Example if you want a full database export except 1 schema (say TEST), then:-

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp FULL=YES EXCLUDE=SCHEMA:"='TEST'" LOGFILE=test.log

Similarly suppose you want to exclude all indexes from a schema export and the condition is to export only indexes that starts with TEST_%, then:-

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST EXCLUDE=INDEX:"LIKE 'TEST_%'" LOGFILE=test.log

So using this clause you can exclude any specific content from the dump, try and explore this option more. To get a list of available objects that can be excluded query the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS views.

INCLUDE – Exactly opposite to EXCLUDE clause, this is used when you explicitly want to export, say only some tables, indexes along with views, packages in a schema dump, so only the the object types explicitly mentioned in the include clause would be imported and nothing else.

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST INCLUDE=TABLE:"IN ('EMP', 'DEPT')" INCLUDE=PROCEDURE INCLUDE=INDEX:"LIKE 'TEST%'" LOGFILE=test.log

So the above expdp command would only export tables EMP & DEPT, PROCEDURE and all indexes starting with TEST% no other objects of TEST schema would be exported.

QUERY – This clause allows you to filter the data that gets exported, think of it as EXCLUDE & INCLUDE clause but with more control over filtering, thus this is more like a WHERE clause of the expdp command.

When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table.

It accepts values in the form:- QUERY = [schema.][table_name:] query_clause

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST QUERY=TEST.EMP:"WHERE DEPT_ID > 90 AND SAL > 90000" LOGFILE=test.log

Above command would export all tables of TEST schema but only specific records of EMP table.

CLUSTER – This is used in RAC env. and used to spread the worker process on other oracle instances to utilize RAC resources for datapump jobs. Continue reading All Datapump Options