Oracle Multitenant (PDB Level) Initialization Parameters

Oracle Multitenant (PDB Level) Initialization Parameters 

I was asked what are those parameters that can be modifiable at PDB level. And my simple answer is to check v$parameters view and look for ISPDB_MODIFIABLE column have TRUE value than it is modifiable otherwise not.

I have just extract the same here, some of you might not have DBA privileges, specially developers don't have rights to view v$ views.

SrNo.	Init Parameters	Description	Default Value
1	_optimizer_gather_stats_on_conventional_dml	optimizer online stats gathering for conventional DML	TRUE
2	_optimizer_use_stats_on_conventional_dml	use optimizer statistics gathered for conventional DML	TRUE
3	_serial_direct_read	enable direct read in serial	auto
4	adg_account_info_tracking	ADG user account info tracked in standby(LOCAL) or in Primary(GLOBAL)	LOCAL
5	allow_rowid_column_type	Allow creation of rowid column	FALSE
6	approx_for_aggregation	Replace exact aggregation with approximate aggregation	FALSE
7	approx_for_count_distinct	Replace count distinct with approx_count_distinct	FALSE
8	approx_for_percentile	Replace percentile_* with approx_percentile	none
9	aq_tm_processes	number of AQ Time Managers to start	1
10	asm_diskstring	disk set locations for discovery
11	awr_pdb_autoflush_enabled	Enable/Disable AWR automatic PDB flushing	FALSE
12	bitmap_merge_area_size	maximum memory allow for BITMAP MERGE	1048576
13	blank_trimming	blank trimming semantics parameter	FALSE
14	cell_offload_compaction	Cell packet compaction strategy	ADAPTIVE
15	cell_offload_decryption	enable SQL processing offload of encrypted data to cells	TRUE
16	cell_offload_parameters	Additional cell offload parameters	NONE
17	cell_offload_plan_display	Cell offload explain plan display	AUTO
18	cell_offload_processing	enable SQL processing offload to cells	TRUE
19	cell_offloadgroup_name	Set the offload group name
20	client_result_cache_lag	client result cache maximum lag in milliseconds	3000
21	client_result_cache_size	client result cache max size in bytes	0
22	client_statistics_level	Client Statistics Level	TYPICAL
23	commit_logging	transaction commit log write behaviour
24	commit_point_strength	Bias this node has toward not preparing in a two-phase commit	1
25	commit_wait	transaction commit log wait behaviour
26	commit_write	transaction commit log write behaviour
27	common_user_prefix	Enforce restriction on a prefix of a Common User/Role/Profile name	NONE
28	container_data	which containers should data be returned from?	ALL
29	containers_parallel_degree	Parallel degree for a CONTAINERS() query	65535
30	cpu_count	maximum number of CPUs	0
31	cpu_min_count	minimum number of CPUs required
32	create_bitmap_area_size	size of create bitmap buffer for bitmap index	8388608
33	create_stored_outlines	create stored outlines for DML statements
34	cursor_bind_capture_destination	Allowed destination for captured bind variables	memory+disk
35	cursor_invalidation	default for DDL cursor invalidation semantics	IMMEDIATE
36	cursor_sharing	cursor sharing mode	EXACT
37	db_block_checking	header checking and data and index block checking	FALSE
38	db_cache_size	Size of DEFAULT buffer pool for standard block size buffers	0
39	db_create_file_dest	default database location	NONE
40	db_create_online_log_dest_1	online log/controlfile destination #1	NONE
41	db_create_online_log_dest_2	online log/controlfile destination #2	NONE
42	db_create_online_log_dest_3	online log/controlfile destination #3	NONE
43	db_create_online_log_dest_4	online log/controlfile destination #4	NONE
44	db_create_online_log_dest_5	online log/controlfile destination #5	NONE
45	db_domain	directory part of global database name stored with CREATE DATABASE
46	db_file_multiblock_read_count	db block to be read each IO	0
47	db_files	max allowable # db files	200
48	db_index_compression_inheritance	options for table or tablespace level compression inheritance	NONE
49	db_performance_profile	Database performance category
50	db_securefile	permit securefile storage during lob creation	PERMITTED
51	db_unrecoverable_scn_tracking	Track nologging SCN in controlfile	TRUE
52	ddl_lock_timeout	timeout to restrict the time that ddls wait for dml lock	0
53	default_sharing	Default sharing clause	metadata
54	deferred_segment_creation	defer segment creation to first insert	TRUE
55	dst_upgrade_insert_conv	Enables/Disables internal conversions during DST upgrade	TRUE
56	enable_automatic_maintenance_pdb	Enable/Disable Automated Maintenance for Non-Root PDB	TRUE
57	enable_ddl_logging	enable ddl logging	FALSE
58	encrypt_new_tablespaces	whether to encrypt newly created tablespaces	CLOUD_ONLY
59	fixed_date	fixed SYSDATE value	NONE
60	forward_listener	forward listener
61	global_names	enforce that database links have same name as remote database	FALSE
62	heat_map	ILM Heatmap Tracking	OFF
63	ignore_session_set_param_errors	Ignore errors during alter session param set
64	inmemory_automatic_level	Enable Automatic In-Memory management	OFF
65	inmemory_clause_default	Default in-memory clause for new tables	NONE
66	inmemory_expressions_usage	Controls which In-Memory Expressions are populated in-memory	ENABLE
67	inmemory_force	Force tables to be in-memory or not	DEFAULT
68	inmemory_optimized_arithmetic	Controls whether or not DSBs are stored in-memory	DISABLE
69	inmemory_prefer_xmem_memcompress	Prefer to store tables with given memcompress levels in xmem	NONE
70	inmemory_prefer_xmem_priority	Prefer to store tables with given priority levels in xmem	NONE
71	inmemory_query	Specifies whether in-memory queries are allowed	ENABLE
72	inmemory_size	size in bytes of in-memory area	0
73	inmemory_virtual_columns	Controls which user-defined virtual columns are stored in-memory	MANUAL
74	inmemory_xmem_size	size in bytes of in-memory xmem area	0
75	java_jit_enabled	Java VM JIT enabled	TRUE
76	job_queue_processes	maximum number of job queue slave processes	4000
77	ldap_directory_access	RDBMS's LDAP access option	NONE
78	ldap_directory_sysauth	OID usage parameter	no
79	listener_networks	listener registration networks
80	lob_signature_enable	enable lob signature	FALSE
81	local_listener	local listener
82	log_archive_min_succeed_dest	minimum number of archive destinations that must succeed	1
83	long_module_action	Use longer module and action	TRUE
84	max_datapump_jobs_per_pdb	maximum number of concurrent Data Pump Jobs per PDB	100
85	max_datapump_parallel_per_job	maximum number of parallel processes per Data Pump Job	50
86	max_dump_file_size	Maximum size (in bytes) of dump file	unlimited
87	max_idle_blocker_time	maximum idle time for a blocking session in minutes	0
88	max_idle_time	maximum session idle time in minutes	0
89	max_iops	MAX IO per second	0
90	max_mbps	MAX MB per second	0
91	max_pdbs	max number of pdbs allowed in CDB or Application ROOT	4098
92	max_string_size	controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL	STANDARD
93	multishard_query_data_consistency	consistency setting for multishard queries	strong
94	multishard_query_partial_results	enable partial results for multishard queries	not allowed
95	nls_calendar	NLS calendar system name	NONE
96	nls_comp	NLS comparison	BINARY
97	nls_currency	NLS local currency symbol	NONE
98	nls_date_format	NLS Oracle date format	NONE
99	nls_date_language	NLS date language name	NONE
100	nls_dual_currency	Dual currency symbol	NONE
101	nls_iso_currency	NLS ISO currency territory name	NONE
102	nls_language	NLS language name	AMERICAN
103	nls_length_semantics	create columns using byte or char semantics by default	BYTE
104	nls_nchar_conv_excp	NLS raise an exception instead of allowing implicit conversion	FALSE
105	nls_numeric_characters	NLS numeric characters	NONE
106	nls_sort	NLS linguistic definition name	NONE
107	nls_territory	NLS territory name	AMERICA
108	nls_time_format	time format	NONE
109	nls_time_tz_format	time with timezone format	NONE
110	nls_timestamp_format	time stamp format	NONE
111	nls_timestamp_tz_format	timestamp with timezone format	NONE
112	object_cache_max_size_percent	percentage of maximum size over optimal of the user session's object cache	10
113	object_cache_optimal_size	optimal size of the user session's object cache in bytes	10240000
114	olap_page_pool_size	size of the olap page pool in bytes	0
115	open_cursors	max # cursors per session	50
116	open_links	max # open links per session	4
117	optimizer_adaptive_plans	controls all types of adaptive plans	TRUE
118	optimizer_adaptive_reporting_only	use reporting-only mode for adaptive optimizations	FALSE
119	optimizer_adaptive_statistics	controls all types of adaptive statistics	FALSE
120	optimizer_capture_sql_plan_baselines	automatic capture of SQL plan baselines for repeatable statements	FALSE
121	optimizer_dynamic_sampling	optimizer dynamic sampling	2
122	optimizer_features_enable	optimizer plan compatibility parameter	19.1.0
123	optimizer_ignore_hints	enables the embedded hints to be ignored	FALSE
124	optimizer_ignore_parallel_hints	enables embedded parallel hints to be ignored	FALSE
125	optimizer_index_caching	optimizer percent index caching	0
126	optimizer_index_cost_adj	optimizer index cost adjustment	100
127	optimizer_inmemory_aware	optimizer in-memory columnar awareness	TRUE
128	optimizer_mode	optimizer mode	all_rows
129	optimizer_secure_view_merging	optimizer secure view merging and predicate pushdown/movearound	TRUE
130	optimizer_use_invisible_indexes	Usage of invisible indexes (TRUE/FALSE)	FALSE
131	optimizer_use_pending_statistics	Control whether to use optimizer pending statistics	FALSE
132	optimizer_use_sql_plan_baselines	use of SQL plan baselines for captured sql statements	TRUE
133	parallel_degree_limit	limit placed on degree of parallelism	CPU
134	parallel_degree_policy	policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)	MANUAL
135	parallel_force_local	force single instance execution	FALSE
136	parallel_instance_group	instance group to use for all parallel operations	NONE
137	parallel_max_servers	maximum parallel query servers per instance
138	parallel_min_degree	controls the minimum DOP computed by Auto DOP	1
139	parallel_min_time_threshold	threshold above which a plan is a candidate for parallelization (in seconds)	AUTO
140	parallel_servers_target	instance target in terms of number of parallel servers	0
141	pdb_file_name_convert	PDB file name convert patterns and strings for create cdb/pdb
142	pdb_lockdown	pluggable database lockdown profile
143	pdb_os_credential	pluggable database OS credential to bind
144	pdb_template	PDB template
145	pga_aggregate_limit	limit of aggregate PGA memory for the instance or PDB	0
146	pga_aggregate_target	Target size for the aggregate PGA memory consumed by the instance	0
147	plscope_settings	plscope_settings controls the compile time collection, cross reference, and stor age of PL/SQL source code identifier and SQL statement data	IDENTIFIERS:NONE
148	plsql_ccflags	PL/SQL ccflags	NONE
149	plsql_code_type	PL/SQL code-type	INTERPRETED
150	plsql_debug	PL/SQL debug	FALSE
151	plsql_optimize_level	PL/SQL optimize level	2
152	plsql_v2_compatibility	PL/SQL version 2.x compatibility flag	FALSE
153	plsql_warnings	PL/SQL compiler warnings settings	NONE
154	private_temp_table_prefix	Private temporary table prefix	ORA$PTT_
155	query_rewrite_enabled	allow rewrite of queries using materialized views if enabled	TRUE
156	query_rewrite_integrity	perform rewrite using materialized views with desired integrity	enforced
157	recyclebin	recyclebin processing	on
158	remote_dependencies_mode	remote-procedure-call dependencies mode parameter	timestamp
159	remote_listener	remote listener
160	remote_recovery_file_dest	default remote database recovery file location for refresh/relocate	NONE
161	resource_limit	master switch for resource limit	TRUE
162	resource_manager_plan	resource mgr top plan
163	result_cache_max_result	maximum result size as percent of cache size	5
164	result_cache_max_size	maximum amount of memory to be used by the cache	1
165	result_cache_mode	result cache operator usage mode	MANUAL
166	result_cache_remote_expiration	maximum life time (min) for any result using a remote object	0
167	resumable_timeout	set resumable_timeout	0
168	rollback_segments	undo segment list
169	scheduler_follow_pdbtz	Make scheduler objects follow PDB TZ	FALSE
170	session_cached_cursors	Number of cursors to cache in a session.	50
171	sessions	user and system sessions
172	sga_min_size	Minimum, guaranteed size of PDB's SGA	0
173	sga_target	Target size of SGA	0
174	shadow_core_dump	Core Size for Shadow Processes	partial
175	shared_pool_size	size in bytes of shared pool	134217728
176	shared_servers	number of shared servers to start up	4294967294
177	shrd_dupl_table_refresh_rate	duplicated table refresh rate (in seconds)	60
178	skip_unusable_indexes	skip unusable indexes if set to TRUE	TRUE
179	smtp_out_server	utl_smtp server and port configuration parameter
180	sort_area_retained_size	size of in-memory sort work area retained between fetch calls	0
181	sort_area_size	size of in-memory sort work area	65536
182	spatial_vector_acceleration	enable spatial vector acceleration	FALSE
183	sql_trace	enable SQL trace	FALSE
184	sql92_security	require select privilege for searched update/delete	TRUE
185	sqltune_category	Category qualifier for applying hintsets	DEFAULT
186	star_transformation_enabled	enable the use of star transformation	FALSE
187	statistics_level	statistics level	TYPICAL
188	tde_configuration	Per-PDB configuration for Transparent Data Encryption	NONE
189	temp_undo_enabled	is temporary undo enabled	FALSE
190	timed_os_statistics	internal os statistic gathering interval in seconds	0
191	timed_statistics	maintain internal timing statistics	FALSE
192	undo_management	instance runs in SMU mode if TRUE, else in RBU mode	AUTO
193	undo_retention	undo retention in seconds	900
194	undo_tablespace	use/switch undo tablespace	NONE
195	unified_audit_systemlog	Syslog facility and level for Unified Audit
196	workarea_size_policy	policy used to size SQL working areas (MANUAL/AUTO)	AUTO
197	xml_db_events	are XML DB events enabled	enable

I hope, above will help you to understand what you can change at PDB level.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Why I Love Oracle Multitenant Architecture

Why I Love Oracle Multitenant Architecture

I have written multiple article on Multitenant. I hope you have gone through all these. If not, you can refer the same as below,

Multitenant Part 1 : Multitenant Overview

Multitenant Part 2 : PDB Hot Clone

Multitenant Part 3 : PDB Refresh

Multitenant Part 4 : PDB Relocation

Multitenant Part 5 : PDB Snapshot Carousel

This is my last article in this series. I would like to share my experience about Multitenant and Why I love Oracle Multitenant.

Ask a group of DBAs what's their biggest pain point in their DBA world, and eight out of ten, will say, Creating new DBs for Developer/Tester, Maintenance of Existing databases and Monitoring and Troubleshooting of all the databases (including patching, upgrading etc).

With Oracle Multitenant Architecture, One Container Database (CDB) can have multiple Pluggable Databases (PDBs). This makes DBAs life easier by provisioning new Pluggable databases with single command, Maintenance of Single DB server and One Database Instance of CDB, Monitoring and troubleshooting of database at ease. All databases are up to date with latest patches.

There are more development requirements, Different application type require specialized databases. For example, Spatial applications need spatial databases, Graph applications require Graph DB, some of Payments and fraud protection applications require Blockchain databases, to read/store different documents require specialized databases to store documents and so on.

Oracle Database natively supports all modern data types into one product. Oracle supports Spatial data for location awareness, JSON for document stores, IoT for device integration, in-memory technologies for real-time analytics, Blockchain native tables.

Hence, we called Oracle Database as Converged database.

Today's modern application development runs on Microservice architecture. Each microservice should have its own database that contains data to that relevant  microservice specifically. This helps you to deploy individual services independently. Also Individual teams can have their own databases for the corresponding microservice.

Oracle Multitenant is right fit into the Microservice development environment. Each microservice will have it's own pluggable database. Also in the Development scenario individual teams can have their own database (PDB).

"Oracle Multitenant enables an Oracle Database to function as a container database (CDB). A CDB consolidates multiple pluggable databases (PDB), a portable collection of schemas, schema objects, and non-schema objects. Whether deployed on-premises or in the cloud, with Oracle Multitenant, applications run unchanged in self-contained PDBs, improving resource utilization, management, and overall security." - Oracle Docs

With Multitenant, one can have many benefits, I am just highlighting few here,

• Efficient Consolidation
• Rapid Provisioning and Cloning
• Faster Upgrades and Patching
• Manage Many Databases As One
• Plug Into The Cloud

Why I love Multitenant?

There are many reasons why I love Multitenant, But here I am sharing Three Top Most Reasons and  my experience with Multitenant and gradually you will come to know about the same.

Reason 1 : Manage Many Database As One

Multitenant enables me to manage single database server to run multiple databases. I had created one CDB and I have multiple pluggable database inside the same. 

• One server to Manage
• One Database to Manage
• One Database Resource Manager to Manage PDBs
• One-time Patching
• One-time Upgradation

Reason 2 : Simple Administration

Multitenant helped me to manage multiple database with very ease. Very simple administration.

With Multitenant,

Earlier Provisioning/Creating of Database was taking 1.5 hours & Now, I am able to finish this in 10mins.

Earlier Database Cloning was taking more than 2 hours, & Now, I am able to finish this task in 30mins.

Earlier Patching multiple single instance database was taking around 12+ hours with Multiple down time windows for different databases & Now, in single downtime window of 2 hours I am able to complete patching my database.

Earlier database refresh was taking almost 1+ hours of time & Now, I have automated PDB refresh and developers are getting Refreshed database at any point of time.

With Multitenant Database Administration, Now, I am able to save lot of my time. I hope you also save your time by adopting Multitenant Architecture.

Reason 3 : Ease of Maintaining Dev/Test Environment.

Our Development team introduce bug fixes and application patches every quarter. Hence, every new development and bug fix environment is required to maintain at certain period of time, say for example one bug fix environment need to maintain almost 18 months and every quarter new application & database environment is needed to maintain Application/Database Fixes and new development.

Every Quarter, creating new database with latest version of database and maintaining old database is tedious task for DBAs.

Multitenant has helped me a lot in this development scenarios and reduces my lot of work and saved my time as well.

Earlier Provisioning/Creating of Database was taking 1.5 hours & Now I am able to finish this in 10mins.

Also Rather maintaining 15+ Database servers, Now I am only maintaining 5 database servers.

Now you can imagine how much work has been reduced by Multitenant Architecture.

Did You Know?

• No Application changes required to adopt Multitenant Architecture. Application remains Unchanged. Nothing to worry for Developers.
• You will have TNS entry for each PDBs. You can define PDB listener with different port.
• AutoUpgrade Automates Upgrade and Conversion of Non-CDB to PDB
• To simplify upgrades to Oracle Database 21c AutoUpgrade automates steps to convert a database from a non-CDB to a PDB as part of a database upgrade.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 5 : PDB snapshot Carousel

 Multitenant Part 5 : PDB Snapshot Carousel

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 4 : PDB Relocation

Multitenant Part 4 : PDB Relocation

Online PDB relocation,

PDB Relocation is the fastest way to move a PDB with minimal or nearer to no down time. Otherwise, plugging and unplugging PDBs require outage means downtime is required.

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.

On target database server run following command,

CREATE PLUGGABLE DATABASE PROPDB1 FROM PROPDB1@prod_link RELOCATE;

When you run this command, it executes a hot clone of source PDB from its original location to its target location. The source PDB copies data files, undo blocks, and redo blocks to the target PDB upto the SCN marked for it.

When this step completes, two transactional consistent copies of this PDB exist: one in the source CDB and one in the target CDB . during this operation, transaction continues uninterrupted on the source PDB. Users or applications connected to the source PDB are unaware that a relocation is happening behind the scene.

All existing application connections, and new connections created during this step, continue to connect to the source PDB.

When you issues ALTER PLUGGABLE DATABASE OPEN command

• The target PDB implicitly sets the end SCN marker, and applies any redo or undo required to complete media recovery to satisfy the implicit end SCN marker.
• When media recovery occurs on the target PDB, Oracle Database initiates active session draining on the source PDB.
• PDB services are registered with the listener and are available on the target CDB.
• The source PDB is closed.
• The target PDB opens in read/write mode.

Relocation in Different Listener Networks

AVAILABILITY MAX

Here, at additional step, you need to reconfigure the original listener.

If a local listener redirects to a Single Client Access Name (SCAN) listener in an Oracle RAC configuration, thn you need to add parameter in each node listener file.

And parameter name is,

ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN1=YES

I hope, this helps you to perform PDB relocation in very simple steps.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

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