Page 2 of 3

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.

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL, [@INST_ID, SQL_ID]’;

  • 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.

Method:

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.

dbca

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.

18c_cloud

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

Oracle 18c – Install binaries on Oracle Linux 7

In this blog, I’ll go through oracle 18c installation on oracle Linux 7.4. Oracle 18c is not officially announced for on-premises database. But for study purpose, I thought to use Oracle 18c binaries from oracle edelivery which is available for Oracle Exadata. Other option is to try 18c on Oracle SQL live which I explained here.

My environment is all ready to go. I am using Oracle virtual box 5.2.2 with Oracle Linux 7.4, 4GB memory and extra disk for oracle binaries and data files.

Oracle recommends that when you install Oracle Linux 6 or Oracle Linux 7 and use Oracle Preinstallation RPM to configure your operating systems for Oracle Database and Oracle Grid Infrastructure installations. The Oracle Preinstallation RPM will automatically creates standard (not role-allocated) Oracle installation owner and groups, and sets up other kernel configuration settings as required for Oracle installations. 18c preinstall rpm is not available yet so used 12R2 as its compatible with 18c. Continue reading → Oracle 18c – Install binaries on Oracle Linux 7

Oracle 18c – Private Temporary Tables

Oracle 18c introduced, Private temporary tables concept which are temporary database objects that are dropped at the end of a transaction or session. Private temporary tables are stored in memory and each one is visible only to the session that created it.

The “CREATE PRIVATE TEMPORARY TABLE” statement to create a private temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific.

Here is my explanation on Oracle Live SQL.

This statement creates a private temporary table that is transaction specific: Continue reading → Oracle 18c – Private Temporary Tables