Oracle 12R2 – Local Undo Switch

From 12.2 onwards,  each container in an instance can use its own undo table space and this is called local undo mode. In previous release, all containers in an instance shared the same undo table space in multitenant set up.

Today I upgraded, container database from 12.1 -> 12.2 via DBUA and looked at the UNDO setting. Found that we have to manually switch the undo modes for upgraded instances. So here are the steps. Continue reading → Oracle 12R2 – Local Undo Switch

Oracle 18c – Scalable Sequences

From Oracle 18c, Sequences can be made scalable by using SCALE clause in CREATE/ ALTER SEQUENCE statement. Scalable sequences optimize the sequence generation by using a unique combination of instance number and session number to reduce the impact of Index leaf block contention during massive loads. This is  one of the few features that is not automatically enabled as it requires some intervention by DBA’s to ensure this does not change their implemented business logic.

Here is the syntax for defining a scalable sequence.

Create /Alter  sequence sequence_name …… scale [extend | noextend] | noscale

You can check DBA_SEQUENCES/USER_SEQUENCE/ALL_SEQUENCE dictionaries to know whether sequence is scalable or not. By default sequences are not scalable as you can see in dictionary both columns scale_flag/extend_flag is set to N.


Now create the sequence with scale clause. When the scale clause is specified, a 6 digit numeric scalable number is prefixed to the digit of the sequence. Out of 6 digits, three digits  are instance numbers followed by a three digit session number. These are generated by: Instance number is [(instance id % 100) + 100] Session number is [session id % 1000]

Continue reading → Oracle 18c – Scalable Sequences

Oracle 18c – Cancel a SQL Statement in a Session

From Oracle 18c, you can terminate a SQL query consuming excessive resources in a session like runaway sql queries. This is an alternative of killing the whole session. When you cancel a DML statement, the statement is rolled back.

Here is the syntax of cancelling a SQL statement.


  • If @INST_ID is not specified , the instance id of the current session is used.
  • If SQL_ID is not specified, the currently running SQL statement in the specified session is canceled.

Continue reading → Oracle 18c – Cancel a SQL Statement in a Session

Oracle 18c – Preplugin Backups including restore and recovery test

Preplugin backups are backups taken of a non-CDB or a PDB before they are plugged into a target CDB. You can relocate the PDB by unplugging it from a source CDB or NON-CDB and plugging it into target CDB. RMAN can perform PDB restore and recovery operations on the target CDB by using preplugin backups. From 18c, RMAN now supports use of the PREPLUGIN for commands like CATALOG, CROSSCHECK, LIST, CHANGE etc. Here is a complete explanantion of preplugin PDB backup on source CDB and restore/recovery on target CDB after plugging in.

Source: CDB18 and PDB18 ( PDB Backup and unplug from source CDB)

Destination: CDB_dest and PDB18 ( plug-in to dest CDB and restore/recover test)

Creating Preplugin Backups of PDBs Using RMAN On source CDB:

Connect to the PDB as a local or common user with SYSDBA/SYSBACKUP privilege. Continue reading → Oracle 18c – Preplugin Backups including restore and recovery test

Oracle 18c – Duplicate a PDB to an existing CDB

Starting with Oracle Database Release 18c, RMAN enables you to use the DUPLICATE command to duplicate a PDB to an existing CDB. This is very useful feature which will allow the CDBs in your environment to duplicate PDBs, instead of having to create additional CDBs for deduplication tasks.

There are some restrictions on PDB duplication to an existing CDB:

  • Only active database duplication is supported.
  • You can’t duplicate PDB to a standby CDB.
  • One PDB can duplicated at a time.
  • You can’t skip exclude specific tablespace during PDB duplications. Means partial duplication is not allowed.
  • PDB with TDE- Encrypted tablespaces is not supported.
  • Only few clauses of the duplicate commands are supported like DB_FILE_NAME_CONVERT, SECTION SIZE etc… some clauses like FARSYNC, LOG_FILE_NAME_CONVERT is not supported. Check oracle documentation for more detail.


My environment is set up as below and It’s all ready to test the scenario. Check my other blogs, how to install and create 18c database from here.

  18c Env.
CDB Name PDB Name
Source CDB18 PDB18
Destination CDB_dest PDB_dest

The example assumes the following scenario:

  • Both Source CDB, CDB18 and destination CDB, CDB_dest are on same VM machine and both CDB’s are 18c compatible.
  • The PDB being duplicate PDB18,  is in read-write mode.
  •  Both source and destination CDB’s are open in read write mode with archiving enabled.

Continue reading → Oracle 18c – Duplicate a PDB to an existing CDB

Oracle 18c – Run database without ORA-12754

In my last post, I installed the Oracle 18c binaries on Oracle Linux 7. Now next step is to create the database. But you’ll be stuck with error “ORA-12754” when you trying to create database with exadata binaries.


The library which is causing the problem is “libserver18”. It’s really hard to troubleshoot this library. So I thought to copy this library from Oracle cloud environment. I deployed the 18c database from my free trail cloud account. You need to wait at least 10-15 mins before the database service is ready for you.


Once database is ready, make an SSH connection which is explained here.

Now I replaced the library from my OPC environment to VM machine and relink the libraries again. Without relink, you’ll receive the same error. Continue reading → Oracle 18c – Run database without ORA-12754