ORA-03113: end-of-file on communication channel

ORA-03113: end-of-file on communication channel 

[oracle@oprd pfile]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 22 16:22:35 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.
Database opened.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB01                         MOUNTED

SQL> alter pluggable database PDB01 open;

Pluggable database altered.


What do I do to increase the size of the flash recovery area (FRA)?

Answer:  The size of the flashback recovery area (FRA) is determined by the size of the db_recovery_file_dest_size parameter.  To increase the size of the flash recovery area (FRA) simply  run this command:



SQL> alter system set db_recovery_file_dest_size=50g scope=both;

System altered.

SQL> show parameter db_rec

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big integer 50G
db_recycle_cache_size                big integer 0
SQL>
SQL>
SQL>
SQL> create pfile from spfile;

File created.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.
Database opened.

SQL> show parameter db_rec

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big integer 50G
db_recycle_cache_size                big integer 0


SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB01                         MOUNTED


SQL> alter pluggable database PDB01 open;

Pluggable database altered.

ORA-28374: typed master key not found in wallet

When i was trying to add datafile facing below issue on 12c multitenant pluggable-database.

SQL> create tablespace odsprod datafile size 5G;
create tablespace odsprod datafile size 5G
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet

SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS                         WALLET_TYPE

------------------------------ --------------------

OPEN_NO_MASTER_KEY             AUTOLOGIN

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;

WRL_PARAMETER                       STATUS                         WALLET_TYPE

------------------------------ --------------------

/u01/oracle/dcs/commonstore/wallets/tde/pdb01_iad1m2/   OPEN         AUTOLOGIN

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY <admin-password>  CONTAINER=all;

keystore altered.

SQL> ALTER SESSION SET CONTAINER =pdb01;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <admin-password> with backup;

keystore altered.

SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS                         WALLET_TYPE

------------------------------ --------------------

OPEN                           PASSWORD

SQL> create tablespace pdb01 datafile size 5G;

Tablespace created.

How to configure TDE in pluggable database in 12c

How to configure TDE in pluggable database in 12c for standalone and RAC environment 


APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

GOAL
 This document details step by step instructions to configure TDE in 12c pluggable database for standalone and RAC environment.

SOLUTION

 1. Add the below entry in sqlnet.ora file

ENCRYPTION_WALLET_LOCATION =
           (SOURCE = (METHOD = FILE)
                   (METHOD_DATA =
                              (DIRECTORY = /cdbrdbms/etc/$ORACLE_SID)
                   )
           )

2. Create Key store on CDB database and generate master encryption key for CDB

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/cdbrdbms/etc/MTc12c1' IDENTIFIED BY "welcome1";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "welcome1";
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "welcome1" WITH BACKUP;

Note: If TDE was already configured for CDB / other PDBs , then skip step 1 and step 2.
  
3. Verify the wallet has been opened in CDB database
 
select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
FILE
/cdbrdbms/etc/MTc12c1/
OPEN PASSWORD SINGLE NO
0


4. Connect to PDB
 
SQL> alter session set container=MTC12P2;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
MTC12P2


5. Open the keystore in that PDB and generate master encryption key for the PDB
 
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "welcome1";
keystore altered

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 MTC12P2 READ WRITE NO

SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
FILE
/cdbrdbms/etc/MTc12c1/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0


 
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "welcome1" with backup;
keystore altered.


SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
FILE
/cdbrdbms/etc/MTc12c1/
OPEN PASSWORD SINGLE NO
0




6. Create encrypted tablespace
 
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 MTC12P2 READ WRITE NO

SQL> create tablespace enc128_ts
datafile '/cdbrdbms/64bit/app/oracle/oradata/MTc12c1/MTc12p2/Test_encrption.dbf'
size 1M autoextend on next 1M
encryption using 'AES128'
default storage (encrypt)
/ 2 3 4 5 6
Tablespace created.
 


7. For RAC environment
7.1 Make sure encryption_wallet_location parameter is configured in sqlnet.ora file of all other RAC nodes.

ENCRYPTION_WALLET_LOCATION =
         (SOURCE = (METHOD = FILE)
              (METHOD_DATA =
                       (DIRECTORY = /cdbrdbms/etc/$ORACLE_SID)
                )
          )


7.2 Copy the wallet file ewallet.p12 from first RAC node to all other RAC nodes ENCRYPTION_WALLET_LOCATION directory

EXP-00111

ERROR :

EXP-00111: Table WORKER resides in an Encrypted Tablespace sujeet and will not be exported


Cause:

Encrypted Tablespace feature is not supported by classic export


Action:

Use DATAPUMP Utility to export this table.

ORA-28014: cannot drop administrative users

The below error is encountered when I was trying to drop schema from 12C database.

SQL> drop user BKP CASCADE;
drop user BKP CASCADE
*
ERROR at line 1:
ORA-28014: cannot drop administrative users

Solution :-

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> drop user BKP CASCADE;
User dropped.