Oracle : Drop Pluggable Database (PDB) 19c

 

Oracle : Drop Pluggable Database (PDB)

• Dropping pluggable database is similar to dropping any other regular database, you have two options while dropping pluggable database related to its datafiles
• Dropping PDB including datafiles
• Dropping PDB keeping datafiles

Before dropping pluggable database we can check PDB status by

a. SQL> show pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           READ WRITE NO

SQL>

b. select con_id, name,open_mode from v$containers;

SQL> set lin 1000

SQL> col name format a20

SQL> select con_id, name,open_mode from v$containers;

    CON_ID NAME                 OPEN_MODE

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

         1 CDB$ROOT        READ WRITE

         2 PDB$SEED         READ ONLY

         3 PDB1                 READ WRITE

         4 PDB2                 READ WRITE

         5 PDB3                 READ WRITE

--> We will drop PDB3 now. Before dropping we need to close database.

SQL> alter pluggable database PDB3 close immediate;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           MOUNTED

SQL>

if our database is multi node RAC then we can use instances=all option. 

SQL> alter pluggable database PDB3 close immediate instances=all;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           READ WRITE NO

SQL> alter pluggable database PDB3 close immediate instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         5 PDB3                           MOUNTED

SQL>

--> Dropping Database

SQL> drop pluggable database PDB3 including datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

SQL>

SQL> drop pluggable database PDB3 keep datafiles; --> with keep datafiles option

Pluggable database dropped.

Enable/Disable Archive log mode in RAC 11gR2

Enable Archivelog

1)  Login to one of the nodes , verify the archive log mode

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 200
Current log sequence 201

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
PUB NOARCHIVELOG

2) Disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:

sqlplus "/ as sysdba"

SQL> alter system set cluster_database=false scope=spfile sid='orcl1;

3) Shutdown all instances accessing the clustered database:

  srvctl stop database -d orcl

4)Using the local instance, MOUNT the database:

sqlplus "/ as sysdba"
SQL> startup mount 

5) Enable archiving:

SQL> alter database archivelog;

6) Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='orcl1;

7) Shutdown the local instance:

SQL> shutdown immediate 

8) Bring all instance back up using srvctl:

srvctl start database -d orcl

9)Login to the local instance and verify Archive Log Mode is enabled:

sqlplus "/ as sysdba"

SQL> archive log list
Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     200
Next log sequence to archive  201

After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.

Disable Archivelog
1) export oracle_sid=orcl1

SQL> alter system set cluster_database=false scope=spfile sid='orcl1';

System altered.

2) srvctl stop database -d orcl -o immediate

3) SQL> startup mount

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Next log sequence to archive 19

Current log sequence 19

SQL> alter database noarchivelog;

Database altered.

SQL> alter system set cluster_database=true scope=spfile sid='orcl1';

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

4) srvctl start database -d orcl

5) export oracle_sid=orcl1

sqlplus / as sysdba

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Current log sequence 19

SQL>