Month: February 2017

ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Before dropping a temporary tablespace, make sure it is not a default temporary tablespace.

Trying to drop a temporary tablespace which belongs to a default temporary tablespace group.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME          TABLESPACE_NAME
------------------- ------------------------------
TEMP_OLTP           TEMP
TEMP_OLTP           TEMP2
TEMP_OLTP           TEMP3

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group


SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Change default temporary tablespace from  temporary tablespace group TEMP_OLTP to a temporary tablespace TEMP.:

SQL> alter pluggable database default temporary tablespace temp;

Pluggable database altered.

Now drop temporary tablespaces:

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME      TABLESPACE_NAME
--------------- -----------------
TEMP_OLTP        TEMPA

Make temporary tablespace group as default again:

SQL> alter pluggable database default temporary tablespace TEMP_OLTP;

Pluggable database altered.
Advertisement

Manage Temporary Tablespace in Container Database (CDB) and Pluggable Database (PDB)

Both CDB and PDB has its own dedicated TEMP tablespace.

In a 12cR1, both CDB and PDB have own dedicated TEMP tablespace by default

SQL> show con_id con_name

CON_ID
---------
1

CON_NAME
---------
CDB$ROOT

SQL> select CON_ID,NAME from v$tablespace where name='TEMP';

CON_ID      NAME
---------- -------------
 1         TEMP
 2         TEMP
 3         TEMP

Default Temporary Tablespace in PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP OLTP_TEMP;

Tablespace altered.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE OLTP_TEMP;

Pluggable database altered.

Check the default Temporary Tablespace for PDB:

SQL> show con_id con_name

CON_ID
---------
3

CON_NAME
---------
PDB1

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME           PROPERTY_VALUE  DESCRIPTION
----------------------- --------------- --------------------------
DEFAULT_TEMP_TABLESPACE OLTP_TEMP       Name of default temporary 
                                        tablespace

Check the default Temporary Tablespace for CDB as below, we can see CDB is still using its default temporary tablespace TEMP.

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME             PROPERTY_VALUE  DESCRIPTION
------------------------- --------------- -------------------------
DEFAULT_TEMP_TABLESPACE   TEMP            Name of default temporary
                                          tablespace

Tablespace SYSMGMTDATA Is Full in GIMR Database (-MGMTDB)

Cluster Health Monitor ( CHM) tables could be fragmented in MGMTDB database.

1) Check the datafile of tablespace SYSMGMTDATA in PDB:

SQL> select PDB_NAME from dba_pdbs;

PDB_NAME
-------------
RACTEST
PDB$SEED

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

SQL> select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,
            BYTES/1024/1024,MAXBYTES/1024/1024 
      from dba_data_files 
     where TABLESPACE_NAME='SYSMGMTDATA';

TABLESPACE_NAME 
--------------- 
FILE_NAME
----------------------------------------------------------------------   
AUT BYTES/1024/1024 MAXBYTES/1024/1024
--- --------------- -------------------
SYSMGMTDATA      +OCR_VOTE/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.922884445 
NO   2048              2048

2) Check CHM data in repository, and we can see the purging is working:

SQL> select trunc(BEGINTIME), count(*) 
       from chm.CHMOS_PROCESS_INT_TBL 
   group by trunc(BEGINTIME) 
   order by trunc(BEGINTIME);

TRUNC(BEG  COUNT(*)
---------  ---------
21-FEB-17  1222178
22-FEB-17  3265667
23-FEB-17  1327167

3) Check the current CHM repository retention in seconds :

$ oclumon manage -get reppath

CHM Repository Path = +OCR_VOTE/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.922884445

$ oclumon manage -get repsize

CHM Repository Size = 136320 seconds

4) Check the big objects in repository, they are all from two partition tables CHMOS_PROCESS_INT_TBL and CHMOS_DEVICE_INT_TBL.

SQL> select owner, segment_name,segment_type , bytes/1024/1024 
       from dba_segments 
      where owner='CHM' 
        and bytes/1024/1024>10;
OWNER      SEGMENT_NAME            SEGMENT_TYPE       BYTES/1024/1024
---------- ----------------------  ------------------ ---------------
CHM        CHMOS_PROCESS_INT_TBL    TABLE PARTITION   18
CHM        CHMOS_DEVICE_INT_TBL     TABLE PARTITION   18
CHM        CHMOS_PROCESS_INT_TBL    TABLE PARTITION   18
CHM        CHMOS_DEVICE_INT_TBL     TABLE PARTITION   18

Solution 1 :  Move GIMR repository onto to a different disk group as per “How to Move 12c Grid Infrastructure Management Repository ( GIMR ) to Another Diskgroup“.

Solution 2: Manually truncate the big objects owned by CHM as per Doc ID 2177879.1. Normally the following two tables can be truncated :

SQL> truncate table CHM.CHMOS_PROCESS_INT_TBL;
SQL> truncate table CHM.CHMOS_DEVICE_INT_TBL;

Solution 3:  Resize datafile bigger. For example, increases the size from 2048M to 2560M

SQL> alter database datafile '+OCR_VOTE/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.922884445' resize 2560m;

Database altered.

Duplicate RAC Database Without Connecting To Target Database

Duplicate test RAC database from prod RAC database using overnight tape RMAN backups.

This post demonstrates how to refresh RAC TEST database from source RAC PROD database without connecting to source RAC PROD database by using overnight tape backups.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-12751: cpu time or run time policy violation

There is an overnight auto-purge job failure every day by throwing “ORA-12751: cpu time or run time policy violation” error.

Extracted trace file from slave process ( m001 ) of  Manageability Monitor Process ( MMON) :

*** 2017-02-01 01:11:39.393
*** SESSION ID:(9.3069) 2017-02-01 01:11:39.393
*** CLIENT ID:() 2017-02-01 01:11:39.393
*** SERVICE NAME:(SYS$BACKGROUND) 2017-02-01 01:11:39.393
*** MODULE NAME:(MMON_SLAVE) 2017-02-01 01:11:39.393
*** ACTION NAME:(Auto-Purge Slave Action) 2017-02-01 01:11:39.393
...
..
----- START DDE Action: 'ORA_12751_DUMP' (Sync) -----
CPU time exceeded 300 seconds
Time limit violation detected at:
ksedsts()+465<-kspol_12751_dump()+145<-dbgdaExecuteAction()+1065<-dbgerRunAction()+109<-dbgerRunActions()+4134<-dbgexPhaseII()+1873<-dbgexProcessError()+2680<-dbgeExecuteForError()+88<-dbgePostErrorKGE()+2136<-dbkePostKGE_kgsf()+71<-kgeselv()+276<-ksesecl0()+162
<-ksucin()+147<-delrow()+13790<-qerdlFetch()+1822<-delexe()+1001<-opiexe()+10315<-kpoal8()+2380<-opiodr()+917<-kpoodrc()+36<-rpiswu2()+1776<-kpoodr()+636<-upirtrc()+2436<-kpurcsc()+98<-kpuexec()+10790<-OCIStmtExecute()+39<-kewrose_oci_stmt_exec()+55<-kewrgwxf1_gwrsql_exft_1()+317
<-kewrgwxf_gwrsql_exft()+496<-kewrdsr1_delete_snaprange_1()+476<-kewrdsr_delete_snapshot_range()+133<-kewrpanp_purge_all_nonpts()+923<-kewrpdc_periodic_cleanup()+41<-kewrsnrm_snapshot_removal()+169<-kewrpsr_purge_snapshot_range()+116<-kewrapad_auto_purge_all_dbids()+682
<-kewraps_auto_purge_slave()+942<-kebm_slave_main()+586<-ksvrdp()+1766<-opirip()+674<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253Current Wait Stack:
...
..
.
*** 2017-02-01 01:15:05.083
*** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or run time policy violation
)
 *** SQLSTR: total-len=351, dump-len=240,
 STR={delete from WRH$_SYSMETRIC_HISTORY tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and }

We can see there is a 300 seconds ( 5 minutes ) CPU time  limitation resource violation.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe