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

Oracle 12c CDB database crash with ORA-600 INTERNAL_PLAN

I have installed Oracle 12c recently on linux x86 64bit linux server.  This is using new Container architecture (i.e CDB)

I connect to server and started using SQLplus ,whithin one minute of database open, i got connect lost error message,  when try to connect again, found database instance is no more, it was crashed.

I tried one more time to startup, just to see the behaviour as same results, it got crashed again as soon as database opened.

When checked the Alert.log found the following ORA-600 causing databse crash.

###############
Setting Resource Manager plan SCHEDULER[0x4459]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc  (incident=16873) (PDBNAME=PDB$SEED):
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01/incident/incdir_16873/CDB01_dbrm_3506_i16873.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 18 01:33:58 2015
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc:
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Fri Sep 18 01:33:58 2015
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc:
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Fri Sep 18 01:33:58 2015
USER (ospid: 3506): terminating the instance due to error 56710
Fri Sep 18 01:33:58 2015
System state dump requested by (instance=1, osid=3506 (DBRM)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_diag_3504_20150918013358.trc
Fri Sep 18 01:33:59 2015
Dumping diagnostic data in directory=[cdmp_20150918013358], requested by (instance=1, osid=3506 (DBRM)), summary=[abnormal instance termination].
Fri Sep 18 01:33:59 2015
Instance terminated by USER, pid = 3506


Problem 

ORA-600:[kgskigetelt_subplan] . is due to bug and as of date(18 Sep 15) no patch or workaround available for this bug.

Bug 20479923 : ORA-00600:[KGSKIGETELT_SUBPLAN] FOLLOWED BY INSTANCE CRASH

ORA-01033 when connecting to PDB, Pluggable Database ORACLE initialization or shutdown in progress

ORA-01033: ORACLE initialization or shutdown in progress 

ORA-01033: ORACLE initialization or shutdown in progress
01033. 00000 -  "ORACLE initialization or shutdown in progress"
*Cause:    An attempt was made to log on while Oracle is being started up
           or shutdown.

*Action:   Wait a few minutes. Then retry the operation.

Upload was successful but collections currently disabled - disk full

oracle@dbserver1:/u01/app/oracle/diag> emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /u01/app/oracle/product/agent11g
Agent binaries    : /u01/app/oracle/product/agent11g
Agent Process ID  : 25212
Parent Process ID : 25185
Agent URL         : https://dbserver1.oraukint.local:3872/emd/main/
Repository URL    : https://oemserver.orauk.local:1159/em/upload
Started at        : 2014-10-16 15:01:19
Started by user   : oracle
Last Reload       : 2014-10-16 15:16:26
Last successful upload                       : 2014-10-16 15:12:51
Total Megabytes of XML files uploaded so far :    25.58
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :     3.97%
Collection Status                            : Disabled by Upload Manager
Last successful heartbeat to OMS             : 2014-10-16 15:17:58
---------------------------------------------------------------
Agent is Running and Ready
oracle@dbserver1:/u01/app/oracle/diag> emctl  upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload was successful but collections currently disabled - disk full

oracle@dbserver1:/>cd $ORACLE_HOME/config

grep -i  DiskUsedPct emd.properties

UploadMaxDiskUsedPct=98

UploadMaxDiskUsedPctFloor=98

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> bdf .

Filesystem          kbytes    used   avail %used Mounted on

/dev/vg01/lvol1    52424704 45984123 6440581   99% /u01

Solution:-

- As we can clearly see disk utilization is above the defined limit in emd.properties.

Clear space from /u01 filesystem where agent is installed.

restart agent 

emctl stop agent

emctl start agent.

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> emctl stop agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Stopping agent ... stopped.

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> emctl start agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Starting agent ............................. started.

oracle@dbserver1:/u01/app/oracle/product/agent11g/sysman/config> emctl status agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

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

Agent Version     : 11.1.0.1.0

OMS Version       : 11.1.0.1.0

Protocol Version  : 11.1.0.0.0

Agent Home        : /u01/app/oracle/product/agent11g

Agent binaries    : /u01/app/oracle/product/agent11g

Agent Process ID  : 350

Parent Process ID : 325

Agent URL         : 

https://dbserver1.oraukint.local:3872/emd/main/

Repository URL    : 

https://oemserver.orauk.local:1159/em/upload

Started at        : 2014-10-16 15:33:17

Started by user   : oracle

Last Reload       : 2014-10-16 15:33:17

Last successful upload                       : 2014-10-16 15:34:23

Total Megabytes of XML files uploaded so far :     0.15

Number of XML files pending upload           :        0

Size of XML files pending upload(MB)         :     0.00

Available disk space on upload filesystem    :    26.32%

Last successful heartbeat to OMS             : 2014-10-16 15:34:14

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

Agent is Running and Ready

12c Database is it any different when Install ????

Hi All,

12c database is out now from many months and today thought to install the 12c database , just to see how different it look from installation point of view but observed the following so far.

* Installation is exactly same as 11g (only Logo changed to 12c) , Last page got option of clicking Edit to change any configuration you don't like (new )
* New OS group introduced , to make DBA's life more complicated until we all understand what to use them for.
* QOPatch directory scared me, thinking patching i have to learn again from scratch but no Patching is same as before ( Same OPath, here you need 12.0.1.x.x version) , still need to figure out what this QOPatch is used for.


I know there is Long list of changes(New Features) in 12c specially Plugable database etc, i will be reading them and will share with everyone.

Operating system user Groups in 12c Plugable database

Oracle 11g  we saw new OS group called SYSASM , now to add more flexibility (and also complexity ) Oracle introduced few moreOS group for Oracle OS user when working with 12C database.

New Groups for 12c database Installation : OSBACKUPDBA, OSDGDBA , OSKMDBA

The OSDBA group (typically, dba) 

You must create this group the first time you install Oracle Database software on the system. This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege). The name used for this group in Oracle code examples is dba.

The OSOPER group for Oracle Database (typically, oper) 

This is an optional group. Create this group if you want a separate group of operating system users to have a limited set of database administrative privileges (the SYSOPER privilege). This group cannot directly connect as SYSOPER, unless explicitly granted. However, they have the privileges granted by the SYSOPER privilege. By default, members of the OSDBA group have all privileges granted by the SYSOPER privilege.

The usual name for this group is oper.

The OSBACKUPDBA group for Oracle Database (typically, backupdba) 

Create this group if you want a separate group of operating system users to have a limited set of database backup and recovery related administrative privileges (the SYSBACKUP privilege). The usual name for this group is backupdba.

The OSDGDBA group for Oracle Data Guard (typically, dgdba) 

Create this group if you want a separate group of operating sytem users to have a limited set of privileges to administer and monitor Oracle Data Guard (the SYSDG privilege). The usual name for this group is dgdba.

The OSKMDBA group for encyption key management (typically, kmdba) 

Create this group if you want a separate group of operating sytem users to have a limited set of privileges for encryption key management such as Oracle Wallet Manager management (the SYSKM privilege). The usual name for this group is kmdba.