From 12c onwards, MGMTDB which is known as GRID INFRASTRUCTURE MANAGEMENT REPOSITORY( GIMR) , stores diagnostic and performance related data for GRID infrastructure. This MGMTDB is created as part of oracle grid installation. However there might be some instances where mgmtdb gets corrupted or requirement is to move the database to another diskgroup, then solution […]

From Oracle 12.2 onwards we can flashback a Pluggable database(PDB). And flashback is very easy and simple if LOCAL UNDO is enabled(which is also a new feature in Oracle 12.2). Let’s start the DEMO. DEMO: 1. Check whether local undo is enabled or not. SQL> select * from database_properties where property_name=’LOCAL_UNDO_ENABLED’; PROPERTY_NAME PROPE DESCRIPTION ———————– […]

For creating an encrypted tablespace in a PLUGGABLE DATABASE ( PDB) for multitenant oracle 12c setup, we need to do a few additional steps. DEMO: In the below DEMO, we will create a encrypted tablespace under a pluggable database SDCP1 1. Update sqlnet.ora file with ENCRYPTION_WALLET_LOCATION cat sqlnet.ora # sqlnet.ora Network Configuration File: /export/home/oracle/product/12c/product/12.2.0/dbhome_1/network/admin/sqlnet.ora # […]

In the multitenant database, For getting an export dump from the pluggable database, we need a follow a bit different process. DEMO: PLUGGABLE DATABASE(PDB) – PRE1 SCHEMA_NAME – DBACLASS ( this schema/user is present in PDB PRE1) 1. Make sure PDB service is registered in the listener. $ lsnrctl status LISTENER_POC LSNRCTL for Solaris: Version […]

In this article , we will provide steps for rolling back a patch in non-rac standalone database. PATCH TO BE ROLLED BACK – 27105253 – Database Jan 2018 Release Update (DB RU) 12.2.0.1.180116   1. Check the existence of patch in oracle home inventory. $ORACLE_HOME/OPatch/opatch lsinventory | grep 27105253 Patch 27105253 : applied on Thu […]

In this article , we will explain the steps for applying patch Database Jan 2018 Release Update (DB RU) on oracle 12.2 database. Beginning in July 2017, Oracle has transitioned to a more flexible and responsive strategy for the database software release process. These changes only affect Database and Grid Infrastructure release 12.2 or later. […]

In oracle 12.2 version, we can perform flashback at PDB level also.It has no dependency on container. EXAMPLE: Create a restore point at PDB level: SQL> alter session set container=HEPDB1; SQL> create restore point CHECKPOINT_1 guarantee flashback database; SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT; NAME TIME SCN PDB GUA ————– ————————————————————————— ———- — — CHECKPOINT_1 05-MAR-18 […]

                TRUST_EXISTING_TABLE_PARTITIONS is a new option for the parameter data_option in the impdp utility of Oracle 12.2. Previously, If we are loading data to an existing partitioned table using impdp, then despite mentioning parallel option, partitions were getting loaded one by one, Which slowdown the import process.   […]

OUTBOUND_DBLINK_PROTOCOLS  specifies the network protocols allowed for communicating for outbound/outgoing database links in the database. It is introduced in Oracle 12.2 release. Possible values – { ALL | NONE | [ TCP | [, ] | TCPS | [, ] | IPC ] } By default, the value is set to ALL. i.e User can […]

Till oracle 12c, For doing DML transactions in parallel, we need to enable PDML (parallel DML) at session level. I.e before any DML statement, we need to issue below statement. ALTER SESSION ENABLE PARALLEL DML; — Then parallel dml statement insert /*+ parallel(8) */ into TEST2 select * from TEST2; In oracle 12c, it introduced […]