Blog

“SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Takes Long Time and Uses Huge Temp Space

It is a good practice for DBA to periodically collect stats of fixed objects, data dictionary, SYS and SYSTEM schemas,

The following query from OEM agent keeps failing, after upgrading Enterprise Manager Cloud Control and a monitoring agent to 13.2, this query is run in the 11.2.0.4 target database:

SELECT MEDIA
 FROM V$BACKUP_PIECE_DETAILS
 WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 
   AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1

Also it uses huge temp space :

SQL> select USERNAME,TABLESPACE,BLOCKS*8/1024/1024 
      from V$TEMPSEG_USAGE 
  order by BLOCKS desc ;

USERNAME   TABLESPACE                       BLOCKS*8/1024/1024
---------- ------------------------------- ------------------
DBSNMP     TEMP                             30.649414

It is the exact same issue as per (Doc ID 2201982.1). so the following actions are taken :

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Finally flush the cursor out of memory for next running :

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, 
            loads, version_count, invalidations, parse_calls 
      from v$sqlarea 
     where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS    
---------------- ---------- --------------- ---------- -----
VERSION_COUNT INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
00000001418FA048 4087355773 1896464546      213        5          
1             1             213

SQL> exec dbms_shared_pool.purge('00000001418FA048,4087355773','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, 
            version_count, invalidations, parse_calls 
       from v$sqlarea 
      where sql_id='dx4nqvbtu06bx';

no rows selected

Have a check of the new execution time. We can see the new execution plan is created, and the execution time is only 0.25 second.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions,
            ELAPSED_TIME,ELAPSED_TIME/executions/1000000 TimePerExecution
      from v$sqlarea 
     where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE  PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME 
---------------- ----------- --------------- ---------- ------------ 
TIMEPEREXECUTION
----------------
00000001418FA048   4087355773   75529090       2          504858       .252429

How To Flush an Object Out the Library Cache

We know how to flash all shared pool by :

SQL>alter system flush shared_pool;

Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated

To start managed recovery process (MRP) with real time apply ( RTA):
11c: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
12c: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;

This warning will be seen in alert log if we issue the same command in 12c as we have done in 11c:

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Instead we can use the following command to eliminate the WARNING:

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

No standby redo logfiles available for T-1

It looks like a bug.

One single instance 12c Oracle database alert log shows the following information:

 RFS[1]: No standby redo logfiles available for T-1

While DataGuard shows ORA-16857 error:

DGMGRL> show database verbose "TESTSTY";

Database - TESTSTY

 Role: PHYSICAL STANDBY
 Intended State: APPLY-OFF
 Transport Lag: 7 minutes 9 seconds (computed 50 seconds ago)
 Apply Lag: 7 minutes 9 seconds (computed 50 seconds ago)
 Average Apply Rate: (unknown)
 Active Apply Rate: (unknown)
 Maximum Apply Rate: (unknown)
 Real Time Query: OFF
 Instance(s):
 TESTSTY

Database Warning(s):
 ORA-16857: standby disconnected from redo source for longer than specified threshold

DGMGRL> validate database "TESTSTY"
...
..
.
Warning: standby redo logs not configured for thread 1 on TESTSTY

Checked both primary and standby database, the standby redo logs have been created. It is strange to see the thread id is different between primary and secondary database.

— on standby :

SQL>select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS,CON_ID 
      from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024/1024 ARC STATUS     CON_ID
---------- ---------- -------------------- --- ---------- ----------
 4         0          1                    YES UNASSIGNED 0
 5         0          1                    YES UNASSIGNED 0
 6         0          1                    YES UNASSIGNED 0
 7         0          1                    YES UNASSIGNED 0

— On Primary

SQL>select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS,CON_ID from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024/1024 ARC STATUS     CON_ID
---------- ---------- -------------------- --- ---------- ----------
 4         1          1                    YES UNASSIGNED 0
 5         1          1                    YES UNASSIGNED 0
 6         1          1                    YES UNASSIGNED 0
 7         1          1                    YES UNASSIGNED 0

Drop all standby online redo logs on both primary and standby databases, and recreate then again by specify “thread 1” explicitly.

-- for standby db which is under recovery, recovery needs to be stopped first
SQL>alter database recover managed standby database cancel;

SQL>alter database add standby logfile thread 1 group 4 size 1024m;

It will create standby online redo logs for one under +FRA, and another one under from “db_create_file_dest” parameter, if “db_create_online_log_dest_x” are not defined.

Finally restart the recovery process, everything is fine.

SQL>alter database recover managed standby database disconnect nodelay;

Database altered.

Only see this thread issue for single instance database in 12c, which is a new feature or bug ?

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.