I was managing a database that had a temporary tablespace which was having problems to hold the results of a sort. Then I solved this problem by creating a temporary tablespace group. And according to Oracle Corporation, “… enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.”. So how can I do that? If you are managing a multitenant database you must have in mind what problem is occurring in one of your PDB or the CDB. Let’s see if we are going to create…
Category: Oracle Database General
Warning: PDB altered with errors. How PDB_PLUG_IN_VIOLATIONS view can help you?
I was cloning a PDB from another PDB when I faced this error: Then I was wondering what shall I do now once the command is “show errors” but replied “No errors.”, and it didn’t give any clear message that was different from “Warning: PDB altered with errors.”. I came to a realization after doing some research and I found that the view pdb_plug_in_violations and found out the reason of the warning and it was because my database has Transparent Data Encryption configured and was missing the keys from the…
Enabling and disabling table for the IM column store in Oracle 12c
In order to Enable or Disable the IM Column Store for Tables, your database must be with the Oracle Database In-Memory (Database In-Memory) option enabled. If not, see this post : https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/ According to Oracle, “You enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. You disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.”. Then, in this post I am going to show you…
Enabling Oracle Database In-Memory (Database In-Memory) option in Oracle Database 12c Release 1
According to Oracle documentation “Oracle Database In-Memory (Database In-Memory) is a suite of features, first introduced in Oracle Database 12c Release 1 (12.1.0.2), that greatly improves performance for real-time analytics and mixed workloads”. Therefore, in this post I will explain how to enable your Oracle Database to use the In-Memory option : 1- Be sure that your database is running on version level 12.1.0 or higher: 2- Set the parameter INMEMORY_SIZE to any value at about 100M (minimum setting): 3- Restart the database: brunorsHi! I am Bruno, a Brazilian born…
Retrieving a dropped table on Oracle 12c
Sometimes developers need to recover some tables and we as Oracle DBAs are asked to recover them. So in this post I am going to show you how do to that. I will be creating the table named TECHDATABASKET under the SH user using the dba_tables as example and afterwards I am going to use the syntax “FLASHBACK TABLE SH.TECHDATABASKET TO BEFORE DROP;” in order to recover this table. In the meantime, we need to execute a SELECT key on the table dba_recyclebin or simply the usage of the command…
[Oracle 12c]How to remove and add databases from Oracle cluster
Owner:oracle (Oracle software owner) Database: techdb Instances: techdb1, techdb2 Servers/Node: basket-techdb01, basket-techdb02 To remove and later add the cluster Oracle database, follow these steps: It is important to know the previous configuration for a possible backup: Stop the database: Remove the database from cluster configuration: Add the database in the cluster configuration: (For all nodes) Start the database: Check the configuration again : Check the status of the database : brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a…
How to check if a database is configured with Oracle Multitenant Architecture
Check whether the database uses Oracle Multitenant Architecture; it is a simple operation. You can check this feature by querying the v$database using the CDB field as in the SQL example below: Additionally, in case your database is a multitenant database, you can visualize it by using the following query: Published: 08/11/2018Updated: 12/04/2023 brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an…
Creating a CDB database by command line
Sometimes the Oracle DBA needs to create a CDB database without DBCA. This procedure is possible. However, it is needed some additional steps to be done. For instance, to run some scripts to create components who DBCA utilitty create automatically. Therefore, in this post I am going to show how to create a CDB database by command line: Create directories for the Oracle Database. As the database will be named “db2”, some file system structures will contain this word: Create the init<SID>.ora file at $ORACLE_HOME/dbs: Start the…
Simple way how to use Database Point-in-Time Recovery
There are some situations where it is relevant for DBA to use FLASHBACK or Database Point-in-Time Recovery such as when a user modifies some data incorrectly or also when an upgrade failed and if it wishes to revert the previous situation of the database among several others possibilities. With this, in this post I will be explaining in a simple way how to use Database Point-in-Time Recovery. Therefore, I will be considering some requirements for this operation be possible: – The database must be enabled in Archivelog mode: – The…
Oracle Database 12c : Common User x Local User
In this post I will be showing you how to create an user in the CDB, what are called “Common Users” (users using the COMMON_USER_PREFIX parameter to set the user prefix at creation time) and then afterwards an user in a PDB called TECHDATABASKET , which are the “Local Users”. 1- Creating Common User in a CDB: 2- Creating Local User in a PD Neste post estarei mostrando simplificamente como criar um usuário no CBD, o que chamados de “Common Users” (usuários que utilizam o parâmetro COMMON_USER_PREFIX para definir o…