Month: January 2018

Temporary Tablespace Group

Temporary Tablespace and Temporary Tablespace Group

Create a temporary tablespace, and implicitly add it to a temporary tablespace group.

SQL>CREATE BIGFILE TEMPORARY TABLESPACE TEMP01 TEMPFILE '+DATA1' 
    SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32G EXTENT MANAGEMENT 
    LOCAL UNIFORM SIZE 1M TABLESPACE GROUP TEMP_GRP;

Add a temporary tablespace to the temporary tablespace group.

SQL>ALTER TABLESPACE temp02 TABLESPACE GROUP TEMP_GRP;

Remove a temporary tablespace from a temporary tablespace group.

SQL>ALTER TABLESPACE temp03 TABLESPACE GROUP '';

Assign a user to a default temporary tablespace group.

SQL>ALTER USER TESTUSER TEMPORARY TABLESPACE TEMP_GRP;

Assign database a  default temporary tablespace group.

SQL>ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GRP;

Pluggable database altered.

Check temporary tablespace and its files.

SQL> select tablespace_name,FILE_ID,file_name,AUTOEXTENSIBLE,
     MAXBYTES/1024/1024/1024, INCREMENT_BY,USER_BYTES/1024/1024/1024 
     from dba_temp_files;

Check temporary tablespace and group.

SQL>select * from DBA_TABLESPACE_GROUPS;

Check who is using temporary tablespace.

SQL> select INST_ID,USERNAME,TABLESPACE,CONTENTS,SQL_ID,BLOCKS 
     from gv$SORT_USAGE;

Drop temporary tablespace.

SQL> drop tablespace temp02 including contents and datafiles;
drop tablespace temp02 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary 
           tablespace group

Drop  temporary tablespace group.

After remove all the temporary tablespace from temporary tablespace group, 
the temporary tablespace group will be dropped from database automatically.

Drop Temporary Tablespace Hangs With “enq: TS – contention”

Background process SMON is using the temporary tablespace to be dropped with “enq: TS – contention” wait event.

After created a new temporary tablespace TEMPNEW as database default temporary tablespace, then try to drop the old temporary tablespace “TEMP”, it hangs up forever.

SQL> drop tablespace temp including contents and datafiles;

Check session wait event “enq: TS – contention” against TEMP tablespace.

SQL> select SID,EVENT,P2TEXT,P2,SECONDS_IN_WAIT,WAIT_CLASS 
       from v$session_wait 
      where sid=3413;

SID   EVENT                P2TEXT        P2  SECONDS_IN_WAIT WAIT_CLASS
----- -------------------- ------------- --- -------------- ----------
 3413 enq: TS - contention tablespace ID  2  973             Other

SQL> select TS#,name from v$tablespace where ts#=2;

TS#         NAME
---------- -----------
 2         TEMP

Check who is still using temporary tablespace TEMP.

SQL> select INST_ID,USERNAME,TABLESPACE,CONTENTS,SQL_ID,BLOCKS 
       from gv$SORT_USAGE;

INST_ID     USERNAME   TABLESPACE  CONTENTS  SQL_ID       BLOCKS
---------- ---------   ----------- --------- ------------ ----------
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER1       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER2       TEMP        TEMPORARY cjjt4dw97qc1n 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128

6 rows selected.

Those user process are waiting for TS enqueue which is held by SMON.

SQL> SELECT SID,ID1,ID2,LMODE,REQUEST FROM V$LOCK WHERE TYPE='TS';

SID         ID1       ID2        LMODE       REQUEST
---------- ---------- ---------- ---------- ----------
 3413      2          1          0           6
 3413      2          0          6           0
 4261      7          2          3           0

SQL> SELECT PROGRAM FROM V$SESSION WHERE SID in (3413,4261);

PROGRAM
-----------------------------
sqlplus@ractest1 (TNS V1-V3)
oracle@ractest1 (SMON)

SOLUTION

  1. Bounce the database.
  2. then drop the temporary tablespace successfully.

Huge ASM LMHB Trace File Size

The ASM LMHB trace file is huge in 12.1.0.2 GI environment.

$cd /u01/app/grid/diag/asm/+asm/+ASM1/trace
$ ls -ltr *lmhb*
-rw-r----- 1 grid oinstall 11551946219 Jan 8 16:59 +ASM1_lmhb_88049.trc

According to (Doc ID 2137683.1), it is a known bug, which is fixed in 12cR2 GI.

Or

Apply one-off Patch 20878790.

Or

Just empty the trace file periodically by :

$ cat /dev/null>+ASM1_lmhb_88049.trc

RMAN Backup Failed With RMAN-03014 RMAN-03009

RMAN failed with errors while resync the recovery catalog.

...
..
.
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03015: error occurred in stored script al_netbkup
RMAN-03002: failure of allocate command at 01/10/2018 08:52:24
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 01/10/2018 08:52:24
ORA-01403: no data found
RMAN>

Find there is a database guarantee restore point.

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME
---------- --------------------- --- ------------ ---------------------------------------------------------------------------
RESTORE_POINT_TIME PRE
--------------------------------------------------------------------------- ---
NAME CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
 851245362 1 YES 1.9327E+10 09-JAN-18 10.46.35.000000000 AM
 YES
POINT_201801091046

Drop this restore point.

SQL> drop restore point POINT_201801091046;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected

Check again, both sync catalog and RMAN backups are successful.

RMAN> list backup of database;

starting full resync of recovery catalog
full resync complete

List of Backup Sets
===================
...
..
.

OEM Blackout Stuck in “Stop Pending” Status

There is one blackout stuck in “Stop Pending” Status for OEM 13c, when manually stopped it with errors:

Error: The blackout is already pending stop

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe
Log in

Now check from OEM console again, the problem is gone.