How to create Oracle pluggable database

 

Step1> Login to the database as sysdba

Step2> Run create pluggable database command from CDB container

SQL> create pluggable database PDB1 admin user admin identified  by password;
Pluggable database created.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                                MOUNTED

Step3> Open pluggable database in read/write mode

SQL> alter pluggable database PDB1 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                                READ WRITE NO

Step4> Save the pluggable database state

SQL> alter pluggable database PDB1 save state;

Pluggable database altered.

How to clone an Oracle pluggable database (PDB)

 

Step1> Bring the Source PDB in Read Only Mode

SQL> alter session set container=PDB;
Session altered.

SQL> alter pluggable database close immediate;
Pluggable database altered.

SQL> alter pluggable database open read only;
Pluggable database altered.

SQL> select con_id,name,open_mode,restricted from v$pdbs;
    CON_ID NAME       OPEN_MODE                      RESTRICTE
---------- ---------- ------------------------------ ---------
         2 PDB       READ ONLY                      NO

Step2> Clone the Source PDB

SQL> create pluggable database PDBCL from PDB ;
Pluggable database created.

SQL> select PDB_NAME,STATUS from cdb_pdbs;
PDB_NAME             STATUS
-------------------- ---------------------------
PDB$SEED           NORMAL
PDB                       NORMAL
PDBCL                  NEW

Step3> Open the Clone PDB

SQL> alter pluggable database PDBCL open;
Pluggable database altered.

Step4> Verify the Status of Clone PDB
SQL> select con_id,name,open_mode,restricted from v$pdbs;

Step5> Bring Back the Source PDB in Read Write Mode again
SQL> Alter pluggable database PDB close immediate;
SQL> Alter pluggable database PDB open;

How to configure Auto Login wallet in Oracle

 

Step1> Check the Wallet's Current Status

SQL> set linesize 200

SQL>col WALLET_DIR for a32
SQL>col status for a21

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
STATUS                WALLET_DIR                       WALLET_TYPE
--------------------- -------------------------------- ------------------------------------------------------------
OPEN                  +DATA1/CDB/wallet/           PASSWORD

1 row selected.

Step2> Create local Auto Login keyStore from existing keyStore

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE  '+DATA1/CDB/wallet/' IDENTIFIED BY "xxxxxx";

keystore altered.

Step3> Close the Password Wallet

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "xxxx" CONTAINER=ALL;

keystore altered.

Step4> Check the Status of Auto Login Wallet

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;

STATUS                WALLET_DIR                       WALLET_TYPE
--------------------- -------------------------------- ------------------------------------------------------------
OPEN                  +DATA1/CDB/wallet/           LOCAL_AUTOLOGIN

1 row selected.

ORA-28365: wallet is not open while starting the Database

$ srvctl start instance -d CDB -i CDB01

PRCR-1013 : Failed to start resource ora.cdb.db
PRCR-1064 : Failed to start resource ora.cdb.db on node node1.oracle.com
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-28365: wallet is not open
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/node1.oracle.com/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.cdb.db' on 'node1.oracle.com' failed

Solution:
Start the instance in mount mode
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size            1392511096 bytes
Database Buffers          738197504 bytes
Redo Buffers               13848576 bytes
Database mounted.

Check wallet status

set linesize 200
col WALLET_DIR for a32
col status for a21

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
STATUS                WALLET_DIR                       WALLET_TYPE
--------------------- -------------------------------- ------------------------------------------------------------
CLOSED                +DATA1/CDB/wallet/           UNKNOWN

Open the Keystore

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "xxxxx";

keystore altered.

Open the database

SQL> alter database open;

Database altered.

Droping the database in silent mode using DBCA

 

$ export ORACLE_HOME=/u01/oracle/12.2.0.1/dbhome_1

$ export PATH=$PATH:$ORACLE_HOME/bin

$dbca -silent -deleteDatabase -sourceDB CDBNAME -forceArchiveLogDeletion -sysDBAPassword xxxxxxx -sysDBAUserName sys

Displaying Top consuming process in Linux

Displaying top CPU consuming processes:

ps aux | head -1; ps aux | sort -rnk 3 | head -10

Displaying top memory-consuming processes:

ps aux | head -1; ps aux | sort -rnk 4 | head -10

How to create directory in oracle database

SQL> Create directory PAP_HOME_DIR    as '/userdata/applications/cusap/data/gs_data';
SQL> grant read, write on directory PAP_HOME_DIR to APPS;

To query the privileges given to directory execute the following:

SQL> SET lines 80
SQL> COL grantee FORMAT a20
SQL> COL privilege FORMAT a10
SQL> SELECT directory_name, grantee, privilege  FROM user_tab_privs t, all_directories d 
 WHERE t.table_name(+)=d.directory_name ORDER BY 1,2,3;