oracle 12c

The Problem While connecting the database as sysdba, we are getting below error: sqlplus /as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed May 10 00:40:31 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. ERROR: ORA-12547: TNS:lost contact The version of Oracle database used here is 12.2.0.1.0. The Solution The solution is quite simple here. The OS variable ORACLE_BASE was not set specifically here. Just set this variable into the bash profile of oracle user … [Read more...] about ORA-12547: TNS:lost Contact (Oracle 12c2)

This is an example for the 12c new feature of creating interval partitioned tables as parent tables for reference partitioning. 1. Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table. CREATE TABLE parent (pk INT CONSTRAINT parent_pk PRIMARY KEY, i INT) PARTITION BY RANGE(i) INTERVAL (10) (partition p_p0 values less than ( 0), partition p_p1 values less than … [Read more...] about How to Create Interval-Reference Partitioned Tables in Oracle 12c

Question: How to create an encrypted tablespace in a 12c pluggable database of multi tenant DB? To create an encrypted tablespace in a 12c pluggable database requires some additional steps as compared to previous version 11g. The reason is by default the wallet does not hold a tablespace key for the pluggable databases. 1. Set parameter encryption_wallet_location in sqlnet.ora Example sqlnet.ora: # sqlnet.ora Network Configuration File: … [Read more...] about How To Create an Encrypted Tablespace in Oracle 12c Pluggable Database

The Problem On Oracle database 12.2.0.1 version, cpu_count is not correct: OS has 128 CPU in total, however CPU_COUNT is only 104. SQL> show parameter cpu NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cpu_count integer 104 … [Read more...] about Oracle Database 12c2 : CPU_COUNT is Wrong

In this release, a data file can now be moved online while it is open and being accessed, even for data files in system tablespace. Being able to move a data file online means that many maintenance operations, such as moving data to another storage device or moving databases into Oracle Automatic Storage Management (Oracle ASM), can be performed while users are accessing the system. This ensures that continuity of service and service-level agreements (SLA) on uptime can be met. With Oracle … [Read more...] about Oracle Database 12c New Feature – Move a Datafile Online

This post illustrates how to rename a database in 12.2 and then move the datafiles to another location online (given the DB name is changed, we expect the datafile are moved to another directory whose directory name is the new DB name). We will rename database from ORCL122 to CDB122, and move datafiles from /refresh/home/app/oracle/oradata/ORCL122/ to /refresh/home/app/oracle/oradata/CDB122/ 1. backup database. 2. set ORACLE_SID to ORCL122. 3. shutdown database, and then start it to … [Read more...] about How to rename database and then move datafile online in Oracle Database 12.2

Question: How to Restore Dropped PDB in Multitenant using Auxiliary instance ( For Single Instance Database)? In a multitenant container database (CDB), the metadata for data dictionary tables and view definitions is stored only in the root. However, each pluggable database (PDB) has its own set of data dictionary tables and views for the database objects contained in the PDB. So, a pluggable database (PDB) cannot be restored as a database. It must be restored as database object using an … [Read more...] about How to Restore a Dropped Pluggable Database (PDB) in Multitenant Environment

If flashback of CDB complete, there are chances to previously dropped PDB datafiles goes offline. There might be PDB dropped in past and now dropped PDB related datafiles becomes offline during flashback database. Example Backup of this database taken around scn 4700000. PDB3 was dropped at SCN 4800000. Flashback for CDB was complete until scn 4810629. Find the files those are offline (those are related to Dropped PDB i.e pdb3) sql> select file#,name from v$datafile; For this test, we … [Read more...] about How to Recover DROPPED PDB After Flashback of CDB