Oracle 12c rman backup gone with drop pluggable database pdb

Today, I was testing backup and restore process for new oracle 12c pluggable database and part of test i thought to test drop and restore of pluggable database.


I have created a small two tablespace database PDB1 from PDB$SEED datbase for this test.

Before attempting the drop , i thought to backup the database so i can restore later when database is droppped and perform recovery operation.


RMAN> backup pluggable database PDB1;

Starting backup at 28-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00021 name=/u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_sysaux_c31rdofq_.dbf
input datafile file number=00020 name=/u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_system_c31rdofp_.dbf
channel ORA_DISK_1: starting piece 1 at 28-OCT-15
channel ORA_DISK_1: finished piece 1 at 28-OCT-15
piece handle=/u01/oadata/fast_recovery_area/CDBORA/232C3E643AA01E17E0530100007FCD5B/backupset/2015_10_28/o1_mf_nnndf_TAG20151028T080754_c31s6cop_.bkp tag=TAG20151028T080754 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 28-OCT-15

Starting Control File and SPFILE Autobackup at 28-OCT-15
piece handle=/u01/oadata/fast_recovery_area/CDBORA/autobackup/2015_10_28/o1_mf_s_894269310_c31s7gmk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-OCT-15

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

5       Full    660.88M    DISK        00:00:26     28-OCT-15      

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20151028T080754

        Piece Name: /u01/oadata/fast_recovery_area/CDBORA/232C3E643AA01E17E0530100007FCD5B/backupset/2015_10_28/o1_mf_nnndf_TAG20151028T080754_c31s6cop_.bkp

  List of Datafiles in backup set 5

  Container ID: 5, PDB Name: PDB1

  File LV Type Ckp SCN    Ckp Time  Name

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

  20      Full 1964340    28-OCT-15 /u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_system_c31rdofp_.dbf

  21      Full 1964340    28-OCT-15 /u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_sysaux_c31rdofq_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

6       Full    17.20M     DISK        00:00:00     28-OCT-15      

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20151028T080830

        Piece Name: /u01/oadata/fast_recovery_area/CDBORA/autobackup/2015_10_28/o1_mf_s_894269310_c31s7gmk_.bkp

  SPFILE Included: Modification time: 28-OCT-15

  SPFILE db_unique_name: CDBORA

  Control File Included: Ckp SCN: 1964358      Ckp time: 28-OCT-15

SQL> SELECT CON_ID,NAME, OPEN_MODE FROM V$PDBS;

    CON_ID NAME      OPEN_MODE

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

2 PDB$SEED      READ ONLY

3 PDB2      READ WRITE

4 DPBSALES      READ WRITE

5 PDB1      READ WRITE

SQL> DROP PLUGGABLE DATABASE PDB1;

DROP PLUGGABLE DATABASE PDB1

*

ERROR at line 1:

ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

as it failed to drop without deleting files, I used including datafiles option.

SQL> DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILES;

Pluggable database dropped.

now database was dropped , i thought to go back to my backup and restore .

RMAN> LIST Backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

6       Full    17.20M     DISK        00:00:00     28-OCT-15      

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20151028T080830

        Piece Name: /u01/oadata/fast_recovery_area/CDBORA/autobackup/2015_10_28/o1_mf_s_894269310_c31s7gmk_.bkp

  SPFILE Included: Modification time: 28-OCT-15

  SPFILE db_unique_name: CDBORA

  Control File Included: Ckp SCN: 1964358      Ckp time: 28-OCT-15

I couldn't find my backup in rman ,because information from controlfile is gone. Fortunately my backup file are still there, so its not big issue, i could use those to restore my database. but as current controlfile no longer knows about PDB1 i will have to restore the controlfile of time before dropping the database.

Happy Learning !!!

How to Cleanup huge crf / CHM repository file in Grid Infrastructure home 11.2.0.2 11.2.0.3 11.2.0.4

We got alert on RDBMS home file system is above 90% full and investigation revealed crf directory under grid infrastructure home was above 26G.

once you know its $ORACLE_HOME/crf and you can easily guess these are the files  repository oforacle Cluster Health Monitor

This issue was observed in our two node cluster running Oracle Grid Infrastructure 11g 11.2.0.4.0 on Linux.


---  To confirm / Crosscheck.

$ORACLE_HOME/bin/oclumon manage -get reppath

CHM Repository Path = /cluster/app/11.2.0/grid/crf/db/cmsdb02

Done


[root@cmsdb02 cmsdb02]# cd /cluster/app/11.2.0/grid/crf/db/cmsdb02
[root@cmsdb02 cmsdb02]# ls -lrt
total 25830096
-rw-r----- 1 root root        8192 Sep  2 07:07 repdhosts.bdb
-rw-r----- 1 root root       24576 Sep  2 07:07 __db.001
-rw-r----- 1 root root        8192 Sep  2 07:10 crfconn.bdb
-rw-r--r-- 1 root root   120000000 Sep  2 07:11 cmsdb02.ldb
-rw-r----- 1 root root    16777216 Oct 27 13:11 log.0000030408
-rw-r----- 1 root root    16777216 Oct 27 13:42 log.0000030409
-rw-r----- 1 root root      401408 Oct 27 13:42 __db.002
-rw-r----- 1 root root   329068544 Oct 27 13:42 crfts.bdb
-rw-r----- 1 root root   508063744 Oct 27 13:42 crfloclts.bdb
-rw-r----- 1 root root   399507456 Oct 27 13:42 crfhosts.bdb
-rw-r----- 1 root root   404766720 Oct 27 13:42 crfcpu.bdb
-rw-r----- 1 root root 24340660224 Oct 27 13:42 crfclust.bdb
-rw-r----- 1 root root   407449600 Oct 27 13:42 crfalert.bdb
-rw-r----- 1 root root       57344 Oct 27 13:43 __db.006
-rw-r----- 1 root root     1187840 Oct 27 13:43 __db.005
-rw-r----- 1 root root     2162688 Oct 27 13:43 __db.004
-rw-r----- 1 root root     2629632 Oct 27 13:43 __db.003

Bug/Fix :

Checking on metalink confirmed its a bug

Bug 12711827 : HUGE CRFCLUST.BDB WHICH CAN NOT BE REDUCED IN FILESYSTEM  11.2.0.2
Bug 14479330 : HUGE SIZE OF CRFCLUST.BDB - ORACLE 11.2.0.3

Look like based bug was was fixed with below in 11.2.0.3 to control the size but there is not fix mentioned for any of above bugs exclusively.

Patch 10165314: CHM/CRF/IPDOS REPOSITORY EXCEEDS 1GB AFTER ADD/REMOVE NODE OR FRESH INSTALL



Workaround/Solution.

temporary workaround of the problem is to delete the CHM repository files and ORA.CRF should be down when you cleanup.


For detail Check metalink  1343105.1 Oracle Cluster Health Monitor (CHM) using large amount of space (more than default)


set oracle ASM home.

$ . oraenv
+ASM1

$ crsctl stat res ora.crf -init
NAME=ora.crf
TYPE=ora.crf.type
TARGET=ONLINE
STATE=ONLINE on cmsdb02


$oclumon manage -get reppath

CHM Repository Path = /cluster/app/11.2.0/grid/crf/db/cmsdb02
Done

# Stop CHM (Cluster health monitor) process ora.crf

$ crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'cmsdb02'
CRS-2677: Stop of 'ora.crf' on 'cmsdb02' succeeded

# Delete the revelent files. Please remember you need root password to delete the files as they owned by root.

$ cd /cluster/app/11.2.0/grid/crf/db/cmsdb02
$ rm *.bdb


# Start CHM (Cluster health monitor) process ora.crf

$ crsctl start res ora.crf -init


# Check process is back up and running


$ crsctl stat res ora.crf -init
NAME=ora.crf
TYPE=ora.crf.type
TARGET=ONLINE
STATE=ONLINE on cmsdb02