Multitenant Part 3 : PDB Refresh

Multitenant Part 3 : PDB Refresh

The refreshable PDB at target will be in read only mode and 

The read-only PDB can be used for reporting purposes.

Later point of time, You can change a refreshable PDB to a non-refreshable PDB, but  remember not vice versa.

Now, let see how it works via this graphic,

From Prod CDB, we do clone of the PRODPDB1, once it done, at Dev site it should be in read only mode. 

Technology behind this refreshable PDB is, PDB hot clone only. Very first you need to do hot clone with refresh mode parameter in create pluggable command.

After that, whenever you need latest version of your production data, you just need to refresh it. And as additional step you can make new PDBs from your refreshable PDB

Step by Step to perform Refreshable PDB

Here I would like to share step by step commands to perform Refreshable PDB

Let’s define what could be our database name for Production and Development and CDB/PDB name.

Throughout our scenario I will follow below names to be performed.

Source Database Server (Production)

CDB Name : PRODCDB

PDB Name : PROPDB1

Target Database Server (Development)

CDB Name : DEVDCDB

PDB Name : DEVPDB1

Prerequisites of Scenario

You can follow same set of commands and replicate this in your environment.

These prerequisites are common for all our scenarios, like, hot clone, refreshable PDB, PDB relocation and snapshot carousel, other scenarios you can found in my blog or follow the link embedded above. 

Source Database Server

At source we need to configure database in archive log mode if not, it is mandatory to do it and local undo mode, so each PDB will have their own undo tablespace.

Database should be in Archive log mode and local undo mode.

Target Database Server

• Add tns entry of source database and create database link to connect to source database.
• There should be a common user in Source Database
• Grant privileges to common user i.e. create session and create pluggable database to all.

CREATE DATABASE LINK prod_link CONNECT TO c##clone_user IDENTIFIED BY  clone_user USING 'PROPDB1';

Once we are done with these prerequisites, only few commands need to run after that to complete the task.

Here, I am giving two example to create refreshable PDB 1) Manual 2) Automatic as below,

Example 1

In this example, database refresh mode is manual, it means that you can refresh database at your own peace whenever  you need. 

Create manual refreshable PDB

CREATE PLUGGABLE DATABASE PROPDB1_RF FROM PDB1@prod_link_pub REFRESH MODE MANUAL;

After creating refreshable PDB there may be some change happens to your main database and you want to refresh again to reflect it in your PDB so there are few steps to refresh it.

Manually Refresh your PROPDB1_RF from Source PROPDB1.

First, set your container to refreshable PDB

alter session set container=PROPDB1_RF;

Second, close your PDB

alter pluggable database PROPDB1_RF close immediate;

Third, refresh your current PDB

alter pluggable database PROPDB1_RF refresh;

Fourth, And finally, you can open it in read only mode.

alter pluggable database open read only;

Example 2

In this in second command refresh mode is every 10 min, so your refreshable PDB will be refresh automatically in every 10mins. 

CREATE PLUGGABLE DATABASE PROPDB1_RF FROM PDB1@prod_link REFRESH MODE EVERY 10 MINUTES;

Create clone from Refreshable PDB

Addition to that, you can create one or more PDBs from your refreshable PDB.

A refreshable clone PDB must be either close or read only mode. A refreshable PDB must be closed when a refresh is performed. 

In automatic refresh, If it is not closed when automatic refresh is attempted, then the refresh is deferred until the next scheduled refresh.

Command to create New PDB from Refreshable PDB

sql> create pluggable database PDB1CLONE from PROPDB1_RF;

sql> alter pluggable database PDB1CLONE open;

Refreshable PDB Switchover

In Oracle 12.2, Refreshable clone PDB was introduced.  In Oracle 18c, now it allows you to switch the roles of a source PDB and a refreshable PDB. This functionality is known as Refreshable PDB Switchover.

It means that you can make your refreshable PDB as primary one. And here is the command for the same.

alter pluggable database PROPDB1_RF refresh mode every 10 minutes from c##clone_user@prod_link_pub switchover;

I hope this article will help you to create refreshable PDB seamlessly.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 2 : PDB Hot Clone

 Multitenant Part 2 : PDB Hot Clone

• Introduction
• Histroy about PDB clone
• Step by Step to Perform Hot Clone
• How Stuff Works?

Introduction

Developer and testing team always require fresh database to identify bug or issues.

This is just a graphical representation of what we are going to do now.

At PRODCdb container database we have ProPdb1, this we are going to clone at our DEVCdb container database as HOTPorPdb1

History about PDB clone

Very quickly there is some history about clone pdbs.

In 12cR1, pdb clone was introduced; this was a feature with some restrictions. And restriction is like you have to make your pdb in read only mode. Basically this is cold cloning, not a hot clone. Because our pdb in read only mode. 

Later part in 12cR2, new feature is introduce as Hot clone, you can do cloning when your database / pdb is in read/write mode.

And later in 19c, remote clone can be done via dbca too in silent mode.

Step by Step to perform Hot Clone

Here I would like to share step by step commands to perform Hot Clone

Let’s define what could be our database name for Production and Development and CDB/PDB name.

Throughout our scenario I will follow below names to be performed.

Source Database Server (Production)

CDB Name : PRODCDB

PDB Name : PROPDB1

Target Database Server (Development)

CDB Name : DEVDCDB

PDB Name : DEVPDB1

Prerequisites of Scenarios

You can follow same set of commands and replicate this in your environment.

These prerequisites are common for all our scenarios, like, Hot Clone, Refreshable PDB, PDB Relocation and Snapshot Carousel, other scenarios you can found in my blog or follow the link embedded above. 

Source Database Server

At source we need to configure database in archive log mode if not, it is mandatory to do it and local undo mode, so each pdb will have their own undo tablespace.

Database should be in Archive log mode and local undo mode.

Target Database Server

Step 1 : Add tns entry of source database and create database link to connect to source database.

Step 2 : There should be a common user in Source Database

Step 3 : Grant privileges to common user i.e. create session and create pluggable database to all.

Create a database link which will be later used for cloning

CREATE DATABASE LINK prod_link CONNECT TO c##clone_user IDENTIFIED BY  clone_user USING 'PROPDB1';

Once we are done with these prerequisites, only few commands need to run after that to complete the task.

I always say that, each task have five steps,  and three steps we already completed here so remaining two steps needs to complete it.

On Target Server

Hot Clone from ProdCdb to DevCdb, Now, there are only two steps remaining. 

Step 4 : to create pluggable database from target database using database link.

Create pluggable database from db link,

CREATE PLUGGABLE DATABASE PROPDBHTCLONE FROM PROPDB1@prod_link;

Step 5 : Now, just to open database using alter pluggable database open;

After creation, new PDB will be in MOUNTED state. Open it to change the state to READ WRITE.

ALTER PLUGGABLE DATABASE PROPDBHTCLONE OPEN;

How Stuff Works,

You may wonder how this stuff works, So here is some insight of hot clone. 

When we run create pluggable database command, We do parallel scan of data file and it will read each block and transfer from source database to target database.

 

Now you may have question like what about those on going transaction which are happening in target database which are still not part of datafile. 

The answer is, we ship redos and apply in target database, so all your current transactions will ship their.

But there may be some committed and some uncommitted transaction how to tackle that?

So answer is, undo segment is used for the same to rollback your uncommitted transactions.

I hope, this helps you to perform PDB Hot Clone in these simple five steps.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 1 : Overview

Multitenant Part 1 : Overview 

Multitenant -- the container database architecture.. the first database designed for the Cloud.

Oracle has introduced Oracle Multitenant architecture with the release of Oracle Database 12c R1 on July 2013.  It has been eight year now by the time I am writing this Article. You can find many Articles on Multitenant and I am adding one more in the list of the same. 

I found few reasons to write this article as below,
1) Still many DBAs are not using Multitenant or haven’t got chance to work on it.
2) Many organizations are still on 11g or 12c with single instance database.
3) 20c Onwards, it is mandatory to have container database architecture at the time of installation itself.
4) Enabling DBAs to learn Multitenant architecture.
5) Helping DBAs to simplify their day to day activity and reducing administrative work.

Here, I am writing series of articles on Multitenant, you can also go through the same and see how easy to administrate CDB/PDBs.

Multitenant Part 2 : PDB Hot Clone

Multitenant Part 3 : PDB Refresh

Multitenant Part 4 : PDB Relocation

Multitenant Part 5 : PDB Snapshot Carousel

And finally, I would like to share,

Why I Love Multitenant

Now, let's understand Multitenant architecture and it's core concepts,

Multitenant Architecture

Before Oracle Database 12c, each database is having their own instance and each database requires its own processor, memory and other server resources. Most of the efforts were going to manage those individual instances, in terms of patching, migration, upgrades and performance tuning.

In addition to management issues, these databases often waste server resources because they use only a very small portion of dedicated server. Hence consolidation of these databases into a single database Oracle introduces Multitenant option, which run one or more Pluggable Databases (PDBs) within a single Oracle instance, called a multitenant Container Database (CDB).

Following image shows multitenant architecture.

Just to give brief understanding, This is multitenant architecture; you can found this poster in Oracle Documents.

As you see, we have single instance which consist of common memory area and background process for all PDBs and common set of sys, sysaux tablespace, redo log files, archive log file.

But we have different set of files at PDB level. PDB have their own sys and sysaux tablespace to store PDB level meta data and user tablespaces, local undo and temp files.

In PDB, one users cannot access to other PDBs data, only common user have some credentials to manage PDBs all at the CDB level. Here you can have different set of duties assign at PDB level and CDB level.

Following is the detailed architecture of Multitenant.

Key Benefits of Multitenant 

If you are adopting multitenant architecture, your application will run without change. Also you will get benefits of rapid cloning. You can plug and unplug non-cdb database into container database as PDB.

As Operation perspective, you can manage many database as one. Less operation on DB like patching an upgrade.

Using this approach, you will get benefits of consolidation of your hardware + software i.e database.

Using Multitenant, you are consolidating all your database, my mean to say that database resides on multiple server will now be on one server as pluggable database. This directly benefits you in your capital expenditure.

Also operation wise you are managing many db as one so it will helps you to do upgrade or patching as one activity for all rather doing multiple times. Provisioning of new PDB is also simple

Dev and testing team will get database ready in couple of minutes via cloning or snapshot cloning. Database can be plug and unplug from one CDB to another CDB easily with few steps.

This is really very easy to adopt multitenant in your environment. Just need to understand the core architecture for the same.

Key Benefits for DBAs

There are several features introduce in multitenant gradually from 12c to 19c. Plug & unplug of PDBs, PDB cloning, Hot clones, flashback PDBs, PDB level AW reports, initially in 12c R1 AWR report was only at CDB level. But after that it is possible to take AWR report at PDB level. 

You can manage cpu/io/memory at PDB level with resource manager.  You can have Refreshable PDB, Dataguard at PDB level. And also you can have keystore for each PDBs. PDB lockdown profiles , that helps you to restrict user to run commands such as alter system, alter session, accessing of common objects, AWR reports etc  it is very useful from security perspective and separation of duties among users.

Multitenant Features specific to DBAs

PDB Level Operations

Plug and Unplug 

PDB Hot Clone

Refreshable PDB

Snapshot Carousel

Online PDB Relocation

High Availability with PDBs

Flashback PDB

Dataguard at PDB level

PDBs Level Monitoring and Diagnoses

PDB Level AWR Report

PDB Level Resource Management

Keysotres for PDBs

CDB Fleet Management

Security

PDB Lockdown Profile

Oracle Applications Certified with Multitenant

Here are list of Applications that are certified with Multitenant. Other custom application build different programming languages are also supported for Multitenant. Also Multitenant support Microservice architecture. 

Fusion Applications

Fusion ERP

Fusion HCM

Fusion CRM

NetSuite

Applications Unlimited

Siebel

PeopleSoft

JD Edwards

Oracle Cloud Applications

Taleo Business Edition

RightNow

Oracle Argus Analytics 

Oracle Utilities Mobile Workforce Management 

Oracle FLEXCUBE Direct

Oracle Argus Mart

Supply Chain

Oracle Utilities Customer Care and Billing

Oracle Argus Insight

Oracle Utilities Work and Asset Management

Retail Analytics

Oracle Argus Safety

Planning Non-RPAS 

Oracle Utilities Smart Grid Gateway

Merch Suite

Oracle Utilities Meter Data Management System

Stores Suite

And Many More...

Multitenant Announcement so far…

• Multitenant came up with 12c release. Oracle is more focusing on Cloud and Multitenant deployments and hence oracle has deprecated non-cdb architecture with its 12.1.0.2
• And 19c onwards, Oracle provides you 3 PDBs without multitenant license. This will encourage all of us to use CDB/PDB architecture here.
• And now 20c onwards, non-cdb architecture will be de-supported. So here it is mandatory for all of us to learn more about multitenant and its features and how to use it.

References

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/introduction-to-the-multitenant-architecture.html

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

SANGAM20 : Rapid Fire Session: Database Performance Tuning 

As you all know AIOUG's flagship event SANGAM20 was happened early this December. It was three week event 03Dec to 06-Dec, 10-Dec to 13-Dec and 17-Dec to 20-Dec 2020.

It was really awesome event and many great speakers has presented in this event.

This year, AIOUG brings new concept of Rapid Fire where two or more speakers speaks about technology and participants can shoot questions to the experts.

I am very much grateful to be a part of this event. Vivek Sharma, Karan Dodwal and Chandan Tanwani (me) were speaker for Rapid Fire session of Database Performance Tuning. Our Agenda for this session was as below,

• High-Level Overview of Database/Application Performance and Oracle Optimizer - Vivek Sharma
• SQL Plan Optimization - Karan Dodwal
• SQL Execution Plan Case Studies - Chandan Tanwani

If you were not able to attend this session must watch recording on AIOUG website. Watch here,

https://www.aioug.org/sangam20-sessions?session_id=191

SANGAM20 Interview by Jim Grisanzio

I am very grateful that I was interviewed by Jim Grisanzio. It was nice talking with Jim and I shareD my thought about Rapid Fire session i.e. what attendees can learn and how this will help to achieve maximum performance and it's insight. And we had discussed on AIOUG's Flagship event SANGAM20, about challenges to organize such big event, the limitations and the barriers and volunteer's passion about AIOUG events.

Thank you all for joining us in Sangam20. Looking forward for your great participation again in upcoming events.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

SANGAM20 : A Flagship Event by AIOUG : It's Oracle Learning Festival  

AIOUG: Asia’s Largest Oracle User Group, is back with SANGAM20, a Flagship event of the year 2020.

SANGAM20, This is Oracle Learning Festival. This is 12th year and this time it is for twelve days. Truly inspirational event which is scheduled on 3-6, 10-13, 17-20 December 2020. Must for in-depth technical sessions, Hands-on-labs, Rapid Fire Sessions, Fireside chats, Keynotes, Community sessions and of course, open your mind and expand your network at Sangam20. Only this year it is being brought to you in a new format to adapt to the 'new' normal.

What's New
AIOUG is bringing unique Oracle festival to you in a new and exciting way, using a digital format to bring you an interactive, productive and fun festival experience.
The new format lends itself very nicely to our global theme! So no matter where you are in the world, you can come and be part of Sangam20.
This year AIOUG leveraging the best digital tools to make the Oracle festival fun and immersive experience. And importantly deliver innovative new solutions that will help your business.


Sangam20 Highlights:

• New Content! New Speakers!
• 9 Keynote Sessions
• 12 days, 90+ hours of learning and networking
• 3 Fire chat, 9 Rapid Fire Sessions
• 90+ Sessions, 120+ Speakers World-class speakers
• 6+ Hands-on Lab Sessions
• 7 Tracks ( Database, Applications, Analytics & ML, Future Technology Cloud, Oracle ACE Community Sessions, Interviews, and many more )
• Global Webinar Series covering APAC, EMEA, AMS

How to Register: 

Register for FREE and Secure Your Seat! Registration: 3 simple steps to register

1. Go to https://www.aioug.org/join page and join the AIOUG Starter (FREE) membership.
2. Log in and go to https://www.aioug.org/sangam20#sang_597 page.
3. Check the schedule and register your favourite session(s).

How to Join the Webinars: Login to the AIOUG website and Go to https://www.aioug.org/sangam20#sang_597

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Password Less; Schema Only Accounts 

Schema Only Accounts Overview

Oracle Database 18c introduced new feature called "Schema Only Accounts". It means that there is no password authentication on schemas.

This new functionality allows administrators to secure their databases and protect their applications.

Since schemas have no password, one cannot connect directly to the database (this is the whole purpose of the schema only account).

The only way is, connect through a proxy account.

As one cannot connect directly to the schema only accounts, the schema itself is more Secure. In other words, you don’t want application users to connect directly with the schema account.

Why and When to use?

Generally, schema is created with password and credentials might shared with different stack holders or application users.

To avoid direct usage of schema and password, Proxy user can be created but still one can login to schema if someone knows the password.

So, how to avoid miss use of schema password? 

Solution is, Schema Only Accounts (in other words password less).

Since there is no password for schemas, no direct connection is allowed. And also no password management is required like password expiry or password rotation.

This feature reduces the security risk of attackers using default/regular passwords to hack into these accounts.

Configure Password less Schema Only Accounts

Following is example show that mobileapp schema not required and authentication to login. One cannot directly login to this schema as there is no password but User cbtadmin has given grants to access objects from mobileapp schema using proxy authentication. 

Create Schema without password.

Syntax

CREATE USER <schema_name> NO AUTHENTICATION QUOTA UNLIMITED ON users;

  

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO <schema_name>;

Example

CREATE USER mobileapp NO AUTHENTICATION QUOTA UNLIMITED ON users;

  

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO mobileapp;

Allow application user to connect to Schema

Syntax

ALTER USER schema_owner GRANT CONNECT THROUGH <application user>;

Example

ALTER USER mobileapp GRANT CONNECT THROUGH cbtadmin;

How application user can connect to schema

Syntax

sql> conn <application user>[schema name]/password@pdb1

Example

sql> conn cbtadmin[mobileapp]/cbtadmin@pdb1

Resources

1) https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-22328DCF-B137-499B-ACC9-49D6C72B6A57

2) https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/release-changes.html#GUID-256DEEBF-8FBE-4641-BAE3-D23D53ADFB44

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Oracle Groundbreakers Yatra 2020 - Online Webinar Series

Oracle Groundbreakers Yatra 2020 - Online Webinar Series

AIOUG is largest Oracle users group. In Oracle Groundbreakers Yatra 2020 (OGYatra) webinar series, they are covering 125+ hours of learning sessions in 14 days, 100+ speakers, including Oracle Gurus, Oracle ACE and Java Champions.

It's my pleasure that I am also part of this #OGYatra webinar series and get a chance to speak and share my knowledge to large audience across the world. Many Thanks to Sai Penumuru and AIOUG team for great support.

Join me on 16th July at 11:30am and let's discuss on Oracle Multi-Tenant Architecture and it's Feature with Live Demo.

Specially, I will try to cover following features in Live Demo,
'- Monitoring of CDB/PDBs
'- Hot Clone PDB Databases
'- Plug-Unplug PDBs from one CDB to another and non-CDB to CDB
'- Refreshable PDBs 
'- PDB Snapshot carousel

Looking forward for your participation.
And Thank you in advance for joining me. See you all soon.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error

ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error

I was getting this TNS error while creating Listner with "netca", here is the screenshot for the same.

D:\app\db193\bin>netca

Oracle Net Services Configuration:
Configuring Listener:LISTENERCBT
ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error
  caused by: oracle.net.config.ConfigException: TNS-04414: File error
  caused by: TNS-04605: Invalid syntax error: Unexpected char or LITERAL "IEPDB1" before or at  IEPDB1 = (
  Error in file D:\TNS_ADMIN\tnsnames.ora

This is my windows machine and creating new listner for my newly created CDB. DB version is 19.3

As above error shows that I have some issue with my tnsnames.ora with some of my TNS Entry with IEPDB1.

In my environment TNS_ADMIN environment variable is set and it is pointing to my D:\TNS_ADMIN\tnsnames.ora

Here I found some junk characters near IEPDB1 TNS entry, I removed it and save the file and ran netca again...
Now, all went good...It solved my problem .

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

 

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

Recently I have installed Oracle 19c Database binary to windows machine. And after that I started DBCA to create database.

In "Configuration options" steps I got memory error. i.e. [DBT-50000] Unable to check for available memory
Earlier I selected 6GB for my SGA but after this error I had selected 1GB only. But still was facing this issue.

Here is the screenshot for the same.

After searching oracle docs for known issue, I found, it's a bug. One can find details about this bug on following oracle notes.

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory in "Specify configuration option" (Doc ID 2631718.1)

Workaround for this issue is,

Run dbca with "-J-Doracle.assistants.dbca.validate.ConfigurationParams=false" like a bellow command,

$> dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

This will solve your issue and DBCA will run smooth, no issues thn.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

 

 

ORA-65259: partial synchronization of application is disallowed

ORA-65259: partial synchronization of application is disallowed

You have started application Sync but you find it is taking more time to complete this action. By mistake or purposely you did ctrl+c or stopped the application sync operation. Later point of time if you again start application syncing command, now you might end up hitting bug.

Check below listing,

alter pluggable database application MYAPP sync
*
ERROR at line 1:
ORA-65259: partial synchronization of application is disallowed

This issue occurs in two reasons,
1) If any other operation also going on in parallel with sync.
2) If previously Sync operation was killed or Stopped.

In both the cases, it makes PDBs in inconsistent state. Here suggestion is, always try to avoid cancellation on SYNC operation on application PDBs, no matter if it is taking long time.

Here are two workaround for this issue.

Workaround 1.
Work around for this issue is to apply for bug 24430427.
Patch can be found on https://support.oracle.com

Workaround 2.
Step 1. Create new PDB under application root.
Step 2. Synchronize PDBs with the application root
Step 3:- Upgrade application

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert