Flashback PDB in Oracle Database 12c Release 2

Oracle database 12c Release 2 provides flashback capability at the individual PDB level. Lets discuss the new feature. we can learn about Flashback database in general from below links Flashback Database explained and limitation For the oracle database to be flashback, archive log mode and flashback mode should be on How to put database in archive log mode sqlplus “/ as sysdba” SHUTDOWN IMMEDIATE STARTUP … Continue reading Flashback PDB in Oracle Database 12c Release 2 »

PDB Relocate in Oracle database 12c Release 2

Last updated on December 17th, 2017 at 05:25 pmA pluggable database is a self-contained, fully-functional Oracle database. We often need to move the pluggable database from one CDB to another CDB. This will be a unplug/plug operation where outage will depend on the location of source and destination CDB 1) When moving between servers with shared storage, such as is typically the case in Cloud … Continue reading PDB Relocate in Oracle database 12c Release 2 »

REGEXP string Manipulation

String manipulation and searching contribute to a large percentage of the logic within a Webbased application. Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. Usage ranges from the simple (“Find the word SAN in the text”) to the more complex (“Extract all URLs from the text”) to the archaic (“Find all words in which every second character … Continue reading REGEXP string Manipulation »

How to use Optimizer hints

What is Optimizer? When a query is submitted in Oracle server. optimizer attempts to generate the best execution plan for a SQL statement. The best execution plan is defined as the plan with the lowest cost among all considered candidate plans. The cost computation accounts for factors of query execution such as I/O, CPU, and communication. The best method of execution depends on myriad conditions … Continue reading How to use Optimizer hints »

Refreshable PDB in Oracle Database 12.2

Refreshable PDB is a new features introduced in 12.2 . It builds upon hot clone capability in Oracle Database 12.2. It basically means we can refresh the remote PDB from source PDB on regular intervals by just applying the incremental redo This feature eliminates the need to re-create a cloned environment from scratch and also ensures that a cloned PDB can be periodically synchronized with … Continue reading Refreshable PDB in Oracle Database 12.2 »

ASM best practice to add disk

Here are ASM best practice to add disk which should be always use to manipulate the ASM disks 0) First check the newly added disk is visible in ASM  disksselect header_status,path from v$asm_disk where path= ”; It should show candidate .1) Create a new temporary diskgroup: CREATE DISKGROUP TEST_DISK EXTERNAL REDUNDANCY DISK ”; 2) Check if the diskgroup is created and mounted: SELECT STATE, NAME FROM … Continue reading ASM best practice to add disk »

How to use Patch wizard in Oracle EBS environment

How to use Patch wizard  You can submit a specific patch analysis request through the Patch wizard to determine the impact of a patch on your system. Patch Wizard Impact Analysis provides reports on: • The total number of files in the patch • The number of files the patch will install • The products that will have updated files • The files that will … Continue reading How to use Patch wizard in Oracle EBS environment »

How to remotely clone a PDB or non-CDB in Oracle Database 12.1

Many times, we may need to copy the remote PDB to Local PDB for testing purpose. The case would be copying Production PDB to Development PDB box. Some times , we may want to migrate the non-CDB to PDB to make sure Multitenant features in the Oracle database Restriction for doing this 1)The default tablespaces for each common user in the remote PDB must exist … Continue reading How to remotely clone a PDB or non-CDB in Oracle Database 12.1 »

Hot PDB cloning in Oracle database 12.2

In Oracle database release 12.1, in order to clone a PDB, The source PDB must be quiescent(read only mode) for the duration of the clone operation. So it was called a “cold clone” operation because it involved an outage (from a transactional perspective) in the source database. This applies for both the same CDB clone or Remote CDB clone. Related Article Oracle database 12c:How to … Continue reading Hot PDB cloning in Oracle database 12.2 »

Local Undo in Oracle Database 12c R2(12.2)

Last updated on December 17th, 2017 at 05:20 pmProblem statement We have global or shared undo for the entire CDB in 12c R1. With shared undo, before performing operations such as (cold) clone or unplug, it is necessary for the database to check for any uncommitted transactions in the source PDB. This is to avoid problems with transactional consistency in the PDB after the clone … Continue reading Local Undo in Oracle Database 12c R2(12.2) »

Find indexes status and assigned columns for a table

It is very common that you want to know / see the available index on a table. And also you sometime want to know the columns which are assigned to the indexes. Following views would be helpful in these cases dba_ind_columns all_ind_columns   Query to Find the indexes with assigned column is  set pagesize 50000 verify off echo off col table_name head ‘Table Name’ format … Continue reading Find indexes status and assigned columns for a table »

Oracle Database Interactive Quick Reference Guide | 11g & 12c

The Oracle Database Interactive Quick Reference is a multimedia tool for various terms and concepts used in the Oracle Database . Built as a multimedia web page, this diagram provides descriptions of database architectural components, as well as references to relevant documentation. This is very helpful reference and cheat sheet for writing custom data dictionary scripts, locating views pertinent to a specific database component or … Continue reading Oracle Database Interactive Quick Reference Guide | 11g & 12c »